<?php
// ============================================================
// api.php — REST API WebGIS Rumah Ibadah
// Endpoint: /api.php?resource=rumah_ibadah&action=...
//           /api.php?resource=lokasi_bantuan&action=...
//           /api.php?resource=sumbangan&action=...
//           /api.php?resource=penyaluran&action=...
// ============================================================

require_once 'koneksi.php';

header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type');

// Preflight CORS
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') { http_response_code(200); exit; }

$method   = $_SERVER['REQUEST_METHOD'];
$resource = $_GET['resource'] ?? '';
$action   = $_GET['action']   ?? 'list';
$id       = isset($_GET['id']) ? (int)$_GET['id'] : null;
$body     = json_decode(file_get_contents('php://input'), true) ?? [];

// ============================================================
// ROUTER
// ============================================================
try {
    switch ($resource) {
        case 'rumah_ibadah':
            handleRumahIbadah($method, $action, $id, $body);
            break;
        case 'lokasi_bantuan':
            handleLokasiBantuan($method, $action, $id, $body);
            break;
        case 'sumbangan':
            handleSumbangan($method, $action, $id, $body);
            break;
        case 'penyaluran':
            handlePenyaluran($method, $action, $id, $body);
            break;
        case 'spbu':
            handleSpbu($method, $action, $id, $body);
            break;
        case 'jalan':
            handleJalan($method, $action, $id, $body);
            break;
        case 'parsil':
            handleParsil($method, $action, $id, $body);
            break;
        case 'setup':
            handleSetup();
            break;
        default:
            jsonError('Resource tidak ditemukan.', 404);
    }
} catch (PDOException $e) {
    jsonError('Database error: ' . $e->getMessage(), 500);
} catch (Exception $e) {
    jsonError($e->getMessage(), 400);
}

// ============================================================
// RUMAH IBADAH
// ============================================================
function handleRumahIbadah(string $method, string $action, ?int $id, array $body): void {
    $db = getDB();

    // GET LIST
    if ($method === 'GET' && $action === 'list') {
        $rows = $db->query(
            "SELECT id, nama, jenis, alamat, narahubung, latitude, longitude, radius_aktif,
                    created_at
             FROM rumah_ibadah
             ORDER BY nama ASC"
        )->fetchAll();
        jsonOk($rows);
    }

    // GET DETAIL + jumlah penerima dalam radius
    elseif ($method === 'GET' && $action === 'detail' && $id) {
        $stmt = $db->prepare(
            "SELECT id, nama, jenis, alamat, narahubung, latitude, longitude,
                    radius_aktif, created_at
             FROM rumah_ibadah WHERE id = ?"
        );
        $stmt->execute([$id]);
        $ri = $stmt->fetch();
        if (!$ri) jsonError('Data tidak ditemukan.', 404);

        // Hitung penerima dalam radius
        $stmtCount = $db->prepare(
            "SELECT COUNT(*) AS total
             FROM lokasi_bantuan lb, rumah_ibadah ri
             WHERE ri.id = ?
               AND ST_Distance_Sphere(lb.koordinat, ri.koordinat) <= ri.radius_aktif"
        );
        $stmtCount->execute([$id]);
        $ri['jumlah_dalam_radius'] = (int)$stmtCount->fetchColumn();

        jsonOk($ri);
    }

    // POST CREATE
    elseif ($method === 'POST' && $action === 'create') {
        $v = validasiRI($body);
        $stmt = $db->prepare(
            "INSERT INTO rumah_ibadah
                (nama, jenis, alamat, narahubung, latitude, longitude, koordinat, radius_aktif)
             VALUES
                (:nama, :jenis, :alamat, :narahubung, :lat, :lng,
                 ST_GeomFromText(:titik, 4326), :radius)"
        );
        $stmt->execute([
            ':nama'       => $v['nama'],
            ':jenis'      => $v['jenis'],
            ':alamat'     => $v['alamat'],
            ':narahubung' => $v['narahubung'],
            ':lat'        => $v['latitude'],
            ':lng'        => $v['longitude'],
            ':titik'      => "POINT({$v['latitude']} {$v['longitude']})",
            ':radius'     => $v['radius_aktif'],
        ]);
        $newId = (int)$db->lastInsertId();
        jsonOk(['message' => 'Rumah ibadah berhasil ditambahkan.', 'id' => $newId], 201);
    }

    // PUT UPDATE
    elseif ($method === 'PUT' && $action === 'update' && $id) {
        $v = validasiRI($body);
        $stmt = $db->prepare(
            "UPDATE rumah_ibadah SET
                nama = :nama, jenis = :jenis, alamat = :alamat,
                narahubung = :narahubung, latitude = :lat, longitude = :lng,
                koordinat = ST_GeomFromText(:titik, 4326), radius_aktif = :radius
             WHERE id = :id"
        );
        $stmt->execute([
            ':nama'       => $v['nama'],
            ':jenis'      => $v['jenis'],
            ':alamat'     => $v['alamat'],
            ':narahubung' => $v['narahubung'],
            ':lat'        => $v['latitude'],
            ':lng'        => $v['longitude'],
            ':titik'      => "POINT({$v['latitude']} {$v['longitude']})",
            ':radius'     => $v['radius_aktif'],
            ':id'         => $id,
        ]);
        jsonOk(['message' => 'Data rumah ibadah berhasil diperbarui.']);
    }

    // DELETE
    elseif ($method === 'DELETE' && $action === 'delete' && $id) {
        $stmt = $db->prepare("DELETE FROM rumah_ibadah WHERE id = ?");
        $stmt->execute([$id]);
        jsonOk(['message' => 'Rumah ibadah berhasil dihapus.']);
    }

    else {
        jsonError('Aksi tidak dikenali.', 400);
    }
}

