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

require "db.php";

/* =========================
   FILTERS
========================= */
$name_filter   = $_GET['name']   ?? '';
$cnic_filter   = $_GET['cnic']   ?? '';
$mobile_filter = $_GET['mobile'] ?? '';

$where = " WHERE s.status='Approved' ";

if($name_filter!=''){
    $name_filter = mysqli_real_escape_string($conn,$name_filter);
    $where .= " AND c.name LIKE '%$name_filter%' ";
}
if($cnic_filter!=''){
    $cnic_filter = mysqli_real_escape_string($conn,$cnic_filter);
    $where .= " AND c.cnic LIKE '%$cnic_filter%' ";
}
if($mobile_filter!=''){
    $mobile_filter = mysqli_real_escape_string($conn,$mobile_filter);
    $where .= " AND c.mobile LIKE '%$mobile_filter%' ";
}

/* =========================
   MAIN QUERY
========================= */

$sql = "
SELECT
    s.id AS sale_id,
    s.sale_date,
    c.name,
    c.cnic,
    c.mobile,

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

    (
        SELECT SUM(IFNULL(installments,0))
        FROM hps_sale_items
        WHERE sale_id = s.id
    ) AS total_months,

    (
        SELECT SUM(IFNULL(down_payment,0))
        FROM hps_sale_items
        WHERE sale_id = s.id
    ) AS down_payment,

    (
        (
            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>Outstanding Report</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
body{
    background:#f4f6f9;
}
.card{
    border:1px solid #000;
}
.table th{
    background:#000;
    color:#fff;
}
.table td{
    color:#000;
}

/* ===== PRINT STYLE ===== */
@media print{

    body{
        background:#fff;
    }

    .no-print{
        display:none !important;
    }

    .card{
        border:none;
        box-shadow:none;
    }

    .table th{
        background:#fff !important;
        color:#000 !important;
        border:1px solid #000 !important;
    }

    .table td{
        color:#000 !important;
        border:1px solid #000 !important;
    }

    table{
        width:100%;
        font-size:12px;
    }

    h4{
        text-align:center;
        font-weight:bold;
        margin-bottom:20px;
    }

    @page{
        size:A4 portrait;
        margin:15mm;
    }
}
</style>

</head>
<body>

<div class="container-fluid mt-4">
<div class="card shadow p-4">

<h4 class="mb-3">Customer Outstanding & Next Installment Due Report</h4>

<form method="get" class="row g-2 mb-3 no-print">
    <div class="col-md-3">
        <input type="text" name="name" class="form-control"
        placeholder="Customer Name"
        value="<?= htmlspecialchars($name_filter) ?>">
    </div>
    <div class="col-md-3">
        <input type="text" name="cnic" class="form-control"
        placeholder="CNIC"
        value="<?= htmlspecialchars($cnic_filter) ?>">
    </div>
    <div class="col-md-3">
        <input type="text" name="mobile" class="form-control"
        placeholder="Mobile"
        value="<?= htmlspecialchars($mobile_filter) ?>">
    </div>
    <div class="col-md-3 d-flex gap-2">
    <button class="btn btn-primary w-100 no-print">Filter</button>
    <a href="?" class="btn btn-secondary w-100 no-print">Reset</a>
    <button type="button" onclick="window.print()" class="btn btn-dark w-100 no-print">Print</button>
</div>

</form>

<div class="table-responsive">
<table class="table table-bordered table-striped text-center align-middle">
<thead>
<tr>
    <th>#</th>
    <th>Customer Name</th>
    <th>CNIC</th>
    <th>Mobile</th>
    <th>Total Outstanding</th>
    <th>Next Due Amount</th>
    <th>Next Due Date</th>
    <th>Status</th>
</tr>
</thead>
<tbody>

<?php
$i=1;
$total_grand=0;

while($row = mysqli_fetch_assoc($res)):

$receivable = (float)$row['receivable'];
$paid       = (float)$row['paid'];
$monthly    = (float)$row['monthly_total'];
$total_months = (int)$row['total_months'];
$down_payment = (float)$row['down_payment'];

$total_outstanding = $receivable - $paid;
$total_grand += $total_outstanding;

$next_due_amount = "-";
$next_due_date   = "-";
$status_text     = "-";

if($total_outstanding > 0 && $monthly > 0){

    $sale_date = new DateTime($row['sale_date']);
    $today     = new DateTime();

    $remaining_after_down = max(0, $paid - $down_payment);
    $paid_installments = floor($remaining_after_down / $monthly);
    $next_installment_number = $paid_installments + 1;

    if($next_installment_number <= $total_months){

        $next_due_amount = number_format($monthly,2);

        $next_due_date_obj = clone $sale_date;
        $next_due_date_obj->modify("+$next_installment_number month");
        $next_due_date = $next_due_date_obj->format("d-M-Y");

        if($next_due_date_obj < $today){

            $days = $next_due_date_obj->diff($today)->days;

            if($days <= 10){
                $label = "Late";
            }elseif($days <= 20){
                $label = "Alert";
            }else{
                $label = "Overdue";
            }

            $status_text = "<span style='color:red;font-weight:bold'>
                            {$days} days<br>($label)
                            </span>";

        }elseif($next_due_date_obj > $today){

            $days = $today->diff($next_due_date_obj)->days;

            $status_text = "<span style='color:green;font-weight:bold'>
                            {$days} days<br>(Upcoming)
                            </span>";

        }else{

            $status_text = "<span style='color:orange;font-weight:bold'>
                            Due Today
                            </span>";
        }
    }
}
?>

<tr>
    <td><?= $i++ ?></td>
    <td><?= htmlspecialchars($row['name']) ?></td>
    <td><?= htmlspecialchars($row['cnic']) ?></td>
    <td><?= htmlspecialchars($row['mobile']) ?></td>
    <td><?= number_format($total_outstanding,2) ?></td>
    <td><?= $next_due_amount ?></td>
    <td><?= $next_due_date ?></td>
    <td><?= $status_text ?></td>
</tr>

<?php endwhile; ?>

</tbody>

<tfoot>
<tr>
    <th colspan="4">Grand Total Outstanding</th>
    <th colspan="4"><?= number_format($total_grand,2) ?></th>
</tr>
</tfoot>

</table>
</div>

</div>
</div>

</body>
</html>
