<?php
namespace App\Modules\Finance\Models;

class FinanceModel extends \Core\Model {
    protected $moduleName = 'Finance';
    // Uses deleteItem() with allowedTables whitelist and inline deleted_at IS NULL.
    // Base Model::delete() is intentionally bypassed.
    protected $useSoftDeletes = false;
    
    // --- CATEGORIES ---
    public function getCategories($userId, $type = null) {
        if ($type) {
            return $this->query("SELECT * FROM fin_categories WHERE user_id = ? AND type = ? AND deleted_at IS NULL ORDER BY name ASC", [$userId, $type])->fetchAll();
        }
        return $this->query("SELECT * FROM fin_categories WHERE user_id = ? AND deleted_at IS NULL ORDER BY linked_fund ASC, name ASC", [$userId])->fetchAll();
    }

    public function setCategoryFund($userId, $categoryName, $fund) {
        $sql = "UPDATE fin_categories SET linked_fund = ? WHERE user_id = ? AND name = ?";
        return $this->query($sql, [$fund, $userId, $categoryName]);
    }

    public function addCategory($userId, $type, $name, $budget = null, $linkedFund = 'personal', $color = '#6366f1') {
        $sql = "INSERT INTO fin_categories (user_id, type, name, monthly_budget, linked_fund, color) VALUES (?, ?, ?, ?, ?, ?)";
        return $this->query($sql, [$userId, $type, $name, $budget, $linkedFund, $color]);
    }

    public function deleteCategory($id, $userId) {
        $sql = "UPDATE fin_categories SET deleted_at = CURRENT_TIMESTAMP WHERE id = ? AND user_id = ?";
        return $this->query($sql, [$id, $userId]);
    }
    
    // --- INCOMES & AUTO DISTRIBUTION --- 
    public function addIncome($userId, $source, $amount, $date) {
        // Round each share to 2 decimal places to avoid floating-point drift.
        // Charity gets the remainder so that the 4 shares always add up exactly to $amount.
        $distPersonal = round($amount * 0.50, 2);
        $distBusiness = round($amount * 0.20, 2);
        $distSavings  = round($amount * 0.20, 2);
        $distCharity  = round($amount - $distPersonal - $distBusiness - $distSavings, 2);

        $sql = "INSERT INTO finance_incomes (user_id, source, amount, date_received, dist_personal, dist_business, dist_savings, dist_charity) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        $res = $this->query($sql, [$userId, $source, $amount, $date, $distPersonal, $distBusiness, $distSavings, $distCharity]);
        $this->logAction($userId, 'created', "إضافة دخل جديد: {$source} ({$amount})");
        return $res;
    }

