<?php
declare(strict_types=1);

/**
 * modules/reports/costs_fees.php
 *
 * Cost Breakdown: Parking & Waiting (Driver vs Client)
 *
 * Filters:
 *  - Date range (bookings.pickup_date)
 *  - Group by: Month | Partner | Driver
 *  - Side: Driver fees, Client fees, or Both
 *  - Search (when grouping by Partner/Driver)
 *
 * Data:
 *  From bookings (Completed only):
 *    driver_parking_fee, driver_waiting_fee,
 *    client_parking_fee, client_waiting_fee
 *
 * Output:
 *  - KPI cards (totals for driver/client fees)
 *  - Table grouped by chosen dimension
 *  - Stacked bar chart (top groups)
 *  - 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);
$group = (string)($_GET['group']  ?? 'month');   // month|partner|driver
$side  = (string)($_GET['side']   ?? 'both');    // driver|client|both
$q     = trim((string)($_GET['q'] ?? ''));       // name filter for partner/driver
$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($group, ['month','partner','driver'], true)) $group = 'month';
if (!in_array($side, ['driver','client','both'], true))    $side  = 'both';

$hasPartners = tbl_exists('partners');
$hasDrivers  = tbl_exists('drivers');
$hasBV       = tbl_exists('booking_vehicles');

/* -----------------------------
   Build query by group
------------------------------*/
$baseWhere = "b.company_id = :cid AND b.status = 'Completed' AND b.pickup_date BETWEEN :from AND :to";

$selectSums = "
  SUM(COALESCE(b.driver_parking_fee,0)) AS drv_park,
  SUM(COALESCE(b.driver_waiting_fee,0)) AS drv_wait,
  SUM(COALESCE(b.client_parking_fee,0)) AS cli_park,
  SUM(COALESCE(b.client_waiting_fee,0)) AS cli_wait
";

$args = [':cid'=>$cid, ':from'=>$from, ':to'=>$to];

switch ($group) {
  case 'partner':
    // Group by partner (using partner_id in bookings)
    $nameSel = $hasPartners ? "COALESCE(p.name, CONCAT('#', b.partner_id))" : "CONCAT('#', b.partner_id)";
    $nameWhere = '';
    if ($q !== '') {
      if ($hasPartners) {
        $nameWhere = " AND p.name LIKE :q";
        $args[':q'] = '%'.$q.'%';
      } else {
        // fallback: filter label after fetch (we'll still add a benign SQL condition)
        $nameWhere = ""; // do it in PHP later
      }
    }
    $sql = "
      SELECT
        b.partner_id AS grp_id,
        {$nameSel}   AS grp_label,
        {$selectSums}
      FROM bookings b
      ".($hasPartners ? "LEFT JOIN partners p ON p.id=b.partner_id AND p.company_id=b.company_id" : "")."
      WHERE {$baseWhere} AND b.partner_id IS NOT NULL {$nameWhere}
      GROUP BY b.partner_id, grp_label
      ORDER BY grp_label ASC
      LIMIT 2000
    ";
    break;

  case 'driver':
    // Group by first assigned driver (from booking_vehicles)
    if ($hasBV) {
      $nameSel = $hasDrivers ? "COALESCE(d.name, CONCAT('#', drv.driver_id))" : "CONCAT('#', drv.driver_id)";
      $nameWhere = '';
      if ($q !== '' && $hasDrivers) { $nameWhere = " AND d.name LIKE :q"; $args[':q'] = '%'.$q.'%'; }

      $sql = "
        WITH first_driver AS (
          SELECT bv.booking_id, bv.driver_id
            FROM booking_vehicles bv
            JOIN (
              SELECT booking_id, MIN(sequence_order) AS min_seq
                FROM booking_vehicles
               GROUP BY booking_id
            ) x ON x.booking_id=bv.booking_id AND x.min_seq=bv.sequence_order
        )
        SELECT
          drv.driver_id   AS grp_id,
          {$nameSel}      AS grp_label,
          {$selectSums}
        FROM bookings b
        JOIN first_driver drv ON drv.booking_id = b.id
        ".($hasDrivers ? "LEFT JOIN drivers d ON d.id=drv.driver_id AND d.company_id=b.company_id" : "")."
        WHERE {$baseWhere} {$nameWhere}
        GROUP BY drv.driver_id, grp_label
        ORDER BY grp_label ASC
        LIMIT 2000
      ";
    } else {
      // booking_vehicles missing → nothing to group; return empty
      $sql = "
        SELECT NULL AS grp_id, '' AS grp_label,
               0 AS drv_park, 0 AS drv_wait, 0 AS cli_park, 0 AS cli_wait
        WHERE 1=0
      ";
    }
    break;

  case 'month':
  default:
    // Group by YYYY-MM of pickup_date
    $sql = "
      SELECT
        DATE_FORMAT(b.pickup_date, '%Y-%m') AS grp_id,
        DATE_FORMAT(b.pickup_date, '%Y-%m') AS grp_label,
        {$selectSums}
      FROM bookings b
      WHERE {$baseWhere}
      GROUP BY grp_id, grp_label
      ORDER BY grp_id ASC
      LIMIT 2000
    ";
    break;
}

