<?php
declare(strict_types=1);

/**
 * modules/reports/bookings_status.php
 *
 * Bookings by Status & Volume
 *
 * Filters:
 *  - Date range (pickup_date)
 *  - Group by: Day | Week | Month
 *  - Partner (optional)
 *  - Corporate (optional)
 *  - Search (ref / client / addresses)
 *
 * Output:
 *  - KPI cards (Totals by key statuses)
 *  - Trend chart (lines per status)
 *  - Job type split (Transfer vs As Directed)
 *  - Table by bucket (counts per status + total)
 *  - CSV export
 */

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; }
}

/* -----------------------------
   Filters & defaults
------------------------------*/
$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);
$group    = (string)($_GET['group'] ?? 'month'); // day|week|month
$partnerId= (int)($_GET['partner_id'] ?? 0);
$corpId   = (int)($_GET['corporate_id'] ?? 0);
$q        = trim((string)($_GET['q'] ?? ''));
$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($group, ['day','week','month'], true)) $group = 'month';

$hasPartners  = tbl_exists('partners');
$hasCorporates= tbl_exists('corporates');

/* -----------------------------
   Dropdown data (optional)
------------------------------*/
$partners = [];
$corporates = [];
try {
  if ($hasPartners) {
    $p = db()->prepare("SELECT id, name FROM partners WHERE company_id=:cid ORDER BY name ASC");
    $p->execute([':cid'=>$cid]);
    $partners = $p->fetchAll() ?: [];
  }
  if ($hasCorporates) {
    $c = db()->prepare("SELECT id, name FROM corporates WHERE company_id=:cid ORDER BY name ASC");
    $c->execute([':cid'=>$cid]);
    $corporates = $c->fetchAll() ?: [];
  }
} catch (Throwable) {}

/* -----------------------------
   Build Query
------------------------------*/
$bucketIdExpr = '';
$bucketLabelExpr = '';

if ($group === 'day') {
  $bucketIdExpr    = "DATE(b.pickup_date)";
  $bucketLabelExpr = "DATE_FORMAT(b.pickup_date, '%Y-%m-%d')";
} elseif ($group === 'week') {
  // Week starts Monday; label "Week of YYYY-MM-DD"
  $bucketIdExpr    = "DATE_SUB(b.pickup_date, INTERVAL WEEKDAY(b.pickup_date) DAY)";
  $bucketLabelExpr = "CONCAT('Week of ', DATE_FORMAT(DATE_SUB(b.pickup_date, INTERVAL WEEKDAY(b.pickup_date) DAY),'%Y-%m-%d'))";
} else { // month
  $bucketIdExpr    = "DATE_FORMAT(b.pickup_date, '%Y-%m')";
  $bucketLabelExpr = "DATE_FORMAT(b.pickup_date, '%Y-%m')";
}

$baseWhere = ["b.company_id = :cid", "b.pickup_date BETWEEN :from AND :to"];
$args = [':cid'=>$cid, ':from'=>$from, ':to'=>$to];

if ($partnerId > 0) { $baseWhere[] = "b.partner_id = :pid"; $args[':pid'] = $partnerId; }
if ($corpId > 0)    { $baseWhere[] = "b.corporate_id = :cid2"; $args[':cid2'] = $corpId; }
if ($q !== '') {
  $baseWhere[] = "(b.booking_ref LIKE :q OR b.client_name LIKE :q OR b.pickup_address LIKE :q OR b.dropoff_address LIKE :q)";
  $args[':q'] = '%'.$q.'%';
}

$whereSQL = implode(' AND ', $baseWhere);

// Status list from schema
$statuses = ['New','Confirmed','Assigned','InProgress','Completed','Invoiced','Paid'];

// Build SUM(CASE...) per status
$sumParts = [];
foreach ($statuses as $st) {
  $alias = 's_' . strtolower($st);
  $sumParts[] = "SUM(CASE WHEN b.status = " . db()->quote($st) . " THEN 1 ELSE 0 END) AS {$alias}";
}
$sumStatusSQL = implode(",\n        ", $sumParts);

// Job type splits
$sumTypesSQL = "
  SUM(CASE WHEN b.booking_type='Transfer'    THEN 1 ELSE 0 END) AS t_transfer,
  SUM(CASE WHEN b.booking_type='As Directed' THEN 1 ELSE 0 END) AS t_ad
";

// Main aggregation
$sql = "
  SELECT
    {$bucketIdExpr}    AS bucket_id,
    {$bucketLabelExpr} AS bucket_label,
    COUNT(*) AS total,
    {$sumStatusSQL},
    {$sumTypesSQL}
  FROM bookings b
  WHERE {$whereSQL}
  GROUP BY bucket_id, bucket_label
  ORDER BY bucket_id ASC
  LIMIT 2000
";

$rows = [];
try {
  $st = db()->prepare($sql);
  $st->execute($args);
  $rows = $st->fetchAll() ?: [];
} catch (Throwable $e) {
  $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? $e->getMessage() : 'Unable to load bookings status report.';
}

