<?php
require_once __DIR__ . '/../config/db_remote.php';
header('Content-Type: application/json; charset=utf-8');

if (!isset($mysqli) || !($mysqli instanceof mysqli)) {
  http_response_code(500);
  echo json_encode(['ok'=>false,'error'=>'DB not available']);
  exit;
}

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

$whereRange = ($range === 'day')
  ? "DATE(br.read_time) = CURDATE()"
  : "br.read_time >= DATE_SUB(NOW(), INTERVAL {$days} DAY)";

/* Trend 30 days (unificato) */
$tLabels=[]; $tData=[];
$res = $mysqli->query("
  SELECT DATE(read_time) d, COUNT(*) c
  FROM barcode_readings
  WHERE read_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  GROUP BY DATE(read_time)
  ORDER BY d
");
if ($res) while ($r=$res->fetch_assoc()) { $tLabels[]=$r['d']; $tData[]=(int)$r['c']; }

/* SKU stacked (unificato) */
$skuLabels=[]; $skuSeries=[];
$tmp=[]; $daysSet=[];

$res = $mysqli->query("
  SELECT DATE(br.read_time) d, cc.SKU, COUNT(*) c
  FROM barcode_readings br
  LEFT JOIN casecodes cc ON cc.serial = br.code
  WHERE {$whereRange}
  GROUP BY DATE(br.read_time), cc.SKU
  ORDER BY d
");
if ($res) while ($r=$res->fetch_assoc()) {
  $d = (string)$r['d'];
  $sku = (string)($r['SKU'] ?? '');
  if ($sku === '') $sku = '(n/a)';
  $daysSet[$d]=true;
  $tmp[$sku][$d] = (int)$r['c'];
}
$skuLabels = array_keys($daysSet);
sort($skuLabels);

// limit: top 10 SKUs by total in range
$totals=[];
foreach ($tmp as $sku=>$map) $totals[$sku] = array_sum($map);
arsort($totals);
$topSkus = array_slice(array_keys($totals), 0, 10);

foreach ($topSkus as $sku) {
  $arr=[];
  foreach ($skuLabels as $d) $arr[] = $tmp[$sku][$d] ?? 0;
  $skuSeries[$sku]=$arr;
}

/* Compare Keyence vs Symcode */
$cLabels=[]; $cKey=[]; $cSym=[];
$res = $mysqli->query("
  SELECT DATE(read_time) d,
    SUM(CASE WHEN source_code='KEYENCE' THEN 1 ELSE 0 END) keyence_cnt,
    SUM(CASE WHEN source_code LIKE 'SYM_%' THEN 1 ELSE 0 END) sym_cnt
  FROM barcode_readings
  WHERE {$whereRange}
  GROUP BY DATE(read_time)
  ORDER BY d
");
if ($res) while ($r=$res->fetch_assoc()) {
  $cLabels[] = $r['d'];
  $cKey[] = (int)$r['keyence_cnt'];
  $cSym[] = (int)$r['sym_cnt'];
}

/* Live feed today */
$live=[]; $lastId=0;
$res = $mysqli->query("
  SELECT br.id, br.code, br.source_code, br.read_time,
         cc.SKU, cc.grower
  FROM barcode_readings br
  LEFT JOIN casecodes cc ON cc.serial = br.code
  WHERE DATE(br.read_time)=CURDATE()
  ORDER BY br.id DESC
  LIMIT 120
");
if ($res) while ($r=$res->fetch_assoc()) {
  $live[]=$r;
  $lastId = max($lastId, (int)$r['id']);
}

echo json_encode([
  'ok'=>true,
  'trend'=>['labels'=>$tLabels,'data'=>$tData],
  'sku'=>['labels'=>$skuLabels,'series'=>$skuSeries],
  'compare'=>['labels'=>$cLabels,'keyence'=>$cKey,'symcode'=>$cSym],
  'live'=>['rows'=>$live,'lastId'=>$lastId],
]);
