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

// --- 1. EXCEL XML CLASS ---
class ExcelXML {
    private $xml = "";
    public function __construct() {
        $this->xml  = "<?xml version=\"1.0\"?>\n<?mso-application progid=\"Excel.Sheet\"?>\n";
        $this->xml .= "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\n";
        $this->xml .= "<Styles><Style ss:ID=\"sHeader\"><Font ss:Bold=\"1\"/></Style></Styles>\n";
    }
    public function addWorksheet($name, $data) {
        $name = preg_replace('/[\\\\\/?*\:\[\]]/', '', substr($name, 0, 31));
        $this->xml .= " <Worksheet ss:Name=\"$name\">\n  <Table>\n";
        foreach ($data as $row) {
            $this->xml .= "   <Row>\n";
            $isHeader = ($row === reset($data));
            foreach ($row as $cell) {
                $type = is_numeric($cell) ? "Number" : "String";
                $style = $isHeader ? "ss:StyleID=\"sHeader\"" : "";
                $this->xml .= "    <Cell $style><Data ss:Type=\"$type\">" . htmlspecialchars($cell ?? '') . "</Data></Cell>\n";
            }
            $this->xml .= "   </Row>\n";
        }
        $this->xml .= "  </Table>\n </Worksheet>\n";
    }
    public function generate($filename) {
        $this->xml .= "</Workbook>";
        header("Content-Type: application/vnd.ms-excel");
        header("Content-Disposition: attachment; filename=\"$filename\"");
        echo $this->xml;
        exit;
    }
}

// --- 2. INPUT HANDLING & DATE LOGIC ---
$rep_type = $_POST['rep_type'] ?? 'Monthly';
$sel_month = (int)($_POST['rep_month'] ?? date('m'));
$sel_year  = (int)($_POST['rep_year'] ?? date('Y'));

$start_ts = 0; $end_ts = 0; $period_label = "";
$lbl_active = "Total Active Loans (Status 2)";
$lbl_paid = "Total Paid Off Files (This Month)";
$lbl_port = "Total Loan Portfolio (Status 2)";
$lbl_bad = "Total Bad Loans (>1 Yr)";
$lbl_freeze = "Total Freezed Amount";

if ($rep_type == 'Weekly') {
    $picked_date = $_POST['rep_week_date'] ?? date('Y-m-d');
    $ts = strtotime($picked_date);
    $day_of_week = date('N', $ts);
    $start_ts = ($day_of_week == 1) ? mktime(0,0,0, date('m',$ts), date('d',$ts), date('Y',$ts)) : strtotime('last monday', $ts);
    $end_ts = strtotime('+6 days 23 hours 59 minutes 59 seconds', $start_ts);
    $period_label = "Weekly Report: " . date("d M", $start_ts) . " - " . date("d M Y", $end_ts);
    $lbl_active = "New Loans Issued (This Week)";
    $lbl_paid = "Paid Off Files (This Week)";
    $lbl_port = "Value of New Loans (This Week)";
    $lbl_bad = "Bad Loans (N/A)";
    $lbl_freeze = "Freezed (N/A)";
} elseif ($rep_type == 'Overall') {
    $start_ts = mktime(0, 0, 0, 2, 1, 2026);
    $end_ts = time(); 
    $period_label = "Overall Performance (Since 1st Feb 2026)";
    $lbl_active = "Total Active Loans (Current)";
    $lbl_paid = "Total Paid Off Files (Since Feb 1, 2026)";
    $lbl_port = "Total Current Portfolio";
} else {
    $start_ts = mktime(0, 0, 0, $sel_month - 1, 26, $sel_year);
    $end_ts   = mktime(23, 59, 59, $sel_month, 25, $sel_year);
    $period_label = "Monthly Report: " . date("d M Y", $start_ts) . " - " . date("d M Y", $end_ts);
}

if (isset($_POST['export_rep'])) {
    if(isset($_SESSION['rep_export_sheets'])) {
        $xls = new ExcelXML();
        $sheets = $_SESSION['rep_export_sheets'];
        foreach($sheets as $name => $rows) {
             $xls->addWorksheet($name, $rows);
        }
        $xls->generate("Report_" . $rep_type . "_" . date('Y-m-d') . ".xls");
    }
    exit();
}

// --- 4. SMART ID DETECTION ---
$map = ['FOLIO'=>3, 'KAROMU'=>9, 'FINE'=>5, 'INTEREST'=>4, 'MOMO'=>7, 'PRINCIPAL'=>12, 'AIRTEL'=>13, 'TRANSFER'=>14];

