<?php
// member self loan-application-api.php - Complete API with proper charge type handling

// ============================================
// INITIALIZATION & CONFIGURATION
// ============================================

// Start session if not already started
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

// Load CORS configuration FIRST
require_once __DIR__ . '/includes/cors.php';
configureCors();

// Load environment variables
require_once __DIR__ . '/includes/config_loader.php';

// Load dependencies
require_once __DIR__ . '/includes/config.php';
require_once __DIR__ . '/includes/auth_functions.php';
require_once __DIR__ . '/includes/sms_helper.php';
require_once __DIR__ . '/includes/email_helper.php';
require_once __DIR__ . '/auth/AuditLogger.php';
require_once __DIR__ . '/auth/JWTManager.php';

// Load PHPMailer for advanced email
require_once __DIR__ . '/vendor/autoload.php';
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\SMTP;
use PHPMailer\PHPMailer\Exception;

// Set response headers
header('Content-Type: application/json');
header('Cache-Control: no-cache, no-store, must-revalidate');
header('Pragma: no-cache');
header('Expires: 0');
ini_set('log_errors', 1);
error_reporting(E_ALL & ~E_NOTICE);

// Security headers
header('X-Frame-Options: DENY');
header('X-Content-Type-Options: nosniff');
header('X-XSS-Protection: 1; mode=block');
header('Referrer-Policy: strict-origin-when-cross-origin');

// Handle preflight OPTIONS request
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit();
}

// ============================================
// AUTHENTICATION
// ============================================

// Check if user is logged in
if (!isset($_SESSION['member_id'])) {
    http_response_code(401);
    echo json_encode([
        'success' => false,
        'error' => 'Authentication required',
        'code' => 'UNAUTHORIZED'
    ]);
    exit();
}

$memberId = (int)$_SESSION['member_id'];
$userRole = $_SESSION['role'] ?? 'member';

// ============================================
// GLOBAL VARIABLES
// ============================================

$method = $_SERVER['REQUEST_METHOD'];
$action = $_GET['action'] ?? null;

// Define charge type constants
define('CHARGE_TYPE_UPFRONT', 'upfront');
define('CHARGE_TYPE_TOPUP', 'topup');

// ============================================
// ENHANCED LOAN CHARGES MANAGER WITH CHARGE TYPES
// ============================================

/**
 * Enhanced Account Charges Helper with full formula support and charge types
 */
class AccountChargesHelper {
    private $pdo;
    private $db;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
        $this->db = new DatabaseWrapper($pdo);
        set_time_limit(30);
    }
    
    /**
     * Calculate charge amount for a single charge with precision
     */
    private function calculateChargeAmount($charge, $loanAmount, $totalMonths, $rate) {
        $totalMonths = intval($totalMonths);
        $years = round($totalMonths / 12, 2);
        $loanAmount = round(floatval($loanAmount), 2);
        $rate = round(floatval($rate), 2);
        
        $calculatedAmount = 0;
        
        if ($charge['value_type'] === 'fixed') {
            $calculatedAmount = floatval($charge['charge_value']);
            $calculatedAmount = round($calculatedAmount, 2);
        } 
        elseif ($charge['value_type'] === 'percentage') {
            $calculatedAmount = $loanAmount * (floatval($charge['charge_value']) / 100);
            $calculatedAmount = round($calculatedAmount, 2);
        } 
        elseif ($charge['value_type'] === 'formulae') {
            if (!empty($charge['formula_expression'])) {
                try {
                    $expression = $charge['formula_expression'];
                    
                    $expression = preg_replace('/\bL\b/', $loanAmount, $expression);
                    $expression = preg_replace('/\bM\b/', $totalMonths, $expression);
                    $expression = preg_replace('/\bT\b/', $years, $expression);
                    $expression = preg_replace('/\bR\b/', $rate, $expression);
                    
                    $calculatedAmount = $this->evaluateExpression($expression);
                    $calculatedAmount = round($calculatedAmount, 2);
                    
                } catch (Exception $e) {
                    error_log("❌ Formula evaluation error: " . $e->getMessage());
                    $calculatedAmount = 0;
                }
            }
        }
        
        // Apply minimum amount if enabled
        if (!empty($charge['has_minimum']) && $calculatedAmount < floatval($charge['minimum_amount'])) {
            $calculatedAmount = round(floatval($charge['minimum_amount']), 2);
        }
        
        // Apply PHCF if enabled
        if (!empty($charge['has_phcf']) && floatval($charge['phcf_percentage']) > 0) {
            $phcf = $calculatedAmount * (floatval($charge['phcf_percentage']) / 100);
            $phcf = round($phcf, 2);
            $calculatedAmount += $phcf;
            $calculatedAmount = round($calculatedAmount, 2);
        }
        
        return round($calculatedAmount, 2);
    }
    
    /**
     * Evaluate expression with support for MAX/MIN functions and basic math
     */
    private function evaluateExpression($expr) {
        $expr = str_replace(' ', '', $expr);
        
        // Handle MAX function
        if (strpos($expr, 'MAX(') !== false) {
            $pos = strpos($expr, 'MAX(');
            $openParen = $pos + 3;
            $depth = 1;
            $i = $openParen;
            $len = strlen($expr);
            
            while ($i < $len && $depth > 0) {
                if ($expr[$i] == '(') $depth++;
                elseif ($expr[$i] == ')') $depth--;
                $i++;
            }
            $closeParen = $i - 1;
            
            $inner = substr($expr, $openParen, $closeParen - $openParen);
            $rest = substr($expr, $closeParen + 1);
            
            $args = $this->splitArguments($inner);
            $values = array();
            foreach ($args as $arg) {
                $value = $this->evaluateExpression(trim($arg));
                $values[] = $value;
            }
            $result = max($values);
            $result = round($result, 2);
            
            if ($rest != '') {
                return $this->evaluateExpression($result . $rest);
            }
            return $result;
        }
        
        // Handle MIN function
        if (strpos($expr, 'MIN(') !== false) {
            $pos = strpos($expr, 'MIN(');
            $openParen = $pos + 3;
            $depth = 1;
            $i = $openParen;
            $len = strlen($expr);
            
            while ($i < $len && $depth > 0) {
                if ($expr[$i] == '(') $depth++;
                elseif ($expr[$i] == ')') $depth--;
                $i++;
            }
            $closeParen = $i - 1;
            
            $inner = substr($expr, $openParen, $closeParen - $openParen);
            $rest = substr($expr, $closeParen + 1);
            
            $args = $this->splitArguments($inner);
            $values = array();
            foreach ($args as $arg) {
                $values[] = $this->evaluateExpression(trim($arg));
            }
            $result = min($values);
            $result = round($result, 2);
            
            if ($rest != '') {
                return $this->evaluateExpression($result . $rest);
            }
            return $result;
        }
        
        // Evaluate arithmetic expression
        return $this->evaluateArithmetic($expr);
    }
    
    /**
     * Split arguments by commas, respecting parentheses
     */
    private function splitArguments($expression) {
        $args = [];
        $current = '';
        $depth = 0;
        $len = strlen($expression);
        
        for ($i = 0; $i < $len; $i++) {
            $char = $expression[$i];
            
            if ($char == '(') {
                $depth++;
                $current .= $char;
            } elseif ($char == ')') {
                $depth--;
                $current .= $char;
            } elseif ($char == ',' && $depth == 0) {
                $args[] = trim($current);
                $current = '';
            } else {
                $current .= $char;
            }
        }
        
        if ($current !== '') {
            $args[] = trim($current);
        }
        
        return $args;
    }
    
    /**
     * Evaluate arithmetic expression with operator precedence
     */
    private function evaluateArithmetic($expr) {
        $expr = trim($expr);
        
        if (is_numeric($expr)) {
            return round(floatval($expr), 2);
        }
        
        // Handle parentheses
        if (strpos($expr, '(') !== false) {
            $lastOpen = strrpos($expr, '(');
            $closePos = strpos($expr, ')', $lastOpen);
            if ($closePos !== false) {
                $inner = substr($expr, $lastOpen + 1, $closePos - $lastOpen - 1);
                $before = substr($expr, 0, $lastOpen);
                $after = substr($expr, $closePos + 1);
                
                $innerValue = $this->evaluateArithmetic($inner);
                $newExpr = $before . $innerValue . $after;
                
                return $this->evaluateArithmetic($newExpr);
            }
        }
        
        // Handle multiplication and division
        $pattern = '/(-?\d+(?:\.\d+)?)\s*([*\/])\s*(-?\d+(?:\.\d+)?)/';
        while (preg_match($pattern, $expr, $matches)) {
            $left = round(floatval($matches[1]), 2);
            $op = $matches[2];
            $right = round(floatval($matches[3]), 2);
            
            if ($op == '*') {
                $result = $left * $right;
            } else {
                if ($right == 0) throw new Exception('Division by zero');
                $result = $left / $right;
            }
            
            $result = round($result, 2);
            
            $pos = strpos($expr, $matches[0]);
            if ($pos !== false) {
                $expr = substr_replace($expr, $result, $pos, strlen($matches[0]));
            }
        }
        
        // Handle addition and subtraction
        $pattern = '/(-?\d+(?:\.\d+)?)\s*([+\-])\s*(-?\d+(?:\.\d+)?)/';
        while (preg_match($pattern, $expr, $matches)) {
            $left = round(floatval($matches[1]), 2);
            $op = $matches[2];
            $right = round(floatval($matches[3]), 2);
            
            if ($op == '+') {
                $result = $left + $right;
            } else {
                $result = $left - $right;
            }
            
            $result = round($result, 2);
            
            $pos = strpos($expr, $matches[0]);
            if ($pos !== false) {
                $expr = substr_replace($expr, $result, $pos, strlen($matches[0]));
            }
        }
        
        return round(floatval($expr), 2);
    }
    
    /**
     * Get all active charges for a loan type with charge_application field
     */
    public function getLoanCharges($accountTypeId) {
        return $this->db->fetchAll("
            SELECT 
                id, charge_type, charge_value, value_type, 
                calculation_method, description,
                has_minimum, minimum_amount, 
                has_phcf, phcf_percentage,
                formula_expression, formula_code,
                lower_limit, upper_limit, status,
                COALESCE(calculation_method, 'topup') as charge_application
                
            FROM account_charges 
            WHERE account_type_id = ? 
            AND status = 'active'
            
        ", [$accountTypeId]);
    }
    
    /**
     * Apply all active charges with proper separation of upfront vs topup
     */
    public function applyLoanCharges($loanId, $accountTypeId, $loanAmount, $loanPeriodMonths, $rate = 0) {
        set_time_limit(60);
        
        $totalMonths = intval($loanPeriodMonths);
        
        error_log("💰 Applying charges for loan {$loanId}");
        
        $charges = $this->getLoanCharges($accountTypeId);
        
        $upfrontCharges = [];
        $topupCharges = [];
        $totalUpfront = 0;
        $totalTopup = 0;
        
        foreach ($charges as $charge) {
            $lower = $charge['lower_limit'] !== null ? floatval($charge['lower_limit']) : -INF;
            $upper = $charge['upper_limit'] !== null ? floatval($charge['upper_limit']) : INF;
            
            if ($loanAmount >= $lower && $loanAmount <= $upper) {
                $chargeAmount = $this->calculateChargeAmount($charge, $loanAmount, $totalMonths, $rate);
                
                $chargeData = [
                    'charge_id' => $charge['id'],
                    'charge_type' => $charge['charge_type'],
                    'charge_amount' => round($chargeAmount, 2),
                    'value_type' => $charge['value_type'],
                    'charge_value' => $charge['charge_value'],
                    'calculation_method' => $charge['calculation_method'],
                    'description' => $charge['description'] ?? null,
                    'has_minimum' => $charge['has_minimum'],
                    'minimum_amount' => $charge['minimum_amount'],
                    'has_phcf' => $charge['has_phcf'],
                    'phcf_percentage' => $charge['phcf_percentage'],
                    'formula_expression' => $charge['formula_expression'],
                    'formula_code' => $charge['formula_code'],
                    'charge_application' => $charge['charge_application']
                ];
                
                $application = $charge['charge_application'];
                
                if ($application === CHARGE_TYPE_UPFRONT) {
                    $upfrontCharges[] = $chargeData;
                    $totalUpfront += $chargeAmount;
                } else {
                    $topupCharges[] = $chargeData;
                    $totalTopup += $chargeAmount;
                }
                
                // Store in loan_charges table
                $this->db->execute("
                    INSERT INTO loan_charges (
                        loan_id, charge_id, charge_type, charge_amount,
                        value_type, calculation_method, description,
                        has_minimum, minimum_amount, has_phcf, phcf_percentage,
                        formula_expression, formula_code,
                        lower_limit, upper_limit,  status, created_at
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,  'pending', NOW())
                ", [
                    $loanId,
                    $charge['id'],
                    $charge['charge_type'],
                    round($chargeAmount, 2),
                    $charge['value_type'],
                    $charge['calculation_method'],
                    $charge['description'] ?? '',
                    $charge['has_minimum'] ?? 0,
                    $charge['minimum_amount'] ?? 0,
                    $charge['has_phcf'] ?? 0,
                    $charge['phcf_percentage'] ?? 0,
                    $charge['formula_expression'] ?? null,
                    $charge['formula_code'] ?? null,
                    $charge['lower_limit'] ?? null,
                    $charge['upper_limit'] ?? null,
                  
                ]);
            }
        }
        
        $netDisbursement = $loanAmount - $totalUpfront;
        $totalRepaymentAmount = $loanAmount + $totalTopup;
        
        // Update loan with charges breakdown
        $this->db->execute("
            UPDATE loans 
            SET total_charges = ?,
                upfront_charges = ?,
                topup_charges = ?,
                net_disbursement = ?,
                total_repayment_amount = ?
            WHERE id = ?
        ", [
            round($totalUpfront + $totalTopup, 2),
            round($totalUpfront, 2),
            round($totalTopup, 2),
            round($netDisbursement, 2),
            round($totalRepaymentAmount, 2),
            $loanId
        ]);
        
        return [
            'upfront_charges' => $upfrontCharges,
            'topup_charges' => $topupCharges,
            'total_upfront' => round($totalUpfront, 2),
            'total_topup' => round($totalTopup, 2),
            'total_charges' => round($totalUpfront + $totalTopup, 2),
            'net_disbursement' => round($netDisbursement, 2),
            'total_repayment_amount' => round($totalRepaymentAmount, 2)
        ];
    }
    
    /**
     * Preview charges without saving to database
     */
    public function previewCharges($accountTypeId, $loanAmount, $loanPeriodMonths, $rate = 0) {
        $totalMonths = intval($loanPeriodMonths);
        $charges = $this->getLoanCharges($accountTypeId);
        
        $upfrontCharges = [];
        $topupCharges = [];
        $totalUpfront = 0;
        $totalTopup = 0;
        
        foreach ($charges as $charge) {
            $lower = $charge['lower_limit'] !== null ? floatval($charge['lower_limit']) : -INF;
            $upper = $charge['upper_limit'] !== null ? floatval($charge['upper_limit']) : INF;
            
            if ($loanAmount >= $lower && $loanAmount <= $upper) {
                $chargeAmount = $this->calculateChargeAmount($charge, $loanAmount, $totalMonths, $rate);
                
                $chargeData = [
                    'charge_id' => $charge['id'],
                    'charge_type' => $charge['charge_type'],
                    'charge_amount' => round($chargeAmount, 2),
                    'value_type' => $charge['value_type'],
                    'charge_value' => $charge['charge_value'],
                    'calculation_method' => $charge['calculation_method'],
                    'description' => $charge['description'] ?? null,
                    'has_minimum' => $charge['has_minimum'],
                    'minimum_amount' => $charge['minimum_amount'],
                    'has_phcf' => $charge['has_phcf'],
                    'phcf_percentage' => $charge['phcf_percentage'],
                    'formula_expression' => $charge['formula_expression'],
                    'formula_code' => $charge['formula_code'],
                    'charge_application' => $charge['charge_application']
                ];
                
                $application = $charge['charge_application'];
                
                if ($application === CHARGE_TYPE_UPFRONT) {
                    $upfrontCharges[] = $chargeData;
                    $totalUpfront += $chargeAmount;
                } else {
                    $topupCharges[] = $chargeData;
                    $totalTopup += $chargeAmount;
                }
            }
        }
        
        $netDisbursement = $loanAmount - $totalUpfront;
        $totalRepaymentAmount = $loanAmount + $totalTopup;
        
        // Calculate interest on the loan amount (not including topup charges)
        $interestAmount = ($loanAmount * $rate * $totalMonths) / (12 * 100);
        $totalPayable = $totalRepaymentAmount + $interestAmount;
        $monthlyInstallment = $totalPayable / $totalMonths;
        
        return [
            'upfront_charges' => $upfrontCharges,
            'topup_charges' => $topupCharges,
            'total_upfront' => round($totalUpfront, 2),
            'total_topup' => round($totalTopup, 2),
            'total_charges' => round($totalUpfront + $totalTopup, 2),
            'loan_amount' => round($loanAmount, 2),
            'net_disbursement' => round($netDisbursement, 2),
            'total_repayment_amount' => round($totalRepaymentAmount, 2),
            'interest_amount' => round($interestAmount, 2),
            'total_payable' => round($totalPayable, 2),
            'monthly_installment' => round($monthlyInstallment, 2),
            'explanation' => [
                'upfront' => 'Upfront charges are deducted from your loan disbursement. You will receive the net amount.',
                'topup' => 'Top-up charges are added to your loan repayment. You will repay the loan amount plus these charges.'
            ]
        ];
    }
}

