goodie/addons/account/models/account_reports.go

package models

import (
	"fmt"

	"odoo-go/pkg/orm"
)

// initAccountTaxReport registers accounting report models.
// Mirrors: odoo/addons/account_reports/models/account_report.py
func initAccountTaxReport() {
	m := orm.NewModel("account.report", orm.ModelOpts{
		Description: "Accounting Report",
		Order:       "sequence, id",
	})
	m.AddFields(
		orm.Char("name", orm.FieldOpts{String: "Name", Required: true, Translate: true}),
		orm.Many2one("country_id", "res.country", orm.FieldOpts{String: "Country"}),
		orm.Selection("report_type", []orm.SelectionItem{
			{Value: "tax_report", Label: "Tax Report"},
			{Value: "general_ledger", Label: "General Ledger"},
			{Value: "partner_ledger", Label: "Partner Ledger"},
			{Value: "aged_receivable", Label: "Aged Receivable"},
			{Value: "aged_payable", Label: "Aged Payable"},
			{Value: "balance_sheet", Label: "Balance Sheet"},
			{Value: "profit_loss", Label: "Profit and Loss"},
			{Value: "trial_balance", Label: "Trial Balance"},
		}, orm.FieldOpts{String: "Type"}),
		orm.Integer("sequence", orm.FieldOpts{String: "Sequence", Default: 10}),
		orm.One2many("line_ids", "account.report.line", "report_id", orm.FieldOpts{String: "Lines"}),
		orm.Boolean("active", orm.FieldOpts{String: "Active", Default: true}),
	)

	// Generate report data
	// Mirrors: odoo/addons/account_reports/models/account_report.py AccountReport._get_report_data()
	m.RegisterMethod("get_report_data", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		env := rs.Env()
		reportID := rs.IDs()[0]

		var reportType string
		err := env.Tx().QueryRow(env.Ctx(), `SELECT report_type FROM account_report WHERE id = $1`, reportID).Scan(&reportType)
		if err != nil {
			return nil, fmt.Errorf("account: read report type: %w", err)
		}

		switch reportType {
		case "trial_balance":
			return generateTrialBalance(env)
		case "balance_sheet":
			return generateBalanceSheet(env)
		case "profit_loss":
			return generateProfitLoss(env)
		case "aged_receivable":
			return generateAgedReport(env, "asset_receivable")
		case "aged_payable":
			return generateAgedReport(env, "liability_payable")
		case "general_ledger":
			return generateGeneralLedger(env)
		case "tax_report":
			return generateTaxReport(env)
		default:
			return map[string]interface{}{"lines": []interface{}{}}, nil
		}
	})
}

// initAccountReportLine registers the report line model.
// Mirrors: odoo/addons/account_reports/models/account_report.py AccountReportLine
func initAccountReportLine() {
	orm.NewModel("account.report.line", orm.ModelOpts{
		Description: "Report Line",
		Order:       "sequence, id",
	}).AddFields(
		orm.Many2one("report_id", "account.report", orm.FieldOpts{String: "Report", Required: true, OnDelete: orm.OnDeleteCascade}),
		orm.Char("name", orm.FieldOpts{String: "Name", Required: true}),
		orm.Char("code", orm.FieldOpts{String: "Code"}),
		orm.Char("formula", orm.FieldOpts{String: "Formula"}),
		orm.Integer("sequence", orm.FieldOpts{String: "Sequence"}),
		orm.Many2one("parent_id", "account.report.line", orm.FieldOpts{String: "Parent"}),
		orm.Integer("level", orm.FieldOpts{String: "Level"}),
	)
}

// -- Report generation functions --

// reportOpts holds optional date/state filters for report generation.
type reportOpts struct {
	DateFrom   string // YYYY-MM-DD, empty = no lower bound
	DateTo     string // YYYY-MM-DD, empty = no upper bound
	TargetMove string // "posted" or "all"
}

