# -*- coding: utf-8 -*-
"""
app.py — Dashboard Kekurangan Sampel IPLM (TANPA HITUNG INDEKS)
FIX FULL:
- Target 68% diambil dari META:
  * Kab/Kota: kolom sampel_total
  * Provinsi: kolom total _sampel (atau variasinya)
- Normalisasi label diperkuat:
  * kab/kota: hapus kata "DAN", seragamkan KAB/KOTA, buang simbol
  * provinsi: buang prefix "PROVINSI/PROPINSI", buang simbol
- Jika META tidak match:
  * ditandai META_MATCH="TIDAK" + Target NaN (bukan 0), supaya tidak menyesatkan
"""

import os
import re
import tempfile
from pathlib import Path

import gradio as gr
import numpy as np
import pandas as pd
import plotly.graph_objects as go
from huggingface_hub import InferenceClient

from docx import Document

import plotly.express as px
try:
    import kaleido  # noqa: F401
    HAS_KALEIDO = True
except Exception:
    HAS_KALEIDO = False


# ============================================================
# 1) KONFIGURASI FILE
# ============================================================
DATA_FILE       = "IPLM_clean_manual_131225.xlsx"
META_KAB_FILE   = "Data_populasi_Kab_kota.xlsx"
META_PROV_FILE  = "Data_populasi_propinsi.xlsx"

TARGET_COVERAGE = 0.68

# ============================================================
# 1b) LLM
# ============================================================
USE_LLM = True
LLM_MODEL_NAME = "meta-llama/Meta-Llama-3-8B-Instruct"
HF_TOKEN = (
    os.getenv("HF_SECRET")
    or os.getenv("HUGGINGFACEHUB_API_TOKEN")
    or os.getenv("HF_API_TOKEN")
)

_HF_CLIENT = None
def get_llm_client():
    global _HF_CLIENT
    if _HF_CLIENT is not None:
        return _HF_CLIENT
    try:
        if HF_TOKEN:
            _HF_CLIENT = InferenceClient(model=LLM_MODEL_NAME, token=HF_TOKEN)
        else:
            _HF_CLIENT = InferenceClient(model=LLM_MODEL_NAME)
        return _HF_CLIENT
    except Exception:
        _HF_CLIENT = None
        return None


# ============================================================
# 2) UTIL
# ============================================================
def _canon(s: str) -> str:
    return re.sub(r"[^a-z0-9]+", "", str(s).lower())

def pick_col(df, candidates):
    for c in candidates:
        if c in df.columns:
            return c
    can_map = {_canon(c): c for c in df.columns}
    for c in candidates:
        k = _canon(c)
        if k in can_map:
            return can_map[k]
    return None

def coerce_num(val):
    if pd.isna(val):
        return np.nan
    t = str(val).strip()
    if t == "" or t in {"-", "–", "—"}:
        return np.nan
    t = t.replace("\u00a0", " ").replace("Rp", "").replace("%", "")
    t = re.sub(r"[^0-9,.\-]", "", t)
    if t.count(".") > 1 and t.count(",") == 1:
        t = t.replace(".", "").replace(",", ".")
    elif t.count(",") > 1 and t.count(".") == 1:
        t = t.replace(",", "")
    elif t.count(",") == 1 and t.count(".") == 0:
        t = t.replace(",", ".")
    else:
        t = t.replace(",", "")
    try:
        return float(t)
    except Exception:
        return np.nan

def norm_kew(v):
    if pd.isna(v):
        return None
    t = str(v).strip().upper()
    if "KAB" in t or "KOTA" in t:
        return "KAB/KOTA"
    if "PROV" in t:
        return "PROVINSI"
    if "PUSAT" in t or "NASIONAL" in t:
        return "PUSAT"
    return t

def _norm_text(x):
    if pd.isna(x):
        return None
    t = str(x).strip().upper()
    return " ".join(t.split())

# ---- Normalisasi PROV (untuk join) ----
def norm_prov_label(s):
    if pd.isna(s):
        return None
    t = str(s).upper().strip()
    t = " ".join(t.split())
    # buang prefix
    t = re.sub(r"^\s*(PROVINSI|PROPINSI)\s+", "", t)
    # buang tanda baca
    t = re.sub(r"[^A-Z0-9 ]+", " ", t)
    t = " ".join(t.split())
    # key
    return re.sub(r"[^A-Z0-9]+", "", t)

# ---- Normalisasi KAB/KOTA (untuk join) ----
def norm_kab_label(s):
    """
    FIX UTAMA:
    - Samakan variasi "KABUPATEN/KAB./KAB" dan "KOTA ADM./KOTA ADMINISTRASI"
    - Hapus kata 'DAN' agar match kasus: "PANGKAJENE DAN KEPULAUAN" vs "PANGKAJENE KEPULAUAN"
    - Buang simbol, spasi ganda
    """
    if pd.isna(s):
        return None
    t = str(s).upper().strip()
    t = " ".join(t.split())

    # seragamkan kab/kota
    t = t.replace("KABUPATEN", "KAB")
    t = t.replace("KAB.", "KAB")
    t = t.replace("KOTA ADMINISTRASI", "KOTA")
    t = t.replace("KOTA ADM.", "KOTA")
    t = t.replace("KOTA.", "KOTA")

    # FIX: buang "DAN" sebagai stopword join
    t = re.sub(r"\bDAN\b", " ", t)

    # bersihin simbol
    t = re.sub(r"[^A-Z0-9 ]+", " ", t)
    t = " ".join(t.split())

    return re.sub(r"[^A-Z0-9]+", "", t)

