<?php
declare(strict_types=1);

/**
 * modules/reports/gross_margin.php
 *
 * Revenue vs Driver Cost (Gross Margin)
 *
 * Source: bookings
 *   revenue = total_client_price (+ client_parking_fee + client_waiting_fee if include_fees=1)
 *   cost    = total_driver_price + driver_parking_fee + driver_waiting_fee + partner_commission_amount
 *
 * Filters:
 *   - Date range (pickup_date)
 *   - Status: Completed (default) | Invoiced/Paid | All
 *   - Group by: Month | Day | Partner | Corporate | Booking Type
 *   - Partner (optional)
 *   - Corporate (optional)
 *   - Include client fees in revenue (checkbox)
 *
 * Outputs:
 *   - KPI cards (Revenue, Cost, Gross Margin, GM%)
 *   - Grouped table with Revenue/Cost/Margin/Margin%
 *   - Chart (line if Month/Day, bar otherwise)
 *   - CSV export of grouped rows
 */

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 safe_num($v): float { return (float)$v; }

/* -----------------------------
   Filter 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'] ?? 'completed'); // completed | invoiced_paid | all
$group  = (string)($_GET['group']  ?? 'month');     // month | day | partner | corporate | booking_type
$pid    = (int)($_GET['partner_id']  ?? 0);
$coid   = (int)($_GET['corporate_id']?? 0);
$incFees= (int)($_GET['include_fees'] ?? 1);        // 1=include client fees in revenue
$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, ['completed','invoiced_paid','all'], true)) $status = 'completed';
if (!in_array($group, ['month','day','partner','corporate','booking_type'], true)) $group = 'month';

$fromSQL = $from;  // bookings.pickup_date is a DATE
$toSQL   = $to;

/* -----------------------------
   Lookups (partners, corporates)
------------------------------*/
$partners = [];
$corps    = [];
try {
  $p = db()->prepare("SELECT id, name FROM partners WHERE company_id=:cid ORDER BY name ASC LIMIT 1000");
  $p->execute([':cid'=>$cid]);
  $partners = $p->fetchAll() ?: [];
} catch (Throwable) {}

try {
  $c = db()->prepare("SELECT id, name FROM corporates WHERE company_id=:cid ORDER BY name ASC LIMIT 1000");
  $c->execute([':cid'=>$cid]);
  $corps = $c->fetchAll() ?: [];
} catch (Throwable) {}

/* -----------------------------
   WHERE for bookings
------------------------------*/
$where = ["b.company_id=:cid", "b.pickup_date BETWEEN :f AND :t"];
$args  = [':cid'=>$cid, ':f'=>$fromSQL, ':t'=>$toSQL];

if ($status === 'completed') {
  $where[] = "b.status='Completed'";
} elseif ($status === 'invoiced_paid') {
  $where[] = "b.status IN ('Invoiced','Paid')";
}

if ($pid > 0)  { $where[] = "b.partner_id=:pid";     $args[':pid']=$pid; }
if ($coid > 0) { $where[] = "b.corporate_id=:coid";  $args[':coid']=$coid; }

$whereSql = implode(' AND ', $where);

/* -----------------------------
   Group expressions
------------------------------*/
switch ($group) {
  case 'day':
    $grpLabel = "DATE_FORMAT(b.pickup_date, '%Y-%m-%d')";
    $grpSort  = "b.pickup_date";
    $grpTitle = 'Day';
    break;

  case 'partner':
    $grpLabel = "COALESCE(p.name, CONCAT('#', b.partner_id))";
    $grpSort  = "COALESCE(p.name, CONCAT('#', b.partner_id))";
    $grpTitle = 'Partner';
    break;

  case 'corporate':
    $grpLabel = "COALESCE(c.name, CONCAT('#', b.corporate_id))";
    $grpSort  = "COALESCE(c.name, CONCAT('#', b.corporate_id))";
    $grpTitle = 'Corporate';
    break;

  case 'booking_type':
    $grpLabel = "b.booking_type";
    $grpSort  = "b.booking_type";
    $grpTitle = 'Booking Type';
    break;

  case 'month':
  default:
    $grpLabel = "DATE_FORMAT(b.pickup_date, '%Y-%m')";
    $grpSort  = "DATE_FORMAT(b.pickup_date, '%Y-%m')";
    $grpTitle = 'Month';
    $group = 'month';
    break;
}

/* -----------------------------
   Revenue / Cost expressions
------------------------------*/
$revExpr = "COALESCE(b.total_client_price,0)"
         . ($incFees ? " + COALESCE(b.client_parking_fee,0) + COALESCE(b.client_waiting_fee,0)" : "");

$costExpr = "COALESCE(b.total_driver_price,0)"
          . " + COALESCE(b.driver_parking_fee,0)"
          . " + COALESCE(b.driver_waiting_fee,0)"
          . " + COALESCE(b.partner_commission_amount,0)";