function validasiRI(array $b): array {
    $nama   = trim($b['nama']   ?? '');
    $jenis  = trim($b['jenis']  ?? '');
    $alamat = trim($b['alamat'] ?? '');
    $lat    = isset($b['latitude'])  ? (float)$b['latitude']  : null;
    $lng    = isset($b['longitude']) ? (float)$b['longitude'] : null;
    $radius = isset($b['radius_aktif']) ? (int)$b['radius_aktif'] : 500;

    $jenisList = ['Masjid','Gereja','Pura','Vihara','Kelenteng'];

    if (!$nama)                          throw new Exception('Nama wajib diisi.');
    if (!in_array($jenis, $jenisList))   throw new Exception('Jenis tidak valid.');
    if (!$alamat)                        throw new Exception('Alamat wajib diisi.');
    if ($lat === null || $lat < -90  || $lat > 90)   throw new Exception('Latitude tidak valid.');
    if ($lng === null || $lng < -180 || $lng > 180)  throw new Exception('Longitude tidak valid.');
    if ($radius < 0 || $radius > 1500)   throw new Exception('Radius harus antara 0–1500 meter.');

    return [
        'nama'        => $nama,
        'jenis'       => $jenis,
        'alamat'      => $alamat,
        'narahubung'  => trim($b['narahubung'] ?? ''),
        'latitude'    => $lat,
        'longitude'   => $lng,
        'radius_aktif'=> $radius,
    ];
}

