<?php
declare(strict_types=1);

/**
 * modules/reports/avg_fees.php
 *
 * Average Fees per Job
 * - Averages of Client vs Driver fees (Parking & Waiting)
 * - Group by: None (overall) | Month | Partner | Corporate | Driver
 * - Outlier detection (z-score and/or minimum total fee)
 * - Charts (avg total; client vs driver split)
 * - CSV exports (grouped + outliers)
 */

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
------------------------------*/
$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);
$groupBy  = (string)($_GET['group_by'] ?? 'none'); // none|month|partner|corporate|driver
$q        = trim((string)($_GET['q'] ?? ''));      // search name of group (partner/driver/corporate) when applicable

$zThresh  = (float)($_GET['z'] ?? 2.0);            // z-score threshold
$minAmt   = isset($_GET['min']) && $_GET['min'] !== '' ? (float)$_GET['min'] : null; // absolute fee threshold
$limit    = max(5, (int)($_GET['limit'] ?? 50));   // outliers table size
$export   = (string)($_GET['export'] ?? '');       // csv
$etype    = (string)($_GET['t'] ?? 'groups');      // groups|outliers

if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $from)) $from = $monthStart;
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $to))   $to   = $monthEnd;

$hasBV       = tbl_exists('booking_vehicles');
$hasDrivers  = tbl_exists('drivers');
$hasPartners = tbl_exists('partners');
$hasCorps    = tbl_exists('corporates');
if ($groupBy === 'driver' && (!$hasBV || !$hasDrivers)) {
  $errors[] = 'Driver grouping requires booking_vehicles and drivers tables.';
}

/* -----------------------------
   Build SQL for group aggregation
------------------------------*/
$where = [
  "b.company_id = :cid",
  "b.status = 'Completed'",
  "b.pickup_date BETWEEN :from AND :to"
];
$args  = [':cid'=>$cid, ':from'=>$from, ':to'=>$to];

$grpKey   = "'all'";     // default for 'none'
$grpLabel = "'All'";
$joins    = "";

switch ($groupBy) {
  case 'month':
    $grpKey   = "DATE_FORMAT(b.pickup_date,'%Y-%m')";
    $grpLabel = $grpKey;
    break;

  case 'partner':
    if ($hasPartners) {
      $joins   .= " JOIN partners p ON p.id = b.partner_id AND p.company_id = b.company_id ";
      $grpKey   = "p.id";
      $grpLabel = "p.name";
      if ($q !== '') { $where[] = "p.name LIKE :q"; $args[':q'] = '%'.$q.'%'; }
    } else {
      $errors[] = 'partners table not found, cannot group by partner.';
    }
    break;

  case 'corporate':
    if ($hasCorps) {
      $joins   .= " LEFT JOIN corporates c ON c.id = b.corporate_id AND c.company_id = b.company_id ";
      $grpKey   = "COALESCE(c.id, 0)";
      $grpLabel = "COALESCE(c.name, '—')";
      if ($q !== '') { $where[] = "c.name LIKE :q"; $args[':q'] = '%'.$q.'%'; }
    } else {
      $errors[] = 'corporates table not found, cannot group by corporate.';
    }
    break;

  case 'driver':
    if ($hasBV && $hasDrivers) {
      // first assigned driver per booking via "min id" trick
      $joins .= "
        LEFT JOIN (
          SELECT bv2.booking_id, MIN(bv2.id) AS min_bv_id
          FROM booking_vehicles bv2
          GROUP BY bv2.booking_id
        ) map ON map.booking_id = b.id
        LEFT JOIN booking_vehicles bv ON bv.id = map.min_bv_id
        LEFT JOIN drivers d ON d.id = bv.driver_id AND d.company_id = b.company_id
      ";
      $grpKey   = "COALESCE(d.id, 0)";
      $grpLabel = "COALESCE(d.name, '—')";
      if ($q !== '') { $where[] = "d.name LIKE :q"; $args[':q'] = '%'.$q.'%'; }
    }
    break;

  case 'none':
  default:
    // keep default 'All'
    break;
}

