<?php
declare(strict_types=1);

/**
 * modules/reports/job_mix.php
 *
 * Job Mix & Peak Times
 *
 * Filters:
 *  - Date range (pickup_date)
 *  - Partner (optional)
 *  - Corporate (optional)
 *  - Search (ref / client / addresses)
 *
 * Output:
 *  - KPI cards (Totals + % split)
 *  - Doughnut chart (Transfer vs As Directed)
 *  - Bar chart (Pickups by Hour)
 *  - Bar chart (Pickups by Weekday)
 *  - Table: job-type breakdown (count, revenue, avg ticket)
 *  - 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);
$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;

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

/* -----------------------------
   Dropdown data
------------------------------*/
$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) {}

/* -----------------------------
   WHERE base
------------------------------*/
$where = ["b.company_id = :cid", "b.pickup_date BETWEEN :from AND :to"];
$args  = [':cid'=>$cid, ':from'=>$from, ':to'=>$to];

if ($partnerId > 0) { $where[] = "b.partner_id = :pid"; $args[':pid'] = $partnerId; }
if ($corpId > 0)    { $where[] = "b.corporate_id = :cid2"; $args[':cid2'] = $corpId; }
if ($q !== '') {
  $where[] = "(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 ', $where);

/* -----------------------------
   Queries
------------------------------*/

// 1) Job type breakdown
$sqlType = "
  SELECT
    b.booking_type,
    COUNT(*) AS cnt,
    COALESCE(SUM(b.total_client_price),0) AS revenue
  FROM bookings b
  WHERE {$whereSQL}
  GROUP BY b.booking_type
";
$typeRows = [];
try { $st = db()->prepare($sqlType); $st->execute($args); $typeRows = $st->fetchAll() ?: []; }
catch (Throwable $e) { $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? $e->getMessage() : 'Unable to load job mix.'; }

// 2) Pickups by HOUR (0..23) — ignore NULL time
$sqlHour = "
  SELECT
    HOUR(b.pickup_time) AS hr,
    COUNT(*) AS cnt
  FROM bookings b
  WHERE {$whereSQL} AND b.pickup_time IS NOT NULL
  GROUP BY HOUR(b.pickup_time)
  ORDER BY hr ASC
";
$hourRows = [];
try { $sh = db()->prepare($sqlHour); $sh->execute($args); $hourRows = $sh->fetchAll() ?: []; }
catch (Throwable $e) { $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? $e->getMessage() : 'Unable to load pickups by hour.'; }

// 3) Pickups by WEEKDAY (Mon..Sun)
$sqlWd = "
  SELECT
    WEEKDAY(b.pickup_date) AS w, /* 0=Mon..6=Sun */
    COUNT(*) AS cnt
  FROM bookings b
  WHERE {$whereSQL} AND b.pickup_date IS NOT NULL
  GROUP BY WEEKDAY(b.pickup_date)
  ORDER BY w ASC
";
$wdRows = [];
try { $sw = db()->prepare($sqlWd); $sw->execute($args); $wdRows = $sw->fetchAll() ?: []; }
catch (Throwable $e) { $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? $e->getMessage() : 'Unable to load pickups by weekday.'; }

/* -----------------------------
   Transform & KPIs
------------------------------*/
$byType = [
  'Transfer'    => ['cnt'=>0,'revenue'=>0.0],
  'As Directed' => ['cnt'=>0,'revenue'=>0.0],
];
$totalCnt = 0; $totalRev = 0.0;
foreach ($typeRows as $r) {
  $t = (string)($r['booking_type'] ?? '');
  $cnt = (int)$r['cnt'];
  $rev = (float)$r['revenue'];
  if (!isset($byType[$t])) $byType[$t] = ['cnt'=>0,'revenue'=>0.0];
  $byType[$t]['cnt'] += $cnt;
  $byType[$t]['revenue'] += $rev;
  $totalCnt += $cnt;
  $totalRev += $rev;
}

// Percent split
$pcTransfer = $totalCnt > 0 ? round(100 * $byType['Transfer']['cnt'] / $totalCnt, 1) : 0;
$pcAD       = $totalCnt > 0 ? round(100 * $byType['As Directed']['cnt'] / $totalCnt, 1) : 0;

// Hour buckets 0..23
$hourCounts = array_fill(0, 24, 0);
foreach ($hourRows as $r) {
  $h = is_null($r['hr']) ? null : (int)$r['hr'];
  if ($h !== null && $h >= 0 && $h <= 23) $hourCounts[$h] = (int)$r['cnt'];
}

// Weekday buckets Mon..Sun
$wdCounts = array_fill(0, 7, 0);
foreach ($wdRows as $r) {
  $w = is_null($r['w']) ? null : (int)$r['w'];
  if ($w !== null && $w >= 0 && $w <= 6) $wdCounts[$w] = (int)$r['cnt'];
}
$wdLabels = ['Mon','Tue','Wed','Thu','Fri','Sat','Sun'];

/* -----------------------------
   CSV Export
------------------------------*/
if ($export === 'csv') {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="job_mix_'.date('Ymd_His').'.csv"');
  $out = fopen('php://output', 'w');

  // Meta
  fputcsv($out, ['From',$from,'To',$to,'Partner',$partnerId,'Corporate',$corpId,'Search',$q]);
  fputcsv($out, []);

  // Section 1: Job Type
  fputcsv($out, ['Job Type','Count','Revenue','Avg Ticket']);
  foreach ($byType as $k=>$v) {
    $avg = $v['cnt']>0 ? $v['revenue']/$v['cnt'] : 0;
    fputcsv($out, [$k, (int)$v['cnt'], number_format($v['revenue'],2,'.',''), number_format($avg,2,'.','')]);
  }
  fputcsv($out, ['TOTAL', (int)$totalCnt, number_format($totalRev,2,'.',''), $totalCnt>0?number_format($totalRev/$totalCnt,2,'.',''):'0.00']);
  fputcsv($out, []);

  // Section 2: Pickups by Hour
  fputcsv($out, ['Hour','Count']);
  for ($h=0;$h<=23;$h++) fputcsv($out, [sprintf('%02d:00',$h), (int)$hourCounts[$h]]);
  fputcsv($out, []);

  // Section 3: Pickups by Weekday
  fputcsv($out, ['Weekday','Count']);
  for ($i=0;$i<7;$i++) fputcsv($out, [$wdLabels[$i], (int)$wdCounts[$i]]);

  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">Job Mix & Peak Times</h1>
    <div class="text-muted">Distribution of jobs and pickup peaks. 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-3">
        <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-3">
        <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-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
$avgTicket = $totalCnt>0 ? $totalRev/$totalCnt : 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($totalCnt) ?></div>
      </div>
    </div>
  </div>
  <div class="col-6 col-md-3">
    <div class="card shadow-sm h-100 border-success">
      <div class="card-body">
        <div class="small text-muted">Transfer</div>
        <div class="display-6"><?= number_format((int)$byType['Transfer']['cnt']) ?></div>
        <div class="text-muted"><?= $pcTransfer ?>%</div>
      </div>
    </div>
  </div>
  <div class="col-6 col-md-3">
    <div class="card shadow-sm h-100 border-warning">
      <div class="card-body">
        <div class="small text-muted">As Directed</div>
        <div class="display-6"><?= number_format((int)$byType['As Directed']['cnt']) ?></div>
        <div class="text-muted"><?= $pcAD ?>%</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">Avg Ticket (Client £)</div>
        <div class="display-6">£<?= number_format($avgTicket, 2) ?></div>
      </div>
    </div>
  </div>
</div>

<!-- Charts -->
<div class="row g-3 mb-3">
  <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 Mix</div>
        <canvas id="mixChart" height="160"></canvas>
      </div>
    </div>
  </div>
  <div class="col-12 col-lg-8">
    <div class="card shadow-sm h-100">
      <div class="card-body">
        <div class="fw-semibold mb-2">Pickups by Hour</div>
        <canvas id="hourChart" height="160"></canvas>
      </div>
    </div>
  </div>
</div>

<div class="row g-3 mb-3">
  <div class="col-12">
    <div class="card shadow-sm h-100">
      <div class="card-body">
        <div class="fw-semibold mb-2">Pickups by Weekday</div>
        <canvas id="wdChart" height="120"></canvas>
      </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">Job Type Breakdown</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>Job Type</th>
            <th class="text-end">Count</th>
            <th class="text-end">Revenue (Client £)</th>
            <th class="text-end">Avg Ticket (Client £)</th>
            <th class="text-end">% of Jobs</th>
          </tr>
        </thead>
        <tbody>
          <?php
            $typesOrder = ['Transfer','As Directed'];
            $haveRows = false;
            foreach ($typesOrder as $t):
              $cnt = (int)($byType[$t]['cnt'] ?? 0);
              $rev = (float)($byType[$t]['revenue'] ?? 0.0);
              if ($cnt>0) $haveRows = true;
              $avg = $cnt>0 ? $rev/$cnt : 0;
              $pc  = $totalCnt>0 ? (100*$cnt/$totalCnt) : 0;
          ?>
            <tr>
              <td><?= e($t) ?></td>
              <td class="text-end"><?= number_format($cnt) ?></td>
              <td class="text-end">£<?= number_format($rev, 2) ?></td>
              <td class="text-end">£<?= number_format($avg, 2) ?></td>
              <td class="text-end"><?= number_format($pc, 1) ?>%</td>
            </tr>
          <?php endforeach; ?>
          <?php if (!$haveRows): ?>
            <tr><td colspan="5" class="text-center text-muted py-4">No results.</td></tr>
          <?php endif; ?>
        </tbody>
        <?php if ($totalCnt>0): ?>
        <tfoot class="table-light">
          <tr>
            <th>Total</th>
            <th class="text-end"><?= number_format($totalCnt) ?></th>
            <th class="text-end">£<?= number_format($totalRev, 2) ?></th>
            <th class="text-end">£<?= number_format($avgTicket, 2) ?></th>
            <th class="text-end">100.0%</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(){
  // Doughnut: Job Mix
  const mixEl = document.getElementById('mixChart');
  if (mixEl && typeof Chart !== 'undefined') {
    new Chart(mixEl, {
      type: 'doughnut',
      data: {
        labels: ['Transfer','As Directed'],
        datasets: [{ data: [<?= (int)$byType['Transfer']['cnt'] ?>, <?= (int)$byType['As Directed']['cnt'] ?>] }]
      },
      options: { responsive: true, plugins: { legend: { position: 'bottom' } } }
    });
  }

  // Bar: Pickups by Hour
  const hourEl = document.getElementById('hourChart');
  if (hourEl && typeof Chart !== 'undefined') {
    const hLabels = Array.from({length:24}, (_,i)=> (i.toString().padStart(2,'0')+':00'));
    const hData   = <?= json_encode(array_values($hourCounts)) ?>;
    new Chart(hourEl, {
      type: 'bar',
      data: { labels: hLabels, datasets: [{ label: 'Pickups', data: hData }] },
      options: {
        responsive: true,
        plugins: { legend: { display:false } },
        scales: { y: { beginAtZero:true, ticks:{ precision:0 } } }
      }
    });
  }

  // Bar: Pickups by Weekday
  const wdEl = document.getElementById('wdChart');
  if (wdEl && typeof Chart !== 'undefined') {
    const wdLabels = <?= json_encode($wdLabels) ?>;
    const wdData   = <?= json_encode(array_values($wdCounts)) ?>;
    new Chart(wdEl, {
      type: 'bar',
      data: { labels: wdLabels, datasets: [{ label: 'Pickups', data: wdData }] },
      options: {
        responsive: true,
        plugins: { legend: { display:false } },
        scales: { y: { beginAtZero:true, ticks:{ precision:0 } } }
      }
    });
  }
})();
</script>
