package database

import (
	"Engine/internal/model"
	"context"
	"database/sql"
	"fmt"
)

type ReportRepo struct {
	db *sql.DB
}

func NewReportRepo(db *sql.DB) *ReportRepo {
	return &ReportRepo{db: db}
}

// getAmountsByGLType is the shared query for both actuals and budget tables,
// filtered to a single GL account type, period, and department.
func (rr *ReportRepo) getActualAmountsByGLType(
	ctx context.Context,
	accountType model.GLAccountType,
	fiscalYear, fiscalPeriod int,
	deptCode string,
) ([]model.GLAmountRow, error) {
	const q = `
        SELECT  g.code,
                g.description,
                SUM(a.amount),
                a.currency
        FROM    actuals a
        JOIN    gl_accounts  g ON g.id = a.gl_account_id
        JOIN    departments  d ON d.id = a.department_id
        WHERE   g.type          = ?
        AND     g.active        = 1
        AND     a.fiscal_year   = ?
        AND     a.fiscal_period = ?
        AND     d.code          = ?
        GROUP BY g.id, a.currency
        ORDER BY g.code`

	rows, err := rr.db.QueryContext(ctx, q,
		string(accountType), fiscalYear, fiscalPeriod, deptCode)
	if err != nil {
		return nil, fmt.Errorf("getActualAmountsByGLType(%s): %w", accountType, err)
	}
	defer rows.Close()

	var out []model.GLAmountRow
	for rows.Next() {
		var r model.GLAmountRow
		if err := rows.Scan(&r.GLCode, &r.Description, &r.Amount, &r.Currency); err != nil {
			return nil, fmt.Errorf("getActualAmountsByGLType(%s): scan: %w", accountType, err)
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

func (rr *ReportRepo) getBudgetAmountsByGLType(
	ctx context.Context,
	accountType model.GLAccountType,
	fiscalYear, fiscalPeriod int,
	deptCode string,
	version model.BudgetVersion,
) ([]model.RevenueAmounts, error) {
	const q = `
        SELECT  g.code,
                g.description,
                SUM(b.amount),
                b.currency
        FROM    budgets b
        JOIN    gl_accounts  g ON g.id = b.gl_account_id
        JOIN    departments  d ON d.id = b.department_id
        WHERE   g.type          = ?
        AND     g.active        = 1
        AND     b.fiscal_year   = ?
        AND     b.fiscal_period = ?
        AND     d.code          = ?
        AND     b.version       = ?
        GROUP BY g.id, b.currency
        ORDER BY g.code`

	rows, err := rr.db.QueryContext(ctx, q,
		string(accountType), fiscalYear, fiscalPeriod, deptCode, string(version))
	if err != nil {
		return nil, fmt.Errorf("getBudgetAmountsByGLType(%s): %w", accountType, err)
	}
	defer rows.Close()

	var out []model.RevenueAmounts
	for rows.Next() {
		var r model.RevenueAmounts
		if err := rows.Scan(&r.GLCode, &r.Description, &r.Amount, &r.Currency); err != nil {
			return nil, fmt.Errorf("getBudgetAmountsByGLType(%s): scan: %w", accountType, err)
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// ── Public surface ────────────────────────────────────────────────────────────

func (rr *ReportRepo) GetGLRevenueActuals(ctx context.Context, fiscalYear, fiscalPeriod int, deptCode string) ([]model.GLAmountRow, error) {
	return rr.getActualAmountsByGLType(ctx, model.GLRevenue, fiscalYear, fiscalPeriod, deptCode)
}

func (rr *ReportRepo) GetGLRevenueBudget(ctx context.Context, fiscalYear, fiscalPeriod int, deptCode string, version model.BudgetVersion) ([]model.RevenueAmounts, error) {
	return rr.getBudgetAmountsByGLType(ctx, model.GLRevenue, fiscalYear, fiscalPeriod, deptCode, version)
}

// Same pair for the other types — all delegate to the shared private methods.

func (rr *ReportRepo) GetGLCOGSActuals(ctx context.Context, fiscalYear, fiscalPeriod int, deptCode string) ([]model.GLAmountRow, error) {
	return rr.getActualAmountsByGLType(ctx, model.GLCOGS, fiscalYear, fiscalPeriod, deptCode)
}

func (rr *ReportRepo) GetGLCOGSBudget(ctx context.Context, fiscalYear, fiscalPeriod int, deptCode string, version model.BudgetVersion) ([]model.RevenueAmounts, error) {
	return rr.getBudgetAmountsByGLType(ctx, model.GLCOGS, fiscalYear, fiscalPeriod, deptCode, version)
}

func (rr *ReportRepo) GetGLOpexActuals(ctx context.Context, fiscalYear, fiscalPeriod int, deptCode string) ([]model.GLAmountRow, error) {
	return rr.getActualAmountsByGLType(ctx, model.GLOpex, fiscalYear, fiscalPeriod, deptCode)
}

func (rr *ReportRepo) GetGLOpexBudget(ctx context.Context, fiscalYear, fiscalPeriod int, deptCode string, version model.BudgetVersion) ([]model.RevenueAmounts, error) {
	return rr.getBudgetAmountsByGLType(ctx, model.GLOpex, fiscalYear, fiscalPeriod, deptCode, version)
}

func (rr *ReportRepo) GetGLCapexActuals(ctx context.Context, fiscalYear, fiscalPeriod int, deptCode string) ([]model.GLAmountRow, error) {
	return rr.getActualAmountsByGLType(ctx, model.GLCapex, fiscalYear, fiscalPeriod, deptCode)
}

func (rr *ReportRepo) GetGLCapexBudget(ctx context.Context, fiscalYear, fiscalPeriod int, deptCode string, version model.BudgetVersion) ([]model.RevenueAmounts, error) {
	return rr.getBudgetAmountsByGLType(ctx, model.GLCapex, fiscalYear, fiscalPeriod, deptCode, version)
}

func (rr *ReportRepo) GetGLHeadcountActuals(ctx context.Context, fiscalYear, fiscalPeriod int, deptCode string) ([]model.GLAmountRow, error) {
	return rr.getActualAmountsByGLType(ctx, model.GLHeadcount, fiscalYear, fiscalPeriod, deptCode)
}

func (rr *ReportRepo) GetGLHeadcountBudget(ctx context.Context, fiscalYear, fiscalPeriod int, deptCode string, version model.BudgetVersion) ([]model.RevenueAmounts, error) {
	return rr.getBudgetAmountsByGLType(ctx, model.GLHeadcount, fiscalYear, fiscalPeriod, deptCode, version)
}