// reportStateFilter returns the SQL WHERE clause fragment for move state filtering.
func reportStateFilter(opts reportOpts) string {
	if opts.TargetMove == "all" {
		return "(m.state IS NOT NULL OR m.id IS NULL)"
	}
	return "(m.state = 'posted' OR m.id IS NULL)"
}

// reportDateFilter returns SQL WHERE clause fragment for date filtering.
func reportDateFilter(opts reportOpts) string {
	clause := ""
	if opts.DateFrom != "" {
		clause += " AND m.date >= '" + opts.DateFrom + "'"
	}
	if opts.DateTo != "" {
		clause += " AND m.date <= '" + opts.DateTo + "'"
	}
	return clause
}

// generateTrialBalance produces a trial balance report.
// Mirrors: odoo/addons/account_reports/models/account_trial_balance_report.py
func generateTrialBalance(env *orm.Environment, opts ...reportOpts) (interface{}, error) {
	opt := reportOpts{TargetMove: "posted"}
	if len(opts) > 0 {
		opt = opts[0]
	}
	rows, err := env.Tx().Query(env.Ctx(), fmt.Sprintf(`
		SELECT a.code, a.name, a.account_type,
			COALESCE(SUM(l.debit), 0) as total_debit,
			COALESCE(SUM(l.credit), 0) as total_credit,
			COALESCE(SUM(l.balance), 0) as balance
		FROM account_account a
		LEFT JOIN account_move_line l ON l.account_id = a.id
		LEFT JOIN account_move m ON m.id = l.move_id
		WHERE %s %s
		GROUP BY a.id, a.code, a.name, a.account_type
		HAVING COALESCE(SUM(l.debit), 0) != 0 OR COALESCE(SUM(l.credit), 0) != 0
		ORDER BY a.code`, reportStateFilter(opt), reportDateFilter(opt)))
	if err != nil {
		return nil, fmt.Errorf("account: trial balance query: %w", err)
	}
	defer rows.Close()

	var lines []map[string]interface{}
	var totalDebit, totalCredit float64
	for rows.Next() {
		var code, name, accType string
		var debit, credit, balance float64
		if err := rows.Scan(&code, &name, &accType, &debit, &credit, &balance); err != nil {
			return nil, fmt.Errorf("account: trial balance scan: %w", err)
		}
		lines = append(lines, map[string]interface{}{
			"code": code, "name": name, "account_type": accType,
			"debit": debit, "credit": credit, "balance": balance,
		})
		totalDebit += debit
		totalCredit += credit
	}
	lines = append(lines, map[string]interface{}{
		"code": "", "name": "TOTAL", "account_type": "",
		"debit": totalDebit, "credit": totalCredit, "balance": totalDebit - totalCredit,
	})
	return map[string]interface{}{"lines": lines}, nil
}

// generateBalanceSheet produces assets vs liabilities+equity.
// Mirrors: odoo/addons/account_reports/models/account_balance_sheet.py
func generateBalanceSheet(env *orm.Environment, opts ...reportOpts) (interface{}, error) {
	opt := reportOpts{TargetMove: "posted"}
	if len(opts) > 0 {
		opt = opts[0]
	}
	rows, err := env.Tx().Query(env.Ctx(), fmt.Sprintf(`
		SELECT
			CASE
				WHEN a.account_type LIKE 'asset%%' THEN 'Assets'
				WHEN a.account_type LIKE 'liability%%' THEN 'Liabilities'
				WHEN a.account_type LIKE 'equity%%' THEN 'Equity'
				ELSE 'Other'
			END as section,
			a.code, a.name,
			COALESCE(SUM(l.balance), 0) as balance
		FROM account_account a
		LEFT JOIN account_move_line l ON l.account_id = a.id
		LEFT JOIN account_move m ON m.id = l.move_id
		WHERE %s %s
		  AND (a.account_type LIKE 'asset%%' OR a.account_type LIKE 'liability%%' OR a.account_type LIKE 'equity%%')
		GROUP BY a.id, a.code, a.name, a.account_type
		HAVING COALESCE(SUM(l.balance), 0) != 0
		ORDER BY a.code`, reportStateFilter(opt), reportDateFilter(opt)))
	if err != nil {
		return nil, fmt.Errorf("account: balance sheet query: %w", err)
	}
	defer rows.Close()

	var lines []map[string]interface{}
	for rows.Next() {
		var section, code, name string
		var balance float64
		if err := rows.Scan(&section, &code, &name, &balance); err != nil {
			return nil, fmt.Errorf("account: balance sheet scan: %w", err)
		}
		lines = append(lines, map[string]interface{}{
			"section": section, "code": code, "name": name, "balance": balance,
		})
	}
	return map[string]interface{}{"lines": lines}, nil
}

