<?php
require "db.php";

function esc($v){
    return htmlspecialchars($v ?? '', ENT_QUOTES, 'UTF-8');
}

/* -----------------------------
   FILTERS
------------------------------*/
$from = $_GET['from'] ?? '';
$to   = $_GET['to'] ?? '';
$head = (int)($_GET['head'] ?? 0);
$mode = $_GET['mode'] ?? '';

$where = [];

if ($from) $where[] = "e.exp_date >= '".mysqli_real_escape_string($conn,$from)."'";
if ($to)   $where[] = "e.exp_date <= '".mysqli_real_escape_string($conn,$to)."'";
if ($head) $where[] = "e.head_id = $head";
if ($mode) $where[] = "e.pay_mode = '".mysqli_real_escape_string($conn,$mode)."'";

$cond = count($where) ? "WHERE ".implode(" AND ", $where) : "";

/* -----------------------------
   DATA
------------------------------*/
$sql = "
SELECT e.*, 
       h.name AS head,
       s.name AS sub
FROM hps_expenses e
JOIN hps_expense_heads h ON h.id=e.head_id
JOIN hps_expense_sub_heads s ON s.id=e.sub_head_id
$cond
ORDER BY e.exp_date DESC, e.id DESC
";
$res = mysqli_query($conn,$sql);

/* -----------------------------
   TOTALS
------------------------------*/
$totals = mysqli_fetch_assoc(mysqli_query($conn,"
SELECT
  SUM(amount) AS total,
  SUM(CASE WHEN pay_mode='Cash' THEN amount ELSE 0 END) AS cash,
  SUM(CASE WHEN pay_mode='Bank' THEN amount ELSE 0 END) AS bank,
  SUM(CASE WHEN pay_mode='Online' THEN amount ELSE 0 END) AS online
FROM hps_expenses e
$cond
"));

$heads = mysqli_query($conn,"SELECT * FROM hps_expense_heads ORDER BY name");
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Expense Report</title>

<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">

<style>
body{background:#f1f3f5}

/* Card */
.main-card{
    background:#fff;
    padding:22px;
    border-radius:20px;
    box-shadow:0 10px 25px rgba(0,0,0,.08);
    max-width:1200px;
    margin:auto;
}

/* Zebra + hover */
#expTable tbody tr:nth-child(odd){background:#f4f7fb}
#expTable tbody tr:hover{background:#eaf5ea}

/* Print */
@media print{
    .no-print{display:none !important}
    body{background:#fff}
    .main-card{box-shadow:none}
}
</style>
</head>
<body>

<div class="container py-4">
<div class="main-card">

<h4 class="mb-3">Expense Report</h4>

<!-- FILTERS -->
<form method="get" class="row g-2 mb-3 no-print">

<div class="col-md-2">
<label class="small">From</label>
<input type="date" name="from" value="<?=esc($from)?>" class="form-control form-control-sm">
</div>

<div class="col-md-2">
<label class="small">To</label>
<input type="date" name="to" value="<?=esc($to)?>" class="form-control form-control-sm">
</div>

<div class="col-md-3">
<label class="small">Main Head</label>
<select name="head" class="form-select form-select-sm">
<option value="">All</option>
<?php while($h=mysqli_fetch_assoc($heads)): ?>
<option value="<?=$h['id']?>" <?=$head==$h['id']?'selected':''?>>
<?=esc($h['name'])?>
</option>
<?php endwhile; ?>
</select>
</div>

<div class="col-md-2">
<label class="small">Payment Mode</label>
<select name="mode" class="form-select form-select-sm">
<option value="">All</option>
<option <?=$mode=='Cash'?'selected':''?>>Cash</option>
<option <?=$mode=='Bank'?'selected':''?>>Bank</option>
<option <?=$mode=='Online'?'selected':''?>>Online</option>
</select>
</div>

<div class="col-md-3 d-flex align-items-end gap-2">
<button class="btn btn-primary btn-sm">Filter</button>
<button type="button" onclick="window.print()" class="btn btn-secondary btn-sm">
Print
</button>
</div>

</form>

<!-- SEARCH -->
<div class="row mb-2 no-print">
<div class="col-md-4">
<input id="searchBox" class="form-control form-control-sm"
placeholder="Live search expense...">
</div>
</div>

<!-- TOTALS -->
<div class="row mb-3">
<div class="col-md-3"><b>Total:</b> <?=number_format($totals['total'] ?? 0,2)?></div>
<div class="col-md-3"><b>Cash:</b> <?=number_format($totals['cash'] ?? 0,2)?></div>
<div class="col-md-3"><b>Bank:</b> <?=number_format($totals['bank'] ?? 0,2)?></div>
<div class="col-md-3"><b>Online:</b> <?=number_format($totals['online'] ?? 0,2)?></div>
</div>

<!-- TABLE -->
<table class="table table-bordered table-sm" id="expTable">
<thead class="table-dark">
<tr>
<th>Date</th>
<th>Main Head</th>
<th>Sub Head</th>
<th class="text-end">Amount</th>
<th>Mode</th>
<th>Details</th>
</tr>
</thead>
<tbody>

<?php while($r=mysqli_fetch_assoc($res)): ?>
<tr>
<td><?=esc($r['exp_date'])?></td>
<td><?=esc($r['head'])?></td>
<td><?=esc($r['sub'])?></td>
<td class="text-end"><?=number_format($r['amount'],2)?></td>
<td><?=esc($r['pay_mode'])?></td>
<td><?=esc($r['details'])?></td>
</tr>
<?php endwhile; ?>

<?php if(mysqli_num_rows($res)==0): ?>
<tr><td colspan="6" class="text-center text-muted">No records found</td></tr>
<?php endif; ?>

</tbody>
</table>

</div>
</div>

<script>
// LIVE SEARCH
document.getElementById('searchBox').addEventListener('keyup',function(){
let v=this.value.toLowerCase();
document.querySelectorAll('#expTable tbody tr').forEach(tr=>{
tr.style.display = tr.innerText.toLowerCase().includes(v) ? '' : 'none';
});
});
</script>

</body>
</html>
