<?php
declare(strict_types=1);

/**
 * modules/reports/index.php
 * Reports hub + key charts.
 *
 * Charts:
 *  - Driver Leaderboard (bookings count, Completed only, primary driver)
 *  - Partner Leaderboard (bookings count, Completed only)
 *  - Corporate Leaderboard (revenue & jobs, Completed/Invoiced/Paid)
 *  - Monthly Sales (last 12 months) — line
 *
 * Filters (affect leaderboards): from/to pickup_date
 */

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 ym_list_last_12(): array {
  $out = [];
  $dt = new DateTimeImmutable('first day of this month');
  for ($i = 11; $i >= 0; $i--) {
    $m = $dt->modify("-{$i} months")->format('Y-m');
    $out[] = $m;
  }
  return $out;
}

/* -----------------------------
   Inputs
------------------------------*/
$today      = new DateTimeImmutable('today');
$monthStart = $today->modify('first day of this month')->format('Y-m-01');

$from = (string)($_GET['from'] ?? $monthStart);
$to   = (string)($_GET['to']   ?? $today->format('Y-m-d'));
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $from)) $from = $monthStart;
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $to))   $to   = $today->format('Y-m-d');

/* -----------------------------
   Data: Monthly Sales (last 12 months)
------------------------------*/
$monthlyLabels = ym_list_last_12();
$monthlyMap    = array_fill_keys($monthlyLabels, 0.0);

if (tbl_exists('bookings')) {
  try {
    $q = db()->prepare("
      SELECT DATE_FORMAT(pickup_date, '%Y-%m') ym,
             SUM(COALESCE(total_client_price,0)
               + COALESCE(client_parking_fee,0)
               + COALESCE(client_waiting_fee,0)) AS revenue
      FROM bookings
      WHERE company_id = :cid
        AND pickup_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
        AND status IN ('Completed','Invoiced','Paid')
      GROUP BY ym
      ORDER BY ym ASC
    ");
    $q->execute([':cid'=>$cid]);
    foreach ($q->fetchAll() as $r) {
      $ym = (string)$r['ym'];
      if (isset($monthlyMap[$ym])) $monthlyMap[$ym] = (float)$r['revenue'];
    }
  } catch (Throwable $e) {
    $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? 'Monthly sales: '.$e->getMessage() : 'Unable to load monthly sales.';
  }
} else {
  $errors[] = 'bookings table not found (monthly sales).';
}
$monthlyData = array_values($monthlyMap);

/* -----------------------------
   Data: Driver Leaderboard (Top 10 by bookings)
   - Completed bookings
   - Primary driver only (first by sequence_order / id)
   - Filtered by [from..to]
------------------------------*/
$driversLabels = []; $driversValues = [];
if (tbl_exists('bookings') && tbl_exists('booking_vehicles') && tbl_exists('drivers')) {
  try {
    $sql = "
      SELECT d.id, d.name, COUNT(*) AS jobs
      FROM bookings b
      JOIN booking_vehicles bv ON bv.booking_id = b.id
      JOIN drivers d ON d.id = bv.driver_id
      WHERE b.company_id = :cid
        AND b.status = 'Completed'
        AND b.pickup_date BETWEEN :from AND :to
        /* primary driver only */
        AND NOT EXISTS (
          SELECT 1 FROM booking_vehicles bv2
          WHERE bv2.booking_id = bv.booking_id
            AND (bv2.sequence_order < bv.sequence_order OR (bv2.sequence_order = bv.sequence_order AND bv2.id < bv.id))
        )
      GROUP BY d.id, d.name
      ORDER BY jobs DESC
      LIMIT 10
    ";
    $q = db()->prepare($sql);
    $q->execute([':cid'=>$cid, ':from'=>$from, ':to'=>$to]);
    $rows = $q->fetchAll() ?: [];
    foreach ($rows as $r) {
      $driversLabels[] = (string)($r['name'] ?? ('#'.$r['id']));
      $driversValues[] = (int)$r['jobs'];
    }
  } catch (Throwable $e) {
    $errors[] = (APP_ENV==='dev') ? 'Driver leaderboard: '.$e->getMessage() : 'Unable to load drivers leaderboard.';
  }
}

/* -----------------------------
   Data: Partner Leaderboard (Top 10 by bookings)
------------------------------*/
$partnersLabels = []; $partnersValues = [];
if (tbl_exists('bookings') && tbl_exists('partners')) {
  try {
    $sql = "
      SELECT p.id, p.name, COUNT(*) AS jobs
      FROM bookings b
      JOIN partners p ON p.id = b.partner_id AND p.company_id=b.company_id
      WHERE b.company_id = :cid
        AND b.partner_id IS NOT NULL
        AND b.status = 'Completed'
        AND b.pickup_date BETWEEN :from AND :to
      GROUP BY p.id, p.name
      ORDER BY jobs DESC
      LIMIT 10
    ";
    $q = db()->prepare($sql);
    $q->execute([':cid'=>$cid, ':from'=>$from, ':to'=>$to]);
    $rows = $q->fetchAll() ?: [];
    foreach ($rows as $r) {
      $partnersLabels[] = (string)($r['name'] ?? ('#'.$r['id']));
      $partnersValues[] = (int)$r['jobs'];
    }
  } catch (Throwable $e) {
    $errors[] = (APP_ENV==='dev') ? 'Partner leaderboard: '.$e->getMessage() : 'Unable to load partners leaderboard.';
  }
}

/* -----------------------------
   Data: Corporate Leaderboard (Top 10 by revenue)
------------------------------*/
$corpLabels = []; $corpValues = []; $corpJobs = [];
if (tbl_exists('bookings') && tbl_exists('corporates')) {
  try {
    $sql = "
      SELECT c.id, c.name,
             COUNT(*) AS jobs,
             SUM(COALESCE(b.total_client_price,0)
               + COALESCE(b.client_parking_fee,0)
               + COALESCE(b.client_waiting_fee,0)) AS revenue
      FROM bookings b
      JOIN corporates c ON c.id = b.corporate_id
      WHERE b.company_id = :cid
        AND b.corporate_id IS NOT NULL
        AND b.status IN ('Completed','Invoiced','Paid')
        AND b.pickup_date BETWEEN :from AND :to
      GROUP BY c.id, c.name
      ORDER BY revenue DESC
      LIMIT 10
    ";
    $q = db()->prepare($sql);
    $q->execute([':cid'=>$cid, ':from'=>$from, ':to'=>$to]);
    $rows = $q->fetchAll() ?: [];
    foreach ($rows as $r) {
      $corpLabels[] = (string)($r['name'] ?? ('#'.$r['id']));
      $corpValues[] = (float)$r['revenue'];
      $corpJobs[]   = (int)$r['jobs'];
    }
  } catch (Throwable $e) {
    $errors[] = (APP_ENV==='dev') ? 'Corporate leaderboard: '.$e->getMessage() : 'Unable to load corporates leaderboard.';
  }
}

/* -----------------------------
   Render
------------------------------*/
include dirname(__DIR__, 2) . '/includes/header.php';
?>
<style>
  .chart-card { min-height: 360px; }
  .chart-box { height: 300px; }
  @media print { .no-print { display:none !important; } }
</style>

<div class="d-flex justify-content-between align-items-center mb-3">
  <div>
    <h1 class="h4 mb-0">Reports</h1>
    <div class="text-muted">Visual overview + shortcuts to detailed reports.</div>
  </div>
  <div>
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('dashboard/md.php')) ?>">← MD Dashboard</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-4 col-md-3">
        <label class="form-label">From</label>
        <input type="date" class="form-control" name="from" value="<?= e($from) ?>">
      </div>
      <div class="col-sm-4 col-md-3">
        <label class="form-label">To</label>
        <input type="date" class="form-control" name="to" value="<?= e($to) ?>">
      </div>
      <div class="col-sm-4 col-md-2 d-grid">
        <button class="btn btn-primary">Apply</button>
      </div>
      <div class="col-md-4 text-muted small">
        Leaderboards respect this range. Monthly Sales shows last 12 months.
      </div>
    </form>
  </div>
