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

// Compatibility: support both legacy `barcode_scans` and newer `barcode_readings`.
$scanTable = null;
$q = $mysqli->query("SHOW TABLES LIKE 'barcode_readings'");
if ($q && $q->num_rows > 0) {
  $scanTable = 'barcode_readings';
}
$q = $mysqli->query("SHOW TABLES LIKE 'barcode_scans'");
if (!$scanTable && $q && $q->num_rows > 0) {
  $scanTable = 'barcode_scans';
}

if (!$scanTable) {
  http_response_code(500);
  echo json_encode(['ok'=>false,'error'=>'No scan table found (barcode_readings/barcode_scans)']);
  exit;
}

$colSource = ($scanTable === 'barcode_readings') ? 'source_code' : 'source';
$colTime   = ($scanTable === 'barcode_readings') ? 'read_time' : 'scanned_at';

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) : '';

/**
 * Production sources:
 * - KEYENCE
 * - all enabled SYMCODE readers scan_role=PROD
 * - MANUAL (optional: keep visible)
 */
$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);

// include legacy NULL as production-visible for historical continuity (only for barcode_readings)
$whereSource = ($scanTable === 'barcode_readings')
  ? "(br.{$colSource} IN {$inSources} OR br.{$colSource} IS NULL)"
  : "(br.{$colSource} IN {$inSources})";

// KPI
// NOTE: counts are *scans* (rows in barcode_readings), not unique casecodes.
$kpiToday = 0;
$kpiHour  = 0;
$kpiWeek  = 0;
$kpiMonth = 0;
$kpiTotal = 0;
$res = $mysqli->query("SELECT COUNT(*) c FROM {$scanTable} br WHERE DATE(br.{$colTime})=CURDATE() AND {$whereSource}");
if ($res && ($row=$res->fetch_assoc())) $kpiToday = (int)$row['c'];

$res = $mysqli->query("SELECT COUNT(*) c FROM {$scanTable} br WHERE br.{$colTime} >= DATE_SUB(NOW(), INTERVAL 1 HOUR) AND {$whereSource}");
if ($res && ($row=$res->fetch_assoc())) $kpiHour = (int)$row['c'];

$res = $mysqli->query("SELECT COUNT(*) c FROM {$scanTable} br WHERE br.{$colTime} >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND {$whereSource}");
if ($res && ($row=$res->fetch_assoc())) $kpiWeek = (int)$row['c'];

$res = $mysqli->query("SELECT COUNT(*) c FROM {$scanTable} br WHERE br.{$colTime} >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND {$whereSource}");
if ($res && ($row=$res->fetch_assoc())) $kpiMonth = (int)$row['c'];

$res = $mysqli->query("SELECT COUNT(*) c FROM {$scanTable} br WHERE {$whereSource}");
if ($res && ($row=$res->fetch_assoc())) $kpiTotal = (int)$row['c'];

