<?php include('db_connect.php'); ?>
<style>
    .on-print {
        display: none !important;
    }
    .text-monospace {
        font-family: SFMono-Regular, Menlo, Monaco, Consolas, monospace !important;
    }
    .badge-soft-dark {
        background-color: #343a40;
        color: #fff;
        padding: 3px 6px;
        font-size: 11px;
        border-radius: 3px;
    }
    .text-bolder {
        font-weight: 700 !important;
    }
</style>

<noscript>
    <style>
        body {
            font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;
            color: #222;
            margin: 0;
            padding: 0;
        }
        .text-center { text-align: center; }
        .text-right { text-align: right; }
        .text-muted { color: #6c757d; }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 15px;
        }
        tr, td, th {
            border: 1px solid #333;
            padding: 6px 8px;
            font-size: 11px;
            vertical-align: middle;
        }
        th {
            background-color: #f8f9fa !important;
            font-weight: bold;
            text-transform: uppercase;
            font-size: 10px;
            letter-spacing: 0.5px;
        }
        .on-print {
            display: block !important;
        }
        .text-monospace {
            font-family: monospace;
        }
        @page {
            size: A4 landscape;
            margin: 1.2cm;
        }
    </style>
</noscript>

<div class="container-fluid mt-3">
    <div class="col-lg-12">
        <div class="card shadow-sm border-0">
            <div class="card-body">
                <div class="col-md-12">
                    <div class="d-flex justify-content-between align-items-center mb-3">
                        <h5 class="text-dark mb-0"><b>Financial Outstanding Balances Ledger (Unified Statement)</b></h5>
                        <button class="btn btn-sm btn-success px-4 font-weight-bold" type="button" id="print">
                            <i class="fa fa-print"></i> Print Statement Sheet
                        </button>
                    </div>
                    <hr>
                    
                    <div id="report">
                        <div class="on-print text-center w-100 mb-4">
                            <h2 style="margin:0 0 5px 0; text-transform: uppercase; letter-spacing:1px;">Comprehensive Rental & Utilities Balances Report</h2>
                            <p style="margin:0; font-size:13px; color:#555;">Consolidated System Audit As of: <b><?php echo date('F d, Y') ?></b></p>
                            <hr style="border: 0; border-top: 2px solid #333; margin-top:12px;">
                        </div>
                        
                        <div class="table-responsive">
                            <table class="table table-bordered table-striped table-hover m-0" style="font-size: 13px;">
                                <thead class="bg-light text-dark">
                                    <tr>
                                        <th class="text-center" style="width: 3%;">#</th>
                                        <th style="width: 18%;">Tenant Occupant Name</th>
                                        <th class="text-center" style="width: 7%;">Unit</th>
                                        <th class="text-right" style="width: 9%;">Rent Rate</th>
                                        <th class="text-right" style="width: 8%;">Parking/Mo</th>
                                        <th class="text-right" style="width: 9%;">Security Dep.</th>
                                        <th class="text-right" style="width: 9%;">Utility Bills</th>
                                        <th class="text-center" style="width: 9%;">Billing Period</th>
                                        <th class="text-right" style="width: 10%;">Gross Payable</th>
                                        <th class="text-right" style="width: 10%;">Total Paid</th>
                                        <th class="text-right" style="width: 10%;">Balance Owed</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php 
                                    $i = 1;
                                    $total_rent_pool = 0;
                                    $total_parking_pool = 0;
                                    $total_deposit_pool = 0;
                                    $total_utility_pool = 0;
                                    
                                    $grand_payable = 0;
                                    $grand_paid = 0;
                                    $grand_outstanding = 0;

                                    $tenants = $conn->query("SELECT t.*, concat(t.lastname,', ',t.firstname,' ',t.middlename) as name, h.house_no, h.price as house_price FROM tenants t INNER JOIN houses h ON h.id = t.house_id WHERE t.status = 1 ORDER BY h.house_no ASC");
                                    
                                    if($tenants->num_rows > 0):
                                        while($row = $tenants->fetch_assoc()):
                                            $tenant_id = $row['id'];
                                            
                                            // 1. Compute dynamic timeline variables
                                            $date_in = new DateTime($row['date_in']);
                                            $current_date = new DateTime(date('Y-m-d'));
                                            $interval = $date_in->diff($current_date);
                                            
                                            $months_stayed = ($interval->y * 12) + $interval->m;
                                            if($interval->d > 0) {
                                                $months_stayed++; // Day overflow rounds up to next full month billing period
                                            }
                                            
                                            // Enforce mandatory 3-month upfront billing constraint if tenant is new
                                            $is_new_tenant = false;
                                            if($months_stayed < 3) {
                                                $billing_months = 3;
                                                $is_new_tenant = true;
                                            } else {
                                                $billing_months = $months_stayed;
                                            }

                                            // 2. Core itemized subtotal formulations using the computed window matrix
                                            $rent_subtotal = floatval($row['house_price']) * $billing_months;
                                            $parking_subtotal = floatval($row['parking_monthly']) * $billing_months;
                                            $security_deposit = floatval($row['security_deposit']);
                                            
                                            // 3. Aggregate metered utility ledger bills
                                            $util_q = $conn->query("SELECT SUM(amount) as utility_total FROM utility_bills WHERE tenant_id = $tenant_id");
                                            $util_data = $util_q->fetch_assoc();
                                            $utility_subtotal = !empty($util_data['utility_total']) ? floatval($util_data['utility_total']) : 0.00;
                                            
                                            // 4. Combined structural formulation 
                                            $payable = $rent_subtotal + $parking_subtotal + $security_deposit + $utility_subtotal;
                                            
                                            // 5. Total remittance historical calculation
                                            $paid_q = $conn->query("SELECT SUM(amount) as paid FROM payments WHERE tenant_id = $tenant_id");
                                            $paid_data = $paid_q->fetch_assoc();
                                            $paid = !empty($paid_data['paid']) ? floatval($paid_data['paid']) : 0.00;
                                            
                                            $outstanding = $payable - $paid;

                                            // Summary row aggregations mapping matrix
                                            $total_rent_pool += $rent_subtotal;
                                            $total_parking_pool += $parking_subtotal;
                                            $total_deposit_pool += $security_deposit;
                                            $total_utility_pool += $utility_subtotal;
                                            
                                            $grand_payable += $payable;
                                            $grand_paid += $paid;
                                            $grand_outstanding += $outstanding;
                                    ?>
                                    <tr>
                                        <td class="text-center align-middle"><?php echo $i++ ?></td>
                                        <td class="align-middle font-weight-bold text-dark">
                                            <?php echo ucwords($row['name']) ?>
                                            <?php if($is_new_tenant): ?>
                                                <small class="d-block text-danger font-weight-bold">(Initial Minimum Term Applied)</small>
                                            <?php endif; ?>
                                        </td>
                                        <td class="text-center align-middle font-weight-bold text-monospace"><span class="badge-soft-dark"><?php echo $row['house_no'] ?></span></td>
                                        <td class="text-right align-middle text-monospace text-muted"><?php echo number_format($row['house_price'], 2) ?></td>
                                        <td class="text-right align-middle text-monospace text-muted"><?php echo number_format($row['parking_monthly'], 2) ?></td>
                                        <td class="text-right align-middle text-monospace text-muted"><?php echo number_format($row['security_deposit'], 2) ?></td>
                                        <td class="text-right align-middle text-monospace text-info"><?php echo number_format($utility_subtotal, 2) ?></td>
                                        <td class="text-center align-middle text-monospace font-weight-bold text-secondary">
                                            <?php echo $billing_months ?> mo/s
                                            <?php if($months_stayed < $billing_months): ?>
                                                <small class="d-block text-muted style-italic" style="font-size:10px;">Actual: <?php echo $months_stayed ?> mo</small>
                                            <?php endif; ?>
                                        </td>
                                        <td class="text-right align-middle text-monospace font-weight-bold text-dark"><?php echo number_format($payable, 2) ?></td>
                                        <td class="text-right align-middle text-monospace text-success font-weight-medium"><?php echo number_format($paid, 2) ?></td>
                                        <td class="text-right align-middle text-monospace font-weight-bold <?php echo $outstanding > 0 ? 'text-danger' : 'text-secondary'; ?>">
                                            <?php echo number_format($outstanding, 2) ?>
                                        </td>
                                    </tr>
                                    <?php 
                                        endwhile; 
                                    ?>
                                    <tr class="bg-light font-weight-bold" style="border-top: 2.5px solid #222;">
                                        <td colspan="3" class="text-right align-middle text-dark text-bolder">GRAND TOTALS (KES):</td>
                                        <td class="text-right align-middle text-monospace text-muted" style="font-size:11px;">Rent Sub: <?php echo number_format($total_rent_pool, 2) ?></td>
                                        <td class="text-right align-middle text-monospace text-muted" style="font-size:11px;">Park Sub: <?php echo number_format($total_parking_pool, 2) ?></td>
                                        <td class="text-right align-middle text-monospace text-muted" style="font-size:11px;">Dep. Sub: <?php echo number_format($total_deposit_pool, 2) ?></td>
                                        <td class="text-right align-middle text-monospace text-info" style="font-size:11px;">Util Sub: <?php echo number_format($total_utility_pool, 2) ?></td>
                                        <td class="bg-white border-0"></td>
                                        <td class="text-right align-middle text-monospace text-dark" style="font-size:14px; background-color: #f1f3f5;"><?php echo number_format($grand_payable, 2) ?></td>
                                        <td class="text-right align-middle text-monospace text-success" style="font-size:14px; background-color: #f1f3f5;"><?php echo number_format($grand_paid, 2) ?></td>
                                        <td class="text-right align-middle text-monospace text-danger" style="font-size:14px; background-color: #e9ecef;"><?php echo number_format($grand_outstanding, 2) ?></td>
                                    </tr>
                                    <?php else: ?>
                                    <tr>
                                        <td colspan="11" class="text-center font-weight-bold p-5 text-muted">No active occupancy ledger records located inside data partitions.</td>
                                    </tr>
                                    <?php endif; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<script>
    $('#print').click(function(){
        var _style = $('noscript').clone();
        var _content = $('#report').clone();
        var nw = window.open("", "_blank", "width=1100,height=800");
        
        nw.document.write('<html><head><title>Print Rental Balances Summary Sheet</title>');
        nw.document.write('<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/css/bootstrap.min.css">');
        nw.document.write(_style.html());
        nw.document.write('</head><body class="bg-white">');
        nw.document.write('<div class="p-3">' + _content.html() + '</div>');
        nw.document.write('</body></html>');
        
        nw.document.close();
        
        setTimeout(function(){
            nw.focus();
            nw.print();
            setTimeout(function(){
                nw.close();
            }, 500);
        }, 750);
    });
</script>