/* -----------------------------
   Query grouped data
------------------------------*/
$rows = [];
try {
  $sql = "
    SELECT t.grp_label, MIN(t.grp_sort) AS grp_sort,
           SUM(t.revenue) AS revenue, SUM(t.cost) AS cost, COUNT(*) AS jobs
    FROM (
      SELECT
        $grpLabel AS grp_label,
        $grpSort  AS grp_sort,
        ($revExpr) AS revenue,
        ($costExpr) AS cost
      FROM bookings b
      LEFT JOIN partners  p ON p.id=b.partner_id   AND p.company_id=b.company_id
      LEFT JOIN corporates c ON c.id=b.corporate_id AND c.company_id=b.company_id
      WHERE $whereSql
    ) t
    GROUP BY t.grp_label
    ORDER BY " . (($group==='month' || $group==='day') ? "grp_sort ASC" : "revenue DESC") . "
    LIMIT 1000
  ";
  $q = db()->prepare($sql);
  $q->execute($args);
  $rows = $q->fetchAll() ?: [];
} catch (Throwable $e) {
  $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? $e->getMessage() : 'Unable to load margin data.';
}

/* -----------------------------
   Totals + chart arrays
------------------------------*/
$totalRev = 0.0; $totalCost = 0.0; $labels=[]; $revSeries=[]; $costSeries=[]; $gmSeries=[];
foreach ($rows as &$r) {
  $r['revenue'] = safe_num($r['revenue']);
  $r['cost']    = safe_num($r['cost']);
  $r['gm']      = $r['revenue'] - $r['cost'];
  $r['gm_pct']  = $r['revenue'] > 0.00001 ? ($r['gm'] / $r['revenue'] * 100.0) : 0.0;

  $totalRev  += $r['revenue'];
  $totalCost += $r['cost'];

  $labels[]    = (string)$r['grp_label'];
  $revSeries[] = number_format($r['revenue'], 2, '.', '');
  $costSeries[]= number_format($r['cost'], 2, '.', '');
  $gmSeries[]  = number_format($r['gm'], 2, '.', '');
}
unset($r);
$totalGM   = $totalRev - $totalCost;
$totalGMPc = $totalRev > 0.00001 ? ($totalGM / $totalRev * 100.0) : 0.0;