// ============================================================
// LOKASI BANTUAN
// ============================================================
function handleLokasiBantuan(string $method, string $action, ?int $id, array $body): void {
    $db     = getDB();
    $radius = isset($_GET['radius']) ? (int)$_GET['radius'] : 500;
    $radius = max(0, min(1500, $radius));

    // GET LIST + status warna
    if ($method === 'GET' && $action === 'list') {
        // Cek apakah ada rumah ibadah
        $adaRI = (int)$db->query("SELECT COUNT(*) FROM rumah_ibadah")->fetchColumn();

        if ($adaRI === 0) {
            // Kembalikan list tanpa status jarak
            $rows = $db->query(
                "SELECT id, nama_kk, jml_anggota, kategori, alamat, keterangan,
                        latitude, longitude, NULL AS jarak_meter,
                        NULL AS nama_ri, NULL AS id_ri, 'MERAH' AS status_warna
                 FROM lokasi_bantuan ORDER BY nama_kk ASC"
            )->fetchAll();
            jsonOk($rows);
            return;
        }

        // Hitung jarak ke rumah ibadah terdekat per titik
        $rows = $db->query(
            "SELECT lb.id, lb.nama_kk, lb.jml_anggota, lb.kategori,
                    lb.alamat, lb.keterangan, lb.latitude, lb.longitude
             FROM lokasi_bantuan lb ORDER BY lb.nama_kk ASC"
        )->fetchAll();

        $result = [];
        $stmtJarak = $db->prepare(
            "SELECT ri.id AS id_ri, ri.nama AS nama_ri, ri.radius_aktif,
                    ST_Distance_Sphere(lb.koordinat, ri.koordinat) AS jarak_meter
             FROM lokasi_bantuan lb
             CROSS JOIN rumah_ibadah ri
             WHERE lb.id = ?
             ORDER BY jarak_meter ASC
             LIMIT 1"
        );
        foreach ($rows as $lb) {
            $stmtJarak->execute([$lb['id']]);
            $terdekat = $stmtJarak->fetch();
            $jarak = $terdekat ? (float)$terdekat['jarak_meter'] : null;
            $lb['jarak_meter']  = $terdekat ? round($jarak, 1) : null;
            $lb['nama_ri']      = $terdekat['nama_ri']  ?? null;
            $lb['id_ri']        = $terdekat['id_ri']    ?? null;
            $lb['radius_ri']    = $terdekat['radius_aktif'] ?? null;
            // HIJAU = dalam jangkauan, MERAH = di luar jangkauan
            $lb['status_warna'] = ($terdekat && $jarak <= $radius) ? 'HIJAU' : 'MERAH';
            $result[] = $lb;
        }
        jsonOk($result);
    }

    // GET DETAIL
    elseif ($method === 'GET' && $action === 'detail' && $id) {
        $stmt = $db->prepare("SELECT * FROM lokasi_bantuan WHERE id = ?");
        $stmt->execute([$id]);
        $lb = $stmt->fetch();
        if (!$lb) jsonError('Data tidak ditemukan.', 404);
        jsonOk($lb);
    }

    // POST CREATE
    elseif ($method === 'POST' && $action === 'create') {
        $adaRI = (int)$db->query("SELECT COUNT(*) FROM rumah_ibadah")->fetchColumn();
        if ($adaRI === 0) {
            jsonError('Tambahkan minimal satu rumah ibadah terlebih dahulu.', 422);
        }

        $v = validasiLB($body);
        $stmt = $db->prepare(
            "INSERT INTO lokasi_bantuan
                (nama_kk, jml_anggota, kategori, alamat, keterangan, latitude, longitude, koordinat)
             VALUES
                (:nama_kk, :jml, :kategori, :alamat, :ket, :lat, :lng,
                 ST_GeomFromText(:titik, 4326))"
        );
        $stmt->execute([
            ':nama_kk'  => $v['nama_kk'],
            ':jml'      => $v['jml_anggota'],
            ':kategori' => $v['kategori'],
            ':alamat'   => $v['alamat'],
            ':ket'      => $v['keterangan'],
            ':lat'      => $v['latitude'],
            ':lng'      => $v['longitude'],
            ':titik'    => "POINT({$v['latitude']} {$v['longitude']})",
        ]);
        $newId = (int)$db->lastInsertId();
        jsonOk(['message' => 'Lokasi penerima bantuan berhasil ditambahkan.', 'id' => $newId], 201);
    }

    // PUT UPDATE
    elseif ($method === 'PUT' && $action === 'update' && $id) {
        $v = validasiLB($body);
        $stmt = $db->prepare(
            "UPDATE lokasi_bantuan SET
                nama_kk = :nama_kk, jml_anggota = :jml, kategori = :kategori,
                alamat = :alamat, keterangan = :ket,
                latitude = :lat, longitude = :lng,
                koordinat = ST_GeomFromText(:titik, 4326)
             WHERE id = :id"
        );
        $stmt->execute([
            ':nama_kk'  => $v['nama_kk'],
            ':jml'      => $v['jml_anggota'],
            ':kategori' => $v['kategori'],
            ':alamat'   => $v['alamat'],
            ':ket'      => $v['keterangan'],
            ':lat'      => $v['latitude'],
            ':lng'      => $v['longitude'],
            ':titik'    => "POINT({$v['latitude']} {$v['longitude']})",
            ':id'       => $id,
        ]);
        jsonOk(['message' => 'Data lokasi bantuan berhasil diperbarui.']);
    }

    // DELETE
    elseif ($method === 'DELETE' && $action === 'delete' && $id) {
        $stmt = $db->prepare("DELETE FROM lokasi_bantuan WHERE id = ?");
        $stmt->execute([$id]);
        jsonOk(['message' => 'Lokasi bantuan berhasil dihapus.']);
    }

    else {
        jsonError('Aksi tidak dikenali.', 400);
    }
}

function validasiLB(array $b): array {
    $nama_kk    = trim($b['nama_kk']    ?? '');
    $jml        = isset($b['jml_anggota']) ? (int)$b['jml_anggota'] : 1;
    $kategori   = trim($b['kategori']   ?? '');
    $alamat     = trim($b['alamat']     ?? '');
    $lat        = isset($b['latitude'])  ? (float)$b['latitude']  : null;
    $lng        = isset($b['longitude']) ? (float)$b['longitude'] : null;

    $katList = ['Kemiskinan','Bencana','Lansia','Lainnya'];

    if (!$nama_kk)                         throw new Exception('Nama kepala keluarga wajib diisi.');
    if ($jml < 1)                          throw new Exception('Jumlah anggota minimal 1.');
    if (!in_array($kategori, $katList))    throw new Exception('Kategori tidak valid.');
    if (!$alamat)                          throw new Exception('Alamat wajib diisi.');
    if ($lat === null || $lat < -90  || $lat > 90)   throw new Exception('Latitude tidak valid.');
    if ($lng === null || $lng < -180 || $lng > 180)  throw new Exception('Longitude tidak valid.');

    return [
        'nama_kk'     => $nama_kk,
        'jml_anggota' => $jml,
        'kategori'    => $kategori,
        'alamat'      => $alamat,
        'keterangan'  => trim($b['keterangan'] ?? ''),
        'latitude'    => $lat,
        'longitude'   => $lng,
    ];
}

