<?php
/**
 * FinancialStatements — Trial Balance, Balance Sheet, Profit & Loss.
 *
 * Pure reads over posted journal_lines + chart_of_accounts. No writes.
 * All calculations use natural-sign convention:
 *   ASSET / EXPENSE  → normal debit balance  (debit - credit)
 *   LIABILITY / EQUITY / REVENUE → normal credit balance (credit - debit)
 *
 * "As at" cutoffs use JE.entry_date <= :asAt so period-end statements are stable.
 * P&L uses BETWEEN from AND to on JE.entry_date.
 */
class FinancialStatements extends Model
{
    /**
     * Trial balance — every non-control COA account with its debit + credit totals
     * and natural-side balance as at $asAt. Non-zero rows only.
     */
    public function trialBalance(string $asAt = ''): array
    {
        $asAt = $asAt ?: date('Y-m-d');
        $db = new DB();
        return $db->mySelect(
            "SELECT coa.c0d, coa.account_code, coa.account_name, coa.account_type, coa.is_bank,
                    IFNULL(SUM(jl.debit), 0)  AS total_debit,
                    IFNULL(SUM(jl.credit), 0) AS total_credit,
                    CASE
                        WHEN coa.account_type IN ('ASSET','EXPENSE')
                            THEN IFNULL(SUM(jl.debit), 0) - IFNULL(SUM(jl.credit), 0)
                        ELSE IFNULL(SUM(jl.credit), 0) - IFNULL(SUM(jl.debit), 0)
                    END AS balance
               FROM chart_of_accounts coa
          LEFT JOIN journal_lines jl    ON jl.account_id = coa.c0d
          LEFT JOIN journal_entries je  ON je.c0d = jl.journal_entry_id
                                       AND je.deleted = 0
                                       AND je.status  = 'POSTED'
                                       AND je.entry_date <= :d
              WHERE coa.is_active = 1
              GROUP BY coa.c0d, coa.account_code, coa.account_name, coa.account_type, coa.is_bank
             HAVING total_debit <> 0 OR total_credit <> 0
              ORDER BY coa.account_code",
            [':d' => $asAt]
        ) ?: [];
    }

    /**
     * Balance sheet as at $asAt. Returns three grouped arrays plus totals:
     *   assets, liabilities, equity_and_reserves (equity + retained earnings)
     * Retained earnings = (revenue - expense) YTD (no year-end close yet).
     */
    public function balanceSheet(string $asAt = ''): array
    {
        $asAt = $asAt ?: date('Y-m-d');
        $tb   = $this->trialBalance($asAt);

        $assets = $liabs = $equity = [];
        $sumA = $sumL = $sumE = $sumR = $sumEx = 0.0;

        foreach ($tb as $row) {
            $bal = (float)$row['balance'];
            switch ($row['account_type']) {
                case 'ASSET':     $assets[] = $row; $sumA  += $bal; break;
                case 'LIABILITY': $liabs[]  = $row; $sumL  += $bal; break;
                case 'EQUITY':    $equity[] = $row; $sumE  += $bal; break;
                case 'REVENUE':   $sumR  += $bal; break;
                case 'EXPENSE':   $sumEx += $bal; break;
            }
        }
        $retained = $sumR - $sumEx;

        return [
            'as_at'                => $asAt,
            'assets'               => $assets,
            'liabilities'          => $liabs,
            'equity'               => $equity,
            'retained_earnings'    => $retained,
            'total_assets'         => $sumA,
            'total_liabilities'    => $sumL,
            'total_equity_capital' => $sumE,
            'total_equity_all'     => $sumE + $retained,
            'total_liab_and_eq'    => $sumL + $sumE + $retained,
            'balances'             => abs($sumA - ($sumL + $sumE + $retained)) < 0.01,
        ];
    }

    /**
     * Profit & Loss for the range [from..to]. Groups by account with subtotals
     * for Revenue, Cost of Sales (COA 5xxx), Operating Expenses (COA 6xxx+),
     * and computes Gross Profit + Net Profit.
     */
    public function profitAndLoss(string $from = '', string $to = ''): array
    {
        $from = $from ?: date('Y-01-01');
        $to   = $to   ?: date('Y-m-d');
        $db = new DB();

        $rows = $db->mySelect(
            "SELECT coa.c0d, coa.account_code, coa.account_name, coa.account_type,
                    IFNULL(SUM(jl.debit), 0)  AS total_debit,
                    IFNULL(SUM(jl.credit), 0) AS total_credit,
                    CASE WHEN coa.account_type = 'REVENUE'
                         THEN IFNULL(SUM(jl.credit), 0) - IFNULL(SUM(jl.debit), 0)
                         ELSE IFNULL(SUM(jl.debit), 0)  - IFNULL(SUM(jl.credit), 0)
                    END AS amount
               FROM chart_of_accounts coa
               JOIN journal_lines jl    ON jl.account_id = coa.c0d
               JOIN journal_entries je  ON je.c0d = jl.journal_entry_id
                                       AND je.deleted = 0
                                       AND je.status  = 'POSTED'
                                       AND je.entry_date BETWEEN :d1 AND :d2
              WHERE coa.is_active = 1
                AND coa.account_type IN ('REVENUE','EXPENSE')
              GROUP BY coa.c0d, coa.account_code, coa.account_name, coa.account_type
             HAVING amount <> 0
              ORDER BY coa.account_code",
            [':d1' => $from, ':d2' => $to]
        ) ?: [];

        $revenue = $cogs = $opex = [];
        $sumR = $sumC = $sumO = 0.0;
        foreach ($rows as $r) {
            $amt = (float)$r['amount'];
            if ($r['account_type'] === 'REVENUE')      { $revenue[] = $r; $sumR += $amt; }
            elseif (strpos((string)$r['account_code'], '5') === 0) { $cogs[] = $r; $sumC += $amt; }
            else                                                    { $opex[] = $r; $sumO += $amt; }
        }
        $gross = $sumR - $sumC;
        $net   = $gross - $sumO;

        return [
            'from'          => $from,
            'to'            => $to,
            'revenue'       => $revenue,
            'cogs'          => $cogs,
            'opex'          => $opex,
            'total_revenue' => $sumR,
            'total_cogs'    => $sumC,
            'total_opex'    => $sumO,
            'gross_profit'  => $gross,
            'net_profit'    => $net,
            'gross_margin'  => $sumR > 0 ? ($gross / $sumR) * 100 : 0,
            'net_margin'    => $sumR > 0 ? ($net   / $sumR) * 100 : 0,
        ];
    }
}
