<?php
declare(strict_types=1);

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

class TrialBalance
{
    private PDO $db;

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

    /**
     * 📘 Get Trial Balance for all accounts
     * 
     * @param string|null $start Start date filter
     * @param string|null $end   End date filter
     * @return array
     */
    public function getTrialBalance(?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_code,
                a.account_name,
                a.account_type,
                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' {$conditions}
            GROUP BY a.account_code, a.account_name, a.account_type
            ORDER BY a.account_type, a.account_code
        ";

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

        // Compute total debit & credit for footer
        $totals = [
            'debit'  => 0.00,
            'credit' => 0.00,
        ];

        foreach ($rows as $r) {
            $totals['debit']  += (float) $r['total_debit'];
            $totals['credit'] += (float) $r['total_credit'];
        }

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