<?php
declare(strict_types=1);

/**
 * modules/payments/export_csv.php
 *
 * Export driver payments view (per booking) to CSV.
 * Matches the logic used in payments/drivers.php: each row is a booking with
 * Driver Due, Paid So Far, and Outstanding.
 *
 * GET params:
 *  - from: YYYY-MM-DD (default: first day of current month)
 *  - to:   YYYY-MM-DD (default: today)
 *  - status: all|unpaid|paid (default: unpaid)
 *
 * Columns:
 *  Booking ID, Ref, Pickup Date, Pickup Time, Client, Driver, Partner, Corporate,
 *  Client Total (£), Driver Due (£), Commission (£), Profit (£),
 *  Paid So Far (£), Outstanding (£), Status
 */

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) {
  http_response_code(403);
  exit('Not authorized.');
}

/* ---------- Parse filters ---------- */
$today      = new DateTimeImmutable('today');
$monthStart = $today->modify('first day of this month');

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

$status = strtolower(trim((string)($_GET['status'] ?? 'unpaid')));
if (!in_array($status, ['all','unpaid','paid'], true)) $status = 'unpaid';

/* ---------- Build query ---------- */
/**
 * We export completed/cleared type bookings (finance view).
 * Driver due = driver base + driver extras
 * Client total = client base + client extras
 * Paid so far = SUM(driver_payouts.amount)
 * Outstanding = driver due - paid so far
 * Profit = client total - driver due - commission
 *
 * We also pull "primary" driver (first sequence in booking_vehicles).
 */
$sql = "
  SELECT
    b.id,
    b.booking_ref,
    b.pickup_date,
    b.pickup_time,
    b.client_name,
    b.status,
    COALESCE(p.name, '')         AS partner_name,
    COALESCE(c.name, '')         AS corporate_name,
    -- financials
    (COALESCE(b.total_client_price,0)
      + COALESCE(b.client_parking_fee,0)
      + COALESCE(b.client_waiting_fee,0)
    ) AS client_total,
    (COALESCE(b.total_driver_price,0)
      + COALESCE(b.driver_parking_fee,0)
      + COALESCE(b.driver_waiting_fee,0)
    ) AS driver_due,
    COALESCE(b.partner_commission_amount,0) AS commission,
    -- driver
    COALESCE(d.name,'') AS driver_name,
    -- payouts
    COALESCE(pay.paid_sum,0) AS paid_sum
  FROM bookings b
  LEFT JOIN partners  p ON p.id = b.partner_id
  LEFT JOIN corporates c ON c.id = b.corporate_id
  -- primary driver (first sequence)
  LEFT JOIN (
    SELECT bv.booking_id, MIN(bv.id) AS min_id
    FROM booking_vehicles bv
    GROUP BY bv.booking_id
  ) x ON x.booking_id = b.id
  LEFT JOIN booking_vehicles bv ON bv.id = x.min_id
  LEFT JOIN drivers d ON d.id = bv.driver_id
  -- payouts aggregate
  LEFT JOIN (
    SELECT booking_id, SUM(amount) AS paid_sum
    FROM driver_payouts
    WHERE company_id = :cid_pay
    GROUP BY booking_id
  ) pay ON pay.booking_id = b.id
  WHERE b.company_id = :cid
    AND b.pickup_date BETWEEN :from AND :to
    AND b.status IN ('Completed','Invoiced','Paid')
  ORDER BY b.pickup_date ASC, b.pickup_time ASC, b.id ASC
";

$stmt = db()->prepare($sql);
$stmt->execute([
  ':cid'     => $cid,
  ':cid_pay' => $cid,
  ':from'    => $from,
  ':to'      => $to,
]);

/* ---------- Output CSV headers ---------- */
$fname = 'driver_payments_' . $from . '_to_' . $to . '_' . $status . '.csv';
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="' . $fname . '"');
header('Pragma: no-cache');
header('Expires: 0');

$fh = fopen('php://output', 'w');
if ($fh === false) {
  echo "Unable to open output stream.";
  exit;
}

/* ---------- CSV Header Row ---------- */
fputcsv($fh, [
  'Booking ID',
  'Ref',
  'Pickup Date',
  'Pickup Time',
  'Client',
  'Driver',
  'Partner',
  'Corporate',
  'Client Total (£)',
  'Driver Due (£)',
  'Commission (£)',
  'Profit (£)',
  'Paid So Far (£)',
  'Outstanding (£)',
  'Status',
]);

/* ---------- Stream rows ---------- */
while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $clientTotal = (float)$r['client_total'];
  $driverDue   = (float)$r['driver_due'];
  $commission  = (float)$r['commission'];
  $paidSum     = (float)$r['paid_sum'];
  $profit      = $clientTotal - $driverDue - $commission;
  $outstanding = max(0.0, $driverDue - $paidSum);

  // status filter
  if ($status === 'unpaid' && $outstanding <= 0.00001) continue;
  if ($status === 'paid'   && $outstanding >  0.00001) continue;

  fputcsv($fh, [
    (int)$r['id'],
    (string)$r['booking_ref'],
    (string)$r['pickup_date'],
    (string)$r['pickup_time'],
    (string)$r['client_name'],
    (string)$r['driver_name'],
    (string)$r['partner_name'],
    (string)$r['corporate_name'],
    number_format($clientTotal, 2, '.', ''),
    number_format($driverDue,   2, '.', ''),
    number_format($commission,  2, '.', ''),
    number_format($profit,      2, '.', ''),
    number_format($paidSum,     2, '.', ''),
    number_format($outstanding, 2, '.', ''),
    (string)$r['status'],
  ]);
}

fclose($fh);
exit;