// Agg fields
$aggFields = "
  COUNT(*) AS jobs,
  AVG(COALESCE(b.client_parking_fee,0))  AS avg_cp,
  AVG(COALESCE(b.client_waiting_fee,0))  AS avg_cw,
  AVG(COALESCE(b.driver_parking_fee,0))  AS avg_dp,
  AVG(COALESCE(b.driver_waiting_fee,0))  AS avg_dw,
  AVG(COALESCE(b.client_parking_fee,0) + COALESCE(b.client_waiting_fee,0)
    +  COALESCE(b.driver_parking_fee,0) + COALESCE(b.driver_waiting_fee,0)) AS avg_total,
  SUM(COALESCE(b.client_parking_fee,0) > 0) AS cnt_cp,
  SUM(COALESCE(b.client_waiting_fee,0) > 0) AS cnt_cw,
  SUM(COALESCE(b.driver_parking_fee,0) > 0) AS cnt_dp,
  SUM(COALESCE(b.driver_waiting_fee,0) > 0) AS cnt_dw
";

/* -----------------------------
   Run group query
------------------------------*/
$groupRows = [];
if (!$errors) {
  try {
    if ($groupBy === 'none') {
      $sql = "
        SELECT
          {$grpKey} AS grp_key,
          {$grpLabel} AS grp_label,
          {$aggFields}
        FROM bookings b
        {$joins}
        WHERE ".implode(' AND ', $where)."
        LIMIT 1
      ";
    } else {
      $sql = "
        SELECT
          {$grpKey}   AS grp_key,
          {$grpLabel} AS grp_label,
          {$aggFields}
        FROM bookings b
        {$joins}
        WHERE ".implode(' AND ', $where)."
        GROUP BY grp_key, grp_label
        ORDER BY avg_total DESC, jobs DESC
        LIMIT 1000
      ";
    }
    $stmt = db()->prepare($sql);
    $stmt->execute($args);
    $groupRows = $stmt->fetchAll() ?: [];
  } catch (Throwable $e) {
    $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? $e->getMessage() : 'Unable to load averages.';
  }
}

