<?php
declare(strict_types=1);

/**
 * modules/reports/payouts_partners.php
 *
 * Partner Payouts — Paid vs Outstanding
 *
 * Filters:
 *  - Date range (pickup_date)
 *  - Status filter: unpaid|paid|all (by outstanding calculation)
 *  - Specific partner (optional)
 *  - Search (booking ref / client / partner)
 *
 * Outputs:
 *  - KPI cards (Jobs, Due, Paid, Outstanding)
 *  - Charts: Top 10 partners by Outstanding and by Total Due
 *  - Table of bookings with partner, due, paid, outstanding
 *  - CSV export
 *  - Quick link to Bulk Pay (payments/partners_bulk.php) when a partner is selected
 */

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; }
}

/** Safe partner-due SQL expr built from existing `bookings` columns only. */
function partner_due_expr(string $alias='partner_due'): string {
  static $cache = null;
  if ($cache === null) {
    $cache = [];
    try {
      $rs = db()->query("SHOW COLUMNS FROM bookings");
      foreach ($rs->fetchAll() as $c) $cache[strtolower((string)$c['Field'])] = true;
    } catch (Throwable) { $cache = []; }
  }
  $cands = [
    'partner_due',
    'partner_amount',
    'partner_commission_amount',
    'partner_commission',
    'partner_fee',
    'partner_total',
    'total_partner_price',
  ];
  $use = [];
  foreach ($cands as $c) if (isset($cache[strtolower($c)])) $use[] = "b.`{$c}`";
  if (!$use) return "0 AS {$alias}";
  return 'COALESCE(' . implode(',', $use) . ") AS {$alias}";
}

/* -----------------------------
   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);
$status   = (string)($_GET['status'] ?? 'unpaid'); // unpaid|paid|all
$partnerId= (int)($_GET['partner_id'] ?? 0);
$q        = trim((string)($_GET['q'] ?? ''));
$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 (for filter)
------------------------------*/
$partners = [];
try {
  $ps = db()->prepare("SELECT id, name FROM partners WHERE company_id=:cid ORDER BY name ASC");
  $ps->execute([':cid'=>$cid]);
  $partners = $ps->fetchAll() ?: [];
} catch (Throwable) {}

/* -----------------------------
   Query bookings + payouts
------------------------------*/
$rows = [];
$hasPayouts = tbl_exists('partner_payouts');

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

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

  if ($q !== '') {
    $where[] = "(b.booking_ref LIKE :q OR b.client_name LIKE :q OR p.name LIKE :q)";
    $args[':q'] = '%'.$q.'%';
  }

  $paidSumSQL = $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
      b.id,
      b.booking_ref,
      b.client_name,
      b.pickup_date,
      b.pickup_time,
      p.id   AS partner_id,
      p.name AS partner_name,
      ".partner_due_expr('partner_due').",
      {$paidSumSQL} 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)."
    ORDER BY b.pickup_date DESC, b.pickup_time DESC, b.id DESC
    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 payouts.';
}

/* -----------------------------
   Compute totals + filter by status (unpaid/paid)
------------------------------*/
$totalJobs = 0; $sumDue = 0.0; $sumPaid = 0.0; $sumOut = 0.0;

$rows = array_values(array_filter(array_map(function($r){
  $due  = (float)($r['partner_due'] ?? 0);
  $paid = (float)($r['paid_sum'] ?? 0);
  $r['outstanding'] = max(0.0, round($due - $paid, 2));
  return $r;
}, $rows), function($r) use($status){
  if ($status === 'unpaid') return ($r['outstanding'] ?? 0) > 0.0001;
  if ($status === 'paid')   return ($r['outstanding'] ?? 0) <= 0.0001 && ($r['partner_due'] ?? 0) > 0;
  return true;
}));

foreach ($rows as $r) {
  $totalJobs++;
  $sumDue  += (float)($r['partner_due'] ?? 0);
  $sumPaid += (float)($r['paid_sum'] ?? 0);
  $sumOut  += (float)($r['outstanding'] ?? 0);
}

/* -----------------------------
   Aggregation by partner (for charts)
------------------------------*/
$byPartner = [];
foreach ($rows as $r) {
  $pid = (int)($r['partner_id'] ?? 0);
  if ($pid <= 0) continue;
  if (!isset($byPartner[$pid])) {
    $byPartner[$pid] = [
      'name' => (string)($r['partner_name'] ?? '—'),
      'jobs' => 0,
      'due'  => 0.0,
      'paid' => 0.0,
      'out'  => 0.0,
    ];
  }
  $byPartner[$pid]['jobs']++;
  $byPartner[$pid]['due']  += (float)$r['partner_due'];
  $byPartner[$pid]['paid'] += (float)$r['paid_sum'];
  $byPartner[$pid]['out']  += (float)$r['outstanding'];
}

usort($byPartner, fn($a,$b)=> ($b['out'] <=> $a['out']));
$topOutstanding = array_slice($byPartner, 0, 10);

$byPartner2 = $byPartner;
usort($byPartner2, fn($a,$b)=> ($b['due'] <=> $a['due']));
$topDue = array_slice($byPartner2, 0, 10);

