<?php

// ── Borrower Functions ────────────────────────────────────────────────────
function addBorrower($conn, $first_name, $last_name, $address, $contact, $id_type, $id_number, $occupation, $photo = null) {
    $sql = "INSERT INTO borrowers (first_name, last_name, address, contact, id_type, id_number, occupation, photo)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
    executeQuery($conn, $sql, [$first_name, $last_name, $address, $contact, $id_type, $id_number, $occupation, $photo], "ssssssss");
    return $conn->insert_id;
}

function updateBorrower($conn, $id, $first_name, $last_name, $address, $contact, $id_type, $id_number, $occupation, $photo = null) {
    if ($photo !== null) {
        $sql = "UPDATE borrowers SET first_name=?, last_name=?, address=?, contact=?, id_type=?, id_number=?, occupation=?, photo=? WHERE id=?";
        executeQuery($conn, $sql, [$first_name, $last_name, $address, $contact, $id_type, $id_number, $occupation, $photo, $id], "ssssssssi");
    } else {
        $sql = "UPDATE borrowers SET first_name=?, last_name=?, address=?, contact=?, id_type=?, id_number=?, occupation=? WHERE id=?";
        executeQuery($conn, $sql, [$first_name, $last_name, $address, $contact, $id_type, $id_number, $occupation, $id], "sssssssi");
    }
    return true;
}

function deleteBorrower($conn, $id) {
    executeQuery($conn, "DELETE FROM borrowers WHERE id=?", [$id], "i");
    return true;
}

function deleteBorrowerFull($conn, $id) {
    $borrower = getBorrowerById($conn, $id);
    if (!$borrower) return false;
    foreach (['photo', 'id_photo'] as $col) {
        if (!empty($borrower[$col]) && file_exists($borrower[$col])) {
            unlink($borrower[$col]);
        }
    }
    executeQuery($conn, "DELETE FROM borrowers WHERE id=?", [$id], "i");
    return true;
}

function getBorrowerById($conn, $id) {
    return fetchOne($conn, "SELECT * FROM borrowers WHERE id=?", [$id], "i");
}

function getAllBorrowers($conn) {
    return fetchAll($conn, "SELECT * FROM borrowers WHERE status='active' ORDER BY first_name ASC");
}

function getBorrowerFullName($conn, $id) {
    $b = getBorrowerById($conn, $id);
    return $b ? $b['first_name'] . ' ' . $b['last_name'] : 'Unknown';
}

// ── Pagination helpers ────────────────────────────────────────────────────
function getPaginatedBorrowers($conn, $search = '', $page = 1, $per_page = 20) {
    $offset = ($page - 1) * $per_page;
    $baseSelect = "SELECT b.*,
        (SELECT COUNT(*) FROM loans WHERE borrower_id = b.id) as loan_count,
        (SELECT COUNT(*) FROM payments p JOIN loans l ON p.loan_id = l.id WHERE l.borrower_id = b.id) as payment_count
        FROM borrowers b WHERE b.status='active'";
    if ($search) {
        $like = "%$search%";
        $data  = fetchAll($conn,
            "$baseSelect AND (b.first_name LIKE ? OR b.last_name LIKE ? OR b.contact LIKE ? OR b.id_number LIKE ?)
             ORDER BY b.first_name ASC LIMIT ? OFFSET ?",
            [$like, $like, $like, $like, $per_page, $offset], "ssssii");
        $total = getCount($conn,
            "SELECT COUNT(*) FROM borrowers b WHERE b.status='active'
             AND (b.first_name LIKE ? OR b.last_name LIKE ? OR b.contact LIKE ? OR b.id_number LIKE ?)",
            [$like, $like, $like, $like], "ssss");
    } else {
        $data  = fetchAll($conn,
            "$baseSelect ORDER BY b.first_name ASC LIMIT ? OFFSET ?",
            [$per_page, $offset], "ii");
        $total = getCount($conn, "SELECT COUNT(*) FROM borrowers b WHERE b.status='active'");
    }
    return ['data' => $data, 'total' => $total, 'pages' => max(1, ceil($total / $per_page)), 'page' => $page];
}

