<?php
declare(strict_types=1);

/**
 * modules/reports/driver_leaderboard.php
 *
 * Driver Leaderboard — who covered the most bookings and at what cost.
 *
 * Filters:
 *  - Date range (pickup_date)
 *  - Status (default Completed)
 *  - Min jobs threshold
 *  - Search driver name/phone/email
 *
 * Outputs:
 *  - KPI cards (drivers, jobs, due total, avg cost/job)
 *  - Bar chart: Top 10 by Jobs
 *  - Bar chart: Top 10 by Total Due
 *  - Table: per driver (jobs, base, parking, waiting, due, paid, outstanding, avg/job)
 *  - CSV export
 *  - Quick “Bank” popup for driver bank details
 */

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; }
}
function decode_bank(?string $json): array {
  $defaults = ['bank_name'=>'','account_name'=>'','account_number'=>'','sort_code'=>'','iban'=>'','swift'=>'','notes'=>''];
  if (!$json) return $defaults;
  $arr = json_decode($json, true);
  return is_array($arr) ? array_merge($defaults, array_intersect_key($arr, $defaults)) : $defaults;
}
/** Build a safe SQL expression to get the *primary* driver for a booking. */
function primary_driver_expr(): string {
  // Prefer booking_vehicles (sequence_order ASC), fallback to bookings.driver_id if exists
  static $hasBV = null, $hasDriverIdCol = null;
  if ($hasBV === null) {
    $hasBV = tbl_exists('booking_vehicles');
  }
  if ($hasDriverIdCol === null) {
    $hasDriverIdCol = false;
    try {
      $rs = db()->query("SHOW COLUMNS FROM bookings LIKE 'driver_id'");
      $hasDriverIdCol = (bool)$rs->fetch();
    } catch (Throwable) {}
  }

  if ($hasBV) {
    return "(SELECT bv.driver_id FROM booking_vehicles bv
              WHERE bv.booking_id = b.id
              ORDER BY bv.sequence_order ASC, bv.id ASC
              LIMIT 1)";
  }
  if ($hasDriverIdCol) {
    return "b.driver_id";
  }
  return "NULL";
}

/* --------------------------------
   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);
$status   = (string)($_GET['status'] ?? 'Completed'); // Completed|All
$minJobs  = max(0, (int)($_GET['min_jobs'] ?? 0));
$q        = trim((string)($_GET['q'] ?? ''));
$sortBy   = (string)($_GET['sort'] ?? 'jobs'); // jobs|due|avg|paid|out|parking|waiting|name
$dir      = strtolower((string)($_GET['dir'] ?? 'desc')) === 'asc' ? 'asc' : 'desc';
$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;

/* --------------------------------
   Data query
---------------------------------*/
$hasDrivers   = tbl_exists('drivers');
$hasBookings  = tbl_exists('bookings');
$hasPayouts   = tbl_exists('driver_payouts');

if (!$hasDrivers || !$hasBookings) {
  $errors[] = 'Missing required tables (drivers / bookings).';
}

$rows = [];
$totalDrivers = 0; $totalJobs = 0; $sumDue = 0.0;