$q_ids = mysqli_query($conn, "SELECT inctype_id, inctype_type FROM inctype");
while($row = mysqli_fetch_assoc($q_ids)) {
    $name = strtoupper($row['inctype_type']); 
    $id = $row['inctype_id'];
    if (strpos($name, 'FOLIO')!==false || strpos($name, 'LOAN FEE')!==false) $map['FOLIO']=$id;
    elseif (strpos($name, 'KAROMU')!==false) $map['KAROMU']=$id;
    elseif (strpos($name, 'FINE')!==false) $map['FINE']=$id;
    elseif (strpos($name, 'INTEREST')!==false) $map['INTEREST']=$id;
    elseif (strpos($name, 'MOMO')!==false) $map['MOMO']=$id;
    elseif (strpos($name, 'PRINCIPAL')!==false || strpos($name, 'PRINCIPLE')!==false) $map['PRINCIPAL']=$id;
    elseif (strpos($name, 'AIRTEL')!==false) $map['AIRTEL']=$id;
    elseif (strpos($name, 'TRANSFER')!==false) $map['TRANSFER']=$id;
}

$id_f=$map['FOLIO']; $id_k=$map['KAROMU']; $id_fn=$map['FINE']; $id_i=$map['INTEREST'];
$id_m=$map['MOMO']; $id_p=$map['PRINCIPAL']; $id_trans=$map['TRANSFER'];
$id_air = $map['AIRTEL'] ?: 13; 

// --- 5. PREPARE DATA ---
$sheets = [];

// A. SUMMARY CALCULATIONS
$inc_totals = ['KAROMU'=>0, 'MOMO'=>0, 'PRINCIPAL'=>0, 'INTEREST'=>0, 'FINE'=>0, 'FOLIO'=>0, 'AIRTEL'=>0, 'TRANSFER'=>0, 'TOTAL'=>0];
$q_inc = mysqli_query($conn, "SELECT inc_amount, inctype_id FROM incomes WHERE inc_date BETWEEN $start_ts AND $end_ts");
while($r = mysqli_fetch_assoc($q_inc)) {
    $id = $r['inctype_id']; $amt = $r['inc_amount'];
    if($id == $id_p) { $inc_totals['PRINCIPAL'] += $amt; $inc_totals['TOTAL'] += $amt; }
    elseif($id == $id_i) { $inc_totals['INTEREST'] += $amt; $inc_totals['TOTAL'] += $amt; }
    elseif($id == $id_m) { $inc_totals['MOMO'] += $amt; $inc_totals['TOTAL'] += $amt; }
    elseif($id == $id_fn) { $inc_totals['FINE'] += $amt; $inc_totals['TOTAL'] += $amt; }
    elseif($id == $id_f) { $inc_totals['FOLIO'] += $amt; $inc_totals['TOTAL'] += $amt; }
    elseif($id == $id_k) { $inc_totals['KAROMU'] += $amt; $inc_totals['TOTAL'] += $amt; }
    elseif($id == $id_air) { $inc_totals['AIRTEL'] += $amt; $inc_totals['TOTAL'] += $amt; }
    elseif($id == $id_trans) { $inc_totals['TRANSFER'] += $amt; $inc_totals['TOTAL'] += $amt; }
}

$exp_totals = ['BANKED'=>0, 'LOAN'=>0, 'MC_DEP'=>0, 'MC_LOAN'=>0, 'MOMO'=>0, 'AIRTEL'=>0, 'OFFICE'=>0, 'FOLIO'=>0, 'TOTAL'=>0];
$q_exp = mysqli_query($conn, "SELECT e.exp_amount, e.exptype_id, t.exptype_type FROM expenses e JOIN exptype t ON e.exptype_id=t.exptype_id WHERE e.exp_date BETWEEN $start_ts AND $end_ts");
while($r = mysqli_fetch_assoc($q_exp)) {
    $n = strtoupper($r['exptype_type']); 
    $eid = $r['exptype_id'];
    $amt = $r['exp_amount'];
    
    if($eid == 12 || strpos($n, 'AIRTEL')!==false) $exp_totals['AIRTEL'] += $amt; 
    elseif(strpos($n, 'BANKED')!==false) $exp_totals['BANKED'] += $amt;
    elseif(strpos($n, 'LOAN')!==false && strpos($n, 'M/C')===false) $exp_totals['LOAN'] += $amt;
    elseif(strpos($n, 'M/C DEP')!==false) $exp_totals['MC_DEP'] += $amt;
    elseif(strpos($n, 'M/C LOAN')!==false) $exp_totals['MC_LOAN'] += $amt;
    elseif(strpos($n, 'MOMO')!==false) $exp_totals['MOMO'] += $amt;
    elseif(strpos($n, 'OFFICE')!==false) $exp_totals['OFFICE'] += $amt;
    elseif(strpos($n, 'FOLIO')!==false) $exp_totals['FOLIO'] += $amt;
    $exp_totals['TOTAL'] += $amt;
}

