<?php
require_once 'functions.php';
checkLogin();
$db_link = connect();

if (!isset($_GET['lid'])) { die("Error: Loan ID missing."); }
$loan_id = sanitize($db_link, $_GET['lid']);

// Fetch Loan Info
$sql = "SELECT l.*, c.cust_name, c.cust_no, c.cust_phone, ls.loanstatus_status 
        FROM loans l 
        JOIN customer c ON l.cust_id = c.cust_id 
        JOIN loanstatus ls ON l.loanstatus_id = ls.loanstatus_id
        WHERE l.loan_id = '$loan_id'";
$query = mysqli_query($db_link, $sql);
$loan = mysqli_fetch_assoc($query);

// Fetch Transactions
$sql_trans = "SELECT * FROM ltrans WHERE loan_id = '$loan_id' ORDER BY ltrans_date ASC, ltrans_id ASC";
$query_trans = mysqli_query($db_link, $sql_trans);

// Logo Auto-Detect
$logo_base64 = "";
$logo_path = "";
if (file_exists("uploads/photos/logo.png")) { $logo_path = "uploads/photos/logo.png"; } 
elseif (file_exists("uploads/photos/image_9c4217.png")) { $logo_path = "uploads/photos/image_9c4217.png"; }
if ($logo_path) {
    $type = pathinfo($logo_path, PATHINFO_EXTENSION);
    $data = file_get_contents($logo_path);
    $logo_base64 = 'data:image/' . $type . ';base64,' . base64_encode($data);
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>Statement - <?php echo $loan['loan_no']; ?></title>
    <style>
        body { font-family: 'Segoe UI', Tahoma, sans-serif; font-size: 11px; color: #333; max-width: 950px; margin: 0 auto; padding: 20px; }
        .header { display: flex; align-items: center; border-bottom: 2px solid #E95D0F; padding-bottom: 10px; margin-bottom: 15px; }
        .logo { width: 100px; height: auto; margin-right: 15px; }
        .company-info h1 { margin: 0; color: #0056b3; font-size: 20px; }
        .client-box { background: #f4f4f4; border: 1px solid #ddd; padding: 10px; display: flex; margin-bottom: 15px; }
        .box-col { flex: 1; }
        .label { font-weight: bold; width: 110px; display: inline-block; }
        
        table { width: 100%; border-collapse: collapse; margin-top: 10px; font-size: 11px; }
        th, td { border: 1px solid #ccc; padding: 6px; text-align: right; }
        th { background: #E95D0F; color: white; text-align: center; }
        .text-left { text-align: left; }
        .text-center { text-align: center; }
        .summary-row { font-weight: bold; background: #eee; }
        
        @media print { button { display: none; } }
    </style>
</head>
<body>
    <div style="text-align:right;"><button onclick="window.print()" style="padding:8px; cursor:pointer;">PRINT STATEMENT</button></div>

    <div class="header">
        <?php if($logo_base64): ?><img src="<?php echo $logo_base64; ?>" class="logo"><?php endif; ?>
        <div class="company-info">
            <h1>KAROMU QUICK SERVICES LTD</h1>
            <p><strong>"Your Financial Solution"</strong> | P.O. BOX 149380 KAMPALA | Tel: 0782559240</p>
        </div>
    </div>

    <div class="client-box">
        <div class="box-col">
            <div><span class="label">Name:</span> <?php echo $loan['cust_name']; ?></div>
            <div><span class="label">Client ID:</span> <?php echo $loan['cust_no']; ?></div>
            <div><span class="label">Phone:</span> <?php echo $loan['cust_phone']; ?></div>
        </div>
        <div class="box-col">
            <div><span class="label">Loan No:</span> <?php echo $loan['loan_no']; ?></div>
            <div><span class="label">Principal:</span> <?php echo number_format($loan['loan_principal']); ?></div>
            <div><span class="label">Date Issued:</span> <?php echo date("d-M-Y", $loan['loan_dateout']); ?></div>
        </div>
        <div class="box-col">
            <div><span class="label">Repay Total:</span> <?php echo number_format($loan['loan_repaytotal']); ?></div>
            <div><span class="label">Interest Rate:</span> <?php echo $loan['loan_interest']; ?>%</div>
            <div><span class="label">Status:</span> <?php echo $loan['loanstatus_status']; ?></div>
        </div>
    </div>

    <table>
        <thead>
            <tr>
                <th width="10%">Date</th>
                <th class="text-left">Receipt / Desc</th>
                <th>Total Paid</th>
                <th style="background:#dff0d8; color:black;">To Principal</th>
                <th style="background:#d9edf7; color:black;">To Interest</th>
                <th>Fined</th>
                <th>Principal Bal</th>
                <th>Interest Bal</th>
            </tr>
        </thead>
        <tbody>
            <tr class="summary-row">
                <td class="text-center"><?php echo date("d.m.Y", $loan['loan_dateout']); ?></td>
                <td class="text-left">Loan Disbursed</td>
                <td>-</td>
                <td>-</td>
                <td>-</td>
                <td>-</td>
                <td><?php echo number_format($loan['loan_principal']); ?></td>
                <td><?php echo number_format($loan['loan_repaytotal'] - $loan['loan_principal']); ?></td>
            </tr>

            <?php
            $bal_p = $loan['loan_principal'];
            $bal_i = $loan['loan_repaytotal'] - $loan['loan_principal']; // Total Interest Due
            $tot_paid = 0; $tot_princ_pd = 0; $tot_int_pd = 0; $tot_fine = 0;

            while ($row = mysqli_fetch_assoc($query_trans)) {
                // Payment Row
                if ($row['ltrans_date'] != NULL) {
                    $paid_p = $row['ltrans_principal'];
                    $paid_i = $row['ltrans_interest'];
                    $total_row = $paid_p + $paid_i;
                    
                    $bal_p -= $paid_p;
                    $bal_i -= $paid_i;
                    
                    $tot_paid += $total_row;
                    $tot_princ_pd += $paid_p;
                    $tot_int_pd += $paid_i;

                    echo "<tr>
                        <td class='text-center'>".date("d.m.Y", $row['ltrans_date'])."</td>
                        <td class='text-left'>".$row['ltrans_receipt']."</td>
                        <td><b>".number_format($total_row)."</b></td>
                        <td style='background:#f0fff0;'>".number_format($paid_p)."</td>
                        <td style='background:#f0f8ff;'>".number_format($paid_i)."</td>
                        <td>-</td>
                        <td>".number_format($bal_p)."</td>
                        <td>".number_format($bal_i)."</td>
                    </tr>";
                }
                
                // Fine Row
                if ($row['ltrans_fined'] == 1 && $row['fine_amount'] > 0) {
                    $tot_fine += $row['fine_amount'];
                    echo "<tr>
                        <td class='text-center'>".date("d.m.Y", $row['ltrans_created'])."</td>
                        <td class='text-left' style='color:red;'>Penalty / Fine</td>
                        <td>-</td><td>-</td><td>-</td>
                        <td style='color:red;'>".number_format($row['fine_amount'])."</td>
                        <td>".number_format($bal_p)."</td>
                        <td>".number_format($bal_i)."</td>
                    </tr>";
                }
            }
            ?>
            <tr class="summary-row">
                <td colspan="2" class="text-left">TOTALS</td>
                <td><?php echo number_format($tot_paid); ?></td>
                <td><?php echo number_format($tot_princ_pd); ?></td>
                <td><?php echo number_format($tot_int_pd); ?></td>
                <td style="color:red;"><?php echo number_format($tot_fine); ?></td>
                <td><?php echo number_format($bal_p); ?></td>
                <td><?php echo number_format($bal_i); ?></td>
            </tr>
        </tbody>
    </table>
    
    <div style="margin-top:30px; font-size:10px; text-align:center;">
        Generated by System User: <?php echo $_SESSION['log_user']; ?> on <?php echo date("d-M-Y H:i"); ?>
    </div>
</body>
</html>