if (!$errors) {
  try {
    $drvExpr = primary_driver_expr();
    if ($drvExpr === "NULL") {
      throw new RuntimeException('No way to determine booking → driver (booking_vehicles and bookings.driver_id both missing).');
    }

    // Base WHERE on bookings
    $where = ["b.company_id = :cid", "b.pickup_date BETWEEN :from AND :to"];
    $args  = [':cid'=>$cid, ':from'=>$from, ':to'=>$to];

    if ($status === 'Completed') $where[] = "b.status = 'Completed'";

    // Pre-aggregate per booking: driver_id, base/park/wait, due, paid
    $paidSQL = $hasPayouts
      ? "(SELECT COALESCE(SUM(dp.amount),0) FROM driver_payouts dp
           WHERE dp.company_id = b.company_id AND dp.booking_id = b.id)"
      : "0";

    $sub = "
      SELECT
        {$drvExpr} AS driver_id,
        COALESCE(b.total_driver_price,0) AS base,
        COALESCE(b.driver_parking_fee,0) AS park,
        COALESCE(b.driver_waiting_fee,0) AS wait,
        (COALESCE(b.total_driver_price,0) + COALESCE(b.driver_parking_fee,0) + COALESCE(b.driver_waiting_fee,0)) AS due,
        {$paidSQL} AS paid
      FROM bookings b
      WHERE ".implode(' AND ', $where)."
    ";

    // Wrap and group by driver
    $sql = "
      SELECT
        d.id AS driver_id,
        d.name,
        d.phone,
        d.email,
        COUNT(*) AS jobs,
        COALESCE(SUM(t.base),0) AS base_sum,
        COALESCE(SUM(t.park),0) AS park_sum,
        COALESCE(SUM(t.wait),0) AS wait_sum,
        COALESCE(SUM(t.due),0)  AS due_sum,
        COALESCE(SUM(t.paid),0) AS paid_sum
      FROM ( {$sub} ) t
      JOIN drivers d ON d.id = t.driver_id
      WHERE t.driver_id IS NOT NULL
      GROUP BY d.id, d.name, d.phone, d.email
    ";

    // Execute
    $stmt = db()->prepare($sql);
    $stmt->execute($args);
    $rows = $stmt->fetchAll() ?: [];

    // Search + Min jobs filter in PHP (simple)
    if ($q !== '' || $minJobs > 0) {
      $rows = array_values(array_filter($rows, function($r) use ($q,$minJobs) {
        if ($minJobs > 0 && (int)$r['jobs'] < $minJobs) return false;
        if ($q === '') return true;
        $hay = strtolower(trim(($r['name'] ?? '').' '.($r['phone'] ?? '').' '.($r['email'] ?? '')));
        return str_contains($hay, strtolower($q));
      }));
    }

    // Enrich: outstanding, avg/job
    foreach ($rows as &$r) {
      $due  = (float)$r['due_sum'];
      $paid = (float)$r['paid_sum'];
      $r['out_sum'] = max(0.0, round($due - $paid, 2));
      $r['avg_cost'] = ((int)$r['jobs'] > 0) ? ($due / (int)$r['jobs']) : 0.0;
    }
    unset($r);

    // Sort
    $key = match($sortBy) {
      'name'    => 'name',
      'due'     => 'due_sum',
      'avg'     => 'avg_cost',
      'paid'    => 'paid_sum',
      'out'     => 'out_sum',
      'parking' => 'park_sum',
      'waiting' => 'wait_sum',
      default   => 'jobs',
    };
    usort($rows, function($a,$b) use($key,$dir){
      $av = $a[$key] ?? 0; $bv = $b[$key] ?? 0;
      if (is_string($av) || is_string($bv)) {
        $cmp = strcasecmp((string)$av, (string)$bv);
      } else {
        $cmp = $av <=> $bv;
      }
      return $dir === 'asc' ? $cmp : -$cmp;
    });

    // Totals
    $totalDrivers = count($rows);
    foreach ($rows as $r) {
      $totalJobs += (int)$r['jobs'];
      $sumDue    += (float)$r['due_sum'];
    }

  } catch (Throwable $e) {
    $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? $e->getMessage() : 'Unable to load driver leaderboard.';
  }
}

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

  fputcsv($out, ['From',$from,'To',$to,'Status',$status,'Min Jobs',$minJobs,'Sort',$sortBy,'Dir',$dir,'Search',$q]);
  fputcsv($out, []);
  fputcsv($out, ['Driver','Jobs','Base £','Parking £','Waiting £','Due £','Paid £','Outstanding £','Avg/Job £','Phone','Email']);

  foreach ($rows as $r) {
    fputcsv($out, [
      (string)($r['name'] ?? '—'),
      (int)$r['jobs'],
      number_format((float)$r['base_sum'],2,'.',''),
      number_format((float)$r['park_sum'],2,'.',''),
      number_format((float)$r['wait_sum'],2,'.',''),
      number_format((float)$r['due_sum'],2,'.',''),
      number_format((float)$r['paid_sum'],2,'.',''),
      number_format((float)$r['out_sum'],2,'.',''),
      number_format((float)$r['avg_cost'],2,'.',''),
      (string)($r['phone'] ?? ''),
      (string)($r['email'] ?? ''),
    ]);
  }
  fclose($out);
  exit;
}

