<?php

class FinanceTransaction extends Model
{
    protected string $table = 'finance_transactions';

    public function listWithFilters(string $accountId = '', string $category = '', string $dateFrom = '', string $dateTo = ''): array
    {
        $sql = "SELECT ft.*, fa.account_name
                FROM finance_transactions ft
                JOIN finance_accounts fa ON fa.id = ft.finance_account_id
                WHERE 1=1";
        $params = [];

        if ($accountId !== '' && $accountId !== 'ALL') {
            $sql .= " AND ft.finance_account_id = :account_id";
            $params['account_id'] = $accountId;
        }
        if ($category !== '' && $category !== 'ALL') {
            $sql .= " AND ft.category = :category";
            $params['category'] = $category;
        }
        if ($dateFrom !== '') {
            $sql .= " AND DATE(ft.created_at) >= :date_from";
            $params['date_from'] = $dateFrom;
        }
        if ($dateTo !== '') {
            $sql .= " AND DATE(ft.created_at) <= :date_to";
            $params['date_to'] = $dateTo;
        }
        $sql .= " ORDER BY ft.id DESC LIMIT 200";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function monthSummary(): array
    {
        $stmt = $this->db->query(
            "SELECT
                COALESCE(SUM(CASE WHEN transaction_type='CREDIT' AND category='SALES' AND MONTH(created_at)=MONTH(CURDATE()) AND YEAR(created_at)=YEAR(CURDATE()) THEN amount ELSE 0 END),0) as month_income,
                COALESCE(SUM(CASE WHEN transaction_type='DEBIT' AND MONTH(created_at)=MONTH(CURDATE()) AND YEAR(created_at)=YEAR(CURDATE()) THEN amount ELSE 0 END),0) as month_expense,
                COALESCE(SUM(CASE WHEN transaction_type='CREDIT' AND category='SALES' AND MONTH(created_at)=MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND YEAR(created_at)=YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) THEN amount ELSE 0 END),0) as last_month_income,
                COALESCE(SUM(CASE WHEN transaction_type='DEBIT' AND MONTH(created_at)=MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND YEAR(created_at)=YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) THEN amount ELSE 0 END),0) as last_month_expense
             FROM finance_transactions"
        );
        return $stmt->fetch();
    }

    public function cashFlowTrend(int $days = 7): array
    {
        $stmt = $this->db->prepare(
            "SELECT DATE(created_at) as d,
                COALESCE(SUM(CASE WHEN transaction_type='CREDIT' THEN amount ELSE 0 END),0) as income,
                COALESCE(SUM(CASE WHEN transaction_type='DEBIT' THEN amount ELSE 0 END),0) as expense
             FROM finance_transactions
             WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL :days DAY)
             GROUP BY DATE(created_at)"
        );
        $stmt->execute(['days' => $days - 1]);
        $map = [];
        foreach ($stmt->fetchAll() as $row) {
            $map[$row['d']] = ['income' => (float) $row['income'], 'expense' => (float) $row['expense']];
        }
        $labels = []; $incomeData = []; $expenseData = [];
        for ($i = $days - 1; $i >= 0; $i--) {
            $d = date('Y-m-d', strtotime("-{$i} days"));
            $labels[] = date('D', strtotime($d));
            $incomeData[] = $map[$d]['income'] ?? 0;
            $expenseData[] = $map[$d]['expense'] ?? 0;
        }
        return ['labels' => $labels, 'income' => $incomeData, 'expense' => $expenseData];
    }
}