// Helper function to get combined loans
function getCombinedLoans($conn, $status_id, $date_col='loan_dateout') {
    $loans = [];
    $q1 = mysqli_query($conn, "SELECT loan_id, loan_no, loan_principal, $date_col, loan_interest, 'IND' as type, cust_name as name FROM loans LEFT JOIN customer ON loans.cust_id = customer.cust_id WHERE loanstatus_id = $status_id");
    while($r = mysqli_fetch_assoc($q1)) { if(!$r['name']) $r['name'] = "Individual (ID {$r['loan_id']})"; $loans[] = $r; }
    $q2 = mysqli_query($conn, "SELECT loan_id, loan_no, loan_principal, $date_col, loan_interest, 'GRP' as type, group_name as name FROM group_loans LEFT JOIN `group` ON group_loans.group_id = `group`.group_id WHERE loanstatus_id = $status_id");
    while($r = mysqli_fetch_assoc($q2)) { if(!$r['name']) $r['name'] = "Group (ID {$r['loan_id']})"; $loans[] = $r; }
    return $loans;
}

// B. STATS LOGIC
$active_count = 0; $portfolio_val = 0; $paid_cnt = 0; $freezed_val = 0; $bad_val = 0;

if ($rep_type == 'Weekly') {
    $q_new_ind = mysqli_query($conn, "SELECT loan_principal FROM loans WHERE loanstatus_id = 2 AND loan_dateout BETWEEN $start_ts AND $end_ts");
    while($r = mysqli_fetch_assoc($q_new_ind)) { $active_count++; $portfolio_val += $r['loan_principal']; }
    $q_new_grp = mysqli_query($conn, "SELECT loan_principal FROM group_loans WHERE loanstatus_id = 2 AND loan_dateout BETWEEN $start_ts AND $end_ts");
    while($r = mysqli_fetch_assoc($q_new_grp)) { $active_count++; $portfolio_val += $r['loan_principal']; }

    $all_paid = getCombinedLoans($conn, 5);
    foreach($all_paid as $l) {
        $tbl = ($l['type'] == 'IND') ? 'ltrans' : 'group_ltrans';
        $q_trans = mysqli_query($conn, "SELECT MAX(ltrans_date) as last_date FROM $tbl WHERE loan_id={$l['loan_id']}");
        $last_date = mysqli_fetch_assoc($q_trans)['last_date'];
        if($last_date >= $start_ts && $last_date <= $end_ts) $paid_cnt++;
    }
} else {
    $all_active = getCombinedLoans($conn, 2);
    $active_count = count($all_active);
    foreach($all_active as $l) {
        $tbl = ($l['type'] == 'IND') ? 'ltrans' : 'group_ltrans';
        $q_paid = mysqli_query($conn, "SELECT SUM(ltrans_principal) as paid FROM $tbl WHERE loan_id={$l['loan_id']}");
        $paid = mysqli_fetch_assoc($q_paid)['paid'] ?: 0;
        $bal = $l['loan_principal'] - $paid;
        if($bal > 0) $portfolio_val += $bal;
    }
    $all_paid = getCombinedLoans($conn, 5);
    foreach($all_paid as $l) {
        $tbl = ($l['type'] == 'IND') ? 'ltrans' : 'group_ltrans';
        $q_trans = mysqli_query($conn, "SELECT MAX(ltrans_date) as last_date FROM $tbl WHERE loan_id={$l['loan_id']}");
        $last_date = mysqli_fetch_assoc($q_trans)['last_date'];
        if($last_date >= $start_ts && $last_date <= $end_ts) $paid_cnt++;
    }
    foreach($all_active as $l) {
        if($l['loan_interest'] == 0) {
            $tbl = ($l['type'] == 'IND') ? 'ltrans' : 'group_ltrans';
            $q_paid = mysqli_query($conn, "SELECT SUM(ltrans_principal) as p FROM $tbl WHERE loan_id={$l['loan_id']}");
            $paid = mysqli_fetch_assoc($q_paid)['p'];
            $bal = $l['loan_principal'] - $paid;
            if($bal > 0) $freezed_val += $bal;
        }
    }
    $one_year_ago = time() - (365*86400);
    foreach($all_active as $l) {
        $tbl = ($l['type'] == 'IND') ? 'ltrans' : 'group_ltrans';
        $q_trans = mysqli_query($conn, "SELECT MAX(ltrans_date) as last_pay, SUM(ltrans_principal) as paid FROM $tbl WHERE loan_id={$l['loan_id']} AND ltrans_date IS NOT NULL");
        $trans = mysqli_fetch_assoc($q_trans);
        $last_activity = $trans['last_pay'] ?: $l['loan_dateout']; 
        $bal = $l['loan_principal'] - ($trans['paid'] ?: 0);
        if($last_activity < $one_year_ago && $bal > 0) $bad_val += $bal;
    }
}

