<?php
declare(strict_types=1);

require_once __DIR__ . '/../../app/Config/database.php';

/**
 * Report Model
 * ---------------------------------------------------------
 * Provides financial data aggregation for:
 *  - Trial Balance
 *  - Profit & Loss Statement
 *  - Balance Sheet
 *  - Cash Flow Statement
 */
class Report
{
    private PDO $db;

    public function __construct()
    {
        $this->db = Database::getConnection();
    }

    /**
     * TRIAL BALANCE
     * ---------------------------------------------------------
     * Returns all accounts with summed debit & credit totals
     * (only from posted journals).
     */
    public function getTrialBalance(): array
    {
        $sql = "
            SELECT 
                c.account_code,
                c.account_name,
                c.account_type,
                COALESCE(SUM(l.debit), 0)  AS total_debit,
                COALESCE(SUM(l.credit), 0) AS total_credit
            FROM journal_lines l
            INNER JOIN journal_entries j ON j.id = l.journal_id
            INNER JOIN chart_of_accounts c ON c.account_code = l.account_code
            WHERE j.status = 'posted'
            GROUP BY c.account_code, c.account_name, c.account_type
            ORDER BY c.account_code ASC
        ";

        $stmt = $this->db->query($sql);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * PROFIT & LOSS STATEMENT
     * ---------------------------------------------------------
     * Returns Income & Expense accounts within an optional date range.
     */
    public function getProfitLoss(?string $start = null, ?string $end = null): array
    {
        $conditions = ["j.status = 'posted'", "c.account_type IN ('Income','Expense')"];
        $params = [];

        if ($start && $end) {
            $conditions[] = "j.journal_date BETWEEN ? AND ?";
            $params = [$start, $end];
        }

        $sql = sprintf("
            SELECT 
                c.account_type,
                c.account_code,
                c.account_name,
                COALESCE(SUM(l.debit), 0)  AS total_debit,
                COALESCE(SUM(l.credit), 0) AS total_credit
            FROM journal_lines l
            INNER JOIN journal_entries j ON j.id = l.journal_id
            INNER JOIN chart_of_accounts c ON c.account_code = l.account_code
            WHERE %s
            GROUP BY c.account_type, c.account_code, c.account_name
            ORDER BY c.account_type, c.account_code ASC
        ", implode(' AND ', $conditions));

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * BALANCE SHEET
     * ---------------------------------------------------------
     * Returns Assets, Liabilities, and Equity accounts.
     */
    public function getBalanceSheet(): array
    {
        $sql = "
            SELECT 
                c.account_type,
                c.account_code,
                c.account_name,
                COALESCE(SUM(l.debit), 0)  AS total_debit,
                COALESCE(SUM(l.credit), 0) AS total_credit
            FROM journal_lines l
            INNER JOIN journal_entries j ON j.id = l.journal_id
            INNER JOIN chart_of_accounts c ON c.account_code = l.account_code
            WHERE j.status = 'posted'
              AND c.account_type IN ('Asset', 'Liability', 'Equity')
            GROUP BY c.account_type, c.account_code, c.account_name
            ORDER BY c.account_type, c.account_code ASC
        ";

        $stmt = $this->db->query($sql);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * CASH FLOW STATEMENT
     * ---------------------------------------------------------
     * Categorises inflows/outflows by inferred activity type:
     *  - Operating: Income & Expense
     *  - Investing: Asset
     *  - Financing: Liability & Equity
     */
    public function getCashFlow(?string $start = null, ?string $end = null): array
    {
        $conditions = ["j.status = 'posted'"];
        $params = [];

        if ($start && $end) {
            $conditions[] = "j.journal_date BETWEEN ? AND ?";
            $params = [$start, $end];
        }

        $sql = sprintf("
            SELECT 
                c.account_type,
                c.account_code,
                c.account_name,
                COALESCE(SUM(l.debit), 0)  AS total_debit,
                COALESCE(SUM(l.credit), 0) AS total_credit
            FROM journal_lines l
            INNER JOIN journal_entries j ON j.id = l.journal_id
            INNER JOIN chart_of_accounts c ON c.account_code = l.account_code
            WHERE %s
            GROUP BY c.account_type, c.account_code, c.account_name
            ORDER BY c.account_type, c.account_code ASC
        ", implode(' AND ', $conditions));

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $flows = [
            'Operating' => [],
            'Investing' => [],
            'Financing' => []
        ];

        foreach ($rows as $r) {
            $debit  = (float) $r['total_debit'];
            $credit = (float) $r['total_credit'];
            $net    = $credit - $debit; // inflow (+), outflow (–)

            switch ($r['account_type']) {
                case 'Income':
                case 'Expense':
                    $flows['Operating'][] = $r + ['net' => $net];
                    break;
                case 'Asset':
                    $flows['Investing'][] = $r + ['net' => $net];
                    break;
                case 'Liability':
                case 'Equity':
                    $flows['Financing'][] = $r + ['net' => $net];
                    break;
            }
        }

        return $flows;
    }
}
