<?php
// hps_default_report.php
require "db.php";

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

/* =========================
   DATE FILTERS
========================= */
$from = $_GET['from'] ?? '';
$to   = $_GET['to'] ?? '';

$cond = "sch.due_date < CURDATE()";

if ($from) {
    $cond .= " AND sch.due_date >= '".mysqli_real_escape_string($conn,$from)."'";
}
if ($to) {
    $cond .= " AND sch.due_date <= '".mysqli_real_escape_string($conn,$to)."'";
}

/* =========================
   FETCH DEFAULTERS
========================= */
$sql = "
SELECT
    c.cnic,
    c.name AS customer,
    c.mobile,
    s.sale_no,

    SUM(sch.amount) AS due_amount,

    IFNULL((
        SELECT SUM(p.amount)
        FROM hps_payments p
        WHERE p.sale_id = s.id
    ),0) AS paid_amount,

    (SUM(sch.amount) -
     IFNULL((
        SELECT SUM(p.amount)
        FROM hps_payments p
        WHERE p.sale_id = s.id
     ),0)
    ) AS outstanding,

    MIN(sch.due_date) AS first_due_date,
    DATEDIFF(CURDATE(), MIN(sch.due_date)) AS days_late

FROM hps_payment_schedule sch
JOIN hps_sales s       ON s.id = sch.sale_id
JOIN hps_customers c  ON c.id = s.customer_id

WHERE $cond
AND s.status = 'Approved'

GROUP BY s.id

HAVING outstanding > 0

ORDER BY days_late DESC
";

$res = mysqli_query($conn, $sql);
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Defaulter 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-3d{
    background:#fff;
    max-width:1300px;
    margin:30px auto;
    padding:25px;
    border-radius:20px;
    box-shadow:0 10px 25px rgba(0,0,0,.12);
}
tr:hover{background:#fff3cd}
@media print{
    .no-print{display:none}
    body{background:#fff}
}
</style>
</head>
<body>

<div class="card-3d">

<h4 class="mb-3 text-danger">⚠ Customer Defaulter Report</h4>

<!-- FILTER -->
<form class="row g-2 mb-3 no-print">
<div class="col-md-3">
<label class="small">From Due Date</label>
<input type="date" name="from" value="<?=esc($from)?>" class="form-control form-control-sm">
</div>

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

<div class="col-md-2 d-flex align-items-end">
<button class="btn btn-primary btn-sm w-100">Filter</button>
</div>

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

<!-- TABLE -->
<table class="table table-bordered table-sm">
<thead class="table-dark">
<tr>
<th>CNIC</th>
<th>Customer</th>
<th>Mobile</th>
<th>Sale No</th>
<th>Due Amount</th>
<th>Paid</th>
<th>Outstanding</th>
<th>First Due</th>
<th>Days Late</th>
</tr>
</thead>
<tbody>

<?php if(mysqli_num_rows($res)==0): ?>
<tr>
<td colspan="9" class="text-center text-success">
No defaulters 🎉
</td>
</tr>
<?php endif; ?>

<?php while($r=mysqli_fetch_assoc($res)): ?>
<tr>
<td><?=esc($r['cnic'])?></td>
<td><?=esc($r['customer'])?></td>
<td><?=esc($r['mobile'])?></td>
<td><?=esc($r['sale_no'])?></td>
<td><?=number_format($r['due_amount'])?></td>
<td><?=number_format($r['paid_amount'])?></td>
<td class="text-danger fw-bold"><?=number_format($r['outstanding'])?></td>
<td><?=$r['first_due_date']?></td>
<td class="text-danger fw-bold"><?=$r['days_late']?></td>
</tr>
<?php endwhile; ?>

</tbody>
</table>

</div>
</body>
</html>
