<?php

declare(strict_types=1);

namespace App\Modules\Budget;

use App\Core\BaseModel;

/**
 * BudgetModel — SQL and business logic for Couple Wedding Budget Planner.
 *
 * @package Inveetaire
 * @ref     RC-017
 */
class BudgetModel extends BaseModel
{
    protected string $table = 'budget_expenses';

    private array $defaultCategories = [
        'Venue', 'Decoration', 'Photography', 'Videography', 'Wedding Organizer',
        'MC', 'Entertainment', 'Wedding Dress', 'Suit', 'Makeup', 'Invitation',
        'Souvenir', 'Catering', 'Wedding Cake', 'Transportation', 'Accommodation',
        'Church', 'Tea Pai', 'Marriage License', 'Honeymoon', 'Miscellaneous'
    ];

    // ─────────────────────────────────────────────────────────────────────────
    // BUDGET SETTINGS (TOTAL BUDGET LIMIT & CURRENCY)
    // ─────────────────────────────────────────────────────────────────────────

    public function getSettings(int $workspaceId): array
    {
        $stmt = $this->query(
            'SELECT * FROM budget_settings WHERE workspace_id = ? LIMIT 1',
            [$workspaceId]
        );
        $settings = $stmt->fetch();
        
        if (!$settings) {
            // Return defaults if not set yet
            return [
                'total_budget' => 0.00,
                'currency' => 'IDR'
            ];
        }
        return $settings;
    }

    public function saveSettings(int $workspaceId, float $totalBudget, string $currency = 'IDR'): bool
    {
        $settings = $this->getSettings($workspaceId);
        
        $db = $this->db();
        $stmt = $db->prepare('SELECT id FROM budget_settings WHERE workspace_id = ?');
        $stmt->execute([$workspaceId]);
        $exists = $stmt->fetch();

        if ($exists) {
            $this->query(
                'UPDATE budget_settings SET total_budget = ?, currency = ?, updated_at = NOW() WHERE workspace_id = ?',
                [$totalBudget, $currency, $workspaceId]
            );
        } else {
            $this->query(
                'INSERT INTO budget_settings (workspace_id, total_budget, currency, created_at) VALUES (?, ?, ?, NOW())',
                [$workspaceId, $totalBudget, $currency]
            );
        }
        return true;
    }

    // ─────────────────────────────────────────────────────────────────────────
    // BUDGET CATEGORIES
    // ─────────────────────────────────────────────────────────────────────────

    public function getCategories(int $workspaceId): array
    {
        $this->ensureDefaultCategoriesExist($workspaceId);
        
        return $this->query(
            'SELECT * FROM budget_categories WHERE workspace_id = ? AND is_deleted = 0 ORDER BY sort_order ASC, id ASC',
            [$workspaceId]
        )->fetchAll();
    }

    public function ensureDefaultCategoriesExist(int $workspaceId): void
    {
        $stmt = $this->query(
            'SELECT COUNT(*) as cnt FROM budget_categories WHERE workspace_id = ?',
            [$workspaceId]
        );
        $res = $stmt->fetch();
        
        if ((int)$res['cnt'] === 0) {
            $db = $this->db();
            $stmtInsert = $db->prepare(
                'INSERT INTO budget_categories (workspace_id, name, sort_order, created_at) VALUES (?, ?, ?, NOW())'
            );
            foreach ($this->defaultCategories as $idx => $name) {
                $stmtInsert->execute([$workspaceId, $name, $idx * 10]);
            }
        }
    }

    public function createCategory(int $workspaceId, string $name): int
    {
        // Get max sort order
        $stmt = $this->query(
            'SELECT MAX(sort_order) as max_sort FROM budget_categories WHERE workspace_id = ? AND is_deleted = 0',
            [$workspaceId]
        );
        $res = $stmt->fetch();
        $nextSort = $res ? ((int)$res['max_sort'] + 10) : 0;

        $db = $this->db();
        $stmt = $db->prepare(
            'INSERT INTO budget_categories (workspace_id, name, sort_order, created_at) VALUES (?, ?, ?, NOW())'
        );
        $stmt->execute([$workspaceId, $name, $nextSort]);
        return (int)$db->lastInsertId();
    }

