<?php
namespace App\Services;

use App\Core\Database;

class FinanceService
{
    /**
     * Recalculates the waterfall for a specific financial reference (e.g., lease_contract).
     * Pays off obligations from oldest to newest based on the net balance.
     */
    public static function recalculateWaterfall(int $referenceId, string $referenceType): void
    {
        $db = Database::getConnection();

        // 0. Determine if the contract is taxable
        $isTaxable = false;
        if ($referenceType === 'lease') {
            $stmtTax = $db->prepare("SELECT is_taxable FROM lease_contracts WHERE id = :id");
            $stmtTax->execute(['id' => $referenceId]);
            $isTaxable = (bool)$stmtTax->fetchColumn();
        } elseif ($referenceType === 'service') {
            $stmtTax = $db->prepare("SELECT is_taxable FROM service_contracts WHERE id = :id");
            $stmtTax->execute(['id' => $referenceId]);
            $isTaxable = (bool)$stmtTax->fetchColumn();
        } elseif ($referenceType === 'sales') {
            $stmtTax = $db->prepare("SELECT is_taxable FROM sales_contracts WHERE id = :id");
            $stmtTax->execute(['id' => $referenceId]);
            $isTaxable = (bool)$stmtTax->fetchColumn();
        } elseif ($referenceType === 'purchase') {
            $stmtTax = $db->prepare("SELECT is_taxable FROM purchase_contracts WHERE id = :id");
            $stmtTax->execute(['id' => $referenceId]);
            $isTaxable = (bool)$stmtTax->fetchColumn();
        } elseif ($referenceType === 'investment') {
            $stmtTax = $db->prepare("SELECT is_taxable FROM investment_contracts WHERE id = :id");
            $stmtTax->execute(['id' => $referenceId]);
            $isTaxable = (bool)$stmtTax->fetchColumn();
        } elseif ($referenceType === 'financing') {
            $stmtTax = $db->prepare("SELECT is_taxable FROM financing_contracts WHERE id = :id");
            $stmtTax->execute(['id' => $referenceId]);
            $isTaxable = (bool)$stmtTax->fetchColumn();
        } elseif ($referenceType === 'invoice') {
            $stmtTax = $db->prepare("SELECT CASE WHEN tax_mode != 'none' THEN 1 ELSE 0 END FROM invoices WHERE id = :id");
            $stmtTax->execute(['id' => $referenceId]);
            $isTaxable = (bool)$stmtTax->fetchColumn();
        }
        
        // Dynamic amount column: use total (net + tax) if taxable, otherwise just net
        $amountCol = $isTaxable ? "(net_amount + tax_amount)" : "net_amount";

        // 1. Calculate sum of 'receipt' vouchers
        $stmtReceipts = $db->prepare("SELECT SUM($amountCol) as total FROM vouchers WHERE reference_id = :ref_id AND reference_type = :ref_type AND type = 'receipt' AND status = 'active'");
        $stmtReceipts->execute(['ref_id' => $referenceId, 'ref_type' => $referenceType]);
        $totalReceipts = (float)$stmtReceipts->fetchColumn();

        // 2. Calculate sum of 'payment' vouchers
        $stmtPayments = $db->prepare("SELECT SUM($amountCol) as total FROM vouchers WHERE reference_id = :ref_id AND reference_type = :ref_type AND type = 'payment' AND status = 'active'");
        $stmtPayments->execute(['ref_id' => $referenceId, 'ref_type' => $referenceType]);
        $totalPayments = (float)$stmtPayments->fetchColumn();

        // 3. Calculate sum of 'adjustment' vouchers
        $stmtAdjustments = $db->prepare("SELECT SUM($amountCol) as total FROM vouchers WHERE reference_id = :ref_id AND reference_type = :ref_type AND type = 'adjustment' AND status = 'active'");
        $stmtAdjustments->execute(['ref_id' => $referenceId, 'ref_type' => $referenceType]);
        $totalAdjustments = (float)$stmtAdjustments->fetchColumn();

        // Net Balance depends on contract nature
        // Receivable (Lease, Sales, Invoice): Receipts (+) promote paid status, Payments (-) are refunds, Adjustments (+) are credits
        // Payable (Service, Investment, Purchase, Financing): Payments (+) promote paid status, Receipts (-) are refunds, Adjustments (+) are debits
        $receivableTypes = ['lease', 'sales', 'invoice'];
        if (in_array($referenceType, $receivableTypes)) {
            $netBalance = $totalReceipts - $totalPayments - $totalAdjustments;
        } else {
            // For Liability types (Service, Investment, etc.), a 'Debit' adjustment (+), which reduces our debt, 
            // should increase the available funds to pay off obligations.
            $netBalance = $totalPayments - $totalReceipts + $totalAdjustments;
        }

        // 3. Reset all related obligations
        $stmtReset = $db->prepare("UPDATE financial_obligations SET paid_amount = 0, status = 'pending' WHERE reference_id = :ref_id AND reference_type = :ref_type");
        $stmtReset->execute(['ref_id' => $referenceId, 'ref_type' => $referenceType]);

        // 4. Fetch obligations chronologically (Using total_amount which includes tax)
        $stmtObs = $db->prepare("SELECT id, total_amount FROM financial_obligations WHERE reference_id = :ref_id AND reference_type = :ref_type ORDER BY due_date ASC, id ASC");
        $stmtObs->execute(['ref_id' => $referenceId, 'ref_type' => $referenceType]);
        $obligations = $stmtObs->fetchAll(\PDO::FETCH_ASSOC);

        // 5. Run the Waterfall payment
        $stmtUpdate = $db->prepare("UPDATE financial_obligations SET paid_amount = :paid_amount, status = :status WHERE id = :id");
        
        foreach ($obligations as $ob) {
            $obAmount = (float)$ob['total_amount'];
            
            if ($netBalance <= 0) {
                // No more balance
                break; 
            }

            if ($netBalance >= $obAmount) {
                // Fully pay this obligation
                $paid = $obAmount;
                $status = 'paid';
                $netBalance -= $obAmount;
            } else {
                // Partially pay this obligation
                $paid = $netBalance;
                $status = 'partial';
                $netBalance = 0;
            }

            $stmtUpdate->execute([
                'paid_amount' => $paid,
                'status' => $status,
                'id' => $ob['id']
            ]);
        }
    }