// Add data to Excel Sheet Array
$sheets['SUMMARY'] = [
    ['SUMMARY REPORT', $period_label], ['', ''],
    ['CASHIN CATEGORY', 'AMOUNT'],
    ['Karomu A/C', $inc_totals['KAROMU']], ['MOMO A/C', $inc_totals['MOMO']], 
    ['Airtel A/C', $inc_totals['AIRTEL']], ['Transfer Fee', $inc_totals['TRANSFER']],
    ['PRINCIPAL', $inc_totals['PRINCIPAL']], ['Loan Interest', $inc_totals['INTEREST']],
    ['Loan Fine', $inc_totals['FINE']], ['Folio', $inc_totals['FOLIO']], 
    ['TOTAL CASHINS', $inc_totals['TOTAL']],
    ['', ''], ['CASHOUT CATEGORY', 'AMOUNT'],
    ['BANKED', $exp_totals['BANKED']], ['LOAN', $exp_totals['LOAN']], ['M/C DEP EXP', $exp_totals['MC_DEP']], 
    ['M/C LOAN', $exp_totals['MC_LOAN']], ['MOMO PAY', $exp_totals['MOMO']], ['AIRTEL PAY', $exp_totals['AIRTEL']],
    ['OFFICE EXP', $exp_totals['OFFICE']], ['FOLIO EXP', $exp_totals['FOLIO']], ['TOTAL CASHOUTS', $exp_totals['TOTAL']],
    ['', ''], ['METRIC', 'VALUE'],
    [$lbl_active, $active_count], [$lbl_paid, $paid_cnt], [$lbl_port, $portfolio_val],
    [$lbl_freeze, $freezed_val], [$lbl_bad, $bad_val]
];

// --- GENERATE DETAILED LISTS (Always export these sheets now) ---

// 1. LOAN PORTFOLIO
$rows = [['FILE NO', 'CLIENT NAME', 'LOAN BALANCE', 'LOAN TAKEN', 'UNPAID INT', 'ISSUED']];
$all_active = getCombinedLoans($conn, 2);
foreach($all_active as $l){
    $tbl = ($l['type'] == 'IND') ? 'ltrans' : 'group_ltrans';
    $q_pay = mysqli_query($conn, "SELECT SUM(ltrans_principal) as p, SUM(ltrans_interest) as i, SUM(ltrans_interestdue) as id FROM $tbl WHERE loan_id={$l['loan_id']}");
    $pay = mysqli_fetch_assoc($q_pay);
    $bal = $l['loan_principal'] - $pay['p'];
    $int_bal = $pay['id'] - $pay['i'];
    if($bal > 0) $rows[] = [$l['loan_no'], $l['name'], $bal, $l['loan_principal'], $int_bal, date('d.m.Y',$l['loan_dateout'])];
}
$sheets['LOAN PORTFOLIO'] = $rows;

// 2. BAD LOANS
$rows = [['LOAN NO', 'CLIENT NAME', 'BALANCE', 'LAST PAY']];
foreach($all_active as $l){
    $tbl = ($l['type'] == 'IND') ? 'ltrans' : 'group_ltrans';
    $q_inf = mysqli_query($conn, "SELECT MAX(ltrans_date) as ld, SUM(ltrans_principal) as p FROM $tbl WHERE loan_id={$l['loan_id']} AND ltrans_date IS NOT NULL");
    $inf = mysqli_fetch_assoc($q_inf);
    $last = $inf['ld'] ?: $l['loan_dateout'];
    $bal = $l['loan_principal'] - $inf['p'];
    if($last < $one_year_ago && $bal > 0) $rows[] = [$l['loan_no'], $l['name'], $bal, date('d.m.Y',$last)];
}
$sheets['BAD LOANS'] = $rows;