// ============================================
// ENHANCED NOTIFICATION FUNCTION WITH FULL DETAILS
// ============================================

/**
 * Send comprehensive loan application confirmation email with all details
 */
function sendLoaneeApplicationNotification($pdo, $memberId, $loanData) {
    try {
        $db = new DatabaseWrapper($pdo);
        
        $member = $db->fetchOne("
            SELECT first_name, last_name, email_address, phone_number, communication_preference
            FROM members WHERE id = ?
        ", [$memberId]);
        
        if (!$member) {
            error_log("Member not found for notification: " . $memberId);
            return false;
        }
        
        $subject = "Loan Application Received - #{$loanData['loan_number']}";
        
        // Build upfront charges HTML
        $upfrontHtml = '';
        if (isset($loanData['upfront_charges']) && !empty($loanData['upfront_charges'])) {
            $upfrontHtml = "<div style='margin: 15px 0; padding: 10px; background-color: #fff3e0; border-left: 4px solid #ff9800;'>
                            <h4 style='color: #e65100; margin: 0 0 10px 0;'>📋 Upfront Charges (Deducted from Disbursement)</h4>
                            <table style='width:100%; border-collapse: collapse;'>
                            <thead><tr style='background-color: #ffe0b2;'>
                                <th style='padding: 8px; border: 1px solid #ddd; text-align: left;'>Charge Type</th>
                                <th style='padding: 8px; border: 1px solid #ddd; text-align: right;'>Amount (Ksh)</th>
                            <tr></thead><tbody>";
            foreach ($loanData['upfront_charges'] as $charge) {
                $upfrontHtml .= "<tr>";
                $upfrontHtml .= "<td style='padding: 8px; border: 1px solid #ddd;'>" . htmlspecialchars($charge['charge_type']) . "</td>";
                $upfrontHtml .= "<td style='padding: 8px; border: 1px solid #ddd; text-align: right;'>" . number_format($charge['charge_amount'], 2) . "</td>";
                $upfrontHtml .= "</tr>";
            }
            $upfrontHtml .= "<tr style='background-color: #ffe0b2; font-weight: bold;'>
                                <td style='padding: 8px; border: 1px solid #ddd;'>Total Upfront</td>
                                <td style='padding: 8px; border: 1px solid #ddd; text-align: right;'>" . number_format($loanData['total_upfront'] ?? 0, 2) . "</td>
                              </tr>";
            $upfrontHtml .= "</tbody></table></div>";
        }
        
        // Build topup charges HTML
        $topupHtml = '';
        if (isset($loanData['topup_charges']) && !empty($loanData['topup_charges'])) {
            $topupHtml = "<div style='margin: 15px 0; padding: 10px; background-color: #e3f2fd; border-left: 4px solid #2196f3;'>
                          <h4 style='color: #0d47a1; margin: 0 0 10px 0;'>➕ Top-up Charges (Added to Repayment)</h4>
                          <table style='width:100%; border-collapse: collapse;'>
                          <thead><tr style='background-color: #bbdefb;'>
                              <th style='padding: 8px; border: 1px solid #ddd; text-align: left;'>Charge Type</th>
                              <th style='padding: 8px; border: 1px solid #ddd; text-align: right;'>Amount (Ksh)</th>
                          </tr></thead><tbody>";
            foreach ($loanData['topup_charges'] as $charge) {
                $topupHtml .= "<tr>";
                $topupHtml .= "<td style='padding: 8px; border: 1px solid #ddd;'>" . htmlspecialchars($charge['charge_type']) . "</td>";
                $topupHtml .= "<td style='padding: 8px; border: 1px solid #ddd; text-align: right;'>" . number_format($charge['charge_amount'], 2) . "</td>";
                $topupHtml .= "</tr>";
            }
            $topupHtml .= "<tr style='background-color: #bbdefb; font-weight: bold;'>
                              <td style='padding: 8px; border: 1px solid #ddd;'>Total Top-up</td>
                              <td style='padding: 8px; border: 1px solid #ddd; text-align: right;'>" . number_format($loanData['total_topup'] ?? 0, 2) . "</td>
                            </tr>";
            $topupHtml .= "</tbody></table></div>";
        }
        
        // Build guarantors HTML
        $guarantorsHtml = '';
        if (isset($loanData['guarantors_added']) && !empty($loanData['guarantors_added'])) {
            $guarantorsHtml = "<div style='margin: 15px 0; padding: 10px; background-color: #e8f5e9; border-left: 4px solid #4caf50;'>
                               <h4 style='color: #1b5e20; margin: 0 0 10px 0;'>👥 Guarantor Details</h4>
                               <table style='width:100%; border-collapse: collapse;'>
                               <thead><tr style='background-color: #c8e6c9;'>
                                   <th style='padding: 8px; border: 1px solid #ddd; text-align: left;'>Name</th>
                                   <th style='padding: 8px; border: 1px solid #ddd; text-align: right;'>Guarantee Amount (Ksh)</th>
                                   <th style='padding: 8px; border: 1px solid #ddd; text-align: left;'>Status</th>
                                </tr></thead><tbody>";
            foreach ($loanData['guarantors_added'] as $guarantor) {
                $guarantorsHtml .= "<tr>";
                $guarantorsHtml .= "<td style='padding: 8px; border: 1px solid #ddd;'>" . htmlspecialchars($guarantor['name']) . "</td>";
                $guarantorsHtml .= "<td style='padding: 8px; border: 1px solid #ddd; text-align: right;'>" . number_format($guarantor['amount'], 2) . "</td>";
                $guarantorsHtml .= "<td style='padding: 8px; border: 1px solid #ddd;'><span style='color: #ff9800;'>Pending Approval</span></td>";
                $guarantorsHtml .= "</tr>";
            }
            $guarantorsHtml .= "<tr style='background-color: #c8e6c9; font-weight: bold;'>
                                <td style='padding: 8px; border: 1px solid #ddd;'>Total External Guarantee</td>
                                <td style='padding: 8px; border: 1px solid #ddd; text-align: right;'>" . number_format($loanData['external_guarantee'] ?? 0, 2) . "</td>
                                <td style='padding: 8px; border: 1px solid #ddd;'></td>
                              </tr>";
            $guarantorsHtml .= "</tbody></table></div>";
        }
        
        $htmlBody = "
            <html>
            <head>
                <style>
                    body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; }
                    .container { max-width: 700px; margin: 0 auto; padding: 20px; }
                    .header { background: linear-gradient(135deg, #4CAF50, #45a049); color: white; padding: 20px; text-align: center; border-radius: 8px 8px 0 0; }
                    .content { padding: 25px; background-color: #ffffff; border: 1px solid #ddd; border-top: none; border-radius: 0 0 8px 8px; }
                    .section-title { color: #4CAF50; border-bottom: 2px solid #4CAF50; padding-bottom: 8px; margin-top: 20px; }
                    .amount { font-size: 24px; color: #4CAF50; font-weight: bold; }
                    .disbursement { font-size: 22px; color: #ff9800; font-weight: bold; }
                    .repayment { font-size: 22px; color: #2196f3; font-weight: bold; }
                    .footer { margin-top: 25px; padding-top: 15px; font-size: 11px; color: #999; text-align: center; border-top: 1px solid #eee; }
                    table { width: 100%; border-collapse: collapse; margin: 10px 0; }
                    td { padding: 10px; border-bottom: 1px solid #eee; }
                    .label { font-weight: bold; width: 45%; background-color: #f5f5f5; }
                    .coverage-box { background-color: #e8f5e9; padding: 15px; border-radius: 8px; margin: 15px 0; border-left: 4px solid #4CAF50; }
                    .warning-box { background-color: #fff3e0; padding: 15px; border-radius: 8px; margin: 15px 0; border-left: 4px solid #ff9800; }
                    .info-box { background-color: #e3f2fd; padding: 15px; border-radius: 8px; margin: 15px 0; border-left: 4px solid #2196f3; }
                    .badge { display: inline-block; padding: 3px 8px; border-radius: 4px; font-size: 12px; font-weight: bold; }
                    .badge-pending { background-color: #ff9800; color: white; }
                </style>
            </head>
            <body>
                <div class='container'>
                    <div class='header'>
                        <h2 style='margin: 0;'>✅ Loan Application Received</h2>
                        <p style='margin: 5px 0 0; opacity: 0.9;'>Reference: #{$loanData['loan_number']}</p>
                    </div>
                    <div class='content'>
                        <p>Dear <strong>{$member['first_name']} {$member['last_name']}</strong>,</p>
                        <p>Thank you for submitting your loan application. We have received it and it is now being processed.</p>
                        
                        <h3 class='section-title'>📋 Loan Summary</h3>
                        <table>
                            <tr><td class='label'>Loan Number:</td><td><strong>{$loanData['loan_number']}</strong></td></tr>
                            <tr><td class='label'>Loan Type:</td><td>{$loanData['loan_type']}</td></tr>
                            <tr><td class='label'>Loan Purpose:</td><td>{$loanData['loan_purpose']}</td></tr>
                            <tr><td class='label'>Interest Rate:</td><td>{$loanData['interest_rate']}% p.a.</td></tr>
                            <tr><td class='label'>Loan Period:</td><td>{$loanData['loan_period']} months</td></tr>
                            <tr><td class='label'>Application Date:</td><td>" . date('Y-m-d H:i:s') . "</td></tr>
                            <tr><td class='label'>Status:</td><td><span class='badge badge-pending'>Pending Review</span></td></tr>
                        </table>
                        
                        <h3 class='section-title'>💰 Financial Breakdown</h3>
                        <table>
                            <tr><td class='label'>Requested Loan Amount:</td><td class='amount'>Ksh " . number_format($loanData['loan_amount'], 2) . "</td></tr>
                            <tr><td class='label'>Self Guarantee (from savings):</td><td><strong>Ksh " . number_format($loanData['self_guarantee_amount'] ?? 0, 2) . "</strong></td></tr>
                        </table>
                        
                        {$upfrontHtml}
                        
                        <div class='warning-box'>
                            <strong>🏦 Net Disbursement (What you'll receive):</strong><br>
                            <span class='disbursement'>Ksh " . number_format($loanData['net_disbursement'] ?? 0, 2) . "</span><br>
                            <small>Loan amount minus upfront charges = Amount deposited to your account</small>
                        </div>
                        
                        {$topupHtml}
                        
                        <div class='info-box'>
                            <strong>📊 Repayment Calculation:</strong><br>
                            <strong>Total Repayment Principal:</strong> Ksh " . number_format($loanData['total_repayment_amount'] ?? 0, 2) . "<br>
                            <small>Loan amount + Top-up charges = Amount you will repay (excluding interest)</small>
                        </div>
                        
                        <table>
                            <tr><td class='label'>Interest Amount:</td><td>Ksh " . number_format($loanData['interest_amount'] ?? 0, 2) . "</td></tr>
                            <tr><td class='label'>Total Payable Amount:</td><td class='repayment'>Ksh " . number_format($loanData['total_payable'] ?? 0, 2) . "</td></tr>
                            <tr><td class='label'>Monthly Installment:</td><td><strong>Ksh " . number_format($loanData['monthly_installment'] ?? 0, 2) . "</strong> for " . $loanData['loan_period'] . " months</td></tr>
                        </table>
                        
                        <div class='coverage-box'>
                            <strong>🔒 Loan Coverage Summary</strong><br>
                            • <strong>Self Guarantee:</strong> Ksh " . number_format($loanData['self_guarantee_amount'] ?? 0, 2) . " (from your savings)<br>
                            • <strong>External Guarantee:</strong> Ksh " . number_format($loanData['external_guarantee'] ?? 0, 2) . " (from guarantors)<br>
                            • <strong>Total Coverage:</strong> Ksh " . number_format(($loanData['self_guarantee_amount'] ?? 0) + ($loanData['external_guarantee'] ?? 0), 2) . "<br>
                            • <strong>Coverage Percentage:</strong> " . number_format((($loanData['self_guarantee_amount'] ?? 0) + ($loanData['external_guarantee'] ?? 0)) / ($loanData['loan_amount'] ?? 1) * 100, 2) . "% of loan amount
                        </div>
                        
                        {$guarantorsHtml}
                        
                        <h3 class='section-title'>📌 Next Steps</h3>
                        <ol>
                            <li>Our team will review your application within 2-3 business days</li>
                            <li>Guarantors will receive notifications to approve the guarantee requests</li>
                            <li>You can track the status of your application in your member portal</li>
                            <li>Once approved, funds will be disbursed to your nominated account</li>
                        </ol>
                        
                        <div class='info-box'>
                            <strong>💡 Important Notes:</strong><br>
                            • <strong>Upfront charges</strong> are deducted from your loan before disbursement<br>
                            • <strong>Top-up charges</strong> are added to your loan repayment amount<br>
                            • <strong>Self-guarantee</strong> uses your savings as collateral for the loan<br>
                            • <strong>Guarantors</strong> must approve the request before the loan can be approved<br>
                            • The monthly installment shown includes both principal and interest
                        </div>
                        
                        <p>If you have any questions, please contact our customer support team.</p>
                        <p>Thank you for choosing our services!</p>
                    </div>
                    <div class='footer'>
                        <p>This is an automated message, please do not reply to this email.</p>
                        <p>&copy; " . date('Y') . " Loan Management System. All rights reserved.</p>
                    </div>
                </div>
            </body>
            </html>
        ";
        
        $textBody = "Dear {$member['first_name']} {$member['last_name']},\n\n";
        $textBody .= "Thank you for submitting your loan application #{$loanData['loan_number']}.\n\n";
        $textBody .= "Loan Summary:\n";
        $textBody .= "- Loan Amount: Ksh " . number_format($loanData['loan_amount'], 2) . "\n";
        $textBody .= "- Self Guarantee: Ksh " . number_format($loanData['self_guarantee_amount'] ?? 0, 2) . "\n";
        $textBody .= "- External Guarantee: Ksh " . number_format($loanData['external_guarantee'] ?? 0, 2) . "\n";
        $textBody .= "- Upfront Charges: Ksh " . number_format($loanData['total_upfront'] ?? 0, 2) . "\n";
        $textBody .= "- Top-up Charges: Ksh " . number_format($loanData['total_topup'] ?? 0, 2) . "\n";
        $textBody .= "- Net Disbursement: Ksh " . number_format($loanData['net_disbursement'] ?? 0, 2) . "\n";
        $textBody .= "- Total Payable: Ksh " . number_format($loanData['total_payable'] ?? 0, 2) . "\n";
        $textBody .= "- Monthly Installment: Ksh " . number_format($loanData['monthly_installment'] ?? 0, 2) . " for " . $loanData['loan_period'] . " months\n\n";
        $textBody .= "Guarantors Added: " . count($loanData['guarantors_added'] ?? []) . "\n\n";
        $textBody .= "Next Steps:\n";
        $textBody .= "1. Our team will review your application\n";
        $textBody .= "2. Guarantors will be notified to approve\n";
        $textBody .= "3. Once approved, funds will be disbursed\n\n";
        $textBody .= "Thank you for choosing our services!";
        
        $preference = $member['communication_preference'] ?? 'both';
        $emailSent = false;
        $smsSent = false;
        
        if (($preference === 'email' || $preference === 'both') && !empty($member['email_address'])) {
            $emailSent = sendLoanEmail($member['email_address'], $subject, $htmlBody, $textBody);
            error_log("Loanee notification email sent: " . ($emailSent ? 'Success' : 'Failed'));
        }
        
        if (($preference === 'sms' || $preference === 'both') && !empty($member['phone_number'])) {
            $smsMessage = "Dear {$member['first_name']}, loan #{$loanData['loan_number']} received.\n" .
                         "Loan: Ksh " . number_format($loanData['loan_amount'], 2) . "\n" .
                         "Self-Guarantee: Ksh " . number_format($loanData['self_guarantee_amount'] ?? 0, 2) . "\n" .
                         "Net Disbursement: Ksh " . number_format($loanData['net_disbursement'] ?? 0, 2) . "\n" .
                         "Monthly: Ksh " . number_format($loanData['monthly_installment'] ?? 0, 2) . " x " . $loanData['loan_period'] . " months\n" .
                         "Login to portal for details.";
            $smsSent = sendLoanSMS($member['phone_number'], $smsMessage);
            error_log("Loanee notification SMS sent: " . ($smsSent ? 'Success' : 'Failed'));
        }
        
        return ($emailSent || $smsSent);
        
    } catch (Exception $e) {
        error_log("Send loanee notification error: " . $e->getMessage());
        return false;
    }
}

/**
 * Send guarantor request notification
 */
function sendGuarantorRequestNotification($pdo, $guarantorId, $loanData, $applicantName, $guaranteeAmount) {
    try {
        $db = new DatabaseWrapper($pdo);
        
        $guarantor = $db->fetchOne("
            SELECT first_name, last_name, email_address, phone_number, communication_preference
            FROM members WHERE id = ?
        ", [$guarantorId]);
        
        if (!$guarantor) {
            error_log("Guarantor not found for notification: " . $guarantorId);
            return false;
        }
        
        $subject = "Guarantor Request - Loan #{$loanData['loan_number']}";
        
        $htmlBody = "
            <html>
            <head>
                <style>
                    body { font-family: Arial, sans-serif; line-height: 1.6; }
                    .container { max-width: 600px; margin: 0 auto; padding: 20px; }
                    .header { background-color: #2196F3; color: white; padding: 15px; text-align: center; border-radius: 5px; }
                    .content { padding: 20px; background-color: #f9f9f9; border-radius: 5px; margin-top: 15px; }
                    .amount { font-size: 24px; color: #2196F3; font-weight: bold; }
                    .warning { background-color: #fff3cd; padding: 15px; border-left: 4px solid #ffc107; margin: 15px 0; }
                    .footer { margin-top: 20px; font-size: 12px; color: #666; text-align: center; }
                    table { width: 100%; border-collapse: collapse; }
                    td { padding: 8px; border-bottom: 1px solid #ddd; }
                    .label { font-weight: bold; width: 40%; }
                </style>
            </head>
            <body>
                <div class='container'>
                    <div class='header'>
                        <h2>Loan Guarantor Request</h2>
                    </div>
                    <div class='content'>
                        <p>Dear {$guarantor['first_name']} {$guarantor['last_name']},</p>
                        <p><strong>{$applicantName}</strong> has requested you to act as a guarantor for their loan application.</p>
                        
                        <h3>Loan Details:</h3>
                        <table>
                            <tr><td class='label'>Loan Number:</td><td><strong>{$loanData['loan_number']}</strong></strong></td></tr>
                            <tr><td class='label'>Loan Amount:</td><td class='amount'>Ksh " . number_format($loanData['loan_amount'], 2) . "</strong></td></tr>
                            <tr><td class='label'>Your Guarantee Amount:</td><td style='color: #f44336; font-weight: bold;'>Ksh " . number_format($guaranteeAmount, 2) . "</strong></td></tr>
                            <tr><td class='label'>Loan Period:</td><td>{$loanData['loan_period']} months</strong></td></tr>
                            <tr><td class='label'>Interest Rate:</td><td>{$loanData['interest_rate']}% p.a.</strong></td></tr>
                            <tr><td class='label'>Monthly Installment:</td><td>Ksh " . number_format($loanData['monthly_installment'] ?? 0, 2) . "</strong></td></tr>
                        </table>
                        
                        <div class='warning'>
                            <strong>⚠️ Your Obligation as a Guarantor:</strong><br>
                            • Your savings account will be used as collateral for this guarantee<br>
                            • The guaranteed amount (Ksh " . number_format($guaranteeAmount, 2) . ") will be locked until the loan is fully repaid<br>
                            • If the borrower defaults, you will be responsible for repaying this amount<br>
                            • You are required to respond to this request within 7 days<br>
                            • Failure to respond will result in automatic decline
                        </div>
                        
                        <p><strong>Please log in to your member portal to review and respond to this guarantor request.</strong></p>
                        <p>Thank you for your support!</p>
                    </div>
                    <div class='footer'>
                        <p>This is an automated message, please do not reply to this email.</p>
                    </div>
                </div>
            </body>
            </html>
        ";
        
        $smsMessage = "GUARANTOR REQUEST: {$applicantName} requests you to guarantee loan #{$loanData['loan_number']} of Ksh " . 
                     number_format($loanData['loan_amount'], 2) . " for {$loanData['loan_period']} months. " .
                     "Your guarantee amount: Ksh " . number_format($guaranteeAmount, 2) . ". " .
                     "Login to your portal to respond.";
        
        $preference = $guarantor['communication_preference'] ?? 'both';
        $emailSent = false;
        $smsSent = false;
        
        if (($preference === 'email' || $preference === 'both') && !empty($guarantor['email_address'])) {
            $emailSent = sendLoanEmail($guarantor['email_address'], $subject, $htmlBody);
            error_log("Guarantor request email sent: " . ($emailSent ? 'Success' : 'Failed'));
        }
        
        if (($preference === 'sms' || $preference === 'both') && !empty($guarantor['phone_number'])) {
            $smsSent = sendLoanSMS($guarantor['phone_number'], $smsMessage);
            error_log("Guarantor request SMS sent: " . ($smsSent ? 'Success' : 'Failed'));
        }
        
        // Add internal notification
        try {
            $stmt = $pdo->prepare("
                INSERT INTO member_internal_notifications 
                (member_id, title, message, type, status, created_at)
                VALUES (?, ?, ?, 'guarantor_request', 'unread', NOW())
            ");
            $stmt->execute([
                $guarantorId,
                "Loan Guarantor Request",
                "{$applicantName} has requested you to guarantee a loan of Ksh " . number_format($loanData['loan_amount'], 2)
            ]);
        } catch (Exception $e) {
            error_log("Failed to add internal notification: " . $e->getMessage());
        }
        
        return ($emailSent || $smsSent);
        
    } catch (Exception $e) {
        error_log("Send guarantor request notification error: " . $e->getMessage());
        return false;
    }
}

// ============================================
// SMTP AND EMAIL HELPER FUNCTIONS
// ============================================

/**
 * Get SMTP settings from JSON file or database
 */
function getLoanSmtpSettings($pdo) {
    $jsonFilePath = __DIR__ . '/smtp_settings_data.json';
    
    if (file_exists($jsonFilePath)) {
        $json = file_get_contents($jsonFilePath);
        $settings = json_decode($json, true);
        
        if (json_last_error() === JSON_ERROR_NONE && !empty($settings['smtp_host'])) {
            return $settings;
        }
    }
    
    try {
        $stmt = $pdo->query("SELECT * FROM email_settings LIMIT 1");
        $dbSettings = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($dbSettings) {
            return [
                'smtp_host' => $dbSettings['smtp_host'],
                'smtp_username' => $dbSettings['smtp_username'],
                'smtp_password' => $dbSettings['smtp_password'],
                'smtp_port' => $dbSettings['smtp_port'],
                'smtp_encryption' => $dbSettings['smtp_encryption'],
                'from_name' => $dbSettings['from_name']
            ];
        }
    } catch (Exception $e) {
        error_log("Database SMTP settings failed: " . $e->getMessage());
    }
    
    return [
        'smtp_host' => 'smtp.gmail.com',
        'smtp_username' => '',
        'smtp_password' => '',
        'smtp_port' => 587,
        'smtp_encryption' => 'tls',
        'from_name' => 'Loan System'
    ];
}

/**
 * Send email notification using PHPMailer
 */
function sendLoanEmail($recipientEmail, $subject, $htmlBody, $textBody = null) {
    try {
        global $pdo;
        $smtpSettings = getLoanSmtpSettings($pdo);
        
        if (empty($smtpSettings['smtp_username']) || empty($smtpSettings['smtp_password'])) {
            error_log("SMTP not configured: Missing credentials");
            return false;
        }
        
        $mail = new PHPMailer(true);
        
        $mail->isSMTP();
        $mail->Host = $smtpSettings['smtp_host'];
        $mail->SMTPAuth = true;
        $mail->Username = $smtpSettings['smtp_username'];
        $mail->Password = $smtpSettings['smtp_password'];
        $mail->SMTPSecure = $smtpSettings['smtp_encryption'];
        $mail->Port = $smtpSettings['smtp_port'];
        
        $mail->SMTPOptions = array(
            'ssl' => array(
                'verify_peer' => false,
                'verify_peer_name' => false,
                'allow_self_signed' => true
            )
        );
        
        $mail->setFrom($smtpSettings['smtp_username'], $smtpSettings['from_name'] ?? 'Loan System');
        $mail->addAddress($recipientEmail);
        
        $mail->isHTML(true);
        $mail->Subject = $subject;
        $mail->Body = $htmlBody;
        $mail->AltBody = $textBody ?? strip_tags($htmlBody);
        
        return $mail->send();
        
    } catch (Exception $e) {
        error_log("Loan Email Failed: " . $e->getMessage());
        return false;
    }
}

/**
 * Send SMS notification
 */
function sendLoanSMS($phoneNumber, $message) {
    try {
        if (empty($phoneNumber)) {
            error_log("No phone number provided for SMS");
            return false;
        }
        
        if (function_exists('sendSms')) {
            global $pdo;
            return sendSms($pdo, $phoneNumber, $message);
        } else {
            error_log("sendSms function not available");
            return false;
        }
    } catch (Exception $e) {
        error_log("Loan SMS Failed: " . $e->getMessage());
        return false;
    }
}

// ============================================
// DATABASE WRAPPER CLASS
// ============================================

class DatabaseWrapper {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function fetchAll($sql, $params = []) {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (PDOException $e) {
            error_log("❌ Database error: " . $e->getMessage());
            throw new Exception('Database error', 500);
        }
    }
    
    public function fetchOne($sql, $params = []) {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
        } catch (PDOException $e) {
            error_log("❌ Database error: " . $e->getMessage());
            throw new Exception('Database error', 500);
        }
    }
    
    public function execute($sql, $params = []) {
        try {
            $stmt = $this->pdo->prepare($sql);
            return $stmt->execute($params);
        } catch (PDOException $e) {
            error_log("❌ Database error: " . $e->getMessage());
            throw new Exception('Database error', 500);
        }
    }
    
    public function insertAndGetId($sql, $params = []) {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $this->pdo->lastInsertId();
        } catch (PDOException $e) {
            error_log("❌ Database error: " . $e->getMessage());
            throw new Exception('Database error', 500);
        }
    }
}

// ============================================
// HELPER FUNCTIONS
// ============================================

function apiLog($message, $level = 'INFO') {
    $logEntry = "[" . date('Y-m-d H:i:s') . "] [$level] " . $message;
    error_log($logEntry);
    
    $logFile = __DIR__ . '/logs/loan-application-api_' . date('Y-m-d') . '.log';
    if (!file_exists(dirname($logFile))) {
        mkdir(dirname($logFile), 0755, true);
    }
    file_put_contents($logFile, $logEntry . PHP_EOL, FILE_APPEND);
}

function maskContactInfo($data, $type) {
    if (empty($data)) return 'Not provided';
    
    switch ($type) {
        case 'phone':
            if (strlen($data) >= 7) {
                $prefix = substr($data, 0, 4);
                $suffix = substr($data, -2);
                return $prefix . '******' . $suffix;
            }
            return substr($data, 0, 4) . '******';
        case 'email':
            $parts = explode('@', $data);
            if (count($parts) === 2) {
                $username = $parts[0];
                $domain = $parts[1];
                if (strlen($username) > 3) {
                    $maskedUsername = substr($username, 0, 3) . '***';
                } else {
                    $maskedUsername = substr($username, 0, 1) . '***';
                }
                return $maskedUsername . '@' . $domain;
            }
            return substr($data, 0, 3) . '***@****';
        case 'id':
            return maskIdNumber($data);
        default:
            return $data;
    }
}

function maskIdNumber($idNumber) {
    if (empty($idNumber) || strlen($idNumber) < 4) {
        return '****';
    }
    $first = substr($idNumber, 0, 2);
    $last = substr($idNumber, -2);
    $length = strlen($idNumber);
    $mask = str_repeat('*', $length - 4);
    return $first . $mask . $last;
}

function getMemberDetails($pdo, $memberId) {
    $db = new DatabaseWrapper($pdo);
    return $db->fetchOne("
        SELECT 
            id, first_name, last_name, member_number, 
            personal_number, phone_number, email_address,
            registration_date, date_of_birth, id_number, status
        FROM members 
        WHERE id = ? AND status = 'active'
    ", [$memberId]);
}

function getMemberSavingsAccounts($pdo, $memberId) {
    $db = new DatabaseWrapper($pdo);
    return $db->fetchAll("
        SELECT 
            ma.id,
            ma.account_number,
            ma.available_balance,
            at.account_name,
            at.account_type,
            at.type,
            ma.sub_account_code
        FROM member_accounts ma
        JOIN account_types at ON ma.account_type_id = at.id
        WHERE ma.member_id = ? 
        AND at.account_type = 'savings'
        AND ma.status = 'active'
        ORDER BY at.account_name
    ", [$memberId]);
}

function getLoanAccountTypes($pdo) {
    $db = new DatabaseWrapper($pdo);
    return $db->fetchAll("
        SELECT 
            at.id, 
            at.account_name, 
            at.type_code,
            at.max_loan_factor,
            at.interest_rate,
            at.min_loan_amount,
            at.max_loan_amount,
            at.description,
            at.self_guarantee_percentage,
            at.basis_savings_account,
            (
                SELECT COUNT(*) 
                FROM account_charges ac 
                WHERE ac.account_type_id = at.id 
                AND ac.status = 'active'
            ) as charges_count
        FROM account_types at
        WHERE at.account_type = 'loan'
        AND at.status = 1
        ORDER BY at.account_name
    ");
}

function getLoanPurposes($pdo) {
    $db = new DatabaseWrapper($pdo);
    return $db->fetchAll("
        SELECT 
            id, 
            purpose_name, 
            purpose_code,
            description
        FROM loan_purposes
        WHERE status = 1
        AND is_selectable = 1
        ORDER BY purpose_name
    ");
}

function calculateMaxEligibility($availableBalance, $loanFactor) {
    return $availableBalance * $loanFactor;
}

function calculateAdvancedLoanCharges($pdo, $loanTypeId, $loanAmount, $loanPeriodMonths, $interestRate = 0) {
    $chargesHelper = new AccountChargesHelper($pdo);
    return $chargesHelper->previewCharges($loanTypeId, $loanAmount, $loanPeriodMonths, $interestRate);
}

function validateLoanApplicationWithCharges($data, $maxEligibility, $chargesInfo) {
    $errors = [];
    
    $required = ['account_type_id', 'savings_account_id', 'loan_purpose_id', 'loan_amount', 'loan_period'];
    foreach ($required as $field) {
        if (!isset($data[$field]) || empty($data[$field])) {
            $errors[] = ucfirst(str_replace('_', ' ', $field)) . " is required";
        }
    }
    
    if (!empty($errors)) {
        return ['valid' => false, 'errors' => $errors];
    }
    
    $loanAmount = (float)$data['loan_amount'];
    if ($loanAmount < 1000) {
        $errors[] = "Minimum loan amount is Ksh 1,000";
    }
    
    $netDisbursement = $loanAmount - $chargesInfo['total_upfront'];
    
    if ($netDisbursement < 0) {
        $errors[] = "Upfront charges (" . number_format($chargesInfo['total_upfront'], 2) . 
                   ") exceed loan amount (" . number_format($loanAmount, 2) . "). Net disbursement would be negative.";
    }
    
    if ($netDisbursement > $maxEligibility) {
        $errors[] = "Net disbursement amount (after upfront charges) exceeds maximum eligibility of Ksh " . 
                   number_format($maxEligibility, 2);
    }
    
    $allowedPeriods = [6, 12, 24, 36];
    $loanPeriod = (int)$data['loan_period'];
    if (!in_array($loanPeriod, $allowedPeriods)) {
        $errors[] = "Invalid loan period selected. Allowed: " . implode(', ', $allowedPeriods) . " months";
    }
    
    if (!empty($errors)) {
        return ['valid' => false, 'errors' => $errors];
    }
    
    return [
        'valid' => true,
        'data' => [
            'account_type_id' => (int)$data['account_type_id'],
            'savings_account_id' => (int)$data['savings_account_id'],
            'loan_purpose_id' => (int)$data['loan_purpose_id'],
            'loan_amount' => $loanAmount,
            'loan_period' => $loanPeriod,
            'total_charges' => $chargesInfo['total_charges'],
            'total_upfront' => $chargesInfo['total_upfront'],
            'total_topup' => $chargesInfo['total_topup'],
            'net_disbursement' => $netDisbursement,
            'total_repayment_amount' => $loanAmount + $chargesInfo['total_topup']
        ]
    ];
}

function calculateGuaranteeRequirements($loanAmount, $availableBalance, $includeCharges = false, $upfrontCharges = 0, $topupCharges = 0) {
    // For guarantee calculation, we consider the actual loan amount that needs to be secured
    $amountToGuarantee = $loanAmount;
    
    $selfGuaranteeAmount = min($amountToGuarantee, $availableBalance);
    $remainingAmount = $amountToGuarantee - $selfGuaranteeAmount;
    $needGuarantors = $remainingAmount > 0;
    
    return [
        'self_guarantee_amount' => $selfGuaranteeAmount,
        'remaining_amount' => $remainingAmount,
        'need_guarantors' => $needGuarantors,
        'available_balance' => $availableBalance,
        'total_amount_considered' => $amountToGuarantee
    ];
}

function generateLoanNumber($pdo = null) {
    $loanNumber = 'LN' . time() . rand(100, 999);
    
    if ($pdo) {
        $db = new DatabaseWrapper($pdo);
        $existing = $db->fetchOne("SELECT id FROM loans WHERE loan_number = ?", [$loanNumber]);
        if ($existing) {
            return generateLoanNumber($pdo);
        }
    }
    
    return $loanNumber;
}

function getEligibleGuaranteeAccountTypes() {
    return ['Non-Withdrawable', 'Withdrawable'];
}

function checkExistingLoanOfSameProduct($pdo, $memberId, $accountTypeId) {
    $db = new DatabaseWrapper($pdo);
    
    $existingLoan = $db->fetchOne("
        SELECT id, loan_number, loan_amount, status, created_at
        FROM loans 
        WHERE member_id = ? 
        AND account_type_id = ? 
        AND status IN ('pending', 'active', 'approved', 'disbursed')
        LIMIT 1
    ", [$memberId, $accountTypeId]);
    
    if ($existingLoan) {
        return [
            'has_existing' => true,
            'loan' => $existingLoan
        ];
    }
    
    return ['has_existing' => false];
}

function verifyGuarantor($pdo, $memberId, $searchTerm, $guaranteeAmount) {
    apiLog("=== VERIFY GUARANTOR START ===");
    apiLog("Applicant Member ID: " . $memberId);
    apiLog("Search Term: " . $searchTerm);
    apiLog("Guarantee Amount: " . $guaranteeAmount);
    
    $db = new DatabaseWrapper($pdo);
    
    try {
        $searchTerm = trim($searchTerm);
        apiLog("Cleaned search term: '" . $searchTerm . "'");
        
        if (empty($searchTerm)) {
            return ['success' => false, 'message' => "Please enter a search term"];
        }
        
        if ($guaranteeAmount < 100) {
            return ['success' => false, 'message' => "Minimum guarantee amount is Ksh 100"];
        }
        
        $searchStrategies = [
            'personal_number' => "personal_number = ?",
            'member_number' => "member_number = ?",
            'id_number' => "id_number = ?",
            'phone_number' => "phone_number = ?"
        ];
        
        $guarantor = null;
        $foundBy = null;
        
        foreach ($searchStrategies as $field => $condition) {
            $query = "
                SELECT 
                    id, first_name, last_name, email_address, phone_number,
                    personal_number, member_number, id_number, date_of_birth,
                    registration_date, status, communication_preference
                FROM members 
                WHERE $condition 
                AND id != ?
                AND status = 'active'
            ";
            
            $result = $db->fetchOne($query, [$searchTerm, $memberId]);
            
            if ($result) {
                apiLog("✓ Found by $field: " . json_encode($result));
                $guarantor = $result;
                $foundBy = $field;
                break;
            }
        }
        
        if (!$guarantor) {
            $selfQuery = "
                SELECT id, first_name, last_name 
                FROM members 
                WHERE (personal_number = ? OR member_number = ? OR id_number = ? OR phone_number = ?)
                AND id = ?
            ";
            $selfResult = $db->fetchOne($selfQuery, [$searchTerm, $searchTerm, $searchTerm, $searchTerm, $memberId]);
            
            if ($selfResult) {
                apiLog("Member found but it's the applicant themselves");
                return ['success' => false, 'message' => "Cannot use yourself as a guarantor"];
            }
            
            apiLog("❌ No matching member found");
            return ['success' => false, 'message' => "Member not found. Please check the member number, ID number, or phone number."];
        }
        
        apiLog("Guarantor ID: " . $guarantor['id']);
        apiLog("Guarantor Name: " . $guarantor['first_name'] . ' ' . $guarantor['last_name']);
        
        $savingsQuery = "
            SELECT 
                ma.id,
                ma.account_number,
                ma.available_balance,
                at.account_name,
                at.account_type,
                at.type,
                ma.sub_account_code
            FROM member_accounts ma
            JOIN account_types at ON ma.account_type_id = at.id
            WHERE ma.member_id = ? 
            AND at.account_type = 'savings'
            AND ma.status = 'active'
            AND at.type = 'Non-Withdrawable'
            ORDER BY ma.available_balance DESC
        ";
        
        $savingsAccounts = $db->fetchAll($savingsQuery, [$guarantor['id']]);
        
        apiLog("Found " . count($savingsAccounts) . " eligible savings accounts");
        
        if (empty($savingsAccounts)) {
            $eligibleTypes = getEligibleGuaranteeAccountTypes();
            $accountNames = implode(' or ', $eligibleTypes);
            
            return [
                'success' => false,
                'message' => "Member has no eligible savings accounts for guarantees. " .
                           "Only " . $accountNames . " accounts can be used for guarantees.",
                'eligible_account_types' => $eligibleTypes
            ];
        }
        
        $eligibleAccount = null;
        
        foreach ($savingsAccounts as $account) {
            if ($account['available_balance'] >= $guaranteeAmount) {
                $eligibleAccount = $account;
                break;
            }
        }
        
        if (!$eligibleAccount) {
            $highestAccount = $savingsAccounts[0];
            $eligibleTypes = getEligibleGuaranteeAccountTypes();
            $accountNames = implode(' or ', $eligibleTypes);
            
            return [
                'success' => false,
                'message' => "Insufficient savings balance in eligible accounts. " .
                           "Only " . $accountNames . " accounts can be used for guarantees. " .
                           "Available in " . $highestAccount['account_name'] . " (" . $highestAccount['type'] . "): Ksh " . 
                           number_format($highestAccount['available_balance'], 2) . 
                           ", Required: Ksh " . number_format($guaranteeAmount, 2),
                'eligible_account_types' => $eligibleTypes,
                'available_balance' => $highestAccount['available_balance'],
                'account_name' => $highestAccount['account_name'],
                'account_type' => $highestAccount['type']
            ];
        }
        
        apiLog("✓ Eligible account found: " . $eligibleAccount['account_name']);
        
        $age = null;
        if (!empty($guarantor['date_of_birth'])) {
            $dob = new DateTime($guarantor['date_of_birth']);
            $today = new DateTime();
            $age = $today->diff($dob)->y;
        }
        
        apiLog("=== VERIFY GUARANTOR END - SUCCESS ===");
        
        return [
            'success' => true,
            'guarantor' => [
                'id' => $guarantor['id'],
                'search_type' => $foundBy,
                'name' => $guarantor['first_name'] . ' ' . $guarantor['last_name'],
                'email' => maskContactInfo($guarantor['email_address'], 'email'),
                'phone' => maskContactInfo($guarantor['phone_number'], 'phone'),
                'personal_number' => $guarantor['personal_number'],
                'member_number' => $guarantor['member_number'],
                'id_number' => maskContactInfo($guarantor['id_number'], 'id'),
                'age' => $age,
                'registration_date' => $guarantor['registration_date'],
                'available_balance' => $eligibleAccount['available_balance'],
                'account_name' => $eligibleAccount['account_name'],
                'account_type' => $eligibleAccount['type'],
                'account_number' => $eligibleAccount['account_number'],
                'account_id' => $eligibleAccount['id'],
                'sub_account_code' => $eligibleAccount['sub_account_code'],
                'eligible_for_guarantee' => true
            ]
        ];
        
    } catch (Exception $e) {
        apiLog("❌ Database Exception: " . $e->getMessage(), 'ERROR');
        return ['success' => false, 'message' => "Database error occurred while verifying guarantor"];
    }
}

// ============================================
// REQUEST HANDLERS
// ============================================

function handleGetRequest($pdo, $memberId, $action) {
    apiLog("Processing GET request with action: " . ($action ?? 'none'));
    
    switch ($action) {
        case 'loan_data':
            try {
                $member = getMemberDetails($pdo, $memberId);
                $savingsAccounts = getMemberSavingsAccounts($pdo, $memberId);
                $loanAccountTypes = getLoanAccountTypes($pdo);
                $loanPurposes = getLoanPurposes($pdo);
                
                if (!$member) {
                    http_response_code(404);
                    echo json_encode(['success' => false, 'error' => 'Member not found or inactive']);
                    return;
                }
                
                echo json_encode([
                    'success' => true,
                    'data' => [
                        'member' => $member,
                        'savings_accounts' => $savingsAccounts,
                        'loan_account_types' => $loanAccountTypes,
                        'loan_purposes' => $loanPurposes
                    ]
                ], JSON_NUMERIC_CHECK);
                
                apiLog("Loan data retrieved successfully for member: " . $memberId);
                
            } catch (Exception $e) {
                apiLog("Failed to fetch loan data: " . $e->getMessage(), 'ERROR');
                http_response_code(500);
                echo json_encode([
                    'success' => false,
                    'error' => 'Failed to fetch loan data',
                    'message' => (defined('ENVIRONMENT') && ENVIRONMENT === 'development') ? $e->getMessage() : null
                ]);
            }
            break;
            
        case 'eligibility':
            $savingsAccountId = $_GET['savings_account_id'] ?? null;
            $accountTypeId = $_GET['account_type_id'] ?? null;
            
            if (!$savingsAccountId || !$accountTypeId) {
                http_response_code(400);
                echo json_encode([
                    'success' => false,
                    'error' => 'Savings account ID and account type ID are required'
                ]);
                return;
            }
            
            try {
                $db = new DatabaseWrapper($pdo);
                
                $account = $db->fetchOne("
                    SELECT ma.available_balance, at.account_name, at.type
                    FROM member_accounts ma
                    JOIN account_types at ON ma.account_type_id = at.id
                    WHERE ma.id = ? AND ma.member_id = ?
                ", [$savingsAccountId, $memberId]);
                
                if (!$account) {
                    http_response_code(404);
                    echo json_encode(['success' => false, 'error' => 'Savings account not found']);
                    return;
                }
                
                $loanType = $db->fetchOne("
                    SELECT max_loan_factor, account_name, interest_rate
                    FROM account_types 
                    WHERE id = ?
                ", [$accountTypeId]);
                
                if (!$loanType) {
                    http_response_code(404);
                    echo json_encode(['success' => false, 'error' => 'Loan account type not found']);
                    return;
                }
                
                $availableBalance = (float)$account['available_balance'];
                $maxLoanFactor = (float)$loanType['max_loan_factor'];
                $maxEligibility = calculateMaxEligibility($availableBalance, $maxLoanFactor);
                
                echo json_encode([
                    'success' => true,
                    'data' => [
                        'savings_account' => $account['account_name'],
                        'account_type' => $account['type'],
                        'loan_type' => $loanType['account_name'],
                        'interest_rate' => $loanType['interest_rate'],
                        'available_balance' => $availableBalance,
                        'max_loan_factor' => $maxLoanFactor,
                        'max_eligibility' => $maxEligibility,
                        'calculation' => "{$availableBalance} × {$maxLoanFactor}"
                    ]
                ], JSON_NUMERIC_CHECK);
                
            } catch (Exception $e) {
                apiLog("Failed to calculate eligibility: " . $e->getMessage(), 'ERROR');
                http_response_code(500);
                echo json_encode([
                    'success' => false,
                    'error' => 'Failed to calculate eligibility',
                    'message' => (defined('ENVIRONMENT') && ENVIRONMENT === 'development') ? $e->getMessage() : null
                ]);
            }
            break;
            
        case 'guarantee_calculation':
            $loanAmount = $_GET['loan_amount'] ?? null;
            $savingsAccountId = $_GET['savings_account_id'] ?? null;
            
            if (!$loanAmount || !$savingsAccountId) {
                http_response_code(400);
                echo json_encode([
                    'success' => false,
                    'error' => 'Loan amount and savings account ID are required'
                ]);
                return;
            }
            
            try {
                $db = new DatabaseWrapper($pdo);
                
                $account = $db->fetchOne("
                    SELECT available_balance, account_number, 
                           (SELECT type FROM account_types WHERE id = ma.account_type_id) as account_type
                    FROM member_accounts ma
                    WHERE ma.id = ? AND ma.member_id = ?
                ", [$savingsAccountId, $memberId]);
                
                if (!$account) {
                    http_response_code(404);
                    echo json_encode(['success' => false, 'error' => 'Savings account not found']);
                    return;
                }
                
                $loanAmount = (float)$loanAmount;
                $availableBalance = (float)$account['available_balance'];
                
                $guarantee = calculateGuaranteeRequirements($loanAmount, $availableBalance);
                
                echo json_encode([
                    'success' => true,
                    'data' => array_merge($guarantee, [
                        'account_number' => $account['account_number'],
                        'account_type' => $account['account_type']
                    ])
                ], JSON_NUMERIC_CHECK);
                
            } catch (Exception $e) {
                apiLog("Failed to calculate guarantee: " . $e->getMessage(), 'ERROR');
                http_response_code(500);
                echo json_encode([
                    'success' => false,
                    'error' => 'Failed to calculate guarantee requirements',
                    'message' => (defined('ENVIRONMENT') && ENVIRONMENT === 'development') ? $e->getMessage() : null
                ]);
            }
            break;
            
        case 'loan_charges':
            $loanTypeId = $_GET['loan_type_id'] ?? null;
            $loanAmount = $_GET['loan_amount'] ?? null;
            $loanPeriod = $_GET['loan_period'] ?? 12;
            
            if (!$loanTypeId || !$loanAmount) {
                http_response_code(400);
                echo json_encode([
                    'success' => false,
                    'error' => 'Loan type ID and amount are required'
                ]);
                return;
            }
            
            try {
                $db = new DatabaseWrapper($pdo);
                $loanType = $db->fetchOne("SELECT interest_rate FROM account_types WHERE id = ?", [$loanTypeId]);
                $interestRate = $loanType['interest_rate'] ?? 0;
                
                $chargesInfo = calculateAdvancedLoanCharges($pdo, $loanTypeId, (float)$loanAmount, (int)$loanPeriod, $interestRate);
                
                echo json_encode([
                    'success' => true,
                    'data' => $chargesInfo,
                    'interest_rate' => $interestRate,
                    'explanation' => [
                        'upfront' => 'Upfront charges are deducted from disbursement. You will receive: Loan Amount - Upfront Charges',
                        'topup' => 'Top-up charges are added to repayment. You will repay: Loan Amount + Top-up Charges + Interest'
                    ]
                ], JSON_NUMERIC_CHECK);
                
            } catch (Exception $e) {
                apiLog("Failed to calculate charges: " . $e->getMessage(), 'ERROR');
                http_response_code(500);
                echo json_encode([
                    'success' => false,
                    'error' => 'Failed to calculate loan charges',
                    'message' => (defined('ENVIRONMENT') && ENVIRONMENT === 'development') ? $e->getMessage() : null
                ]);
            }
            break;
            
        case 'verify_guarantor':
            $memberNumber = $_GET['member_number'] ?? null;
            $guaranteeAmount = $_GET['guarantee_amount'] ?? null;
            
            if (!$memberNumber || !$guaranteeAmount) {
                http_response_code(400);
                echo json_encode([
                    'success' => false,
                    'error' => 'Member number and guarantee amount are required'
                ]);
                return;
            }
            
            try {
                $result = verifyGuarantor($pdo, $memberId, $memberNumber, (float)$guaranteeAmount);
                
                if ($result['success']) {
                    echo json_encode([
                        'success' => true,
                        'data' => $result['guarantor']
                    ], JSON_NUMERIC_CHECK);
                } else {
                    http_response_code(400);
                    echo json_encode([
                        'success' => false,
                        'error' => $result['message'],
                        'eligible_account_types' => getEligibleGuaranteeAccountTypes()
                    ]);
                }
                
            } catch (Exception $e) {
                apiLog("Failed to verify guarantor: " . $e->getMessage(), 'ERROR');
                http_response_code(500);
                echo json_encode([
                    'success' => false,
                    'error' => 'Failed to verify guarantor',
                    'message' => (defined('ENVIRONMENT') && ENVIRONMENT === 'development') ? $e->getMessage() : null
                ]);
            }
            break;
            
        case 'eligible_account_types':
            try {
                $eligibleTypes = getEligibleGuaranteeAccountTypes();
                
                echo json_encode([
                    'success' => true,
                    'data' => [
                        'eligible_types' => $eligibleTypes,
                        'count' => count($eligibleTypes),
                        'description' => 'Only these account types can be used for loan guarantees'
                    ]
                ], JSON_NUMERIC_CHECK);
                
            } catch (Exception $e) {
                http_response_code(500);
                echo json_encode([
                    'success' => false,
                    'error' => 'Failed to fetch eligible account types',
                    'message' => (defined('ENVIRONMENT') && ENVIRONMENT === 'development') ? $e->getMessage() : null
                ]);
            }
            break;
            
        default:
            try {
                $savingsAccounts = getMemberSavingsAccounts($pdo, $memberId);
                $loanAccountTypes = getLoanAccountTypes($pdo);
                $eligibleGuaranteeTypes = getEligibleGuaranteeAccountTypes();
                
                echo json_encode([
                    'success' => true,
                    'data' => [
                        'savings_accounts' => $savingsAccounts,
                        'loan_account_types' => $loanAccountTypes,
                        'eligible_guarantee_account_types' => $eligibleGuaranteeTypes,
                        'active_application' => isset($_SESSION['loan_application']),
                        'charges_note' => 'Charges are fetched dynamically with full formula support including MAX/MIN functions, minimum amounts, and PHCF',
                        'charges_features' => [
                            'formula_support' => 'Formulas support variables: L (Loan Amount), M (Months), T (Years), R (Interest Rate)',
                            'function_support' => 'MAX() and MIN() functions are supported',
                            'minimum_amount' => 'Minimum amount enforcement with PHCF calculation',
                            'phcf_support' => 'PHCF percentage can be applied to any charge',
                            'charge_types' => 'Supports both UPFRONT (deducted from disbursement) and TOPUP (added to repayment) charges'
                        ]
                    ]
                ], JSON_NUMERIC_CHECK);
                
            } catch (Exception $e) {
                apiLog("Failed to fetch loan information: " . $e->getMessage(), 'ERROR');
                http_response_code(500);
                echo json_encode([
                    'success' => false,
                    'error' => 'Failed to fetch loan information',
                    'message' => (defined('ENVIRONMENT') && ENVIRONMENT === 'development') ? $e->getMessage() : null
                ]);
            }
            break;
    }
}

function handlePostRequest($pdo, $memberId, $action) {
    $input = json_decode(file_get_contents('php://input'), true);
    
    if (json_last_error() !== JSON_ERROR_NONE) {
        http_response_code(400);
        echo json_encode([
            'success' => false,
            'error' => 'Invalid JSON input',
            'json_error' => json_last_error_msg()
        ]);
        return;
    }
    
    apiLog("Processing POST request with action: " . ($action ?? 'none'));
    
    switch ($action) {
        case 'submit_full_application':
            if (!isset($input['savings_account_id']) || !isset($input['account_type_id']) || 
                !isset($input['loan_amount']) || !isset($input['loan_period']) || !isset($input['loan_purpose_id'])) {
                http_response_code(400);
                echo json_encode([
                    'success' => false,
                    'error' => 'Missing required fields',
                    'required_fields' => ['savings_account_id', 'account_type_id', 'loan_amount', 'loan_period', 'loan_purpose_id']
                ]);
                return;
            }
            
            try {
                $pdo->beginTransaction();
                $db = new DatabaseWrapper($pdo);
                $chargesHelper = new AccountChargesHelper($pdo);
                
                apiLog("Starting full loan application submission");
                
                // Check existing loan
                $existingLoanCheck = checkExistingLoanOfSameProduct($pdo, $memberId, $input['account_type_id']);
                if ($existingLoanCheck['has_existing']) {
                    $existing = $existingLoanCheck['loan'];
                    throw new Exception("You already have a " . ucfirst($existing['status']) . " loan of this product type. Loan #{$existing['loan_number']}");
                }
                
                // Get savings account details
                $savingsAccount = $db->fetchOne("
                    SELECT ma.available_balance, at.account_name, ma.account_number, at.type
                    FROM member_accounts ma
                    JOIN account_types at ON ma.account_type_id = at.id
                    WHERE ma.id = ? AND ma.member_id = ?
                ", [$input['savings_account_id'], $memberId]);
                
                if (!$savingsAccount) {
                    throw new Exception("Invalid savings account selected");
                }
                
                // Get loan account type details
                $loanAccountType = $db->fetchOne("
                    SELECT max_loan_factor, account_name, interest_rate, id
                    FROM account_types 
                    WHERE id = ?
                ", [$input['account_type_id']]);
                
                if (!$loanAccountType) {
                    throw new Exception("Invalid loan account type selected");
                }
                
                // Get loan purpose
                $loanPurpose = $db->fetchOne("
                    SELECT purpose_name, purpose_code
                    FROM loan_purposes 
                    WHERE id = ?
                ", [$input['loan_purpose_id']]);
                
                $loanAmount = (float)$input['loan_amount'];
                $loanPeriod = (int)$input['loan_period'];
                $interestRate = (float)$loanAccountType['interest_rate'];
                
                // Calculate charges with proper type separation
                $chargesInfo = $chargesHelper->previewCharges(
                    $input['account_type_id'], 
                    $loanAmount, 
                    $loanPeriod, 
                    $interestRate
                );
                
                // Calculate maximum eligibility based on net disbursement
                $maxEligibility = calculateMaxEligibility(
                    $savingsAccount['available_balance'], 
                    $loanAccountType['max_loan_factor']
                );
                
                $validation = validateLoanApplicationWithCharges($input, $maxEligibility, $chargesInfo);
                
                if (!$validation['valid']) {
                    http_response_code(400);
                    echo json_encode([
                        'success' => false,
                        'error' => 'Validation failed',
                        'errors' => $validation['errors']
                    ]);
                    $pdo->rollBack();
                    return;
                }
                
                $loanData = $validation['data'];
                
                // Calculate guarantee requirements
                $guarantee = calculateGuaranteeRequirements(
                    $loanData['loan_amount'],
                    $savingsAccount['available_balance'],
                    false,
                    $chargesInfo['total_upfront'],
                    $chargesInfo['total_topup']
                );
                
                $loanNumber = generateLoanNumber($pdo);
                
                // Calculate financials
                $totalInterest = ($loanData['loan_amount'] * $interestRate * $loanData['loan_period']) / (12 * 100);
                $totalPayable = $loanData['total_repayment_amount'] + $totalInterest;
                $monthlyInstallment = $totalPayable / $loanData['loan_period'];
                
                // Create loan record
                $loanId = $db->insertAndGetId("
                    INSERT INTO loans (
                        member_id, loan_number, account_type_id, 
                        savings_account_id, loan_purpose_id, loan_amount, 
                        loan_period, status, application_date,
                        self_guaranteed_amount, total_guaranteed_amount,
                        interest_rate, total_charges, upfront_charges, topup_charges,
                        net_disbursement, total_repayment_amount,
                        total_payable, monthly_installment, created_at,
                        description
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, 'pending', NOW(), ?, 0, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?)
                ", [
                    $memberId,
                    $loanNumber,
                    $loanData['account_type_id'],
                    $loanData['savings_account_id'],
                    $loanData['loan_purpose_id'],
                    $loanData['loan_amount'],
                    $loanData['loan_period'],
                    $guarantee['self_guarantee_amount'],
                    $interestRate,
                    $chargesInfo['total_charges'],
                    $chargesInfo['total_upfront'],
                    $chargesInfo['total_topup'],
                    $loanData['net_disbursement'],
                    $loanData['total_repayment_amount'],
                    $totalPayable,
                    $monthlyInstallment,
                    $input['description'] ?? null
                ]);
                
                // Apply and save charges
                $chargesResult = $chargesHelper->applyLoanCharges(
                    $loanId,
                    $input['account_type_id'],
                    $loanData['loan_amount'],
                    $loanData['loan_period'],
                    $interestRate
                );
                
                // Process guarantors
                $addedGuarantors = [];
                $guarantorDetails = [];
                $totalGuaranteed = 0;
                
                $applicant = getMemberDetails($pdo, $memberId);
                $applicantName = $applicant['first_name'] . ' ' . $applicant['last_name'];
                
                if (isset($input['guarantors']) && is_array($input['guarantors']) && count($input['guarantors']) > 0) {
                    foreach ($input['guarantors'] as $guarantorData) {
                        $verification = verifyGuarantor($pdo, $memberId, $guarantorData['search_term'], (float)$guarantorData['amount']);
                        
                        if (!$verification['success']) {
                            throw new Exception("Guarantor verification failed: " . $verification['message']);
                        }
                        
                        $verifiedGuarantor = $verification['guarantor'];
                        $guaranteeAmount = (float)$guarantorData['amount'];
                        
                        if ($totalGuaranteed + $guaranteeAmount > $guarantee['remaining_amount']) {
                            throw new Exception("Total guarantee amount exceeds required amount of " . 
                                               number_format($guarantee['remaining_amount'], 2));
                        }
                        
                        $db->execute("
                            INSERT INTO loan_guarantors 
                            (loan_id, guarantor_member_id, requested_amount, savings_account_id, 
                             added_by, status, original_balance, created_at)
                            VALUES (?, ?, ?, ?, ?, 'pending', ?, NOW())
                        ", [
                            $loanId,
                            $verifiedGuarantor['id'],
                            $guaranteeAmount,
                            $verifiedGuarantor['account_id'],
                            $memberId,
                            $verifiedGuarantor['available_balance']
                        ]);
                        
                        $guarantorRecordId = $pdo->lastInsertId();
                        $totalGuaranteed += $guaranteeAmount;
                        
                        sendGuarantorRequestNotification(
                            $pdo,
                            $verifiedGuarantor['id'],
                            [
                                'loan_number' => $loanNumber,
                                'loan_amount' => $loanData['loan_amount'],
                                'loan_period' => $loanData['loan_period'],
                                'interest_rate' => $interestRate,
                                'monthly_installment' => $monthlyInstallment
                            ],
                            $applicantName,
                            $guaranteeAmount
                        );
                        
                        $guarantorDetails[] = [
                            'id' => $guarantorRecordId,
                            'guarantor_id' => $verifiedGuarantor['id'],
                            'name' => $verifiedGuarantor['name'],
                            'member_number' => $verifiedGuarantor['member_number'],
                            'amount' => $guaranteeAmount,
                            'account_type' => $verifiedGuarantor['account_type'],
                            'status' => 'pending'
                        ];
                        
                        $addedGuarantors[] = [
                            'id' => $guarantorRecordId,
                            'name' => $verifiedGuarantor['name'],
                            'amount' => $guaranteeAmount
                        ];
                    }
                    
                    $totalCoverage = $guarantee['self_guarantee_amount'] + $totalGuaranteed;
                    
                    if ($totalCoverage < $loanData['loan_amount']) {
                        $shortfall = $loanData['loan_amount'] - $totalCoverage;
                        throw new Exception("Insufficient coverage. Additional Ksh " . number_format($shortfall, 2) . " needed");
                    }
                    
                    $db->execute("
                        UPDATE loans 
                        SET total_guaranteed_amount = ?,
                            guarantor_count = ?,
                            updated_at = NOW()
                        WHERE id = ?
                    ", [$totalGuaranteed, count($input['guarantors']), $loanId]);
                }
                
                $pdo->commit();
                
                // Send comprehensive notification to applicant
                $loanNotificationData = [
                    'loan_number' => $loanNumber,
                    'loan_amount' => $loanData['loan_amount'],
                    'loan_period' => $loanData['loan_period'],
                    'interest_rate' => $interestRate,
                    'monthly_installment' => $monthlyInstallment,
                    'total_upfront' => $chargesResult['total_upfront'],
                    'total_topup' => $chargesResult['total_topup'],
                    'upfront_charges' => $chargesResult['upfront_charges'],
                    'topup_charges' => $chargesResult['topup_charges'],
                    'net_disbursement' => $chargesResult['net_disbursement'],
                    'total_repayment_amount' => $chargesResult['total_repayment_amount'],
                    'interest_amount' => $totalInterest,
                    'total_payable' => $totalPayable,
                    'self_guarantee_amount' => $guarantee['self_guarantee_amount'],
                    'external_guarantee' => $totalGuaranteed,
                    'guarantors_added' => $addedGuarantors,
                    'loan_type' => $loanAccountType['account_name'],
                    'loan_purpose' => $loanPurpose['purpose_name'] ?? 'Not specified'
                ];
                
                sendLoaneeApplicationNotification($pdo, $memberId, $loanNotificationData);
                
                unset($_SESSION['loan_application'], $_SESSION['temp_guarantors']);
                
                echo json_encode([
                    'success' => true,
                    'message' => 'Loan application submitted successfully' . (count($addedGuarantors) > 0 ? ' with guarantors' : ''),
                    'data' => [
                        'loan_id' => $loanId,
                        'loan_number' => $loanNumber,
                        'loan_amount' => $loanData['loan_amount'],
                        'loan_period' => $loanData['loan_period'],
                        'interest_rate' => $interestRate,
                        'monthly_installment' => round($monthlyInstallment, 2),
                        'total_payable' => round($totalPayable, 2),
                        'total_charges' => $chargesResult['total_charges'],
                        'total_upfront' => $chargesResult['total_upfront'],
                        'total_topup' => $chargesResult['total_topup'],
                        'net_disbursement' => $chargesResult['net_disbursement'],
                        'total_repayment_amount' => $chargesResult['total_repayment_amount'],
                        'charges_breakdown' => [
                            'upfront' => $chargesResult['upfront_charges'],
                            'topup' => $chargesResult['topup_charges']
                        ],
                        'total_coverage' => $guarantee['self_guarantee_amount'] + $totalGuaranteed,
                        'self_guarantee' => $guarantee['self_guarantee_amount'],
                        'external_guarantee' => $totalGuaranteed,
                        'guarantor_count' => count($addedGuarantors),
                        'guarantors' => $guarantorDetails,
                        'status' => 'pending',
                        'application_date' => date('Y-m-d H:i:s'),
                        'savings_account' => [
                            'id' => $loanData['savings_account_id'],
                            'number' => $savingsAccount['account_number'],
                            'name' => $savingsAccount['account_name'],
                            'type' => $savingsAccount['type'],
                            'available_balance' => $savingsAccount['available_balance']
                        ]
                    ]
                ]);
                
            } catch (Exception $e) {
                $pdo->rollBack();
                apiLog("Full loan application error: " . $e->getMessage(), 'ERROR');
                http_response_code(400);
                echo json_encode([
                    'success' => false,
                    'error' => $e->getMessage()
                ]);
            }
            break;
            
        case 'calculate_charges':
            if (!isset($input['account_type_id']) || !isset($input['loan_amount']) || !isset($input['loan_period'])) {
                http_response_code(400);
                echo json_encode([
                    'success' => false,
                    'error' => 'Missing required fields: account_type_id, loan_amount, loan_period'
                ]);
                return;
            }
            
            try {
                $db = new DatabaseWrapper($pdo);
                $loanType = $db->fetchOne("SELECT interest_rate FROM account_types WHERE id = ?", [$input['account_type_id']]);
                $interestRate = $loanType['interest_rate'] ?? 0;
                
                $chargesInfo = calculateAdvancedLoanCharges(
                    $pdo,
                    $input['account_type_id'],
                    (float)$input['loan_amount'],
                    (int)$input['loan_period'],
                    $interestRate
                );
                
                echo json_encode([
                    'success' => true,
                    'data' => $chargesInfo,
                    'interest_rate' => $interestRate
                ], JSON_NUMERIC_CHECK);
                
            } catch (Exception $e) {
                apiLog("Charges calculation error: " . $e->getMessage(), 'ERROR');
                http_response_code(500);
                echo json_encode([
                    'success' => false,
                    'error' => 'Failed to calculate charges',
                    'message' => (defined('ENVIRONMENT') && ENVIRONMENT === 'development') ? $e->getMessage() : null
                ]);
            }
            break;
            
        default:
            http_response_code(400);
            echo json_encode([
                'success' => false,
                'error' => 'Invalid action specified'
            ]);
            break;
    }
}

function handleDeleteRequest($pdo, $memberId, $action) {
    apiLog("Processing DELETE request with action: " . ($action ?? 'none'));
    
    switch ($action) {
        case 'remove_guarantor':
            $index = $_GET['index'] ?? null;
            
            if ($index === null) {
                http_response_code(400);
                echo json_encode(['success' => false, 'error' => 'Guarantor index is required']);
                return;
            }
            
            $index = (int)$index;
            
            if (isset($_SESSION['temp_guarantors'][$index])) {
                $removed = $_SESSION['temp_guarantors'][$index];
                array_splice($_SESSION['temp_guarantors'], $index, 1);
                
                echo json_encode([
                    'success' => true,
                    'message' => 'Guarantor removed successfully',
                    'data' => [
                        'removed_guarantor' => $removed,
                        'remaining_guarantors' => $_SESSION['temp_guarantors'] ?? [],
                        'remaining_count' => count($_SESSION['temp_guarantors'] ?? [])
                    ]
                ]);
            } else {
                http_response_code(404);
                echo json_encode(['success' => false, 'error' => 'Guarantor not found']);
            }
            break;
            
        case 'cancel_application':
            $loanId = $_GET['loan_id'] ?? null;
            
            if (!$loanId) {
                http_response_code(400);
                echo json_encode(['success' => false, 'error' => 'Loan ID is required']);
                return;
            }
            
            try {
                $db = new DatabaseWrapper($pdo);
                
                $db->execute("
                    UPDATE loans 
                    SET status = 'cancelled', updated_at = NOW()
                    WHERE id = ? AND member_id = ? AND status IN ('pending')
                ", [$loanId, $memberId]);
                
                $affectedRows = $pdo->rowCount();
                
                if ($affectedRows === 0) {
                    http_response_code(404);
                    echo json_encode([
                        'success' => false,
                        'error' => 'Loan application not found or cannot be cancelled'
                    ]);
                    return;
                }
                
                if (isset($_SESSION['loan_application']) && $_SESSION['loan_application']['loan_id'] == $loanId) {
                    unset($_SESSION['loan_application'], $_SESSION['temp_guarantors']);
                }
                
                echo json_encode([
                    'success' => true,
                    'message' => 'Loan application cancelled successfully'
                ]);
                
            } catch (Exception $e) {
                apiLog("Cancel loan error: " . $e->getMessage(), 'ERROR');
                http_response_code(500);
                echo json_encode([
                    'success' => false,
                    'error' => 'Failed to cancel loan application',
                    'message' => (defined('ENVIRONMENT') && ENVIRONMENT === 'development') ? $e->getMessage() : null
                ]);
            }
            break;
            
        default:
            http_response_code(400);
            echo json_encode([
                'success' => false,
                'error' => 'Invalid action specified'
            ]);
            break;
    }
}

// ============================================
// MAIN EXECUTION
// ============================================

try {
    apiLog("Request received: " . $method . " " . $_SERVER['REQUEST_URI'] . " Action: " . ($action ?? 'none'));
    
    global $pdo;
    if (!isset($pdo) || !$pdo) {
        require_once __DIR__ . '/includes/config.php';
    }
    
    switch ($method) {
        case 'GET':
            handleGetRequest($pdo, $memberId, $action);
            break;
        case 'POST':
            handlePostRequest($pdo, $memberId, $action);
            break;
        case 'DELETE':
            handleDeleteRequest($pdo, $memberId, $action);
            break;
        default:
            http_response_code(405);
            echo json_encode([
                'success' => false,
                'error' => 'Method not allowed',
                'allowed_methods' => ['GET', 'POST', 'DELETE']
            ]);
            break;
    }
} catch (Exception $e) {
    apiLog("Top-level exception: " . $e->getMessage(), 'ERROR');
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'error' => 'Internal server error',
        'message' => 'Something went wrong'
    ]);
}
?>