<?php
session_start();
ini_set('display_errors', 1);
Class Action {
    private $db;

    public function __construct() {
        ob_start();
        include 'db_connect.php';
        
        $this->db = $conn;
    }
    function __destruct() {
        $this->db->close();
        ob_end_flush();
    }

    function login(){
        
            extract($_POST);        
            $qry = $this->db->query("SELECT * FROM users where username = '".$username."' and password = '".md5($password)."' ");
            if($qry->num_rows > 0){
                foreach ($qry->fetch_array() as $key => $value) {
                    if($key != 'passwors' && !is_numeric($key))
                        $_SESSION['login_'.$key] = $value;
                }
                if($_SESSION['login_type'] != 1){
                    foreach ($_SESSION as $key => $value) {
                        unset($_SESSION[$key]);
                    }
                    return 2 ;
                    exit;
                }
                    return 1;
            }else{
                return 3;
            }
    }
    function login2(){
        
            extract($_POST);
            if(isset($email))
                $username = $email;
        $qry = $this->db->query("SELECT * FROM users where username = '".$username."' and password = '".md5($password)."' ");
        if($qry->num_rows > 0){
            foreach ($qry->fetch_array() as $key => $value) {
                if($key != 'passwors' && !is_numeric($key))
                    $_SESSION['login_'.$key] = $value;
            }
            if($_SESSION['login_alumnus_id'] > 0){
                $bio = $this->db->query("SELECT * FROM alumnus_bio where id = ".$_SESSION['login_alumnus_id']);
                if($bio->num_rows > 0){
                    foreach ($bio->fetch_array() as $key => $value) {
                        if($key != 'passwors' && !is_numeric($key))
                            $_SESSION['bio'][$key] = $value;
                    }
                }
            }
            if($_SESSION['bio']['status'] != 1){
                    foreach ($_SESSION as $key => $value) {
                        unset($_SESSION[$key]);
                    }
                    return 2 ;
                    exit;
                }
                return 1;
        }else{
            return 3;
        }
    }
    function logout(){
        session_destroy();
        foreach ($_SESSION as $key => $value) {
            unset($_SESSION[$key]);
        }
        header("location:login.php");
    }
    function logout2(){
        session_destroy();
        foreach ($_SESSION as $key => $value) {
            unset($_SESSION[$key]);
        }
        header("location:../index.php");
    }

    function save_user(){
        extract($_POST);
        $data = " name = '$name' ";
        $data .= ", username = '$username' ";
        if(!empty($password))
        $data .= ", password = '".md5($password)."' ";
        $data .= ", type = '$type' ";
        if($type == 1)
            $establishment_id = 0;
        $data .= ", establishment_id = '$establishment_id' ";
        $chk = $this->db->query("Select * from users where username = '$username' and id !='$id' ")->num_rows;
        if($chk > 0){
            return 2;
            exit;
        }
        if(empty($id)){
            $save = $this->db->query("INSERT INTO users set ".$data);
        }else{
            $save = $this->db->query("UPDATE users set ".$data." where id = ".$id);
        }
        if($save){
            return 1;
        }
    }
    function delete_user(){
        extract($_POST);
        $delete = $this->db->query("DELETE FROM users where id = ".$id);
        if($delete)
            return 1;
    }
    function signup(){
        extract($_POST);
        $data = " name = '".$firstname.' '.$lastname."' ";
        $data .= ", username = '$email' ";
        $data .= ", password = '".md5($password)."' ";
        $chk = $this->db->query("SELECT * FROM users where username = '$email' ")->num_rows;
        if($chk > 0){
            return 2;
            exit;
        }
            $save = $this->db->query("INSERT INTO users set ".$data);
        if($save){
            $uid = $this->db->insert_id;
            $data = '';
            foreach($_POST as $k => $v){
                if($k =='password')
                    continue;
                if(empty($data) && !is_numeric($k) )
                    $data = " $k = '$v' ";
                else
                    $data .= ", $k = '$v' ";
            }
            if($_FILES['img']['tmp_name'] != ''){
                            $fname = strtotime(date('y-m-d H:i')).'_'.$_FILES['img']['name'];
                            $move = move_uploaded_file($_FILES['img']['tmp_name'],'assets/uploads/'. $fname);
                            $data .= ", avatar = '$fname' ";

            }
            $save_alumni = $this->db->query("INSERT INTO alumnus_bio set $data ");
            if($data){
                $aid = $this->db->insert_id;
                $this->db->query("UPDATE users set alumnus_id = $aid where id = $uid ");
                $login = $this->login2();
                if($login)
                return 1;
            }
        }
    }
    function update_account(){
        extract($_POST);
        $data = " name = '".$firstname.' '.$lastname."' ";
        $data .= ", username = '$email' ";
        if(!empty($password))
        $data .= ", password = '".md5($password)."' ";
        $chk = $this->db->query("SELECT * FROM users where username = '$email' and id != '{$_SESSION['login_id']}' ")->num_rows;
        if($chk > 0){
            return 2;
            exit;
        }
            $save = $this->db->query("UPDATE users set $data where id = '{$_SESSION['login_id']}' ");
        if($save){
            $data = '';
            foreach($_POST as $k => $v){
                if($k =='password')
                    continue;
                if(empty($data) && !is_numeric($k) )
                    $data = " $k = '$v' ";
                else
                    $data .= ", $k = '$v' ";
            }
            if($_FILES['img']['tmp_name'] != ''){
                            $fname = strtotime(date('y-m-d H:i')).'_'.$_FILES['img']['name'];
                            $move = move_uploaded_file($_FILES['img']['tmp_name'],'assets/uploads/'. $fname);
                            $data .= ", avatar = '$fname' ";

            }
            $save_alumni = $this->db->query("UPDATE alumnus_bio set $data where id = '{$_SESSION['bio']['id']}' ");
            if($data){
                foreach ($_SESSION as $key => $value) {
                    unset($_SESSION[$key]);
                }
                $login = $this->login2();
                if($login)
                return 1;
            }
        }
    }

    function save_settings(){
        extract($_POST);
        $data = " name = '".str_replace("'","&#x2019;",$name)."' ";
        $data .= ", email = '$email' ";
        $data .= ", contact = '$contact' ";
        $data .= ", about_content = '".htmlentities(str_replace("'","&#x2019;",$about))."' ";
        if($_FILES['img']['tmp_name'] != ''){
                        $fname = strtotime(date('y-m-d H:i')).'_'.$_FILES['img']['name'];
                        $move = move_uploaded_file($_FILES['img']['tmp_name'],'assets/uploads/'. $fname);
                    $data .= ", cover_img = '$fname' ";

        }
        
        // echo "INSERT INTO system_settings set ".$data;
        $chk = $this->db->query("SELECT * FROM system_settings");
        if($chk->num_rows > 0){
            $save = $this->db->query("UPDATE system_settings set ".$data);
        }else{
            $save = $this->db->query("INSERT INTO system_settings set ".$data);
        }
        if($save){
        $query = $this->db->query("SELECT * FROM system_settings limit 1")->fetch_array();
        foreach ($query as $key => $value) {
            if(!is_numeric($key))
                $_SESSION['system'][$key] = $value;
        }

            return 1;
                }
    }

    
    function save_category(){
        extract($_POST);
        $data = " name = '$name' ";
            if(empty($id)){
                $save = $this->db->query("INSERT INTO categories set $data");
            }else{
                $save = $this->db->query("UPDATE categories set $data where id = $id");
            }
        if($save)
            return 1;
    }
    function delete_category(){
        extract($_POST);
        $delete = $this->db->query("DELETE FROM categories where id = ".$id);
        if($delete){
            return 1;
        }
    }
    function save_house(){
        extract($_POST);
        
        // Capture the new footprint measurement
        $sqm = isset($_POST['sqm']) ? $_POST['sqm'] : 0;

        // Build the query data string mapping parameters
        $data = " house_no = '$house_no' ";
        $data .= ", building = '$building' ";
        $data .= ", floor = '$floor' ";
        $data .= ", sqm = '$sqm' "; // Added property footprint parameter
        $data .= ", description = '$description' ";
        $data .= ", category_id = '$category_id' ";
        $data .= ", price = '$price' ";

        // Structural integrity check: Prevent duplicate house numbers within the same building block
        if(empty($id)){
            $chk = $this->db->query("SELECT * FROM houses where house_no = '$house_no' and building = '$building'")->num_rows;
        } else {
            $chk = $this->db->query("SELECT * FROM houses where house_no = '$house_no' and building = '$building' and id != $id")->num_rows;
        }

        if($chk > 0){
            return 2; // Returns status code matching front-end warning for existing structural duplicates
            exit;
        }

        // Process database upsert statement
        if(empty($id)){
            $save = $this->db->query("INSERT INTO houses set $data");
        } else {
            $save = $this->db->query("UPDATE houses set $data where id = $id");
        }

        if($save){
            return 1; // Success status flag
        } else {
            return 0; // Failure/Database fallback error flag
        }
    }

    function delete_house(){
        extract($_POST);
        $delete = $this->db->query("DELETE FROM houses where id = ".$id);
        if($delete){
            return 1;
        }
    }
    function save_tenant(){
        extract($_POST);
        $data = " firstname = '$firstname' ";
        $data .= ", lastname = '$lastname' ";
        $data .= ", middlename = '$middlename' ";
        $data .= ", email = '$email' ";
        $data .= ", contact = '$contact' ";
        $data .= ", house_id = '$house_id' ";
        $data .= ", date_in = '$date_in' ";
        $data .= ", security_deposit = '$security_deposit' ";
        $data .= ", parking_monthly = '$parking_monthly' ";

        // Handle File Upload for Tenancy Agreement
        if(isset($_FILES['agreement_doc']) && $_FILES['agreement_doc']['tmp_name'] != ''){
            $fname = strtotime(date('y-m-d H:i')).'_'.$_FILES['agreement_doc']['name'];
            // Create folder if it doesn't exist
            if(!is_dir('assets/uploads/contracts')) mkdir('assets/uploads/contracts');
            
            $move = move_uploaded_file($_FILES['agreement_doc']['tmp_name'], 'assets/uploads/contracts/'. $fname);
            if($move){
                $data .= ", agreement_path = '$fname' ";
            }
        }

        if(empty($id)){
            $data .= ", status = 1 ";
            $save = $this->db->query("INSERT INTO tenants set $data");
        }else{
            $save = $this->db->query("UPDATE tenants set $data where id = $id");
        }

        if($save)
            return 1;
    }
    function delete_tenant(){
        extract($_POST);
        $delete = $this->db->query("UPDATE tenants set status = 0 where id = ".$id);
        if($delete){
            return 1;
        }
    }
    function get_tdetails(){
    extract($_POST);
    $data = array();
    $tenants = $this->db->query("SELECT t.*, concat(t.lastname,', ',t.firstname) as name, h.house_no, h.price, h.building FROM tenants t inner join houses h on h.id = t.house_id where t.id = {$id} ");
    foreach($tenants->fetch_array() as $k => $v){
        if(!is_numeric($k)) $$k = $v;
    }

    // 1. Calculate Months Stayed
    $d1 = new DateTime($date_in);
    $d2 = new DateTime(date('Y-m-d'));
    $interval = $d1->diff($d2);
    $months = ($interval->y * 12) + $interval->m;

    // Enforce your minimum 3-month baseline period protection rule consistently
    $billing_months = ($months < 3) ? 3 : $months;

    // 2. Fetch Total Utility Bills for this tenant
    $utils = $this->db->query("SELECT SUM(amount) as utotal FROM utility_bills where tenant_id = $id");
    $u_total = $utils->num_rows > 0 ? $utils->fetch_array()['utotal'] : 0;
    $u_total = $u_total > 0 ? $u_total : 0;

    // 3. Calculate Global Payable Liabilities
    $total_rent = $price * $billing_months;
    $total_parking = (isset($parking_monthly) ? $parking_monthly : 0) * $billing_months;
    
    // Formula: Rent + Parking + Utilities + Security Deposit
    $payable = $total_rent + $total_parking + $u_total + (isset($security_deposit) ? $security_deposit : 0);

    $data['payable'] = number_format($payable, 2, '.', '');
    
    // 4. Total Paid (Existing Payments)
    $paid = $this->db->query("SELECT SUM(amount) as paid FROM payments where tenant_id = ".$id.(isset($pid) && !empty($pid) ? " and id != '$pid'" : ""));
    $paid = $paid->num_rows > 0 ? $paid->fetch_array()['paid'] : 0;
    $paid = $paid > 0 ? $paid : 0;
    
    $data['paid'] = number_format($paid, 2, '.', '');
    $data['outstanding'] = number_format($payable - $paid, 2, '.', '');
    
    // Expose individual component pricing structures for the manage_payment UI form fields
    $data['rent_price'] = $price;
    $data['parking_price'] = isset($parking_monthly) ? $parking_monthly : 0;
    
    // Metadata block string description for the UI info panel alert window
    $data['details'] = "Accumulated Rent (" . $billing_months . " mos): KES " . number_format($total_rent, 2) . " | Utilities Due: KES " . number_format($u_total, 2) . " | Parking Due: KES " . number_format($total_parking, 2);
    
    return json_encode($data);
}
    
    function save_payment(){
        extract($_POST);
        $data = "";
        foreach($_POST as $k => $v){
            if(!in_array($k, array('id','ref_code')) && !is_numeric($k)){
                if(empty($data)){
                    $data .= " $k='$v' ";
                }else{
                    $data .= ", $k='$v' ";
                }
            }
        }
        if(empty($id)){
            $save = $this->db->query("INSERT INTO payments set $data");
            $id=$this->db->insert_id;
        }else{
            $save = $this->db->query("UPDATE payments set $data where id = $id");
        }

        if($save){
            return 1;
        }
    }
    function delete_payment(){
        extract($_POST);
        $delete = $this->db->query("DELETE FROM payments where id = ".$id);
        if($delete){
            return 1;
        }
    }
    function save_utility(){
        extract($_POST);
        $data = " tenant_id = '$tenant_id' ";
        $data .= ", type = '$type' ";
        $data .= ", reading = '$reading' ";
        $data .= ", amount = '$amount' ";
        $data .= ", billing_date = '$billing_date' ";
        
        if(empty($id)){
            $save = $this->db->query("INSERT INTO utility_bills set $data");
        }else{
            $save = $this->db->query("UPDATE utility_bills set $data where id = $id");
        }
        if($save) return 1;
    }

    function delete_utility(){
        extract($_POST);
        $delete = $this->db->query("DELETE FROM utility_bills where id = ".$id);
        if($delete) return 1;
    }

    function get_report(){
    extract($_POST);
    $data = array();
    $month_str = date("F, Y", strtotime($month));
    
    $target_year_month = date('Y-m', strtotime($month)); 
    $day_15_deadline = $target_year_month . "-15 23:59:59";
    $month_end_date = date('Y-m-t', strtotime($month . "-01"));
    
    $building_where = ($building != 'all') ? " AND h.building = '".$this->db->real_escape_string($building)."'" : "";

    $tenants = $this->db->query("SELECT t.*, concat(t.lastname,', ',t.firstname) as name, h.house_no, h.price as rent_price, h.building, h.floor 
                                 FROM tenants t 
                                 INNER JOIN houses h ON h.id = t.house_id 
                                 WHERE t.status = 1 $building_where 
                                 ORDER BY name ASC");

    ob_start();
    ?>
    <div class="container-fluid">
        <h4 class="text-center mb-1"><b>Collection & Financial Allocation Report</b></h4>
        <p class="text-center text-muted mb-4">Target Scope: <b><?php echo ($building == 'all' ? "All Buildings / Blocks" : ucwords($building)) ?></b> — Period: <b><?php echo $month_str ?></b></p>
        
        <?php if($tenants->num_rows > 0): ?>
        <div class="table-responsive">
            <table class="table table-bordered table-striped table-hover align-middle" id="report-table" style="font-size: 13px;">
                <thead class="bg-dark text-white header-accent">
                    <tr>
                        <th rowspan="2" class="text-center align-middle" style="width: 4%;">#</th>
                        <th rowspan="2" class="align-middle" style="width: 20%;">Tenant & Unit Details</th>
                        <th colspan="3" class="text-center bg-primary text-white py-1">House Rent (KES)</th>
                        <th colspan="2" class="text-center bg-info text-white py-1">Parking Space (KES)</th>
                        <th colspan="2" class="text-center bg-warning text-dark py-1">Utilities & Garbage (KES)</th>
                        <th rowspan="2" class="text-right align-middle bg-danger text-white" style="width: 8%;">Late Penalty (5%)</th>
                        <th rowspan="2" class="text-right align-middle bg-secondary text-white" style="width: 10%;">Outstanding Balance</th>
                    </tr>
                    <tr>
                        <th class="text-right text-monospace" style="font-size: 11px; width: 9%;">Charged (-12% WHT)</th>
                        <th class="text-right text-monospace" style="font-size: 11px; width: 8%;">Paid By 15th</th>
                        <th class="text-right text-monospace" style="font-size: 11px; width: 8%;">Paid After 15th</th>
                        <th class="text-right text-monospace" style="font-size: 11px; width: 7%;">Charged</th>
                        <th class="text-right text-monospace" style="font-size: 11px; width: 7%;">Paid</th>
                        <th class="text-right text-monospace" style="font-size: 11px; width: 7%;">Charged</th>
                        <th class="text-right text-monospace" style="font-size: 11px; width: 7%;">Paid</th>
                    </tr>
                </thead>
                <tbody>
                    <?php 
                    $i = 1;
                    $g_rent_charge = $g_rent_paid_early = $g_rent_paid_late = 0;
                    $g_park_charge = $g_park_paid = 0;
                    $g_util_charge = $g_util_paid = 0;
                    $g_penalty_charged = 0;
                    $g_total_balance = 0;

                    while($row = $tenants->fetch_assoc()):
                        $id = $row['id'];
                        
                        // FIX: Calculate months based strictly on Year and Month, ignoring the moving day
                        $start_year = date('Y', strtotime($row['date_in']));
                        $start_month = date('m', strtotime($row['date_in']));
                        $target_year = date('Y', strtotime($month . "-01"));
                        $target_month_num = date('m', strtotime($month . "-01"));
                        
                        if ($target_year_month < date('Y-m', strtotime($row['date_in']))) {
                            $billing_months = 0;
                        } else {
                            $months_diff = (($target_year - $start_year) * 12) + ($target_month_num - $start_month) + 1;
                            // Enforce your system's 3-month initial baseline baseline period check
                            $billing_months = ($months_diff > 0 && $months_diff < 3) ? 3 : $months_diff;
                        }

                        if ($billing_months == 0) continue; // Skip displaying if tenant hasn't joined yet

                        // Base Liabilities Calculation
                        $base_rent = floatval($row['rent_price']);
                        
                        // Handle the WHT/VAT 12% Reduction Check
                        if(isset($row['apply_wht']) && $row['apply_wht'] == 1 || isset($row['wht_applicable'])) {
                            $rent_charged = ($base_rent * 0.88) * $billing_months; 
                        } else {
                            $rent_charged = $base_rent * $billing_months;
                        }
                        
                        $parking_charged = (isset($row['parking_monthly']) ? floatval($row['parking_monthly']) : 0) * $billing_months;

                        $util_q = $this->db->query("SELECT SUM(amount) as total FROM utility_bills WHERE tenant_id = $id AND DATE(billing_date) <= '{$month_end_date}'");
                        $util_charged = ($util_q && $util_q->num_rows > 0) ? (float)$util_q->fetch_assoc()['total'] : 0;

                        // Payments split into timeline-aware buckets (On/Before 15th vs After 15th)
                        $pmts_q = $this->db->query("SELECT 
                            SUM(CASE WHEN (LOWER(type) = 'rent' OR type IS NULL OR type = '') AND date_created <= '{$day_15_deadline}' THEN amount ELSE 0 END) as rent_paid_early,
                            SUM(CASE WHEN (LOWER(type) = 'rent' OR type IS NULL OR type = '') AND date_created > '{$day_15_deadline}' THEN amount ELSE 0 END) as rent_paid_late,
                            SUM(CASE WHEN LOWER(type) = 'parking' THEN amount ELSE 0 END) as parking_paid,
                            SUM(CASE WHEN LOWER(type) = 'utility' THEN amount ELSE 0 END) as utility_paid
                            FROM payments WHERE tenant_id = $id AND DATE(date_created) <= '{$month_end_date}'");
                        
                        $pmts = $pmts_q->fetch_assoc();
                        $rent_paid_early = (float)($pmts['rent_paid_early'] ?? 0);
                        $rent_paid_late = (float)($pmts['rent_paid_late'] ?? 0);
                        $rent_total_paid = $rent_paid_early + $rent_paid_late;
                        
                        $parking_paid = (float)($pmts['parking_paid'] ?? 0);
                        $util_paid = (float)($pmts['utility_paid'] ?? 0);

                        // Calculate 5% Penalties dynamically if current date is past the 18th
                        $penalty_charged = 0;
                        $current_date = date('Y-m-d');
                        $day_18_deadline = $target_year_month . "-18";
                        
                        if ($current_date > $day_18_deadline) {
                            $rent_arrears = $rent_charged - $rent_total_paid;
                            if ($rent_arrears > 0) {
                                $penalty_charged = $rent_arrears * 0.05; 
                            }
                        }

                        $tenant_balance = ($rent_charged + $parking_charged + $util_charged + $penalty_charged) - ($rent_total_paid + $parking_paid + $util_paid);

                        // Accumulate Grand Financial Totals
                        $g_rent_charge += $rent_charged; 
                        $g_rent_paid_early += $rent_paid_early;
                        $g_rent_paid_late += $rent_paid_late;
                        $g_park_charge += $parking_charged; 
                        $g_park_paid += $parking_paid;
                        $g_util_charge += $util_charged; 
                        $g_util_paid += $util_paid;
                        $g_penalty_charged += $penalty_charged;
                        $g_total_balance += $tenant_balance;
                    ?>
                    <tr>
                        <td class="text-center align-middle font-weight-bold text-secondary"><?php echo $i++; ?></td>
                        <td class="align-middle">
                            <div class="font-weight-bold text-dark"><?php echo ucwords($row['name']); ?></div>
                            <small class="text-muted"><i class="fa fa-building text-secondary"></i> <?php echo ucwords($row['building']); ?> — Unit <?php echo $row['house_no']; ?></small>
                        </td>
                        
                        <td class="text-right align-middle text-monospace text-secondary"><?php echo number_format($rent_charged, 2); ?></td>
                        <td class="text-right align-middle text-monospace font-weight-bold text-success"><?php echo number_format($rent_paid_early, 2); ?></td>
                        <td class="text-right align-middle text-monospace font-weight-bold text-primary"><?php echo number_format($rent_paid_late, 2); ?></td>
                        
                        <td class="text-right align-middle text-monospace text-secondary"><?php echo number_format($parking_charged, 2); ?></td>
                        <td class="text-right align-middle text-monospace font-weight-bold text-info"><?php echo number_format($parking_paid, 2); ?></td>
                        
                        <td class="text-right align-middle text-monospace text-secondary"><?php echo number_format($util_charged, 2); ?></td>
                        <td class="text-right align-middle text-monospace font-weight-bold text-warning text-dark"><?php echo number_format($util_paid, 2); ?></td>
                        
                        <td class="text-right align-middle text-monospace text-danger font-weight-bold"><?php echo number_format($penalty_charged, 2); ?></td>
                        
                        <td class="text-right align-middle text-monospace font-weight-bold <?php echo ($tenant_balance > 0) ? 'text-danger' : 'text-success'; ?>">
                            <?php echo number_format($tenant_balance, 2); ?>
                        </td>
                    </tr>
                    <?php endwhile; ?>
                </tbody>
                <tfoot class="bg-light font-weight-bold" style="border-top: 2px solid #334155;">
                    <tr>
                        <td colspan="2" class="text-center text-uppercase text-dark font-weight-bold">Summary Totals:</td>
                        <td class="text-right text-monospace text-secondary"><?php echo number_format($g_rent_charge, 2); ?></td>
                        <td class="text-right text-monospace text-success font-weight-bold"><?php echo number_format($g_rent_paid_early, 2); ?></td>
                        <td class="text-right text-monospace text-primary font-weight-bold"><?php echo number_format($g_rent_paid_late, 2); ?></td>
                        
                        <td class="text-right text-monospace text-secondary"><?php echo number_format($g_park_charge, 2); ?></td>
                        <td class="text-right text-monospace text-info font-weight-bold"><?php echo number_format($g_park_paid, 2); ?></td>
                        
                        <td class="text-right text-monospace text-secondary"><?php echo number_format($g_util_charge, 2); ?></td>
                        <td class="text-right text-monospace text-warning text-dark font-weight-bold"><?php echo number_format($g_util_paid, 2); ?></td>
                        
                        <td class="text-right text-monospace text-danger font-weight-bold"><?php echo number_format($g_penalty_charged, 2); ?></td>
                        
                        <td class="text-right text-monospace <?php echo ($g_total_balance > 0) ? 'text-danger' : 'text-success'; ?>" style="font-size: 14px;">
                            KES <?php echo number_format($g_total_balance, 2); ?>
                        </td>
                    </tr>
                </tfoot>
            </table>
        </div>
        <?php else: ?>
        <div class="alert alert-info text-center shadow-sm">
            <i class="fa fa-info-circle mr-2"></i> No active tenant records found.
        </div>
        <?php endif; ?>
    </div>
    <?php
    return ob_get_clean();
}
   function get_rent_analysis(){
        extract($_POST);
        $first_day_current_month = $month . "-01";
        
        $payments_query = $this->db->query("
            SELECT 
                tenant_id,
                SUM(CASE WHEN DAY(date_created) <= 15 THEN amount ELSE 0 END) as paid_by_15th,
                SUM(CASE WHEN DAY(date_created) > 15 THEN amount ELSE 0 END) as paid_after_15th
            FROM payments 
            WHERE DATE_FORMAT(date_created, '%Y-%m') = '{$month}' 
              AND (type = 'rent' OR type IS NULL OR type = '')
            GROUP BY tenant_id
        ");
        
        $paid_data = array();
        while($p_row = $payments_query->fetch_assoc()){
            $paid_data[$p_row['tenant_id']] = $p_row;
        }

        $tenants_query = $this->db->query("
            SELECT t.*, concat(t.lastname, ', ', t.firstname) as name, h.house_no, h.price as rent_receivable, h.sqm 
            FROM tenants t 
            INNER JOIN houses h ON h.id = t.house_id 
            WHERE t.status = 1 
            ORDER BY concat(t.lastname, ', ', t.firstname) ASC
        ");

        $html = '';
        if($tenants_query->num_rows > 0):
            $html .= '<div class="table-responsive">';
            $html .= '<table class="table table-bordered table-striped table-hover m-0" style="font-size: 13px;">';
            $html .= '<thead class="bg-dark text-white text-center">';
            $html .= '<tr>';
            $html .= '<th>Tenant Name</th>';
            $html .= '<th>No.</th>';
            $html .= '<th>Sqm</th>';
            $html .= '<th>Rent Arrears B/F</th>';
            $html .= '<th>Rent Receivable</th>';
            $html .= '<th>WHT/VAT -12%</th>';
            $html .= '<th>Paid By 15th</th>';
            $html .= '<th>Arrears As Of 18th</th>';
            $html .= '<th>5% Penalty</th>';
            $html .= '<th>Total Outstanding</th>';
            $html .= '<th>Paid After 15th</th>';
            $html .= '<th>Arrears Closing</th>';
            $html .= '</tr>';
            $html .= '</thead>';
            $html .= '<tbody>';

            $tot_bf = $tot_receivable = $tot_wht = $tot_paid_15 = 0;
            $tot_arr_18 = $tot_penalty = $tot_out = $tot_paid_after = $tot_closing = 0;

            while($row = $tenants_query->fetch_assoc()):
                $t_id = $row['id'];
                $sqm_display = (!empty($row['sqm'])) ? $row['sqm'] : '-';
                
                // FIX: Calculate historical months using resilient day-independent math
                $start_year = date('Y', strtotime($row['date_in']));
                $start_month = date('m', strtotime($row['date_in']));
                $target_year = date('Y', strtotime($first_day_current_month));
                $target_month_num = date('m', strtotime($first_day_current_month));
                
                $historical_months = (($target_year - $start_year) * 12) + ($target_month_num - $start_month);
                if($historical_months < 0) $historical_months = 0;
                
                // Historical Charges Base Calculation
                $hist_rent_charged = floatval($row['rent_receivable']) * $historical_months;
                
                // Historical Payments
                $hist_paid_q = $this->db->query("SELECT SUM(amount) as total FROM payments WHERE tenant_id = {$t_id} AND date_created < '{$first_day_current_month}'");
                $hist_paid = ($hist_paid_q && $hist_paid_q->num_rows > 0) ? floatval($hist_paid_q->fetch_assoc()['total']) : 0;
                
                $arrears_bf = $hist_rent_charged - $hist_paid;
                if($arrears_bf < 0) $arrears_bf = 0; 
                
                // Handle current month values cleanly
                $rent_receivable = floatval($row['rent_receivable']);
                $wht_vat = 0.00;
                
                if(isset($row['apply_wht']) && $row['apply_wht'] == 1 || isset($row['wht_applicable'])) {
                    $wht_vat = $rent_receivable * 0.12;
                    $effective_receivable = $rent_receivable - $wht_vat;
                } else {
                    $effective_receivable = $rent_receivable;
                }
                
                $paid_by_15 = isset($paid_data[$t_id]) ? floatval($paid_data[$t_id]['paid_by_15th']) : 0.00;
                $paid_after_15 = isset($paid_data[$t_id]) ? floatval($paid_data[$t_id]['paid_after_15th']) : 0.00;
                
                $arrears_as_of_18 = ($arrears_bf + $effective_receivable) - $paid_by_15;
                if($arrears_as_of_18 < 0) $arrears_as_of_18 = 0;
                
                $penalty = ($arrears_as_of_18 > 0) ? ($arrears_as_of_18 * 0.05) : 0.00;
                $total_outstanding = $arrears_as_of_18 + $penalty;
                
                $arrears_closing = $total_outstanding - $paid_after_15;
                if($arrears_closing < 0) $arrears_closing = 0;

                $tot_bf += $arrears_bf;
                $tot_receivable += $rent_receivable;
                $tot_wht += $wht_vat;
                $tot_paid_15 += $paid_by_15;
                $tot_arr_18 += $arrears_as_of_18;
                $tot_penalty += $penalty;
                $tot_out += $total_outstanding;
                $tot_paid_after += $paid_after_15;
                $tot_closing += $arrears_closing;

                $html .= '<tr>';
                $html .= '<td><strong>' . ucwords($row['name']) . '</strong></td>';
                $html .= '<td class="text-center">' . $row['house_no'] . '</td>';
                $html .= '<td class="text-center">' . $sqm_display . '</td>';
                $html .= '<td class="text-right text-monospace">' . number_format($arrears_bf, 2) . '</td>';
                $html .= '<td class="text-right text-monospace">' . number_format($rent_receivable, 2) . '</td>';
                $html .= '<td class="text-right text-monospace text-secondary">' . number_format($wht_vat, 2) . '</td>';
                $html .= '<td class="text-right font-weight-bold text-success text-monospace">' . number_format($paid_by_15, 2) . '</td>';
                $html .= '<td class="text-right text-danger text-monospace">' . number_format($arrears_as_of_18, 2) . '</td>';
                $html .= '<td class="text-right text-warning text-monospace">' . number_format($penalty, 2) . '</td>';
                $html .= '<td class="text-right font-weight-bold text-monospace">' . number_format($total_outstanding, 2) . '</td>';
                $html .= '<td class="text-right text-info text-monospace">' . number_format($paid_after_15, 2) . '</td>';
                $html .= '<td class="text-right font-weight-bold text-dark text-monospace">' . number_format($arrears_closing, 2) . '</td>';
                $html .= '</tr>';
            endwhile;

            $html .= '</tbody>';
            $html .= '<tfoot class="bg-light font-weight-bold" style="border-top: 2px solid #1e293b;">';
            $html .= '<tr>';
            $html .= '<td colspan="3" class="text-center text-uppercase">Totals:</td>';
            $html .= '<td class="text-right text-monospace">' . number_format($tot_bf, 2) . '</td>';
            $html .= '<td class="text-right text-monospace">' . number_format($tot_receivable, 2) . '</td>';
            $html .= '<td class="text-right text-monospace text-secondary">' . number_format($tot_wht, 2) . '</td>';
            $html .= '<td class="text-right text-monospace text-success">' . number_format($tot_paid_15, 2) . '</td>';
            $html .= '<td class="text-right text-monospace text-danger">' . number_format($tot_arr_18, 2) . '</td>';
            $html .= '<td class="text-right text-monospace text-warning">' . number_format($tot_penalty, 2) . '</td>';
            $html .= '<td class="text-right text-monospace text-dark">' . number_format($tot_out, 2) . '</td>';
            $html .= '<td class="text-right text-monospace text-info">' . number_format($tot_paid_after, 2) . '</td>';
            $html .= '<td class="text-right text-monospace text-dark" style="font-size:14px;">' . number_format($tot_closing, 2) . '</td>';
            $html .= '</tr>';
            $html .= '</tfoot>';
            $html .= '</table>';
            $html .= '</div>';
        else:
            $html .= '<div class="alert alert-info text-center m-0">No active tenant balances captured.</div>';
        endif;

        return $html;
}
}
?>