// 3. FREEZED LOANS
$rows = [['LOAN NO', 'CLIENT NAME', 'BALANCE']];
foreach($all_active as $l){
    if($l['loan_interest'] == 0) {
        $tbl = ($l['type'] == 'IND') ? 'ltrans' : 'group_ltrans';
        $q_paid = mysqli_query($conn, "SELECT SUM(ltrans_principal) as p FROM $tbl WHERE loan_id={$l['loan_id']}");
        $paid = mysqli_fetch_assoc($q_paid)['p'];
        $bal = $l['loan_principal'] - $paid;
        if($bal > 0) $rows[] = [$l['loan_no'], $l['name'], $bal];
    }
}
$sheets['FREEZED'] = $rows;

// 4. PAID OFFS
$rows = [['LOAN NO', 'CLIENT NAME', 'PRINCIPAL', 'CLEARED DATE']];
$all_paid_files = getCombinedLoans($conn, 5);
foreach($all_paid_files as $l){
    $tbl = ($l['type'] == 'IND') ? 'ltrans' : 'group_ltrans';
    $q_trans = mysqli_query($conn, "SELECT MAX(ltrans_date) as last_date FROM $tbl WHERE loan_id={$l['loan_id']}");
    $last_date = mysqli_fetch_assoc($q_trans)['last_date'];
    if($last_date >= $start_ts && $last_date <= $end_ts) $rows[] = [$l['loan_no'], $l['name'], $l['loan_principal'], date('d.m.Y',$last_date)];
}
$sheets['PAID OFFS'] = $rows;

// 5. CASHINS 
$rows = [['DATE', 'RECEIPT', 'NAME', 'FOLIO', 'KAROMU', 'FINE', 'INTEREST', 'MOMO', 'AIRTEL', 'TRANSFER', 'PRINCIPAL', 'TOTAL', 'DESCRIPTION']];
$sql = "SELECT 
            IF(i.ltrans_id > 0, CONCAT('L-', i.ltrans_id), CONCAT('I-', i.inc_id)) as group_key,
            MAX(i.inc_date) as inc_date, MAX(i.inc_receipt) as inc_receipt, MAX(i.inc_text) as inc_text,
            MAX(c.cust_name) as cust_name, MAX(g.group_name) as group_name,
            SUM(CASE WHEN i.inctype_id = $id_f THEN i.inc_amount ELSE 0 END) as val_folio,
            SUM(CASE WHEN i.inctype_id = $id_k THEN i.inc_amount ELSE 0 END) as val_karomu,
            SUM(CASE WHEN i.inctype_id = $id_fn THEN i.inc_amount ELSE 0 END) as val_fine,
            SUM(CASE WHEN i.inctype_id = $id_i THEN i.inc_amount ELSE 0 END) as val_interest,
            SUM(CASE WHEN i.inctype_id = $id_m THEN i.inc_amount ELSE 0 END) as val_momo,
            SUM(CASE WHEN i.inctype_id = $id_air THEN i.inc_amount ELSE 0 END) as val_airtel,
            SUM(CASE WHEN i.inctype_id = $id_trans THEN i.inc_amount ELSE 0 END) as val_transfer,
            SUM(CASE WHEN i.inctype_id = $id_p THEN i.inc_amount ELSE 0 END) as val_principal,
            SUM(i.inc_amount) as total_amount
        FROM incomes i LEFT JOIN customer c ON i.cust_id = c.cust_id LEFT JOIN `group` g ON i.group_id = g.group_id
        WHERE i.inc_date BETWEEN $start_ts AND $end_ts
        GROUP BY group_key ORDER BY inc_date ASC";
