<?php
// api/dashboard_stats.php v2.0
// ============================================================
// API Statistik untuk Dashboard & Grafik
// ============================================================
require_once __DIR__ . '/../includes/config.php';

header('Content-Type: application/json; charset=utf-8');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type');
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') exit;
if ($_SERVER['REQUEST_METHOD'] !== 'GET') jsonResponse(['success'=>false,'error'=>'Method tidak diizinkan'],405);

$db = getDB();

// ── Filter opsional ────────────────────────────────────────
$kelurahan = $_GET['kelurahan'] ?? null;
$kecamatan = $_GET['kecamatan'] ?? null;
$whereBase = "WHERE 1=1";
$params    = [];
if ($kelurahan) { $whereBase .= " AND w.kelurahan = ?"; $params[] = $kelurahan; }
if ($kecamatan) { $whereBase .= " AND w.kecamatan = ?"; $params[] = $kecamatan; }

$joinW = "LEFT JOIN wilayah w ON w.id = pm.wilayah_id";

// ═══════════════════════════════════════════════════════════
// 1. RINGKASAN UTAMA (KPI Cards)
// ═══════════════════════════════════════════════════════════
$kpi = $db->prepare("
    SELECT
        COUNT(pm.id)                                             AS total_kk,
        SUM(pm.jumlah_anggota)                                   AS total_jiwa,
        SUM(pm.jumlah_anak_sekolah)                              AS total_anak_sekolah,
        SUM(CASE WHEN pm.status_verifikasi='terverifikasi' THEN 1 ELSE 0 END) AS terverifikasi,
        SUM(CASE WHEN pm.status_verifikasi='belum_diverifikasi' THEN 1 ELSE 0 END) AS belum_verifikasi,
        SUM(CASE WHEN pm.punya_bpjs=1 THEN 1 ELSE 0 END)        AS punya_bpjs,
        SUM(CASE WHEN pm.kategori_kemiskinan='sangat_miskin' THEN 1 ELSE 0 END) AS sangat_miskin,
        SUM(CASE WHEN pm.kategori_kemiskinan='miskin'        THEN 1 ELSE 0 END) AS miskin,
        SUM(CASE WHEN pm.kategori_kemiskinan='hampir_miskin' THEN 1 ELSE 0 END) AS hampir_miskin,
        SUM(CASE WHEN pm.kategori_kemiskinan='mampu'         THEN 1 ELSE 0 END) AS mampu,
        ROUND(AVG(pm.penghasilan_bulanan),0)                     AS avg_penghasilan,
        SUM(CASE WHEN bp_count.cnt > 0 THEN 1 ELSE 0 END)       AS penerima_bantuan,
        SUM(CASE WHEN bp_count.cnt = 0 THEN 1 ELSE 0 END)       AS belum_bantuan
    FROM penduduk_miskin pm
    $joinW
    LEFT JOIN (
        SELECT penduduk_id, COUNT(*) AS cnt
        FROM bantuan_penduduk
        WHERE status='aktif'
        GROUP BY penduduk_id
    ) bp_count ON bp_count.penduduk_id = pm.id
    $whereBase
");
$kpi->execute($params);
$kpiData = $kpi->fetch();

// ═══════════════════════════════════════════════════════════
// 2. DISTRIBUSI KATEGORI KEMISKINAN (Donut Chart)
// ═══════════════════════════════════════════════════════════
$katStmt = $db->prepare("
    SELECT pm.kategori_kemiskinan AS label, COUNT(*) AS value
    FROM penduduk_miskin pm $joinW $whereBase
    GROUP BY pm.kategori_kemiskinan
    ORDER BY FIELD(pm.kategori_kemiskinan,'sangat_miskin','miskin','hampir_miskin','mampu')
");
$katStmt->execute($params);
$kategoriChart = $katStmt->fetchAll();

// ═══════════════════════════════════════════════════════════
// 3. KK PER KELURAHAN (Bar Chart)
// ═══════════════════════════════════════════════════════════
$kelStmt = $db->prepare("
    SELECT
        COALESCE(w.kelurahan,'Tidak Diketahui') AS label,
        COUNT(pm.id)                             AS total_kk,
        SUM(pm.jumlah_anggota)                   AS total_jiwa,
        SUM(CASE WHEN bp_c.cnt>0 THEN 1 ELSE 0 END) AS penerima
    FROM penduduk_miskin pm $joinW
    LEFT JOIN (
        SELECT penduduk_id, COUNT(*) AS cnt FROM bantuan_penduduk WHERE status='aktif' GROUP BY penduduk_id
    ) bp_c ON bp_c.penduduk_id = pm.id
    $whereBase
    GROUP BY w.kelurahan
    ORDER BY total_kk DESC
    LIMIT 15
");
$kelStmt->execute($params);
$kelurahanChart = $kelStmt->fetchAll();

// ═══════════════════════════════════════════════════════════
// 4. DISTRIBUSI JENIS BANTUAN (Pie Chart)
// ═══════════════════════════════════════════════════════════
$bantuanStmt = $db->prepare("
    SELECT jb.kode, jb.nama AS label, jb.warna_badge AS color, COUNT(bp.id) AS value
    FROM bantuan_penduduk bp
    JOIN jenis_bantuan jb ON jb.id = bp.bantuan_id
    JOIN penduduk_miskin pm ON pm.id = bp.penduduk_id
    $joinW
    " . str_replace('WHERE 1=1', 'WHERE bp.status=\'aktif\'', $whereBase) . "
    GROUP BY jb.id
    ORDER BY value DESC
");
$bantuanStmt->execute($params);
$bantuanChart = $bantuanStmt->fetchAll();

// ═══════════════════════════════════════════════════════════
// 5. TREN PENDATAAN PER BULAN (Line Chart) – 12 bulan terakhir
// ═══════════════════════════════════════════════════════════
$trenStmt = $db->prepare("
    SELECT
        DATE_FORMAT(pm.created_at,'%Y-%m') AS bulan,
        COUNT(pm.id)                        AS total
    FROM penduduk_miskin pm $joinW $whereBase
    AND pm.created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
    GROUP BY DATE_FORMAT(pm.created_at,'%Y-%m')
    ORDER BY bulan ASC
");
$trenStmt->execute($params);
$trenChart = $trenStmt->fetchAll();

// ═══════════════════════════════════════════════════════════
// 6. DISTRIBUSI PEKERJAAN (Bar horizontal)
// ═══════════════════════════════════════════════════════════
$pekerjaanStmt = $db->prepare("
    SELECT pm.status_pekerjaan AS label, COUNT(*) AS value
    FROM penduduk_miskin pm $joinW $whereBase
    GROUP BY pm.status_pekerjaan
    ORDER BY value DESC
");
$pekerjaanStmt->execute($params);
$pekerjaanChart = $pekerjaanStmt->fetchAll();

// ═══════════════════════════════════════════════════════════
// 7. DATA HEATMAP (lat/lng + bobot untuk Leaflet.heat)
// ═══════════════════════════════════════════════════════════
$heatStmt = $db->prepare("
    SELECT pm.lat, pm.lng,
        CASE pm.kategori_kemiskinan
            WHEN 'sangat_miskin'  THEN 1.0
            WHEN 'miskin'         THEN 0.6
            WHEN 'hampir_miskin'  THEN 0.3
            WHEN 'mampu'          THEN 0.1
            ELSE 0.1
        END AS intensity
    FROM penduduk_miskin pm $joinW $whereBase
");
$heatStmt->execute($params);
$heatmap = $heatStmt->fetchAll();

// ═══════════════════════════════════════════════════════════
// 8. TOP 10 KK BELUM MENERIMA BANTUAN APAPUN
// ═══════════════════════════════════════════════════════════
$belumBantuanStmt = $db->prepare("
    SELECT pm.id, pm.nama_kk, pm.nik_kk, pm.alamat,
           pm.kategori_kemiskinan, pm.jumlah_anggota,
           pm.status_verifikasi,
           COALESCE(w.kelurahan,'–') AS kelurahan,
           DATEDIFF(NOW(), pm.created_at) AS hari_terdata
    FROM penduduk_miskin pm $joinW
    $whereBase
    AND NOT EXISTS (
        SELECT 1 FROM bantuan_penduduk bp
        WHERE bp.penduduk_id = pm.id AND bp.status='aktif'
    )
    ORDER BY pm.kategori_kemiskinan ASC, hari_terdata DESC
    LIMIT 10
");
$belumBantuanStmt->execute($params);
$belumBantuan = $belumBantuanStmt->fetchAll();

// ── Statistik Rumah Ibadah (tetap dari v1.1) ──────────────
$ibadahStats = $db->query("
    SELECT COUNT(*) AS total_ibadah FROM rumah_ibadah
")->fetch();

jsonResponse([
    'success'         => true,
    'kpi'             => $kpiData,
    'chart_kategori'  => $kategoriChart,
    'chart_kelurahan' => $kelurahanChart,
    'chart_bantuan'   => $bantuanChart,
    'chart_tren'      => $trenChart,
    'chart_pekerjaan' => $pekerjaanChart,
    'heatmap'         => $heatmap,
    'belum_bantuan'   => $belumBantuan,
    'total_ibadah'    => (int)$ibadahStats['total_ibadah'],
    'generated_at'    => date('Y-m-d H:i:s'),
]);