    public function getIncomeSummary($userId) {
        $stmt = $this->query("SELECT 
            SUM(amount) as total_income,
            SUM(dist_personal) as personal_wallet,
            SUM(dist_business) as business_wallet,
            SUM(dist_savings) as savings_wallet,
            SUM(dist_charity) as charity_wallet
            FROM finance_incomes WHERE user_id = ? AND deleted_at IS NULL", [$userId]);
        return $stmt->fetch();
    }

    public function getIncomes($userId, $page = 1, $perPage = 20) {
        $sql = "SELECT * FROM finance_incomes WHERE user_id = ? AND deleted_at IS NULL ORDER BY date_received DESC";
        return $this->paginate($sql, [$userId], $page, $perPage);
    }

    // --- EXPENSES ---
    public function addExpense($userId, $category, $itemName, $amount, $date, $notes = null) {
        $sql = "INSERT INTO finance_expenses (user_id, category, item_name, amount, expense_date, notes) VALUES (?, ?, ?, ?, ?, ?)";
        $this->logAction($userId, 'created', "إضافة مصروف: {$category} - {$itemName} ({$amount})");
        return $this->query($sql, [$userId, $category, $itemName, $amount, $date, $notes]);
    }

    public function getExpenseSummary($userId) {
        return $this->query("SELECT SUM(amount) as total_expense FROM finance_expenses WHERE user_id = ? AND deleted_at IS NULL", [$userId])->fetch()->total_expense ?? 0;
    }

    public function getExpenses($userId, $page = 1, $perPage = 20) {
        $sql = "SELECT * FROM finance_expenses WHERE user_id = ? AND deleted_at IS NULL ORDER BY created_at DESC, id DESC";
        return $this->paginate($sql, [$userId], $page, $perPage);
    }

    public function getWalletBalances($userId) {
        // 1. Total distributed income per wallet (all-time, non-deleted)
        $income = $this->query("
            SELECT
                COALESCE(SUM(dist_personal), 0) AS total_personal,
                COALESCE(SUM(dist_business), 0) AS total_business,
                COALESCE(SUM(dist_savings),  0) AS total_savings,
                COALESCE(SUM(dist_charity),  0) AS total_charity
            FROM finance_incomes
            WHERE user_id = ? AND deleted_at IS NULL",
        [$userId])->fetch();

        // 2. Total expenses per wallet.
        //    Uses LEFT JOIN so that expenses whose category was soft-deleted or renamed
        //    are still counted — they fall back to 'personal' via COALESCE.
        //    c.deleted_at IS NULL is intentionally ABSENT from the JOIN condition:
        //    we want to match active categories properly, but if no match exists
        //    the LEFT JOIN gives NULL and COALESCE maps it to 'personal'.
        $expenses = $this->query("
            SELECT
                COALESCE(SUM(CASE WHEN COALESCE(c.linked_fund, 'personal') = 'personal' THEN e.amount ELSE 0 END), 0) AS spent_personal,
                COALESCE(SUM(CASE WHEN COALESCE(c.linked_fund, 'personal') = 'business' THEN e.amount ELSE 0 END), 0) AS spent_business,
                COALESCE(SUM(CASE WHEN COALESCE(c.linked_fund, 'personal') = 'savings'  THEN e.amount ELSE 0 END), 0) AS spent_savings,
                COALESCE(SUM(CASE WHEN COALESCE(c.linked_fund, 'personal') = 'charity'  THEN e.amount ELSE 0 END), 0) AS spent_charity
            FROM finance_expenses e
            LEFT JOIN fin_categories c
                ON  (e.category COLLATE utf8mb4_unicode_ci) = (c.name COLLATE utf8mb4_unicode_ci)
                AND e.user_id = c.user_id
                AND c.deleted_at IS NULL
            WHERE e.user_id = ? AND e.deleted_at IS NULL",
        [$userId])->fetch();

        return [
            'personal' => (float)($income->total_personal ?? 0) - (float)($expenses->spent_personal ?? 0),
            'business' => (float)($income->total_business ?? 0) - (float)($expenses->spent_business ?? 0),
            'savings'  => (float)($income->total_savings  ?? 0) - (float)($expenses->spent_savings  ?? 0),
            'charity'  => (float)($income->total_charity  ?? 0) - (float)($expenses->spent_charity  ?? 0),
            'totals'   => [
                'income' => $income,
                'spent'  => $expenses
            ]
        ];
    }

    public function getMonthlyAccountAnalysis($userId) {
        $sql = "SELECT category, SUM(amount) as total_spent, COUNT(*) as txn_count 
                FROM finance_expenses 
                WHERE user_id = ? AND MONTH(expense_date) = MONTH(CURRENT_DATE) 
                AND YEAR(expense_date) = YEAR(CURRENT_DATE) AND deleted_at IS NULL 
                GROUP BY category 
                ORDER BY total_spent DESC";
        return $this->query($sql, [$userId])->fetchAll();
    }

    // --- SAVING GOALS ---
    public function addSavingGoal($userId, $title, $target, $expectedDate) {
        $sql = "INSERT INTO finance_saving_goals (user_id, title, target_amount, expected_date) VALUES (?, ?, ?, ?)";
        $this->logAction($userId, 'created', "هدف ادخار جديد: {$title}");
        return $this->query($sql, [$userId, $title, $target, $expectedDate]);
    }

    public function updateSavingGoalProgress($goalId, $userId, $amountToAdd) {
        $sql = "UPDATE finance_saving_goals SET current_amount = current_amount + ? WHERE id = ? AND user_id = ?";
        return $this->query($sql, [$amountToAdd, $goalId, $userId]);
    }

    public function getSavingGoals($userId) {
        return $this->query("SELECT * FROM finance_saving_goals WHERE user_id = ? AND deleted_at IS NULL ORDER BY created_at DESC", [$userId])->fetchAll();
    }

    // --- INSTALLMENTS ---
    public function addInstallment($userId, $name, $icon, $totalMonths, $monthlyAmount, $startDate) {
        $sql = "INSERT INTO finance_installments (user_id, name, icon, total_months, monthly_amount, start_date, paid_months_json) VALUES (?, ?, ?, ?, ?, ?, '[]')";
        $this->logAction($userId, 'created', "تسجيل قسط: {$name}");
        return $this->query($sql, [$userId, $name, $icon, $totalMonths, $monthlyAmount, $startDate]);
    }

    public function updateInstallmentPaidMonths($id, $userId, $jsonArray) {
        $sql = "UPDATE finance_installments SET paid_months_json = ? WHERE id = ? AND user_id = ? AND deleted_at IS NULL";
        return $this->query($sql, [$jsonArray, $id, $userId]);
    }

    public function getInstallments($userId) {
        return $this->query("SELECT * FROM finance_installments WHERE user_id = ? AND deleted_at IS NULL ORDER BY created_at DESC", [$userId])->fetchAll();
    }

    // --- DEBTS ---
    public function addDebt($userId, $person, $amount, $type, $dueDate = null) {
        $sql = "INSERT INTO finance_debts (user_id, person_name, amount, type, due_date) VALUES (?, ?, ?, ?, ?)";
        return $this->query($sql, [$userId, $person, $amount, $type, $dueDate]);
    }

    public function resolveDebt($id, $userId) {
        $sql = "UPDATE finance_debts SET status = 'paid' WHERE id = ? AND user_id = ?";
        return $this->query($sql, [$id, $userId]);
    }

    public function getDebts($userId) {
        return $this->query("
            SELECT d.*, 
                   IFNULL((SELECT SUM(amount) FROM finance_debt_payments WHERE debt_id = d.id), 0) AS paid_amount 
            FROM finance_debts d 
            WHERE d.user_id = ? AND d.deleted_at IS NULL 
            ORDER BY d.status ASC
        ", [$userId])->fetchAll();
    }

    public function addDebtPartialPayment($userId, $debtId, $amount, $date, $notes) {
        $debt = $this->query("SELECT id, amount, status, IFNULL((SELECT SUM(amount) FROM finance_debt_payments WHERE debt_id = ?), 0) AS total_paid FROM finance_debts WHERE id = ? AND user_id = ?", [$debtId, $debtId, $userId])->fetch();
        if(!$debt) return false;

        $sql = "INSERT INTO finance_debt_payments (debt_id, amount, payment_date, notes) VALUES (?, ?, ?, ?)";
        $res = $this->query($sql, [$debtId, $amount, $date, $notes]);
        
        if ($debt->total_paid + $amount >= $debt->amount) {
            $this->resolveDebt($debtId, $userId);
        }
        return $res;
    }

    // --- INVESTMENTS ---
    public function addInvestment($userId, $name, $type, $buyPrice, $quantity, $currentPrice) {
        $sql = "INSERT INTO finance_investments (user_id, asset_name, asset_type, buy_price, quantity, current_value) VALUES (?, ?, ?, ?, ?, ?)";
        return $this->query($sql, [$userId, $name, $type, $buyPrice, $quantity, $currentPrice]); 
    }

    public function updateInvestmentValue($id, $userId, $currentValue) {
        $sql = "UPDATE finance_investments SET current_value = ? WHERE id = ? AND user_id = ?";
        return $this->query($sql, [$currentValue, $id, $userId]);
    }

    public function getInvestments($userId) {
        return $this->query("SELECT * FROM finance_investments WHERE user_id = ? AND deleted_at IS NULL", [$userId])->fetchAll();
    }

    // --- GENERIC CRUD FOR ALL FINANCE TABLES ---
    private $allowedTables = ['finance_incomes', 'finance_expenses', 'finance_saving_goals', 'finance_installments', 'finance_debts', 'finance_investments', 'fin_categories'];

    public function getItem($table, $id, $userId) {
        if (!in_array($table, $this->allowedTables)) return false;
        return $this->query("SELECT * FROM {$table} WHERE id = ? AND user_id = ? AND deleted_at IS NULL", [$id, $userId])->fetch();
    }

    public function deleteItem($table, $id, $userId) {
        if (!in_array($table, $this->allowedTables)) return false;
        $sql = "UPDATE {$table} SET deleted_at = CURRENT_TIMESTAMP WHERE id = ? AND user_id = ?";
        $this->logAction($userId, 'deleted', "حذف سجل من {$table} برقم {$id}");
        return $this->query($sql, [$id, $userId]);
    }

    public function updateItem($table, $id, $userId, $data) {
        if (!in_array($table, $this->allowedTables) || empty($data)) return false;
        
        $setFields = [];
        $values = [];
        foreach ($data as $key => $val) {
            $setFields[] = "`{$key}` = ?";
            $values[] = $val;
        }
        $values[] = $id;
        $values[] = $userId;
        
        $setSql = implode(', ', $setFields);
        $sql = "UPDATE {$table} SET {$setSql} WHERE id = ? AND user_id = ? AND deleted_at IS NULL";
        
        $this->logAction($userId, 'updated', "تعديل سجل في {$table} برقم {$id}");
        $stmt = $this->query($sql, $values);
        
        if (!$stmt) {
            error_log("FinanceModel Error: Update failed for {$table} ID {$id}. SQL: {$sql}");
            return false;
        }
        return $stmt->rowCount() > 0;
    }
}
