<?php
error_reporting(E_ALL & ~E_DEPRECATED);
ini_set('display_errors', 1);

require "db.php";

/* =========================
   FILTERS
========================= */
$from_date = $_GET['from_date'] ?? '';
$to_date   = $_GET['to_date']   ?? '';
$search    = $_GET['search']    ?? '';
$mode      = $_GET['mode']      ?? ''; // normal | daily | customer

$where = "WHERE 1=1";

if ($from_date && $to_date) {
    $where .= " AND s.sale_date BETWEEN '$from_date' AND '$to_date'";
}

if ($search && $mode === '') {
    $where .= " AND (
        c.name LIKE '%$search%' OR
        c.mobile LIKE '%$search%' OR
        c.cnic LIKE '%$search%'
    )";
}

/* =========================
   QUERY
========================= */
if ($mode === 'daily') {

    $sql = "
    SELECT
        s.sale_date,

        SUM((
            SELECT SUM(IFNULL(price,0) * IFNULL(qty,1))
            FROM hps_sale_items
            WHERE sale_id = s.id
        )) AS sale_amount,

        SUM((
            SELECT SUM(
                (IFNULL(down_payment,0) + (IFNULL(monthly_installment,0) * IFNULL(installments,0)))
                * IFNULL(qty,1)
            )
            FROM hps_sale_items
            WHERE sale_id = s.id
        ) + IFNULL(s.documentation_charges,0)) AS receivable,

        SUM((
            SELECT SUM(IFNULL(cr,0))
            FROM hps_customer_ledger
            WHERE ref_id = s.id
        )) AS paid

    FROM hps_sales s
    JOIN hps_customers c ON c.id = s.customer_id
    $where
    GROUP BY s.sale_date
    HAVING sale_amount > 0
    ORDER BY s.sale_date DESC
    ";

} elseif ($mode === 'customer') {

    $sql = "
    SELECT
        c.name AS customer,

        SUM((
            SELECT SUM(IFNULL(price,0) * IFNULL(qty,1))
            FROM hps_sale_items
            WHERE sale_id = s.id
        )) AS sale_amount,

        SUM((
            SELECT SUM(
                (IFNULL(down_payment,0) + (IFNULL(monthly_installment,0) * IFNULL(installments,0)))
                * IFNULL(qty,1)
            )
            FROM hps_sale_items
            WHERE sale_id = s.id
        ) + IFNULL(s.documentation_charges,0)) AS receivable,

        SUM((
            SELECT SUM(IFNULL(cr,0))
            FROM hps_customer_ledger
            WHERE ref_id = s.id
        )) AS paid

    FROM hps_sales s
    JOIN hps_customers c ON c.id = s.customer_id
    $where
    GROUP BY c.id
    HAVING sale_amount > 0
    ORDER BY c.name
    ";

} else {

    $where .= " AND (
        SELECT SUM(IFNULL(price,0) * IFNULL(qty,1))
        FROM hps_sale_items
        WHERE sale_id = s.id
    ) > 0";

    $sql = "
    SELECT
    s.sale_date,
    s.sale_ref,
    c.name AS customer,


        (
            SELECT SUM(IFNULL(price,0) * IFNULL(qty,1))
            FROM hps_sale_items
            WHERE sale_id = s.id
        ) AS sale_amount,

        (
            (
                SELECT SUM(
                    (IFNULL(down_payment,0) + (IFNULL(monthly_installment,0) * IFNULL(installments,0)))
                    * IFNULL(qty,1)
                )
                FROM hps_sale_items
                WHERE sale_id = s.id
            )
            + IFNULL(s.documentation_charges,0)
        ) AS receivable,

        (
            SELECT SUM(IFNULL(cr,0))
            FROM hps_customer_ledger
            WHERE ref_id = s.id
        ) AS paid

    FROM hps_sales s
    JOIN hps_customers c ON c.id = s.customer_id
    $where
    ORDER BY s.sale_date DESC
    ";
}

$res = mysqli_query($conn, $sql);
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Hire Purchase Sales Report</title>

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

<style>
table { width:100%; table-layout:fixed; }
th,td { white-space:nowrap; vertical-align:middle; }
.col-amt { text-align:right; width:140px; }

.fixed-footer {
    position: fixed;
    bottom: 0;
    left: 0;
    right: 0;
    background: #fff;
    border-top: 1px solid #ddd;
    padding: 10px 20px;
}

