<?php
declare(strict_types=1);

/**
 * modules/reports/pending_aging.php
 *
 * Pending Payments Aging (Driver / Partner / Client)
 * Data source: pending_payments
 *  - Buckets: 0–30 / 31–60 / 61–90 / 90+ days (by created_at)
 *  - Filters: date range (created_at), type, status, min balance, search by booking_ref
 *  - Joins (for context only):
 *      • bookings (booking_ref, client_name, pickup_date/time, partner_id)
 *      • primary driver via booking_vehicles (first by sequence_order)
 *      • partners (name)
 *  - KPI cards + bucket table + detail table
 *  - CSV export (?export=csv) of detail list
 */

require_once dirname(__DIR__, 2) . '/config/functions.php';
require_role(['MD','Accounts','Admin','Management']);

$user = current_user();
$cid  = (int)($user['company_id'] ?? 0);
if ($cid <= 0) redirect(url_public('login.php'));

$errors = [];
$notice = null;

/* -----------------------------
   Helpers
------------------------------*/
function tbl_exists(string $t): bool {
  try { db()->query("SELECT 1 FROM `{$t}` LIMIT 1"); return true; }
  catch (Throwable) { return false; }
}

/* -----------------------------
   Guard: table required
------------------------------*/
if (!tbl_exists('pending_payments')) {
  include dirname(__DIR__, 2) . '/includes/header.php';
  ?>
  <div class="d-flex justify-content-between align-items-center mb-3">
    <h1 class="h4 mb-0">Pending Payments Aging</h1>
  </div>
  <div class="alert alert-danger">
    The <code>pending_payments</code> table is missing. This report requires it.
  </div>
  <?php
  include dirname(__DIR__, 2) . '/includes/footer.php';
  exit;
}

/* -----------------------------
   Filters
------------------------------*/
$today      = new DateTimeImmutable('today');
$monthStart = $today->modify('first day of this month')->format('Y-m-01');
$monthEnd   = $today->modify('last day of this month')->format('Y-m-d');

$from   = (string)($_GET['from'] ?? $monthStart);
$to     = (string)($_GET['to']   ?? $monthEnd);
$type   = (string)($_GET['type'] ?? 'all');       // driver|partner|client|all
$status = (string)($_GET['status'] ?? 'pending'); // pending|closed|all
$minBal = (float)($_GET['min_balance'] ?? 0.01);  // ignore tiny noise by default
$q      = trim((string)($_GET['q'] ?? ''));       // booking_ref search
$export = (string)($_GET['export'] ?? '');

if (!preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) $from = $monthStart;
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/',$to))   $to   = $monthEnd;
if (!in_array($type, ['driver','partner','client','all'], true)) $type = 'all';
if (!in_array($status, ['pending','closed','all'], true)) $status = 'pending';

$fromDT = $from.' 00:00:00';
$toDT   = $to.' 23:59:59';

/* -----------------------------
   WHERE (shared)
------------------------------*/
$where = ["pp.company_id = :cid", "pp.created_at BETWEEN :from AND :to"];
$args  = [':cid'=>$cid, ':from'=>$fromDT, ':to'=>$toDT];

if ($type !== 'all') { $where[] = "pp.`type` = :type"; $args[':type'] = $type; }
if ($status !== 'all') { $where[] = "pp.`status` = :status"; $args[':status'] = $status; }
if ($minBal > 0)   { $where[] = "pp.balance_due >= :minb"; $args[':minb'] = $minBal; }
if ($q !== '')     { $where[] = "pp.booking_ref LIKE :q"; $args[':q'] = '%'.$q.'%'; }

$whereSql = implode(' AND ', $where);