# ---- Display bersih (untuk dropdown/UI) ----
def clean_prov_display(s):
    if pd.isna(s):
        return None
    t = str(s).upper().strip()
    t = " ".join(t.split())
    t = t.replace("PROPINSI", "PROVINSI")
    while t.startswith("PROVINSI PROVINSI "):
        t = t.replace("PROVINSI PROVINSI ", "PROVINSI ", 1)
    t = t.replace("PROVINSI PROVINSI ", "PROVINSI ")
    if not t.startswith("PROVINSI "):
        t = "PROVINSI " + t
    return t

def clean_kab_display(s):
    if pd.isna(s):
        return None
    t = str(s).upper().strip()
    t = " ".join(t.split())
    t = t.replace("KABUPATEN", "KAB.")
    t = t.replace("KAB  ", "KAB. ")
    t = t.replace("KOTA ADMINISTRASI", "KOTA")
    # rapikan variasi "DAN" supaya konsisten tampilan juga
    t = re.sub(r"\bDAN\b", " ", t)
    t = " ".join(t.split())
    return t

def make_pie_plotly(num, den, title):
    if not HAS_KALEIDO:
        return None
    if den is None or pd.isna(den) or den <= 0:
        values = [0, 1]
        labels = ["Terjangkau", "Belum Terjangkau"]
    else:
        num = 0 if pd.isna(num) else float(num)
        den = float(den)
        values = [max(num, 0), max(den - num, 0)]
        labels = ["Terjangkau", "Belum Terjangkau"]
    fig = px.pie(values=values, names=labels, title=title, hole=0.35)
    tmp = tempfile.mktemp(suffix=".png")
    try:
        fig.write_image(tmp, scale=2)
        return tmp
    except Exception:
        return None


# ============================================================
# 3) LOAD DATA (DM + META)
# ============================================================
DATA_INFO = ""
df_all_raw = None

meta_kab_df = None   # kab_key -> target total + opsional sekolah/umum
meta_prov_df = None  # prov_key -> target total

prov_col_glob = None
kab_col_glob = None
kew_col_glob = None
jenis_col_glob = None
subjenis_col_glob = None
nama_col_glob = None

extra_info = []

# ---- Load DM ----
try:
    fp = Path(DATA_FILE)
    if not fp.exists():
        raise FileNotFoundError(f"File tidak ditemukan: {DATA_FILE}")

    xls = pd.ExcelFile(fp)
    frames = [pd.read_excel(fp, sheet_name=s) for s in xls.sheet_names]
    df_all_raw = pd.concat(frames, ignore_index=True, sort=False)

    prov_col_glob  = pick_col(df_all_raw, ["provinsi", "Provinsi", "PROVINSI"])
    kab_col_glob   = pick_col(df_all_raw, ["kab_kota", "kab/kota", "Kab/Kota", "KAB/KOTA", "kabupaten_kota", "kota"])
    kew_col_glob   = pick_col(df_all_raw, ["kewenangan", "jenis_kewenangan", "Kewenangan", "KEWENANGAN"])
    jenis_col_glob = pick_col(df_all_raw, ["jenis_perpustakaan", "JENIS_PERPUSTAKAAN", "Jenis Perpustakaan"])
    subjenis_col_glob = pick_col(df_all_raw, ["sub_jenis_perpus", "Sub Jenis", "SubJenis", "subjenis", "jenjang"])
    nama_col_glob  = pick_col(df_all_raw, ["nm_perpustakaan", "nama_perpustakaan", "nm_instansi_lembaga", "Nama Perpustakaan"])

    if kew_col_glob:
        df_all_raw["KEW_NORM"] = df_all_raw[kew_col_glob].apply(norm_kew)
    else:
        df_all_raw["KEW_NORM"] = None

    val_map_jenis = {
        "PERPUSTAKAAN SEKOLAH": "sekolah",
        "SEKOLAH": "sekolah",
        "PERPUSTAKAAN UMUM": "umum",
        "UMUM": "umum",
        "PERPUSTAKAAN DAERAH": "umum",
        "PERPUSTAKAAN KHUSUS": "khusus",
        "KHUSUS": "khusus",
        "PERPUSTAKAAN PERGURUAN TINGGI": "khusus",
        "PERGURUAN TINGGI": "khusus",
    }
    if jenis_col_glob:
        df_all_raw["_dataset"] = df_all_raw[jenis_col_glob].apply(_norm_text).map(val_map_jenis)
    else:
        df_all_raw["_dataset"] = None

    if prov_col_glob and prov_col_glob in df_all_raw.columns:
        df_all_raw["prov_clean"] = df_all_raw[prov_col_glob].apply(clean_prov_display)
    else:
        df_all_raw["prov_clean"] = None

    if kab_col_glob and kab_col_glob in df_all_raw.columns:
        df_all_raw["kab_clean"] = df_all_raw[kab_col_glob].apply(clean_kab_display)
    else:
        df_all_raw["kab_clean"] = None

    DATA_INFO = f"Data terbaca dari: **{DATA_FILE}** | Jumlah baris: **{len(df_all_raw)}**"
