<?php include('db_connect.php');?>

<div class="container-fluid">
    <div class="col-lg-12 mt-3">
        <div class="card">
            <div class="card-header d-flex justify-content-between align-items-center bg-light">
                <h5 class="mb-0 text-dark"><b>Rent & Utility Payments Dashboard</b></h5>
                <button class="btn btn-primary btn-sm px-3" type="button" id="new_payment">
                    <i class="fa fa-plus"></i> Record New Payment
                </button>
            </div>
            <div class="card-body">
                <table class="table table-condensed table-bordered table-hover" id="payment-panel-table">
                    <thead>
                        <tr>
                            <th class="text-center">#</th>
                            <th>Tenant Name</th>
                            <th>House Unit No</th>
                            <th class="text-right">Outstanding Balance</th>
                            <th>Last Payment Date</th>
                            <th class="text-center">Action Summary</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php 
                        $i = 1;
                        // Pull active tenants and their underlying property configuration
                        $tenants = $conn->query("SELECT t.*, concat(t.lastname,', ',t.firstname,' ',t.middlename) as name, h.house_no, h.price FROM tenants t inner join houses h on h.id = t.house_id where t.status = 1 order by h.house_no desc ");
                        
                        while($row = $tenants->fetch_assoc()):
                            $tenant_id = $row['id'];

                            // 1. Calculate Months Stayed
                            $date_in = new DateTime($row['date_in']);
                            $today = new DateTime(date('Y-m-d'));
                            $interval = $date_in->diff($today);
                            $months_stayed = ($interval->y * 12) + $interval->m;

                            // 2. Enforce the "3+1" minimum baseline period rule
                            $billing_months = ($months_stayed < 3) ? 3 : $months_stayed;

                            // 3. Aggregate Total Accumulated Utilities (Garbage, Water, etc.)
                            $utils = $conn->query("SELECT SUM(amount) as utotal FROM utility_bills where tenant_id = " . $tenant_id);
                            $u_total = $utils->num_rows > 0 ? $utils->fetch_array()['utotal'] : 0;
                            $u_total = $u_total > 0 ? $u_total : 0;

                            // 4. Sum up Gross Liabilities (Broken down by asset categories)
                            $total_rent_liability = $row['price'] * $billing_months;
                            $total_parking_liability = $row['parking_monthly'] * $billing_months;
                            $gross_payable = $total_rent_liability + $total_parking_liability + $u_total + $row['security_deposit'];

                            // 5. Aggregate Categorized Payments Made (Rent, Parking, Utility Sheets)
                            $payments_query = $conn->query("
                                SELECT 
                                    SUM(CASE WHEN type = 'rent' THEN amount ELSE 0 END) as rent_paid,
                                    SUM(CASE WHEN type = 'parking' THEN amount ELSE 0 END) as parking_paid,
                                    SUM(CASE WHEN type = 'utility' THEN amount ELSE 0 END) as utility_paid,
                                    SUM(amount) as total_paid 
                                FROM payments 
                                WHERE tenant_id = " . $tenant_id
                            );
                            
                            $p_res = $payments_query->fetch_assoc();
                            $paid = isset($p_res['total_paid']) ? $p_res['total_paid'] : 0;

                            // 6. Get Metadata for the Last Isolated Transaction Entry
                            $last_payment_query = $conn->query("SELECT date_created FROM payments where tenant_id = " . $tenant_id . " order by date_created desc limit 1");
                            $last_payment = $last_payment_query->num_rows > 0 ? date("M d, Y", strtotime($last_payment_query->fetch_array()['date_created'])) : '<em>No payments recorded</em>';

                            // Determine the Net Balance Remainder
                            $outstanding = $gross_payable - $paid;
                        ?>
                        <tr>
                            <td class="text-center"><?php echo $i++ ?></td>
                            <td>
                                <span class="font-weight-bold text-dark"><?php echo ucwords($row['name']) ?></span>
                            </td>
                            <td>
                                <span class="badge badge-secondary px-2 py-1">Unit <?php echo $row['house_no'] ?></span>
                            </td>
                            <td class="text-right">
                                <span class="font-weight-bold d-block <?php echo $outstanding > 0 ? 'text-danger' : 'text-success' ?>">
                                    <?php echo number_format($outstanding, 2) ?>
                                </span>
                                <?php if($outstanding > 0): ?>
                                    <span class="badge badge-pill badge-danger-light text-danger font-weight-normal" style="background-color: #fce8e6; font-size: 10px;">Unpaid</span>
                                <?php else: ?>
                                    <span class="badge badge-pill badge-success-light text-success font-weight-normal" style="background-color: #e6f4ea; font-size: 10px;">Cleared</span>
                                <?php endif; ?>
                            </td>
                            <td>
                                <small class="text-muted"><?php echo $last_payment ?></small>
                            </td>
                            <td class="text-center">
                                <button class="btn btn-sm btn-outline-primary view_payment font-weight-bold" type="button" data-id="<?php echo $row['id'] ?>">
                                    <i class="fa fa-history"></i> View Ledger Logs
                                </button>
                            </td>
                        </tr>
                        <?php endwhile; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>  
</div>

<style>
    td {
        vertical-align: middle !important;
    }
    td p {
        margin: unset;
    }
    .badge {
        font-size: 0.75rem;
    }
</style>

<script>
    $(document).ready(function(){
        if($('#payment-panel-table').length > 0){
            $('#payment-panel-table').dataTable({
                "sort": true,
                "info": true
            });
        }
    });
    
    $('#new_payment').click(function(){
        uni_modal("Record New Cash/M-Pesa Remittance","manage_payment.php","mid-large");
    });
    
    $('.view_payment').click(function(){
        uni_modal("Tenant Statement & Transaction Logs","view_payment.php?id="+$(this).attr('data-id'),"large");
    });
    
    function delete_payment($id){
        start_load();
        $.ajax({
            url: 'ajax.php?action=delete_payment',
            method: 'POST',
            data: { id: $id },
            success: function(resp){
                if(resp == 1){
                    alert_toast("Payment entry successfully wiped from ledger.", 'success');
                    setTimeout(function(){
                        location.reload();
                    }, 1000);
                } else {
                    alert_toast("An infrastructure error blocked transaction adjustment.", 'danger');
                    end_load();
                }
            }
        });
    }
</script>