<?php
declare(strict_types=1);

/**
 * modules/reports/partners_performance.php
 *
 * Partner Performance dashboard
 * - KPIs (partners, jobs, commission due, paid, outstanding)
 * - Filters: date range (pickup_date), partner, search
 * - Charts: Top partners by Jobs & by Commission
 * - Table per partner with Jobs, Client Revenue, Commission (avg), Paid, Outstanding
 * - CSV export
 * - Quick Bulk Pay link per partner
 */

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);
$partnerId = (int)($_GET['partner_id'] ?? 0);
$q         = trim((string)($_GET['q'] ?? '')); // search partners.name
$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;

/* -----------------------------
   Partner list (filter)
------------------------------*/
$partnerList = [];
try {
  $ps = db()->prepare("SELECT id, name FROM partners WHERE company_id=:cid ORDER BY name ASC");
  $ps->execute([':cid'=>$cid]);
  $partnerList = $ps->fetchAll() ?: [];
} catch (Throwable) {}

/* -----------------------------
   Query & aggregate
------------------------------*/
$hasPayouts = tbl_exists('partner_payouts');

$rows = []; // grouped by partner at SQL level
try {
  $where = [
    "b.company_id = :cid",
    "b.status = 'Completed'",
    "b.partner_id IS NOT NULL",
    "b.pickup_date BETWEEN :from AND :to"
  ];
  $args  = [':cid'=>$cid, ':from'=>$from, ':to'=>$to];

  if ($partnerId > 0) { $where[] = "p.id = :pid"; $args[':pid'] = $partnerId; }
  if ($q !== '') { $where[] = "p.name LIKE :q"; $args[':q'] = '%'.$q.'%'; }

  // Sum paid per booking via correlated subquery, then group per partner
  $paidPerBooking = $hasPayouts
    ? "(SELECT COALESCE(SUM(pp.amount),0) FROM partner_payouts pp
         WHERE pp.company_id=b.company_id AND pp.booking_id=b.id)"
    : "0";

  $sql = "
    SELECT
      p.id   AS partner_id,
      p.name AS partner_name,
      COUNT(*) AS jobs_count,
      SUM(COALESCE(b.partner_commission_amount,0)) AS total_comm,
      SUM(COALESCE(b.total_client_price,0) + COALESCE(b.client_parking_fee,0) + COALESCE(b.client_waiting_fee,0)) AS client_revenue,
      SUM({$paidPerBooking}) AS paid_sum
    FROM bookings b
    JOIN partners p ON p.id = b.partner_id AND p.company_id = b.company_id
    WHERE ".implode(' AND ', $where)."
    GROUP BY p.id, p.name
    ORDER BY total_comm DESC, jobs_count DESC, p.name ASC
    LIMIT 1000
  ";

  $stmt = db()->prepare($sql);
  $stmt->execute($args);
  $rows = $stmt->fetchAll() ?: [];

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

/* -----------------------------
   Compute totals + enrich
------------------------------*/
$partnersCount = 0;
$totalJobs     = 0;
$sumComm       = 0.0;
$sumPaid       = 0.0;
$sumOut        = 0.0;

foreach ($rows as &$r) {
  $jobs  = (int)($r['jobs_count'] ?? 0);
  $comm  = (float)($r['total_comm'] ?? 0);
  $paid  = (float)($r['paid_sum'] ?? 0);
  $rev   = (float)($r['client_revenue'] ?? 0);
  $out   = max(0.0, round($comm - $paid, 2));
  $avg   = $jobs > 0 ? round($comm / $jobs, 2) : 0.0;
  $share = $rev > 0 ? round(100 * $comm / $rev, 2) : null;

  $r['avg_comm']   = $avg;
  $r['outstanding']= $out;
  $r['share_pct']  = $share;

  $partnersCount++;
  $totalJobs += $jobs;
  $sumComm   += $comm;
  $sumPaid   += $paid;
  $sumOut    += $out;
}
unset($r);

/* -----------------------------
   Charts data
------------------------------*/
$topByJobs = $rows;
usort($topByJobs, fn($a,$b)=>($b['jobs_count'] <=> $a['jobs_count']));
$topByJobs = array_slice($topByJobs, 0, 10);

$topByComm = $rows;
usort($topByComm, fn($a,$b)=>((float)$b['total_comm'] <=> (float)$a['total_comm']));
$topByComm = array_slice($topByComm, 0, 10);

