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

$data = [
    "sppg_per_wilayah" => [],
    "dana_per_sppg" => [],
    "siswa_per_sppg" => [],
    "pengaduan_status" => [],
    "distribusi" => [
        "total_porsi" => 0,
        "total_terdistribusi" => 0,
        "belum_tersalurkan" => 0
    ],
    "stok_bahan" => [],
];

/* SPPG per wilayah */
$sqlWilayah = "
    SELECT 
        COALESCE(kabupaten, 'Tidak Diketahui') AS wilayah,
        COUNT(*) AS total
    FROM sppg
    GROUP BY kabupaten
    ORDER BY total DESC
";

$resultWilayah = $conn->query($sqlWilayah);

if ($resultWilayah) {
    while ($row = $resultWilayah->fetch_assoc()) {
        $data["sppg_per_wilayah"][] = [
            "name" => $row["wilayah"],
            "value" => (int) $row["total"]
        ];
    }
}

/* Dana per SPPG */
$sqlDana = "
    SELECT
        nama,
        COALESCE(total_dana, 0) AS total_dana
    FROM sppg
    ORDER BY total_dana DESC
    LIMIT 10
";

$resultDana = $conn->query($sqlDana);

if ($resultDana) {
    while ($row = $resultDana->fetch_assoc()) {
        $data["dana_per_sppg"][] = [
            "name" => $row["nama"],
            "value" => (float) $row["total_dana"]
        ];
    }
}

/* Siswa per SPPG */
$sqlSiswa = "
    SELECT
        s.nama,
        COALESCE(SUM(se.jumlah_siswa), 0) AS total_siswa
    FROM sppg s
    LEFT JOIN sekolah se ON se.sppg_id = s.id
    GROUP BY s.id, s.nama
    ORDER BY total_siswa DESC
    LIMIT 10
";

$resultSiswa = $conn->query($sqlSiswa);

if ($resultSiswa) {
    while ($row = $resultSiswa->fetch_assoc()) {
        $data["siswa_per_sppg"][] = [
            "name" => $row["nama"],
            "value" => (int) $row["total_siswa"]
        ];
    }
}

/* Pengaduan berdasarkan status */
$sqlPengaduan = "
    SELECT
        status,
        COUNT(*) AS total
    FROM pengaduan
    GROUP BY status
";

$resultPengaduan = $conn->query($sqlPengaduan);

if ($resultPengaduan) {
    while ($row = $resultPengaduan->fetch_assoc()) {
        $data["pengaduan_status"][] = [
            "name" => $row["status"],
            "value" => (int) $row["total"]
        ];
    }
}

/* Distribusi porsi */
$sqlDistribusi = "
    SELECT
        COALESCE(SUM(jumlah_porsi), 0) AS total_porsi,
        COALESCE(SUM(jumlah_terdistribusi), 0) AS total_terdistribusi
    FROM laporan_operasional
";

$resultDistribusi = $conn->query($sqlDistribusi);

if ($resultDistribusi && $row = $resultDistribusi->fetch_assoc()) {
    $totalPorsi = (int) $row["total_porsi"];
    $totalTerdistribusi = (int) $row["total_terdistribusi"];

    $data["distribusi"] = [
        "total_porsi" => $totalPorsi,
        "total_terdistribusi" => $totalTerdistribusi,
        "belum_tersalurkan" => max($totalPorsi - $totalTerdistribusi, 0)
    ];
}

/* Stok bahan makanan */
$sqlBahan = "
    SELECT
        nama_bahan,
        COALESCE(stok, 0) AS stok
    FROM bahan_makanan
    ORDER BY stok ASC
    LIMIT 10
";

$resultBahan = $conn->query($sqlBahan);

if ($resultBahan) {
    while ($row = $resultBahan->fetch_assoc()) {
        $data["stok_bahan"][] = [
            "name" => $row["nama_bahan"],
            "value" => (float) $row["stok"]
        ];
    }
}

response(true, "Data statistik berhasil diambil", $data);

?>