except Exception as e:
    df_all_raw = None
    DATA_INFO = f"⚠️ Gagal memuat `{DATA_FILE}` | Error: `{e}`"

# ---- Meta Kab/Kota ----
try:
    meta_kab_raw = pd.read_excel(META_KAB_FILE)

    col_kab = pick_col(meta_kab_raw, ["KABUPATEN_KOTA", "KAB/KOTA", "Kab/Kota", "Kab_Kota", "kab/kota", "kabupaten_kota"])
    col_target_total = pick_col(meta_kab_raw, ["sampel_total", "Sampel_total", "SAMPEL_TOTAL"])

    col_target_umum = pick_col(meta_kab_raw, ["Sampel_umum_68%", "sampel_umum_68%", "SAMPEL_UMUM_68%"])
    col_target_sek  = pick_col(meta_kab_raw, ["Sampel_sekolah_68%", "sampel_sekolah_68%", "SAMPEL_SEKOLAH_68%"])

    if col_kab and col_target_total:
        meta_kab_df = pd.DataFrame({
            "Kab_Kota_Label": meta_kab_raw[col_kab].astype(str).str.strip(),
            "Target_Total_68": meta_kab_raw[col_target_total].apply(coerce_num),
        })
        meta_kab_df["Target_Umum_68"] = meta_kab_raw[col_target_umum].apply(coerce_num) if col_target_umum else np.nan
        meta_kab_df["Target_Sekolah_68"] = meta_kab_raw[col_target_sek].apply(coerce_num) if col_target_sek else np.nan

        meta_kab_df["kab_key"] = meta_kab_df["Kab_Kota_Label"].apply(norm_kab_label)

        meta_kab_df = meta_kab_df.groupby("kab_key", as_index=False).agg({
            "Kab_Kota_Label": "first",
            "Target_Total_68": "first",
            "Target_Umum_68": "first",
            "Target_Sekolah_68": "first",
        })

        extra_info.append(f"Meta Kab/Kota terbaca: **{META_KAB_FILE}** (n={len(meta_kab_df)}) | Target=`sampel_total`")
    else:
        meta_kab_df = None
        extra_info.append(f"⚠️ Kolom `KABUPATEN_KOTA` atau `sampel_total` tidak ditemukan di `{META_KAB_FILE}`")
except Exception as e:
    meta_kab_df = None
    extra_info.append(f"⚠️ Gagal memuat `{META_KAB_FILE}` ({e})")

# ---- Meta Provinsi ----
try:
    meta_prov_raw = pd.read_excel(META_PROV_FILE)

    col_prov = pick_col(meta_prov_raw, ["Provinsi", "provinsi", "PROVINSI", "NAMA_PROVINSI", "Nama Provinsi", "nm_prov", "nm_provinsi", "prov"])

    # banyak variasi spasi/underscore
    col_target_total = pick_col(meta_prov_raw, ["total _sampel", "total_sampel", "TOTAL _SAMPEL", "TOTAL_SAMPEL", "total sampel", "TOTAL SAMPEL"])

    if col_prov and col_target_total:
        meta_prov_df = pd.DataFrame({
            "Provinsi_Label": meta_prov_raw[col_prov].astype(str).str.strip(),
            "Target_Total_68": meta_prov_raw[col_target_total].apply(coerce_num),
        })
        meta_prov_df["prov_key"] = meta_prov_df["Provinsi_Label"].apply(norm_prov_label)
        meta_prov_df = meta_prov_df.groupby("prov_key", as_index=False).agg({
            "Provinsi_Label": "first",
            "Target_Total_68": "first",
        })
        extra_info.append(f"Meta Provinsi terbaca: **{META_PROV_FILE}** ({len(meta_prov_df)} provinsi) | Target=`{col_target_total}`")
    else:
        meta_prov_df = None
        extra_info.append(f"⚠️ Kolom `Provinsi` atau `total _sampel` tidak ditemukan di `{META_PROV_FILE}`")
except Exception as e:
    meta_prov_df = None
    extra_info.append(f"⚠️ Gagal memuat file populasi provinsi: {e}")

if extra_info:
    DATA_INFO = DATA_INFO + "<br>" + "<br>".join(extra_info)


# ============================================================
# 4) DROPDOWN
# ============================================================
def all_prov_choices():
    if df_all_raw is None or "prov_clean" not in df_all_raw.columns:
        return ["(Semua)"]
    s = df_all_raw["prov_clean"].dropna().astype(str).str.strip()
    vals = sorted([o for o in s.unique() if o and o != ""])
    return ["(Semua)"] + vals