// overall trend last 30 days
$trendLabels=[]; $trendData=[];
$res = $mysqli->query("
	SELECT DATE(br.{$colTime}) d, COUNT(*) c
  FROM {$scanTable} br
  WHERE {$whereSource}
    AND br.{$colTime} >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
	GROUP BY DATE(br.{$colTime})
	ORDER BY DATE(br.{$colTime})
");
if ($res) while ($row=$res->fetch_assoc()) {
  $trendLabels[] = $row['d'];
  $trendData[] = (int)$row['c'];
}

// SKU list for selector (within range)
$skuOptions=[];
$res = $mysqli->query("
  SELECT DISTINCT cc.SKU
  FROM {$scanTable} br
  LEFT JOIN casecodes cc ON cc.serial = br.code
  WHERE {$whereSource}
    AND br.{$colTime} >= DATE_SUB(CURDATE(), INTERVAL {$days} DAY)
    AND cc.SKU IS NOT NULL AND cc.SKU <> ''
  ORDER BY cc.SKU
");
if ($res) while ($row=$res->fetch_assoc()) $skuOptions[] = (string)$row['SKU'];

// SKU daily trend (stacked): top 8 OR selected SKU
$skusForChart=[];
if ($selectedSkuSql !== '') {
  $skusForChart = [$selectedSku];
} else {
  $res = $mysqli->query("
    SELECT cc.SKU, COUNT(*) total
    FROM {$scanTable} br
    LEFT JOIN casecodes cc ON cc.serial = br.code
    WHERE {$whereSource}
      AND br.{$colTime} >= DATE_SUB(CURDATE(), INTERVAL {$days} DAY)
      AND cc.SKU IS NOT NULL AND cc.SKU <> ''
    GROUP BY cc.SKU
    ORDER BY total DESC
    LIMIT 8
  ");
  if ($res) while ($row=$res->fetch_assoc()) $skusForChart[] = (string)$row['SKU'];
}

$skuLabels=[]; $skuSeries=[]; // sku => counts aligned
if (!empty($skusForChart)) {
  $inSku = in_list($mysqli, $skusForChart);
  $skuWhere = ($selectedSkuSql !== '') ? " AND cc.SKU = '{$selectedSkuSql}' " : "";

  $res = $mysqli->query("
    SELECT DATE(br.{$colTime}) d, cc.SKU, COUNT(*) total
    FROM {$scanTable} br
    LEFT JOIN casecodes cc ON cc.serial = br.code
    WHERE {$whereSource}
      AND br.{$colTime} >= DATE_SUB(CURDATE(), INTERVAL {$days} DAY)
      AND cc.SKU IN {$inSku}
      {$skuWhere}
    GROUP BY DATE(br.{$colTime}), cc.SKU
    ORDER BY DATE(br.{$colTime})
  ");

  $tmp=[];
  if ($res) while ($row=$res->fetch_assoc()) {
    $d=(string)$row['d']; $sku=(string)$row['SKU'];
    if (!in_array($d, $skuLabels, true)) $skuLabels[]=$d;
    $tmp[$sku][$d]=(int)$row['total'];
  }

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

// Compare Keyence vs Symcode (production-only)
$cmpLabels=[]; $cmpKeyence=[]; $cmpSymcode=[];
$inSym = in_list($mysqli, $prodSym ?: ['__NONE__']); // avoid empty IN ()
$res = $mysqli->query("
  SELECT DATE(br.{$colTime}) d,
         SUM(CASE WHEN br.{$colSource}='KEYENCE' " . (($scanTable==='barcode_readings') ? "OR br.{$colSource} IS NULL" : "") . " THEN 1 ELSE 0 END) AS keyence_cnt,
         SUM(CASE WHEN br.{$colSource} IN {$inSym} THEN 1 ELSE 0 END) AS sym_cnt
  FROM {$scanTable} br
  WHERE {$whereSource}
    AND br.{$colTime} >= DATE_SUB(CURDATE(), INTERVAL {$days} DAY)
  GROUP BY DATE(br.{$colTime})
  ORDER BY DATE(br.{$colTime})
");
if ($res) while ($row=$res->fetch_assoc()) {
  $cmpLabels[] = $row['d'];
  $cmpKeyence[] = (int)$row['keyence_cnt'];
  $cmpSymcode[] = (int)$row['sym_cnt'];
}

// Live feed
$live=[];
$res = $mysqli->query("
  SELECT br.id, br.code, br.{$colSource} AS source_code, br.{$colTime} AS read_time,
         cc.SKU, cc.grower, cc.variety, cc.packaging, cc.size
  FROM {$scanTable} br
  LEFT JOIN casecodes cc ON cc.serial = br.code
  WHERE {$whereSource}
    AND DATE(br.{$colTime})=CURDATE()
  ORDER BY br.id DESC
  LIMIT 120
");
$lastId = 0;
if ($res) while ($row=$res->fetch_assoc()) {
  $live[]=$row;
  $lastId = max($lastId, (int)$row['id']);
}

echo json_encode([
  'ok'=>true,
  'range'=>$range,
  'scan_table'=>$scanTable,
  'kpi'=>[
    'today'=>$kpiToday,
    'hour'=>$kpiHour,
    'week'=>$kpiWeek,
    'month'=>$kpiMonth,
    'total'=>$kpiTotal,
  ],
  'trend30'=>['labels'=>$trendLabels,'data'=>$trendData],
  'skuOptions'=>$skuOptions,
  'skuTrend'=>['labels'=>$skuLabels,'series'=>$skuSeries],
  'compare'=>['labels'=>$cmpLabels,'keyence'=>$cmpKeyence,'symcode'=>$cmpSymcode],
  'live'=>['rows'=>$live,'lastId'=>$lastId],
]);