$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 cost breakdown.';
}

/* Optional client-side name filter if partners table missing but user searched */
if ($group === 'partner' && $q !== '' && !$hasPartners && $rows) {
  $rows = array_values(array_filter($rows, function($r) use($q) {
    return stripos((string)($r['grp_label'] ?? ''), $q) !== false;
  }));
}

/* -----------------------------
   Compute totals & reshape
------------------------------*/
$totalDrvPark = 0.0; $totalDrvWait = 0.0; $totalCliPark = 0.0; $totalCliWait = 0.0;
$display = [];

foreach ($rows as $r) {
  $dp = (float)$r['drv_park'];
  $dw = (float)$r['drv_wait'];
  $cp = (float)$r['cli_park'];
  $cw = (float)$r['cli_wait'];

  $totalDrvPark += $dp;
  $totalDrvWait += $dw;
  $totalCliPark += $cp;
  $totalCliWait += $cw;

  $display[] = [
    'label'      => (string)($r['grp_label'] ?? '—'),
    'drv_park'   => $dp,
    'drv_wait'   => $dw,
    'drv_total'  => $dp + $dw,
    'cli_park'   => $cp,
    'cli_wait'   => $cw,
    'cli_total'  => $cp + $cw,
    'grand'      => $dp + $dw + $cp + $cw,
  ];
}

// Sort for month: by label asc (YYYY-MM); for others, by grand desc by default
if ($group === 'month') {
  usort($display, fn($a,$b)=> strcmp($a['label'],$b['label']));
} else {
  usort($display, fn($a,$b)=> ($b['grand'] <=> $a['grand']) ?: strcmp($a['label'],$b['label']));
}

$totals = [
  'driver_total' => $totalDrvPark + $totalDrvWait,
  'client_total' => $totalCliPark + $totalCliWait,
  'grand_total'  => $totalDrvPark + $totalDrvWait + $totalCliPark + $totalCliWait,
];

/* -----------------------------
   Export CSV
------------------------------*/
if ($export === 'csv') {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="costs_fees_'.date('Ymd_His').'.csv"');
  $out = fopen('php://output', 'w');
  fputcsv($out, ['From',$from,'To',$to,'Group',$group,'Side',$side]);
  fputcsv($out, []);
  fputcsv($out, ['Group',
                 'Driver Parking','Driver Waiting','Driver Total',
                 'Client Parking','Client Waiting','Client Total',
                 'Grand Total']);
  foreach ($display as $row) {
    fputcsv($out, [
      $row['label'],
      number_format($row['drv_park'],2,'.',''),
      number_format($row['drv_wait'],2,'.',''),
      number_format($row['drv_total'],2,'.',''),
      number_format($row['cli_park'],2,'.',''),
      number_format($row['cli_wait'],2,'.',''),
      number_format($row['cli_total'],2,'.',''),
      number_format($row['grand'],2,'.',''),
    ]);
  }
  fputcsv($out, []);
  fputcsv($out, ['Totals',
    number_format($totalDrvPark,2,'.',''),
    number_format($totalDrvWait,2,'.',''),
    number_format($totals['driver_total'],2,'.',''),
    number_format($totalCliPark,2,'.',''),
    number_format($totalCliWait,2,'.',''),
    number_format($totals['client_total'],2,'.',''),
    number_format($totals['grand_total'],2,'.',''),
  ]);
  fclose($out);
  exit;
}

/* -----------------------------
   Chart data (Top groups)
------------------------------*/
$top = $display;
$chartMax = ($group === 'month') ? 12 : 10; // keep charts tidy
if (count($top) > $chartMax) $top = array_slice($top, 0, $chartMax);

$labels = array_map(fn($r)=> (string)$r['label'], $top);

if ($side === 'driver') {
  $ds1 = array_map(fn($r)=> (float)$r['drv_park'], $top);
  $ds2 = array_map(fn($r)=> (float)$r['drv_wait'], $top);
  $legend1 = 'Driver Parking'; $legend2 = 'Driver Waiting';
} elseif ($side === 'client') {
  $ds1 = array_map(fn($r)=> (float)$r['cli_park'], $top);
  $ds2 = array_map(fn($r)=> (float)$r['cli_wait'], $top);
  $legend1 = 'Client Parking'; $legend2 = 'Client Waiting';
} else { // both → stack totals (driver vs client)
  $ds1 = array_map(fn($r)=> (float)$r['drv_total'], $top);
  $ds2 = array_map(fn($r)=> (float)$r['cli_total'], $top);
  $legend1 = 'Driver Fees (Total)'; $legend2 = 'Client Fees (Total)';
}

