<?php
declare(strict_types=1);

/**
 * modules/reports/driver_utilization.php
 *
 * Driver Utilization & Earnings
 *
 * Filters:
 *  - Date range (bookings.pickup_date)
 *  - Search by driver name (if drivers table exists)
 *  - Minimum jobs threshold
 *  - Include driver fees (parking + waiting) in due
 *
 * Definitions (Completed bookings only):
 *  driver_due = total_driver_price [+ driver_parking_fee + driver_waiting_fee if toggled]
 *  paid_sum   = SUM(driver_payouts.amount) per booking (regardless of payout date)
 *  outstanding = driver_due - paid_sum
 *
 * Output:
 *  - KPI cards (Drivers, Jobs, £ Due, £ Outstanding)
 *  - Table (driver, jobs, due, paid, outstanding, avg/job)
 *  - Top-10 drivers by Jobs (bar chart)
 *  - 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);
$q           = trim((string)($_GET['q'] ?? ''));     // driver name
$minJobs     = (int)($_GET['min_jobs'] ?? 0);
$includeFees = (int)($_GET['include_fees'] ?? 1) === 1 ? 1 : 0; // default ON
$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;

$hasDrivers = tbl_exists('drivers');
$hasBV      = tbl_exists('booking_vehicles');
$hasPayouts = tbl_exists('driver_payouts');

/* -----------------------------
   Query
------------------------------*/
$feesExpr = $includeFees
  ? " + COALESCE(b.driver_parking_fee,0) + COALESCE(b.driver_waiting_fee,0) "
  : "";

$paidByBookingSQL = $hasPayouts
  ? "(SELECT dp.company_id, dp.booking_id, COALESCE(SUM(dp.amount),0) AS paid_sum
        FROM driver_payouts dp
       WHERE dp.company_id = :cid
       GROUP BY dp.company_id, dp.booking_id)"
  : "(SELECT 0 AS company_id, 0 AS booking_id, 0 AS paid_sum)"; // harmless dummy

// Inner rows (one per booking inside range) with computed driver_id, due, paid_sum
$driverIdExpr = $hasBV
  ? "(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)"
  : "(SELECT dsub.id
        FROM drivers dsub
       WHERE dsub.id = (SELECT bv2.driver_id FROM booking_vehicles bv2 WHERE bv2.booking_id=b.id ORDER BY bv2.sequence_order ASC, bv2.id ASC LIMIT 1)
       LIMIT 1)"; // Fallback still tries first-assigned if table exists; otherwise will be NULL

// Build outer name select/join
$joinDrivers = $hasDrivers ? "LEFT JOIN drivers d ON d.id=t.driver_id AND d.company_id=:cid" : "";
$nameSelect  = $hasDrivers ? "COALESCE(d.name, CONCAT('#', t.driver_id)) AS driver_name" : "CONCAT('#', t.driver_id) AS driver_name";

// Name filter (only if drivers table exists)
$nameWhere = "t.driver_id IS NOT NULL";
$args = [':cid'=>$cid, ':from'=>$from, ':to'=>$to, ':minjobs'=>max(0,$minJobs)];
if ($hasDrivers && $q !== '') { $nameWhere .= " AND d.name LIKE :q"; $args[':q'] = '%'.$q.'%'; }

$sql = "
  SELECT
    t.driver_id,
    {$nameSelect},
    COUNT(*)                 AS jobs,
    SUM(t.due)               AS due_sum,
    SUM(COALESCE(t.paid,0))  AS paid_sum,
    GREATEST(SUM(t.due) - SUM(COALESCE(t.paid,0)), 0) AS outstanding_sum
  FROM (
    SELECT
      b.id AS booking_id,
      {$driverIdExpr} AS driver_id,
      (COALESCE(b.total_driver_price,0) {$feesExpr}) AS due,
      COALESCE(paidb.paid_sum, 0) AS paid
    FROM bookings b
    LEFT JOIN {$paidByBookingSQL} paidb
           ON paidb.company_id = b.company_id AND paidb.booking_id = b.id
    WHERE b.company_id = :cid
      AND b.status = 'Completed'
      AND b.pickup_date BETWEEN :from AND :to
  ) t
  {$joinDrivers}
  WHERE {$nameWhere}
  GROUP BY t.driver_id, driver_name
  HAVING jobs >= :minjobs
  ORDER BY jobs DESC, due_sum DESC, driver_name ASC
  LIMIT 1000
";

$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 driver utilization.';
}

/* -----------------------------
   Totals & export
------------------------------*/
$driversCount = count($rows);
$totalJobs    = 0;
$totalDue     = 0.0;
$totalPaid    = 0.0;
$totalOut     = 0.0;

foreach ($rows as $r) {
  $totalJobs += (int)$r['jobs'];
  $totalDue  += (float)$r['due_sum'];
  $totalPaid += (float)$r['paid_sum'];
  $totalOut  += (float)$r['outstanding_sum'];
}

if ($export === 'csv') {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="driver_utilization_'.date('Ymd_His').'.csv"');
  $out = fopen('php://output', 'w');
  fputcsv($out, [
    'Period From', $from,
    'To', $to,
    'Include Driver Fees', $includeFees ? 'Yes' : 'No',
    'Min Jobs', $minJobs
  ]);
  fputcsv($out, []);
  fputcsv($out, ['Driver','Jobs','Due (£)','Paid (£)','Outstanding (£)','Avg / Job (£)']);
  foreach ($rows as $r) {
    $jobs = (int)$r['jobs'];
    $due  = (float)$r['due_sum'];
    $paid = (float)$r['paid_sum'];
    $out  = (float)$r['outstanding_sum'];
    $avg  = $jobs > 0 ? ($due / $jobs) : 0.0;
    fputcsv($out, [
      (string)$r['driver_name'],
      $jobs,
      number_format($due, 2, '.', ''),
      number_format($paid, 2, '.', ''),
      number_format($out, 2, '.', ''),
      number_format($avg, 2, '.', ''),
    ]);
  }
  fputcsv($out, []);
  fputcsv($out, ['Totals', $totalJobs, number_format($totalDue,2,'.',''), number_format($totalPaid,2,'.',''), number_format($totalOut,2,'.',''), $totalJobs>0?number_format($totalDue/$totalJobs,2,'.',''):'0.00']);
  fclose($out);
  exit;
}

