<?php
declare(strict_types=1);

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

class CashFlow
{
    private PDO $db;

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

    /**
     * 💧 Generate Cash Flow Statement
     * 
     * @param string|null $start Start date filter
     * @param string|null $end   End date filter
     * @return array
     */
    public function getCashFlow(?string $start = null, ?string $end = null): array
    {
        $conditions = '';
        $params = [];

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

        // 🧾 Cash movement from journal entries linked to Cash/Bank accounts
        $sql = "
            SELECT 
                j.journal_date,
                a.account_name,
                SUM(l.debit - l.credit) AS net_amount
            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 = 'Asset'
              AND (a.account_name LIKE '%Cash%' OR a.account_name LIKE '%Bank%')
              {$conditions}
            GROUP BY j.journal_date, a.account_name
            ORDER BY j.journal_date ASC
        ";

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

        // 🧮 Compute totals
        $totalInflow  = 0.00;
        $totalOutflow = 0.00;

        foreach ($rows as $r) {
            $amount = (float) $r['net_amount'];
            if ($amount > 0) {
                $totalInflow += $amount;
            } else {
                $totalOutflow += abs($amount);
            }
        }

        $netFlow = $totalInflow - $totalOutflow;

        return [
            'rows'          => $rows,
            'total_inflow'  => $totalInflow,
            'total_outflow' => $totalOutflow,
            'net_flow'      => $netFlow,
        ];
    }
}
