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

$provinsiList = [];
$kabupatenList = [];
$sppgList = [];

$resProv = $conn->query("
    SELECT DISTINCT provinsi
    FROM sppg
    WHERE provinsi IS NOT NULL AND provinsi != ''
    ORDER BY provinsi ASC
");

if ($resProv) {
    while ($row = $resProv->fetch_assoc()) {
        $provinsiList[] = $row["provinsi"];
    }
}

$whereKab = [];
$paramsKab = [];
$typesKab = "";

if ($provinsi !== "") {
    $whereKab[] = "provinsi = ?";
    $paramsKab[] = $provinsi;
    $typesKab .= "s";
}

$sqlKab = "
    SELECT DISTINCT kabupaten
    FROM sppg
    WHERE kabupaten IS NOT NULL AND kabupaten != ''
";

if (count($whereKab) > 0) {
    $sqlKab .= " AND " . implode(" AND ", $whereKab);
}

$sqlKab .= " ORDER BY kabupaten ASC";

$stmtKab = $conn->prepare($sqlKab);
if ($stmtKab) {
    if (count($paramsKab) > 0) {
        $stmtKab->bind_param($typesKab, ...$paramsKab);
    }
    $stmtKab->execute();
    $resKab = $stmtKab->get_result();

    while ($row = $resKab->fetch_assoc()) {
        $kabupatenList[] = $row["kabupaten"];
    }
}

$whereSppg = [];
$paramsSppg = [];
$typesSppg = "";

if ($provinsi !== "") {
    $whereSppg[] = "provinsi = ?";
    $paramsSppg[] = $provinsi;
    $typesSppg .= "s";
}

if ($kabupaten !== "") {
    $whereSppg[] = "kabupaten = ?";
    $paramsSppg[] = $kabupaten;
    $typesSppg .= "s";
}

$sqlSppg = "
    SELECT id, nama, provinsi, kabupaten
    FROM sppg
";

if (count($whereSppg) > 0) {
    $sqlSppg .= " WHERE " . implode(" AND ", $whereSppg);
}

$sqlSppg .= " ORDER BY nama ASC";

$stmtSppg = $conn->prepare($sqlSppg);
if (!$stmtSppg) {
    response(false, "Query opsi SPPG gagal disiapkan: " . $conn->error, null);
}

if (count($paramsSppg) > 0) {
    $stmtSppg->bind_param($typesSppg, ...$paramsSppg);
}

$stmtSppg->execute();
$resSppg = $stmtSppg->get_result();

while ($row = $resSppg->fetch_assoc()) {
    $sppgList[] = [
        "id" => (int) $row["id"],
        "nama" => $row["nama"],
        "provinsi" => $row["provinsi"],
        "kabupaten" => $row["kabupaten"]
    ];
}

response(true, "Opsi filter rekap berhasil diambil", [
    "provinsi" => $provinsiList,
    "kabupaten" => $kabupatenList,
    "sppg" => $sppgList
]);
?>
