<?php
declare(strict_types=1);
/**
 * modules/reports/net_profit.php
 *
 * Net Profit report (from bookings):
 *  - Gross Income (client)      = total_client_price (+ client_parking_fee + client_waiting_fee if toggled)
 *  - Driver Payout (cost)       = total_driver_price + driver_parking_fee + driver_waiting_fee
 *  - Partner Commission (cost)  = partner_commission_amount
 *  - Net Profit                 = Gross Income - (Driver Payout + Partner Commission)
 *
 * Filters:
 *  - Date range (pickup_date)
 *  - Scope: All jobs vs Completed only
 *  - Include client fees in gross (toggle)
 *
 * Output:
 *  - KPI cards (gross, driver payout, partner commission, net profit)
 *  - Chart.js mixed chart (bars for components, line for net)
 *  - Daily table breakdown with totals
 */

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 n2($v): string { return number_format((float)$v, 2); }
function col_exists(string $table, string $col): bool {
  try {
    $q = db()->prepare("
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t AND COLUMN_NAME = :c
      LIMIT 1
    ");
    $q->execute([':t'=>$table, ':c'=>$col]);
    return (bool)$q->fetchColumn();
  } catch (Throwable $e) { return false; }
}

/* ---------------- Filters (GET) ---------------- */
$today  = new DateTimeImmutable('today');
$from   = (string)($_GET['from'] ?? $today->modify('first day of this month')->format('Y-m-d'));
$to     = (string)($_GET['to']   ?? $today->format('Y-m-d'));
$scope  = (string)($_GET['scope'] ?? 'completed'); // 'all' or 'completed'
$incFee = isset($_GET['include_fees']) ? (int)($_GET['include_fees']) : 1; // 1 = include client fees in gross income

if (!preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) $from = $today->modify('first day of this month')->format('Y-m-d');
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/',$to))   $to   = $today->format('Y-m-d');

/* ---------------- Build SQL expressions (defensive) ---------------- */
$hasClientParking = col_exists('bookings','client_parking_fee');
$hasClientWaiting = col_exists('bookings','client_waiting_fee');
$hasDrvParking    = col_exists('bookings','driver_parking_fee');
$hasDrvWaiting    = col_exists('bookings','driver_waiting_fee');
$hasPartnerComm   = col_exists('bookings','partner_commission_amount');

$exprGross = "COALESCE(b.total_client_price,0)";
if ($incFee) {
  if ($hasClientParking) $exprGross .= " + COALESCE(b.client_parking_fee,0)";
  if ($hasClientWaiting) $exprGross .= " + COALESCE(b.client_waiting_fee,0)";
}

$exprDrv = "COALESCE(b.total_driver_price,0)";
if ($hasDrvParking) $exprDrv .= " + COALESCE(b.driver_parking_fee,0)";
if ($hasDrvWaiting) $exprDrv .= " + COALESCE(b.driver_waiting_fee,0)";

$exprComm = $hasPartnerComm ? "COALESCE(b.partner_commission_amount,0)" : "0";

/* ---------------- Fetch daily aggregates ---------------- */
$where = [
  "b.company_id = :cid",
  "b.pickup_date IS NOT NULL",
  "b.pickup_date BETWEEN :from AND :to"
];
$args = [':cid'=>$cid, ':from'=>$from, ':to'=>$to];

if ($scope === 'completed') {
  $where[] = "b.status = 'Completed'";
}

$sql = "
  SELECT
    DATE(b.pickup_date) AS d,
    SUM($exprGross) AS gross_income,
    SUM($exprDrv)   AS driver_payout,
    SUM($exprComm)  AS partner_commission
  FROM bookings b
  WHERE " . implode(' AND ', $where) . "
  GROUP BY DATE(b.pickup_date)
  ORDER BY DATE(b.pickup_date) ASC
";
$stmt = db()->prepare($sql);
$stmt->execute($args);
$rows = $stmt->fetchAll() ?: [];

/* Compute totals + prepare chart arrays */
$totalGross = 0.0; $totalDrv = 0.0; $totalComm = 0.0; $totalNet = 0.0;
$labels = []; $seriesGross = []; $seriesDrv = []; $seriesComm = []; $seriesNet = [];
foreach ($rows as $r) {
  $g = (float)$r['gross_income'];
  $d = (float)$r['driver_payout'];
  $c = (float)$r['partner_commission'];
  $n = $g - ($d + $c);

  $labels[]     = (string)$r['d'];
  $seriesGross[] = round($g, 2);
  $seriesDrv[]   = round($d, 2);
  $seriesComm[]  = round($c, 2);
  $seriesNet[]   = round($n, 2);

  $totalGross += $g;
  $totalDrv   += $d;
  $totalComm  += $c;
  $totalNet   += $n;
}

/* ---------------- 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">Net Profit</h1>
    <div class="text-muted small">From bookings (<?= e($scope==='completed'?'Completed only':'All jobs') ?>)</div>
  </div>
  <div class="d-flex gap-2">
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('reports/index.php')) ?>">← Reports</a>
  </div>
</div>

<?php if ($notice): ?><div class="alert alert-success"><?= e($notice) ?></div><?php endif; ?>
<?php if ($errors): ?><div class="alert alert-danger"><ul class="mb-0"><?php foreach($errors as $er) echo '<li>'.e($er).'</li>'; ?></ul></div><?php endif; ?>

<div class="card shadow-sm mb-3">
  <div class="card-body">
    <form method="get" class="row g-2 align-items-end">
      <div class="col-md-3">
        <label class="form-label">From</label>
        <input type="date" class="form-control" name="from" value="<?= e($from) ?>">
      </div>
      <div class="col-md-3">
        <label class="form-label">To</label>
        <input type="date" class="form-control" name="to" value="<?= e($to) ?>">
      </div>
      <div class="col-md-3">
        <label class="form-label">Scope</label>
        <select class="form-select" name="scope">
          <option value="completed" <?= $scope==='completed'?'selected':'' ?>>Completed jobs</option>
          <option value="all" <?= $scope==='all'?'selected':'' ?>>All jobs</option>
        </select>
      </div>
      <div class="col-md-3">
        <label class="form-label">Gross Income Includes</label>
        <select class="form-select" name="include_fees">
          <option value="1" <?= $incFee? 'selected':'' ?>>Client parking & waiting fees</option>
          <option value="0" <?= !$incFee? 'selected':'' ?>>No client fees (fares only)</option>
        </select>
      </div>

      <div class="col-12 d-flex flex-wrap gap-2 mt-2">
        <button class="btn btn-dark">Apply</button>
        <?php
          $qs7  = http_build_query(['from'=>$today->modify('-6 days')->format('Y-m-d'),'to'=>$today->format('Y-m-d'),'scope'=>$scope,'include_fees'=>$incFee]);
          $qs30 = http_build_query(['from'=>$today->modify('-29 days')->format('Y-m-d'),'to'=>$today->format('Y-m-d'),'scope'=>$scope,'include_fees'=>$incFee]);
          $qs90 = http_build_query(['from'=>$today->modify('-89 days')->format('Y-m-d'),'to'=>$today->format('Y-m-d'),'scope'=>$scope,'include_fees'=>$incFee]);
        ?>
        <a class="btn btn-outline-secondary" href="?<?= e($qs7) ?>">Last 7 days</a>
        <a class="btn btn-outline-secondary" href="?<?= e($qs30) ?>">Last 30 days</a>
        <a class="btn btn-outline-secondary" href="?<?= e($qs90) ?>">Last 90 days</a>
        <a class="btn btn-outline-secondary" href="?scope=completed&include_fees=1">Reset</a>
      </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">
      <div class="card-body">
        <div class="text-muted small">Gross Income</div>
        <div class="display-6 fw-semibold">£ <?= e(n2($totalGross)) ?></div>
        <div class="small text-muted"><?= $incFee ? 'Includes client parking & waiting' : 'Fares only' ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-md-3">
    <div class="card shadow-sm h-100">
      <div class="card-body">
        <div class="text-muted small">Driver Payout</div>
        <div class="display-6 fw-semibold">£ <?= e(n2($totalDrv)) ?></div>
        <div class="small text-muted">Driver price + parking + waiting</div>
      </div>
    </div>
  </div>
  <div class="col-12 col-md-3">
    <div class="card shadow-sm h-100">
      <div class="card-body">
        <div class="text-muted small">Partner Commission</div>
        <div class="display-6 fw-semibold">£ <?= e(n2($totalComm)) ?></div>
        <div class="small text-muted">From bookings.partner_commission_amount</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="text-muted small">Net Profit</div>
        <div class="display-6 fw-semibold <?= $totalNet>=0?'text-success':'text-danger' ?>">£ <?= e(n2($totalNet)) ?></div>
        <div class="small text-muted">Gross − (Driver + Commission)</div>
      </div>
    </div>
  </div>
</div>

<!-- Chart -->
<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">Profit Trend</div>
      <div class="small text-muted">Daily totals</div>
    </div>
    <canvas id="npChart" height="110"></canvas>
  </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 style="min-width:120px;">Date</th>
          <th class="text-end" style="min-width:140px;">Gross £</th>
          <th class="text-end" style="min-width:140px;">Driver £</th>
          <th class="text-end" style="min-width:140px;">Commission £</th>
          <th class="text-end" style="min-width:140px;">Net Profit £</th>
        </tr>
      </thead>
      <tbody>
        <?php if ($rows): foreach ($rows as $r):
          $g=(float)$r['gross_income']; $d=(float)$r['driver_payout']; $c=(float)$r['partner_commission']; $n=$g-($d+$c);
        ?>
          <tr>
            <td><?= e((string)$r['d']) ?></td>
            <td class="text-end">£ <?= e(n2($g)) ?></td>
            <td class="text-end">£ <?= e(n2($d)) ?></td>
            <td class="text-end">£ <?= e(n2($c)) ?></td>
            <td class="text-end fw-semibold <?= $n>=0?'text-success':'text-danger' ?>">£ <?= e(n2($n)) ?></td>
          </tr>
        <?php endforeach; else: ?>
          <tr><td colspan="5" class="text-center text-muted py-4">No data for the selected filters.</td></tr>
        <?php endif; ?>
      </tbody>
      <tfoot class="table-light">
        <tr>
          <th>Totals</th>
          <th class="text-end">£ <?= e(n2($totalGross)) ?></th>
          <th class="text-end">£ <?= e(n2($totalDrv)) ?></th>
          <th class="text-end">£ <?= e(n2($totalComm)) ?></th>
          <th class="text-end">£ <?= e(n2($totalNet)) ?></th>
        </tr>
      </tfoot>
    </table>
  </div>
</div>

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

<!-- Chart.js -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
(function(){
  const labels  = <?= json_encode($labels) ?>;
  const gross   = <?= json_encode($seriesGross) ?>;
  const driver  = <?= json_encode($seriesDrv) ?>;
  const comm    = <?= json_encode($seriesComm) ?>;
  const net     = <?= json_encode($seriesNet) ?>;

  const ctx = document.getElementById('npChart').getContext('2d');

  // Stacked bars for costs & income, line for net profit
  const chart = new Chart(ctx, {
    type: 'bar',
    data: {
      labels,
      datasets: [
        { label: 'Gross Income', data: gross, borderWidth: 1, type: 'bar', order: 3 },
        { label: 'Driver Payout', data: driver, borderWidth: 1, type: 'bar', order: 2 },
        { label: 'Partner Commission', data: comm, borderWidth: 1, type: 'bar', order: 1 },
        { label: 'Net Profit', data: net, borderWidth: 2, type: 'line', tension: 0.3, fill: false, order: 0 }
      ]
    },
    options: {
      responsive: true,
      interaction: { mode: 'index', intersect: false },
      scales: {
        x: { stacked: true, grid: { display:false } },
        y: { stacked: false, beginAtZero: true }
      },
      plugins: {
        legend: { position: 'top' },
        tooltip: {
          callbacks: {
            label: (ctx) => {
              const v = typeof ctx.raw === 'number' ? ctx.raw : 0;
              return `${ctx.dataset.label}: £ ${v.toFixed(2)}`;
            }
          }
        }
      }
    }
  });
})();
</script>