    public function renameCategory(int $workspaceId, int $id, string $newName): bool
    {
        $this->query(
            'UPDATE budget_categories SET name = ?, updated_at = NOW() WHERE id = ? AND workspace_id = ? AND is_deleted = 0',
            [$newName, $id, $workspaceId]
        );
        return true;
    }

    public function deleteCategory(int $workspaceId, int $id): bool
    {
        $db = $this->db();
        $db->beginTransaction();

        try {
            // Soft delete category
            $this->query(
                'UPDATE budget_categories SET is_deleted = 1, deleted_at = NOW() WHERE id = ? AND workspace_id = ?',
                [$id, $workspaceId]
            );

            // Fetch linked expense IDs to soft delete payments
            $stmt = $this->query(
                'SELECT id FROM budget_expenses WHERE category_id = ? AND workspace_id = ? AND is_deleted = 0',
                [$id, $workspaceId]
            );
            $expenseIds = array_column($stmt->fetchAll(), 'id');

            if (!empty($expenseIds)) {
                // Soft delete payments
                $placeholders = implode(',', array_fill(0, count($expenseIds), '?'));
                $this->query(
                    "UPDATE budget_payments SET is_deleted = 1 WHERE expense_id IN ($placeholders) AND workspace_id = ?",
                    array_merge($expenseIds, [$workspaceId])
                );

                // Soft delete expenses
                $this->query(
                    "UPDATE budget_expenses SET is_deleted = 1, deleted_at = NOW() WHERE category_id = ? AND workspace_id = ?",
                    [$id, $workspaceId]
                );
            }

            $db->commit();
            return true;
        } catch (\Exception $e) {
            $db->rollBack();
            throw $e;
        }
    }

    public function reorderCategories(int $workspaceId, array $categoryIds): bool
    {
        $db = $this->db();
        $db->beginTransaction();
        try {
            $stmt = $db->prepare('UPDATE budget_categories SET sort_order = ?, updated_at = NOW() WHERE id = ? AND workspace_id = ?');
            foreach ($categoryIds as $index => $id) {
                $stmt->execute([$index * 10, (int)$id, $workspaceId]);
            }
            $db->commit();
            return true;
        } catch (\Exception $e) {
            $db->rollBack();
            throw $e;
        }
    }

    // ─────────────────────────────────────────────────────────────────────────
    // EXPENSES CRUD & LISTING
    // ─────────────────────────────────────────────────────────────────────────

    public function getExpenses(int $workspaceId, array $filters = [], int $limit = 100, int $offset = 0): array
    {
        $sql = 'SELECT e.*, c.name as category_name 
                FROM budget_expenses e
                JOIN budget_categories c ON c.id = e.category_id
                WHERE e.workspace_id = :workspace_id AND e.is_deleted = 0 AND c.is_deleted = 0';
        
        $params = ['workspace_id' => $workspaceId];

        if (!empty($filters['category_id'])) {
            $sql .= ' AND e.category_id = :category_id';
            $params['category_id'] = (int) $filters['category_id'];
        }

        if (!empty($filters['status'])) {
            $sql .= ' AND e.status = :status';
            $params['status'] = trim($filters['status']);
        }

        if (!empty($filters['search'])) {
            $sql .= ' AND (e.name LIKE :search OR e.vendor_name LIKE :search OR e.notes LIKE :search)';
            $params['search'] = '%' . trim($filters['search']) . '%';
        }

        $sql .= ' ORDER BY c.sort_order ASC, e.id DESC LIMIT :limit OFFSET :offset';

        $db = $this->db();
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':workspace_id', $workspaceId, \PDO::PARAM_INT);
        if (isset($params['category_id'])) {
            $stmt->bindValue(':category_id', $params['category_id'], \PDO::PARAM_INT);
        }
        if (isset($params['status'])) {
            $stmt->bindValue(':status', $params['status'], \PDO::PARAM_STR);
        }
        if (isset($params['search'])) {
            $stmt->bindValue(':search', $params['search'], \PDO::PARAM_STR);
        }
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();
        