// generateProfitLoss produces income vs expenses.
// Mirrors: odoo/addons/account_reports/models/account_profit_loss.py
func generateProfitLoss(env *orm.Environment, opts ...reportOpts) (interface{}, error) {
	opt := reportOpts{TargetMove: "posted"}
	if len(opts) > 0 {
		opt = opts[0]
	}
	rows, err := env.Tx().Query(env.Ctx(), fmt.Sprintf(`
		SELECT
			CASE
				WHEN a.account_type LIKE 'income%%' THEN 'Income'
				WHEN a.account_type LIKE 'expense%%' THEN 'Expenses'
				ELSE 'Other'
			END as section,
			a.code, a.name,
			COALESCE(SUM(l.balance), 0) as balance
		FROM account_account a
		LEFT JOIN account_move_line l ON l.account_id = a.id
		LEFT JOIN account_move m ON m.id = l.move_id
		WHERE %s %s
		  AND (a.account_type LIKE 'income%%' OR a.account_type LIKE 'expense%%')
		GROUP BY a.id, a.code, a.name, a.account_type
		HAVING COALESCE(SUM(l.balance), 0) != 0
		ORDER BY a.code`, reportStateFilter(opt), reportDateFilter(opt)))
	if err != nil {
		return nil, fmt.Errorf("account: profit loss query: %w", err)
	}
	defer rows.Close()

	var lines []map[string]interface{}
	var totalIncome, totalExpense float64
	for rows.Next() {
		var section, code, name string
		var balance float64
		if err := rows.Scan(&section, &code, &name, &balance); err != nil {
			return nil, fmt.Errorf("account: profit loss scan: %w", err)
		}
		lines = append(lines, map[string]interface{}{
			"section": section, "code": code, "name": name, "balance": balance,
		})
		if section == "Income" {
			totalIncome += balance
		}
		if section == "Expenses" {
			totalExpense += balance
		}
	}
	// income is negative (credit), expenses positive (debit)
	profit := totalIncome + totalExpense
	lines = append(lines, map[string]interface{}{
		"section": "Result", "code": "", "name": "Net Profit/Loss", "balance": -profit,
	})
	return map[string]interface{}{"lines": lines}, nil
}

