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

require_once __DIR__ . '/../vendor/autoload.php'; // composer

use Dompdf\Dompdf;
use Dompdf\Options;

$pdo = db();

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

// Deliveries by status
$delStatus = $pdo->prepare("
  SELECT d.status, COUNT(*) AS cnt
  FROM deliveries d
  $deliveryWhere
  GROUP BY d.status
  ORDER BY cnt DESC
");
$delStatus->execute($paramsDel);
$delStatusRows = $delStatus->fetchAll();

// Profit totals
$profitTotalsStmt = $pdo->prepare("
  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'
");
$profitTotalsStmt->execute($paramsMov);
$pt = $profitTotalsStmt->fetch();

// Top profit products
$profitTopStmt = $pdo->prepare("
  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
");
$profitTopStmt->execute($paramsMov);
$profitTop = $profitTopStmt->fetchAll();

// Monthly profit
$profitMonthlyStmt = $pdo->prepare("
  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
");
$profitMonthlyStmt->execute($paramsMov);
$profitMonthly = $profitMonthlyStmt->fetchAll();

// Basic HTML for PDF
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }

$rangeText = ($date_from || $date_to) ? ("From: " . ($date_from ?: '-') . " To: " . ($date_to ?: '-')) : "All time";

$html = '
<html>
<head>
  <style>
    body { font-family: DejaVu Sans, sans-serif; font-size: 12px; }
    h1 { font-size: 18px; margin: 0 0 6px 0; }
    .muted { color: #666; }
    table { width: 100%; border-collapse: collapse; margin: 10px 0 18px 0; }
    th, td { border: 1px solid #ddd; padding: 6px; }
    th { background: #f2f2f2; text-align: left; }
    .right { text-align: right; }
  </style>
</head>
<body>
  <h1>IMS Reports</h1>
  <div class="muted">Generated: '.h(date('Y-m-d H:i:s')).' | '.h($rangeText).'</div>

  <h3>Profit Summary (OUT)</h3>
  <table>
    <tr><th>Qty OUT</th><td class="right">'.number_format((float)$pt['qty_out'],0).'</td></tr>
    <tr><th>Revenue</th><td class="right">'.number_format((float)$pt['revenue'],2).'</td></tr>
    <tr><th>Cost</th><td class="right">'.number_format((float)$pt['cost'],2).'</td></tr>
    <tr><th>Profit</th><td class="right">'.number_format((float)$pt['profit'],2).'</td></tr>
  </table>

  <h3>Deliveries by Status</h3>
  <table>
    <tr><th>Status</th><th class="right">Count</th></tr>';

foreach ($delStatusRows as $r) {
  $html .= '<tr><td>'.h($r['status']).'</td><td class="right">'.(int)$r['cnt'].'</td></tr>';
}

$html .= '
  </table>

  <h3>Top Products by Profit (OUT)</h3>
  <table>
    <tr>
      <th>SKU</th><th>Product</th>
      <th class="right">Qty OUT</th>
      <th class="right">Revenue</th>
      <th class="right">Cost</th>
      <th class="right">Profit</th>
    </tr>';

foreach ($profitTop as $r) {
  $html .= '<tr>
    <td>'.h($r['sku']).'</td>
    <td>'.h($r['name']).'</td>
    <td class="right">'.(int)$r['qty_out'].'</td>
    <td class="right">'.number_format((float)$r['revenue'],2).'</td>
    <td class="right">'.number_format((float)$r['cost'],2).'</td>
    <td class="right">'.number_format((float)$r['profit'],2).'</td>
  </tr>';
}

$html .= '
  </table>

  <h3>Monthly Profit (OUT)</h3>
  <table>
    <tr><th>Month</th><th class="right">Revenue</th><th class="right">Cost</th><th class="right">Profit</th></tr>';

foreach ($profitMonthly as $r) {
  $html .= '<tr>
    <td>'.h($r['ym']).'</td>
    <td class="right">'.number_format((float)$r['revenue'],2).'</td>
    <td class="right">'.number_format((float)$r['cost'],2).'</td>
    <td class="right">'.number_format((float)$r['profit'],2).'</td>
  </tr>';
}

$html .= '
  </table>
</body>
</html>
';

// Dompdf
$options = new Options();
$options->set('isRemoteEnabled', true);

$dompdf = new Dompdf($options);
$dompdf->loadHtml($html);
$dompdf->setPaper('A4', 'portrait');
$dompdf->render();

$filename = 'reports_' . date('Ymd_His') . '.pdf';
$dompdf->stream($filename, ['Attachment' => true]);
exit;
