<?php
// public/reports_export_csv.php
require_once __DIR__ . '/../helpers/auth.php';
require_role(['admin', 'staff']);

$pdo = db();

// Helpers
function get_str(string $k): string { return trim((string)($_GET[$k] ?? '')); }

$date_from = get_str('from');
$date_to   = get_str('to');

$from_dt = $date_from !== '' ? ($date_from . ' 00:00:00') : null;
$to_dt   = $date_to !== '' ? ($date_to . ' 23:59:59') : null;

$deliveryWhere = " WHERE 1=1 ";
$moveWhere = " WHERE 1=1 ";
$paramsDel = [];
$paramsMov = [];

if ($from_dt) {
  $deliveryWhere .= " AND d.created_at >= :from ";
  $moveWhere .= " AND m.created_at >= :from ";
  $paramsDel[':from'] = $from_dt;
  $paramsMov[':from'] = $from_dt;
}
if ($to_dt) {
  $deliveryWhere .= " AND d.created_at <= :to ";
  $moveWhere .= " AND m.created_at <= :to ";
  $paramsDel[':to'] = $to_dt;
  $paramsMov[':to'] = $to_dt;
}

header('Content-Type: text/csv; charset=utf-8');
$fname = 'reports_' . date('Ymd_His') . '.csv';
header('Content-Disposition: attachment; filename=' . $fname);

$out = fopen('php://output', 'w');

// Title + filter info
fputcsv($out, ['IMS Reports Export']);
fputcsv($out, ['Generated At', date('Y-m-d H:i:s')]);
fputcsv($out, ['From', $date_from ?: '-', 'To', $date_to ?: '-']);
fputcsv($out, []);

// 1) Deliveries by status
fputcsv($out, ['Deliveries by Status']);
fputcsv($out, ['Status', 'Count']);

$delStatusSql = "
  SELECT d.status, COUNT(*) AS cnt
  FROM deliveries d
  $deliveryWhere
  GROUP BY d.status
  ORDER BY cnt DESC
";
$delStatusStmt = $pdo->prepare($delStatusSql);
$delStatusStmt->execute($paramsDel);
$rows = $delStatusStmt->fetchAll();

foreach ($rows as $r) {
  fputcsv($out, [$r['status'], (int)$r['cnt']]);
}
fputcsv($out, []);

// 2) Top products by OUT
fputcsv($out, ['Top Products by Stock OUT']);
fputcsv($out, ['SKU', 'Product', 'Total OUT']);

$topOutSql = "
  SELECT p.sku, p.name, SUM(m.quantity) AS total_out
  FROM inventory_movements m
  JOIN products p ON p.id = m.product_id
  $moveWhere
    AND m.movement_type = 'OUT'
  GROUP BY p.id
  ORDER BY total_out DESC
  LIMIT 10
";
$topOutStmt = $pdo->prepare($topOutSql);
$topOutStmt->execute($paramsMov);
$rows = $topOutStmt->fetchAll();

foreach ($rows as $r) {
  fputcsv($out, [$r['sku'], $r['name'], (int)$r['total_out']]);
}
fputcsv($out, []);

// 3) Profit totals + top profit products
fputcsv($out, ['Profit Summary (OUT)']);
fputcsv($out, ['Metric', 'Value']);

$profitTotalSql = "
  SELECT
    COALESCE(SUM(m.quantity),0) AS qty_out,
    COALESCE(SUM(m.quantity * p.sell_price),0) AS revenue,
    COALESCE(SUM(m.quantity * p.cost_price),0) AS cost,
    COALESCE(SUM((m.quantity * p.sell_price) - (m.quantity * p.cost_price)),0) AS profit
  FROM inventory_movements m
  JOIN products p ON p.id = m.product_id
  $moveWhere
    AND m.movement_type = 'OUT'
";
$profitTotalStmt = $pdo->prepare($profitTotalSql);
$profitTotalStmt->execute($paramsMov);
$t = $profitTotalStmt->fetch();

fputcsv($out, ['Qty OUT', (int)($t['qty_out'] ?? 0)]);
fputcsv($out, ['Revenue', number_format((float)($t['revenue'] ?? 0), 2, '.', '')]);
fputcsv($out, ['Cost', number_format((float)($t['cost'] ?? 0), 2, '.', '')]);
fputcsv($out, ['Profit', number_format((float)($t['profit'] ?? 0), 2, '.', '')]);

fputcsv($out, []);

