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

<style>
    /* System Layout Isolation Context */
    .report-view-wrapper {
        padding: 20px;
        background-color: #f8fafc;
        min-height: calc(100vh - 3.5rem);
    }

    /* Modern Card Architecture */
    .report-card {
        border: none !important;
        border-radius: 16px !important;
        background: #ffffff;
        box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.05), 0 2px 4px -1px rgba(0, 0, 0, 0.03) !important;
        overflow: hidden;
    }

    .report-card-header {
        background: linear-gradient(135deg, #1e293b 0%, #334155 100%) !important;
        padding: 18px 24px !important;
        border-bottom: none !important;
    }

    .report-card-title {
        font-size: 1.1rem;
        font-weight: 600;
        letter-spacing: 0.3px;
        color: #ffffff !important;
    }

    /* Premium Button Frameworks */
    .btn-action-generate {
        background: linear-gradient(135deg, #2563eb 0%, #1d4ed8 100%) !important;
        color: #ffffff !important;
        border: none !important;
        border-radius: 8px !important;
        font-weight: 600 !important;
        font-size: 14px !important;
        box-shadow: 0 4px 6px -1px rgba(37, 99, 235, 0.2) !important;
        transition: all 0.2s ease !important;
    }

    .btn-action-generate:hover {
        transform: translateY(-1px);
        box-shadow: 0 6px 12px -1px rgba(37, 99, 235, 0.3) !important;
        opacity: 0.95;
        color: #ffffff !important;
    }

    /* Modernized Table Design Elements */
    #tenant-table {
        width: 100% !important;
        border-collapse: separate;
        border-spacing: 0;
        margin-top: 15px;
    }

    #tenant-table thead th {
        background-color: #f8fafc !important;
        font-weight: 700;
        text-transform: uppercase;
        font-size: 11px;
        letter-spacing: 0.5px;
        border: 1px solid #e2e8f0 !important;
        color: #0f172a !important;
        padding: 12px 16px;
    }

    #tenant-table tbody td {
        border: 1px solid #e2e8f0 !important;
        color: #334155 !important;
        padding: 14px 16px;
        vertical-align: middle !important;
    }

    #tenant-table tbody tr:hover td {
        background-color: #f1f5f9 !important;
    }

    td p { margin: unset; }

    /* Custom Badges & Frameworks */
    .badge { 
        font-size: 11px !important; 
        font-weight: 600 !important;
        padding: 5px 10px !important;
        border-radius: 6px !important;
    }
    
    .badge-success {
        background-color: #dcfce7 !important;
        color: #15803d !important;
    }

    .badge-danger {
        background-color: #fee2e2 !important;
        color: #b91c1c !important;
    }

    .billing-breakdown small { 
        line-height: 1.5; 
        font-size: 12px;
    }
</style>

