<?php
require_once __DIR__ . '/../config/user_functions.php';
if (session_status() === PHP_SESSION_NONE) { session_start(); }
require_once __DIR__ . '/../config/users_local.php';

if (!user_has_permission('reports')) {
  http_response_code(403);
  echo "Access denied";
  exit;
}

require_once __DIR__ . '/../config/db_remote.php';

function sp_sql_in_list(mysqli $mysqli, array $vals): string {
  $out = [];
  foreach ($vals as $v) $out[] = "'" . $mysqli->real_escape_string((string)$v) . "'";
  return "(" . implode(",", $out) . ")";
}

// Build production sources (same logic as production_summary)
$prodReaderCodes = [];
$res = $mysqli->query("SELECT reader_code FROM symcode_readers WHERE scan_role='PROD' AND enabled=1");
if ($res) while ($r = $res->fetch_assoc()) {
  $rc = trim((string)($r['reader_code'] ?? ''));
  if ($rc !== '') $prodReaderCodes[] = $rc;
}
$allowedSources = array_values(array_unique(array_merge($prodReaderCodes, ['KEYENCE','MANUAL'])));
$inSources = sp_sql_in_list($mysqli, $allowedSources);
$whereSource = "(br.source_code IN {$inSources} OR br.source_code IS NULL)";

$skuRange = $_GET['sku_range'] ?? '7d';
$skuRange = in_array($skuRange, ['7d','30d'], true) ? $skuRange : '7d';
$skuDays  = ($skuRange === '30d') ? 30 : 7;

$selectedSku = trim((string)($_GET['sku'] ?? ''));
$skuWhere = '';
if ($selectedSku !== '') {
  $skuWhere = " AND cc.SKU = '" . $mysqli->real_escape_string($selectedSku) . "' ";
}

$sql = "
SELECT DATE(br.read_time) day, cc.SKU, COUNT(*) boxes
FROM barcode_readings br
LEFT JOIN casecodes cc ON cc.serial = br.code
WHERE {$whereSource}
  AND br.read_time >= DATE_SUB(CURDATE(), INTERVAL {$skuDays} DAY)
  AND cc.SKU IS NOT NULL AND cc.SKU <> ''
  {$skuWhere}
GROUP BY DATE(br.read_time), cc.SKU
ORDER BY DATE(br.read_time), cc.SKU
";

$res = $mysqli->query($sql);

$filename = "production_sku_trend_{$skuRange}" . ($selectedSku ? "_{$selectedSku}" : "") . ".csv";
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment; filename="' . $filename . '"');

$out = fopen('php://output', 'w');
fputcsv($out, ['day', 'SKU', 'boxes']);

if ($res) {
  while ($r = $res->fetch_assoc()) {
    fputcsv($out, [$r['day'], $r['SKU'], (int)$r['boxes']]);
  }
}

fclose($out);
exit;