</div>

<!-- Charts -->
<div class="row g-3">
  <div class="col-12">
    <div class="card shadow-sm chart-card">
      <div class="card-body">
        <div class="d-flex justify-content-between align-items-center mb-2">
          <div class="fw-semibold">Monthly Sales (last 12 months)</div>
        </div>
        <div class="chart-box"><canvas id="chSales"></canvas></div>
      </div>
    </div>
  </div>

  <div class="col-lg-6">
    <div class="card shadow-sm chart-card">
      <div class="card-body">
        <div class="d-flex justify-content-between align-items-center mb-2">
          <div class="fw-semibold">Driver Leaderboard (by bookings)</div>
          <div class="small text-muted"><?= e($from) ?> → <?= e($to) ?></div>
        </div>
        <div class="chart-box"><canvas id="chDrivers"></canvas></div>
      </div>
    </div>
  </div>

  <div class="col-lg-6">
    <div class="card shadow-sm chart-card">
      <div class="card-body">
        <div class="d-flex justify-content-between align-items-center mb-2">
          <div class="fw-semibold">Partner Leaderboard (by bookings)</div>
          <div class="small text-muted"><?= e($from) ?> → <?= e($to) ?></div>
        </div>
        <div class="chart-box"><canvas id="chPartners"></canvas></div>
      </div>
    </div>
  </div>

  <div class="col-12">
    <div class="card shadow-sm chart-card">
      <div class="card-body">
        <div class="d-flex justify-content-between align-items-center mb-2">
          <div class="fw-semibold">Corporate Leaderboard (by revenue)</div>
          <div class="small text-muted"><?= e($from) ?> → <?= e($to) ?></div>
        </div>
        <div class="chart-box"><canvas id="chCorporates"></canvas></div>
      </div>
    </div>
  </div>
</div>

