<?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 = [
    "ringkasan" => [
        "total_sppg" => 0,
        "total_dana" => 0,
        "total_siswa" => 0,
        "total_sekolah" => 0,
        "total_karyawan" => 0,
        "total_bahan" => 0,
        "total_pengaduan" => 0,
        "total_porsi" => 0,
        "total_terdistribusi" => 0,
        "belum_tersalurkan" => 0
    ],
    "sppg" => [],
    "pengaduan" => [],
    "bahan_stok_rendah" => [],
    "laporan_operasional" => []
];

$sqlRingkasan = "
    SELECT
        (SELECT COUNT(*) FROM sppg) AS total_sppg,
        (SELECT COALESCE(SUM(total_dana), 0) FROM sppg) AS total_dana,
        (SELECT COALESCE(SUM(jumlah_siswa), 0) FROM sekolah) AS total_siswa,
        (SELECT COUNT(*) FROM sekolah) AS total_sekolah,
        (SELECT COUNT(*) FROM karyawan) AS total_karyawan,
        (SELECT COUNT(*) FROM bahan_makanan) AS total_bahan,
        (SELECT COUNT(*) FROM pengaduan) AS total_pengaduan,
        (SELECT COALESCE(SUM(jumlah_porsi), 0) FROM laporan_operasional) AS total_porsi,
        (SELECT COALESCE(SUM(jumlah_terdistribusi), 0) FROM laporan_operasional) AS total_terdistribusi
";

$resultRingkasan = $conn->query($sqlRingkasan);

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

    $data["ringkasan"] = [
        "total_sppg" => (int) $row["total_sppg"],
        "total_dana" => (float) $row["total_dana"],
        "total_siswa" => (int) $row["total_siswa"],
        "total_sekolah" => (int) $row["total_sekolah"],
        "total_karyawan" => (int) $row["total_karyawan"],
        "total_bahan" => (int) $row["total_bahan"],
        "total_pengaduan" => (int) $row["total_pengaduan"],
        "total_porsi" => $totalPorsi,
        "total_terdistribusi" => $totalTerdistribusi,
        "belum_tersalurkan" => max($totalPorsi - $totalTerdistribusi, 0)
    ];
}

$sqlSppg = "
    SELECT
        id,
        nama,
        provinsi,
        kabupaten,
        total_dana,
        status,
        created_at
    FROM sppg
    ORDER BY id DESC
";

$resultSppg = $conn->query($sqlSppg);

if ($resultSppg) {
    while ($row = $resultSppg->fetch_assoc()) {
        $row["total_dana"] = (float) $row["total_dana"];
        $data["sppg"][] = $row;
    }
}

$sqlPengaduan = "
    SELECT
        *
    FROM pengaduan
    ORDER BY id DESC
    LIMIT 50
";

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

if ($resultPengaduan) {
    while ($row = $resultPengaduan->fetch_assoc()) {
        $data["pengaduan"][] = $row;
    }
}

$sqlBahan = "
    SELECT
        id,
        sppg_id,
        nama_bahan,
        kategori,
        satuan,
        stok,
        harga_satuan,
        tanggal_masuk
    FROM bahan_makanan
    WHERE stok <= 10
    ORDER BY stok ASC
";

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

if ($resultBahan) {
    while ($row = $resultBahan->fetch_assoc()) {
        $row["stok"] = (float) $row["stok"];
        $row["harga_satuan"] = (float) $row["harga_satuan"];
        $data["bahan_stok_rendah"][] = $row;
    }
}

$sqlLaporan = "
    SELECT
        id,
        sppg_id,
        tanggal,
        menu_makanan,
        jumlah_porsi,
        jumlah_terdistribusi,
        kendala,
        status
    FROM laporan_operasional
    ORDER BY tanggal DESC, id DESC
    LIMIT 50
";

$resultLaporan = $conn->query($sqlLaporan);

if ($resultLaporan) {
    while ($row = $resultLaporan->fetch_assoc()) {
        $row["jumlah_porsi"] = (int) $row["jumlah_porsi"];  
        $row["jumlah_terdistribusi"] = (int) $row["jumlah_terdistribusi"];
        $data["laporan_operasional"][] = $row;
    }
}

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

?>