<?PHP
    if (session_status() == PHP_SESSION_NONE) {
        session_start();
    }

    require_once 'functions.php';
    checkLogin();
    $db_link = connect();
    
    getSettings($db_link);
    getFees($db_link);

    // ==========================================
    // --- EXPORT TO EXCEL (CSV) LOGIC ---
    // (Optimized for 10M records using memory streams)
    // ==========================================
    
    // 1. Export OVERDUE / DUE List
    if (isset($_POST['export_rep'])) {
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename=Due_Overdue_Loans_' . date('Y-m-d') . '.csv');
        header("Pragma: no-cache");
        header("Expires: 0");

        $output = fopen('php://output', 'w');
        fputcsv($output, array('Loan No.', 'Customer/Group', 'Due Date', 'Amount Due ('.$_SESSION['set_cur'].')', 'Contact'));

        $total_due_export = 0; 
        $current_date_exp = time(); 
        
        // MYSQLI_USE_RESULT is CRITICAL here for 10M records to prevent memory exhaustion
        $query_exp_ind = "SELECT ltrans_principaldue, ltrans_interestdue, ltrans_due, loan_no, cust_name, cust_phone FROM ltrans LEFT JOIN loans ON ltrans.loan_id = loans.loan_id LEFT JOIN customer ON loans.cust_id = customer.cust_id WHERE ltrans_date IS NULL AND loanstatus_id = 2 AND (ltrans_due <= $current_date_exp) ORDER BY ltrans_due";
        $res_exp_ind = mysqli_query($db_link, $query_exp_ind, MYSQLI_USE_RESULT);

        if ($res_exp_ind) {
            while ($row = mysqli_fetch_assoc($res_exp_ind)){
                $amt = $row['ltrans_principaldue'] + $row['ltrans_interestdue'];
                $total_due_export += $amt;
                fputcsv($output, array($row['loan_no'], $row['cust_name'], date("d.m.Y", $row['ltrans_due']), $amt, $row['cust_phone']));
            }
            mysqli_free_result($res_exp_ind); // Free memory
        }

        $query_exp_grp = "SELECT group_ltrans.ltrans_principaldue, group_ltrans.ltrans_interestdue, group_ltrans.ltrans_due, group_loans.loan_no, `group`.group_name, (SELECT member_contact FROM group_member WHERE group_id = group_loans.group_id ORDER BY member_id LIMIT 1) as member_contact FROM group_ltrans LEFT JOIN group_loans ON group_ltrans.loan_id = group_loans.loan_id LEFT JOIN `group` ON group_loans.group_id = `group`.group_id WHERE ltrans_date IS NULL AND group_loans.loanstatus_id = 2 AND (ltrans_due <= $current_date_exp) ORDER BY ltrans_due";
        $res_exp_grp = mysqli_query($db_link, $query_exp_grp, MYSQLI_USE_RESULT);

        if ($res_exp_grp) {
            while ($row_g = mysqli_fetch_assoc($res_exp_grp)){
                $amt_g = $row_g['ltrans_principaldue'] + $row_g['ltrans_interestdue'];
                $total_due_export += $amt_g;
                fputcsv($output, array($row_g['loan_no'], $row_g['group_name'] . ' (G)', date("d.m.Y", $row_g['ltrans_due']), $amt_g, $row_g['member_contact']));
            }
            mysqli_free_result($res_exp_grp);
        }
        
        fputcsv($output, array('', '', '', 'Total Due:', $total_due_export));
        fclose($output);
        exit; 
    }

    // 2. Export THIS WEEK List
    if (isset($_POST['export_week'])) {
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename=Due_This_Week_' . date('Y-m-d') . '.csv');
        header("Pragma: no-cache");
        header("Expires: 0");

        $output = fopen('php://output', 'w');
        fputcsv($output, array('Loan No.', 'Customer/Group', 'Due Date', 'Amount Due ('.$_SESSION['set_cur'].')', 'Contact'));

        $total_week_export = 0; 
        $week_start_exp = strtotime("monday this week 00:00:00");
        $week_end_exp = strtotime("sunday this week 23:59:59");
        
        $query_week_ind_exp = "SELECT ltrans_principaldue, ltrans_interestdue, ltrans_due, loan_no, cust_name, cust_phone FROM ltrans LEFT JOIN loans ON ltrans.loan_id = loans.loan_id LEFT JOIN customer ON loans.cust_id = customer.cust_id WHERE ltrans_date IS NULL AND loanstatus_id = 2 AND (ltrans_due BETWEEN $week_start_exp AND $week_end_exp) ORDER BY ltrans_due";
        $res_week_ind = mysqli_query($db_link, $query_week_ind_exp, MYSQLI_USE_RESULT);

        if ($res_week_ind) {
            while ($row = mysqli_fetch_assoc($res_week_ind)){
                $amt = $row['ltrans_principaldue'] + $row['ltrans_interestdue'];
                $total_week_export += $amt;
                fputcsv($output, array($row['loan_no'], $row['cust_name'], date("d.m.Y", $row['ltrans_due']), $amt, $row['cust_phone']));
            }
            mysqli_free_result($res_week_ind);
        }

        $query_week_grp_exp = "SELECT group_ltrans.ltrans_principaldue, group_ltrans.ltrans_interestdue, group_ltrans.ltrans_due, group_loans.loan_no, `group`.group_name, (SELECT member_contact FROM group_member WHERE group_id = group_loans.group_id ORDER BY member_id LIMIT 1) as member_contact FROM group_ltrans LEFT JOIN group_loans ON group_ltrans.loan_id = group_loans.loan_id LEFT JOIN `group` ON group_loans.group_id = `group`.group_id WHERE ltrans_date IS NULL AND group_loans.loanstatus_id = 2 AND (ltrans_due BETWEEN $week_start_exp AND $week_end_exp) ORDER BY ltrans_due";
        $res_week_grp = mysqli_query($db_link, $query_week_grp_exp, MYSQLI_USE_RESULT);

        if ($res_week_grp) {
            while ($row_g = mysqli_fetch_assoc($res_week_grp)){
                $amt_g = $row_g['ltrans_principaldue'] + $row_g['ltrans_interestdue'];
                $total_week_export += $amt_g;
                fputcsv($output, array($row_g['loan_no'], $row_g['group_name'] . ' (G)', date("d.m.Y", $row_g['ltrans_due']), $amt_g, $row_g['member_contact']));
            }
            mysqli_free_result($res_week_grp);
        }
        
        fputcsv($output, array('', '', '', 'Total This Week:', $total_week_export));
        fclose($output);
        exit; 
    }

    // ==========================================
    // --- TOP LEVEL SUMMARY DATA ---
    // ==========================================
    $sql_top_ind = "SELECT SUM(loan_principal) as tot_cap, COUNT(loan_id) as active_clients FROM loans WHERE loanstatus_id = 2";
    $res_top_ind = mysqli_fetch_assoc(mysqli_query($db_link, $sql_top_ind));

    $sql_top_grp = "SELECT SUM(loan_principalapproved) as tot_cap, COUNT(loan_id) as active_groups FROM group_loans WHERE loanstatus_id = 2";
    $res_top_grp = mysqli_fetch_assoc(mysqli_query($db_link, $sql_top_grp));

    $top_capital = (float)$res_top_ind['tot_cap'] + (float)$res_top_grp['tot_cap'];
    $top_clients = (int)$res_top_ind['active_clients'] + (int)$res_top_grp['active_groups'];

    // ==========================================
    // --- LOGIC FOR EXPECTED VS ACTUAL COLLECTION ---
    // ==========================================
    $period = isset($_POST['period']) ? $_POST['period'] : 'all_time'; 
    $type = isset($_POST['amount_type']) ? $_POST['amount_type'] : 'total';

    if ($period == 'yesterday') {
        $start_time = strtotime("yesterday 00:00:00");
        $end_time = strtotime("yesterday 23:59:59");
    } elseif ($period == 'last_week') {
        $start_time = strtotime("-7 days 00:00:00");
        $end_time = strtotime("today 23:59:59");
    } elseif ($period == 'last_month') {
        $start_time = strtotime("-30 days 00:00:00");
        $end_time = strtotime("today 23:59:59");
    } elseif ($period == 'last_60') {
        $start_time = strtotime("-60 days 00:00:00");
        $end_time = strtotime("today 23:59:59");
    } else { 
        $start_time = 1; 
        $end_time = strtotime("tomorrow 23:59:59");
    }

    if ($type == 'principal') {
        $col_exp = "ltrans_principaldue";
        $col_act = "ltrans_principal"; 
    } elseif ($type == 'interest') {
        $col_exp = "ltrans_interestdue";
        $col_act = "ltrans_interest"; 
    } else {
        $col_exp = "(ltrans_principaldue + ltrans_interestdue)";
        $col_act = "(ltrans_principal + ltrans_interest)"; 
    }

    // 1. EXPECTED
    $sql_exp_ind = "SELECT IFNULL(SUM($col_exp), 0) as expected FROM ltrans JOIN loans ON ltrans.loan_id = loans.loan_id WHERE loans.loanstatus_id = 2 AND ltrans.ltrans_due BETWEEN $start_time AND $end_time";
    $res_exp_ind = mysqli_fetch_assoc(mysqli_query($db_link, $sql_exp_ind));
    $sql_exp_grp = "SELECT IFNULL(SUM($col_exp), 0) as expected FROM group_ltrans JOIN group_loans ON group_ltrans.loan_id = group_loans.loan_id WHERE group_loans.loanstatus_id = 2 AND group_ltrans.ltrans_due BETWEEN $start_time AND $end_time";
    $res_exp_grp = mysqli_fetch_assoc(mysqli_query($db_link, $sql_exp_grp));
    $total_expected = (float)$res_exp_ind['expected'] + (float)$res_exp_grp['expected'];

    // 2. ACTUAL
    $sql_act_ind = "SELECT IFNULL(SUM($col_act), 0) as actual FROM ltrans JOIN loans ON ltrans.loan_id = loans.loan_id WHERE loans.loanstatus_id = 2 AND ltrans.ltrans_date BETWEEN $start_time AND $end_time";
    $res_act_ind = mysqli_fetch_assoc(mysqli_query($db_link, $sql_act_ind));
    $sql_act_grp = "SELECT IFNULL(SUM($col_act), 0) as actual FROM group_ltrans JOIN group_loans ON group_ltrans.loan_id = group_loans.loan_id WHERE group_loans.loanstatus_id = 2 AND group_ltrans.ltrans_date BETWEEN $start_time AND $end_time";
    $res_act_grp = mysqli_fetch_assoc(mysqli_query($db_link, $sql_act_grp));
    $total_actual = (float)$res_act_ind['actual'] + (float)$res_act_grp['actual'];

    // 3. CALCULATIONS
    $variance = $total_actual - $total_expected;
    $efficiency = ($total_expected > 0) ? ($total_actual / $total_expected) * 100 : 0;
    $display_efficiency = min($efficiency, 100); 

    if ($efficiency < 50) { $bar_color = "#ef4444"; }
    elseif ($efficiency < 85) { $bar_color = "#f59e0b"; } 
    else { $bar_color = "#10b981"; } 


    // ==========================================
    // --- RECENT ACTIVITY FEED (LIMIT ALREADY IN PLACE) ---
    // ==========================================
    $recent_payments = [];

    $q_rec_ind = mysqli_query($db_link, "SELECT l.loan_no, c.cust_name as name, (IFNULL(t.ltrans_principal,0) + IFNULL(t.ltrans_interest,0)) as amount, t.ltrans_date as pdate FROM ltrans t JOIN loans l ON t.loan_id = l.loan_id JOIN customer c ON l.cust_id = c.cust_id WHERE t.ltrans_date IS NOT NULL AND t.ltrans_date != 0 ORDER BY t.ltrans_date DESC LIMIT 5");
    while($r = mysqli_fetch_assoc($q_rec_ind)) {
        $recent_payments[] = ['loan_no' => $r['loan_no'], 'name' => $r['name'], 'amount' => $r['amount'], 'date' => $r['pdate']];
    }

    $q_rec_grp = mysqli_query($db_link, "SELECT gl.loan_no, g.group_name as name, (IFNULL(t.ltrans_principal,0) + IFNULL(t.ltrans_interest,0)) as amount, t.ltrans_date as pdate FROM group_ltrans t JOIN group_loans gl ON t.loan_id = gl.loan_id JOIN `group` g ON gl.group_id = g.group_id WHERE t.ltrans_date IS NOT NULL AND t.ltrans_date != 0 ORDER BY t.ltrans_date DESC LIMIT 5");
    while($r = mysqli_fetch_assoc($q_rec_grp)) {
        $recent_payments[] = ['loan_no' => $r['loan_no'], 'name' => $r['name'] . ' (G)', 'amount' => $r['amount'], 'date' => $r['pdate']];
    }

    usort($recent_payments, function($a, $b) { return $b['date'] <=> $a['date']; });
    $recent_payments = array_slice($recent_payments, 0, 5);
