<?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 getColumns($conn, $table) {
    $cols = [];
    $res = $conn->query("SHOW COLUMNS FROM `$table`");

    if ($res) {
        while ($row = $res->fetch_assoc()) {
            $cols[] = $row["Field"];
        }
    }

    return $cols;
}

function hasColumn($cols, $name) {
    return in_array($name, $cols);
}

$sppgId = isset($_GET["sppg_id"]) ? (int) $_GET["sppg_id"] : 0;

if ($sppgId <= 0) {
    response(false, "ID SPPG tidak valid", null);
}

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

$sppgNama = hasColumn($sppgCols, "nama") ? "nama" : "''";
$sppgAlamat = hasColumn($sppgCols, "alamat") ? "alamat" : "''";
$sppgProvinsi = hasColumn($sppgCols, "provinsi") ? "provinsi" : "''";
$sppgKabupaten = hasColumn($sppgCols, "kabupaten") ? "kabupaten" : "''";
$sppgStatus = hasColumn($sppgCols, "status") ? "status" : "''";
$sppgDanaDiterima = hasColumn($sppgCols, "dana_diterima") ? "dana_diterima" : "0";
$sppgDanaDibelanjakan = hasColumn($sppgCols, "dana_dibelanjakan") ? "dana_dibelanjakan" : "0";

$sqlSppg = "
    SELECT
      id,
      $sppgNama AS nama,
      $sppgAlamat AS alamat,
      $sppgProvinsi AS provinsi,
      $sppgKabupaten AS kabupaten,
      $sppgStatus AS status,
      $sppgDanaDiterima AS dana_diterima,
      $sppgDanaDibelanjakan AS dana_dibelanjakan
    FROM sppg
    WHERE id = ?
    LIMIT 1
";

$stmtSppg = $conn->prepare($sqlSppg);

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

$stmtSppg->bind_param("i", $sppgId);
$stmtSppg->execute();
$resSppg = $stmtSppg->get_result();

if (!$resSppg || $resSppg->num_rows === 0) {
    response(false, "Data SPPG tidak ditemukan", null);
}

$sppg = $resSppg->fetch_assoc();

$sekolah = [];

$namaSekolah = hasColumn($sekolahCols, "nama_sekolah") ? "nama_sekolah" : "''";
$alamatSekolah = hasColumn($sekolahCols, "alamat") ? "alamat" : "''";
$jenjangSekolah = hasColumn($sekolahCols, "jenjang") ? "jenjang" : "''";
$jumlahSiswa = hasColumn($sekolahCols, "jumlah_siswa") ? "jumlah_siswa" : "0";
$createdAt = hasColumn($sekolahCols, "created_at") ? "created_at" : "NULL";

if (!hasColumn($sekolahCols, "sppg_id")) {
    response(false, "Tabel sekolah belum punya kolom sppg_id", null);
}

$sqlSekolah = "
    SELECT
      id,
      sppg_id,
      $namaSekolah AS nama_sekolah,
      $alamatSekolah AS alamat,
      $jenjangSekolah AS jenjang,
      $jumlahSiswa AS jumlah_siswa,
      $createdAt AS created_at
    FROM sekolah
    WHERE sppg_id = ?
    ORDER BY nama_sekolah ASC
";

$stmtSekolah = $conn->prepare($sqlSekolah);

if (!$stmtSekolah) {
    response(false, "Query sekolah gagal disiapkan: " . $conn->error, [
        "sql" => $sqlSekolah
    ]);
}

$stmtSekolah->bind_param("i", $sppgId);
$stmtSekolah->execute();
$resSekolah = $stmtSekolah->get_result();

while ($row = $resSekolah->fetch_assoc()) {
    $sekolah[] = [
        "id" => (int) $row["id"],
        "sppg_id" => (int) $row["sppg_id"],
        "nama_sekolah" => $row["nama_sekolah"],
        "alamat" => $row["alamat"],
        "jenjang" => $row["jenjang"],
        "jumlah_siswa" => (int) ($row["jumlah_siswa"] ?? 0),
        "created_at" => $row["created_at"]
    ];
}

response(true, "Data SPPG dan sekolah berhasil diambil", [
    "sppg" => [
        "id" => (int) $sppg["id"],
        "nama" => $sppg["nama"],
        "alamat" => $sppg["alamat"],
        "provinsi" => $sppg["provinsi"],
        "kabupaten" => $sppg["kabupaten"],
        "status" => $sppg["status"],
        "dana_diterima" => (int) ($sppg["dana_diterima"] ?? 0),
        "dana_dibelanjakan" => (int) ($sppg["dana_dibelanjakan"] ?? 0)
    ],
    "sekolah" => $sekolah
]);
?>