/* -----------------------------
   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">Cost Breakdown — Parking & Waiting</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-2">
        <label class="form-label">Group By</label>
        <select class="form-select" name="group">
          <option value="month"   <?= $group==='month'  ?'selected':'' ?>>Month</option>
          <option value="partner" <?= $group==='partner'?'selected':'' ?>>Partner</option>
          <option value="driver"  <?= $group==='driver' ?'selected':'' ?>>Driver</option>
        </select>
      </div>
      <div class="col-sm-6 col-md-2">
        <label class="form-label">Side</label>
        <select class="form-select" name="side">
          <option value="both"   <?= $side==='both'  ?'selected':'' ?>>Both (Driver vs Client)</option>
          <option value="driver" <?= $side==='driver'?'selected':'' ?>>Driver Fees</option>
          <option value="client" <?= $side==='client'?'selected':'' ?>>Client Fees</option>
        </select>
      </div>
      <div class="col-sm-6 col-md-3">
        <label class="form-label">Search</label>
        <input class="form-control"
               name="q"
               placeholder="<?= $group==='partner' ? 'Partner name…' : ($group==='driver' ? 'Driver name…' : '—') ?>"
               value="<?= e($q) ?>"
               <?= $group==='month' ? 'disabled' : '' ?>>
      </div>
      <div class="col-sm-12 col-md-1 d-grid">
        <button class="btn btn-primary">Apply</button>
      </div>
    </form>
  </div>
</div>

<!-- KPI Cards -->
<?php
$drvTotal = $totals['driver_total'];
$cliTotal = $totals['client_total'];
$grand    = $totals['grand_total'];
?>
<div class="row g-3 mb-3">
  <div class="col-12 col-md-4">
    <div class="card shadow-sm h-100 border-dark">
      <div class="card-body">
        <div class="small text-muted">£ Driver Fees (Parking + Waiting)</div>
        <div class="display-6">£<?= number_format($drvTotal, 2) ?></div>
      </div>
    </div>
  </div>
  <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">£ Client Fees (Parking + Waiting)</div>
        <div class="display-6">£<?= number_format($cliTotal, 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 Fees</div>
        <div class="display-6">£<?= number_format($grand, 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">Breakdown by <?= e(ucfirst($group)) ?></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><?= e(ucfirst($group)) ?></th>
            <th class="text-end">£ Driver Parking</th>
            <th class="text-end">£ Driver Waiting</th>
            <th class="text-end">£ Driver Total</th>
            <th class="text-end">£ Client Parking</th>
            <th class="text-end">£ Client Waiting</th>
            <th class="text-end">£ Client Total</th>
            <th class="text-end">£ Grand Total</th>
          </tr>
        </thead>
        <tbody>
          <?php if ($display): foreach ($display as $r): ?>
            <tr>
              <td><?= e($r['label']) ?></td>
              <td class="text-end">£<?= number_format($r['drv_park'], 2) ?></td>
              <td class="text-end">£<?= number_format($r['drv_wait'], 2) ?></td>
              <td class="text-end">£<?= number_format($r['drv_total'], 2) ?></td>
              <td class="text-end">£<?= number_format($r['cli_park'], 2) ?></td>
              <td class="text-end">£<?= number_format($r['cli_wait'], 2) ?></td>
              <td class="text-end">£<?= number_format($r['cli_total'], 2) ?></td>
              <td class="text-end fw-semibold">£<?= number_format($r['grand'], 2) ?></td>
            </tr>
          <?php endforeach; else: ?>
            <tr><td colspan="8" class="text-center text-muted py-4">No results.</td></tr>
          <?php endif; ?>
        </tbody>
        <?php if ($display): ?>
        <tfoot class="table-light">
          <tr>
            <th class="text-end">Totals</th>
            <th class="text-end">£<?= number_format($totalDrvPark, 2) ?></th>
            <th class="text-end">£<?= number_format($totalDrvWait, 2) ?></th>
            <th class="text-end">£<?= number_format($drvTotal, 2) ?></th>
            <th class="text-end">£<?= number_format($totalCliPark, 2) ?></th>
            <th class="text-end">£<?= number_format($totalCliWait, 2) ?></th>
            <th class="text-end">£<?= number_format($cliTotal, 2) ?></th>
            <th class="text-end">£<?= number_format($grand, 2) ?></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">
      <?= $group==='month' ? 'Last Months' : 'Top '.count($top).' ' . ucfirst($group).'s' ?> — Stacked Fees
    </div>
    <canvas id="feesChart" 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('feesChart'); if (!el || typeof Chart==='undefined') return;

  const labels = <?= json_encode(array_values($labels)) ?>;
  const ds1 = <?= json_encode(array_values($ds1)) ?>;
  const ds2 = <?= json_encode(array_values($ds2)) ?>;

  new Chart(el, {
    type: 'bar',
    data: {
      labels,
      datasets: [
        { label: <?= json_encode($legend1) ?>, data: ds1, stack: 'fees' },
        { label: <?= json_encode($legend2) ?>, data: ds2, stack: 'fees' }
      ]
    },
    options: {
      responsive: true,
      plugins: { legend: { position: 'top' } },
      scales: { y: { beginAtZero: true } }
    }
  });
})();
</script>