/* -----------------------------
   KPIs (totals & avg age)
------------------------------*/
$totalAmt = 0.0; $itemCount = 0; $avgAge = 0.0;
try {
  $k = db()->prepare("
    SELECT
      COUNT(*) AS cnt,
      COALESCE(SUM(pp.balance_due),0) AS total_amt,
      COALESCE(AVG(TIMESTAMPDIFF(DAY, pp.created_at, NOW())),0) AS avg_age
    FROM pending_payments pp
    WHERE $whereSql
  ");
  $k->execute($args);
  $row = $k->fetch() ?: [];
  $itemCount = (int)($row['cnt'] ?? 0);
  $totalAmt  = (float)($row['total_amt'] ?? 0.0);
  $avgAge    = (float)($row['avg_age'] ?? 0.0);
} catch (Throwable $e) {
  $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? 'KPIs: '.$e->getMessage() : 'Unable to load KPIs.';
}

/* -----------------------------
   Bucket summary
------------------------------*/
$buckets = [
  '0–30'  => ['cnt'=>0, 'amt'=>0.0],
  '31–60' => ['cnt'=>0, 'amt'=>0.0],
  '61–90' => ['cnt'=>0, 'amt'=>0.0],
  '90+'   => ['cnt'=>0, 'amt'=>0.0],
];
try {
  $g = db()->prepare("
    SELECT bucket, COUNT(*) AS cnt, SUM(balance_due) AS amt
    FROM (
      SELECT
        CASE
          WHEN TIMESTAMPDIFF(DAY, pp.created_at, NOW()) <= 30 THEN '0–30'
          WHEN TIMESTAMPDIFF(DAY, pp.created_at, NOW()) <= 60 THEN '31–60'
          WHEN TIMESTAMPDIFF(DAY, pp.created_at, NOW()) <= 90 THEN '61–90'
          ELSE '90+'
        END AS bucket,
        pp.balance_due
      FROM pending_payments pp
      WHERE $whereSql
    ) x
    GROUP BY bucket
  ");
  $g->execute($args);
  foreach ($g->fetchAll() as $r) {
    $b = (string)$r['bucket'];
    if (!isset($buckets[$b])) continue;
    $buckets[$b]['cnt'] = (int)$r['cnt'];
    $buckets[$b]['amt'] = (float)$r['amt'];
  }
} catch (Throwable $e) {
  $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? 'Buckets: '.$e->getMessage() : 'Unable to load aging buckets.';
}

/* -----------------------------
   Detail (with joins for context)
------------------------------*/
$detail = [];
try {
  $sql = "
    SELECT
      pp.id, pp.booking_id, pp.booking_ref, pp.`type`, pp.total_due, pp.amount_paid,
      pp.balance_due, pp.`status`, pp.created_at,
      b.client_name, b.pickup_date, b.pickup_time, b.partner_id,
      /* primary driver name via subquery to booking_vehicles */
      (SELECT d.name
         FROM booking_vehicles bv
         JOIN drivers d ON d.id=bv.driver_id
        WHERE bv.booking_id = b.id
        ORDER BY bv.sequence_order ASC, bv.id ASC
        LIMIT 1) AS driver_name,
      (SELECT d.id
         FROM booking_vehicles bv
         JOIN drivers d ON d.id=bv.driver_id
        WHERE bv.booking_id = b.id
        ORDER BY bv.sequence_order ASC, bv.id ASC
        LIMIT 1) AS driver_id,
      (SELECT p.name FROM partners p WHERE p.id=b.partner_id AND p.company_id=b.company_id LIMIT 1) AS partner_name,
      TIMESTAMPDIFF(DAY, pp.created_at, NOW()) AS age_days,
      CASE
        WHEN TIMESTAMPDIFF(DAY, pp.created_at, NOW()) <= 30 THEN '0–30'
        WHEN TIMESTAMPDIFF(DAY, pp.created_at, NOW()) <= 60 THEN '31–60'
        WHEN TIMESTAMPDIFF(DAY, pp.created_at, NOW()) <= 90 THEN '61–90'
        ELSE '90+'
      END AS bucket
    FROM pending_payments pp
    LEFT JOIN bookings b
      ON b.id = pp.booking_id AND b.company_id = pp.company_id
    WHERE $whereSql
    ORDER BY age_days DESC, pp.balance_due DESC, pp.id DESC
    LIMIT 500
  ";
  $d = db()->prepare($sql);
  $d->execute($args);
  $detail = $d->fetchAll() ?: [];
} catch (Throwable $e) {
  $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? 'Detail: '.$e->getMessage() : 'Unable to load detail list.';
}

/* -----------------------------
   CSV Export (detail)
------------------------------*/
if ($export === 'csv') {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="pending_aging_'.date('Ymd_His').'.csv"');
  $out = fopen('php://output', 'w');
  fputcsv($out, [
    'ID','Booking Ref','Type','Status','Created At','Age (days)','Bucket',
    'Client','Pickup Date','Pickup Time','Driver','Partner','Total Due','Amount Paid','Balance Due'
  ]);
  foreach ($detail as $r) {
    fputcsv($out, [
      (int)$r['id'],
      (string)$r['booking_ref'],
      (string)$r['type'],
      (string)$r['status'],
      (string)$r['created_at'],
      (int)$r['age_days'],
      (string)$r['bucket'],
      (string)($r['client_name'] ?? ''),
      (string)($r['pickup_date'] ?? ''),
      (string)($r['pickup_time'] ?? ''),
      (string)($r['driver_name'] ?? ''),
      (string)($r['partner_name'] ?? ''),
      number_format((float)$r['total_due'], 2, '.', ''),
      number_format((float)$r['amount_paid'], 2, '.', ''),
      number_format((float)$r['balance_due'], 2, '.', ''),
    ]);
  }
  fclose($out);
  exit;
}

/* -----------------------------
   Render
------------------------------*/
include dirname(__DIR__, 2) . '/includes/header.php';
?>
<div class="d-flex justify-content-between align-items-center mb-3">
  <div>
    <h1 class="h4 mb-0">Pending Payments Aging</h1>
    <div class="text-muted">Buckets by days since created (0–30 / 31–60 / 61–90 / 90+).</div>
  </div>
  <div class="d-flex flex-wrap gap-2">
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('reports/index.php')) ?>">← Reports Home</a>
    <?php $qs = $_GET; $qs['export'] = 'csv'; $exportUrl = '?' . http_build_query($qs); ?>
    <a class="btn btn-outline-primary" href="<?= e($exportUrl) ?>">⬇️ Export CSV</a>
  </div>
</div>

<?php if ($notice): ?><div class="alert alert-success"><?= e($notice) ?></div><?php endif; ?>
<?php if ($errors): ?><div class="alert alert-warning"><ul class="mb-0"><?php foreach ($errors as $er) echo '<li>'.e($er).'</li>'; ?></ul></div><?php endif; ?>

<!-- Filters -->
<div class="card shadow-sm mb-3">
  <div class="card-body">
    <form class="row g-2 align-items-end" method="get">
      <div class="col-sm-3">
        <label class="form-label">From</label>
        <input type="date" class="form-control" name="from" value="<?= e($from) ?>">
      </div>
      <div class="col-sm-3">
        <label class="form-label">To</label>
        <input type="date" class="form-control" name="to" value="<?= e($to) ?>">
      </div>
      <div class="col-sm-3">
        <label class="form-label">Type</label>
        <select class="form-select" name="type">
          <option value="all"     <?= $type==='all'?'selected':'' ?>>All</option>
          <option value="driver"  <?= $type==='driver'?'selected':'' ?>>Driver</option>
          <option value="partner" <?= $type==='partner'?'selected':'' ?>>Partner</option>
          <option value="client"  <?= $type==='client'?'selected':'' ?>>Client</option>
        </select>
      </div>
      <div class="col-sm-3">
        <label class="form-label">Status</label>
        <select class="form-select" name="status">
          <option value="pending" <?= $status==='pending'?'selected':'' ?>>Pending</option>
          <option value="closed"  <?= $status==='closed'?'selected':'' ?>>Closed</option>
          <option value="all"     <?= $status==='all'?'selected':'' ?>>All</option>
        </select>
      </div>
      <div class="col-sm-3">
        <label class="form-label">Min Balance (£)</label>
        <input type="number" step="0.01" min="0" class="form-control" name="min_balance" value="<?= e(number_format($minBal,2,'.','')) ?>">
      </div>
      <div class="col-sm-6">
        <label class="form-label">Search Booking Ref</label>
        <input class="form-control" name="q" value="<?= e($q) ?>" placeholder="e.g., BH-2025-0901">
      </div>
      <div class="col-sm-3 d-grid">
        <button class="btn btn-primary">Apply Filters</button>
      </div>
    </form>
  </div>
</div>

<!-- KPI Cards -->
<div class="row g-3 mb-3">
  <div class="col-12 col-lg-4">
    <div class="card shadow-sm h-100 border-dark">
      <div class="card-body">
        <div class="small text-muted">Total Pending</div>
        <div class="display-6">£<?= number_format($totalAmt, 2) ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-lg-4">
    <div class="card shadow-sm h-100 border-secondary">
      <div class="card-body">
        <div class="small text-muted">Items</div>
        <div class="display-6"><?= number_format($itemCount) ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-lg-4">
    <div class="card shadow-sm h-100 border-info">
      <div class="card-body">
        <div class="small text-muted">Average Age (days)</div>
        <div class="display-6"><?= number_format($avgAge, 1) ?></div>
      </div>
    </div>
  </div>
</div>

<!-- Aging Buckets -->
<div class="card shadow-sm mb-3">
  <div class="card-body">
    <div class="d-flex justify-content-between align-items-center mb-2">
      <div class="fw-semibold">Aging Buckets</div>
      <div class="small text-muted"><?= e($from) ?> → <?= e($to) ?></div>
    </div>

    <div class="table-responsive mb-3">
      <table class="table table-sm align-middle mb-0">
        <thead class="table-light">
          <tr>
            <th>Bucket</th>
            <th class="text-end">Count</th>
            <th class="text-end">Amount (£)</th>
          </tr>
        </thead>
        <tbody>
          <?php foreach (['0–30','31–60','61–90','90+'] as $bk): ?>
            <tr>
              <td><?= e($bk) ?></td>
              <td class="text-end"><?= number_format((int)$buckets[$bk]['cnt']) ?></td>
              <td class="text-end">£<?= number_format((float)$buckets[$bk]['amt'], 2) ?></td>
            </tr>
          <?php endforeach; ?>
        </tbody>
      </table>
    </div>

    <!-- Optional mini chart (uses Chart.js if available) -->
    <canvas id="agingChart" height="80"></canvas>
  </div>
</div>

<!-- Detail List -->
<div class="card shadow-sm">
  <div class="card-body">
    <div class="d-flex justify-content-between align-items-center mb-2">
      <div class="fw-semibold">Pending Items (Top 500 by age/balance)</div>
    </div>
    <div class="table-responsive">
      <table class="table table-sm align-middle mb-0">
        <thead class="table-light">
          <tr>
            <th>Booking Ref</th>
            <th>Type</th>
            <th>Status</th>
            <th>Created</th>
            <th class="text-end">Age (days)</th>
            <th>Bucket</th>
            <th>Client</th>
            <th>Driver</th>
            <th>Partner</th>
            <th class="text-end">£ Total</th>
            <th class="text-end">£ Paid</th>
            <th class="text-end">£ Balance</th>
          </tr>
        </thead>
        <tbody>
          <?php if ($detail): foreach ($detail as $r): ?>
            <tr>
              <td><strong><?= e($r['booking_ref'] ?? ('#'.$r['booking_id'])) ?></strong></td>
              <td><span class="badge text-bg-light"><?= e($r['type']) ?></span></td>
              <td><span class="badge text-bg-<?= ($r['status']==='pending'?'warning':'success') ?>"><?= e($r['status']) ?></span></td>
              <td><?= e((string)$r['created_at']) ?></td>
              <td class="text-end"><?= number_format((int)$r['age_days']) ?></td>
              <td><?= e((string)$r['bucket']) ?></td>
              <td><?= e((string)($r['client_name'] ?? '')) ?></td>
              <td><?= e((string)($r['driver_name'] ?? '')) ?></td>
              <td><?= e((string)($r['partner_name'] ?? '')) ?></td>
              <td class="text-end">£<?= number_format((float)$r['total_due'], 2) ?></td>
              <td class="text-end">£<?= number_format((float)$r['amount_paid'], 2) ?></td>
              <td class="text-end fw-semibold">£<?= number_format((float)$r['balance_due'], 2) ?></td>
            </tr>
          <?php endforeach; else: ?>
            <tr><td colspan="12" class="text-center text-muted py-4">No pending items for the selected filters.</td></tr>
          <?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>

<?php include dirname(__DIR__, 2) . '/includes/footer.php'; ?>

<!-- Mini chart (safe if CDN blocked: it just won't render) -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
(function(){
  const ctx = document.getElementById('agingChart');
  if (!ctx || typeof Chart === 'undefined') return;
  const labels = ['0–30','31–60','61–90','90+'];
  const counts = [
    <?= (int)$buckets['0–30']['cnt'] ?>,
    <?= (int)$buckets['31–60']['cnt'] ?>,
    <?= (int)$buckets['61–90']['cnt'] ?>,
    <?= (int)$buckets['90+']['cnt'] ?>
  ];
  const amounts = [
    <?= number_format((float)$buckets['0–30']['amt'], 2, '.', '') ?>,
    <?= number_format((float)$buckets['31–60']['amt'], 2, '.', '') ?>,
    <?= number_format((float)$buckets['61–90']['amt'], 2, '.', '') ?>,
    <?= number_format((float)$buckets['90+']['amt'], 2, '.', '') ?>
  ];

  new Chart(ctx, {
    type: 'bar',
    data: {
      labels,
      datasets: [
        { label: 'Count',  data: counts },
        { label: 'Amount (£)', data: amounts }
      ]
    },
    options: {
      responsive: true,
      plugins: { legend: { position: 'top' } },
      scales: {
        y: { beginAtZero: true }
      }
    }
  });
})();
</script>