// ============================================================
// SUMBANGAN (Stok donasi di rumah ibadah)
// ============================================================
function handleSumbangan(string $method, string $action, ?int $id, array $body): void {
    $db = getDB();
    $riId = isset($_GET['rumah_ibadah_id']) ? (int)$_GET['rumah_ibadah_id'] : null;

    // GET LIST by rumah_ibadah_id
    if ($method === 'GET' && $action === 'list') {
        if (!$riId) jsonError('Parameter rumah_ibadah_id diperlukan.', 400);
        $stmt = $db->prepare(
            "SELECT s.id, s.rumah_ibadah_id, s.jenis, s.jumlah, s.satuan, s.keterangan,
                    s.created_at,
                    COALESCE(SUM(p.jumlah_disalurkan), 0) AS total_disalurkan,
                    (s.jumlah - COALESCE(SUM(p.jumlah_disalurkan), 0)) AS sisa
             FROM sumbangan s
             LEFT JOIN penyaluran_bantuan p ON p.sumbangan_id = s.id
             WHERE s.rumah_ibadah_id = ?
             GROUP BY s.id
             ORDER BY s.created_at DESC"
        );
        $stmt->execute([$riId]);
        jsonOk($stmt->fetchAll());
    }

    // POST CREATE
    elseif ($method === 'POST' && $action === 'create') {
        $v = validasiSumbangan($body);
        // Cek rumah ibadah ada
        $cek = $db->prepare("SELECT id FROM rumah_ibadah WHERE id = ?");
        $cek->execute([$v['rumah_ibadah_id']]);
        if (!$cek->fetch()) jsonError('Rumah ibadah tidak ditemukan.', 404);

        $stmt = $db->prepare(
            "INSERT INTO sumbangan (rumah_ibadah_id, jenis, jumlah, satuan, keterangan)
             VALUES (:ri_id, :jenis, :jumlah, :satuan, :ket)"
        );
        $stmt->execute([
            ':ri_id'  => $v['rumah_ibadah_id'],
            ':jenis'  => $v['jenis'],
            ':jumlah' => $v['jumlah'],
            ':satuan' => $v['satuan'],
            ':ket'    => $v['keterangan'],
        ]);
        jsonOk(['message' => 'Sumbangan berhasil ditambahkan.', 'id' => (int)$db->lastInsertId()], 201);
    }

    // PUT UPDATE
    elseif ($method === 'PUT' && $action === 'update' && $id) {
        $v = validasiSumbangan($body);
        $stmt = $db->prepare(
            "UPDATE sumbangan SET jenis=:jenis, jumlah=:jumlah, satuan=:satuan, keterangan=:ket
             WHERE id=:id"
        );
        $stmt->execute([
            ':jenis'  => $v['jenis'],
            ':jumlah' => $v['jumlah'],
            ':satuan' => $v['satuan'],
            ':ket'    => $v['keterangan'],
            ':id'     => $id,
        ]);
        jsonOk(['message' => 'Sumbangan berhasil diperbarui.']);
    }

    // DELETE
    elseif ($method === 'DELETE' && $action === 'delete' && $id) {
        // Cek apakah sudah ada penyaluran
        $cek = $db->prepare("SELECT COUNT(*) FROM penyaluran_bantuan WHERE sumbangan_id = ?");
        $cek->execute([$id]);
        if ((int)$cek->fetchColumn() > 0) {
            jsonError('Sumbangan ini sudah memiliki riwayat penyaluran dan tidak dapat dihapus.', 422);
        }
        $db->prepare("DELETE FROM sumbangan WHERE id = ?")->execute([$id]);
        jsonOk(['message' => 'Sumbangan berhasil dihapus.']);
    }

    else {
        jsonError('Aksi tidak dikenali.', 400);
    }
}

function validasiSumbangan(array $b): array {
    $riId   = isset($b['rumah_ibadah_id']) ? (int)$b['rumah_ibadah_id'] : 0;
    $jenis  = trim($b['jenis']  ?? '');
    $jumlah = isset($b['jumlah']) ? (float)$b['jumlah'] : null;
    $satuan = trim($b['satuan'] ?? '');

    if (!$riId)                  throw new Exception('rumah_ibadah_id wajib diisi.');
    if (!$jenis)                 throw new Exception('Jenis sumbangan wajib diisi.');
    if ($jumlah === null || $jumlah <= 0) throw new Exception('Jumlah harus lebih dari 0.');
    if (!$satuan)                throw new Exception('Satuan wajib diisi.');

    return [
        'rumah_ibadah_id' => $riId,
        'jenis'           => $jenis,
        'jumlah'          => $jumlah,
        'satuan'          => $satuan,
        'keterangan'      => trim($b['keterangan'] ?? ''),
    ];
}

