<?php
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: Content-Type");
header("Access-Control-Allow-Methods: GET, OPTIONS");
header("Content-Type: application/json");

if ($_SERVER["REQUEST_METHOD"] === "OPTIONS") exit;

require "../../config/database.php";
require "../../helpers/response.php";

$provinsi = trim($_GET["provinsi"] ?? "");
$kabupaten = trim($_GET["kabupaten"] ?? "");
$sppgId = isset($_GET["sppg_id"]) ? (int) $_GET["sppg_id"] : 0;
$periode = trim($_GET["periode"] ?? "mingguan");

if (!in_array($periode, ["mingguan", "bulanan"])) {
    $periode = "mingguan";
}

if ($periode === "mingguan") {
    $tanggalMulai = date("Y-m-d", strtotime("monday this week"));
    $tanggalSelesai = date("Y-m-d", strtotime("sunday this week"));
} else {
    $tanggalMulai = date("Y-m-01");
    $tanggalSelesai = date("Y-m-t");
}

$where = ["DATE(tp.created_at) BETWEEN ? AND ?"];
$params = [$tanggalMulai, $tanggalSelesai];
$types = "ss";

if ($provinsi !== "") {
    $where[] = "s.provinsi = ?";
    $params[] = $provinsi;
    $types .= "s";
}

if ($kabupaten !== "") {
    $where[] = "s.kabupaten = ?";
    $params[] = $kabupaten;
    $types .= "s";
}

if ($sppgId > 0) {
    $where[] = "tp.sppg_id = ?";
    $params[] = $sppgId;
    $types .= "i";
}

$whereSql = implode(" AND ", $where);

$sql = "
    SELECT
      COUNT(*) AS total_pengiriman,
      COALESCE(SUM(tp.jumlah_porsi), 0) AS total_porsi,
      SUM(CASE WHEN tp.status = 'selesai' THEN 1 ELSE 0 END) AS total_tervalidasi,
      SUM(CASE WHEN tp.status = 'bermasalah' THEN 1 ELSE 0 END) AS total_bermasalah,
      SUM(CASE WHEN tp.foto_omprengan IS NOT NULL THEN 1 ELSE 0 END) AS bukti_omprengan,
      SUM(CASE WHEN tp.foto_menu IS NOT NULL THEN 1 ELSE 0 END) AS bukti_menu,
      SUM(CASE WHEN tp.foto_penerimaan IS NOT NULL THEN 1 ELSE 0 END) AS bukti_penerimaan
    FROM tracking_pengiriman tp
    LEFT JOIN sppg s ON s.id = tp.sppg_id
    WHERE $whereSql
";

$stmt = $conn->prepare($sql);
if (!$stmt) {
    response(false, "Query rekap gagal disiapkan: " . $conn->error, null);
}
$stmt->bind_param($types, ...$params);
$stmt->execute();
$summary = $stmt->get_result()->fetch_assoc();

$sqlItems = "
    SELECT
      tp.id,
      tp.sppg_id,
      tp.nama_sekolah,
      tp.jumlah_porsi,
      tp.jumlah_diterima,
      tp.status,
      tp.validasi_menu,
      tp.validasi_jumlah,
      tp.validasi_kondisi,
      tp.created_at,
      tp.updated_at,
      s.nama AS nama_sppg,
      s.provinsi,
      s.kabupaten
    FROM tracking_pengiriman tp
    LEFT JOIN sppg s ON s.id = tp.sppg_id
    WHERE $whereSql
    ORDER BY tp.updated_at DESC
    LIMIT 100
";

$stmtItems = $conn->prepare($sqlItems);
$stmtItems->bind_param($types, ...$params);
$stmtItems->execute();
$resultItems = $stmtItems->get_result();

$items = [];
while ($row = $resultItems->fetch_assoc()) {
    $row["id"] = (int) $row["id"];
    $row["sppg_id"] = (int) $row["sppg_id"];
    $row["jumlah_porsi"] = (int) $row["jumlah_porsi"];
    $row["jumlah_diterima"] = $row["jumlah_diterima"] !== null ? (int) $row["jumlah_diterima"] : null;
    $items[] = $row;
}

response(true, "Rekap pengiriman berhasil diambil", [
    "periode" => $periode,
    "tanggal_mulai" => $tanggalMulai,
    "tanggal_selesai" => $tanggalSelesai,
    "filter" => [
        "provinsi" => $provinsi,
        "kabupaten" => $kabupaten,
        "sppg_id" => $sppgId
    ],
    "summary" => [
        "total_pengiriman" => (int) ($summary["total_pengiriman"] ?? 0),
        "total_porsi" => (int) ($summary["total_porsi"] ?? 0),
        "total_tervalidasi" => (int) ($summary["total_tervalidasi"] ?? 0),
        "total_bermasalah" => (int) ($summary["total_bermasalah"] ?? 0),
        "bukti_omprengan" => (int) ($summary["bukti_omprengan"] ?? 0),
        "bukti_menu" => (int) ($summary["bukti_menu"] ?? 0),
        "bukti_penerimaan" => (int) ($summary["bukti_penerimaan"] ?? 0)
    ],
    "items" => $items
]);
?>