def get_kab_choices_for_prov(prov_value):
    if df_all_raw is None or "kab_clean" not in df_all_raw.columns:
        return ["(Semua)"]
    if prov_value is None or prov_value == "(Semua)":
        s = df_all_raw["kab_clean"].dropna().astype(str).str.strip()
    else:
        m = df_all_raw["prov_clean"].astype(str).str.strip() == str(prov_value).strip()
        s = df_all_raw.loc[m, "kab_clean"].dropna().astype(str).str.strip()
    vals = sorted([x for x in s.unique() if x and x != ""])
    return ["(Semua)"] + vals

def all_kew_choices():
    if df_all_raw is None:
        return ["(Semua)"]
    s = df_all_raw.get("KEW_NORM", pd.Series(dtype=object)).dropna().astype(str).str.strip()
    vals = sorted([o for o in s.unique() if o != ""])
    return ["(Semua)"] + vals if vals else ["(Semua)"]

prov_choices = all_prov_choices()
kab_choices  = get_kab_choices_for_prov(prov_choices[0] if prov_choices else "(Semua)")
kew_choices  = all_kew_choices()
default_kew  = "KAB/KOTA" if "KAB/KOTA" in kew_choices else (kew_choices[0] if kew_choices else "(Semua)")


# ============================================================
# 5) VERIFIKASI GAP — TARGET DARI META (bukan hitung ulang)
# ============================================================
def compute_gap_verification(df_filtered: pd.DataFrame, kew_value: str) -> pd.DataFrame:
    if df_filtered is None or len(df_filtered) == 0:
        return pd.DataFrame()

    kew_norm = str(kew_value or "").upper()

    # =================== KAB/KOTA ===================
    if ("KAB" in kew_norm or "KOTA" in kew_norm):
        if "kab_clean" not in df_filtered.columns or meta_kab_df is None:
            return pd.DataFrame({"Info": ["Kolom kab_clean atau meta kab tidak tersedia."]})

        tmp = df_filtered.copy()
        tmp = tmp[pd.notna(tmp["kab_clean"])]
        if tmp.empty:
            return pd.DataFrame()

        tmp["kab_key"] = tmp["kab_clean"].apply(norm_kab_label)

        g_total = tmp.groupby("kab_key").size().rename("Sampel Total (DM)").reset_index()

        tmp_sek = tmp[tmp["_dataset"] == "sekolah"].copy() if "_dataset" in tmp.columns else tmp.copy()
        g_sek_total = tmp_sek.groupby("kab_key").size().rename("Sampel Sekolah (DM)").reset_index()

        tmp_umum = tmp[tmp["_dataset"] == "umum"].copy() if "_dataset" in tmp.columns else tmp.copy()
        g_umum = tmp_umum.groupby("kab_key").size().rename("Sampel Umum (DM)").reset_index()

        merged = (
            g_total
            .merge(g_sek_total, on="kab_key", how="left")
            .merge(g_umum, on="kab_key", how="left")
            .merge(
                meta_kab_df[["kab_key", "Kab_Kota_Label", "Target_Total_68", "Target_Umum_68", "Target_Sekolah_68"]],
                on="kab_key", how="left"
            )
        )

        for c in ["Sampel Total (DM)", "Sampel Sekolah (DM)", "Sampel Umum (DM)"]:
            merged[c] = merged[c].fillna(0).astype(int)

        # marker match meta
        merged["META_MATCH"] = np.where(pd.notna(merged["Target_Total_68"]), "YA", "TIDAK")

        # target dari meta (ceil biar integer ke atas)
        merged["Target Total (68%)"] = np.ceil(pd.to_numeric(merged["Target_Total_68"], errors="coerce"))
        merged["Target Sekolah (68%)"] = np.ceil(pd.to_numeric(merged["Target_Sekolah_68"], errors="coerce"))
        merged["Target Umum (68%)"] = np.ceil(pd.to_numeric(merged["Target_Umum_68"], errors="coerce"))

        # kekurangan: kalau target NaN -> NaN (bukan 0)
        def _gap(target_series, sampel_series):
            t = pd.to_numeric(target_series, errors="coerce")
            s = pd.to_numeric(sampel_series, errors="coerce").fillna(0)
            out = t - s
            out = out.where(t.notna(), np.nan)
            return out.clip(lower=0)

        merged["Kekurangan Sampel Total"] = _gap(merged["Target Total (68%)"], merged["Sampel Total (DM)"])
        merged["Kekurangan Sampel Sekolah"] = _gap(merged["Target Sekolah (68%)"], merged["Sampel Sekolah (DM)"])
        merged["Kekurangan Sampel Umum"] = _gap(merged["Target Umum (68%)"], merged["Sampel Umum (DM)"])

        out = pd.DataFrame({
            "Kab/Kota": merged["Kab_Kota_Label"].fillna(merged["kab_key"]),
            "META_MATCH": merged["META_MATCH"],

            "Sampel Total (DM)": merged["Sampel Total (DM)"],
            "Target Total (68%) [META:sampel_total]": merged["Target Total (68%)"],
            "Kekurangan Sampel Total": merged["Kekurangan Sampel Total"],

            "Sampel Sekolah (DM)": merged["Sampel Sekolah (DM)"],
            "Target Sekolah (68%) [META]": merged["Target Sekolah (68%)"],
            "Kekurangan Sampel Sekolah": merged["Kekurangan Sampel Sekolah"],

            "Sampel Umum (DM)": merged["Sampel Umum (DM)"],
            "Target Umum (68%) [META]": merged["Target Umum (68%)"],
            "Kekurangan Sampel Umum": merged["Kekurangan Sampel Umum"],
        })

        # cast tampilan angka: biarkan NaN tetap NaN supaya ketahuan mismatch meta
        num_cols = [c for c in out.columns if c not in {"Kab/Kota", "META_MATCH"}]
        for c in num_cols:
            out[c] = pd.to_numeric(out[c], errors="coerce")

        return out.sort_values(["META_MATCH", "Kab/Kota"], ascending=[True, True]).reset_index(drop=True)

    # =================== PROVINSI ===================
    if ("PROV" in kew_norm):
        if meta_prov_df is None or "prov_clean" not in df_filtered.columns:
            return pd.DataFrame({"Info": ["Meta provinsi atau kolom prov_clean tidak tersedia."]})

        tmp = df_filtered.copy()
        tmp = tmp[pd.notna(tmp["prov_clean"])]
        if tmp.empty:
            return pd.DataFrame({"Info": ["Tidak ada data sampel kewenangan provinsi."]})

        tmp["prov_key"] = tmp["prov_clean"].apply(norm_prov_label)
        g_total = tmp.groupby("prov_key").size().rename("Sampel Total (DM)").reset_index()

        merged = g_total.merge(meta_prov_df[["prov_key", "Provinsi_Label", "Target_Total_68"]], on="prov_key", how="left")
        merged["Sampel Total (DM)"] = merged["Sampel Total (DM)"].fillna(0).astype(int)
        merged["META_MATCH"] = np.where(pd.notna(merged["Target_Total_68"]), "YA", "TIDAK")

        merged["Target Total (68%)"] = np.ceil(pd.to_numeric(merged["Target_Total_68"], errors="coerce"))
        t = pd.to_numeric(merged["Target Total (68%)"], errors="coerce")
        s = pd.to_numeric(merged["Sampel Total (DM)"], errors="coerce").fillna(0)
        gap = (t - s).where(t.notna(), np.nan).clip(lower=0)
        merged["Kekurangan Sampel Total"] = gap

        out = pd.DataFrame({
            "Provinsi": merged["Provinsi_Label"].fillna(merged["prov_key"]),
            "META_MATCH": merged["META_MATCH"],
            "Sampel Total (DM)": merged["Sampel Total (DM)"],
            "Target Total (68%) [META:total _sampel]": merged["Target Total (68%)"],
            "Kekurangan Sampel Total": merged["Kekurangan Sampel Total"],
        })

        for c in ["Sampel Total (DM)", "Target Total (68%) [META:total _sampel]", "Kekurangan Sampel Total"]:
            out[c] = pd.to_numeric(out[c], errors="coerce")

        return out.sort_values(["META_MATCH", "Provinsi"], ascending=[True, True]).reset_index(drop=True)

    return pd.DataFrame({"Info": ["Kewenangan tidak dikenali / tidak didukung."]})