        return $stmt->fetchAll();
    }

    public function getExpensesCount(int $workspaceId, array $filters = []): int
    {
        $sql = 'SELECT COUNT(*) as total 
                FROM budget_expenses e
                JOIN budget_categories c ON c.id = e.category_id
                WHERE e.workspace_id = :workspace_id AND e.is_deleted = 0 AND c.is_deleted = 0';
        
        $params = ['workspace_id' => $workspaceId];

        if (!empty($filters['category_id'])) {
            $sql .= ' AND e.category_id = :category_id';
            $params['category_id'] = (int) $filters['category_id'];
        }

        if (!empty($filters['status'])) {
            $sql .= ' AND e.status = :status';
            $params['status'] = trim($filters['status']);
        }

        if (!empty($filters['search'])) {
            $sql .= ' AND (e.name LIKE :search OR e.vendor_name LIKE :search)';
            $params['search'] = '%' . trim($filters['search']) . '%';
        }

        $db = $this->db();
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':workspace_id', $workspaceId, \PDO::PARAM_INT);
        if (isset($params['category_id'])) {
            $stmt->bindValue(':category_id', $params['category_id'], \PDO::PARAM_INT);
        }
        if (isset($params['status'])) {
            $stmt->bindValue(':status', $params['status'], \PDO::PARAM_STR);
        }
        if (isset($params['search'])) {
            $stmt->bindValue(':search', $params['search'], \PDO::PARAM_STR);
        }
        $stmt->execute();
        $res = $stmt->fetch();
        return (int) ($res['total'] ?? 0);
    }

    public function getExpense(int $workspaceId, int $id): ?array
    {
        $stmt = $this->query(
            'SELECT e.*, c.name as category_name,
                    v.contact_person as vendor_pic, v.phone as vendor_phone, v.whatsapp as vendor_whatsapp
             FROM budget_expenses e
             JOIN budget_categories c ON c.id = e.category_id
             LEFT JOIN vendors v ON v.budget_expense_id = e.id AND v.is_deleted = 0
             WHERE e.id = ? AND e.workspace_id = ? AND e.is_deleted = 0 AND c.is_deleted = 0 LIMIT 1',
            [$id, $workspaceId]
        );
        $expense = $stmt->fetch();
        return $expense ? $expense : null;
    }

    public function createExpense(int $workspaceId, array $data): int
    {
        $db = $this->db();
        $stmt = $db->prepare(
            'INSERT INTO budget_expenses (
                workspace_id, category_id, name, vendor_name, estimated_cost, actual_cost, 
                status, payment_method, notes, due_date, reminder_enabled, reminder_date, 
                reminder_notes, created_at
            ) VALUES (
                ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW()
            )'
        );

        $stmt->execute([
            $workspaceId,
            (int) $data['category_id'],
            trim($data['name']),
            !empty($data['vendor_name']) ? trim($data['vendor_name']) : null,
            (float) ($data['estimated_cost'] ?? 0.0),
            (float) ($data['actual_cost'] ?? 0.0),
            $data['status'] ?? 'Planned',
            !empty($data['payment_method']) ? trim($data['payment_method']) : null,
            !empty($data['notes']) ? trim($data['notes']) : null,
            !empty($data['due_date']) ? $data['due_date'] : null,
            (int) ($data['reminder_enabled'] ?? 0),
            (!empty($data['reminder_enabled']) && !empty($data['reminder_date'])) ? $data['reminder_date'] : null,
            !empty($data['reminder_notes']) ? trim($data['reminder_notes']) : null
        ]);

        $id = (int)$db->lastInsertId();
        
        // Sync Vendor record
        $this->syncVendorRecord($workspaceId, $id, $data);

        $this->recalculateExpenseState($workspaceId, $id);
        return $id;
    }

    private function syncVendorRecord(int $workspaceId, int $expenseId, array $data): void
    {
        $db = $this->db();
        $vendorName = trim((string)($data['vendor_name'] ?? ''));
        if ($vendorName === '') {
            $stmtDel = $db->prepare('UPDATE vendors SET is_deleted = 1 WHERE budget_expense_id = ? AND workspace_id = ?');
            $stmtDel->execute([$expenseId, $workspaceId]);
            return;
        }

        // Ensure a vendor category exists
        $stmtCat = $db->prepare('SELECT id FROM vendor_categories WHERE workspace_id = ? AND is_deleted = 0 LIMIT 1');
        $stmtCat->execute([$workspaceId]);
        $vCatId = $stmtCat->fetchColumn();
        if (!$vCatId) {
            $stmtInsCat = $db->prepare('INSERT INTO vendor_categories (workspace_id, name, sort_order, created_at) VALUES (?, "Wedding Services", 0, NOW())');
            $stmtInsCat->execute([$workspaceId]);
            $vCatId = $db->lastInsertId();
        }

        $pic = !empty($data['vendor_pic']) ? trim($data['vendor_pic']) : null;
        $phone = !empty($data['vendor_phone']) ? trim($data['vendor_phone']) : null;
        $notes = !empty($data['notes']) ? trim($data['notes']) : null;
        $dealPrice = (float)($data['actual_cost'] ?? 0.0);

        // Check if vendor already exists for this expense
        $stmtCheck = $db->prepare('SELECT id FROM vendors WHERE budget_expense_id = ? AND workspace_id = ? LIMIT 1');
        $stmtCheck->execute([$expenseId, $workspaceId]);
        $vendorId = $stmtCheck->fetchColumn();

        if ($vendorId) {
            $stmtUp = $db->prepare('
                UPDATE vendors 
                SET name = ?, 
                    contact_person = ?, 
                    phone = ?, 
                    whatsapp = ?, 
                    contract_value = ?, 
                    notes = ?, 
                    is_deleted = 0, 
                    updated_at = NOW() 
                WHERE id = ?
            ');
            $stmtUp->execute([$vendorName, $pic, $phone, $phone, $dealPrice, $notes, (int)$vendorId]);
        } else {
            $stmtIns = $db->prepare('
                INSERT INTO vendors (
                    workspace_id, category_id, name, contact_person, phone, whatsapp, 
                    contract_value, budget_expense_id, notes, status, is_deleted, created_at
                ) VALUES (
                    ?, ?, ?, ?, ?, ?, ?, ?, ?, "Booked", 0, NOW()
                )
            ');
            $stmtIns->execute([$workspaceId, $vCatId, $vendorName, $pic, $phone, $phone, $dealPrice, $expenseId, $notes]);
        }
    }

    public function updateExpense(int $workspaceId, int $id, array $data): bool
    {
        $db = $this->db();
        $stmt = $db->prepare(
            'UPDATE budget_expenses SET 
                category_id = ?, 
                name = ?, 
                vendor_name = ?, 
                estimated_cost = ?, 
                actual_cost = ?, 
                status = ?, 
                payment_method = ?, 
                notes = ?, 
                due_date = ?, 
                reminder_enabled = ?, 
                reminder_date = ?, 
                reminder_notes = ?, 
                updated_at = NOW() 
             WHERE id = ? AND workspace_id = ? AND is_deleted = 0'
        );

        $stmt->execute([
            (int) $data['category_id'],
            trim($data['name']),
            !empty($data['vendor_name']) ? trim($data['vendor_name']) : null,
            (float) ($data['estimated_cost'] ?? 0.0),
            (float) ($data['actual_cost'] ?? 0.0),
            $data['status'] ?? 'Planned',
            !empty($data['payment_method']) ? trim($data['payment_method']) : null,
            !empty($data['notes']) ? trim($data['notes']) : null,
            !empty($data['due_date']) ? $data['due_date'] : null,
            (int) ($data['reminder_enabled'] ?? 0),
            (!empty($data['reminder_enabled']) && !empty($data['reminder_date'])) ? $data['reminder_date'] : null,
            !empty($data['reminder_notes']) ? trim($data['reminder_notes']) : null,
            $id,
            $workspaceId
        ]);

        // Sync Vendor record
        $this->syncVendorRecord($workspaceId, $id, $data);

        $this->recalculateExpenseState($workspaceId, $id);
        return true;
    }

    public function deleteExpense(int $workspaceId, int $id): bool
    {
        $db = $this->db();
        $db->beginTransaction();
        try {
            $this->query(
                'UPDATE budget_expenses SET is_deleted = 1, deleted_at = NOW() WHERE id = ? AND workspace_id = ?',
                [$id, $workspaceId]
            );
            $this->query(
                'UPDATE budget_payments SET is_deleted = 1 WHERE expense_id = ? AND workspace_id = ?',
                [$id, $workspaceId]
            );
            $db->commit();
            return true;
        } catch (\Exception $e) {
            $db->rollBack();
            throw $e;
        }
    }

    // ─────────────────────────────────────────────────────────────────────────
    // PAYMENT LOGGING & HISTORY
    // ─────────────────────────────────────────────────────────────────────────

    public function getPayments(int $workspaceId, ?int $expenseId = null, int $limit = 50, int $offset = 0): array
    {
        $sql = 'SELECT p.*, e.name as expense_name, c.name as category_name
                FROM budget_payments p
                JOIN budget_expenses e ON e.id = p.expense_id
                JOIN budget_categories c ON c.id = e.category_id
                WHERE p.workspace_id = :workspace_id AND p.is_deleted = 0 AND e.is_deleted = 0';
        
        if ($expenseId !== null) {
            $sql .= ' AND p.expense_id = :expense_id';
        }
        
        $sql .= ' ORDER BY p.payment_date DESC, p.id DESC LIMIT :limit OFFSET :offset';

        $db = $this->db();
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':workspace_id', $workspaceId, \PDO::PARAM_INT);
        if ($expenseId !== null) {
            $stmt->bindValue(':expense_id', $expenseId, \PDO::PARAM_INT);
        }
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();
        
        return $stmt->fetchAll();
    }

    public function getPaymentsCount(int $workspaceId, ?int $expenseId = null): int
    {
        $sql = 'SELECT COUNT(*) as total
                FROM budget_payments p
                JOIN budget_expenses e ON e.id = p.expense_id
                WHERE p.workspace_id = :workspace_id AND p.is_deleted = 0 AND e.is_deleted = 0';
        
        if ($expenseId !== null) {
            $sql .= ' AND p.expense_id = :expense_id';
        }

        $db = $this->db();
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':workspace_id', $workspaceId, \PDO::PARAM_INT);
        if ($expenseId !== null) {
            $stmt->bindValue(':expense_id', $expenseId, \PDO::PARAM_INT);
        }
        $stmt->execute();
        $res = $stmt->fetch();
        return (int) ($res['total'] ?? 0);
    }

    public function createPayment(int $workspaceId, int $expenseId, float $amount, string $paymentDate, ?string $paymentMethod = null, ?string $referenceNo = null, ?string $notes = null): int
    {
        $db = $this->db();
        $stmt = $db->prepare(
            'INSERT INTO budget_payments (
                workspace_id, expense_id, amount, payment_date, payment_method, reference_no, notes, created_at
            ) VALUES (
                ?, ?, ?, ?, ?, ?, ?, NOW()
            )'
        );
        $stmt->execute([
            $workspaceId,
            $expenseId,
            $amount,
            $paymentDate,
            !empty($paymentMethod) ? trim($paymentMethod) : null,
            !empty($referenceNo) ? trim($referenceNo) : null,
            !empty($notes) ? trim($notes) : null
        ]);

        $paymentId = (int)$db->lastInsertId();
        
        // Auto-recalculate parent expense
        $this->recalculateExpenseState($workspaceId, $expenseId);
        
        return $paymentId;
    }

    public function deletePayment(int $workspaceId, int $id): bool
    {
        // Get expense ID before soft deleting
        $stmt = $this->query(
            'SELECT expense_id FROM budget_payments WHERE id = ? AND workspace_id = ? AND is_deleted = 0 LIMIT 1',
            [$id, $workspaceId]
        );
        $payment = $stmt->fetch();
        
        if (!$payment) {
            return false;
        }
        
        $expenseId = (int) $payment['expense_id'];

        $this->query(
            'UPDATE budget_payments SET is_deleted = 1 WHERE id = ? AND workspace_id = ?',
            [$id, $workspaceId]
        );

        $this->recalculateExpenseState($workspaceId, $expenseId);
        return true;
    }

    public function recalculateExpenseState(int $workspaceId, int $expenseId): void
    {
        // 1. Calculate sum of active payments
        $stmt = $this->query(
            'SELECT SUM(amount) as paid FROM budget_payments WHERE expense_id = ? AND workspace_id = ? AND is_deleted = 0',
            [$expenseId, $workspaceId]
        );
        $res = $stmt->fetch();
        $totalPaid = (float) ($res['paid'] ?? 0.0);

        // 2. Fetch current expense details
        $stmtExpense = $this->query(
            'SELECT estimated_cost, actual_cost, status FROM budget_expenses WHERE id = ? AND workspace_id = ? AND is_deleted = 0 LIMIT 1',
            [$expenseId, $workspaceId]
        );
        $expense = $stmtExpense->fetch();
        
        if (!$expense) {
            return;
        }

        $estimated = (float) $expense['estimated_cost'];
        $actual = (float) $expense['actual_cost'];
        $currentStatus = $expense['status'];

        // Determine correct status based on payment progress
        // A Cancelled expense stays Cancelled unless payment changes it
        if ($currentStatus === 'Cancelled') {
            $newStatus = 'Cancelled';
        } else {
            $targetCost = $actual > 0 ? $actual : $estimated;
            if ($totalPaid >= $targetCost && $targetCost > 0) {
                $newStatus = 'Paid';
            } elseif ($totalPaid > 0) {
                $newStatus = 'Partially Paid';
            } elseif ($actual > 0) {
                $newStatus = 'Booked';
            } else {
                $newStatus = 'Planned';
            }
        }

        // Update the expense totals
        $this->query(
            'UPDATE budget_expenses SET status = ?, updated_at = NOW() WHERE id = ? AND workspace_id = ?',
            [$newStatus, $expenseId, $workspaceId]
        );
    }

    // ─────────────────────────────────────────────────────────────────────────
    // FINANCIAL STATISTICS & AGGREGATIONS
    // ─────────────────────────────────────────────────────────────────────────

    public function getSummaryStats(int $workspaceId): array
    {
        $settings = $this->getSettings($workspaceId);
        $totalBudget = (float) $settings['total_budget'];

        // Aggregate planned (estimated), actual, and paid values
        $stmt = $this->query(
            'SELECT 
                SUM(estimated_cost) as total_planned,
                SUM(actual_cost) as total_actual
             FROM budget_expenses 
             WHERE workspace_id = ? AND is_deleted = 0 AND status != "Cancelled"',
            [$workspaceId]
        );
        $expenseAgg = $stmt->fetch();
        $planned = (float) ($expenseAgg['total_planned'] ?? 0.0);
        $actual = (float) ($expenseAgg['total_actual'] ?? 0.0);

        // Aggregate actual paid amount
        $stmtPaid = $this->query(
            'SELECT SUM(amount) as total_paid 
             FROM budget_payments 
             WHERE workspace_id = ? AND is_deleted = 0',
            [$workspaceId]
        );
        $paidAgg = $stmtPaid->fetch();
        $paid = (float) ($paidAgg['total_paid'] ?? 0.0);

        // Calculate differences
        $remainingBudget = $totalBudget - $paid;
        
        // Target: actual expenses (if any defined) + estimated expenses (for items without actual cost)
        // Let's compute actual remaining to pay: sum(max(actual_cost, 0) - paid) for each expense
        // For simplicity: Remaining to pay is (Total Actual - Total Paid)
        // Wait, if an expense is not paid yet, how much is remaining?
        // Let's compute actual remaining balance from details:
        $stmtRemaining = $this->query(
            'SELECT id, estimated_cost, actual_cost, status FROM budget_expenses WHERE workspace_id = ? AND is_deleted = 0 AND status != "Cancelled"',
            [$workspaceId]
        );
        $allExpenses = $stmtRemaining->fetchAll();
        
        $totalRemainingToPay = 0.0;
        foreach ($allExpenses as $exp) {
            $expId = (int)$exp['id'];
            $expEst = (float)$exp['estimated_cost'];
            $expAct = (float)$exp['actual_cost'];
            $target = $expAct > 0 ? $expAct : $expEst;
            
            $stmtPaidExp = $this->query(
                'SELECT SUM(amount) as paid FROM budget_payments WHERE expense_id = ? AND is_deleted = 0',
                [$expId]
            );
            $paidRes = $stmtPaidExp->fetch();
            $expPaid = (float) ($paidRes['paid'] ?? 0.0);
            
            $rem = max(0.0, $target - $expPaid);
            $totalRemainingToPay += $rem;
        }

        // Budget Progress
        $progressPercent = 0.0;
        if ($totalBudget > 0) {
            $progressPercent = min(100.0, ($paid / $totalBudget) * 100);
        }

        // Over budget calculation
        // Over budget is true if either Total Planned > Total Budget OR Total Actual > Total Budget
        // Wait! The prompt says "Over Budget Indicator". Usually, if Total Planned > Total Budget, or Total Actual > Total Budget, or Total Paid > Total Budget.
        // Let's mark as over-budget if (Total Actual > Total Budget && Total Budget > 0) or (Total Planned > Total Budget && Total Budget > 0).
        $isOverBudget = false;
        if ($totalBudget > 0 && ($planned > $totalBudget || $actual > $totalBudget || $paid > $totalBudget)) {
            $isOverBudget = true;
        }

        return [
            'total_budget' => $totalBudget,
            'currency' => $settings['currency'],
            'total_planned' => $planned,
            'total_actual' => $actual,
            'total_paid' => $paid,
            'remaining_budget' => $remainingBudget,
            'remaining_to_pay' => $totalRemainingToPay,
            'progress_percent' => $progressPercent,
            'is_over_budget' => $isOverBudget
        ];
    }

    public function getCategorySummaryStats(int $workspaceId): array
    {
        $categories = $this->getCategories($workspaceId);
        $result = [];

        foreach ($categories as $cat) {
            $catId = (int) $cat['id'];
            
            // Get 1-to-1 category expense
            $expense = $this->getCategoryExpense($workspaceId, $catId);
            
            $planned = (float) $expense['estimated_cost'];
            $actual = (float) $expense['actual_cost'];
            $vendorName = $expense['vendor_name'] ?: 'Not Assigned';

            // Fetch paid per category
            $stmtPaid = $this->query(
                'SELECT SUM(p.amount) as paid
                 FROM budget_payments p
                 WHERE p.expense_id = ? AND p.workspace_id = ? AND p.is_deleted = 0',
                [(int)$expense['id'], $workspaceId]
            );
            $resPaid = $stmtPaid->fetch();
            $paid = (float) ($resPaid['paid'] ?? 0.0);

            $result[] = [
                'category_id' => $catId,
                'category_name' => $cat['name'],
                'planned' => $planned,
                'actual' => $actual,
                'paid' => $paid,
                'vendor_name' => $vendorName
            ];
        }
        return $result;
    }

    public function getExpenseStatusStats(int $workspaceId): array
    {
        $statuses = ['Planned', 'Booked', 'Partially Paid', 'Paid', 'Cancelled'];
        $stats = [];
        
        foreach ($statuses as $status) {
            $stmt = $this->query(
                'SELECT COUNT(*) as cnt FROM budget_expenses WHERE workspace_id = ? AND status = ? AND is_deleted = 0',
                [$workspaceId, $status]
            );
            $res = $stmt->fetch();
            $stats[$status] = (int) ($res['cnt'] ?? 0);
        }
        return $stats;
    }

    public function getRecentPayments(int $workspaceId, int $limit = 5): array
    {
        return $this->getPayments($workspaceId, null, $limit, 0);
    }

    public function getUpcomingDueDates(int $workspaceId, int $limit = 5): array
    {
        return $this->query(
            'SELECT e.*, c.name as category_name
             FROM budget_expenses e
             JOIN budget_categories c ON c.id = e.category_id
             WHERE e.workspace_id = ? 
               AND e.is_deleted = 0 
               AND c.is_deleted = 0
               AND e.due_date IS NOT NULL 
               AND e.due_date >= CURDATE()
               AND e.status NOT IN ("Paid", "Cancelled")
             ORDER BY e.due_date ASC 
             LIMIT ?',
            [$workspaceId, $limit]
        )->fetchAll();
    }
    public function getAllCategories(int $workspaceId): array
    {
        $this->ensureDefaultCategoriesExist($workspaceId);
        return $this->query(
            'SELECT * FROM budget_categories WHERE workspace_id = ? ORDER BY sort_order ASC, id ASC',
            [$workspaceId]
        )->fetchAll();
    }

    public function updateActiveCategories(int $workspaceId, array $activeCategoryIds): void
    {
        $db = $this->db();
        $db->beginTransaction();
        try {
            $stmtDeactivate = $db->prepare('UPDATE budget_categories SET is_deleted = 1 WHERE workspace_id = ?');
            $stmtDeactivate->execute([$workspaceId]);
            
            if (!empty($activeCategoryIds)) {
                $placeholders = implode(',', array_fill(0, count($activeCategoryIds), '?'));
                $stmtActivate = $db->prepare("UPDATE budget_categories SET is_deleted = 0 WHERE workspace_id = ? AND id IN ($placeholders)");
                $stmtActivate->execute(array_merge([$workspaceId], $activeCategoryIds));
            }
            $db->commit();
        } catch (\Exception $e) {
            $db->rollBack();
            throw $e;
        }
    }

    public function getCategoryExpense(int $workspaceId, int $categoryId): array
    {
        $stmtCatName = $this->query('SELECT name FROM budget_categories WHERE id = ? LIMIT 1', [$categoryId]);
        $catName = $stmtCatName->fetchColumn() ?: 'Wedding Item';

        $stmt = $this->query(
            'SELECT e.*, c.name as category_name,
                    v.contact_person as vendor_pic, v.phone as vendor_phone, v.whatsapp as vendor_whatsapp
             FROM budget_expenses e
             JOIN budget_categories c ON c.id = e.category_id
             LEFT JOIN vendors v ON v.budget_expense_id = e.id AND v.is_deleted = 0
             WHERE e.category_id = ? AND e.workspace_id = ? AND e.is_deleted = 0 LIMIT 1',
            [$categoryId, $workspaceId]
        );
        $expense = $stmt->fetch();
        
        if (!$expense) {
            $db = $this->db();
            $stmtIns = $db->prepare('
                INSERT INTO budget_expenses (
                    workspace_id, category_id, name, estimated_cost, actual_cost, status, created_at
                ) VALUES (?, ?, ?, 0.00, 0.00, "Planned", NOW())
            ');
            $stmtIns->execute([$workspaceId, $categoryId, $catName]);
            
            return $this->getCategoryExpense($workspaceId, $categoryId);
        }

        return $expense;
    }
}