fputcsv($out, ['Top Products by Profit (OUT)']);
fputcsv($out, ['SKU', 'Product', 'Qty OUT', 'Revenue', 'Cost', 'Profit']);

$profitSql = "
  SELECT
    p.sku, p.name,
    SUM(m.quantity) AS qty_out,
    (SUM(m.quantity) * p.sell_price) AS revenue,
    (SUM(m.quantity) * p.cost_price) AS cost,
    ((SUM(m.quantity) * p.sell_price) - (SUM(m.quantity) * p.cost_price)) AS profit
  FROM inventory_movements m
  JOIN products p ON p.id = m.product_id
  $moveWhere
    AND m.movement_type = 'OUT'
  GROUP BY p.id
  ORDER BY profit DESC
  LIMIT 10
";
$profitStmt = $pdo->prepare($profitSql);
$profitStmt->execute($paramsMov);
$rows = $profitStmt->fetchAll();

foreach ($rows as $r) {
  fputcsv($out, [
    $r['sku'],
    $r['name'],
    (int)$r['qty_out'],
    number_format((float)$r['revenue'], 2, '.', ''),
    number_format((float)$r['cost'], 2, '.', ''),
    number_format((float)$r['profit'], 2, '.', '')
  ]);
}

fputcsv($out, []);

// 4) Top customers
fputcsv($out, ['Top Customers by Deliveries']);
fputcsv($out, ['Customer', 'Total Deliveries', 'Delivered']);

$topCustSql = "
  SELECT
    c.name,
    COUNT(*) AS total_deliveries,
    SUM(CASE WHEN d.status='DELIVERED' THEN 1 ELSE 0 END) AS delivered_count
  FROM deliveries d
  LEFT JOIN customers c ON c.id = d.customer_id
  $deliveryWhere
  GROUP BY d.customer_id
  ORDER BY total_deliveries DESC
  LIMIT 10
";
$topCustStmt = $pdo->prepare($topCustSql);
$topCustStmt->execute($paramsDel);
$rows = $topCustStmt->fetchAll();

foreach ($rows as $r) {
  fputcsv($out, [$r['name'] ?? 'Unknown', (int)$r['total_deliveries'], (int)$r['delivered_count']]);
}
fputcsv($out, []);

// 5) Top drivers
fputcsv($out, ['Top Drivers']);
fputcsv($out, ['Driver', 'Assigned', 'Delivered']);

$topDrvSql = "
  SELECT
    u.full_name,
    COUNT(*) AS assigned_count,
    SUM(CASE WHEN d.status='DELIVERED' THEN 1 ELSE 0 END) AS delivered_count
  FROM deliveries d
  LEFT JOIN users u ON u.id = d.assigned_driver_id
  $deliveryWhere
  GROUP BY d.assigned_driver_id
  ORDER BY delivered_count DESC, assigned_count DESC
  LIMIT 10
";
$topDrvStmt = $pdo->prepare($topDrvSql);
$topDrvStmt->execute($paramsDel);
$rows = $topDrvStmt->fetchAll();

foreach ($rows as $r) {
  fputcsv($out, [$r['full_name'] ?? 'Unassigned', (int)$r['assigned_count'], (int)$r['delivered_count']]);
}

// Monthly Profit table
fputcsv($out, []);
fputcsv($out, ['Monthly Profit (OUT)']);
fputcsv($out, ['Month', 'Revenue', 'Cost', 'Profit']);

$profitMonthlySql = "
  SELECT
    DATE_FORMAT(m.created_at, '%Y-%m') AS ym,
    COALESCE(SUM(m.quantity * p.sell_price),0) AS revenue,
    COALESCE(SUM(m.quantity * p.cost_price),0) AS cost,
    COALESCE(SUM((m.quantity * p.sell_price) - (m.quantity * p.cost_price)),0) AS profit
  FROM inventory_movements m
  JOIN products p ON p.id = m.product_id
  $moveWhere
    AND m.movement_type = 'OUT'
  GROUP BY ym
  ORDER BY ym ASC
";
$pm = $pdo->prepare($profitMonthlySql);
$pm->execute($paramsMov);
$rows = $pm->fetchAll();

foreach ($rows as $r) {
  fputcsv($out, [
    $r['ym'],
    number_format((float)$r['revenue'], 2, '.', ''),
    number_format((float)$r['cost'], 2, '.', ''),
    number_format((float)$r['profit'], 2, '.', '')
  ]);
}


fclose($out);
exit;