<!-- Quick Links to Detailed Reports (we'll implement these next) -->
<div class="card shadow-sm mt-3">
  <div class="card-body">
    <div class="row g-2">
     <?php
  // label => target php (to be implemented)
  $links = [
    'Transactions Summary'                  => 'reports/transactions_summary.php',
    'Driver Payouts (Paid vs Outstanding)'  => 'reports/payouts_drivers.php',
    'Partner Payouts (Paid vs Outstanding)' => 'reports/payouts_partners.php',
    'Pending Payments Aging'                => 'reports/pending_aging.php',
    'Cost Breakdown: Parking & Waiting'     => 'reports/costs_fees.php',
    'Cash Outflow Timeline'                 => 'reports/cash_outflow.php',
    'Revenue vs Driver Cost (Gross Margin)' => 'reports/gross_margin.php',
    'Net Profit'                            => 'reports/net_profit.php',
    'Bookings by Status & Volume'           => 'reports/bookings_status.php',
    'Partner Performance'                   => 'reports/partners_performance.php',
    'Corporate Accounts Performance'        => 'reports/corporates_performance.php',
    'Job Mix & Peak Times'                  => 'reports/job_mix.php',
    'Average Fees per Job'                  => 'reports/avg_fees.php',
    'Driver Utilization & Earnings'         => 'reports/driver_utilization.php',
    'Driver Leaderboard (detail)'           => 'reports/driver_leaderboard.php',
  ];
  foreach ($links as $label => $path):
?>

        <div class="col-12 col-sm-6 col-lg-4">
          <a class="btn btn-outline-primary w-100" href="<?= e(url_modules($path)) ?>"><?= e($label) ?></a>
        </div>
      <?php endforeach; ?>
    </div>
  </div>
</div>

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

<!-- Chart.js (local fallback then CDN) -->
<script src="<?= e(url_public('assets/chart.umd.min.js')) ?>"></script>
<script>
if (typeof window.Chart === 'undefined') {
  var s = document.createElement('script');
  s.src = 'https://cdn.jsdelivr.net/npm/chart.js@4.4.1/dist/chart.umd.min.js';
  document.head.appendChild(s);
}
</script>

<script>
// Embed data from PHP
const salesLabels   = <?= json_encode($monthlyLabels) ?>;
const salesValues   = <?= json_encode(array_map(fn($v)=>round((float)$v,2), $monthlyData)) ?>;

const drvLabels     = <?= json_encode($driversLabels) ?>;
const drvValues     = <?= json_encode($driversValues) ?>;

const parLabels     = <?= json_encode($partnersLabels) ?>;
const parValues     = <?= json_encode($partnersValues) ?>;

const corpLabels    = <?= json_encode($corpLabels) ?>;
const corpRevenue   = <?= json_encode(array_map(fn($v)=>round((float)$v,2), $corpValues)) ?>;
const corpJobs      = <?= json_encode($corpJobs) ?>;

function whenChartReady(fn){
  if (window.Chart) { fn(); return; }
  const iv = setInterval(()=>{ if(window.Chart){ clearInterval(iv); fn(); } }, 50);
}

whenChartReady(function(){
  // Monthly Sales Line
  new Chart(document.getElementById('chSales'), {
    type: 'line',
    data: {
      labels: salesLabels,
      datasets: [{
        label: 'Revenue (£)',
        data: salesValues,
        tension: 0.2
      }]
    },
    options: {
      responsive: true,
      maintainAspectRatio: false,
      plugins: { legend: { display: true } },
      scales: {
        y: { beginAtZero: true, ticks: { callback: v => '£' + v } }
      }
    }
  });

  // Drivers (bar)
  new Chart(document.getElementById('chDrivers'), {
    type: 'bar',
    data: {
      labels: drvLabels,
      datasets: [{ label: 'Bookings', data: drvValues }]
    },
    options: {
      responsive: true, maintainAspectRatio: false,
      plugins: { legend: { display:false } },
      scales: { y: { beginAtZero:true } }
    }
  });

  // Partners (bar)
  new Chart(document.getElementById('chPartners'), {
    type: 'bar',
    data: {
      labels: parLabels,
      datasets: [{ label: 'Bookings', data: parValues }]
    },
    options: {
      responsive: true, maintainAspectRatio: false,
      plugins: { legend: { display:false } },
      scales: { y: { beginAtZero:true } }
    }
  });

  // Corporates (dual: revenue bars + jobs line)
  new Chart(document.getElementById('chCorporates'), {
    type: 'bar',
    data: {
      labels: corpLabels,
      datasets: [
        { type:'bar',  label: 'Revenue (£)', data: corpRevenue, yAxisID: 'y' },
        { type:'line', label: 'Jobs', data: corpJobs, yAxisID: 'y1', tension: 0.2 }
      ]
    },
    options: {
      responsive: true, maintainAspectRatio: false,
      plugins: { legend: { display:true } },
      scales: {
        y:  { beginAtZero:true, position:'left',  ticks:{ callback: v => '£'+v } },
        y1: { beginAtZero:true, position:'right', grid:{ drawOnChartArea:false } }
      }
    }
  });
});
</script>
