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

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

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

$cond = "1";
if ($from) $cond .= " AND s.sale_date >= '".mysqli_real_escape_string($conn,$from)."'";
if ($to)   $cond .= " AND s.sale_date <= '".mysqli_real_escape_string($conn,$to)."'";

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

    s.id AS sale_id,
    s.sale_no,
    s.sale_date,

    SUM(i.qty * i.price) AS sale_amount,

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

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

FROM hps_sales s
JOIN hps_customers c ON c.id = s.customer_id
JOIN hps_sale_items i ON i.sale_id = s.id

WHERE s.status != 'Rejected'
AND $cond

GROUP BY s.id
ORDER BY c.cnic, s.sale_date
";

$res = mysqli_query($conn,$sql);

/* =========================
   GROUP BY CNIC
========================= */
$data = [];
while($r = mysqli_fetch_assoc($res)){
    $data[$r['cnic']][] = $r;
}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Customer Sales Report (CNIC-wise)</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">

<style>
body{background:#f1f3f5}
.report-card{
    max-width:1400px;
    margin:30px auto;
    padding:25px;
    background:#fff;
    border-radius:20px;
    box-shadow:0 10px 25px rgba(0,0,0,.12);
}
.cnic-box{
    border:1px solid #dee2e6;
    border-radius:12px;
    margin-bottom:20px;
    padding:15px;
}
@media print{
    .no-print{display:none}
    body{background:#fff}
}
</style>
</head>
<body>

<div class="report-card">

<h4 class="mb-3">📊 Customer Sales Report (CNIC-wise)</h4>

<!-- FILTER -->
<form class="row g-2 mb-4 no-print">
<div class="col-md-3">
<label class="small">From 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 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>

<?php if(empty($data)): ?>
<div class="alert alert-info">No records found</div>
<?php endif; ?>

<?php foreach($data as $cnic=>$rows): ?>

<?php
$totSale = $totPaid = $totBal = 0;
foreach($rows as $r){
    $totSale += $r['sale_amount'];
    $totPaid += $r['paid_amount'];
    $totBal  += $r['outstanding'];
}
?>

<div class="cnic-box">

<div class="row mb-2">
<div class="col-md-4">
<b>CNIC:</b> <?=esc($cnic)?><br>
<b>Customer:</b> <?=esc($rows[0]['customer'])?><br>
<b>Mobile:</b> <?=esc($rows[0]['mobile'])?>
</div>
<div class="col-md-8 text-end">
<b>Total Sale:</b> <?=number_format($totSale)?><br>
<b>Total Paid:</b> <?=number_format($totPaid)?><br>
<b class="text-danger">Outstanding:</b> <?=number_format($totBal)?>
</div>
</div>

<table class="table table-bordered table-sm">
<thead class="table-dark">
<tr>
<th>Sale No</th>
<th>Date</th>
<th>Sale Amount</th>
<th>Paid</th>
<th>Outstanding</th>
</tr>
</thead>
<tbody>

<?php foreach($rows as $r): ?>
<tr>
<td><?=esc($r['sale_no'])?></td>
<td><?=esc($r['sale_date'])?></td>
<td><?=number_format($r['sale_amount'])?></td>
<td><?=number_format($r['paid_amount'])?></td>
<td class="text-danger"><?=number_format($r['outstanding'])?></td>
</tr>
<?php endforeach; ?>

</tbody>
</table>

</div>

<?php endforeach; ?>

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