?>
<!DOCTYPE HTML>
<html>
    <?PHP includeHead('Karomu Quick Services', 0); ?>
    <style>
        /* MODERN CSS UPGRADES */
        body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; background-color:  #E2E5D5; }
        
        .top-summary-container { display: flex; gap: 20px; padding: 20px 20px 0 20px; }
        .summary-box { flex: 1; padding: 25px; border-radius: 12px; box-shadow: 0 4px 15px rgba(0,0,0,0.05); text-align: center; color: white; border: none; }
        .summary-box .title { font-size: 13px; text-transform: uppercase; margin-bottom: 8px; font-weight: 600; letter-spacing: 0.5px; opacity: 0.9; }
        .summary-box .value { font-size: 28px; font-weight: bold; text-shadow: 1px 1px 2px rgba(0,0,0,0.1); }
        .summary-box.blue {  background-color:  #1E90FF; }
        .summary-box.green {   background-color: #00A651;}

        .dashboard-container { display: flex; padding: 20px; gap: 20px; align-items: flex-start; }
        .side-left { flex: 1; display: flex; flex-direction: column; gap: 20px; }
        .side-right { flex: 0 0 45%; }
        
        .collection-card { background: #fff; border: none; border-radius: 12px; padding: 20px; box-shadow: 0 4px 15px rgba(0,0,0,0.03); }
        .collection-card h3 { margin-top: 0; color: #2c3e50; font-size: 16px; border-bottom: 2px solid #f0f2f5; padding-bottom: 12px; margin-bottom: 20px; display: flex; justify-content: space-between; align-items: center; }
        
        .filter-row { display: flex; gap: 10px; margin-bottom: 20px; background: #f8fafc; padding: 12px; border-radius: 8px; border: 1px solid #e2e8f0; }
        .filter-row select { border: 1px solid #cbd5e1; border-radius: 5px; padding: 5px; outline: none; }
        
        .stat-grid { display: grid; grid-template-columns: 1fr 1fr; gap: 15px; margin-top:20px; }
        .stat-box { padding: 15px 20px; border-radius: 10px; text-align: left; position: relative; overflow: hidden; border: none; }
        .stat-box::before { content: ''; position: absolute; top: 0; left: 0; width: 5px; height: 100%; }
        .stat-label { font-size: 11px; text-transform: uppercase; font-weight: 600; letter-spacing: 0.5px; margin-bottom: 5px; opacity: 0.8; }
        .stat-value { font-size: 20px; font-weight: bold; }
        
        .stat-box.blue { background: #eff6ff; color: #1e3a8a; }
        .stat-box.blue::before { background: #3b82f6; }
        .stat-box.green { background: #f0fdf4; color: #14532d; }
        .stat-box.green::before { background: #22c55e; }
        .stat-box.orange { background: #fff7ed; color: #9a3412; }
        .stat-box.orange::before { background: #f97316; }
        .stat-box.status { background: #faf5ff; color: #581c87; }
        .stat-box.status::before { background: #a855f7; }

        .chart-container { margin: 10px 0; background: #e2e8f0; border-radius: 20px; height: 26px; position: relative; overflow: hidden; box-shadow: inset 0 2px 4px rgba(0,0,0,0.05); }
        .chart-bar { height: 100%; transition: width 1s ease-in-out; display: flex; align-items: center; justify-content: center; color: white; font-weight: bold; font-size: 13px; text-shadow: 1px 1px 1px rgba(0,0,0,0.2); }
        
        .table-scroll-container { max-height: 350px; overflow-y: auto; border: 1px solid #e2e8f0; border-radius: 6px; }
        table { width: 100%; border-collapse: collapse; font-size: 13px; }
        th { background: #f8fafc; color: #64748b; font-weight: 600; text-transform: uppercase; font-size: 11px; padding: 12px; border-bottom: 1px solid #e2e8f0; text-align: left; position: sticky; top: 0; z-index: 2; }
        td { padding: 12px; border-bottom: 1px solid #f1f5f9; color: #334155; }
        tr { transition: background-color 0.2s ease; }
        tr:hover td { background-color: #cbd5e1; color: #0f172a; cursor: pointer; }
        .amount-badge { color: #059669; font-weight: bold; background: #d1fae5; padding: 4px 8px; border-radius: 6px; display: inline-block; }
        tr.balance td { background-color: #f8fafc; font-weight: bold; text-align: right; border-top: 2px solid #e2e8f0; color: #0f172a; position: sticky; bottom: 0; z-index: 2; }
        tr.balance:hover td { background-color: #f8fafc; cursor: default; }

        .export-btn { background-color: #10b981; color: white; border: none; padding: 6px 12px; border-radius: 5px; cursor: pointer; font-size: 12px; font-weight: 600; transition: background 0.2s; }
        .export-btn:hover { background-color: #059669; }
        a { color: #3b82f6; text-decoration: none; font-weight: 500; }
        a:hover { text-decoration: underline; }
        .table-note { font-size: 11px; color: #94a3b8; margin-top: 10px; font-style: italic;}
    </style>
    </head>
    
    <body>
        <?PHP includeMenu(1); ?>
        
        <div id="menu_main">
            <a href="cust_search.php">Search Customer</a>
            <a href="cust_new.php">New Customer</a>
            <a href="loans_search.php">Search Loan</a>
        </div>

        <div class="top-summary-container">
            <div class="summary-box blue">
                <div class="title">Active Capital in Field</div>
                <div class="value"><?PHP echo number_format($top_capital) . ' ' . $_SESSION['set_cur']; ?></div>
            </div>
            <div class="summary-box green">
                <div class="title">Active Client Portfolios</div>
                <div class="value"><?PHP echo number_format($top_clients); ?></div>
            </div>
        </div>

        <div class="dashboard-container">
            <div class="side-left">
                <div class="collection-card">
                    <h3>Collection Performance</h3>
                    
                    <form method="post" class="filter-row">
                        <select name="period" onchange="this.form.submit()">
                            <option value="all_time" <?PHP if($period=='all_time') echo 'selected'; ?>>All Time</option>
                            <option value="yesterday" <?PHP if($period=='yesterday') echo 'selected'; ?>>Yesterday</option>
                            <option value="last_week" <?PHP if($period=='last_week') echo 'selected'; ?>>Last 7 Days</option>
                            <option value="last_month" <?PHP if($period=='last_month') echo 'selected'; ?>>Last 30 Days</option>
                            <option value="last_60" <?PHP if($period=='last_60') echo 'selected'; ?>>Last 60 Days</option>
                        </select>

                        <select name="amount_type" onchange="this.form.submit()">
                            <option value="total" <?PHP if($type=='total') echo 'selected'; ?>>Full Installment</option>
                            <option value="principal" <?PHP if($type=='principal') echo 'selected'; ?>>Principal Only</option>
                            <option value="interest" <?PHP if($type=='interest') echo 'selected'; ?>>Interest Only</option>
                        </select>
                    </form>

                    <div style="text-align:center; font-size: 12px; font-weight: 600; color: #64748b; margin-bottom:5px; text-transform: uppercase;">Collection Efficiency Rate</div>
                    <div class="chart-container">
                        <div class="chart-bar" style="width: <?PHP echo $display_efficiency; ?>%; background-color: <?PHP echo $bar_color; ?>;">
                            <?PHP echo number_format($efficiency, 1); ?>%
                        </div>
                    </div>

                    <div class="stat-grid">
                        <div class="stat-box blue">
                            <div class="stat-label">Expected (Due)</div>
                            <div class="stat-value"><?PHP echo number_format($total_expected); ?></div>
                        </div>
                        <div class="stat-box green">
                            <div class="stat-label">Actual (Paid)</div>
                            <div class="stat-value"><?PHP echo number_format($total_actual); ?></div>
                        </div>
                        <div class="stat-box orange">
                            <div class="stat-label">Variance</div>
                            <div class="stat-value"><?PHP echo number_format($variance); ?></div>
                        </div>
                        <div class="stat-box status">
                            <div class="stat-label">Status</div>
                            <div class="stat-value" style="color:<?PHP echo $bar_color; ?>;">
                                <?PHP 
                                    if($efficiency >= 100) echo "Surplus";
                                    elseif($efficiency >= 85) echo "Excellent";
                                    elseif($efficiency >= 50) echo "On Track";
                                    else echo "Critical Arrears";
                                ?>
                            </div>
                        </div>
                    </div>
                </div>

                <div class="collection-card">
                    <h3>Recent Collections (Last 5)</h3>
                    <table>
                        <thead>
                            <tr>
                                <th>Date/Time</th>
                                <th>Loan No.</th>
                                <th>Client Name</th>
                                <th>Amount Paid</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?PHP
                            if (empty($recent_payments)) {
                                echo "<tr><td colspan='4' style='text-align:center;'>No recent payments found.</td></tr>";
                            } else {
                                foreach ($recent_payments as $rp) {
                                    echo "<tr>
                                            <td>".date("d.m.Y H:i", $rp['date'])."</td>
                                            <td>{$rp['loan_no']}</td>
                                            <td>{$rp['name']}</td>
                                            <td><span class='amount-badge'>+".number_format($rp['amount'])." {$_SESSION['set_cur']}</span></td>
                                          </tr>";
                                }
                            }
                            ?>
                        </tbody>
                    </table>
                </div>

                <div class="collection-card">
                    <h3>
                        Installments Due This Week
                        <form action="" method="post" style="margin:0;">
                            <input type="submit" name="export_week" value="Export to Excel" class="export-btn" />
                        </form>
                    </h3>
                    
                    <?PHP 
                        $week_start = strtotime("monday this week 00:00:00");
                        $week_end = strtotime("sunday this week 23:59:59");

                        // OPTIMIZATION: Get grand totals straight from DB 
                        $sum_w_ind = mysqli_fetch_assoc(mysqli_query($db_link, "SELECT SUM(ltrans_principaldue + ltrans_interestdue) as tot FROM ltrans JOIN loans ON ltrans.loan_id = loans.loan_id WHERE ltrans_date IS NULL AND loanstatus_id = 2 AND (ltrans_due BETWEEN $week_start AND $week_end)"));
                        $sum_w_grp = mysqli_fetch_assoc(mysqli_query($db_link, "SELECT SUM(ltrans_principaldue + ltrans_interestdue) as tot FROM group_ltrans JOIN group_loans ON group_ltrans.loan_id = group_loans.loan_id WHERE ltrans_date IS NULL AND loanstatus_id = 2 AND (ltrans_due BETWEEN $week_start AND $week_end)"));
                        $grand_total_week = (float)$sum_w_ind['tot'] + (float)$sum_w_grp['tot'];

                        // OPTIMIZATION: UI Query limited to top 100 
                        $query_week_ind = "SELECT * FROM ltrans LEFT JOIN loans ON ltrans.loan_id = loans.loan_id LEFT JOIN customer ON loans.cust_id = customer.cust_id WHERE ltrans_date IS NULL AND loanstatus_id = 2 AND (ltrans_due BETWEEN $week_start AND $week_end) ORDER BY ltrans_due LIMIT 100";
                        $result_week_ind = mysqli_query($db_link, $query_week_ind);

                        $query_week_grp = "SELECT group_ltrans.*, group_loans.loan_no, `group`.group_name, (SELECT member_contact FROM group_member WHERE group_id = group_loans.group_id ORDER BY member_id LIMIT 1) as member_contact FROM group_ltrans LEFT JOIN group_loans ON group_ltrans.loan_id = group_loans.loan_id LEFT JOIN `group` ON group_loans.group_id = `group`.group_id WHERE ltrans_date IS NULL AND group_loans.loanstatus_id = 2 AND (ltrans_due BETWEEN $week_start AND $week_end) ORDER BY ltrans_due LIMIT 100";
                        $result_week_grp = mysqli_query($db_link, $query_week_grp);
                    ?>

                    <div class="table-scroll-container">
                        <table>
                            <thead>
                                <tr>
                                    <th>Loan No.</th>
                                    <th>Customer/Group</th>
                                    <th>Due Date</th>
                                    <th>Amount</th>
                                    <th>Contact</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?PHP
                                if ($result_week_ind) {
                                    while ($row = mysqli_fetch_assoc($result_week_ind)){
                                        $amt = $row['ltrans_principaldue'] + $row['ltrans_interestdue'];
                                        echo '<tr><td><a href="loan.php?lid='.$row['loan_id'].'">'.$row['loan_no'].'</a></td><td>'.$row['cust_name'].'</td><td>'.date("d.m.Y",$row['ltrans_due']).'</td><td>'.number_format($amt).'</td><td>'.$row['cust_phone'].'</td></tr>';
                                    }
                                }
                                if ($result_week_grp) {
                                    while ($row_g = mysqli_fetch_assoc($result_week_grp)){
                                        $amt_g = $row_g['ltrans_principaldue'] + $row_g['ltrans_interestdue'];
                                        echo '<tr><td><a href="group_loan.php?loan_id='.$row_g['loan_id'].'">'.$row_g['loan_no'].'</a></td><td>'.$row_g['group_name'].' (G)</td><td>'.date("d.m.Y",$row_g['ltrans_due']).'</td><td>'.number_format($amt_g).'</td><td>'.$row_g['member_contact'].'</td></tr>';
                                    }
                                }
                                ?>
                            </tbody>
                            <tfoot>
                                <tr class="balance">
                                    <td colspan="5">Total This Week: <?PHP echo number_format($grand_total_week).' '.$_SESSION['set_cur']; ?></td>
                                </tr>
                            </tfoot>
                        </table>
                    </div>
                    <div class="table-note">* Showing maximum of top 100 pending records. Export to Excel to view all.</div>
                </div>
            </div>

            <div class="side-right">
                <div class="collection-card">
                    <h3>
                        Loan Instalments Due or Overdue
                        <form action="" method="post" style="margin:0;">
                            <input type="submit" name="export_rep" value="Export to Excel" class="export-btn" />
                        </form>
                    </h3>
                    
                    <?PHP 
                        $current_date = time(); 

                        // OPTIMIZATION: Get grand totals straight from DB 
                        $sum_o_ind = mysqli_fetch_assoc(mysqli_query($db_link, "SELECT SUM(ltrans_principaldue + ltrans_interestdue) as tot FROM ltrans JOIN loans ON ltrans.loan_id = loans.loan_id WHERE ltrans_date IS NULL AND loanstatus_id = 2 AND (ltrans_due <= $current_date)"));
                        $sum_o_grp = mysqli_fetch_assoc(mysqli_query($db_link, "SELECT SUM(ltrans_principaldue + ltrans_interestdue) as tot FROM group_ltrans JOIN group_loans ON group_ltrans.loan_id = group_loans.loan_id WHERE ltrans_date IS NULL AND loanstatus_id = 2 AND (ltrans_due <= $current_date)"));
                        $grand_total_due = (float)$sum_o_ind['tot'] + (float)$sum_o_grp['tot'];

                        // OPTIMIZATION: UI Query limited to top 100 
                        $query_overd_due = "SELECT * FROM ltrans LEFT JOIN loans ON ltrans.loan_id = loans.loan_id LEFT JOIN customer ON loans.cust_id = customer.cust_id WHERE ltrans_date IS NULL AND loanstatus_id = 2 AND (ltrans_due <= $current_date) ORDER BY ltrans_due LIMIT 100";
                        $query_overd_due_result = mysqli_query($db_link, $query_overd_due);

                        $query_group_overd_due = "SELECT group_ltrans.*, group_loans.loan_no, `group`.group_name, (SELECT member_contact FROM group_member WHERE group_id = group_loans.group_id ORDER BY member_id LIMIT 1) as member_contact FROM group_ltrans LEFT JOIN group_loans ON group_ltrans.loan_id = group_loans.loan_id LEFT JOIN `group` ON group_loans.group_id = `group`.group_id WHERE ltrans_date IS NULL AND group_loans.loanstatus_id = 2 AND (ltrans_due <= $current_date) ORDER BY ltrans_due LIMIT 100";
                        $query_group_overd_due_result = mysqli_query($db_link, $query_group_overd_due);
                    ?>
                    
                    <div class="table-scroll-container">
                        <table>
                            <thead>
                                <tr>
                                    <th>Loan No.</th>
                                    <th>Customer/Group</th>
                                    <th>Due Date</th>
                                    <th>Amount</th>
                                    <th>Contact</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?PHP
                                if ($query_overd_due_result) {
                                    while ($row = mysqli_fetch_assoc($query_overd_due_result)){
                                        $amt = $row['ltrans_principaldue'] + $row['ltrans_interestdue'];
                                        echo '<tr><td><a href="loan.php?lid='.$row['loan_id'].'">'.$row['loan_no'].'</a></td><td>'.$row['cust_name'].'</td><td>'.date("d.m.Y",$row['ltrans_due']).'</td><td>'.number_format($amt).'</td><td>'.$row['cust_phone'].'</td></tr>';
                                    }
                                }
                                if ($query_group_overd_due_result) {
                                    while ($row_g = mysqli_fetch_assoc($query_group_overd_due_result)){
                                        $amt_g = $row_g['ltrans_principaldue'] + $row_g['ltrans_interestdue'];
                                        echo '<tr><td><a href="group_loan.php?loan_id='.$row_g['loan_id'].'">'.$row_g['loan_no'].'</a></td><td>'.$row_g['group_name'].' (G)</td><td>'.date("d.m.Y",$row_g['ltrans_due']).'</td><td>'.number_format($amt_g).'</td><td>'.$row_g['member_contact'].'</td></tr>';
                                    }
                                }
                                ?>
                            </tbody>
                            <tfoot>
                                <tr class="balance">
                                    <td colspan="5">Total Due: <?PHP echo number_format($grand_total_due).' '.$_SESSION['set_cur']; ?></td>
                                </tr>
                            </tfoot>
                        </table>
                    </div>
                    <div class="table-note">* Showing maximum of top 100 overdue records. Export to Excel to view all.</div>
                </div>
            </div>
        </div>

        <?PHP checkLogout(); ?>
    </body>
</html>