// generateAgedReport produces aged receivable/payable.
// Mirrors: odoo/addons/account_reports/models/account_aged_partner_balance.py
func generateAgedReport(env *orm.Environment, accountType string) (interface{}, error) {
	rows, err := env.Tx().Query(env.Ctx(), `
		SELECT COALESCE(p.name, 'Unknown') as partner,
			COALESCE(SUM(CASE WHEN m.date >= CURRENT_DATE - 30 THEN l.amount_residual ELSE 0 END), 0) as current_amount,
			COALESCE(SUM(CASE WHEN m.date < CURRENT_DATE - 30 AND m.date >= CURRENT_DATE - 60 THEN l.amount_residual ELSE 0 END), 0) as days_30,
			COALESCE(SUM(CASE WHEN m.date < CURRENT_DATE - 60 AND m.date >= CURRENT_DATE - 90 THEN l.amount_residual ELSE 0 END), 0) as days_60,
			COALESCE(SUM(CASE WHEN m.date < CURRENT_DATE - 90 THEN l.amount_residual ELSE 0 END), 0) as days_90_plus,
			COALESCE(SUM(l.amount_residual), 0) as total
		FROM account_move_line l
		JOIN account_move m ON m.id = l.move_id AND m.state = 'posted'
		JOIN account_account a ON a.id = l.account_id AND a.account_type = $1
		LEFT JOIN res_partner p ON p.id = l.partner_id
		WHERE l.amount_residual != 0
		GROUP BY p.id, p.name
		ORDER BY total DESC`, accountType)
	if err != nil {
		return nil, fmt.Errorf("account: aged report query: %w", err)
	}
	defer rows.Close()

	var lines []map[string]interface{}
	for rows.Next() {
		var partner string
		var current, d30, d60, d90, total float64
		if err := rows.Scan(&partner, &current, &d30, &d60, &d90, &total); err != nil {
			return nil, fmt.Errorf("account: aged report scan: %w", err)
		}
		lines = append(lines, map[string]interface{}{
			"partner": partner, "current": current, "1-30": d30,
			"31-60": d60, "61-90+": d90, "total": total,
		})
	}
	return map[string]interface{}{"lines": lines}, nil
}

// generateGeneralLedger produces detailed journal entries per account.
// Mirrors: odoo/addons/account_reports/models/account_general_ledger.py
func generateGeneralLedger(env *orm.Environment) (interface{}, error) {
	rows, err := env.Tx().Query(env.Ctx(), `
		SELECT a.code, a.name, m.name as move_name, m.date, l.name as label,
			l.debit, l.credit, l.balance
		FROM account_move_line l
		JOIN account_move m ON m.id = l.move_id AND m.state = 'posted'
		JOIN account_account a ON a.id = l.account_id
		ORDER BY a.code, m.date, l.id
		LIMIT 1000`)
	if err != nil {
		return nil, fmt.Errorf("account: general ledger query: %w", err)
	}
	defer rows.Close()

	var lines []map[string]interface{}
	for rows.Next() {
		var code, name, moveName, label string
		var date interface{}
		var debit, credit, balance float64
		if err := rows.Scan(&code, &name, &moveName, &date, &label, &debit, &credit, &balance); err != nil {
			return nil, fmt.Errorf("account: general ledger scan: %w", err)
		}
		lines = append(lines, map[string]interface{}{
			"account_code": code, "account_name": name, "move": moveName,
			"date": date, "label": label, "debit": debit, "credit": credit, "balance": balance,
		})
	}
	return map[string]interface{}{"lines": lines}, nil
}

// generateTaxReport produces a tax report grouped by tax name and rate.
// Mirrors: odoo/addons/account_reports/models/account_tax_report.py
// Aggregates tax amounts from posted move lines with display_type='tax'.
func generateTaxReport(env *orm.Environment) (interface{}, error) {
	rows, err := env.Tx().Query(env.Ctx(), `
		SELECT COALESCE(t.name, 'Undefined Tax'),
			COALESCE(t.amount, 0) AS tax_rate,
			COALESCE(SUM(ABS(l.balance::float8)), 0) AS tax_amount,
			COALESCE(SUM(ABS(l.tax_base_amount::float8)), 0) AS base_amount,
			COUNT(*) AS line_count
		FROM account_move_line l
		JOIN account_move m ON m.id = l.move_id AND m.state = 'posted'
		LEFT JOIN account_tax t ON t.id = l.tax_line_id
		WHERE l.display_type = 'tax'
		GROUP BY t.name, t.amount
		ORDER BY t.name, t.amount`)
	if err != nil {
		return nil, fmt.Errorf("account: tax report query: %w", err)
	}
	defer rows.Close()

	var lines []map[string]interface{}
	var totalTax, totalBase float64
	for rows.Next() {
		var name string
		var rate, taxAmount, baseAmount float64
		var lineCount int
		if err := rows.Scan(&name, &rate, &taxAmount, &baseAmount, &lineCount); err != nil {
			return nil, fmt.Errorf("account: tax report scan: %w", err)
		}
		lines = append(lines, map[string]interface{}{
			"tax_name":    name,
			"tax_rate":    rate,
			"tax_amount":  taxAmount,
			"base_amount": baseAmount,
			"line_count":  lineCount,
		})
		totalTax += taxAmount
		totalBase += baseAmount
	}

	// Totals row
	lines = append(lines, map[string]interface{}{
		"tax_name":    "TOTAL",
		"tax_rate":    0.0,
		"tax_amount":  totalTax,
		"base_amount": totalBase,
		"line_count":  0,
	})

	return map[string]interface{}{"lines": lines}, nil
}