# ============================================================
# 6) GRAFIK GAP — pakai Kekurangan Total (abaikan NaN)
# ============================================================
def make_gap_figure(verif_df: pd.DataFrame, kew_value: str) -> go.Figure:
    fig = go.Figure()
    if verif_df is None or verif_df.empty:
        fig.update_layout(title="Kekurangan Sampel (tidak ada data)", xaxis_title="Unit", yaxis_title="Kekurangan (unit)")
        return fig

    kew_norm = str(kew_value or "").upper()

    def _num(s):
        return pd.to_numeric(s, errors="coerce").fillna(0).astype(int)

    if ("KAB" in kew_norm or "KOTA" in kew_norm) and ("Kab/Kota" in verif_df.columns):
        dfp = verif_df.copy()
        dfp["gap_total"] = _num(dfp.get("Kekurangan Sampel Total", 0))
        dfp = dfp.sort_values("gap_total", ascending=False)

        x = dfp["Kab/Kota"].astype(str).tolist()
        gap_total = _num(dfp["gap_total"])

        fig.add_trace(go.Bar(
            x=x, y=gap_total, name="Kekurangan Total",
            text=gap_total, textposition="outside",
            hovertemplate="%{x}<br>Kekurangan total: %{y} unit<extra></extra>"
        ))
        fig.update_layout(
            title=f"Kekurangan Sampel TOTAL (KAB/KOTA) — Target {int(TARGET_COVERAGE*100)}% (META)",
            xaxis_title="Kab/Kota", yaxis_title="Kekurangan (unit)",
            margin=dict(l=40, r=20, t=60, b=140),
        )
        fig.update_xaxes(tickangle=-35)
        return fig

    if ("PROV" in kew_norm) and ("Provinsi" in verif_df.columns):
        dfp = verif_df.copy()
        dfp["gap_total"] = _num(dfp.get("Kekurangan Sampel Total", 0))
        dfp = dfp.sort_values("gap_total", ascending=False)

        x = dfp["Provinsi"].astype(str).tolist()
        gap_total = _num(dfp["gap_total"])

        fig.add_trace(go.Bar(
            x=x, y=gap_total, name="Kekurangan Total",
            text=gap_total, textposition="outside",
            hovertemplate="%{x}<br>Kekurangan total: %{y} unit<extra></extra>"
        ))
        fig.update_layout(
            title=f"Kekurangan Sampel TOTAL (PROVINSI) — Target {int(TARGET_COVERAGE*100)}% (META)",
            xaxis_title="Provinsi", yaxis_title="Kekurangan (unit)",
            margin=dict(l=40, r=20, t=60, b=140),
        )
        fig.update_xaxes(tickangle=-35)
        return fig

    fig.update_layout(title="Kekurangan Sampel — format data tidak dikenali", xaxis_title="Unit", yaxis_title="Kekurangan (unit)")
    return fig