/* --------------------------------
   Charts data (top 10)
---------------------------------*/
$topJobs = array_slice($rows, 0, 10);
$sortedByDue = $rows;
usort($sortedByDue, fn($a,$b) => ($b['due_sum'] <=> $a['due_sum']));
$topDue = array_slice($sortedByDue, 0, 10);

/* --------------------------------
   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">Driver Leaderboard</h1>
    <div class="text-muted">Who covered the most bookings and at what cost • 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-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-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-6 col-md-2">
        <label class="form-label">Status</label>
        <select class="form-select" name="status">
          <option value="Completed" <?= $status==='Completed'?'selected':'' ?>>Completed</option>
          <option value="All" <?= $status!=='Completed'?'selected':'' ?>>All</option>
        </select>
      </div>
      <div class="col-6 col-md-2">
        <label class="form-label">Min Jobs</label>
        <input type="number" min="0" class="form-control" name="min_jobs" value="<?= (int)$minJobs ?>">
      </div>
      <div class="col-12 col-md-2">
        <label class="form-label">Search Driver</label>
        <input class="form-control" name="q" placeholder="Name / Phone / Email" value="<?= e($q) ?>">
      </div>

      <div class="col-6 col-md-3">
        <label class="form-label">Sort By</label>
        <select class="form-select" name="sort">
          <option value="jobs"    <?= $sortBy==='jobs'?'selected':'' ?>>Jobs</option>
          <option value="due"     <?= $sortBy==='due'?'selected':'' ?>>Total Due £</option>
          <option value="avg"     <?= $sortBy==='avg'?'selected':'' ?>>Avg Cost / Job £</option>
          <option value="paid"    <?= $sortBy==='paid'?'selected':'' ?>>Paid £</option>
          <option value="out"     <?= $sortBy==='out'?'selected':'' ?>>Outstanding £</option>
          <option value="parking" <?= $sortBy==='parking'?'selected':'' ?>>Parking £</option>
          <option value="waiting" <?= $sortBy==='waiting'?'selected':'' ?>>Waiting £</option>
          <option value="name"    <?= $sortBy==='name'?'selected':'' ?>>Driver Name</option>
        </select>
      </div>
      <div class="col-6 col-md-2">
        <label class="form-label">Direction</label>
        <select class="form-select" name="dir">
          <option value="desc" <?= $dir==='desc'?'selected':'' ?>>Desc</option>
          <option value="asc"  <?= $dir==='asc'?'selected':''  ?>>Asc</option>
        </select>
      </div>

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

<?php
$avgCost = $totalJobs>0 ? ($sumDue/$totalJobs) : 0.0;
?>
<!-- KPI Cards -->
<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">Drivers (with jobs)</div>
        <div class="display-6"><?= number_format($totalDrivers) ?></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">Total Jobs</div>
        <div class="display-6"><?= number_format($totalJobs) ?></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">Total Driver Due</div>
        <div class="display-6">£<?= number_format($sumDue, 2) ?></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">Avg Cost / Job</div>
        <div class="display-6">£<?= number_format($avgCost, 2) ?></div>
      </div>
    </div>
  </div>
</div>

<!-- Charts -->
<div class="row g-3 mb-3">
  <div class="col-12 col-lg-6">
    <div class="card shadow-sm h-100">
      <div class="card-body">
        <div class="fw-semibold mb-2">Top 10 Drivers — Jobs</div>
        <canvas id="jobsChart" height="160"></canvas>
      </div>
    </div>
  </div>
  <div class="col-12 col-lg-6">
    <div class="card shadow-sm h-100">
      <div class="card-body">
        <div class="fw-semibold mb-2">Top 10 Drivers — Total Due (£)</div>
        <canvas id="dueChart" height="160"></canvas>
      </div>
    </div>
  </div>
</div>

<!-- Table -->
<div class="card shadow-sm">
  <div class="table-responsive">
    <table class="table align-middle mb-0">
      <thead class="table-light">
        <tr>
          <th>Driver</th>
          <th class="text-end">Jobs</th>
          <th class="text-end">£ Base</th>
          <th class="text-end">£ Parking</th>
          <th class="text-end">£ Waiting</th>
          <th class="text-end">£ Due</th>
          <th class="text-end">£ Paid</th>
          <th class="text-end">£ Outstanding</th>
          <th class="text-end">£ Avg / Job</th>
          <th class="text-end">Actions</th>
        </tr>
      </thead>
      <tbody>
        <?php if ($rows): foreach ($rows as $r): ?>
          <tr>
            <td>
              <div class="fw-semibold"><?= e($r['name'] ?? '—') ?></div>
              <div class="small text-muted">
                <?= e($r['email'] ?? '') ?><?= (!empty($r['email']) && !empty($r['phone'])) ? ' · ' : '' ?><?= e($r['phone'] ?? '') ?>
              </div>
            </td>
            <td class="text-end"><?= number_format((int)$r['jobs']) ?></td>
            <td class="text-end">£<?= number_format((float)$r['base_sum'],2) ?></td>
            <td class="text-end">£<?= number_format((float)$r['park_sum'],2) ?></td>
            <td class="text-end">£<?= number_format((float)$r['wait_sum'],2) ?></td>
            <td class="text-end">£<?= number_format((float)$r['due_sum'],2) ?></td>
            <td class="text-end">£<?= number_format((float)$r['paid_sum'],2) ?></td>
            <td class="text-end">£<?= number_format((float)$r['out_sum'],2) ?></td>
            <td class="text-end">£<?= number_format((float)$r['avg_cost'],2) ?></td>
            <td class="text-end">
              <div class="btn-group btn-group-sm">
                <a class="btn btn-outline-secondary"
                   href="<?= e(url_modules('drivers/view.php').'?id='.(int)$r['driver_id']) ?>"
                   target="_blank" rel="noopener">Profile</a>
                <button type="button" class="btn btn-outline-secondary js-bank" data-id="<?= (int)$r['driver_id'] ?>">Bank</button>
              </div>
            </td>
          </tr>
        <?php endforeach; else: ?>
          <tr><td colspan="10" class="text-center text-muted py-4">No results.</td></tr>
        <?php endif; ?>
      </tbody>
    </table>
  </div>
</div>

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

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
(function(){
  const jobsLabels = <?= json_encode(array_map(fn($r)=>$r['name'] ?? '—', $topJobs)) ?>;
  const jobsData   = <?= json_encode(array_map(fn($r)=>(int)$r['jobs'], $topJobs)) ?>;

  const dueLabels  = <?= json_encode(array_map(fn($r)=>$r['name'] ?? '—', $topDue)) ?>;
  const dueData    = <?= json_encode(array_map(fn($r)=>(float)$r['due_sum'], $topDue)) ?>;

  const el1 = document.getElementById('jobsChart');
  if (el1 && typeof Chart !== 'undefined') {
    new Chart(el1, {
      type: 'bar',
      data: { labels: jobsLabels, datasets: [{ label: 'Jobs', data: jobsData }] },
      options: { responsive:true, plugins:{ legend:{ display:false } }, scales:{ y:{ beginAtZero:true, ticks:{ precision:0 } } } }
    });
  }

  const el2 = document.getElementById('dueChart');
  if (el2 && typeof Chart !== 'undefined') {
    new Chart(el2, {
      type: 'bar',
      data: { labels: dueLabels, datasets: [{ label: 'Total Due (£)', data: dueData }] },
      options: { responsive:true, plugins:{ legend:{ display:false } }, scales:{ y:{ beginAtZero:true } } }
    });
  }

  // Bank modal loader (reuses /drivers bank details stored as JSON)
  let bankModal, bankBody;
  document.addEventListener('click', async function(e){
    const btn = e.target.closest('.js-bank'); if(!btn) return;
    if (!bankModal) {
      // Build a simple bootstrap modal once
      const wrap = document.createElement('div');
      wrap.innerHTML = `
        <div class="modal fade" id="bankModal" tabindex="-1" aria-hidden="true">
          <div class="modal-dialog modal-md">
            <div class="modal-content">
              <div class="modal-header">
                <h5 class="modal-title">Driver Bank Details</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
              </div>
              <div class="modal-body" id="bankBody">Loading…</div>
            </div>
          </div>
        </div>`;
      document.body.appendChild(wrap);
      bankModal = new bootstrap.Modal(document.getElementById('bankModal'));
      bankBody  = document.getElementById('bankBody');
    }
    bankBody.innerHTML = '<div class="text-muted">Loading…</div>';
    try{
      const url = 'driver_leaderboard.php?fetch_bank=1&driver_id=' + encodeURIComponent(btn.dataset.id)
                + '&from=<?= e($from) ?>&to=<?= e($to) ?>';
      const res = await fetch(url, {headers:{'X-Requested-With':'XMLHttpRequest'}});
      if(!res.ok) throw new Error('HTTP '+res.status);
      bankBody.innerHTML = await res.text();
    }catch(err){
      bankBody.innerHTML = '<div class="text-danger">Unable to load bank details.</div>';
      console.error(err);
    }
    bankModal.show();
  });
})();
</script>

<?php
/* ---------------------------------------------------------
   AJAX micro-endpoint: bank details for a driver
----------------------------------------------------------*/
if (($_GET['fetch_bank'] ?? '') === '1' && isset($_GET['driver_id'])) {
  header('Content-Type: text/html; charset=utf-8');
  try {
    $did = (int)$_GET['driver_id'];
    $q = db()->prepare("SELECT name, bank_details, email, phone FROM drivers WHERE id=:id AND company_id=:cid LIMIT 1");
    $q->execute([':id'=>$did, ':cid'=>$cid]);
    if ($d = $q->fetch()) {
      $b = decode_bank((string)($d['bank_details'] ?? ''));
      ?>
      <div class="mb-2">
        <div class="fw-semibold"><?= e($d['name'] ?? '') ?></div>
        <div class="small text-muted"><?= e($d['email'] ?? '') ?><?= (!empty($d['email']) && !empty($d['phone'])) ? ' · ' : '' ?><?= e($d['phone'] ?? '') ?></div>
      </div>
      <table class="table table-sm align-middle mb-0">
        <tbody>
          <tr><th style="width:40%;">Bank</th><td><?= e($b['bank_name'] ?: '—') ?></td></tr>
          <tr><th>Account Holder</th><td><?= e($b['account_name'] ?: '—') ?></td></tr>
          <tr><th>Account Number</th><td><?= e($b['account_number'] ?: '—') ?></td></tr>
          <tr><th>Sort Code</th><td><?= e($b['sort_code'] ?: '—') ?></td></tr>
          <?php if (!empty($b['iban'])): ?><tr><th>IBAN</th><td><?= e($b['iban']) ?></td></tr><?php endif; ?>
          <?php if (!empty($b['swift'])): ?><tr><th>SWIFT</th><td><?= e($b['swift']) ?></td></tr><?php endif; ?>
          <?php if (!empty($b['notes'])): ?><tr><th>Notes</th><td><?= nl2br(e($b['notes'])) ?></td></tr><?php endif; ?>
        </tbody>
      </table>
      <?php
    } else {
      echo '<div class="text-muted">Not found.</div>';
    }
  } catch (Throwable $e) {
    http_response_code(500);
    echo (defined('APP_ENV') && APP_ENV==='dev') ? e($e->getMessage()) : 'Error';
  }
  exit;
}