// ============================================================
// PENYALURAN BANTUAN
// ============================================================
function handlePenyaluran(string $method, string $action, ?int $id, array $body): void {
    $db = getDB();
    $lbId = isset($_GET['lokasi_bantuan_id']) ? (int)$_GET['lokasi_bantuan_id'] : null;
    $riId = isset($_GET['rumah_ibadah_id'])   ? (int)$_GET['rumah_ibadah_id']   : null;

    // GET LIST riwayat by lokasi_bantuan atau rumah_ibadah
    if ($method === 'GET' && $action === 'list') {
        $where = '';
        $params = [];
        if ($lbId) { $where = 'WHERE p.lokasi_bantuan_id = ?'; $params[] = $lbId; }
        elseif ($riId) { $where = 'WHERE s.rumah_ibadah_id = ?'; $params[] = $riId; }

        $stmt = $db->prepare(
            "SELECT p.id, p.sumbangan_id, p.lokasi_bantuan_id, p.jumlah_disalurkan,
                    p.tanggal, p.keterangan, p.created_at,
                    s.jenis AS jenis_sumbangan, s.satuan,
                    lb.nama_kk, ri.nama AS nama_ri
             FROM penyaluran_bantuan p
             JOIN sumbangan s ON s.id = p.sumbangan_id
             JOIN rumah_ibadah ri ON ri.id = s.rumah_ibadah_id
             JOIN lokasi_bantuan lb ON lb.id = p.lokasi_bantuan_id
             $where
             ORDER BY p.created_at DESC"
        );
        $stmt->execute($params);
        jsonOk($stmt->fetchAll());
    }

    // GET sumbangan tersedia untuk lokasi bantuan tertentu (semua rumah ibadah dalam jangkauan)
    elseif ($method === 'GET' && $action === 'tersedia' && $lbId) {
        $stmtRI = $db->prepare(
            "SELECT ri.id, ri.nama, ri.radius_aktif,
                    ST_Distance_Sphere(lb.koordinat, ri.koordinat) AS jarak
             FROM lokasi_bantuan lb
             CROSS JOIN rumah_ibadah ri
             WHERE lb.id = ?
             ORDER BY jarak ASC"
        );
        $stmtRI->execute([$lbId]);
        $riList = $stmtRI->fetchAll();

        if (!$riList) jsonError('Tidak ada rumah ibadah di sistem.', 404);

        $radiusGlobal = isset($_GET['radius']) ? (int)$_GET['radius'] : 500;
        $tersedia = [];

        $stmtS = $db->prepare(
            "SELECT s.id, s.jenis, s.satuan, s.keterangan,
                    s.jumlah,
                    COALESCE(SUM(p.jumlah_disalurkan), 0) AS total_disalurkan,
                    (s.jumlah - COALESCE(SUM(p.jumlah_disalurkan), 0)) AS sisa
             FROM sumbangan s
             LEFT JOIN penyaluran_bantuan p ON p.sumbangan_id = s.id
             WHERE s.rumah_ibadah_id = ?
             GROUP BY s.id
             HAVING sisa > 0
             ORDER BY s.jenis ASC"
        );

        foreach ($riList as $ri) {
            $batasRadius = isset($_GET['radius']) ? $radiusGlobal : (int)$ri['radius_aktif'];
            if ((float)$ri['jarak'] <= $batasRadius) {
                // Ambil stok
                $stmtS->execute([$ri['id']]);
                $sumbangan = $stmtS->fetchAll();
                
                $tersedia[] = [
                    'rumah_ibadah' => ['id' => $ri['id'], 'nama' => $ri['nama']],
                    'jarak_meter'  => round((float)$ri['jarak'], 1),
                    'sumbangan'    => $sumbangan
                ];
            }
        }

        if (empty($tersedia)) {
            jsonError('Lokasi bantuan ini berada di luar jangkauan rumah ibadah manapun.', 422);
        }

        jsonOk($tersedia);
    }

    // POST CREATE penyaluran baru
    elseif ($method === 'POST' && $action === 'create') {
        $sumbanganId = isset($body['sumbangan_id'])        ? (int)$body['sumbangan_id']        : 0;
        $lbIdPost    = isset($body['lokasi_bantuan_id'])   ? (int)$body['lokasi_bantuan_id']   : 0;
        $jumlah      = isset($body['jumlah_disalurkan'])   ? (float)$body['jumlah_disalurkan'] : 0;
        $tanggal     = trim($body['tanggal']  ?? date('Y-m-d'));
        $ket         = trim($body['keterangan'] ?? '');

        if (!$sumbanganId) throw new Exception('sumbangan_id wajib diisi.');
        if (!$lbIdPost)    throw new Exception('lokasi_bantuan_id wajib diisi.');
        if ($jumlah <= 0)  throw new Exception('Jumlah disalurkan harus lebih dari 0.');

        // Cek stok mencukupi
        $stmtSisa = $db->prepare(
            "SELECT s.jumlah, s.jenis, s.satuan,
                    (s.jumlah - COALESCE(SUM(p.jumlah_disalurkan), 0)) AS sisa
             FROM sumbangan s
             LEFT JOIN penyaluran_bantuan p ON p.sumbangan_id = s.id
             WHERE s.id = ?
             GROUP BY s.id"
        );
        $stmtSisa->execute([$sumbanganId]);
        $stok = $stmtSisa->fetch();
        if (!$stok) throw new Exception('Sumbangan tidak ditemukan.');
        if ((float)$stok['sisa'] < $jumlah) {
            throw new Exception("Stok {$stok['jenis']} tidak mencukupi. Sisa: {$stok['sisa']} {$stok['satuan']}.");
        }

        $stmt = $db->prepare(
            "INSERT INTO penyaluran_bantuan
                (sumbangan_id, lokasi_bantuan_id, jumlah_disalurkan, tanggal, keterangan)
             VALUES (?, ?, ?, ?, ?)"
        );
        $stmt->execute([$sumbanganId, $lbIdPost, $jumlah, $tanggal, $ket]);
        jsonOk(['message' => 'Penyaluran bantuan berhasil dicatat.', 'id' => (int)$db->lastInsertId()], 201);
    }

    else {
        jsonError('Aksi tidak dikenali.', 400);
    }
}

