<?php
declare(strict_types=1);

/**
 * modules/reports/payouts_drivers.php
 *
 * Driver Payouts — Paid vs Outstanding
 *
 * Filters:
 *   - Date range (bookings.pickup_date)
 *   - Status: unpaid | paid | all (based on outstanding > 0)
 *   - Driver (optional)
 *   - View: bookings | drivers (aggregate by driver)
 *
 * Logic:
 *   driver_due = total_driver_price + driver_parking_fee + driver_waiting_fee
 *   paid_sum   = SUM(driver_payouts.amount WHERE booking_id = b.id) (0 if table missing)
 *   outstanding= GREATEST(driver_due - paid_sum, 0)
 *
 * Output:
 *   - KPI cards (Totals: Due / Paid / Outstanding)
 *   - Table (by bookings or aggregated by drivers)
 *   - Top Outstanding Drivers bar chart (top 10)
 *   - 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; }
}
function bookings_has_col(string $col): bool {
  static $have = null;
  if ($have === null) {
    $have = [];
    try {
      $rs = db()->query("SHOW COLUMNS FROM bookings");
      foreach ($rs->fetchAll() ?: [] as $c) {
        $have[strtolower((string)$c['Field'])] = true;
      }
    } catch (Throwable) {}
  }
  return isset($have[strtolower($col)]);
}
/** Primary driver id SQL for a booking (portable) */
function primary_driver_sql(): string {
  if (tbl_exists('booking_vehicles')) {
    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 (bookings_has_col('driver_id')) {
    return "b.driver_id";
  }
  // no driver mapping available
  return "NULL";
}

/* -----------------------------
   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);
$status   = (string)($_GET['status'] ?? 'unpaid');     // unpaid|paid|all
$view     = (string)($_GET['view']   ?? 'bookings');   // bookings|drivers
$driverId = (int)($_GET['driver_id'] ?? 0);
$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($status, ['unpaid','paid','all'], true)) $status = 'unpaid';
if (!in_array($view,   ['bookings','drivers'], true))  $view   = 'bookings';

/* -----------------------------
   Options: driver list for filter
------------------------------*/
$driverOpts = [];
try {
  $dq = db()->prepare("SELECT id, name FROM drivers WHERE company_id=:cid ORDER BY name ASC LIMIT 1000");
  $dq->execute([':cid'=>$cid]);
  $driverOpts = $dq->fetchAll() ?: [];
} catch (Throwable) {}

/* -----------------------------
   Build base query fragments
------------------------------*/
$hasPayouts   = tbl_exists('driver_payouts');
$driverExpr   = primary_driver_sql();
$paidSumSQL   = $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";

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

if ($driverId > 0) {
  $where[] = "$driverExpr = :did";
  $args[':did'] = $driverId;
}
$whereSql = implode(' AND ', $where);

/* -----------------------------
   Query data
------------------------------*/
$rows = [];
$agg  = [];    // for drivers view
$top  = [];    // top outstanding per driver for chart

try {
  if ($view === 'bookings') {
    $having = '';
    if ($status === 'unpaid') $having = 'HAVING outstanding > 0.0001';
    if ($status === 'paid')   $having = 'HAVING outstanding < 0.0001';

    $sql = "
      SELECT t.*
      FROM (
        SELECT
          b.id,
          b.booking_ref,
          b.client_name,
          b.pickup_date,
          b.pickup_time,
          $driverExpr AS driver_id,

          (SELECT d.name  FROM drivers d WHERE d.id = $driverExpr LIMIT 1) AS driver_name,
          (SELECT d.phone FROM drivers d WHERE d.id = $driverExpr LIMIT 1) AS driver_phone,

          (COALESCE(b.total_driver_price,0)
          + COALESCE(b.driver_parking_fee,0)
          + COALESCE(b.driver_waiting_fee,0)) AS driver_due,

          $paidSumSQL AS paid_sum
        FROM bookings b
        WHERE $whereSql
      ) t
      CROSS JOIN LATERAL (SELECT GREATEST(t.driver_due - t.paid_sum, 0) AS outstanding) g
      " . ($having ? $having : '') . "
      ORDER BY t.pickup_date DESC, t.pickup_time DESC, t.id DESC
      LIMIT 1000
    ";
    // Note: CROSS JOIN LATERAL is MySQL 8+ / MariaDB 10.3+. If incompatible, fallback:
    $sql = str_replace(
      "CROSS JOIN LATERAL (SELECT GREATEST(t.driver_due - t.paid_sum, 0) AS outstanding) g",
      "",
      $sql
    );
    $sql = str_replace("SELECT t.*", "SELECT t.*, GREATEST(t.driver_due - t.paid_sum, 0) AS outstanding", $sql);

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

    // Build top drivers (outstanding sum)
    $sumByDriver = [];
    foreach ($rows as $r) {
      $did = (int)($r['driver_id'] ?? 0);
      if ($did <= 0) continue;
      $sumByDriver[$did]['name'] = (string)($r['driver_name'] ?? '—');
      $sumByDriver[$did]['out']  = ($sumByDriver[$did]['out'] ?? 0) + (float)$r['outstanding'];
    }
    uasort($sumByDriver, fn($a,$b)=>($b['out'] <=> $a['out']));
    $top = array_slice($sumByDriver, 0, 10, true);

  } else { // view === 'drivers'
    $having = '';
    if ($status === 'unpaid') $having = 'HAVING outstanding_sum > 0.0001';
    if ($status === 'paid')   $having = 'HAVING outstanding_sum < 0.0001';

    $sql = "
      SELECT
        drv.id   AS driver_id,
        drv.name AS driver_name,
        COUNT(*) AS jobs,
        SUM(COALESCE(b.total_driver_price,0)
          + COALESCE(b.driver_parking_fee,0)
          + COALESCE(b.driver_waiting_fee,0)) AS due_sum,
        SUM($paidSumSQL) AS paid_sum,
        GREATEST(SUM(COALESCE(b.total_driver_price,0)
          + COALESCE(b.driver_parking_fee,0)
          + COALESCE(b.driver_waiting_fee,0)) - SUM($paidSumSQL), 0) AS outstanding_sum
      FROM bookings b
      JOIN drivers drv
        ON drv.id = $driverExpr
      WHERE $whereSql
      GROUP BY drv.id, drv.name
      $having
      ORDER BY outstanding_sum DESC, drv.name ASC
      LIMIT 1000
    ";
    $st = db()->prepare($sql);
    $st->execute($args);
    $agg = $st->fetchAll() ?: [];

    // top for chart == agg top
    $top = [];
    foreach ($agg as $r) {
      $top[(int)$r['driver_id']] = [
        'name' => (string)$r['driver_name'],
        'out'  => (float)$r['outstanding_sum'],
      ];
    }
    uasort($top, fn($a,$b)=>($b['out'] <=> $a['out']));
    $top = array_slice($top, 0, 10, true);
  }
} catch (Throwable $e) {
  $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? $e->getMessage() : 'Unable to load driver payouts.';
}

/* -----------------------------
   Totals & CSV export
------------------------------*/
$totalDue = 0.0; $totalPaid = 0.0; $totalOut = 0.0;

if ($view === 'bookings') {
  foreach ($rows as $r) {
    $d = (float)$r['driver_due']; $p = (float)$r['paid_sum']; $o = max(0.0, $d-$p);
    $totalDue += $d; $totalPaid += $p; $totalOut += $o;
  }
} else {
  foreach ($agg as $r) {
    $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="payouts_drivers_'.date('Ymd_His').'.csv"');
  $out = fopen('php://output', 'w');

  if ($view === 'bookings') {
    fputcsv($out, ['Booking Ref','Pickup','Driver','£ Due','£ Paid','£ Outstanding']);
    foreach ($rows as $r) {
      $pdt = trim((string)($r['pickup_date'] ?? '').' '.(string)($r['pickup_time'] ?? ''));
      fputcsv($out, [
        (string)($r['booking_ref'] ?? ('#'.$r['id'])),
        $pdt,
        (string)($r['driver_name'] ?? '—'),
        number_format((float)$r['driver_due'], 2, '.', ''),
        number_format((float)$r['paid_sum'],    2, '.', ''),
        number_format(max(0.0, (float)$r['driver_due'] - (float)$r['paid_sum']), 2, '.', ''),
      ]);
    }
  } else {
    fputcsv($out, ['Driver','Jobs','#','£ Due','£ Paid','£ Outstanding']);
    foreach ($agg as $r) {
      fputcsv($out, [
        (string)$r['driver_name'],
        (int)$r['jobs'],
        number_format((float)$r['due_sum'], 2, '.', ''),
        number_format((float)$r['paid_sum'], 2, '.', ''),
        number_format((float)$r['outstanding_sum'], 2, '.', ''),
      ]);
    }
  }
  // Totals
  fputcsv($out, ['TOTAL','','', number_format($totalDue,2,'.',''), number_format($totalPaid,2,'.',''), number_format($totalOut,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">Driver Payouts — Paid vs Outstanding</h1>
    <div class="text-muted">Cleared jobs (Completed) 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-md-2">
        <label class="form-label">From</label>
        <input type="date" class="form-control" name="from" value="<?= e($from) ?>">
      </div>
      <div class="col-md-2">
        <label class="form-label">To</label>
        <input type="date" class="form-control" name="to" value="<?= e($to) ?>">
      </div>
      <div class="col-md-2">
        <label class="form-label">Status</label>
        <select class="form-select" name="status">
          <option value="unpaid" <?= $status==='unpaid'?'selected':'' ?>>Unpaid</option>
          <option value="paid"   <?= $status==='paid'  ?'selected':'' ?>>Paid</option>
          <option value="all"    <?= $status==='all'   ?'selected':'' ?>>All</option>
        </select>
      </div>
      <div class="col-md-3">
        <label class="form-label">Driver</label>
        <select class="form-select" name="driver_id">
          <option value="0">— All —</option>
          <?php foreach ($driverOpts as $d): ?>
            <option value="<?= (int)$d['id'] ?>" <?= $driverId===(int)$d['id']?'selected':'' ?>><?= e($d['name'] ?? ('#'.$d['id'])) ?></option>
          <?php endforeach; ?>
        </select>
      </div>
      <div class="col-md-3">
        <label class="form-label">View</label>
        <select class="form-select" name="view">
          <option value="bookings" <?= $view==='bookings'?'selected':'' ?>>By Bookings</option>
          <option value="drivers"  <?= $view==='drivers'?'selected':'' ?>>By Drivers (Aggregated)</option>
        </select>
      </div>
      <div class="col-12 d-grid d-md-block">
        <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-md-4">
    <div class="card shadow-sm h-100 border-secondary">
      <div class="card-body">
        <div class="small text-muted">Total Due</div>
        <div class="display-6">£<?= number_format($totalDue, 2) ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-md-4">
    <div class="card shadow-sm h-100 border-success">
      <div class="card-body">
        <div class="small text-muted">Total Paid</div>
        <div class="display-6">£<?= number_format($totalPaid, 2) ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-md-4">
    <div class="card shadow-sm h-100 border-danger">
      <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">
    <?php if ($view === 'bookings'): ?>
      <div class="d-flex justify-content-between align-items-center mb-2">
        <div class="fw-semibold">Bookings</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>Ref</th>
              <th>Pickup</th>
              <th>Client</th>
              <th>Driver</th>
              <th class="text-end">£ Due</th>
              <th class="text-end">£ Paid</th>
              <th class="text-end">£ Outstanding</th>
            </tr>
          </thead>
          <tbody>
            <?php if ($rows): foreach ($rows as $r): ?>
              <tr class="<?= ((float)$r['outstanding'] ?? 0) > 0 ? '' : 'table-success' ?>">
                <td><strong><?= e($r['booking_ref'] ?? ('#'.$r['id'])) ?></strong></td>
                <td><?= e(trim(($r['pickup_date'] ?? '').' '.($r['pickup_time'] ?? ''))) ?></td>
                <td><?= e($r['client_name'] ?? '—') ?></td>
                <td><?= e($r['driver_name'] ?? '—') ?></td>
                <td class="text-end">£<?= number_format((float)$r['driver_due'], 2) ?></td>
                <td class="text-end">£<?= number_format((float)$r['paid_sum'],    2) ?></td>
                <td class="text-end"><span class="badge text-bg-<?= ((float)$r['outstanding']>0?'danger':'success') ?>">£<?= number_format((float)$r['outstanding'], 2) ?></span></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 colspan="4" class="text-end">Totals</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>
            </tr>
          </tfoot>
          <?php endif; ?>
        </table>
      </div>
    <?php else: ?>
      <div class="d-flex justify-content-between align-items-center mb-2">
        <div class="fw-semibold">Drivers (Aggregated)</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>
            </tr>
          </thead>
          <tbody>
            <?php if ($agg): foreach ($agg as $r): ?>
              <tr class="<?= ((float)$r['outstanding_sum'] ?? 0) > 0 ? '' : 'table-success' ?>">
                <td><?= e($r['driver_name'] ?? '—') ?></td>
                <td class="text-end"><?= number_format((int)($r['jobs'] ?? 0)) ?></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"><span class="badge text-bg-<?= ((float)$r['outstanding_sum']>0?'danger':'success') ?>">£<?= number_format((float)$r['outstanding_sum'], 2) ?></span></td>
              </tr>
            <?php endforeach; else: ?>
              <tr><td colspan="5" class="text-center text-muted py-4">No results.</td></tr>
            <?php endif; ?>
          </tbody>
          <?php if ($agg): ?>
          <tfoot class="table-light">
            <tr>
              <th class="text-end">Totals</th>
              <th class="text-end">—</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>
            </tr>
          </tfoot>
          <?php endif; ?>
        </table>
      </div>
    <?php endif; ?>
  </div>
</div>

<!-- Chart: Top Outstanding Drivers -->
<div class="card shadow-sm">
  <div class="card-body">
    <div class="fw-semibold mb-2">Top Outstanding Drivers (Top 10)</div>
    <canvas id="topChart" 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('topChart'); if (!el || typeof Chart==='undefined') return;

  const labels  = <?= json_encode(array_values(array_map(fn($v)=>$v['name'] ?? '—', $top))) ?>;
  const data    = <?= json_encode(array_values(array_map(fn($v)=>round((float)$v['out'],2), $top))) ?>;

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