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

$query = "
    SELECT
        s.provinsi,
        COUNT(DISTINCT s.id) AS total_sppg,
        COUNT(DISTINCT sk.id) AS total_sekolah,
        COALESCE(SUM(sk.jumlah_siswa), 0) AS total_siswa,
        COALESCE(SUM(DISTINCT s.total_dana), 0) AS total_dana
    FROM sppg s
    LEFT JOIN sekolah sk ON sk.sppg_id = s.id
    GROUP BY s.provinsi
    ORDER BY total_siswa DESC
";

$result = $conn->query($query);

$data = [];

while ($row = $result->fetch_assoc()) {
    $data[] = [
        "provinsi" => $row["provinsi"] ?: "Tidak diketahui",
        "total_sppg" => (int) $row["total_sppg"],
        "total_sekolah" => (int) $row["total_sekolah"],
        "total_siswa" => (int) $row["total_siswa"],
        "total_dana" => (int) $row["total_dana"]
    ];
}

response(true, "Statistik provinsi berhasil diambil", $data);

?>