// ============================================================
// SPBU
// ============================================================
function handleSpbu(string $method, string $action, ?int $id, array $body): void {
    $db = getDB();

    // GET LIST
    if ($method === 'GET' && $action === 'list') {
        $rows = $db->query(
            "SELECT id, nama, buka_24_jam, kontak, latitude, longitude, created_at
             FROM spbu
             ORDER BY nama ASC"
        )->fetchAll();
        jsonOk($rows);
    }

    // POST CREATE
    elseif ($method === 'POST' && $action === 'create') {
        $v = validasiSpbu($body);
        $stmt = $db->prepare(
            "INSERT INTO spbu (nama, buka_24_jam, kontak, latitude, longitude, koordinat)
             VALUES (:nama, :buka_24_jam, :kontak, :lat, :lng, ST_GeomFromText(:titik, 4326))"
        );
        $stmt->execute([
            ':nama'        => $v['nama'],
            ':buka_24_jam' => $v['buka_24_jam'],
            ':kontak'      => $v['kontak'],
            ':lat'         => $v['latitude'],
            ':lng'         => $v['longitude'],
            ':titik'       => "POINT({$v['latitude']} {$v['longitude']})"
        ]);
        jsonOk(['message' => 'SPBU berhasil ditambahkan.', 'id' => (int)$db->lastInsertId()], 201);
    }

    // PUT UPDATE (Bisa update koordinat saja saat drag-and-drop, atau full data)
    elseif ($method === 'PUT' && $action === 'update' && $id) {
        // Cek jika update koordinat saja (drag and drop)
        if (isset($body['latitude']) && isset($body['longitude']) && !isset($body['nama'])) {
            $lat = (float)$body['latitude'];
            $lng = (float)$body['longitude'];
            $stmt = $db->prepare(
                "UPDATE spbu SET latitude = :lat, longitude = :lng, koordinat = ST_GeomFromText(:titik, 4326) WHERE id = :id"
            );
            $stmt->execute([
                ':lat'   => $lat,
                ':lng'   => $lng,
                ':titik' => "POINT({$lat} {$lng})",
                ':id'    => $id
            ]);
            jsonOk(['message' => 'Posisi SPBU berhasil diperbarui.']);
            return;
        }

        // Full update
        $v = validasiSpbu($body);
        $stmt = $db->prepare(
            "UPDATE spbu SET nama = :nama, buka_24_jam = :buka_24_jam, kontak = :kontak,
             latitude = :lat, longitude = :lng, koordinat = ST_GeomFromText(:titik, 4326)
             WHERE id = :id"
        );
        $stmt->execute([
            ':nama'        => $v['nama'],
            ':buka_24_jam' => $v['buka_24_jam'],
            ':kontak'      => $v['kontak'],
            ':lat'         => $v['latitude'],
            ':lng'         => $v['longitude'],
            ':titik'       => "POINT({$v['latitude']} {$v['longitude']})",
            ':id'          => $id
        ]);
        jsonOk(['message' => 'Data SPBU berhasil diperbarui.']);
    }

    // DELETE
    elseif ($method === 'DELETE' && $action === 'delete' && $id) {
        $db->prepare("DELETE FROM spbu WHERE id = ?")->execute([$id]);
        jsonOk(['message' => 'SPBU berhasil dihapus.']);
    }

    else {
        jsonError('Aksi tidak dikenali.', 400);
    }
}

function validasiSpbu(array $b): array {
    $nama = trim($b['nama'] ?? '');
    $lat  = isset($b['latitude'])  ? (float)$b['latitude']  : null;
    $lng  = isset($b['longitude']) ? (float)$b['longitude'] : null;

    if (!$nama) throw new Exception('Nama SPBU wajib diisi.');
    if ($lat === null || $lat < -90  || $lat > 90)   throw new Exception('Latitude tidak valid.');
    if ($lng === null || $lng < -180 || $lng > 180)  throw new Exception('Longitude tidak valid.');

    return [
        'nama'        => $nama,
        'buka_24_jam' => isset($b['buka_24_jam']) ? (int)$b['buka_24_jam'] : 0,
        'kontak'      => trim($b['kontak'] ?? ''),
        'latitude'    => $lat,
        'longitude'   => $lng,
    ];
}

