<?php
declare(strict_types=1);

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

class BalanceSheet
{
    private PDO $db;

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

    /**
     * 📊 Generate Balance Sheet
     *
     * @param string|null $start Start date (optional)
     * @param string|null $end   End date (optional)
     * @return array
     */
    public function getBalanceSheet(?string $start = null, ?string $end = null): array
    {
        $conditions = '';
        $params = [];

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

        $sql = "
            SELECT 
                a.account_type,
                a.account_code,
                a.account_name,
                SUM(l.debit) AS total_debit,
                SUM(l.credit) AS total_credit
            FROM journal_lines l
            JOIN chart_of_accounts a ON a.id = l.account_id
            JOIN journal_entries j ON j.id = l.journal_id
            WHERE j.status = 'posted'
              AND a.account_type IN ('Asset', 'Liability', 'Equity')
              {$conditions}
            GROUP BY a.account_type, a.account_code, a.account_name
            ORDER BY FIELD(a.account_type, 'Asset', 'Liability', 'Equity'), a.account_code
        ";

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

        // 🧮 Summaries
        $assets = 0.00;
        $liabilities = 0.00;
        $equity = 0.00;

        foreach ($rows as $row) {
            $debit  = (float)$row['total_debit'];
            $credit = (float)$row['total_credit'];

            switch ($row['account_type']) {
                case 'Asset':
                    $assets += ($debit - $credit);
                    break;
                case 'Liability':
                    $liabilities += ($credit - $debit);
                    break;
                case 'Equity':
                    $equity += ($credit - $debit);
                    break;
            }
        }

        $totals = [
            'assets'       => $assets,
            'liabilities'  => $liabilities,
            'equity'       => $equity,
            'balance_check'=> $assets - ($liabilities + $equity)
        ];

        return [
            'rows'   => $rows,
            'totals' => $totals
        ];
    }
}