$q = mysqli_query($conn, $sql);
while($r = mysqli_fetch_assoc($q)){
    $name = $r['cust_name'] ?: ($r['group_name'] ?: "N/A");
    $rows[] = [
        date('d.m.Y',$r['inc_date']), $r['inc_receipt'], $name, 
        ($r['val_folio']>0?$r['val_folio']:""), ($r['val_karomu']>0?$r['val_karomu']:""), 
        ($r['val_fine']>0?$r['val_fine']:""), ($r['val_interest']>0?$r['val_interest']:""), 
        ($r['val_momo']>0?$r['val_momo']:""), ($r['val_airtel']>0?$r['val_airtel']:""), 
        ($r['val_transfer']>0?$r['val_transfer']:""), ($r['val_principal']>0?$r['val_principal']:""), 
        $r['total_amount'], $r['inc_text']
    ];
}
$sheets['CASHINS'] = $rows;

// 6. CASHOUTS
$rows = [['DATE', 'VOUCHER', 'BANKED', 'LOAN', 'M/C DEP', 'M/C LOAN', 'MOMO PAY', 'AIRTEL PAY', 'OFFICE EXP', 'FOLIO EXP', 'TOTAL', 'DESCRIPTION']];
$q = mysqli_query($conn, "SELECT e.*, t.exptype_type, e.exptype_id FROM expenses e JOIN exptype t ON e.exptype_id=t.exptype_id WHERE e.exp_date BETWEEN $start_ts AND $end_ts ORDER BY e.exp_date ASC");
while($r = mysqli_fetch_assoc($q)){
    $tn = strtoupper($r['exptype_type']); $amt = $r['exp_amount']; $eid = $r['exptype_id'];
    
    $c_banked = (strpos($tn,'BANKED')!==false)?$amt:"";
    $c_loan = (strpos($tn,'LOAN')!==false && strpos($tn,'M/C')===false)?$amt:"";
    $c_mcdep = (strpos($tn,'M/C DEP')!==false)?$amt:"";
    $c_mcloan = (strpos($tn,'M/C LOAN')!==false)?$amt:"";
    $c_momo = (strpos($tn,'MOMO')!==false)?$amt:"";
    
    $c_airtel = ($eid == 12 || strpos($tn,'AIRTEL')!==false)?$amt:"";
    
    $c_office = (strpos($tn,'OFFICE')!==false)?$amt:"";
    $c_folio = (strpos($tn,'FOLIO')!==false)?$amt:"";
    
    $rows[] = [date('d.m.Y',$r['exp_date']), $r['exp_voucher'], $c_banked, $c_loan, $c_mcdep, $c_mcloan, $c_momo, $c_airtel, $c_office, $c_folio, $amt, $r['exp_text']];
}
$sheets['CASHOUTS'] = $rows;

// 7. BANKED
$rows = [['DATE', 'VOUCHER', 'AMOUNT', 'DESCRIPTION']];
$q = mysqli_query($conn, "SELECT e.*, t.exptype_type FROM expenses e JOIN exptype t ON e.exptype_id=t.exptype_id WHERE e.exp_date BETWEEN $start_ts AND $end_ts AND t.exptype_type LIKE '%BANKED%' ORDER BY e.exp_date ASC");
while($r = mysqli_fetch_assoc($q)){
    $rows[] = [date('d.m.Y',$r['exp_date']), $r['exp_voucher'], $r['exp_amount'], $r['exp_text']];
}
$sheets['BANKED'] = $rows;

// 8. MOMO PAY
$rows = [['DATE', 'VOUCHER', 'AMOUNT', 'DESCRIPTION']];
$q = mysqli_query($conn, "SELECT e.*, t.exptype_type FROM expenses e JOIN exptype t ON e.exptype_id=t.exptype_id WHERE e.exp_date BETWEEN $start_ts AND $end_ts AND t.exptype_type LIKE '%MOMO%' ORDER BY e.exp_date ASC");
while($r = mysqli_fetch_assoc($q)){
    $rows[] = [date('d.m.Y',$r['exp_date']), $r['exp_voucher'], $r['exp_amount'], $r['exp_text']];
}
$sheets['MOMO PAY'] = $rows;

// 9. AIRTEL PAY
$rows = [['DATE', 'VOUCHER', 'AMOUNT', 'DESCRIPTION']];
$q = mysqli_query($conn, "SELECT e.*, t.exptype_type FROM expenses e JOIN exptype t ON e.exptype_id=t.exptype_id WHERE e.exp_date BETWEEN $start_ts AND $end_ts AND (t.exptype_type LIKE '%AIRTEL%' OR e.exptype_id = 12) ORDER BY e.exp_date ASC");
while($r = mysqli_fetch_assoc($q)){
    $rows[] = [date('d.m.Y',$r['exp_date']), $r['exp_voucher'], $r['exp_amount'], $r['exp_text']];
}
$sheets['AIRTEL PAY'] = $rows;