# ============================================================
# 7) LLM NARASI
# ============================================================
def build_context_gap(verif_df: pd.DataFrame, prov: str, kab: str, kew: str) -> str:
    wilayah = kab if kab and kab != "(Semua)" else (prov if prov and prov != "(Semua)" else "NASIONAL")
    lines = []
    lines.append(f"Wilayah filter: {wilayah}")
    lines.append(f"Kewenangan: {kew}")
    lines.append(f"Target pengumpulan: {int(TARGET_COVERAGE*100)}% (TARGET diambil dari META).")
    lines.append(f"Jumlah unit analisis: {len(verif_df)}")

    if "Kekurangan Sampel Total" in verif_df.columns:
        total_gap = int(pd.to_numeric(verif_df["Kekurangan Sampel Total"], errors="coerce").fillna(0).sum())
        lines.append(f"Total Kekurangan Sampel Total: {total_gap}")

    if "META_MATCH" in verif_df.columns:
        n_no = int((verif_df["META_MATCH"] == "TIDAK").sum())
        if n_no > 0:
            lines.append(f"PERINGATAN: ada {n_no} unit yang tidak match ke META (target tidak tersedia).")

    keycol = "Kab/Kota" if "Kab/Kota" in verif_df.columns else ("Provinsi" if "Provinsi" in verif_df.columns else verif_df.columns[0])
    if "Kekurangan Sampel Total" in verif_df.columns:
        t = verif_df.copy()
        t["Kekurangan Sampel Total"] = pd.to_numeric(t["Kekurangan Sampel Total"], errors="coerce").fillna(0)
        top = t.sort_values("Kekurangan Sampel Total", ascending=False).head(10)
        lines.append("\nTop prioritas (gap terbesar):")
        for _, r in top.iterrows():
            lines.append(f"- {r[keycol]}: gap_total={int(r['Kekurangan Sampel Total'])}")

    return "\n".join(lines)

def rule_based_gap_report(verif_df: pd.DataFrame, prov: str, kab: str, kew: str) -> str:
    if verif_df is None or verif_df.empty:
        return "Tidak ada data verifikasi yang dapat dilaporkan."

    wilayah = kab if kab and kab != "(Semua)" else (prov if prov and prov != "(Semua)" else "NASIONAL")
    lines = []
    lines.append("## Ringkasan Kekurangan Sampel IPLM (Rule-based)\n")
    lines.append(f"Wilayah: {wilayah}")
    lines.append(f"Kewenangan: {kew}")
    lines.append(f"Target pengumpulan: {int(TARGET_COVERAGE*100)}% (TARGET diambil dari META: kab/kota=`sampel_total`, provinsi=`total _sampel`).")
    lines.append(f"Jumlah unit analisis: {len(verif_df)}\n")

    if "Kekurangan Sampel Total" in verif_df.columns:
        total_gap = int(pd.to_numeric(verif_df["Kekurangan Sampel Total"], errors="coerce").fillna(0).sum())
        lines.append(f"- Total Kekurangan Sampel Total: **{total_gap}** unit yang perlu dilengkapi menuju target.")
    else:
        lines.append("Kolom kekurangan sampel total tidak ditemukan.")

    if "META_MATCH" in verif_df.columns:
        n_no = int((verif_df["META_MATCH"] == "TIDAK").sum())
        if n_no > 0:
            lines.append(f"- Catatan: **{n_no}** unit belum match ke META, sehingga target tidak tersedia (perlu pembenahan label/meta).")

    lines.append("\nArah tindak lanjut: prioritaskan wilayah dengan gap terbesar, dan pastikan mapping unit ke META valid untuk monitoring yang akurat.")
    return "\n".join(lines)

