package database

import (
	"context"
	"fmt"

	"Engine/internal/model"
)

type BudgetRepo struct {
	db *DB
}

func NewBudgetRepo(db *DB) *BudgetRepo {
	return &BudgetRepo{db: db}
}

func (r *BudgetRepo) Create(ctx context.Context, req model.CreateBudgetRequest) (*model.Budget, error) {
	res, err := r.db.ExecContext(ctx, `
		INSERT INTO budgets
			(fiscal_year, fiscal_period, version, department_id, gl_account_id, amount, currency, notes, created_by)
		VALUES (?,?,?,?,?,?,?,?,?)`,
		req.FiscalYear, req.FiscalPeriod, req.Version,
		req.DepartmentID, req.GLAccountID, req.Amount,
		req.Currency, req.Notes, req.CreatedBy,
	)
	if err != nil {
		return nil, fmt.Errorf("create budget: %w", err)
	}

	id, _ := res.LastInsertId()

	b := &model.Budget{}
	err = r.db.QueryRowContext(ctx, `
		SELECT id, fiscal_year, fiscal_period, version, department_id, gl_account_id,
		       amount, currency, notes, created_by, created_at, updated_at
		FROM budgets WHERE id = ?`, id,
	).Scan(
		&b.ID, &b.FiscalYear, &b.FiscalPeriod, &b.Version,
		&b.DepartmentID, &b.GLAccountID, &b.Amount, &b.Currency,
		&b.Notes, &b.CreatedBy, &b.CreatedAt, &b.UpdatedAt,
	)
	if err != nil {
		return nil, fmt.Errorf("fetch created budget: %w", err)
	}
	return b, nil
}

func (r *BudgetRepo) List(ctx context.Context, fiscalYear, fiscalPeriod int, deptCode string, version model.BudgetVersion) ([]model.BudgetRow, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT g.code, g.description, g.type, g.favour_high, b.amount, b.currency
		FROM budgets b
		JOIN departments d ON d.id = b.department_id
		JOIN gl_accounts g ON g.id = b.gl_account_id
		WHERE b.fiscal_year   = ?
		  AND b.fiscal_period = ?
		  AND (? = '' OR d.code   = ?)
		  AND (? = '' OR b.version = ?)
		ORDER BY g.code`,
		fiscalYear, fiscalPeriod,
		deptCode, deptCode,
		string(version), string(version),
	)
	if err != nil {
		return nil, fmt.Errorf("list budgets: %w", err)
	}
	defer rows.Close()

	var result []model.BudgetRow
	for rows.Next() {
		var row model.BudgetRow
		if err := rows.Scan(&row.GLCode, &row.GLDescription, &row.GLType, &row.FavourHigh, &row.Amount, &row.Currency); err != nil {
			return nil, err
		}
		result = append(result, row)
	}
	return result, rows.Err()
}

func (r *BudgetRepo) Update(ctx context.Context, id int, amount float64, notes, _ string) (*model.Budget, error) {
	_, err := r.db.ExecContext(ctx, `
		UPDATE budgets
		SET amount=?, notes=?, updated_at=CURRENT_TIMESTAMP
		WHERE id=?`,
		amount, notes, id,
	)
	if err != nil {
		return nil, fmt.Errorf("update budget %d: %w", id, err)
	}

	b := &model.Budget{}
	err = r.db.QueryRowContext(ctx, `
		SELECT id, fiscal_year, fiscal_period, version, department_id, gl_account_id,
		       amount, currency, notes, created_by, created_at, updated_at
		FROM budgets WHERE id = ?`, id,
	).Scan(
		&b.ID, &b.FiscalYear, &b.FiscalPeriod, &b.Version,
		&b.DepartmentID, &b.GLAccountID, &b.Amount, &b.Currency,
		&b.Notes, &b.CreatedBy, &b.CreatedAt, &b.UpdatedAt,
	)
	if err != nil {
		return nil, fmt.Errorf("fetch updated budget: %w", err)
	}
	return b, nil
}

func (r *BudgetRepo) Delete(ctx context.Context, id int) error {
	res, err := r.db.ExecContext(ctx, `DELETE FROM budgets WHERE id=?`, id)
	if err != nil {
		return fmt.Errorf("delete budget: %w", err)
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return fmt.Errorf("budget %d not found", id)
	}
	return nil
}