$_SESSION['rep_export_sheets'] = $sheets;
?>
<!DOCTYPE html>
<html>
<head>
    <title>Reports</title>
    <link rel="stylesheet" href="css/mangoo.css" />
    <style>
        body { background: #E3E4CD; font-family: 'Segoe UI', Tahoma, sans-serif; }
        .sub-nav { background: #333; height: 50px; display: flex; justify-content: space-between; align-items: center; padding: 0 10px; color: #ddd; border-bottom: 4px solid #555; }
        .controls { display: flex; gap: 10px; align-items: center; }
        input, select, button { padding: 5px; border-radius: 3px; border: 1px solid #777; background: #222; color: white; }
        .btn-go { background: #E95D0F; border: none; font-weight: bold; cursor: pointer; }
        .btn-export { background: #28a745; border: none; font-weight: bold; cursor: pointer; padding: 8px 20px; color: white; border-radius: 4px; }
        .btn-print { background: #007bff; border: none; font-weight: bold; cursor: pointer; padding: 8px 20px; color: white; border-radius: 4px; }
        .report-container { width: 95%; margin: 20px auto; background: white; padding: 20px; box-shadow: 0 2px 5px rgba(0,0,0,0.2); }
        h2 { border-bottom: 2px solid #E95D0F; padding-bottom: 10px; margin-top: 0; color: #333; }
        .summary-grid { display: grid; grid-template-columns: 1fr 1fr; gap: 20px; margin-top: 20px; }
        .sum-box { border: 1px solid #ccc; padding: 15px; background: #f9f9f9; }
        .sum-row { display: flex; justify-content: space-between; padding: 5px 0; border-bottom: 1px dotted #ddd; }
        .sum-row.total { font-weight: bold; border-top: 2px solid #aaa; border-bottom: none; margin-top: 10px; font-size: 1.1em; }
        .stat-box { margin-top: 20px; border-top: 3px solid #E95D0F; padding-top: 10px; display: flex; justify-content: space-between; }
        .stat-item { text-align: center; }
        .stat-val { font-size: 1.5em; font-weight: bold; color: #E95D0F; }

        @media print {
            body { background: white; -webkit-print-color-adjust: exact; }
            .sub-nav, .btn-export, .btn-print, #menu_main, .controls { display: none !important; }
            .report-container { width: 100%; margin: 0; box-shadow: none; border: none; }
            .sum-box { background: white; border: 1px solid #000; }
            h2 { color: black; border-color: black; }
            .stat-val { color: black !important; }
            .stat-box { border-color: black; }
        }
    </style>
    <script>
        function toggleInputs() {
            var type = document.getElementById('rep_type').value;
            document.getElementById('monthly_inputs').style.display = (type == 'Monthly') ? 'inline-block' : 'none';
            document.getElementById('weekly_inputs').style.display = (type == 'Weekly') ? 'inline-block' : 'none';
        }
    </script>
</head>
<body onload="toggleInputs()">
    <?php includeMenu(5); ?>
     
     <div class="report-container">
          <div>
                <h2 style="margin:0;"><?php echo $period_label; ?></h2>
            </div>
        <table align="left">
          <tr>
    <div class="sub-nav">
        <div style="font-weight:bold; font-size:18px;">Reports</div>
        <form method="post" class="controls">
            <span>Type:</span>
            <select name="rep_type" id="rep_type" onchange="toggleInputs()">
                <option value="Monthly" <?php if($rep_type=='Monthly') echo 'selected'; ?>>Monthly</option>
                <option value="Weekly" <?php if($rep_type=='Weekly') echo 'selected'; ?>>Weekly</option>
                <option value="Overall" <?php if($rep_type=='Overall') echo 'selected'; ?>>Overall Summary</option>
            </select>

            <span id="monthly_inputs">
                <select name="rep_month">
                    <?php foreach(["01"=>"Jan","02"=>"Feb","03"=>"Mar","04"=>"Apr","05"=>"May","06"=>"Jun","07"=>"Jul","08"=>"Aug","09"=>"Sep","10"=>"Oct","11"=>"Nov","12"=>"Dec"] as $k=>$v) echo "<option value='$k' ".($k==$sel_month?'selected':'').">$v</option>"; ?>
                </select>
                <input type="number" name="rep_year" value="<?php echo $sel_year; ?>" style="width:60px;">
            </span>

            <span id="weekly_inputs" style="display:none;">
                <label>Pick any day in the week:</label>
                <input type="date" name="rep_week_date" value="<?php echo date('Y-m-d'); ?>">
            </span>

            <button type="submit" name="select" class="btn-go">GO</button>
        </form>
    </div>
    </tr>
    </table>
    
        <div style="display:flex; justify-content:space-between; align-items:center;">
            <div style="display:flex; gap:10px;">
                <button onclick="window.print()" class="btn-print">Print Report</button>
                <form method="post" style="display:inline;">
                    <input type="hidden" name="export_rep" value="1">
                    <button type="submit" class="btn-export">Download Excel</button>
                </form>
            </div>
        </div>

        <div class="summary-grid">
            <div class="sum-box">
                <h3 style="margin-top:0; color:green;">Cashins</h3>
                <div class="sum-row"><span>Karomu A/C</span><span><?php echo number_format($inc_totals['KAROMU']); ?></span></div>
                <div class="sum-row"><span>MOMO A/C</span><span><?php echo number_format($inc_totals['MOMO']); ?></span></div>
                <div class="sum-row"><span>Airtel A/C</span><span><?php echo number_format($inc_totals['AIRTEL']); ?></span></div>
                <div class="sum-row"><span>Transfer Fee</span><span><?php echo number_format($inc_totals['TRANSFER']); ?></span></div>
                <div class="sum-row"><span>Principal</span><span><?php echo number_format($inc_totals['PRINCIPAL']); ?></span></div>
                <div class="sum-row"><span>Interest</span><span><?php echo number_format($inc_totals['INTEREST']); ?></span></div>
                <div class="sum-row"><span>Loan Fine</span><span><?php echo number_format($inc_totals['FINE']); ?></span></div>
                <div class="sum-row"><span>Folio</span><span><?php echo number_format($inc_totals['FOLIO']); ?></span></div>
                <div class="sum-row total"><span>TOTAL CASHINS</span><span><?php echo number_format($inc_totals['TOTAL']); ?></span></div>
            </div>
            
            <div class="sum-box">
                <h3 style="margin-top:0; color:red;">Cashouts</h3>
                <div class="sum-row"><span>BANKED</span><span><?php echo number_format($exp_totals['BANKED']); ?></span></div>
                <div class="sum-row"><span>LOAN</span><span><?php echo number_format($exp_totals['LOAN']); ?></span></div>
                <div class="sum-row"><span>M/C DEP EXP</span><span><?php echo number_format($exp_totals['MC_DEP']); ?></span></div>
                <div class="sum-row"><span>M/C LOAN</span><span><?php echo number_format($exp_totals['MC_LOAN']); ?></span></div>
                <div class="sum-row"><span>MOMO PAY</span><span><?php echo number_format($exp_totals['MOMO']); ?></span></div>
                <div class="sum-row"><span>AIRTEL PAY</span><span><?php echo number_format($exp_totals['AIRTEL']); ?></span></div>
                <div class="sum-row"><span>OFFICE EXP</span><span><?php echo number_format($exp_totals['OFFICE']); ?></span></div>
                <div class="sum-row"><span>FOLIO EXP</span><span><?php echo number_format($exp_totals['FOLIO']); ?></span></div>
                <div class="sum-row total"><span>TOTAL CASHOUTS</span><span><?php echo number_format($exp_totals['TOTAL']); ?></span></div>
            </div>
        </div>

        <div class="stat-box">
            <div class="stat-item">
                <div class="stat-val"><?php echo number_format($active_count); ?></div>
                <div><?php echo $lbl_active; ?></div>
            </div>
            <div class="stat-item">
                <div class="stat-val"><?php echo number_format($paid_cnt); ?></div>
                <div><?php echo $lbl_paid; ?></div>
            </div>
            <div class="stat-item">
                <div class="stat-val"><?php echo number_format($portfolio_val); ?></div>
                <div><?php echo $lbl_port; ?></div>
            </div>
            <div class="stat-item">
                <div class="stat-val" style="color:orange;"><?php echo number_format($freezed_val); ?></div>
                <div><?php echo $lbl_freeze; ?></div>
            </div>
            <div class="stat-item">
                <div class="stat-val" style="color:red;"><?php echo number_format($bad_val); ?></div>
                <div><?php echo $lbl_bad; ?></div>
            </div>
        </div>
    </div>
</body>
</html>