<?php
declare(strict_types=1);

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

class ProfitLoss
{
    private PDO $db;

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

    /**
     * 📊 Generate Profit & Loss Statement
     * 
     * @param string|null $start Start date (optional)
     * @param string|null $end   End date (optional)
     * @return array
     */
    public function getProfitLoss(?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 ('Income', 'Expense')
              {$conditions}
            GROUP BY a.account_type, a.account_code, a.account_name
            ORDER BY a.account_type, a.account_code
        ";

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

        // 🧮 Totals by category
        $totalIncome = 0.00;
        $totalExpense = 0.00;

        foreach ($rows as $row) {
            $income  = ($row['account_type'] === 'Income')  ? ((float)$row['total_credit'] - (float)$row['total_debit']) : 0;
            $expense = ($row['account_type'] === 'Expense') ? ((float)$row['total_debit'] - (float)$row['total_credit']) : 0;

            $totalIncome  += $income;
            $totalExpense += $expense;
        }

        $netProfit = $totalIncome - $totalExpense;

        return [
            'rows'         => $rows,
            'total_income' => $totalIncome,
            'total_expense'=> $totalExpense,
            'net_profit'   => $netProfit
        ];
    }
}