/* -----------------------------
   CSV Export
------------------------------*/
if ($export === 'csv') {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="payouts_partners_'.date('Ymd_His').'.csv"');
  $out = fopen('php://output', 'w');
  fputcsv($out, ['From',$from,'To',$to,'Status',$status,'PartnerID',$partnerId,'Search',$q]);
  fputcsv($out, []);
  fputcsv($out, ['Partner','Booking Ref','Client','Pickup Date','Pickup Time','Partner Due £','Paid £','Outstanding £']);
  foreach ($rows as $r) {
    fputcsv($out, [
      (string)($r['partner_name'] ?? '—'),
      (string)($r['booking_ref'] ?? ('#'.$r['id'])),
      (string)($r['client_name'] ?? '—'),
      (string)($r['pickup_date'] ?? ''),
      (string)($r['pickup_time'] ?? ''),
      number_format((float)$r['partner_due'],2,'.',''),
      number_format((float)$r['paid_sum'],2,'.',''),
      number_format((float)$r['outstanding'],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 Payouts — Paid vs Outstanding</h1>
    <div class="text-muted">Range: <?= e($from) ?> → <?= 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>
    <?php if ($partnerId>0): ?>
      <a class="btn btn-dark"
         href="<?= e(url_modules('payments/partners_bulk.php').'?partner_id='.$partnerId.'&from='.$from.'&to='.$to) ?>">
         💳 Bulk Pay This Partner
      </a>
    <?php endif; ?>
  </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-6 col-md-3">
        <label class="form-label">Status</label>
        <select class="form-select" name="status">
          <option value="unpaid" <?= $status==='unpaid'?'selected':'' ?>>Unpaid</option>
          <option value="paid"   <?= $status==='paid'  ?'selected':'' ?>>Paid</option>
          <option value="all"    <?= $status==='all'   ?'selected':'' ?>>All</option>
        </select>
      </div>
      <div class="col-6 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($partners 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-6">
        <label class="form-label">Search</label>
        <input class="form-control" name="q" placeholder="Booking Ref / Client / Partner" value="<?= e($q) ?>">
      </div>
      <div class="col-12 col-md-2 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">Bookings</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-dark">
      <div class="card-body">
        <div class="small text-muted">Partner Due (Filtered)</div>
        <div class="display-6">£<?= number_format($sumDue, 2) ?></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">Paid</div>
        <div class="display-6">£<?= number_format($sumPaid, 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 10 Partners — Outstanding (£)</div>
        <canvas id="outChart" 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 10 Partners — Total Due (£)</div>
        <canvas id="dueChart" 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>Booking</th>
          <th>Client</th>
          <th>Pickup</th>
          <th class="text-end">£ Due</th>
          <th class="text-end">£ Paid</th>
          <th class="text-end">£ Outstanding</th>
          <th class="text-end">Actions</th>
        </tr>
      </thead>
      <tbody>
        <?php if ($rows): foreach ($rows as $r): 
          $due  = (float)$r['partner_due'];
          $paid = (float)$r['paid_sum'];
          $out  = (float)$r['outstanding'];
          $badge = $out > 0 ? 'danger' : 'success';
          $pdt = trim(($r['pickup_date'] ?? '') . ' ' . ($r['pickup_time'] ?? ''));
        ?>
          <tr class="<?= $out > 0 ? '' : 'table-success' ?>">
            <td><div class="fw-semibold"><?= e($r['partner_name'] ?? '—') ?></div></td>
            <td><strong><?= e($r['booking_ref'] ?? ('#'.$r['id'])) ?></strong></td>
            <td><?= e($r['client_name'] ?? '—') ?></td>
            <td><?= e($pdt) ?></td>
            <td class="text-end">£<?= number_format($due, 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">
              <?php if (!empty($r['partner_id']) && $out > 0): ?>
                <a class="btn btn-sm btn-outline-dark"
                   href="<?= e(url_modules('payments/partners_bulk.php')
                          . '?partner_id='.(int)$r['partner_id']
                          . '&from='.urlencode($from)
                          . '&to='.urlencode($to)) ?>">
                  Bulk Pay
                </a>
              <?php else: ?>
                <span class="text-muted">—</span>
              <?php endif; ?>
            </td>
          </tr>
        <?php endforeach; else: ?>
          <tr><td colspan="8" 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 outLabels = <?= json_encode(array_map(fn($r)=>$r['name'], $topOutstanding)) ?>;
  const outData   = <?= json_encode(array_map(fn($r)=>(float)$r['out'], $topOutstanding)) ?>;

  const dueLabels = <?= json_encode(array_map(fn($r)=>$r['name'], $topDue)) ?>;
  const dueData   = <?= json_encode(array_map(fn($r)=>(float)$r['due'], $topDue)) ?>;

  const el1 = document.getElementById('outChart');
  if (el1 && typeof Chart !== 'undefined') {
    new Chart(el1, {
      type: 'bar',
      data: { labels: outLabels, datasets: [{ label: 'Outstanding (£)', data: outData }] },
      options: { responsive:true, plugins:{ legend:{ display:false } }, scales:{ y:{ beginAtZero:true } } }
    });
  }

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