<?php
declare(strict_types=1);

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

class Dashboard
{
    private PDO $db;

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

    /**
     * 📊 Get overall financial summary
     */
    public function getSummary(): array
    {
        // 1️⃣ Calculate totals by account type
        $sql = "
            SELECT 
                SUM(CASE WHEN a.account_type = 'Income'     THEN l.credit - l.debit ELSE 0 END) AS total_income,
                SUM(CASE WHEN a.account_type = 'Expense'    THEN l.debit - l.credit ELSE 0 END) AS total_expense,
                SUM(CASE WHEN a.account_type = 'Asset'      THEN l.debit - l.credit ELSE 0 END) AS total_assets,
                SUM(CASE WHEN a.account_type = 'Liability'  THEN l.credit - l.debit ELSE 0 END) AS total_liabilities
            FROM journal_lines l
            JOIN chart_of_accounts a ON a.id = l.account_id
        ";

        $stmt = $this->db->query($sql);
        $summary = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

        // 2️⃣ Derive additional computed values
        $income       = (float)($summary['total_income'] ?? 0);
        $expense      = (float)($summary['total_expense'] ?? 0);
        $assets       = (float)($summary['total_assets'] ?? 0);
        $liabilities  = (float)($summary['total_liabilities'] ?? 0);
        $net_profit   = $income - $expense;

        // 3️⃣ Compute Cash balance (from COA “Cash and Bank” accounts)
        $cashSql = "
            SELECT COALESCE(SUM(l.debit - l.credit), 0)
            FROM journal_lines l
            JOIN chart_of_accounts a ON a.id = l.account_id
            WHERE a.account_name LIKE '%Cash%' OR a.account_name LIKE '%Bank%'
        ";
        $cash = (float)$this->db->query($cashSql)->fetchColumn();

        // 4️⃣ Return full data set for dashboard cards
        return [
            'income'       => $income,
            'expense'      => $expense,
            'assets'       => $assets,
            'liabilities'  => $liabilities,
            'net_profit'   => $net_profit,
            'cash'         => $cash
        ];
    }

    /**
     * 📈 Monthly Income vs Expense Trend
     */
    public function getMonthlyTrend(int $months = 12): array
    {
        $sql = "
            SELECT 
                DATE_FORMAT(j.journal_date, '%Y-%m') AS month,
                SUM(CASE WHEN a.account_type = 'Income' THEN l.credit - l.debit ELSE 0 END) AS income,
                SUM(CASE WHEN a.account_type = 'Expense' THEN l.debit - l.credit ELSE 0 END) AS expense
            FROM journal_entries j
            JOIN journal_lines l ON j.id = l.journal_id
            JOIN chart_of_accounts a ON a.id = l.account_id
            WHERE j.status = 'posted'
            GROUP BY DATE_FORMAT(j.journal_date, '%Y-%m')
            ORDER BY month DESC
            LIMIT :months
        ";

        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':months', $months, PDO::PARAM_INT);
        $stmt->execute();
        return array_reverse($stmt->fetchAll(PDO::FETCH_ASSOC)); // chronological order
    }

    /**
     * 🧾 Latest 5 Journal Entries
     */
    public function getRecentJournals(): array
    {
        $sql = "
            SELECT j.journal_no, j.journal_date, j.description, j.total_debit, j.total_credit, j.status
            FROM journal_entries j
            ORDER BY j.journal_date DESC
            LIMIT 5
        ";
        $stmt = $this->db->query($sql);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * 💰 Outstanding AR/AP Balances
     */
    public function getArApSummary(): array
    {
        $ar = $this->db->query("
            SELECT COALESCE(SUM(total_amount), 0)
            FROM ar_invoices WHERE payment_status != 'paid'
        ")->fetchColumn();

        $ap = $this->db->query("
            SELECT COALESCE(SUM(total_amount), 0)
            FROM ap_bills WHERE payment_status != 'paid'
        ")->fetchColumn();

        return [
            'accounts_receivable' => (float)$ar,
            'accounts_payable'    => (float)$ap,
        ];
    }
}
