<?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"] ?? "");
$limit = isset($_GET["limit"]) ? (int) $_GET["limit"] : 3;

if ($limit <= 0) {
    $limit = 3;
}

if ($limit > 10) {
    $limit = 10;
}

$whereProvinsi = "";
$params = [];
$types = "";

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

$sql = "
    SELECT
        s.id,
        s.nama,
        s.provinsi,
        s.kabupaten,

        COALESCE(AVG(p.rating), 0) AS avg_rating,
        COUNT(DISTINCT p.id) AS total_ulasan,

        COUNT(DISTINCT pg.id) AS total_pengaduan,
        COALESCE(SUM(CASE WHEN pg.status = 'Selesai' THEN 1 ELSE 0 END), 0) AS pengaduan_selesai,

        COUNT(DISTINCT v.id) AS total_validasi,
        COALESCE(SUM(CASE WHEN v.status = 'sesuai' THEN 1 ELSE 0 END), 0) AS validasi_sesuai,

        (
            (COALESCE(AVG(p.rating), 0) / 5) * 50
            +
            (
                CASE
                    WHEN COUNT(DISTINCT pg.id) = 0 THEN 1
                    ELSE COALESCE(SUM(CASE WHEN pg.status = 'Selesai' THEN 1 ELSE 0 END), 0) / COUNT(DISTINCT pg.id)
                END
            ) * 25
            +
            (
                CASE
                    WHEN COUNT(DISTINCT v.id) = 0 THEN 0
                    ELSE COALESCE(SUM(CASE WHEN v.status = 'sesuai' THEN 1 ELSE 0 END), 0) / COUNT(DISTINCT v.id)
                END
            ) * 25
        ) AS skor

    FROM sppg s

    LEFT JOIN sppg_penilaian p
      ON p.sppg_id = s.id
      AND YEAR(p.created_at) = YEAR(CURDATE())
      AND MONTH(p.created_at) = MONTH(CURDATE())

    LEFT JOIN pengaduan pg
      ON pg.sppg_id = s.id
      AND YEAR(pg.created_at) = YEAR(CURDATE())
      AND MONTH(pg.created_at) = MONTH(CURDATE())

    LEFT JOIN sppg_validasi_sekolah v
      ON v.sppg_id = s.id
      AND YEAR(v.created_at) = YEAR(CURDATE())
      AND MONTH(v.created_at) = MONTH(CURDATE())

    $whereProvinsi

    GROUP BY s.id, s.nama, s.provinsi, s.kabupaten
    ORDER BY s.provinsi ASC, skor DESC, avg_rating DESC, total_ulasan DESC, s.nama ASC
";

$stmt = $conn->prepare($sql);

if (!$stmt) {
    response(false, "Query gagal disiapkan: " . $conn->error, null);
}

if (count($params) > 0) {
    $stmt->bind_param($types, ...$params);
}

$stmt->execute();
$result = $stmt->get_result();

$grouped = [];

while ($row = $result->fetch_assoc()) {
    $prov = $row["provinsi"] ?: "Tanpa Provinsi";

    if (!isset($grouped[$prov])) {
        $grouped[$prov] = [];
    }

    if (count($grouped[$prov]) >= $limit) {
        continue;
    }

    $totalPengaduan = (int) $row["total_pengaduan"];
    $pengaduanSelesai = (int) $row["pengaduan_selesai"];
    $totalValidasi = (int) $row["total_validasi"];
    $validasiSesuai = (int) $row["validasi_sesuai"];

    $grouped[$prov][] = [
        "id" => (int) $row["id"],
        "nama" => $row["nama"],
        "provinsi" => $prov,
        "kabupaten" => $row["kabupaten"],
        "rating" => round((float) $row["avg_rating"], 1),
        "total_ulasan" => (int) $row["total_ulasan"],
        "total_pengaduan" => $totalPengaduan,
        "pengaduan_selesai" => $pengaduanSelesai,
        "total_validasi" => $totalValidasi,
        "validasi_sesuai" => $validasiSesuai,
        "skor" => round((float) $row["skor"], 1)
    ];
}

$data = [];

foreach ($grouped as $prov => $items) {
    $data[] = [
        "provinsi" => $prov,
        "items" => $items
    ];
}

response(true, "Data SPPG terbaik bulan ini berhasil diambil", $data);

?>  