function getPaginatedLoans($conn, $status = '', $search = '', $page = 1, $per_page = 20) {
    $offset = ($page - 1) * $per_page;
    $where  = "WHERE 1=1";
    $params = []; $types = "";
    if ($status) { $where .= " AND l.status=?"; $params[] = $status; $types .= "s"; }
    if ($search) {
        $like = "%$search%";
        $where .= " AND (b.first_name LIKE ? OR b.last_name LIKE ? OR b.contact LIKE ?)";
        $params[] = $like; $params[] = $like; $params[] = $like; $types .= "sss";
    }
    $params[] = $per_page; $params[] = $offset; $types .= "ii";
    $data = fetchAll($conn,
        "SELECT l.*, b.first_name, b.last_name FROM loans l
         JOIN borrowers b ON l.borrower_id=b.id $where
         ORDER BY l.created_at DESC LIMIT ? OFFSET ?", $params, $types);
    $cparams = array_slice($params, 0, -2);
    $ctypes  = substr($types, 0, -2);
    $total   = getCount($conn,
        "SELECT COUNT(*) FROM loans l JOIN borrowers b ON l.borrower_id=b.id $where",
        $cparams, $ctypes);
    return ['data' => $data, 'total' => $total, 'pages' => max(1, ceil($total / $per_page)), 'page' => $page];
}

function getPaginatedPayments($conn, $search = '', $date = '', $page = 1, $per_page = 20) {
    $offset = ($page - 1) * $per_page;
    $where  = "WHERE 1=1";
    $params = []; $types = "";
    if ($date)   { $where .= " AND p.payment_date=?"; $params[] = $date;   $types .= "s"; }
    if ($search) {
        $like = "%$search%";
        $where .= " AND (b.first_name LIKE ? OR b.last_name LIKE ? OR p.receipt_number LIKE ?)";
        $params[] = $like; $params[] = $like; $params[] = $like; $types .= "sss";
    }
    $params[] = $per_page; $params[] = $offset; $types .= "ii";
    $data = fetchAll($conn,
        "SELECT p.*, b.first_name, b.last_name, l.loan_amount FROM payments p
         JOIN loans l ON p.loan_id=l.id JOIN borrowers b ON l.borrower_id=b.id
         $where ORDER BY p.payment_date DESC, p.created_at DESC LIMIT ? OFFSET ?",
        $params, $types);
    $cparams = array_slice($params, 0, -2);
    $ctypes  = substr($types, 0, -2);
    $total   = getCount($conn,
        "SELECT COUNT(*) FROM payments p JOIN loans l ON p.loan_id=l.id
         JOIN borrowers b ON l.borrower_id=b.id $where",
        $cparams, $ctypes);
    return ['data' => $data, 'total' => $total, 'pages' => max(1, ceil($total / $per_page)), 'page' => $page];
}

// ── Loan Functions ────────────────────────────────────────────────────────
function calculateLoanDetails($loan_amount, $interest_rate, $term, $payment_mode) {
    // Interest rate is MONTHLY. Convert term to equivalent months first.
    if ($payment_mode === 'daily') {
        $term_in_months = $term / 30;
        $installments   = $term;               // one payment per day
    } elseif ($payment_mode === 'weekly') {
        $term_in_months = ($term * 7) / 30;
        $installments   = $term;               // term is already in weeks
    } else {
        $term_in_months = $term;               // term is already in months
        $installments   = $term;               // one payment per month
    }

    $total_interest     = $loan_amount * ($interest_rate / 100) * $term_in_months;
    $total_payable      = $loan_amount + $total_interest;
    $installment_amount = $total_payable / max(1, $installments);

    return [
        'total_interest'     => round($total_interest, 2),
        'total_payable'      => round($total_payable, 2),
        'installment_amount' => round($installment_amount, 2),
    ];
}

function addLoan($conn, $borrower_id, $loan_amount, $interest_rate, $term, $release_date, $due_date, $payment_mode, $penalty, $renewed_from = null) {
    $details = calculateLoanDetails($loan_amount, $interest_rate, $term, $payment_mode);
    $sql = "INSERT INTO loans (borrower_id, loan_amount, interest_rate, term, release_date, due_date,
            payment_mode, penalty, total_interest, total_payable, installment_amount, balance, status, renewed_from)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', ?)";
    $params = [
        $borrower_id, $loan_amount, $interest_rate, $term,
        $release_date, $due_date, $payment_mode, $penalty,
        $details['total_interest'], $details['total_payable'],
        $details['installment_amount'], $details['total_payable'],
        $renewed_from,
    ];
    executeQuery($conn, $sql, $params, "iddiissdddds" . ($renewed_from ? "i" : "s"));
    return $conn->insert_id;
}