/* -----------------------------
   Totals & Chart Data
------------------------------*/
$totals = [
  'total' => 0,
  'transfer' => 0,
  'ad' => 0,
];
foreach ($statuses as $st) $totals['s_'.strtolower($st)] = 0;

$labels   = [];
$series   = []; // per status arrays
foreach ($statuses as $st) $series['s_'.strtolower($st)] = [];

$typeTransfer = [];
$typeAD       = [];

foreach ($rows as $r) {
  $labels[] = (string)$r['bucket_label'];
  $totals['total'] += (int)$r['total'];
  $totals['transfer'] += (int)$r['t_transfer'];
  $totals['ad'] += (int)$r['t_ad'];

  foreach ($statuses as $st) {
    $k = 's_'.strtolower($st);
    $val = (int)($r[$k] ?? 0);
    $series[$k][] = $val;
    $totals[$k] += $val;
  }

  $typeTransfer[] = (int)$r['t_transfer'];
  $typeAD[]       = (int)$r['t_ad'];
}

/* -----------------------------
   CSV Export
------------------------------*/
if ($export === 'csv') {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="bookings_status_'.date('Ymd_His').'.csv"');
  $out = fopen('php://output', 'w');
  fputcsv($out, ['From',$from,'To',$to,'Group',$group,'Partner',$partnerId,'Corporate',$corpId,'Search',$q]);
  fputcsv($out, []);
  $hdr = ['Bucket','Total'];
  foreach ($statuses as $st) $hdr[] = $st;
  $hdr[] = 'Transfer';
  $hdr[] = 'As Directed';
  fputcsv($out, $hdr);

  foreach ($rows as $r) {
    $line = [(string)$r['bucket_label'], (int)$r['total']];
    foreach ($statuses as $st) $line[] = (int)$r['s_'.strtolower($st)];
    $line[] = (int)$r['t_transfer'];
    $line[] = (int)$r['t_ad'];
    fputcsv($out, $line);
  }

  // Totals
  $totLine = ['TOTAL', (int)$totals['total']];
  foreach ($statuses as $st) $totLine[] = (int)$totals['s_'.strtolower($st)];
  $totLine[] = (int)$totals['transfer'];
  $totLine[] = (int)$totals['ad'];
  fputcsv($out, []);
  fputcsv($out, $totLine);

  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">Bookings — Status & Volume</h1>
    <div class="text-muted">Counts by status over time. Range: <?= e($from) ?> → <?= e($to) ?>.</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-6 col-md-2">
        <label class="form-label">From</label>
        <input type="date" class="form-control" name="from" value="<?= e($from) ?>">
      </div>
      <div class="col-sm-6 col-md-2">
        <label class="form-label">To</label>
        <input type="date" class="form-control" name="to" value="<?= e($to) ?>">
      </div>
      <div class="col-sm-6 col-md-2">
        <label class="form-label">Group By</label>
        <select class="form-select" name="group">
          <option value="day"   <?= $group==='day'  ?'selected':'' ?>>Day</option>
          <option value="week"  <?= $group==='week' ?'selected':'' ?>>Week</option>
          <option value="month" <?= $group==='month'?'selected':'' ?>>Month</option>
        </select>
      </div>

      <div class="col-sm-6 col-md-3">
        <label class="form-label">Partner</label>
        <select class="form-select" name="partner_id">
          <option value="0">— Any —</option>
          <?php foreach($partners as $p): ?>
            <option value="<?= (int)$p['id'] ?>" <?= $partnerId===(int)$p['id']?'selected':'' ?>><?= e($p['name'] ?? ('#'.$p['id'])) ?></option>
          <?php endforeach; ?>
        </select>
      </div>

      <div class="col-sm-6 col-md-3">
        <label class="form-label">Corporate</label>
        <select class="form-select" name="corporate_id">
          <option value="0">— Any —</option>
          <?php foreach($corporates as $c): ?>
            <option value="<?= (int)$c['id'] ?>" <?= $corpId===(int)$c['id']?'selected':'' ?>><?= e($c['name'] ?? ('#'.$c['id'])) ?></option>
          <?php endforeach; ?>
        </select>
      </div>

      <div class="col-12">
        <label class="form-label">Search</label>
        <input class="form-control" name="q" placeholder="Ref / Client / Pickup / Dropoff" value="<?= e($q) ?>">
      </div>

      <div class="col-12 d-grid d-md-block">
        <button class="btn btn-primary mt-2">Apply Filters</button>
      </div>
    </form>
  </div>
</div>

<!-- KPI Cards -->
<?php
$k_completed = (int)($totals['s_completed'] ?? 0);
$k_invoiced  = (int)($totals['s_invoiced'] ?? 0);
$k_paid      = (int)($totals['s_paid'] ?? 0);
?>
<div class="row g-3 mb-3">
  <div class="col-12 col-md-3">
    <div class="card shadow-sm h-100 border-primary">
      <div class="card-body">
        <div class="small text-muted">Total Bookings</div>
        <div class="display-6"><?= number_format((int)$totals['total']) ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-md-3">
    <div class="card shadow-sm h-100 border-success">
      <div class="card-body">
        <div class="small text-muted">Completed</div>
        <div class="display-6"><?= number_format($k_completed) ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-md-3">
    <div class="card shadow-sm h-100 border-warning">
      <div class="card-body">
        <div class="small text-muted">Invoiced</div>
        <div class="display-6"><?= number_format($k_invoiced) ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-md-3">
    <div class="card shadow-sm h-100 border-dark">
      <div class="card-body">
        <div class="small text-muted">Paid</div>
        <div class="display-6"><?= number_format($k_paid) ?></div>
      </div>
    </div>
  </div>
</div>

<!-- Charts -->
<div class="row g-3 mb-3">
  <div class="col-12 col-lg-8">
    <div class="card shadow-sm h-100">
      <div class="card-body">
        <div class="fw-semibold mb-2">Status Trend</div>
        <canvas id="statusChart" height="150"></canvas>
      </div>
    </div>
  </div>
  <div class="col-12 col-lg-4">
    <div class="card shadow-sm h-100">
      <div class="card-body">
        <div class="fw-semibold mb-2">Job Type Split</div>
        <canvas id="typeChart" height="150"></canvas>
        <div class="mt-2 small text-muted">
          Transfer: <strong><?= number_format((int)$totals['transfer']) ?></strong> ·
          As Directed: <strong><?= number_format((int)$totals['ad']) ?></strong>
        </div>
      </div>
    </div>
  </div>
</div>

<!-- Table -->
<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">Breakdown by <?= e(ucfirst($group)) ?></div>
      <div class="small text-muted"><?= e($from) ?> → <?= e($to) ?></div>
    </div>

    <div class="table-responsive">
      <table class="table table-sm align-middle mb-0">
        <thead class="table-light">
          <tr>
            <th><?= e(ucfirst($group)) ?></th>
            <th class="text-end">Total</th>
            <?php foreach ($statuses as $st): ?>
              <th class="text-end"><?= e($st) ?></th>
            <?php endforeach; ?>
            <th class="text-end">Transfer</th>
            <th class="text-end">As Directed</th>
          </tr>
        </thead>
        <tbody>
          <?php if ($rows): foreach ($rows as $r): ?>
            <tr>
              <td><?= e($r['bucket_label']) ?></td>
              <td class="text-end"><?= (int)$r['total'] ?></td>
              <?php foreach ($statuses as $st): $k='s_'.strtolower($st); ?>
                <td class="text-end"><?= (int)$r[$k] ?></td>
              <?php endforeach; ?>
              <td class="text-end"><?= (int)$r['t_transfer'] ?></td>
              <td class="text-end"><?= (int)$r['t_ad'] ?></td>
            </tr>
          <?php endforeach; else: ?>
            <tr><td colspan="<?= 3 + count($statuses) ?>" class="text-center text-muted py-4">No results.</td></tr>
          <?php endif; ?>
        </tbody>
        <?php if ($rows): ?>
        <tfoot class="table-light">
          <tr>
            <th class="text-end">Totals</th>
            <th class="text-end"><?= number_format((int)$totals['total']) ?></th>
            <?php foreach ($statuses as $st): $k='s_'.strtolower($st); ?>
              <th class="text-end"><?= number_format((int)$totals[$k]) ?></th>
            <?php endforeach; ?>
            <th class="text-end"><?= number_format((int)$totals['transfer']) ?></th>
            <th class="text-end"><?= number_format((int)$totals['ad']) ?></th>
          </tr>
        </tfoot>
        <?php endif; ?>
      </table>
    </div>
  </div>
</div>

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

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
(function(){
  // Status Trend (multi-line)
  const el = document.getElementById('statusChart'); if (el && typeof Chart !== 'undefined') {
    const labels = <?= json_encode($labels) ?>;
    const datasets = [];
    const series = <?= json_encode($series) ?>;

    // Keep a sensible order for lines
    const order = ['s_new','s_confirmed','s_assigned','s_inprogress','s_completed','s_invoiced','s_paid'];

    order.forEach((key) => {
      if (!series[key]) return;
      const labelMap = {
        s_new:'New', s_confirmed:'Confirmed', s_assigned:'Assigned', s_inprogress:'InProgress',
        s_completed:'Completed', s_invoiced:'Invoiced', s_paid:'Paid'
      };
      datasets.push({ label: labelMap[key] || key, data: series[key], fill: false, tension: 0.2 });
    });

    new Chart(el, {
      type: 'line',
      data: { labels, datasets },
      options: {
        responsive: true,
        plugins: { legend: { position: 'top' } },
        scales: { y: { beginAtZero: true, ticks: { precision: 0 } } }
      }
    });
  }

  // Job Type Split (doughnut)
  const et = document.getElementById('typeChart');
  if (et && typeof Chart !== 'undefined') {
    new Chart(et, {
      type: 'doughnut',
      data: {
        labels: ['Transfer','As Directed'],
        datasets: [{ data: [<?= (int)$totals['transfer'] ?>, <?= (int)$totals['ad'] ?>] }]
      },
      options: { responsive: true, plugins: { legend: { position: 'bottom' } } }
    });
  }
})();
</script>
