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

// Centralized KPI and chart data for dashboard.
// Uses only bins_ingresso (inventory / bins received) and boxes_produced (finished boxes).
// Expects: $mysqli (DB connection), $range (one of 'today','7d','30d').
// Optional: $grower, $variety for filters.

if (!isset($grower)) $grower = '';
if (!isset($variety)) $variety = '';

$today = date('Y-m-d');
if (!isset($range)) {
    $range = 'today';
}

switch ($range) {
    case '7d':
        $date_from = date('Y-m-d', strtotime('-6 days'));
        break;
    case '30d':
        $date_from = date('Y-m-d', strtotime('-29 days'));
        break;
    case 'today':
    default:
        $date_from = $today;
        $range = 'today';
        break;
}

// Filters for bins_ingresso
$filter_where_bins = "1=1";
if ($grower !== '') {
    $filter_where_bins .= " AND grower = '" . $mysqli->real_escape_string($grower) . "'";
}
if ($variety !== '') {
    $filter_where_bins .= " AND variety = '" . $mysqli->real_escape_string($variety) . "'";
}

// Filters for boxes_produced (optional, if grower/variety columns exist)
$filter_where_boxes = "1=1";
if ($grower !== '') {
    $filter_where_boxes .= " AND grower = '" . $mysqli->real_escape_string($grower) . "'";
}
if ($variety !== '') {
    $filter_where_boxes .= " AND variety = '" . $mysqli->real_escape_string($variety) . "'";
}

// KPI: total bins in inventory (received bins, filtered)
$full_bins_total = 0;
if ($res = $mysqli->query("SELECT COALESCE(SUM(quantity),0) AS q FROM bins_ingresso WHERE $filter_where_bins")) {
    if ($row = $res->fetch_assoc()) {
        $full_bins_total = (int)$row['q'];
    }
}

// KPI: bins received in selected range
$bins_received_range = 0;
$sql_bins_range = sprintf(
    "SELECT COALESCE(SUM(quantity),0) AS q
     FROM bins_ingresso
     WHERE %s AND date BETWEEN '%s' AND '%s'",
    $filter_where_bins,
    $mysqli->real_escape_string($date_from),
    $mysqli->real_escape_string($today)
);
if ($res = $mysqli->query($sql_bins_range)) {
    if ($row = $res->fetch_assoc()) {
        $bins_received_range = (int)$row['q'];
    }
}

// KPI: boxes produced in selected range
$boxes_today = 0;
$sql_boxes_range = sprintf(
    "SELECT COUNT(*) AS c
     FROM boxes_produced
     WHERE %s AND DATE(read_at) BETWEEN '%s' AND '%s'",
    $filter_where_boxes,
    $mysqli->real_escape_string($date_from),
    $mysqli->real_escape_string($today)
);
if ($res = $mysqli->query($sql_boxes_range)) {
    if ($row = $res->fetch_assoc()) {
        $boxes_today = (int)$row['c'];
    }
}

// For backward compatibility: values related to old legacy_production logic
$dumped_bins_total = 0;
$remaining_bins = $full_bins_total; // no explicit dump/usage tracking anymore
$bins_dumped_today = 0;

// Chart 1: boxes produced (by day or by hour only for today)
$labels_boxes = [];
$data_boxes = [];

if ($range === 'today') {
    $sql = sprintf(
        "SELECT HOUR(read_at) AS h, COUNT(*) AS c
         FROM boxes_produced
         WHERE %s AND DATE(read_at) = '%s'
         GROUP BY HOUR(read_at)
         ORDER BY HOUR(read_at)",
        $filter_where_boxes,
        $mysqli->real_escape_string($today)
    );
    if ($res = $mysqli->query($sql)) {
        while ($row = $res->fetch_assoc()) {
            $labels_boxes[] = sprintf('%02d:00', $row['h']);
            $data_boxes[] = (int)$row['c'];
        }
    }
} else {
    $sql = sprintf(
        "SELECT DATE(read_at) AS d, COUNT(*) AS c
         FROM boxes_produced
         WHERE %s AND DATE(read_at) BETWEEN '%s' AND '%s'
         GROUP BY DATE(read_at)
         ORDER BY DATE(read_at)",
        $filter_where_boxes,
        $mysqli->real_escape_string($date_from),
        $mysqli->real_escape_string($today)
    );
    if ($res = $mysqli->query($sql)) {
        while ($row = $res->fetch_assoc()) {
            $labels_boxes[] = date('d/m', strtotime($row['d']));
            $data_boxes[] = (int)$row['c'];
        }
    }
}

// Chart 2: bins received (by day in the selected range)
$labels_dump = [];
$data_dump = [];

$sql = sprintf(
    "SELECT date AS d, COALESCE(SUM(quantity),0) AS q
     FROM bins_ingresso
     WHERE %s AND date BETWEEN '%s' AND '%s'
     GROUP BY date
     ORDER BY date",
    $filter_where_bins,
    $mysqli->real_escape_string($date_from),
    $mysqli->real_escape_string($today)
);
if ($res = $mysqli->query($sql)) {
    while ($row = $res->fetch_assoc()) {
        $labels_dump[] = date('d/m', strtotime($row['d']));
        $data_dump[] = (int)$row['q'];
    }
}

// Chart 3: inventory snapshot (received vs "used" vs remaining)
// Here "used" is always 0 because legacy_production table has been removed.
$inv_labels = ['Received (all time)', 'Used', 'Remaining'];
$inv_data   = [$full_bins_total, 0, $remaining_bins];

?>