function getLoanById($conn, $id) {
    $col = $conn->query("SHOW COLUMNS FROM loans LIKE 'renewed_from'");
    $hasRenewed = $col && $col->num_rows > 0;

    $sql = $hasRenewed
        ? "SELECT l.*, b.first_name, b.last_name, b.contact, b.address,
                  l.renewed_from, rl.id as parent_loan_id
           FROM loans l
           JOIN borrowers b ON l.borrower_id = b.id
           LEFT JOIN loans rl ON l.renewed_from = rl.id
           WHERE l.id = ?"
        : "SELECT l.*, b.first_name, b.last_name, b.contact, b.address,
                  NULL as renewed_from, NULL as parent_loan_id
           FROM loans l
           JOIN borrowers b ON l.borrower_id = b.id
           WHERE l.id = ?";

    return fetchOne($conn, $sql, [$id], "i");
}

function getAllLoans($conn, $status = null, $limit = null) {
    if ($status && $limit) {
        return fetchAll($conn,
            "SELECT l.*, b.first_name, b.last_name FROM loans l
             JOIN borrowers b ON l.borrower_id=b.id WHERE l.status=? ORDER BY l.created_at DESC LIMIT ?",
            [$status, $limit], "si");
    }
    if ($status) {
        return fetchAll($conn,
            "SELECT l.*, b.first_name, b.last_name FROM loans l
             JOIN borrowers b ON l.borrower_id=b.id WHERE l.status=? ORDER BY l.created_at DESC",
            [$status], "s");
    }
    if ($limit) {
        return fetchAll($conn,
            "SELECT l.*, b.first_name, b.last_name FROM loans l
             JOIN borrowers b ON l.borrower_id=b.id ORDER BY l.created_at DESC LIMIT ?",
            [$limit], "i");
    }
    return fetchAll($conn,
        "SELECT l.*, b.first_name, b.last_name FROM loans l
         JOIN borrowers b ON l.borrower_id=b.id ORDER BY l.created_at DESC");
}

// Get a limited number of recent loans — use instead of getAllLoans() + array_slice()
function getRecentLoans($conn, $limit = 5) {
    return fetchAll($conn,
        "SELECT l.*, b.first_name, b.last_name FROM loans l
         JOIN borrowers b ON l.borrower_id=b.id ORDER BY l.created_at DESC LIMIT ?",
        [$limit], "i");
}

// Get loan counts grouped by status — single query instead of 5 getAllLoans() calls
function getLoanCountsByStatus($conn) {
    $rows = fetchAll($conn,
        "SELECT status, COUNT(*) as cnt, 
                COALESCE(SUM(loan_amount),0) as total_amount,
                COALESCE(SUM(balance),0) as total_balance
         FROM loans GROUP BY status");
    $result = [];
    foreach ($rows as $r) {
        $result[$r['status']] = $r;
    }
    return $result;
}

// Get pending approvals count
function getPendingApprovals($conn) {
    return getCount($conn, "SELECT COUNT(*) FROM loans WHERE status = 'pending'");
}

function updateLoanStatus($conn, $loan_id, $status) {
    executeQuery($conn, "UPDATE loans SET status=? WHERE id=?", [$status, $loan_id], "si");
}

function approveLoan($conn, $loan_id, $user_id) {
    // Get loan details to recalculate due_date from today's release date
    $loan = fetchOne($conn, "SELECT term, payment_mode FROM loans WHERE id=? AND status='pending'", [$loan_id], "i");
    if (!$loan) return;

    $release_date = date('Y-m-d');
    $d = new DateTime($release_date);
    $term = (int)$loan['term'];
    if ($loan['payment_mode'] === 'daily')       $d->modify("+{$term} days");
    elseif ($loan['payment_mode'] === 'weekly')  $d->modify("+" . ($term * 7) . " days");
    else                                         $d->modify("+{$term} months");
    $due_date = $d->format('Y-m-d');

    executeQuery($conn,
        "UPDATE loans SET status='approved', release_date=?, due_date=? WHERE id=? AND status='pending'",
        [$release_date, $due_date, $loan_id], "ssi"
    );
    logActivity($conn, $user_id, 'approve_loan', "Approved loan ID: $loan_id — Release: $release_date, Due: $due_date");
}

// ── Amortization schedule ─────────────────────────────────────────────────
function generateAmortizationSchedule($loan) {
    $schedule = [];
    $mode     = $loan['payment_mode'];
    $amt      = (float)$loan['installment_amount'];
    $start    = new DateTime($loan['release_date']);
    $total    = (float)$loan['total_payable'];
    $terms    = (int)$loan['term'];

    if ($mode === 'daily')       $installments = $terms;
    elseif ($mode === 'weekly')  $installments = (int)ceil($terms / 7);
    else                         $installments = (int)ceil($terms / 30);

    $balance = $total;
    for ($i = 1; $i <= $installments; $i++) {
        $due = clone $start;
        if ($mode === 'daily')       $due->modify("+{$i} days");
        elseif ($mode === 'weekly')  $due->modify("+" . ($i * 7) . " days");
        else                         $due->modify("+{$i} months");
        $payment  = ($i === $installments) ? round($balance, 2) : $amt;
        $balance  = round($balance - $payment, 2);
        $schedule[] = [
            'term'     => $i,
            'due_date' => $due->format('Y-m-d'),
            'amount'   => $payment,
            'balance'  => max(0, $balance),
        ];
    }
    return $schedule;
}

// ── Penalty calculation ───────────────────────────────────────────────────
function calculatePenalty($loan) {
    // Applies if overdue, OR if released but past due date
    $isOverdue = $loan['status'] === 'overdue';
    $isPastDue = $loan['status'] === 'released'
                 && !empty($loan['due_date'])
                 && $loan['due_date'] < date('Y-m-d');
    if (!$isOverdue && !$isPastDue) return 0;
    return round((float)$loan['balance'] * ((float)$loan['penalty'] / 100), 2);
}

// ── Payment Functions ─────────────────────────────────────────────────────
function recordPayment($conn, $loan_id, $payment_date, $amount, $penalty_paid = 0,
                        $payment_method = '', $notes = '', $user_id = null,
                        $payment_type = 'regular') {
    $receipt_number = 'RCP-' . date('YmdHis') . '-' . rand(100, 999);

    // payment_type: 'regular' deducts from balance, 'interest_only' does NOT touch principal
    $sql = "INSERT INTO payments
                (loan_id, payment_date, amount, penalty_paid, payment_method,
                 notes, receipt_number, created_by, payment_type)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
    executeQuery($conn, $sql,
        [$loan_id, $payment_date, $amount, $penalty_paid,
         $payment_method, $notes, $receipt_number, $user_id, $payment_type],
        "isddsssis");
    $payment_id = $conn->insert_id;

    // Only reduce principal balance for regular payments
    if ($payment_type === 'regular') {
        $loan        = getLoanById($conn, $loan_id);
        $new_balance = max(0, (float)$loan['balance'] - $amount);
        executeQuery($conn, "UPDATE loans SET balance=? WHERE id=?", [$new_balance, $loan_id], "di");
        if ($new_balance <= 0) {
            updateLoanStatus($conn, $loan_id, 'paid');
        }
    }
    // interest_only: balance unchanged, loan stays active

    return $payment_id;
}

function getPaymentsByLoan($conn, $loan_id) {
    return fetchAll($conn,
        "SELECT p.*, u.full_name FROM payments p
         LEFT JOIN users u ON p.created_by=u.id WHERE p.loan_id=? ORDER BY p.payment_date DESC",
        [$loan_id], "i");
}

function getTotalPaidByLoan($conn, $loan_id) {
    $r = fetchOne($conn, "SELECT COALESCE(SUM(amount),0) as total FROM payments WHERE loan_id=?", [$loan_id], "i");
    return $r['total'];
}

// ── Dashboard stats ───────────────────────────────────────────────────────
function getTotalBorrowers($conn) {
    return getCount($conn, "SELECT COUNT(*) FROM borrowers WHERE status='active'");
}
function getActiveLoans($conn) {
    return getCount($conn, "SELECT COUNT(*) FROM loans WHERE status IN ('pending','approved','released')");
}
function getPaidLoans($conn) {
    return getCount($conn, "SELECT COUNT(*) FROM loans WHERE status='paid'");
}
function getOverdueLoans($conn) {
    return getCount($conn, "SELECT COUNT(*) FROM loans WHERE status='overdue' OR (due_date < CURDATE() AND status = 'released')");
}
function getDailyCollections($conn, $date = null) {
    $date = $date ?? date('Y-m-d');
    $r    = fetchOne($conn, "SELECT COALESCE(SUM(amount + penalty_paid),0) as total FROM payments WHERE payment_date=?", [$date], "s");
    return $r['total'];
}
function getMonthlyCollections($conn, $month = null, $year = null) {
    $month = $month ?? date('n');
    $year  = $year  ?? date('Y');
    $r     = fetchOne($conn,
        "SELECT COALESCE(SUM(amount + penalty_paid),0) as total FROM payments
         WHERE MONTH(payment_date)=? AND YEAR(payment_date)=?",
        [$month, $year], "ii");
    return $r['total'];
}
function updateOverdueLoans($conn) {
    executeQuery($conn,
        "UPDATE loans SET status='overdue' WHERE status = 'released' AND due_date < CURDATE()");
}

// ── Settings ──────────────────────────────────────────────────────────────
function getSettings($conn) {
    return fetchOne($conn, "SELECT * FROM settings LIMIT 1");
}
function updateSettings($conn, $company_name, $default_interest, $penalty, $phone, $address, $email) {
    executeQuery($conn,
        "UPDATE settings SET company_name=?, default_interest=?, penalty=?, phone=?, address=?, email=? WHERE id=1",
        [$company_name, $default_interest, $penalty, $phone, $address, $email], "sddsss");
}

// ── Reports ───────────────────────────────────────────────────────────────
function getMonthlyCollectionsReport($conn, $year = null) {
    $year = $year ?? date('Y');
    return fetchAll($conn,
        "SELECT MONTH(payment_date) as month, SUM(amount + penalty_paid) as total
         FROM payments WHERE YEAR(payment_date)=?
         GROUP BY MONTH(payment_date) ORDER BY MONTH(payment_date)",
        [$year], "i");
}
function getDailyCollectionsReport($conn, $start_date, $end_date) {
    return fetchAll($conn,
        "SELECT payment_date as date, SUM(amount + penalty_paid) as total
         FROM payments WHERE payment_date BETWEEN ? AND ?
         GROUP BY payment_date ORDER BY payment_date DESC",
        [$start_date, $end_date], "ss");
}

// ── Activity / Audit Log ──────────────────────────────────────────────────
function logActivity($conn, $user_id, $action, $description = '') {
    try {
        $ip = $_SERVER['REMOTE_ADDR'] ?? '';
        executeQuery($conn,
            "INSERT INTO activity_log (user_id, action, description, ip_address) VALUES (?,?,?,?)",
            [$user_id, $action, $description, $ip], "isss");
    } catch (Exception $e) { /* ignore if table missing */ }
}
function getActivityLog($conn, $limit = 50, $offset = 0) {
    return fetchAll($conn,
        "SELECT al.*, u.username, u.full_name FROM activity_log al
         LEFT JOIN users u ON al.user_id=u.id
         ORDER BY al.created_at DESC LIMIT ? OFFSET ?",
        [$limit, $offset], "ii");
}



/**
 * Check if a loan is eligible for renewal.
 * Returns ['allowed' => bool, 'reason' => string]
 */
function canRenewLoan($conn, $loan) {
    // Must be approved, overdue, or paid
    if (!in_array($loan['status'], ['released', 'overdue', 'paid'])) {
        return ['allowed' => false, 'reason' => 'Loan must be released before it can be renewed.'];
    }

    // Cannot renew if a renewal already exists (pending or active)
    // Guard: only query renewed_from if the column exists
    $col = $conn->query("SHOW COLUMNS FROM loans LIKE 'renewed_from'");
    if ($col && $col->num_rows > 0) {
        $existing = fetchOne($conn,
            "SELECT id, status FROM loans WHERE renewed_from = ? AND status != 'paid'",
            [$loan['id']], "i");
        if ($existing) {
            return ['allowed' => false, 'reason' => "A renewal already exists (Loan #{$existing['id']} — {$existing['status']})."];
        }
    }

    // Interest must be fully paid
    // Sum interest-only payments + (regular payments - principal reduction logic)
    // Simple rule: total paid must cover total_interest at minimum
    $r = fetchOne($conn,
        "SELECT COALESCE(SUM(amount),0) as interest_paid FROM payments
         WHERE loan_id = ? AND payment_type = 'interest_only'",
        [$loan['id']], "i");
    $interest_only_paid = floatval($r['interest_paid'] ?? 0);

    // Regular payments reduce balance (principal + interest mixed)
    // Calculate how much of regular payments went to interest
    $regular_paid = floatval(getTotalPaidByLoan($conn, $loan['id'])) - $interest_only_paid;
    $original_balance = floatval($loan['loan_amount']);
    $current_balance  = floatval($loan['balance']);
    $principal_paid   = $original_balance - $current_balance;
    $regular_interest_paid = max(0, $regular_paid - $principal_paid);
    $total_interest_paid = $interest_only_paid + $regular_interest_paid;
    $total_interest = floatval($loan['total_interest']);

    if ($total_interest_paid < $total_interest) {
        $unpaid = $total_interest - $total_interest_paid;
        return [
            'allowed' => false,
            'reason'  => 'Unpaid interest of ₱' . number_format($unpaid, 2) . ' must be settled before renewal.'
        ];
    }

    return ['allowed' => true, 'reason' => ''];
}

/**
 * Get all payments grouped by borrower then by loan.
 * Returns: [ borrower_id => [ 'info' => [...], 'loans' => [ loan_id => [ 'info'=>[...], 'payments'=>[...] ] ] ] ]
 */
function getPaymentsGrouped($conn, $search = '', $date = '') {
    $where  = "WHERE 1=1";
    $params = []; $types = "";

    if ($date) {
        $where .= " AND p.payment_date = ?";
        $params[] = $date; $types .= "s";
    }
    if ($search) {
        $like = "%$search%";
        $where .= " AND (b.first_name LIKE ? OR b.last_name LIKE ? OR p.receipt_number LIKE ?)";
        $params[] = $like; $params[] = $like; $params[] = $like; $types .= "sss";
    }

    $rows = fetchAll($conn,
        "SELECT p.*, b.id as borrower_id, b.first_name, b.last_name, b.contact,
                l.id as loan_id, l.loan_amount, l.status as loan_status,
                l.total_payable, l.balance
         FROM payments p
         JOIN loans l ON p.loan_id = l.id
         JOIN borrowers b ON l.borrower_id = b.id
         $where
         ORDER BY b.last_name ASC, b.first_name ASC, l.id DESC, p.payment_date DESC",
        $params, $types);

    $grouped = [];
    foreach ($rows as $r) {
        $bid = $r['borrower_id'];
        $lid = $r['loan_id'];

        if (!isset($grouped[$bid])) {
            $grouped[$bid] = [
                'name'    => $r['first_name'] . ' ' . $r['last_name'],
                'contact' => $r['contact'],
                'total'   => 0,
                'loans'   => [],
            ];
        }
        if (!isset($grouped[$bid]['loans'][$lid])) {
            $grouped[$bid]['loans'][$lid] = [
                'loan_amount'   => $r['loan_amount'],
                'total_payable' => $r['total_payable'],
                'balance'       => $r['balance'],
                'loan_status'   => $r['loan_status'],
                'payments'      => [],
                'loan_total'    => 0,
            ];
        }

        $grouped[$bid]['loans'][$lid]['payments'][] = $r;
        $grouped[$bid]['loans'][$lid]['loan_total'] += $r['amount'];
        $grouped[$bid]['total'] += $r['amount'];
    }

    return $grouped;
}

/**
 * Get all loans grouped by borrower, latest loan first per borrower.
 * Returns: [ borrower_id => [ 'name'=>, 'contact'=>, 'loans'=>[ [...loan row...] ] ] ]
 */
function getLoansGrouped($conn, $status = '', $search = '') {
    $where  = "WHERE 1=1";
    $params = []; $types = "";

    if ($status) {
        $where .= " AND l.status = ?";
        $params[] = $status; $types .= "s";
    }
    if ($search) {
        $like = "%$search%";
        $where .= " AND (b.first_name LIKE ? OR b.last_name LIKE ?)";
        $params[] = $like; $params[] = $like; $types .= "ss";
    }

    $rows = fetchAll($conn,
        "SELECT l.*, b.id as borrower_id, b.first_name, b.last_name, b.contact
         FROM loans l
         JOIN borrowers b ON l.borrower_id = b.id
         $where
         ORDER BY b.last_name ASC, b.first_name ASC, l.id DESC",
        $params, $types);

    $grouped = [];
    foreach ($rows as $r) {
        $bid = $r['borrower_id'];
        if (!isset($grouped[$bid])) {
            $grouped[$bid] = [
                'name'    => $r['first_name'] . ' ' . $r['last_name'],
                'contact' => $r['contact'],
                'loans'   => [],
            ];
        }
        $grouped[$bid]['loans'][] = $r;
    }
    return $grouped;
}

// ── Image/File Upload Helpers (shared by add-borrower & edit-borrower) ─────

function saveBase64Image($base64_string, $prefix, $upload_dir) {
    if (!preg_match('/^data:image\/(\w+);base64,/', $base64_string, $type)) return null;
    $ext = strtolower($type[1]);
    if (!in_array($ext, ['jpg','jpeg','png','gif','webp'])) return null;
    $data = base64_decode(preg_replace('/^data:image\/\w+;base64,/', '', $base64_string));
    if (!$data) return null;
    if (!is_dir($upload_dir)) mkdir($upload_dir, 0755, true);
    $filename = $prefix . '_' . time() . '_' . uniqid() . '.' . $ext;
    file_put_contents($upload_dir . $filename, $data);
    return $upload_dir . $filename;
}

function saveUploadedFile($file_key, $prefix, $upload_dir, &$errors) {
    if (empty($_FILES[$file_key]['name'])) return null;
    $allowed = ['image/jpeg','image/png','image/gif','image/webp'];
    if (!in_array($_FILES[$file_key]['type'], $allowed)) {
        $errors[] = ucfirst($prefix) . ' must be JPG, PNG, GIF, or WEBP';
        return null;
    }
    if ($_FILES[$file_key]['size'] > 4 * 1024 * 1024) {
        $errors[] = ucfirst($prefix) . ' must be smaller than 4MB';
        return null;
    }
    if (!is_dir($upload_dir)) mkdir($upload_dir, 0755, true);
    $ext = pathinfo($_FILES[$file_key]['name'], PATHINFO_EXTENSION);
    $filename = $prefix . '_' . time() . '_' . uniqid() . '.' . $ext;
    if (move_uploaded_file($_FILES[$file_key]['tmp_name'], $upload_dir . $filename)) {
        return $upload_dir . $filename;
    }
    $errors[] = 'Failed to upload ' . $prefix . '. Check folder permissions.';
    return null;
}