/* -----------------------------
   CSV export
------------------------------*/
if ($export === 'csv') {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="gross_margin_'.date('Ymd_His').'.csv"');
  $out = fopen('php://output', 'w');
  fputcsv($out, [$grpTitle, 'Jobs', 'Revenue', 'Cost', 'Gross Margin', 'GM %']);
  foreach ($rows as $r) {
    fputcsv($out, [
      (string)$r['grp_label'],
      (int)($r['jobs'] ?? 0),
      number_format($r['revenue'], 2, '.', ''),
      number_format($r['cost'], 2, '.', ''),
      number_format($r['gm'], 2, '.', ''),
      number_format($r['gm_pct'], 2, '.', '')
    ]);
  }
  // Totals line
  fputcsv($out, [
    'TOTAL',
    array_sum(array_map(fn($x)=>(int)($x['jobs']??0), $rows)),
    number_format($totalRev, 2, '.', ''),
    number_format($totalCost, 2, '.', ''),
    number_format($totalGM, 2, '.', ''),
    number_format($totalGMPc, 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">Revenue vs Driver Cost (Gross Margin)</h1>
    <div class="text-muted">From bookings (pickup date). Status: <?= e(ucfirst(str_replace('_', ' / ', $status))) ?>.</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-3">
        <label class="form-label">From</label>
        <input type="date" class="form-control" name="from" value="<?= e($from) ?>">
      </div>
      <div class="col-sm-3">
        <label class="form-label">To</label>
        <input type="date" class="form-control" name="to" value="<?= e($to) ?>">
      </div>
      <div class="col-sm-3">
        <label class="form-label">Status</label>
        <select class="form-select" name="status">
          <option value="completed"     <?= $status==='completed'?'selected':'' ?>>Completed</option>
          <option value="invoiced_paid" <?= $status==='invoiced_paid'?'selected':'' ?>>Invoiced / Paid</option>
          <option value="all"           <?= $status==='all'?'selected':'' ?>>All</option>
        </select>
      </div>
      <div class="col-sm-3">
        <label class="form-label">Group By</label>
        <select class="form-select" name="group">
          <option value="month"        <?= $group==='month'?'selected':'' ?>>Month</option>
          <option value="day"          <?= $group==='day'?'selected':'' ?>>Day</option>
          <option value="partner"      <?= $group==='partner'?'selected':'' ?>>Partner</option>
          <option value="corporate"    <?= $group==='corporate'?'selected':'' ?>>Corporate</option>
          <option value="booking_type" <?= $group==='booking_type'?'selected':'' ?>>Booking Type</option>
        </select>
      </div>

      <div class="col-sm-3">
        <label class="form-label">Partner</label>
        <select class="form-select" name="partner_id">
          <option value="0">— All —</option>
          <?php foreach($partners as $p): ?>
            <option value="<?= (int)$p['id'] ?>" <?= $pid===(int)$p['id']?'selected':'' ?>><?= e($p['name'] ?? ('#'.$p['id'])) ?></option>
          <?php endforeach; ?>
        </select>
      </div>
      <div class="col-sm-3">
        <label class="form-label">Corporate</label>
        <select class="form-select" name="corporate_id">
          <option value="0">— All —</option>
          <?php foreach($corps as $c): ?>
            <option value="<?= (int)$c['id'] ?>" <?= $coid===(int)$c['id']?'selected':'' ?>><?= e($c['name'] ?? ('#'.$c['id'])) ?></option>
          <?php endforeach; ?>
        </select>
      </div>

      <div class="col-sm-3">
        <label class="form-label">Revenue Includes Client Fees</label>
        <div class="form-check">
          <input class="form-check-input" type="checkbox" name="include_fees" value="1" id="incFees" <?= $incFees? 'checked':'' ?>>
          <label class="form-check-label" for="incFees">Client Parking + Waiting</label>
        </div>
      </div>

      <div class="col-sm-3 d-grid">
        <button class="btn btn-primary">Apply Filters</button>
      </div>
    </form>
  </div>
</div>

<!-- KPIs -->
<div class="row g-3 mb-3">
  <div class="col-12 col-lg-3">
    <div class="card shadow-sm h-100 border-primary">
      <div class="card-body">
        <div class="small text-muted">Revenue</div>
        <div class="display-6">£<?= number_format($totalRev, 2) ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-lg-3">
    <div class="card shadow-sm h-100 border-danger">
      <div class="card-body">
        <div class="small text-muted">Cost</div>
        <div class="display-6">£<?= number_format($totalCost, 2) ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-lg-3">
    <div class="card shadow-sm h-100 border-success">
      <div class="card-body">
        <div class="small text-muted">Gross Margin</div>
        <div class="display-6">£<?= number_format($totalGM, 2) ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-lg-3">
    <div class="card shadow-sm h-100 border-info">
      <div class="card-body">
        <div class="small text-muted">GM %</div>
        <div class="display-6"><?= number_format($totalGMPc, 1) ?>%</div>
      </div>
    </div>
  </div>
</div>

<!-- Grouped 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">Grouped by <?= e($grpTitle) ?></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($grpTitle) ?></th>
            <th class="text-end">Jobs</th>
            <th class="text-end">£ Revenue</th>
            <th class="text-end">£ Cost</th>
            <th class="text-end">£ Margin</th>
            <th class="text-end">GM %</th>
          </tr>
        </thead>
        <tbody>
          <?php if ($rows): foreach ($rows as $r): ?>
            <tr>
              <td><?= e((string)$r['grp_label']) ?></td>
              <td class="text-end"><?= number_format((int)($r['jobs'] ?? 0)) ?></td>
              <td class="text-end">£<?= number_format((float)$r['revenue'], 2) ?></td>
              <td class="text-end">£<?= number_format((float)$r['cost'], 2) ?></td>
              <td class="text-end fw-semibold">£<?= number_format((float)$r['gm'], 2) ?></td>
              <td class="text-end"><?= number_format((float)$r['gm_pct'], 1) ?>%</td>
            </tr>
          <?php endforeach; else: ?>
            <tr><td colspan="6" class="text-center text-muted py-4">No data for the selected filters.</td></tr>
          <?php endif; ?>
        </tbody>
        <?php if ($rows): ?>
        <tfoot class="table-light">
          <tr>
            <th>Total</th>
            <th class="text-end"><?= number_format(array_sum(array_map(fn($x)=>(int)($x['jobs']??0), $rows))) ?></th>
            <th class="text-end">£<?= number_format($totalRev, 2) ?></th>
            <th class="text-end">£<?= number_format($totalCost, 2) ?></th>
            <th class="text-end">£<?= number_format($totalGM, 2) ?></th>
            <th class="text-end"><?= number_format($totalGMPc, 1) ?>%</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">Trend</div>
    <canvas id="gmChart" height="110"></canvas>
  </div>
</div>

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

<!-- Chart.js (safe: if blocked, chart simply won't render) -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
(function(){
  const ctx = document.getElementById('gmChart');
  if (!ctx || typeof Chart === 'undefined') return;

  const labels = <?= json_encode($labels) ?>;
  const rev    = <?= json_encode($revSeries) ?>.map(Number);
  const cost   = <?= json_encode($costSeries) ?>.map(Number);
  const gm     = <?= json_encode($gmSeries) ?>.map(Number);

  const type = <?= json_encode(($group==='month' || $group==='day') ? 'line' : 'bar') ?>;

  new Chart(ctx, {
    type,
    data: {
      labels,
      datasets: [
        { label: 'Revenue', data: rev },
        { label: 'Cost',    data: cost },
        { label: 'Margin',  data: gm }
      ]
    },
    options: {
      responsive: true,
      plugins: { legend: { position: 'top' } },
      scales: {
        y: { beginAtZero: true }
      }
    }
  });
})();
</script>