// ============================================================
// SETUP — Buat tabel otomatis jika belum ada
// ============================================================
function handleSetup(): void {
    $db = getDB();

    $db->exec("
        CREATE TABLE IF NOT EXISTS rumah_ibadah (
            id           INT AUTO_INCREMENT PRIMARY KEY,
            nama         VARCHAR(255)  NOT NULL,
            jenis        ENUM('Masjid','Gereja','Pura','Vihara','Kelenteng') NOT NULL,
            alamat       TEXT          NOT NULL,
            narahubung   VARCHAR(255)  DEFAULT '',
            latitude     DECIMAL(10,7) NOT NULL,
            longitude    DECIMAL(10,7) NOT NULL,
            koordinat    POINT         NOT NULL SRID 4326,
            radius_aktif INT           NOT NULL DEFAULT 500,
            created_at   TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
            SPATIAL INDEX (koordinat)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS lokasi_bantuan (
            id           INT AUTO_INCREMENT PRIMARY KEY,
            nama_kk      VARCHAR(255)  NOT NULL,
            jml_anggota  INT           NOT NULL DEFAULT 1,
            kategori     ENUM('Kemiskinan','Bencana','Lansia','Lainnya') NOT NULL,
            alamat       TEXT          NOT NULL,
            keterangan   TEXT,
            latitude     DECIMAL(10,7) NOT NULL,
            longitude    DECIMAL(10,7) NOT NULL,
            koordinat    POINT         NOT NULL SRID 4326,
            created_at   TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
            SPATIAL INDEX (koordinat)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS sumbangan (
            id               INT AUTO_INCREMENT PRIMARY KEY,
            rumah_ibadah_id  INT            NOT NULL,
            jenis            VARCHAR(100)   NOT NULL,
            jumlah           DECIMAL(12,2)  NOT NULL,
            satuan           VARCHAR(30)    NOT NULL,
            keterangan       TEXT,
            created_at       TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (rumah_ibadah_id) REFERENCES rumah_ibadah(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS penyaluran_bantuan (
            id                  INT AUTO_INCREMENT PRIMARY KEY,
            sumbangan_id        INT           NOT NULL,
            lokasi_bantuan_id   INT           NOT NULL,
            jumlah_disalurkan   DECIMAL(12,2) NOT NULL,
            tanggal             DATE          NOT NULL,
            keterangan          TEXT,
            created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (sumbangan_id)      REFERENCES sumbangan(id)      ON DELETE RESTRICT,
            FOREIGN KEY (lokasi_bantuan_id) REFERENCES lokasi_bantuan(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS spbu (
            id           INT AUTO_INCREMENT PRIMARY KEY,
            nama         VARCHAR(255)  NOT NULL,
            buka_24_jam  TINYINT(1)    NOT NULL DEFAULT 0,
            kontak       VARCHAR(100)  DEFAULT '',
            latitude     DECIMAL(10,7) NOT NULL,
            longitude    DECIMAL(10,7) NOT NULL,
            koordinat    POINT         NOT NULL SRID 4326,
            created_at   TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
            SPATIAL INDEX (koordinat)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS jalan (
            id         INT AUTO_INCREMENT PRIMARY KEY,
            nama       VARCHAR(255)  NOT NULL,
            status     VARCHAR(100)  NOT NULL,
            panjang    DECIMAL(12,2) NOT NULL DEFAULT 0,
            geom       LINESTRING    NOT NULL SRID 4326,
            created_at TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
            SPATIAL INDEX (geom)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS parsil (
            id           INT AUTO_INCREMENT PRIMARY KEY,
            nama_pemilik VARCHAR(255)  NOT NULL,
            status       VARCHAR(100)  NOT NULL,
            luas         DECIMAL(12,2) NOT NULL DEFAULT 0,
            geom         POLYGON       NOT NULL SRID 4326,
            created_at   TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
            SPATIAL INDEX (geom)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");

    jsonOk(['message' => 'Tabel berhasil dibuat / sudah ada.']);
}

// ============================================================
// HELPER
// ============================================================
function jsonOk(mixed $data, int $code = 200): void {
    http_response_code($code);
    echo json_encode($data, JSON_UNESCAPED_UNICODE);
    exit;
}

function jsonError(string $msg, int $code = 400): void {
    http_response_code($code);
    echo json_encode(['error' => $msg], JSON_UNESCAPED_UNICODE);
    exit;
}

// ============================================================
// JALAN & PARSIL
// ============================================================
function handleJalan(string $method, string $action, ?int $id, array $body): void {
    $db = getDB();

    if ($method === 'GET' && $action === 'list') {
        $rows = $db->query(
            "SELECT id, nama, status, panjang,
                    ST_AsGeoJSON(geom) AS geom_json,
                    created_at
             FROM jalan"
        )->fetchAll();
        foreach ($rows as &$r) {
            $r['geom'] = json_decode($r['geom_json'], true);
            unset($r['geom_json']);
        }
        jsonOk($rows);
    }
    elseif ($method === 'POST' && $action === 'create') {
        if (empty($body['nama']) || empty($body['status']) || empty($body['geom'])) {
            jsonError('Data jalan tidak lengkap.');
        }
        $geomJson = json_encode($body['geom']);
        $stmt = $db->prepare(
            "INSERT INTO jalan (nama, status, panjang, geom)
             VALUES (:nama, :status, :panjang, ST_GeomFromGeoJSON(:geom, 2, 4326))"
        );
        $stmt->execute([
            ':nama'    => $body['nama'],
            ':status'  => $body['status'],
            ':panjang' => $body['panjang'] ?? 0,
            ':geom'    => $geomJson
        ]);
        jsonOk(['message' => 'Data jalan berhasil disimpan.', 'id' => $db->lastInsertId()]);
    }
    elseif ($method === 'PUT' && $action === 'update' && $id) {
        $geomJson = isset($body['geom']) ? json_encode($body['geom']) : null;
        if ($geomJson) {
            $stmt = $db->prepare(
                "UPDATE jalan SET
                    nama = :nama, status = :status, panjang = :panjang,
                    geom = ST_GeomFromGeoJSON(:geom, 2, 4326)
                 WHERE id = :id"
            );
            $stmt->execute([
                ':nama'    => $body['nama'],
                ':status'  => $body['status'],
                ':panjang' => $body['panjang'],
                ':geom'    => $geomJson,
                ':id'      => $id
            ]);
        } else {
            $stmt = $db->prepare("UPDATE jalan SET nama = :nama, status = :status WHERE id = :id");
            $stmt->execute([
                ':nama'    => $body['nama'],
                ':status'  => $body['status'],
                ':id'      => $id
            ]);
        }
        jsonOk(['message' => 'Data jalan berhasil diperbarui.']);
    }
    elseif ($method === 'DELETE' && $action === 'delete' && $id) {
        $stmt = $db->prepare("DELETE FROM jalan WHERE id = ?");
        $stmt->execute([$id]);
        jsonOk(['message' => 'Data jalan dihapus.']);
    }
}

function handleParsil(string $method, string $action, ?int $id, array $body): void {
    $db = getDB();

    if ($method === 'GET' && $action === 'list') {
        $rows = $db->query(
            "SELECT id, nama_pemilik, status, luas,
                    ST_AsGeoJSON(geom) AS geom_json,
                    created_at
             FROM parsil"
        )->fetchAll();
        foreach ($rows as &$r) {
            $r['geom'] = json_decode($r['geom_json'], true);
            unset($r['geom_json']);
        }
        jsonOk($rows);
    }
    elseif ($method === 'POST' && $action === 'create') {
        if (empty($body['nama_pemilik']) || empty($body['status']) || empty($body['geom'])) {
            jsonError('Data parsil tidak lengkap.');
        }
        $geomJson = json_encode($body['geom']);
        $stmt = $db->prepare(
            "INSERT INTO parsil (nama_pemilik, status, luas, geom)
             VALUES (:nama_pemilik, :status, :luas, ST_GeomFromGeoJSON(:geom, 2, 4326))"
        );
        $stmt->execute([
            ':nama_pemilik' => $body['nama_pemilik'],
            ':status'       => $body['status'],
            ':luas'         => $body['luas'] ?? 0,
            ':geom'         => $geomJson
        ]);
        jsonOk(['message' => 'Data parsil berhasil disimpan.', 'id' => $db->lastInsertId()]);
    }
    elseif ($method === 'PUT' && $action === 'update' && $id) {
        $geomJson = isset($body['geom']) ? json_encode($body['geom']) : null;
        if ($geomJson) {
            $stmt = $db->prepare(
                "UPDATE parsil SET
                    nama_pemilik = :nama_pemilik, status = :status, luas = :luas,
                    geom = ST_GeomFromGeoJSON(:geom, 2, 4326)
                 WHERE id = :id"
            );
            $stmt->execute([
                ':nama_pemilik' => $body['nama_pemilik'],
                ':status'       => $body['status'],
                ':luas'         => $body['luas'],
                ':geom'         => $geomJson,
                ':id'      => $id
            ]);
        } else {
            $stmt = $db->prepare("UPDATE parsil SET nama_pemilik = :nama_pemilik, status = :status WHERE id = :id");
            $stmt->execute([
                ':nama_pemilik' => $body['nama_pemilik'],
                ':status'       => $body['status'],
                ':id'      => $id
            ]);
        }
        jsonOk(['message' => 'Data parsil berhasil diperbarui.']);
    }
    elseif ($method === 'DELETE' && $action === 'delete' && $id) {
        $stmt = $db->prepare("DELETE FROM parsil WHERE id = ?");
        $stmt->execute([$id]);
        jsonOk(['message' => 'Data parsil dihapus.']);
    }
}