/* -----------------------------
   Chart data (Top 10 by Jobs)
------------------------------*/
$rowsJobs = $rows;
usort($rowsJobs, fn($a,$b)=> ((int)$b['jobs'] <=> (int)$a['jobs']) ?: ((float)$b['due_sum'] <=> (float)$a['due_sum']));
$top = array_slice($rowsJobs, 0, 10);
$chartLabels = array_map(fn($r)=> (string)$r['driver_name'], $top);
$chartJobs   = array_map(fn($r)=> (int)$r['jobs'], $top);

/* -----------------------------
   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 Utilization & Earnings</h1>
    <div class="text-muted">Completed bookings between <?= e($from) ?> and <?= 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-3">
        <label class="form-label">Driver</label>
        <input class="form-control" name="q" placeholder="<?= $hasDrivers ? 'Name contains…' : 'Name (requires drivers table)'; ?>" value="<?= e($q) ?>">
      </div>
      <div class="col-sm-6 col-md-2">
        <label class="form-label">Min Jobs</label>
        <input type="number" min="0" step="1" class="form-control" name="min_jobs" value="<?= (int)$minJobs ?>">
      </div>
      <div class="col-sm-6 col-md-2">
        <label class="form-label">Include Driver Fees</label>
        <select class="form-select" name="include_fees">
          <option value="1" <?= $includeFees? 'selected':'' ?>>Yes</option>
          <option value="0" <?= $includeFees? '':'selected' ?>>No</option>
        </select>
      </div>
      <div class="col-sm-12 col-md-1 d-grid">
        <button class="btn btn-primary">Apply</button>
      </div>
    </form>
  </div>
</div>

<!-- 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</div>
        <div class="display-6"><?= number_format($driversCount) ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-md-3">
    <div class="card shadow-sm h-100 border-secondary">
      <div class="card-body">
        <div class="small text-muted">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-success">
      <div class="card-body">
        <div class="small text-muted">£ Due</div>
        <div class="display-6">£<?= number_format($totalDue, 2) ?></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">£ Outstanding</div>
        <div class="display-6">£<?= number_format($totalOut, 2) ?></div>
      </div>
    </div>
  </div>
</div>

<!-- Table -->
<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">Drivers</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>Driver</th>
            <th class="text-end">Jobs</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></th>
          </tr>
        </thead>
        <tbody>
          <?php if ($rows): foreach ($rows as $r):
            $jobs = (int)$r['jobs'];
            $due  = (float)$r['due_sum'];
            $paid = (float)$r['paid_sum'];
            $out  = (float)$r['outstanding_sum'];
            $avg  = $jobs>0 ? ($due/$jobs) : 0.0;
          ?>
          <tr>
            <td><?= e($r['driver_name'] ?? ('#'.(int)$r['driver_id'])) ?></td>
            <td class="text-end"><?= number_format($jobs) ?></td>
            <td class="text-end">£<?= number_format($due, 2) ?></td>
            <td class="text-end">£<?= number_format($paid, 2) ?></td>
            <td class="text-end"><span class="badge text-bg-<?= $out>0?'warning':'success' ?>">£<?= number_format($out, 2) ?></span></td>
            <td class="text-end">£<?= number_format($avg, 2) ?></td>
            <td class="text-end">
              <?php if ($hasDrivers): ?>
                <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('drivers/view.php').'?id='.(int)$r['driver_id']) ?>" target="_blank" rel="noopener">Profile</a>
              <?php else: ?>
                <span class="text-muted">—</span>
              <?php endif; ?>
            </td>
          </tr>
          <?php endforeach; else: ?>
            <tr><td colspan="7" 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($totalJobs) ?></th>
            <th class="text-end">£<?= number_format($totalDue, 2) ?></th>
            <th class="text-end">£<?= number_format($totalPaid, 2) ?></th>
            <th class="text-end">£<?= number_format($totalOut, 2) ?></th>
            <th class="text-end">£<?= $totalJobs>0?number_format($totalDue/$totalJobs,2):'0.00' ?></th>
            <th></th>
          </tr>
        </tfoot>
        <?php endif; ?>
      </table>
    </div>
  </div>
</div>

<!-- Chart -->
<div class="card shadow-sm">
  <div class="card-body">
    <div class="fw-semibold mb-2">Top 10 Drivers by Jobs</div>
    <canvas id="drvJobsChart" height="110"></canvas>
  </div>
</div>

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

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
(function(){
  const el = document.getElementById('drvJobsChart'); if (!el || typeof Chart==='undefined') return;
  const labels = <?= json_encode(array_values($chartLabels)) ?>;
  const data   = <?= json_encode(array_values($chartJobs)) ?>;

  new Chart(el, {
    type: 'bar',
    data: { labels, datasets: [{ label: 'Jobs', data }] },
    options: {
      responsive: true,
      plugins: { legend: { display: false } },
      scales: { y: { beginAtZero: true, ticks: { precision: 0 } } }
    }
  });
})();
</script>
