<?php
// hps_salesperson_call_report.php

session_start();
require "db.php";

ini_set('display_errors',1);
error_reporting(E_ALL);

function e($value){
    return htmlspecialchars((string)($value ?? ''));
}

/* =========================
   FILTER
========================= */
$salesperson_id = (int)($_GET['salesperson_id'] ?? 0);

/* =========================
   FETCH SALESPERSONS
========================= */
$salespersons = mysqli_query($conn,"
    SELECT id, name
    FROM hps_salespersons
    WHERE status='Active'
    ORDER BY name
");

/* =========================
   MAIN QUERY
========================= */
$where = " WHERE s.status='Approved' ";

if($salesperson_id > 0){
    $where .= " AND s.salesperson_id = $salesperson_id ";
}

$sql = "
SELECT
    s.id,
    s.sale_ref,
    s.sale_date,
    c.name AS customer,
    c.mobile,
    sp.name AS salesperson,

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

    (
        SELECT IFNULL(SUM(cr),0)
        FROM hps_customer_ledger
        WHERE ref_type='INSTALLMENT'
        AND ref_id = s.id
    ) AS total_paid,

    (
        SELECT IFNULL(SUM(
            ((price * qty) - down_payment) / NULLIF(installments,0)
        ),0)
        FROM hps_sale_items
        WHERE sale_id = s.id
    ) AS monthly_installment

FROM hps_sales s
JOIN hps_customers c ON c.id = s.customer_id
LEFT JOIN hps_salespersons sp ON sp.id = s.salesperson_id
$where
ORDER BY s.sale_date DESC
";

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

if(!$result){
    die("SQL ERROR: ".mysqli_error($conn));
}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Salesperson Call Report</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">

<style>
@media print {
    body { font-size:12px; color:#000 !important; }
    * { color:#000 !important; background:#fff !important; }
    .no-print { display:none !important; }
    table { border-collapse:collapse !important; }
    table th, table td { border:1px solid #000 !important; padding:4px !important; }
    .table-dark { background:#fff !important; color:#000 !important; }
}
</style>
</head>
<body>

<div class="container-fluid mt-3">

<h4 class="mb-3">Salesperson Recovery Call Report</h4>

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

    <div class="col-md-4">
        <label class="form-label small">Select Salesperson</label>
        <select name="salesperson_id" class="form-select form-select-sm">
            <option value="0">All Salespersons</option>
            <?php while($sp=mysqli_fetch_assoc($salespersons)): ?>
                <option value="<?= $sp['id'] ?>"
                    <?= ($salesperson_id==$sp['id'])?'selected':'' ?>>
                    <?= e($sp['name']) ?>
                </option>
            <?php endwhile; ?>
        </select>
    </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-success btn-sm w-100">Print</button>
    </div>

</form>

<table class="table table-bordered table-sm">
<thead class="table-dark">
<tr>
    <th>#</th>
    <th>Sale Ref</th>
    <th class="no-print">Details</th>
    <th>Date</th>
    <th>Customer</th>
    <th>Mobile</th>
    <th>Salesperson</th>
    <th>Total</th>
    <th>Monthly</th>
    <th>Inst (Paid/Total)</th>
    <th>Paid</th>
    <th>Outstanding</th>
</tr>
</thead>
<tbody>

<?php
if(mysqli_num_rows($result)==0){
    echo "<tr><td colspan='12' class='text-center'>No records found</td></tr>";
}

$i=1;
$grand_total = 0;
$grand_paid = 0;
$grand_outstanding = 0;

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

    /* =========================
       INSTALLMENT INFO
    ========================== */

    // total installments
    $q1 = mysqli_query($conn,"
        SELECT MAX(installments) as total_inst
        FROM hps_sale_items
        WHERE sale_id=".(int)$row['id']."
    ");
    $total_inst = 0;
    if($q1){
        $r1 = mysqli_fetch_assoc($q1);
        $total_inst = (int)($r1['total_inst'] ?? 0);
    }

    // paid installments
    $q2 = mysqli_query($conn,"
        SELECT COUNT(*) as paid_inst
        FROM hps_customer_ledger
        WHERE ref_type='INSTALLMENT'
        AND ref_id=".(int)$row['id']."
        AND cr > 0
    ");
    $paid_inst = 0;
    if($q2){
        $r2 = mysqli_fetch_assoc($q2);
        $paid_inst = (int)($r2['paid_inst'] ?? 0);
    }

    $outstanding = $row['total_sale'] - $row['total_paid'];

    $grand_total += $row['total_sale'];
    $grand_paid += $row['total_paid'];
    $grand_outstanding += $outstanding;
?>

<tr>
    <td><?= $i++ ?></td>
    <td class="fw-bold"><?= e($row['sale_ref']) ?></td>

    <td class="no-print">
        <button class="btn btn-sm btn-primary"
            data-bs-toggle="modal"
            data-bs-target="#ledgerModal"
            onclick="loadLedger(<?= (int)$row['id'] ?>)">
            View
        </button>
    </td>

    <td><?= date('d-M-Y', strtotime($row['sale_date'])) ?></td>
    <td><?= e($row['customer']) ?></td>
    <td><?= e($row['mobile']) ?></td>
    <td><?= e($row['salesperson'] ?? 'N/A') ?></td>

    <td><?= number_format($row['total_sale']) ?></td>
    <td><?= number_format($row['monthly_installment']) ?></td>
    <td><?= $paid_inst ?>/<?= $total_inst ?></td>
    <td><?= number_format($row['total_paid']) ?></td>
    <td><?= number_format($outstanding) ?></td>
</tr>

<?php endwhile; ?>

<tr class="table-light fw-bold">
    <td colspan="7" class="text-end">TOTAL</td>
    <td><?= number_format($grand_total) ?></td>
    <td>-</td>
    <td>-</td>
    <td><?= number_format($grand_paid) ?></td>
    <td><?= number_format($grand_outstanding) ?></td>
</tr>

</tbody>
</table>

</div>

<!-- LEDGER MODAL -->
<div class="modal fade" id="ledgerModal" tabindex="-1">
  <div class="modal-dialog modal-lg modal-dialog-scrollable">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title">Customer Schedule & Ledger</h5>
        <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
      </div>
      <div class="modal-body" id="ledgerContent">Loading...</div>
    </div>
  </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
<script>
function loadLedger(sale_id){
    document.getElementById("ledgerContent").innerHTML = "Loading...";
    fetch("hps_sale_ledger_modal.php?sale_id=" + sale_id)
    .then(response => response.text())
    .then(data => {
        document.getElementById("ledgerContent").innerHTML = data;
    });
}
</script>

</body>
</html>