def generate_llm_gap_report(verif_df: pd.DataFrame, prov: str, kab: str, kew: str) -> str:
    ctx = build_context_gap(verif_df, prov, kab, kew)
    client = get_llm_client()
    if client is None or not USE_LLM:
        return "⚠️ LLM tidak tersedia, memakai laporan rule-based.\n\n" + rule_based_gap_report(verif_df, prov, kab, kew)

    system_prompt = (
        "Anda adalah analis kebijakan dan manajer program IPLM. "
        "Fokus Anda hanya pada gap sampel (kekurangan unit) dan strategi menutup kekurangan tersebut."
    )
    user_prompt = f"""
DATA RINGKAS GAP SAMPEL IPLM:

{ctx}

TULIS LAPORAN (BAHASA INDONESIA FORMAL) DENGAN STRUKTUR:
1) Ringkasan kondisi pengumpulan data (1 paragraf).
2) Total kekurangan sampel yang masih perlu dikumpulkan menuju target {int(TARGET_COVERAGE*100)}% (1 paragraf).
3) Prioritas wilayah (gap terbesar) dan alasan operasional (1 paragraf).
4) Rencana aksi 30–60 hari (naratif, bukan bullet).

BATASAN:
- Jangan membahas indeks/skor IPLM.
- Tegaskan bahwa target berasal dari META: kab/kota=`sampel_total`, provinsi=`total _sampel`.
- Jika ada unit META_MATCH=TIDAK, sebutkan sebagai isu kualitas data/master reference.
"""
    try:
        resp = client.chat_completion(
            model=LLM_MODEL_NAME,
            messages=[{"role": "system", "content": system_prompt},
                      {"role": "user", "content": user_prompt}],
            max_tokens=900,
            temperature=0.2,
            top_p=0.9,
        )
        text = resp.choices[0].message.content.strip()
        if not text:
            raise ValueError("Respon LLM kosong.")
        return text
    except Exception as e:
        return (
            "⚠️ Error saat memanggil LLM, memakai laporan rule-based.\n\n"
            f"(Detail teknis: {repr(e)})\n\n"
            + rule_based_gap_report(verif_df, prov, kab, kew)
        )


# ============================================================
# 8) WORD REPORT
# ============================================================
def generate_word_report_gap(verif_df: pd.DataFrame, prov: str, kab: str, kew: str, analysis_text: str):
    wilayah = kab if kab and kab != "(Semua)" else (prov if prov and prov != "(Semua)" else "NASIONAL")

    doc = Document()
    doc.add_heading(f"Laporan Kekurangan Sampel IPLM – {wilayah}", level=1)
    doc.add_paragraph(f"Kewenangan: {kew}")
    doc.add_paragraph(f"Target pengumpulan: {int(TARGET_COVERAGE*100)}% (TARGET diambil dari META).")
    doc.add_paragraph(f"Jumlah unit analisis: {len(verif_df)}")

    doc.add_heading("Tabel Verifikasi (Target & Kekurangan Sampel)", level=2)

    view = verif_df.copy()
    if len(view) > 200:
        doc.add_paragraph("Catatan: tabel dipotong (200 baris pertama) untuk menjaga ukuran dokumen.")
        view = view.head(200)

    table = doc.add_table(rows=1, cols=len(view.columns))
    hdr = table.rows[0].cells
    for i, c in enumerate(view.columns):
        hdr[i].text = str(c)

    for _, row in view.iterrows():
        r = table.add_row().cells
        for i, c in enumerate(view.columns):
            r[i].text = "" if pd.isna(row[c]) else str(row[c])

    doc.add_heading("Ringkasan Visual (Opsional)", level=2)
    if not HAS_KALEIDO:
        doc.add_paragraph("Grafik pie tidak dibuat karena 'kaleido' tidak tersedia di server.")
    else:
        pie_made = False
        if "Sampel Total (DM)" in verif_df.columns:
            samp = pd.to_numeric(verif_df["Sampel Total (DM)"], errors="coerce").fillna(0).sum()
            tgt_col = None
            for c in verif_df.columns:
                if "Target Total (68%)" in c:
                    tgt_col = c
                    break
            if tgt_col:
                tgt = pd.to_numeric(verif_df[tgt_col], errors="coerce").fillna(0).sum()
                img = make_pie_plotly(samp, tgt, "Capaian TOTAL (DM) terhadap Target TOTAL (META)")
                if img:
                    doc.add_paragraph("Capaian TOTAL terhadap Target TOTAL (META)")
                    doc.add_picture(img)
                    pie_made = True

        if not pie_made:
            doc.add_paragraph("Tidak ada pasangan kolom sampel-target yang valid untuk dibuat pie chart.")

    doc.add_heading("Analisis Naratif (LLM)", level=2)
    for p in analysis_text.split("\n"):
        if p.strip():
            doc.add_paragraph(p)

    outpath = tempfile.mktemp(suffix=".docx")
    doc.save(outpath)
    return outpath