body { padding-bottom:80px; }

@media print {
    .no-print { display:none !important; }
    body { padding-bottom:0; }
}
</style>
</head>

<body class="p-3">

<h5 class="text-center mb-3">
Hire Purchase Sales Report
<?= $mode==='daily'?'(Daily)':($mode==='customer'?'(Customer)':'') ?>
</h5>

<!-- FILTER BAR -->
<form method="get" class="row g-2 mb-2 no-print">
<input type="hidden" name="mode" value="<?= htmlspecialchars($mode) ?>">

<div class="col-md-3"><input type="date" name="from_date" value="<?= $from_date ?>" class="form-control"></div>
<div class="col-md-3"><input type="date" name="to_date" value="<?= $to_date ?>" class="form-control"></div>

<div class="col-md-4">
<input type="text" id="liveSearch" placeholder="Live search in table…" class="form-control">
</div>

<div class="col-md-2 d-flex gap-2">
<button class="btn btn-primary w-100">Filter</button>
<a href="rpt_sales.php" class="btn btn-secondary w-100">Reset</a>
</div>
</form>

<!-- TABLE -->
<div class="table-responsive">
<table class="table table-bordered table-sm" id="salesTable">

<thead class="table-light">
<tr>
<?php if($mode==='daily'){ ?>
<th>Date</th><th class="col-amt">Sale Amt</th><th class="col-amt">Receivable</th><th class="col-amt">Paid</th><th class="col-amt">Outstanding</th>
<?php } elseif($mode==='customer'){ ?>
<th>Customer</th><th class="col-amt">Sale Amt</th><th class="col-amt">Receivable</th><th class="col-amt">Paid</th><th class="col-amt">Outstanding</th>
<?php } else { ?>
<th>Date</th><th>Sale Ref</th><th>Customer</th>
<th class="col-amt">Sale Amt</th><th class="col-amt">Receivable</th><th class="col-amt">Paid</th><th class="col-amt">Outstanding</th>
<?php } ?>
</tr>
</thead>

<tbody>
<?php
$ts=$tr=$tp=$to=0;
while($r=mysqli_fetch_assoc($res)){
    $sale=(float)$r['sale_amount'];
    $recv=(float)$r['receivable'];
    $paid=(float)$r['paid'];
    $out=$recv-$paid;

    $ts+=$sale; $tr+=$recv; $tp+=$paid; $to+=$out;

    echo "<tr>";

    if($mode==='daily'){
        echo "<td>{$r['sale_date']}</td>";
    } elseif($mode==='customer'){
        echo "<td>{$r['customer']}</td>";
    } else {
    echo "<td>{$r['sale_date']}</td>
          <td>{$r['sale_ref']}</td>
          <td>{$r['customer']}</td>";
}


    echo "
    <td class='text-end'>".number_format($sale,0)."</td>
    <td class='text-end'>".number_format($recv,0)."</td>
    <td class='text-end'>".number_format($paid,0)."</td>
    <td class='text-end fw-bold'>".number_format($out,0)."</td>
    </tr>";
}
?>
</tbody>

<tfoot class="table-light fw-bold">
<tr>
<td colspan="<?= ($mode==='daily'||$mode==='customer')?1:3 ?>" class="text-end">TOTAL</td>

<td class="text-end"><?= number_format($ts,0) ?></td>
<td class="text-end"><?= number_format($tr,0) ?></td>
<td class="text-end"><?= number_format($tp,0) ?></td>
<td class="text-end"><?= number_format($to,0) ?></td>
</tr>
</tfoot>

</table>
</div>

<div class="fixed-footer no-print d-flex justify-content-center gap-3">
<a href="rpt_sales.php?mode=daily" class="btn btn-warning">Daily Sales</a>
<a href="rpt_sales.php?mode=customer" class="btn btn-success">Customer Sales</a>
<button onclick="window.print()" class="btn btn-dark">Print</button>
</div>

<!-- LIVE SEARCH SCRIPT -->
<script>
document.getElementById('liveSearch').addEventListener('keyup', function () {
    const q = this.value.toLowerCase();
    document.querySelectorAll('#salesTable tbody tr').forEach(tr => {
        tr.style.display = tr.innerText.toLowerCase().includes(q) ? '' : 'none';
    });
});
</script>

</body>
</html>
