<?php
declare(strict_types=1);

/**
 * modules/reports/corporates_performance.php
 *
 * Corporate Accounts Performance
 *
 * Filters:
 *  - Date range (bookings.pickup_date)
 *  - Search by corporate name (if corporates table exists)
 *  - Minimum jobs threshold
 *  - Include client fees in revenue (client_parking_fee + client_waiting_fee)
 *
 * Logic (Completed bookings only):
 *  revenue = total_client_price [+ client fees if toggled]
 *  jobs    = COUNT(bookings)
 *  avg_ticket = revenue / jobs
 *
 * Output:
 *  - KPI cards (Total Corporates, Jobs, Revenue, Avg Ticket)
 *  - Table (corporate, jobs, revenue, avg)
 *  - Top-10 corporates by revenue (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'] ?? ''));                 // corporate name
$minJobs = (int)($_GET['min_jobs'] ?? 0);
$includeFees = (int)($_GET['include_fees'] ?? 0) === 1 ? 1 : 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;

$hasCorporates = tbl_exists('corporates');

/* -----------------------------
   Build query
------------------------------*/
$feesExpr = $includeFees
  ? " + COALESCE(b.client_parking_fee,0) + COALESCE(b.client_waiting_fee,0) "
  : "";

$where = [
  "b.company_id = :cid",
  "b.pickup_date BETWEEN :f AND :t",
  "b.status = 'Completed'",
  "b.corporate_id IS NOT NULL"
];
$args  = [':cid'=>$cid, ':f'=>$from, ':t'=>$to];

$join  = "";
$corpNameSel = "CONCAT('#', b.corporate_id)";

if ($hasCorporates) {
  $join = "LEFT JOIN corporates c ON c.id = b.corporate_id";
  $corpNameSel = "COALESCE(c.name, CONCAT('#', b.corporate_id))";
  if ($q !== '') {
    $where[] = "c.name LIKE :q";
    $args[':q'] = '%'.$q.'%';
  }
}

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

$sql = "
  SELECT
    b.corporate_id,
    {$corpNameSel} AS corporate_name,
    COUNT(*) AS jobs,
    SUM(COALESCE(b.total_client_price,0) {$feesExpr}) AS revenue
  FROM bookings b
  {$join}
  WHERE {$whereSql}
  GROUP BY b.corporate_id, corporate_name
  HAVING jobs >= :minjobs
  ORDER BY revenue DESC, corporate_name ASC
  LIMIT 1000
";
$args[':minjobs'] = max(0, $minJobs);

$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 corporate performance.';
}

/* -----------------------------
   KPIs & CSV Export
------------------------------*/
$totalCorps = count($rows);
$totalJobs  = 0;
$totalRev   = 0.0;

foreach ($rows as $r) {
  $totalJobs += (int)$r['jobs'];
  $totalRev  += (float)$r['revenue'];
}

$avgTicket = $totalJobs > 0 ? ($totalRev / $totalJobs) : 0.0;

if ($export === 'csv') {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="corporates_performance_'.date('Ymd_His').'.csv"');
  $out = fopen('php://output', 'w');
  fputcsv($out, [
    'Period From', $from,
    'To', $to,
    'Include Fees', $includeFees ? 'Yes' : 'No',
    'Min Jobs', $minJobs
  ]);
  fputcsv($out, []); // blank line
  fputcsv($out, ['Corporate','Jobs','Revenue (£)','Avg Ticket (£)']);
  foreach ($rows as $r) {
    $jobs = (int)$r['jobs'];
    $rev  = (float)$r['revenue'];
    $avg  = $jobs>0 ? ($rev/$jobs) : 0.0;
    fputcsv($out, [
      (string)$r['corporate_name'],
      $jobs,
      number_format($rev, 2, '.', ''),
      number_format($avg, 2, '.', '')
    ]);
  }
  fputcsv($out, []); // blank
  fputcsv($out, ['Totals', $totalJobs, number_format($totalRev,2,'.',''), number_format($avgTicket,2,'.','')]);
  fclose($out);
  exit;
}

/* -----------------------------
   Chart data (Top 10 by revenue)
------------------------------*/
$top = array_slice($rows, 0, 10);
$chartLabels = array_map(fn($r)=> (string)$r['corporate_name'], $top);
$chartData   = array_map(fn($r)=> round((float)$r['revenue'], 2), $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">Corporate Accounts — Performance</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">Corporate</label>
        <input class="form-control" name="q" placeholder="<?= tbl_exists('corporates') ? 'Name contains…' : 'Name (requires corporates 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 Client Fees</label>
        <select class="form-select" name="include_fees">
          <option value="0" <?= $includeFees? '' : 'selected' ?>>No</option>
          <option value="1" <?= $includeFees? 'selected' : '' ?>>Yes</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">Corporates</div>
        <div class="display-6"><?= number_format($totalCorps) ?></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">Revenue</div>
        <div class="display-6">£<?= number_format($totalRev, 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">Avg Ticket</div>
        <div class="display-6">£<?= number_format($avgTicket, 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">Corporates (Top by Revenue)</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>Corporate</th>
            <th class="text-end">Jobs</th>
            <th class="text-end">£ Revenue</th>
            <th class="text-end">£ Avg Ticket</th>
          </tr>
        </thead>
        <tbody>
          <?php if ($rows): foreach ($rows as $r):
            $jobs = (int)$r['jobs']; $rev = (float)$r['revenue']; $avg = $jobs>0 ? $rev/$jobs : 0.0;
          ?>
          <tr>
            <td><?= e($r['corporate_name'] ?? ('#'.(int)$r['corporate_id'])) ?></td>
            <td class="text-end"><?= number_format($jobs) ?></td>
            <td class="text-end">£<?= number_format($rev, 2) ?></td>
            <td class="text-end">£<?= number_format($avg, 2) ?></td>
          </tr>
          <?php endforeach; else: ?>
            <tr><td colspan="4" 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($totalRev, 2) ?></th>
            <th class="text-end">£<?= number_format($avgTicket, 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">Top 10 Corporates by Revenue</div>
    <canvas id="corpChart" 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('corpChart'); if (!el || typeof Chart==='undefined') return;

  const labels = <?= json_encode(array_values($chartLabels)) ?>;
  const data   = <?= json_encode(array_values($chartData)) ?>;

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