# ============================================================
# 9) CORE RUN
# ============================================================
def run_core(prov_value, kab_value, kew_value):
    if df_all_raw is None or df_all_raw.empty:
        empty = pd.DataFrame()
        return empty, empty, None, None, None, None, "Data DM tidak terbaca.", "Tidak ada analisis."

    df = df_all_raw.copy()

    if prov_value and prov_value != "(Semua)" and "prov_clean" in df.columns:
        df = df[df["prov_clean"].astype(str).str.strip() == str(prov_value).strip()]

    if kab_value and kab_value != "(Semua)" and "kab_clean" in df.columns:
        df = df[df["kab_clean"].astype(str).str.strip() == str(kab_value).strip()]

    if kew_value and kew_value != "(Semua)":
        df = df[df["KEW_NORM"] == kew_value]

    if len(df) == 0:
        empty = pd.DataFrame()
        return empty, empty, None, None, None, None, "Tidak ada data untuk kombinasi filter yang dipilih.", "Tidak ada analisis."

    verif_df = compute_gap_verification(df, kew_value)

    cols = []
    for c in ["prov_clean", "kab_clean", nama_col_glob, kew_col_glob, jenis_col_glob, subjenis_col_glob, "_dataset", "KEW_NORM"]:
        if c and c in df.columns and c not in cols:
            cols.append(c)
    detail_df = df[cols].copy() if cols else df.copy()

    fig_gap = make_gap_figure(verif_df, kew_value)

    tmpdir = tempfile.mkdtemp()
    rekap_excel_path = os.path.join(tmpdir, "Rekap_Kekurangan_Sampel_IPLM_Target_META.xlsx")
    raw_dm_path      = os.path.join(tmpdir, "DM_Subset_Raw.xlsx")

    with pd.ExcelWriter(rekap_excel_path, engine="openpyxl") as w:
        verif_df.to_excel(w, sheet_name="Verifikasi_Gap_Target_META", index=False)
        detail_df.to_excel(w, sheet_name="Detail_Subset_DM", index=False)

    df.to_excel(raw_dm_path, index=False)

    analysis_text = generate_llm_gap_report(verif_df, prov_value, kab_value, kew_value)
    word_path = generate_word_report_gap(verif_df, prov_value, kab_value, kew_value, analysis_text)

    # message ringkas + warning mismatch meta
    warn = ""
    if "META_MATCH" in verif_df.columns:
        n_no = int((verif_df["META_MATCH"] == "TIDAK").sum())
        if n_no > 0:
            warn = f" ⚠️ {n_no} unit tidak match ke META (target NaN)."

    msg = f"OK. Subset DM: {len(df)} baris | Verifikasi: {len(verif_df)} baris | Target: {int(TARGET_COVERAGE*100)}% (META).{warn}"

    return verif_df, detail_df, fig_gap, rekap_excel_path, raw_dm_path, word_path, msg, analysis_text

def on_prov_change(prov_value):
    return gr.update(choices=get_kab_choices_for_prov(prov_value), value="(Semua)")


# ============================================================
# 10) UI
# ============================================================
with gr.Blocks() as demo:
    gr.Markdown(
        f"""
# Dashboard Kekurangan Sampel IPLM — Target {int(TARGET_COVERAGE*100)}% (Tanpa Hitung Indeks)

**Target dari META (bukan hitung ulang):**
- Kab/Kota: `{META_KAB_FILE}` kolom **`sampel_total`**
- Provinsi: `{META_PROV_FILE}` kolom **`total _sampel`** (variasi spasi/underscore didukung)

{DATA_INFO}
"""
    )

    with gr.Row():
        dd_prov = gr.Dropdown(label="Provinsi", choices=prov_choices, value=prov_choices[0])
        dd_kab  = gr.Dropdown(label="Kab/Kota", choices=kab_choices, value=kab_choices[0])
        dd_kew  = gr.Dropdown(label="Kewenangan", choices=kew_choices, value=default_kew)

    dd_prov.change(fn=on_prov_change, inputs=dd_prov, outputs=dd_kab)

    run_btn = gr.Button("Hitung Kekurangan Sampel")
    msg_out = gr.Markdown()

    gr.Markdown("### Verifikasi (Target & Kekurangan Sampel) — Target dari META")
    verif_out = gr.DataFrame(interactive=False)

    gr.Markdown("### Grafik Kekurangan Sampel TOTAL (unit)")
    gap_plot_out = gr.Plot()

    gr.Markdown("### Detail Subset DM (yang terfilter)")
    detail_out = gr.DataFrame(interactive=False)

    gr.Markdown("### Analisis Naratif (LLM)")
    analysis_out = gr.Markdown()

    with gr.Row():
        rekap_excel_out = gr.File(label="Download Rekap (Verifikasi + Detail) (.xlsx)")
        raw_dm_out      = gr.File(label="Download Data Mentah Subset DM (.xlsx)")
        word_out        = gr.File(label="Download Laporan Word (.docx)")

    run_btn.click(
        fn=run_core,
        inputs=[dd_prov, dd_kab, dd_kew],
        outputs=[
            verif_out,
            detail_out,
            gap_plot_out,
            rekap_excel_out,
            raw_dm_out,
            word_out,
            msg_out,
            analysis_out
        ],
    )

demo.launch()