    /**
     * Generates a hierarchical voucher number (e.g., REC-120001)
     * Format: [Prefix]-[PortfolioCode][BranchCode][Sequence]
     */
    public static function generateVoucherNumber(string $prefix, string $type, ?int $branchId = null): string
    {
        $db = Database::getConnection();
        
        // 1. Get Portfolio and Branch codes
        $pCode = '0';
        $bCode = '0';
        
        if ($branchId) {
            $stmt = $db->prepare("SELECT b.code as b_code, p.code as p_code 
                                 FROM branches b 
                                 JOIN portfolios p ON b.portfolio_id = p.id 
                                 WHERE b.id = :branch_id LIMIT 1");
            $stmt->execute(['branch_id' => $branchId]);
            $codes = $stmt->fetch(\PDO::FETCH_ASSOC);
            if ($codes) {
                $pCode = $codes['p_code'] ?: '0';
                $bCode = $codes['b_code'] ?: '0';
            }
        }

        // 2. Find last sequence for THIS branch
        $whereBranch = $branchId ? "branch_id = :branch_id" : "branch_id IS NULL";
        $fetchParams = ['type' => $type];
        if ($branchId) $fetchParams['branch_id'] = $branchId;

        $table = 'vouchers';
        $column = 'voucher_number';
        if ($type === 'proforma') {
            $table = 'proforma_invoices';
            $column = 'invoice_number';
        } elseif ($type === 'claim') {
            $table = 'financial_claims';
            $column = 'claim_number';
        } elseif ($type === 'transfer') {
            $table = 'transfers';
            $column = 'transfer_number';
        } elseif ($type === 'invoice') {
            $table = 'invoices';
            $column = 'invoice_number';
        } elseif ($type === 'letter') {
            $table = 'letters';
            $column = 'serial_number';
        }

        $isStandaloneTable = in_array($type, ['proforma', 'claim', 'transfer', 'invoice', 'letter']);
        
        $stmt = $db->prepare("SELECT $column FROM $table 
                             WHERE " . ($isStandaloneTable ? "1=1" : "type = :type") . " AND $whereBranch 
                             ORDER BY id DESC LIMIT 1");
        
        $execParams = $fetchParams;
        if ($isStandaloneTable) unset($execParams['type']);
        
        $stmt->execute($execParams);
        $lastNumber = $stmt->fetchColumn();

        $sequence = 1;
        if ($lastNumber) {
            $pattern = $prefix . '-' . $pCode . $bCode . '%';
            $stmtMax = $db->prepare("SELECT $column FROM $table 
                                    WHERE " . ($isStandaloneTable ? "1=1" : "type = :type") . " AND $whereBranch AND $column LIKE :pattern 
                                    ORDER BY $column DESC LIMIT 1");
            
            $stmtMax->execute($execParams + ['pattern' => $pattern]);
            $maxNum = $stmtMax->fetchColumn();
            
            if ($maxNum) {
                $parts = explode('-', $maxNum);
                $fullSuffix = end($parts); // e.g. 110006
                // The sequence is the last 4 digits
                $seqPart = substr($fullSuffix, -4);
                $sequence = (int)$seqPart + 1;
            } else {
                // If none match the pattern yet, but we have some vouchers, 
                // we should still probably just count or start at 1 if this is the first with THESE codes
                $sequence = 1;
            }
        }

        // Combine: Prefix - PCode BCode Sequence(padded to 4)
        return $prefix . '-' . $pCode . $bCode . str_pad((string)$sequence, 4, '0', STR_PAD_LEFT);
    }
}