// ---------------------------------------------------------------------------
// Financial Report Wizard
// Mirrors: odoo/addons/account_reports/wizard/account_report_wizard.py
// ---------------------------------------------------------------------------

// initAccountReportWizard registers a transient model that lets the user
// choose date range, target-move filter and report type, then dispatches
// to the appropriate generateXXX function.
func initAccountReportWizard() {
	m := orm.NewModel("account.report.wizard", orm.ModelOpts{
		Description: "Financial Report Wizard",
		Type:        orm.ModelTransient,
	})

	m.AddFields(
		orm.Date("date_from", orm.FieldOpts{String: "Start Date", Required: true}),
		orm.Date("date_to", orm.FieldOpts{String: "End Date", Required: true}),
		orm.Selection("target_move", []orm.SelectionItem{
			{Value: "all", Label: "All Entries"},
			{Value: "posted", Label: "All Posted Entries"},
		}, orm.FieldOpts{String: "Target Moves", Default: "posted", Required: true}),
		orm.Selection("report_type", []orm.SelectionItem{
			{Value: "trial_balance", Label: "Trial Balance"},
			{Value: "balance_sheet", Label: "Balance Sheet"},
			{Value: "profit_loss", Label: "Profit and Loss"},
			{Value: "aged_receivable", Label: "Aged Receivable"},
			{Value: "aged_payable", Label: "Aged Payable"},
			{Value: "general_ledger", Label: "General Ledger"},
			{Value: "tax_report", Label: "Tax Report"},
		}, orm.FieldOpts{String: "Report Type", Required: true}),
	)

	// action_generate_report dispatches to the matching report generator.
	// Mirrors: odoo/addons/account_reports/wizard/account_report_wizard.py action_generate_report()
	m.RegisterMethod("action_generate_report", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		env := rs.Env()
		data, err := rs.Read([]string{"date_from", "date_to", "target_move", "report_type"})
		if err != nil || len(data) == 0 {
			return nil, fmt.Errorf("account: cannot read report wizard data")
		}
		wiz := data[0]

		reportType, _ := wiz["report_type"].(string)
		dateFrom, _ := wiz["date_from"].(string)
		dateTo, _ := wiz["date_to"].(string)
		targetMove, _ := wiz["target_move"].(string)
		if targetMove == "" {
			targetMove = "posted"
		}
		opt := reportOpts{DateFrom: dateFrom, DateTo: dateTo, TargetMove: targetMove}

		switch reportType {
		case "trial_balance":
			return generateTrialBalance(env, opt)
		case "balance_sheet":
			return generateBalanceSheet(env, opt)
		case "profit_loss":
			return generateProfitLoss(env, opt)
		case "aged_receivable":
			return generateAgedReport(env, "asset_receivable")
		case "aged_payable":
			return generateAgedReport(env, "liability_payable")
		case "general_ledger":
			return generateGeneralLedger(env)
		case "tax_report":
			return generateTaxReport(env)
		default:
			return nil, fmt.Errorf("account: unknown report type %q", reportType)
		}
	})
}