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

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

$range = (string)($_GET['range'] ?? '7d');
if (!in_array($range, ['day','7d','30d'], true)) $range = '7d';
$days = ($range === '30d') ? 30 : (($range === 'day') ? 1 : 7);

$selectedSku = trim((string)($_GET['sku'] ?? ''));
$selectedSkuSql = $selectedSku !== '' ? $mysqli->real_escape_string($selectedSku) : '';

$prodSym = [];
$r = $mysqli->query("SELECT reader_code FROM symcode_readers WHERE enabled=1 AND scan_role='PROD'");
if ($r) while ($row=$r->fetch_assoc()) {
  $rc = trim((string)$row['reader_code']);
  if ($rc !== '') $prodSym[] = $rc;
}
$allowed = array_values(array_unique(array_merge(['KEYENCE','MANUAL'], $prodSym)));
$inSources = in_list($mysqli, $allowed);
$whereSource = "(br.source_code IN {$inSources} OR br.source_code IS NULL)";

$skuWhere = ($selectedSkuSql !== '') ? " AND cc.SKU = '{$selectedSkuSql}' " : "";

$sql = "
  SELECT br.id, br.read_time, br.code, COALESCE(br.source_code,'(legacy/null)') AS source_code,
         cc.SKU, cc.grower, cc.variety, cc.packaging, cc.size
  FROM barcode_readings br
  LEFT JOIN casecodes cc ON cc.serial = br.code
  WHERE {$whereSource}
    AND br.read_time >= DATE_SUB(NOW(), INTERVAL {$days} DAY)
    {$skuWhere}
  ORDER BY br.id DESC
";

$filename = "production_live_{$range}" . ($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, ['id','read_time','code','source_code','SKU','grower','variety','packaging','size']);

$res = $mysqli->query($sql);
if ($res) while ($r=$res->fetch_assoc()) {
  fputcsv($out, [
    $r['id'], $r['read_time'], $r['code'], $r['source_code'],
    $r['SKU'], $r['grower'], $r['variety'], $r['packaging'], $r['size']
  ]);
}
fclose($out);
exit;