/* -----------------------------
   CSV Export
------------------------------*/
if ($export === 'csv') {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="partners_performance_'.date('Ymd_His').'.csv"');
  $out = fopen('php://output', 'w');
  fputcsv($out, ['From',$from,'To',$to,'PartnerID',$partnerId,'Search',$q]);
  fputcsv($out, []);
  fputcsv($out, ['Partner','Jobs','Client Revenue £','Commission £','Avg Commission £','Paid £','Outstanding £','Commission % of Revenue']);
  foreach ($rows as $r) {
    fputcsv($out, [
      (string)($r['partner_name'] ?? '—'),
      (int)$r['jobs_count'],
      number_format((float)$r['client_revenue'],2,'.',''),
      number_format((float)$r['total_comm'],2,'.',''),
      number_format((float)$r['avg_comm'],2,'.',''),
      number_format((float)$r['paid_sum'],2,'.',''),
      number_format((float)$r['outstanding'],2,'.',''),
      ($r['share_pct'] === null ? '' : number_format((float)$r['share_pct'],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">Partner Performance</h1>
    <div class="text-muted">Completed bookings only · Range: <?= e($from) ?> → <?= e($to) ?></div>
    <?php if (!$hasPayouts): ?>
      <div class="small text-warning mt-1">Note: <code>partner_payouts</code> table not found — “Paid” will show as £0, and “Outstanding” equals total commission.</div>
    <?php endif; ?>
  </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-6 col-md-3">
        <label class="form-label">From</label>
        <input type="date" class="form-control" name="from" value="<?= e($from) ?>">
      </div>
      <div class="col-6 col-md-3">
        <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">Partner</label>
        <select class="form-select" name="partner_id">
          <option value="0" <?= $partnerId===0?'selected':'' ?>>— Any —</option>
          <?php foreach($partnerList as $p): ?>
            <option value="<?= (int)$p['id'] ?>" <?= $partnerId===(int)$p['id']?'selected':'' ?>>
              <?= e($p['name'] ?? ('#'.$p['id'])) ?>
            </option>
          <?php endforeach; ?>
        </select>
      </div>
      <div class="col-12 col-md-3">
        <label class="form-label">Search</label>
        <input class="form-control" name="q" placeholder="Partner name" value="<?= e($q) ?>">
      </div>
      <div class="col-12 col-md-2 d-grid">
        <button class="btn btn-primary">Apply</button>
      </div>
    </form>
  </div>
</div>

<!-- KPIs -->
<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">Partners</div>
        <div class="display-6"><?= number_format($partnersCount) ?></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">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">Commission Due</div>
        <div class="display-6">£<?= number_format($sumComm, 2) ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-md-3">
    <div class="card shadow-sm h-100 border-warning">
      <div class="card-body">
        <div class="small text-muted">Outstanding</div>
        <div class="display-6">£<?= number_format($sumOut, 2) ?></div>
      </div>
    </div>
  </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">Top Partners by Jobs</div>
        <canvas id="jobsChart" 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">Top Partners by Commission (£)</div>
        <canvas id="commChart" height="160"></canvas>
      </div>
    </div>
  </div>
</div>

<!-- Table -->
<div class="card shadow-sm">
  <div class="table-responsive">
    <table class="table align-middle mb-0">
      <thead class="table-light">
        <tr>
          <th>Partner</th>
          <th class="text-end">Jobs</th>
          <th class="text-end">Client Revenue £</th>
          <th class="text-end">Commission £</th>
          <th class="text-end">Avg / Job £</th>
          <th class="text-end">Paid £</th>
          <th class="text-end">Outstanding £</th>
          <th class="text-end">Comm % of Rev</th>
          <th class="text-end">Actions</th>
        </tr>
      </thead>
      <tbody>
        <?php if ($rows): foreach ($rows as $r):
          $jobs = (int)$r['jobs_count'];
          $rev  = (float)$r['client_revenue'];
          $com  = (float)$r['total_comm'];
          $avg  = (float)$r['avg_comm'];
          $paid = (float)$r['paid_sum'];
          $out  = (float)$r['outstanding'];
          $share= $r['share_pct'];
          $badge = $out > 0 ? 'warning' : 'success';
        ?>
          <tr class="<?= $out > 0 ? '' : 'table-success' ?>">
            <td><div class="fw-semibold"><?= e($r['partner_name'] ?? '—') ?></div></td>
            <td class="text-end"><?= number_format($jobs) ?></td>
            <td class="text-end">£<?= number_format($rev, 2) ?></td>
            <td class="text-end">£<?= number_format($com, 2) ?></td>
            <td class="text-end text-muted">£<?= number_format($avg, 2) ?></td>
            <td class="text-end">£<?= number_format($paid, 2) ?></td>
            <td class="text-end"><span class="badge text-bg-<?= e($badge) ?>">£<?= number_format($out, 2) ?></span></td>
            <td class="text-end"><?= $share===null ? '—' : number_format($share,2).'%' ?></td>
            <td class="text-end">
              <a class="btn btn-sm btn-outline-dark"
                 href="<?= e(url_modules('payments/partners_bulk.php').'?partner_id='.(int)$r['partner_id'].'&from='.$from.'&to='.$to) ?>">
                Bulk Pay
              </a>
            </td>
          </tr>
        <?php endforeach; else: ?>
          <tr><td colspan="9" class="text-center text-muted py-4">No results.</td></tr>
        <?php endif; ?>
      </tbody>
    </table>
  </div>
</div>

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

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
(function(){
  const jobsLabels = <?= json_encode(array_map(fn($r)=>$r['partner_name'], $topByJobs)) ?>;
  const jobsData   = <?= json_encode(array_map(fn($r)=>(int)$r['jobs_count'], $topByJobs)) ?>;

  const commLabels = <?= json_encode(array_map(fn($r)=>$r['partner_name'], $topByComm)) ?>;
  const commData   = <?= json_encode(array_map(fn($r)=>(float)$r['total_comm'], $topByComm)) ?>;

  const el1 = document.getElementById('jobsChart');
  if (el1 && typeof Chart !== 'undefined') {
    new Chart(el1, {
      type: 'bar',
      data: { labels: jobsLabels, datasets: [{ label: 'Jobs', data: jobsData }] },
      options: { responsive:true, plugins:{ legend:{ display:false } }, scales:{ y:{ beginAtZero:true, precision:0 } } }
    });
  }

  const el2 = document.getElementById('commChart');
  if (el2 && typeof Chart !== 'undefined') {
    new Chart(el2, {
      type: 'bar',
      data: { labels: commLabels, datasets: [{ label: 'Commission (£)', data: commData }] },
      options: { responsive:true, plugins:{ legend:{ display:false } }, scales:{ y:{ beginAtZero:true } } }
    });
  }
})();
</script>
