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

function cols($conn, $table) {
    $out = [];
    $res = $conn->query("SHOW COLUMNS FROM `$table`");
    if ($res) while ($row = $res->fetch_assoc()) $out[] = $row["Field"];
    return $out;
}
function has($cols, $name) { return in_array($name, $cols); }

$sppgCols = cols($conn, "sppg");
$sekolahCols = cols($conn, "sekolah");

$role = trim($_GET["role"] ?? "publik");
$sppgId = isset($_GET["sppg_id"]) ? (int) $_GET["sppg_id"] : 0;
$sekolahId = isset($_GET["sekolah_id"]) ? (int) $_GET["sekolah_id"] : 0;
$status = trim($_GET["status"] ?? "");
$tanggal = trim($_GET["tanggal"] ?? date("Y-m-d"));
$provinsi = trim($_GET["provinsi"] ?? "");
$kabupaten = trim($_GET["kabupaten"] ?? "");

$sNama = has($sppgCols, "nama") ? "s.nama" : "NULL";
$sAlamat = has($sppgCols, "alamat") ? "s.alamat" : "NULL";
$sProvinsi = has($sppgCols, "provinsi") ? "s.provinsi" : "NULL";
$sKabupaten = has($sppgCols, "kabupaten") ? "s.kabupaten" : "NULL";
$sStatus = has($sppgCols, "status") ? "s.status" : "NULL";
$sDana1 = has($sppgCols, "dana_diterima") ? "s.dana_diterima" : "0";
$sDana2 = has($sppgCols, "dana_dibelanjakan") ? "s.dana_dibelanjakan" : "0";

$skNama = has($sekolahCols, "nama_sekolah") ? "COALESCE(sk.nama_sekolah, tp.nama_sekolah)" : "tp.nama_sekolah";
$skAlamat = has($sekolahCols, "alamat") ? "COALESCE(sk.alamat, tp.tujuan_alamat)" : "tp.tujuan_alamat";
$skJenjang = has($sekolahCols, "jenjang") ? "sk.jenjang" : "NULL";
$skJumlah = has($sekolahCols, "jumlah_siswa") ? "sk.jumlah_siswa" : "0";

$where = ["DATE(tp.created_at) = ?"];
$params = [$tanggal];
$types = "s";

if ($role === "sppg" && $sppgId > 0) { $where[] = "tp.sppg_id = ?"; $params[] = $sppgId; $types .= "i"; }
if (($role === "validator" || $role === "sekolah") && $sekolahId > 0) { $where[] = "tp.sekolah_id = ?"; $params[] = $sekolahId; $types .= "i"; }
if ($status !== "" && $status !== "semua") { $where[] = "tp.status = ?"; $params[] = $status; $types .= "s"; }
if ($provinsi !== "" && has($sppgCols, "provinsi")) { $where[] = "s.provinsi = ?"; $params[] = $provinsi; $types .= "s"; }
if ($kabupaten !== "" && has($sppgCols, "kabupaten")) { $where[] = "s.kabupaten = ?"; $params[] = $kabupaten; $types .= "s"; }

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

$sql = "SELECT
  tp.*,
  $sNama AS nama_sppg,
  $sAlamat AS alamat_sppg,
  $sProvinsi AS provinsi,
  $sKabupaten AS kabupaten,
  $sStatus AS status_sppg,
  $sDana1 AS dana_diterima,
  $sDana2 AS dana_dibelanjakan,
  $skNama AS nama_sekolah_real,
  $skAlamat AS alamat_sekolah,
  $skJenjang AS jenjang,
  $skJumlah AS jumlah_siswa
FROM tracking_pengiriman tp
LEFT JOIN sppg s ON s.id = tp.sppg_id
LEFT JOIN sekolah sk ON sk.id = tp.sekolah_id
WHERE $whereSql
ORDER BY tp.updated_at DESC, tp.id DESC";

$stmt = $conn->prepare($sql);
if (!$stmt) response(false, "Query tracking gagal disiapkan: " . $conn->error, ["sql" => $sql]);

$stmt->bind_param($types, ...$params);
if (!$stmt->execute()) response(false, "Query tracking gagal dijalankan: " . $stmt->error, null);

$result = $stmt->get_result();
$data = [];

while ($row = $result->fetch_assoc()) {
    $row["id"] = (int) $row["id"];
    $row["sppg_id"] = (int) $row["sppg_id"];
    $row["sekolah_id"] = $row["sekolah_id"] !== null ? (int) $row["sekolah_id"] : null;
    $row["jumlah_porsi"] = (int) $row["jumlah_porsi"];
    $row["jumlah_omprengan"] = (int) $row["jumlah_omprengan"];
    $row["jumlah_diterima"] = $row["jumlah_diterima"] !== null ? (int) $row["jumlah_diterima"] : null;
    $row["dana_diterima"] = (int) ($row["dana_diterima"] ?? 0);
    $row["dana_dibelanjakan"] = (int) ($row["dana_dibelanjakan"] ?? 0);
    $row["jumlah_siswa"] = (int) ($row["jumlah_siswa"] ?? 0);
    $row["nama_sekolah"] = $row["nama_sekolah_real"] ?: $row["nama_sekolah"];
    $row["tujuan_alamat"] = $row["alamat_sekolah"] ?: $row["tujuan_alamat"];
    unset($row["nama_sekolah_real"]);
    $data[] = $row;
}

$summary = ["total_pengiriman" => count($data), "berjalan" => 0, "selesai" => 0, "bermasalah" => 0, "total_porsi" => 0];

foreach ($data as $item) {
    if (in_array($item["status"], ["persiapan","packing","berangkat","dalam_perjalanan","tiba_di_sekolah","diterima_sekolah"])) $summary["berjalan"]++;
    if ($item["status"] === "selesai") $summary["selesai"]++;
    if ($item["status"] === "bermasalah") $summary["bermasalah"]++;
    $summary["total_porsi"] += (int) $item["jumlah_porsi"];
}

response(true, "Data tracking pengiriman sinkron berhasil diambil", ["summary" => $summary, "items" => $data]);
?>