<div class="report-view-wrapper">
    <div class="container-fluid px-0">
        <div class="col-lg-12">
            <div class="card report-card">
                
                <div class="card-header report-card-header d-flex justify-content-between align-items-center">
                    <span class="report-card-title">
                        <i class="fa fa-users mr-2 text-info"></i> 
                        Tenant List & Account Summary
                    </span>
                    <button class="btn btn-action-generate px-3 py-2 d-flex align-items-center" type="button" id="new_tenant">
                        <i class="fa fa-plus mr-2"></i> New Tenant
                    </button>
                </div>
                
                <div class="card-body p-4">
                    <div class="table-responsive">
                        <table class="table table-hover" id="tenant-table">
                            <thead>
                                <tr>
                                    <th class="text-center" style="width: 5%">#</th>
                                    <th style="width: 20%">Tenant Details</th>
                                    <th style="width: 15%">Property</th>
                                    <th style="width: 25%">Billed Charges Breakdown</th>
                                    <th style="width: 15%">Total Balance</th>
                                    <th style="width: 12%">Last Payment</th>
                                    <th class="text-center" style="width: 13%">Action</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php 
                                $i = 1;
                                $tenant = $conn->query("SELECT t.*,concat(t.lastname,', ',t.firstname,' ',t.middlename) as name, h.house_no, h.price, h.building 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=$tenant->fetch_assoc()):
                                    // 1. Calculate Stay Duration accurately
                                    $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. Apply "3+1" rule conditions consistently
                                    $billing_months = ($months_stayed < 3) ? 3 : $months_stayed;

                                    // 3. Fetch Utilities matching total logs
                                    $utils = $conn->query("SELECT SUM(amount) as utotal FROM utility_bills where tenant_id = ".$row['id']);
                                    $u_total = $utils->num_rows > 0 ? $utils->fetch_array()['utotal'] : 0;
                                    $u_total = $u_total > 0 ? $u_total : 0;

                                    // 4. Calculate Aggregate Compound Balances
                                    $total_rent = $row['price'] * $billing_months;
                                    $total_parking = $row['parking_monthly'] * $billing_months;
                                    $payable = $total_rent + $total_parking + $u_total + $row['security_deposit'];

                                    // 5. Fetch Total Payments Received
                                    $paid_query = $conn->query("SELECT SUM(amount) as paid FROM payments where tenant_id =".$row['id']);
                                    $paid = $paid_query->num_rows > 0 ? $paid_query->fetch_array()['paid'] : 0;

                                    $outstanding = $payable - $paid;

                                    // Fetch Last Payment Date String
                                    $last_p = $conn->query("SELECT date_created FROM payments where tenant_id =".$row['id']." order by date_created desc limit 1");
                                    $last_payment = $last_p->num_rows > 0 ? date("M d, Y", strtotime($last_p->fetch_array()['date_created'])) : 'No Payments';
                                ?>
                                <tr>
                                    <td class="text-center font-weight-bold text-muted"><?php echo $i++ ?></td>
                                    <td>
                                        <p class="mb-0 text-dark font-weight-bold"><?php echo ucwords($row['name']) ?></p>
                                        <small class="text-muted"><i class="fa fa-calendar-alt mr-1"></i> In: <?php echo date("M d, Y", strtotime($row['date_in'])) ?></small>
                                    </td>
                                    <td>
                                        <p class="mb-0">Unit: <span class="font-weight-bold text-dark"><?php echo $row['house_no'] ?></span></p>
                                        <small class="text-muted"><i class="fa fa-building mr-1"></i> <?php echo $row['building'] ?></small>
                                    </td>
                                    <td>
                                        <div class="billing-breakdown bg-light p-2 rounded border border-light">
                                            <small class="d-block text-secondary">Rent (Accumulated): <b class="text-dark"><?php echo number_format($total_rent,2) ?></b></small>
                                            <small class="d-block text-secondary">Deposit (One-time): <b class="text-dark"><?php echo number_format($row['security_deposit'],2) ?></b></small>
                                            <?php if($total_parking > 0): ?>
                                                <small class="d-block text-secondary">Parking Total: <b class="text-dark"><?php echo number_format($total_parking,2) ?></b></small>
                                            <?php endif; ?>
                                            <small class="d-block text-info">Utilities Total: <b><?php echo number_format($u_total,2) ?></b></small>
                                            <hr class="my-1" style="border-top: 1px solid #e2e8f0;">
                                            <small class="d-block text-slate font-weight-bold text-dark">Gross Billed: <?php echo number_format($payable,2) ?></small>
                                        </div>
                                    </td>
                                    <td class="text-right">
                                        <p class="font-weight-bold mb-1 <?php echo $outstanding > 0 ? 'text-danger' : 'text-success' ?>" style="font-size: 15px;">
                                            <?php echo number_format($outstanding, 2) ?>
                                        </p>
                                        <?php if($outstanding > 0): ?>
                                            <span class="badge badge-danger">Unpaid Balance</span>
                                        <?php else: ?>
                                            <span class="badge badge-success">Cleared</span>
                                        <?php endif; ?>
                                    </td>
                                    <td><em class="text-secondary small font-weight-bold"><?php echo $last_payment ?></em></td>
                                    <td class="text-center">
                                        <div class="btn-group btn-group-sm rounded shadow-sm">
                                            <button class="btn btn-white text-primary border font-weight-bold view_payment" type="button" data-id="<?php echo $row['id'] ?>" title="View Ledger"><i class="fa fa-eye"></i></button>
                                            <button class="btn btn-white text-dark border font-weight-bold edit_tenant" type="button" data-id="<?php echo $row['id'] ?>" title="Edit Tenant"><i class="fa fa-edit"></i></button>
                                            <button class="btn btn-white text-danger border font-weight-bold delete_tenant" type="button" data-id="<?php echo $row['id'] ?>" title="Delete Tenant"><i class="fa fa-trash-alt"></i></button>
                                        </div>
                                    </td>
                                </tr>
                                <?php endwhile; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
                
            </div>          
        </div>
    </div>
</div>

<script>
    $(document).ready(function(){
        if($('#tenant-table').length > 0){
            $('#tenant-table').dataTable({
                "bAutoWidth": false,
                "sort": true
            });
        }
    });
    
    $('#new_tenant').click(function(){
        uni_modal("New Tenant Registration","manage_tenant.php","mid-large");
    });

    $('.view_payment').click(function(){
        uni_modal("Payment History Summary & Ledger","view_payment.php?id="+$(this).attr('data-id'),"large");
    });

    $('.edit_tenant').click(function(){
        uni_modal("Modify Tenant Core Details","manage_tenant.php?id="+$(this).attr('data-id'),"mid-large");
    });

    $('.delete_tenant').click(function(){
        _conf("Are you sure you want to permanently delete this tenant? This action cannot be reversed.","delete_tenant",[$(this).attr('data-id')]);
    });

    function delete_tenant($id){
        start_load();
        $.ajax({
            url:'ajax.php?action=delete_tenant',
            method:'POST',
            data:{id:$id},
            success:function(resp){
                if(resp==1){
                    alert_toast("Tenant account record successfully deleted.",'success');
                    setTimeout(function(){
                        location.reload();
                    },1000);
                } else {
                    alert_toast("An error occurred while removing the data record.",'danger');
                    end_load();
                }
            }
        });
    }
</script>