/* -----------------------------
   Outliers (row-level)
------------------------------*/
$outliers = [];
$mean = 0.0; $sd = 0.0;
if (!$errors) {
  try {
    // Fetch row-level fees for outlier detection
    $qsql = "
      SELECT
        b.id,
        b.booking_ref,
        b.pickup_date,
        b.pickup_time,
        COALESCE(b.client_parking_fee,0) AS cp,
        COALESCE(b.client_waiting_fee,0) AS cw,
        COALESCE(b.driver_parking_fee,0) AS dp,
        COALESCE(b.driver_waiting_fee,0) AS dw
        ".($hasPartners ? ", (SELECT name FROM partners p WHERE p.id=b.partner_id AND p.company_id=b.company_id LIMIT 1) AS partner_name" : ", NULL AS partner_name")."
        ".($hasBV && $hasDrivers ? ",
          (SELECT d.name
             FROM booking_vehicles bvx
             JOIN drivers d ON d.id=bvx.driver_id AND d.company_id=b.company_id
            WHERE bvx.booking_id=b.id
            ORDER BY bvx.id ASC
            LIMIT 1) AS driver_name
        " : ", NULL AS driver_name")."
      FROM bookings b
      WHERE b.company_id=:cid
        AND b.status='Completed'
        AND b.pickup_date BETWEEN :from AND :to
      ORDER BY b.pickup_date ASC, b.pickup_time ASC, b.id ASC
      LIMIT 5000
    ";
    $qs = db()->prepare($qsql);
    $qs->execute([':cid'=>$cid, ':from'=>$from, ':to'=>$to]);
    $rows = $qs->fetchAll() ?: [];

    $totals = [];
    foreach ($rows as $r) {
      $tot = (float)$r['cp'] + (float)$r['cw'] + (float)$r['dp'] + (float)$r['dw'];
      $totals[] = $tot;
    }
    $count = count($totals);
    if ($count > 1) {
      $mean = array_sum($totals) / $count;
      $variance = 0.0;
      foreach ($totals as $t) { $variance += ($t - $mean) * ($t - $mean); }
      $variance /= ($count - 1); // sample variance
      $sd = sqrt(max(0.0, $variance));
    }

    // Build outliers list
    foreach ($rows as $r) {
      $tot = (float)$r['cp'] + (float)$r['cw'] + (float)$r['dp'] + (float)$r['dw'];
      $z   = ($sd > 0) ? (($tot - $mean) / $sd) : 0.0;
      $passZ   = ($zThresh > 0) ? ($z >= $zThresh) : false;
      $passMin = ($minAmt !== null) ? ($tot >= $minAmt) : false;

      if ($passZ || $passMin) {
        $outliers[] = [
          'booking_ref'  => (string)$r['booking_ref'],
          'pickup'       => trim((string)$r['pickup_date'].' '.(string)$r['pickup_time']),
          'partner_name' => (string)($r['partner_name'] ?? ''),
          'driver_name'  => (string)($r['driver_name'] ?? ''),
          'cp' => (float)$r['cp'], 'cw' => (float)$r['cw'],
          'dp' => (float)$r['dp'], 'dw' => (float)$r['dw'],
          'total' => $tot,
          'z'     => $z,
        ];
      }
    }

    // Sort outliers by total desc, limit
    usort($outliers, fn($a,$b)=>($b['total'] <=> $a['total']));
    $outliers = array_slice($outliers, 0, $limit);

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

/* -----------------------------
   CSV Export
------------------------------*/
if ($export === 'csv') {
  if ($etype === 'outliers') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="avg_fees_outliers_'.date('Ymd_His').'.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['From',$from,'To',$to,'GroupBy',$groupBy,'Z>=',$zThresh,'Min>=',($minAmt??'')]);
    fputcsv($out, []);
    fputcsv($out, ['Booking Ref','Pickup','Partner','Driver','Client Parking','Client Waiting','Driver Parking','Driver Waiting','Total Fees','Z-Score']);
    foreach ($outliers as $r) {
      fputcsv($out, [
        $r['booking_ref'], $r['pickup'], $r['partner_name'], $r['driver_name'],
        number_format($r['cp'],2,'.',''), number_format($r['cw'],2,'.',''),
        number_format($r['dp'],2,'.',''), number_format($r['dw'],2,'.',''),
        number_format($r['total'],2,'.',''), number_format($r['z'],3,'.',''),
      ]);
    }
    fclose($out);
    exit;
  } else {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="avg_fees_groups_'.date('Ymd_His').'.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['From',$from,'To',$to,'GroupBy',$groupBy,'Search',$q]);
    fputcsv($out, []);
    fputcsv($out, ['Group','Jobs','Avg Client Parking','Avg Client Waiting','Avg Driver Parking','Avg Driver Waiting','Avg Total','Jobs w/CP','w/CW','w/DP','w/DW']);
    foreach ($groupRows as $r) {
      fputcsv($out, [
        (string)$r['grp_label'],
        (int)$r['jobs'],
        number_format((float)$r['avg_cp'],2,'.',''),
        number_format((float)$r['avg_cw'],2,'.',''),
        number_format((float)$r['avg_dp'],2,'.',''),
        number_format((float)$r['avg_dw'],2,'.',''),
        number_format((float)$r['avg_total'],2,'.',''),
        (int)$r['cnt_cp'], (int)$r['cnt_cw'], (int)$r['cnt_dp'], (int)$r['cnt_dw'],
      ]);
    }
    fclose($out);
    exit;
  }
}

/* -----------------------------
   Prep chart data
------------------------------*/
$labels   = array_map(fn($r)=> (string)$r['grp_label'], $groupRows);
$avgTotal = array_map(fn($r)=> (float)$r['avg_total'], $groupRows);
$avgClient= array_map(fn($r)=> (float)$r['avg_cp'] + (float)$r['avg_cw'], $groupRows);
$avgDriver= array_map(fn($r)=> (float)$r['avg_dp'] + (float)$r['avg_dw'], $groupRows);

/* -----------------------------
   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">Average Fees per Job</h1>
    <div class="text-muted">Completed bookings · 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
      $qsG = $_GET; $qsG['export']='csv'; $qsG['t']='groups';
      $qsO = $_GET; $qsO['export']='csv'; $qsO['t']='outliers';
    ?>
    <a class="btn btn-outline-primary" href="<?= e('?'.http_build_query($qsG)) ?>">⬇️ Export Groups CSV</a>
    <a class="btn btn-outline-primary" href="<?= e('?'.http_build_query($qsO)) ?>">⬇️ Export Outliers 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-2">
        <label class="form-label">From</label>
        <input type="date" class="form-control" name="from" value="<?= e($from) ?>">
      </div>
      <div class="col-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-12 col-md-3">
        <label class="form-label">Group By</label>
        <select class="form-select" name="group_by">
          <option value="none"     <?= $groupBy==='none'?'selected':'' ?>>None (Overall)</option>
          <option value="month"    <?= $groupBy==='month'?'selected':'' ?>>Month</option>
          <option value="partner"  <?= $groupBy==='partner'?'selected':'' ?>>Partner</option>
          <option value="corporate"<?= $groupBy==='corporate'?'selected':'' ?>>Corporate</option>
          <option value="driver"   <?= $groupBy==='driver'?'selected':'' ?>>Driver</option>
        </select>
        <?php if ($groupBy==='driver' && (!$hasBV || !$hasDrivers)): ?>
          <div class="small text-warning mt-1">Driver grouping requires booking_vehicles + drivers.</div>
        <?php endif; ?>
      </div>
      <div class="col-12 col-md-3">
        <label class="form-label">Search (group name)</label>
        <input class="form-control" name="q" value="<?= e($q) ?>" placeholder="Partner / Driver / Corporate">
      </div>
      <div class="col-6 col-md-1">
        <label class="form-label">Z ≥</label>
        <input type="number" step="0.1" class="form-control" name="z" value="<?= e(number_format($zThresh,1,'.','')) ?>">
      </div>
      <div class="col-6 col-md-1">
        <label class="form-label">Min ≥ £</label>
        <input type="number" step="0.01" class="form-control" name="min" value="<?= e($minAmt!==null ? number_format($minAmt,2,'.','') : '') ?>">
      </div>
      <div class="col-6 col-md-1">
        <label class="form-label">Top</label>
        <input type="number" min="5" step="5" class="form-control" name="limit" value="<?= (int)$limit ?>">
      </div>
      <div class="col-6 col-md-1 d-grid">
        <button class="btn btn-primary">Apply</button>
      </div>
    </form>
  </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">Average Total Fees (per Job)</div>
        <canvas id="avgTotalChart" 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">Client vs Driver Fees (Average per Job)</div>
        <canvas id="splitChart" height="160"></canvas>
      </div>
    </div>
  </div>
</div>

<!-- Grouped Table -->
<div class="card shadow-sm mb-3">
  <div class="table-responsive">
    <table class="table align-middle mb-0">
      <thead class="table-light">
        <tr>
          <th><?= $groupBy==='none' ? 'Scope' : 'Group' ?></th>
          <th class="text-end">Jobs</th>
          <th class="text-end">Avg Client Parking</th>
          <th class="text-end">Avg Client Waiting</th>
          <th class="text-end">Avg Driver Parking</th>
          <th class="text-end">Avg Driver Waiting</th>
          <th class="text-end">Avg Total</th>
          <th class="text-end text-muted">Jobs w/CP</th>
          <th class="text-end text-muted">w/CW</th>
          <th class="text-end text-muted">w/DP</th>
          <th class="text-end text-muted">w/DW</th>
        </tr>
      </thead>
      <tbody>
        <?php if ($groupRows): foreach ($groupRows as $r): ?>
          <tr>
            <td><div class="fw-semibold"><?= e($r['grp_label'] ?? '—') ?></div></td>
            <td class="text-end"><?= number_format((int)$r['jobs']) ?></td>
            <td class="text-end">£<?= number_format((float)$r['avg_cp'], 2) ?></td>
            <td class="text-end">£<?= number_format((float)$r['avg_cw'], 2) ?></td>
            <td class="text-end">£<?= number_format((float)$r['avg_dp'], 2) ?></td>
            <td class="text-end">£<?= number_format((float)$r['avg_dw'], 2) ?></td>
            <td class="text-end fw-semibold">£<?= number_format((float)$r['avg_total'], 2) ?></td>
            <td class="text-end text-muted"><?= number_format((int)$r['cnt_cp']) ?></td>
            <td class="text-end text-muted"><?= number_format((int)$r['cnt_cw']) ?></td>
            <td class="text-end text-muted"><?= number_format((int)$r['cnt_dp']) ?></td>
            <td class="text-end text-muted"><?= number_format((int)$r['cnt_dw']) ?></td>
          </tr>
        <?php endforeach; else: ?>
          <tr><td colspan="11" class="text-center text-muted py-4">No results.</td></tr>
        <?php endif; ?>
      </tbody>
    </table>
  </div>
</div>

<!-- Outliers Table -->
<div class="card shadow-sm">
  <div class="card-body">
    <div class="d-flex justify-content-between align-items-center mb-2">
      <div>
        <div class="fw-semibold">Outliers (High Total Fees)</div>
        <div class="small text-muted">
          Mean £<?= number_format($mean,2) ?><?php if ($sd>0): ?> · SD £<?= number_format($sd,2) ?><?php endif; ?>
          <?php if ($zThresh>0): ?> · Z ≥ <?= e(number_format($zThresh,1)) ?><?php endif; ?>
          <?php if ($minAmt!==null): ?> · Min ≥ £<?= e(number_format($minAmt,2)) ?><?php endif; ?>
        </div>
      </div>
      <?php $qsO = $_GET; $qsO['export']='csv'; $qsO['t']='outliers'; ?>
      <a class="btn btn-sm btn-outline-primary" href="<?= e('?'.http_build_query($qsO)) ?>">⬇️ Export Outliers CSV</a>
    </div>

    <div class="table-responsive">
      <table class="table align-middle mb-0">
        <thead class="table-light">
          <tr>
            <th>Booking</th>
            <th>Pickup</th>
            <th>Partner</th>
            <th>Driver</th>
            <th class="text-end">Client Pkg</th>
            <th class="text-end">Client Wait</th>
            <th class="text-end">Driver Pkg</th>
            <th class="text-end">Driver Wait</th>
            <th class="text-end">Total</th>
            <th class="text-end">Z</th>
          </tr>
        </thead>
        <tbody>
          <?php if ($outliers): foreach ($outliers as $r): ?>
            <tr class="table-warning">
              <td><strong><?= e($r['booking_ref']) ?></strong></td>
              <td><?= e($r['pickup']) ?></td>
              <td><?= e($r['partner_name'] ?: '—') ?></td>
              <td><?= e($r['driver_name'] ?: '—') ?></td>
              <td class="text-end">£<?= number_format($r['cp'],2) ?></td>
              <td class="text-end">£<?= number_format($r['cw'],2) ?></td>
              <td class="text-end">£<?= number_format($r['dp'],2) ?></td>
              <td class="text-end">£<?= number_format($r['dw'],2) ?></td>
              <td class="text-end fw-semibold">£<?= number_format($r['total'],2) ?></td>
              <td class="text-end"><?= $sd>0 ? number_format($r['z'],2) : '—' ?></td>
            </tr>
          <?php endforeach; else: ?>
            <tr><td colspan="10" class="text-center text-muted py-4">No outliers matched your thresholds.</td></tr>
          <?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>

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

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
(function(){
  const labels    = <?= json_encode($labels) ?>;
  const avgTotal  = <?= json_encode($avgTotal) ?>;
  const avgClient = <?= json_encode($avgClient) ?>;
  const avgDriver = <?= json_encode($avgDriver) ?>;

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

  const el2 = document.getElementById('splitChart');
  if (el2 && typeof Chart !== 'undefined') {
    new Chart(el2, {
      type: 'bar',
      data: {
        labels,
        datasets: [
          { label: 'Client Fees (£)', data: avgClient },
          { label: 'Driver Fees (£)', data: avgDriver },
        ]
      },
      options: {
        responsive:true,
        scales: { y:{ beginAtZero:true } }
      }
    });
  }
})();
</script>
