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

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

header('Content-Type: application/json');

$now = date('Y-m-d H:i:s');

// Basic counts
function q_int($mysqli, $sql) {
    $res = $mysqli->query($sql);
    if ($res && ($row = $res->fetch_assoc())) {
        return (int)($row['c'] ?? 0);
    }
    return 0;
}

$data = [];

$data['boxes_today'] = q_int($mysqli, "
    SELECT COUNT(*) AS c
    FROM boxes_produced
    WHERE DATE(read_at) = CURDATE()
");

$data['boxes_last_hour'] = q_int($mysqli, "
    SELECT COUNT(*) AS c
    FROM boxes_produced
    WHERE read_at >= (NOW() - INTERVAL 1 HOUR)
");

$data['boxes_last_15m'] = q_int($mysqli, "
    SELECT COUNT(*) AS c
    FROM boxes_produced
    WHERE read_at >= (NOW() - INTERVAL 15 MINUTE)
");

$data['boxes_week'] = q_int($mysqli, "
    SELECT COUNT(*) AS c
    FROM boxes_produced
    WHERE YEARWEEK(read_at,1) = YEARWEEK(CURDATE(),1)
");

$data['boxes_month'] = q_int($mysqli, "
    SELECT COUNT(*) AS c
    FROM boxes_produced
    WHERE YEAR(read_at) = YEAR(CURDATE())
      AND MONTH(read_at) = MONTH(CURDATE())
");

// Boxes today by hour
$hours = array_fill(0, 24, 0);
$res = $mysqli->query("
    SELECT HOUR(read_at) AS h, COUNT(*) AS c
    FROM boxes_produced
    WHERE DATE(read_at) = CURDATE()
    GROUP BY HOUR(read_at)
    ORDER BY h
");
if ($res) {
    while ($row = $res->fetch_assoc()) {
        $h = (int)$row['h'];
        $hours[$h] = (int)$row['c'];
    }
}
$data['boxes_by_hour'] = $hours;

// Top varieties
$topVar = [];
$res = $mysqli->query("
    SELECT variety, COUNT(*) AS c
    FROM boxes_produced
    WHERE variety IS NOT NULL AND variety <> ''
    GROUP BY variety
    ORDER BY c DESC
    LIMIT 10
");
if ($res) {
    while ($row = $res->fetch_assoc()) {
        $topVar[] = [
            'label' => $row['variety'],
            'count' => (int)$row['c']
        ];
    }
}
$data['top_varieties'] = $topVar;

// Top growers
$topGrow = [];
$res = $mysqli->query("
    SELECT grower, COUNT(*) AS c
    FROM boxes_produced
    WHERE grower IS NOT NULL AND grower <> ''
    GROUP BY grower
    ORDER BY c DESC
    LIMIT 10
");
if ($res) {
    while ($row = $res->fetch_assoc()) {
        $topGrow[] = [
            'label' => $row['grower'],
            'count' => (int)$row['c']
        ];
    }
}
$data['top_growers'] = $topGrow;

echo json_encode($data);
