Blame for app.py

"""Tablo2SQL-TR arayüzü:  streamlit run app.py"""
from __future__ import annotations

import html
import json
import re
import tempfile
import time
import warnings
from pathlib import Path

import pandas as pd
import streamlit as st

warnings.filterwarnings("ignore")

from t2s.agent import Text2SQLAgent, _exec
from t2s.ai import AIHelper
from t2s.errors import ErrorLog
from t2s.export import csv_zip_bytes, sql_dump_bytes, sqlite_bytes, stamp, table_csv_bytes, xlsx_bytes
from t2s.extract import extract_any, resolve
from t2s.i18n import LANGS, catalog, format_num, t
from t2s.llm import OLLAMA_RECS, USAGE, list_models, load_dotenv, make_backend, ollama_pull, ollama_version
import arayuz_tema as TEMA  # noqa: E402
import otomasyon.arayuz as OTO  # noqa: E402
from t2s.settings import CLOUD, KEY_URLS, RULE, SETTINGS_FILE, Settings, mask_key, split_choice
from t2s.pipeline import DOCS_DIR, build_database, merge_continuations
from t2s.schema import (build_table, connect, describe_schema, drop_same_source, ensure_meta, list_tables,
                        set_table_summary, write_table)

ROOT = Path(__file__).resolve().parent
OUT = ROOT / "outputs"
UI_DB = OUT / "db" / "arayuz.db"
UI_LOG = ErrorLog(OUT / "arayuz_hatalari.jsonl")
EVAL_LOG = OUT / "hata_gunlugu.jsonl"
PREFS = OUT / ".arayuz_ayarlari.json"

load_dotenv(ROOT / ".env")
st.set_page_config(page_title="Tablo2SQL", page_icon="🗂️", layout="wide")


# =========================================================================== ayarlar (tek dosya)
CFG = Settings()          # ayarlar/tablo2sql-ayarlar.json
# Seçenek etiketleri dile göre değişen widget anahtarları (dil değişince sıfırlanır)


def _clean_selections():
    """Ayar dosyasına yanlışlıkla etiket olarak yazılmış model seçimlerini temizler (eski sürüm hatası)."""
    ok = set(CFG.choices())
    sec = CFG.get("secim", default={}) or {}
    changed = False
    if sec.get("genel") not in ok:
        sec["genel"], changed = RULE, True
    for k, v in list((sec.get("sekmeler") or {}).items()):
        if isinstance(v, list):
            nv = [x for x in v if x in ok]
            if nv != v:
                sec["sekmeler"][k], changed = nv, True
        elif v is not None and v not in ok:
            sec["sekmeler"][k], changed = None, True
    if changed:
        CFG.save()


_clean_selections()


ss = st.session_state
ss.setdefault("lang", CFG.get("dil", default="tr"))
ss.setdefault("theme", CFG.get("tema", default="light"))
ss.setdefault("db_version", 0)
ss.setdefault("chat", [])
ss.setdefault("extracted", [])
ss.setdefault("conn_state", {})   # sağlayıcı -> {"ok": bool, "msg": str} (bu oturumda denetlendi)
ss.setdefault("q_seed", 0)
L = ss.lang
# Not: seçenek etiketleri dile bağlı widget'ların anahtarına dil eklenir (ör. global_model_tr). Aksi halde
# Streamlit dil değişince eski etiketi (ör. 'Rule-based (AI off)') değer olarak döndürür.


def T(key: str, **kw) -> str:
    return t(key, L, **kw)


# ---- gezinme: sayfalar en başta kaydedilir (aşağıdaki st.rerun çağrıları açık sayfayı kaybetmesin);
# sayfa fonksiyonları dosyanın sonunda tanımlıdır ve NAV.run() ile en sonda çalıştırılır.
_NAV_NAMES = {"tr": ["Belgeler", "Bilgi tabanı", "Soru sor", "Hata kataloğu", "Değerlendirme", "Otomasyon", "Raporlar"],
              "en": ["Documents", "Knowledge base", "Ask", "Error catalog", "Evaluation", "Automation", "Reports"]}
_NAV_KEYS = ["docs", "kb", "ask", "err", "eval", "oto", "rep"]


def _page_runner(k: str):
    def run():
        globals()[f"page_{k}"]()
    run.__name__ = f"page_{k}"
    return run


NAV = st.navigation([st.Page(_page_runner(k), title=n, url_path=k, default=(k == "docs"))
                     for k, n in zip(_NAV_KEYS, _NAV_NAMES.get(L, _NAV_NAMES["tr"]))], position="top")

# ---- tema (arayuz_tema.py: TestAjanı görünümü). Renkler hem Streamlit temasına hem CSS'e verilir.
if ss.get("_theme_applied") != ss.theme:
    for _k, _v in TEMA.streamlit_theme(ss.theme).items():
        try:
            st._config.set_option(f"theme.{_k}", _v)
        except Exception:  # noqa: BLE001 - eski Streamlit sürümlerinde olmayan ayarları atla
            pass
    ss._theme_applied = ss.theme
    st.rerun()

DARK = ss.theme == "dark"
P = TEMA.palette(ss.theme)
C = {  # uygulama içinde kullanılan durum renkleri
    "ok": P["ok"], "ok_bg": P["ok_bg"], "warn": P["warn"], "warn_bg": P["warn_bg"], "bad": P["bad"], "bad_bg": P["bad_bg"],
    "ai": P["acc_text"], "ai_bg": P["acc_soft"], "line": P["line"], "head": P["surface2"],
    "merge": "rgba(125,143,240,.30)" if DARK else "rgba(91,110,232,.18)",
}
st.markdown(TEMA.css(ss.theme, L), unsafe_allow_html=True)
_LOGO, _LOGO_ICON = TEMA.write_logos(OUT / ".tema", ss.theme)
st.logo(str(_LOGO), size="large")


# =========================================================================== veritabanı
def ensure_db():
    if not UI_DB.exists():
        if not any(DOCS_DIR.glob("*.pdf")):
            import subprocess
            import sys
            subprocess.run([sys.executable, str(ROOT / "data" / "generate_samples.py")], check=True)
        build_database(UI_DB, mode="tr")


@st.cache_resource
def get_con(path: str, version: int):
    c = connect(path)
    ensure_meta(c)
    return c


def con():
    return get_con(str(UI_DB), ss.db_version)


@st.cache_resource
def get_ro_con(path: str, version: int):
    return connect(path, readonly=True)


def ro_con():
    """Ajan ve SQL konsolu için salt-okunur bağlantı: üretilen sorgular veritabanını değiştiremez."""
    con()  # veritabanı ve meta tablolar hazır olsun
    return get_ro_con(str(UI_DB), ss.db_version)


def bump_db():
    ss.db_version += 1


ensure_db()


def _ro(fn):
    """İndirme dosyasını tıklama anında üretir. Ayrı bir iş parçacığında çalıştığı için kendi salt-okunur
    bağlantısını açar. (Dosyalar sayfa açılırken hazırlanmaz; indirme yöneticileri de onları yakalamaz.)"""
    c = connect(UI_DB, readonly=True)
    try:
        return fn(c)
    finally:
        c.close()


def _deferred_export(fmt: str) -> bytes:
    return _ro({"db": sqlite_bytes, "xlsx": xlsx_bytes, "zip": csv_zip_bytes, "sql": sql_dump_bytes}[fmt])


def download_row(c, key: str, compact: bool = False):
    """Veritabanını dört biçimde indirme düğmeleri (SQLite, Excel, CSV zip, SQL dökümü)."""
    s = stamp()
    items = [("dl_sqlite", "db", f"tablo2sql_{s}.db", "application/vnd.sqlite3"),
             ("dl_xlsx", "xlsx", f"tablo2sql_{s}.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"),
             ("dl_csv", "zip", f"tablo2sql_{s}_csv.zip", "application/zip"),
             ("dl_sql", "sql", f"tablo2sql_{s}.sql", "application/sql")]
    if compact:
        items = items[:2]
    cols = st.columns(len(items))
    for col, (lab, fmt, fname, mime) in zip(cols, items):
        col.download_button("⬇️ " + T(lab), lambda fmt=fmt: _deferred_export(fmt), fname, mime, key=f"{key}_{fmt}",
                            use_container_width=True)


# =========================================================================== model bağlantıları
DEV_KEYS = {"auto": "dev_auto", "gpu": "dev_gpu", "cpu": "dev_cpu"}
DEV_HELP = {"auto": "dev_help_auto", "gpu": "dev_help_gpu", "cpu": "dev_help_cpu"}
SET_KEYS = {"agir_hizli": "set_agir_hizli", "agir": "set_agir", "stres": "set_stres", "pilot": "set_pilot"}
PROV_NAME = {"ollama": "Ollama", "openai": "OpenAI", "claude": "Claude", RULE: "Kural"}


def fmt_time(iso: str | None) -> str:
    if not iso:
        return "—"
    try:
        tm = time.strptime(iso, "%Y-%m-%dT%H:%M:%S")
        return time.strftime("%d.%m.%Y %H:%M" if L == "tr" else "%m/%d/%Y, %I:%M %p", tm)
    except ValueError:
        return iso


def do_sync(prov: str, quiet: bool = False) -> bool:
    """Sağlayıcının model listesini çeker ve ayar dosyasına yazar."""
    try:
        if prov in CLOUD and not CFG.key(prov):
            CFG.set_models(prov, [], error=T("models_need_key"))
            ss.conn_state[prov] = {"ok": False, "msg": T("models_need_key")}
            return False
        ctx = st.spinner(T("syncing", p=PROV_NAME[prov])) if not quiet else _nullctx()
        with ctx:
            lst = list_models(prov, CFG.key(prov) or None, CFG.base(prov))
        CFG.set_models(prov, [m.__dict__ for m in lst])
        ss.conn_state[prov] = {"ok": True, "msg": ""}
        return True
    except Exception as e:  # noqa: BLE001
        msg = T("ollama_unreachable") if prov == "ollama" else T("models_error", e=str(e)[:200])
        CFG.set_models(prov, [], error=msg)
        ss.conn_state[prov] = {"ok": False, "msg": msg}
        return False


class _nullctx:
    def __enter__(self):
        return self

    def __exit__(self, *a):
        return False


def auto_sync():
    """Oturum başında bir kez: bağlı sağlayıcıları denetle; liste boşsa ya da 12 saatten eskiyse senkronize et."""
    if ss.get("_auto_synced"):
        return
    ss._auto_synced = True
    for prov in ("ollama", "openai", "claude"):
        if prov in CLOUD and not CFG.key(prov):
            continue
        last = CFG.conn(prov).get("son_senkron")
        stale = True
        if last:
            try:
                stale = time.time() - time.mktime(time.strptime(last, "%Y-%m-%dT%H:%M:%S")) > 12 * 3600
            except ValueError:
                pass
        if prov == "ollama" or stale or not CFG.models(prov):
            do_sync(prov, quiet=True)
        else:
            ss.conn_state[prov] = {"ok": True, "msg": ""}


auto_sync()


def status_html(prov: str) -> str:
    stt = ss.conn_state.get(prov)
    n = len(CFG.models(prov))
    if prov in CLOUD and not CFG.key(prov):
        return f"<span style='opacity:.6'>○ {T('st_not_connected')}</span>"
    if stt and not stt["ok"]:
        return f"<span style='color:{C['bad']}'>● {T('st_unreachable')}</span>"
    if stt and stt["ok"]:
        return f"<span style='color:{C['ok']}'>● {T('st_connected', n=n)}</span>"
    return f"<span style='opacity:.6'>○ {T('st_unknown')}</span>"


def model_label(choice: str) -> str:
    prov, mid = split_choice(choice)
    if prov == RULE:
        return T("prov_rule")
    info = next((m for m in CFG.models(prov) if m["id"] == mid), None)
    lab = info["label"] if info and info.get("label") and info["label"] != mid else ""
    extra = f" ({info['note']})" if prov == "ollama" and info and info.get("note") else ""
    return f"{PROV_NAME[prov]} · {lab + ' — ' if lab else ''}{mid}{extra}"


def models_table(prov: str):
    ms = CFG.models(prov)
    if not ms:
        st.caption(T("models_none"))
        return
    rows = []
    for m in ms:
        r = {"model": m["id"]}
        if m.get("label") and m["label"] != m["id"]:
            r[T("col_name")] = m["label"]
        if prov == "ollama":
            r[T("col_size")] = f"{m['size'] / 1e9:.1f} GB" if m.get("size") else ""
            r[T("col_detail")] = m.get("note", "")
        rows.append(r)
    st.dataframe(pd.DataFrame(rows), hide_index=True, use_container_width=True, height=min(320, 36 + 35 * len(rows)))


def card_header(prov: str, badge: str, badge_kind: str):
    a, b = st.columns([3, 2])
    a.markdown(f"<div style='font-size:1.15rem;font-weight:700'>{PROV_NAME[prov]} "
               f"<span class='pill {badge_kind}' style='font-size:.72rem;vertical-align:middle'>{badge}</span></div>",
               unsafe_allow_html=True)
    b.markdown(f"<div style='text-align:right;font-size:.9rem;padding-top:.2rem'>{status_html(prov)}</div>",
               unsafe_allow_html=True)


def ollama_card():
    with st.container(border=True, key="card_ollama"):
        card_header("ollama", T("badge_local"), "ok")
        c = CFG.conn("ollama")
        st.code(CFG.base("ollama"), language=None)
        stt = ss.conn_state.get("ollama")
        if stt and not stt["ok"]:
            st.caption(stt["msg"])
        with st.expander(T("ollama_change_url")):
            url = st.text_input(T("ollama_host"), c.get("url") or "http://localhost:11434", key="dlg_ollama_url")
            if st.button(T("save_and_sync"), key="dlg_ollama_save", type="primary"):
                CFG.set("baglantilar", "ollama", "url", url.strip().rstrip("/"))
                do_sync("ollama")
                st.rerun(scope="fragment")
        st.markdown(f"**{T('device_title')}**")
        dev = st.segmented_control(T("device_title"), ["auto", "gpu", "cpu"], default=c.get("cihaz", "auto"),
                                   format_func=lambda k: T(DEV_KEYS[k]), key=f"dlg_dev_{L}", label_visibility="collapsed")
        if dev and dev != c.get("cihaz"):
            CFG.set("baglantilar", "ollama", "cihaz", dev)
        st.markdown(f"<div style='font-size:.9rem'>{T(DEV_HELP[dev or 'auto'])}</div>", unsafe_allow_html=True)
        st.caption(T("dev_note"))
        x, y = st.columns(2)
        if x.button(T("sync_models"), key="dlg_ollama_sync", use_container_width=True):
            do_sync("ollama")
            st.rerun(scope="fragment")
        if stt and stt["ok"]:
            try:
                y.caption(T("ollama_ver", v=ollama_version(CFG.base("ollama"))))
            except Exception:  # noqa: BLE001
                pass
        with st.expander(T("show_models", n=len(CFG.models("ollama")))):
            models_table("ollama")
        with st.expander(T("ollama_pick_title")):
            st.caption(T("ollama_pick_help"))
            installed = {m["id"] for m in CFG.models("ollama")}
            recs = pd.DataFrame([{"model": r.id, T("col_installed"): "✓" if r.id in installed else "",
                                  T("col_size"): f"~{r.size_gb:g} GB", T("col_mem"): f"{r.min_ram_gb} GB+",
                                  T("col_note"): r.note_tr if L == "tr" else r.note_en} for r in OLLAMA_RECS])
            st.dataframe(recs, hide_index=True, use_container_width=True)
            want = st.selectbox(T("ollama_pull_pick"), [r.id for r in OLLAMA_RECS], key="dlg_pull_pick",
                                accept_new_options=True)
            if st.button(T("ollama_pull"), key="dlg_pull_btn", disabled=not (stt and stt["ok"])):
                bar = st.progress(0.0, T("ollama_pulling", m=want))
                try:
                    for status, done, total in ollama_pull(want, CFG.base("ollama")):
                        bar.progress(min(1.0, (done / total) if done and total else 0.0), f"{want}: {status}")
                    st.success(T("ollama_pulled", m=want))
                    do_sync("ollama", quiet=True)
                except Exception as e:  # noqa: BLE001
                    st.error(str(e)[:300])


def cloud_card(prov: str):
    with st.container(border=True, key=f"card_{prov}"):
        card_header(prov, T("badge_cloud"), "ai")
        c = CFG.conn(prov)
        src = CFG.key_source(prov)
        if src:
            k = mask_key(CFG.key(prov))
            where = T("key_from_env") if src == "ortam" else ""
            st.markdown(f"<div style='font-size:.9rem;opacity:.8'>{T('saved_key')}: <code>{html.escape(k)}</code>{where} · "
                        f"{T('last_synced')} {fmt_time(c.get('son_senkron'))}</div>", unsafe_allow_html=True)
        stt = ss.conn_state.get(prov)
        if stt and not stt["ok"]:
            st.error(stt["msg"])
        a, b = st.columns([3, 1.3], vertical_alignment="bottom")
        newk = a.text_input(T("api_key"), "", type="password", key=f"dlg_key_{prov}",
                            placeholder=T("key_placeholder_new") if src else T("key_placeholder"))
        if b.button(T("save_and_sync"), key=f"dlg_save_{prov}", type="primary", use_container_width=True,
                    disabled=not newk.strip()):
            CFG.set_key(prov, newk)
            ss.pop(f"dlg_key_{prov}", None)
            do_sync(prov)
            st.rerun(scope="fragment")
        if prov == "openai":
            with st.expander(T("server_url_opt"), expanded=bool(c.get("url"))):
                url = st.text_input(T("base_url"), c.get("url") or "", placeholder="https://api.openai.com/v1",
                                    key="dlg_openai_url", help=T("server_url_help"))
                if url.strip().rstrip("/") != (c.get("url") or ""):
                    CFG.set("baglantilar", "openai", "url", url.strip().rstrip("/"))   # anahtar kaydında da kullanılır
                st.caption(T("server_url_note"))
        l1, l2, l3 = st.columns([1.4, 1, 1], vertical_alignment="center")
        l1.markdown(f"[{T('get_key')} ↗]({KEY_URLS[prov]})")
        if l2.button(T("sync_models"), key=f"dlg_sync_{prov}", use_container_width=True, disabled=not src):
            do_sync(prov)
            st.rerun(scope="fragment")
        if l3.button(T("disconnect"), key=f"dlg_disc_{prov}", use_container_width=True,
                     disabled=not (c.get("anahtar") or CFG.models(prov))):
            CFG.disconnect(prov)
            ss.conn_state.pop(prov, None)
            st.rerun(scope="fragment")
        if src == "ortam" and not c.get("anahtar"):
            st.caption(T("env_key_note"))
        with st.expander(T("show_models", n=len(CFG.models(prov)))):
            models_table(prov)


def _dialog(fn):
    try:
        return st.dialog(T("conn_title"), width="medium", on_dismiss="rerun")(fn)
    except TypeError:  # eski Streamlit
        return st.dialog(T("conn_title"), width="medium")(fn)


@_dialog
def connections_dialog():
    st.markdown(f"<div style='opacity:.8'>{T('conn_intro')}</div>", unsafe_allow_html=True)
    st.write("")
    ollama_card()
    cloud_card("openai")
    cloud_card("claude")
    st.info(T("cloud_notice"))
    with st.container(border=True):
        st.markdown(f"**{T('settings_file')}**")
        st.markdown(T("settings_file_help", p=f"`{SETTINGS_FILE.relative_to(ROOT).as_posix()}`"))
        d1, d2 = st.columns(2)
        d1.download_button(T("dl_settings"), lambda: CFG.to_bytes(), SETTINGS_FILE.name, "application/json",
                           key="dlg_dl_settings", use_container_width=True)
        with d2.popover(T("load_settings"), use_container_width=True):
            up = st.file_uploader(T("load_settings"), type=["json"], key="dlg_up_settings",
                                  label_visibility="collapsed")
            if up is not None and st.button(T("load_apply"), key="dlg_load_apply", type="primary"):
                try:
                    CFG.load_bytes(up.getvalue())
                    ss.conn_state = {}
                    ss._auto_synced = False
                    ss.lang, ss.theme = CFG.get("dil"), CFG.get("tema")
                    st.success(T("settings_loaded"))
                    st.rerun()
                except ValueError as e:
                    st.error(str(e))
        st.caption(T("settings_plain_warn"))
    if st.button(T("close_apply"), key="dlg_close", use_container_width=True):
        st.rerun()


def build_backend(choice: str, device: str | None = None):
    """Seçim -> (backend, hata). 'kural' için backend 'kural' dizgesidir."""
    prov, mid = split_choice(choice)
    if prov == RULE:
        return "kural", None
    try:
        dev = device or CFG.conn("ollama").get("cihaz", "auto")
        return make_backend(prov, mid, CFG.key(prov) or None, CFG.base(prov), device=dev if prov == "ollama" else None), None
    except Exception as e:  # noqa: BLE001
        return "kural", str(e)


_AI_CACHE: dict = {}


def tab_model(tab: str):
    """Sekmeye özel model seçici. Varsayılan: kenar çubuğundaki genel model. (backend, AIHelper) döndürür."""
    choices = CFG.choices()
    saved = CFG.get("secim", "sekmeler", tab)
    opts = ["__genel__"] + choices
    idx = opts.index(saved) if saved in opts else 0
    pick = st.selectbox(T("tab_model"), opts, index=idx, key=f"tabmodel_{tab}_{L}",
                       format_func=lambda c: T("use_default", m=model_label(GLOBAL_CHOICE)) if c == "__genel__"
                       else model_label(c))
    if pick not in opts:
        ss.pop(f"tabmodel_{tab}_{L}", None)
        pick = opts[idx]
    if pick != (saved or "__genel__"):
        CFG.set("secim", "sekmeler", tab, None if pick == "__genel__" else pick)
    ch = GLOBAL_CHOICE if pick == "__genel__" else pick
    if ch == GLOBAL_CHOICE:
        return GLOBAL_BACKEND, AI_DEFAULT
    be, err = build_backend(ch)
    if err:
        st.warning(T("backend_err", e=err))
    return be, AIHelper(be if be != "kural" else None, lang=L)


UI_TXT = {  # yeni arayüz metinleri (t2s/i18n.py deney için dondurulduğundan burada)
    "conn_short": {"tr": "Bağlantılar", "en": "Connections"},
    "conn_help": {"tr": "Model bağlantıları (Ollama, OpenAI, Claude) · {n} model", "en": "Model connections (Ollama, OpenAI, Claude) · {n} models"},
}


def UI_T(key: str, **kw) -> str:  # noqa: N802
    s = UI_TXT[key].get(L) or UI_TXT[key]["tr"]
    return s.format(**kw) if kw else s


def prov_state(prov: str) -> str:
    """'ok' | 'bad' | 'off' — sağlayıcının bu oturumdaki durumu."""
    if prov in CLOUD and not CFG.key(prov):
        return "off"
    stt = ss.conn_state.get(prov)
    if stt and not stt["ok"]:
        return "bad"
    return "ok" if (stt and stt["ok"]) else "off"


def short_model(choice: str) -> str:
    prov, mid = split_choice(choice)
    if prov == RULE:
        return "AI kapalı" if L == "tr" else "AI off"
    return mid if len(mid) <= 22 else mid[:21] + "…"


# =========================================================================== üst çubuk
# Sağ üstte (geniş ekranda başlığın içinde): model + ajan ayarları, otomasyon ilerlemesi, bağlantılar, dil, tema
choices = CFG.choices()
saved = CFG.get("secim", "genel", default=RULE)
_states = {p: prov_state(p) for p in ("ollama", "openai", "claude")}
_n_ok = sum(v == "ok" for v in _states.values())
_n_models = sum(len(CFG.models(p)) for p, v in _states.items() if v == "ok")
with st.container(key="t2s_topbar", horizontal=True, horizontal_alignment="right", vertical_alignment="center",
                  gap="small"):
    OTO.topbar_chip(L)       # arka planda süren otomasyonun canlı ilerlemesi
    with st.popover(f":material/neurology: {short_model(saved if saved in choices else RULE)}",
                    help=T("default_model_help")):
        st.markdown(f"**{T('ai_engine')}**")
        st.markdown("".join(f"<div style='font-size:.84rem;display:flex;justify-content:space-between;gap:1rem'>"
                            f"<b>{PROV_NAME[p]}</b>{status_html(p)}</div>" for p in ("ollama", "openai", "claude")),
                    unsafe_allow_html=True)
        GLOBAL_CHOICE = st.selectbox(T("default_model"), choices, index=choices.index(saved) if saved in choices else 0,
                                     format_func=model_label, key=f"global_model_{L}", help=T("default_model_help"))
        if GLOBAL_CHOICE not in choices:       # eski/bozuk durum: kural tabanlıya dön
            ss.pop(f"global_model_{L}", None)
            GLOBAL_CHOICE = saved if saved in choices else RULE
        if GLOBAL_CHOICE != saved:
            CFG.set("secim", "genel", GLOBAL_CHOICE)
            st.rerun()
        if len(choices) == 1:
            st.caption(T("no_models_yet"))
        backend, be_err = build_backend(GLOBAL_CHOICE)
        if be_err:
            st.warning(T("backend_err", e=be_err))
        if backend != "kural" and st.button(T("test_conn"), use_container_width=True, key="ping_btn"):
            with st.spinner(T("ping_wait_ollama") if GLOBAL_CHOICE.startswith("ollama|") else T("ping_wait")):
                ok, msg = backend.ping()
            (st.success if ok else st.error)(T("conn_ok", d=msg) if ok else T("conn_fail", e=msg))
        ai = AIHelper(backend if backend != "kural" else None, lang=L)
        AI_DEFAULT, GLOBAL_BACKEND = ai, backend
        status_ph = st.empty()   # AI kullanım özeti sayfanın sonunda (o turdaki tüm çağrılardan sonra) doldurulur
        st.divider()
        st.markdown(f"**{T('agent_opts')}**")
        self_correct = st.toggle(T("self_correct"), CFG.get("ajan", "kendini_duzelt", default=True), key="sc_w")
        max_att = st.slider(T("max_attempts"), 1, 5, int(CFG.get("ajan", "deneme", default=3)), disabled=not self_correct,
                            key="att_w")
        verbalize = st.toggle(T("verbalize"), ai.active, disabled=not ai.active, key=f"verb_w_{ai.active}")
        hybrid = st.toggle(T("hybrid"), CFG.get("ajan", "hibrit", default=True), disabled=not ai.active, key="hyb_w")
        CFG.set("ajan", "kendini_duzelt", self_correct)
        CFG.set("ajan", "deneme", max_att)
        CFG.set("ajan", "hibrit", hybrid)
        with st.expander(T("ai_where")):
            st.caption(T("ai_where_list"))
    if st.button(f":material/power: {UI_T('conn_short')} · {_n_ok}", key="open_conn",
                 help=UI_T("conn_help", n=_n_models)):
        connections_dialog()
    lang = st.segmented_control(T("language"), list(LANGS), format_func=lambda k: k.upper(), default=L, key="lang_w",
                                label_visibility="collapsed")
    if lang and lang != ss.lang:
        ss.lang = lang
        CFG.set("dil", lang)
        st.rerun()
    if st.button(":material/light_mode:" if DARK else ":material/dark_mode:", key="theme_btn",
                 help=T("theme_light") if DARK else T("theme_dark")):
        ss.theme = "light" if DARK else "dark"
        CFG.set("tema", ss.theme)
        st.rerun()


def make_agent():
    return Text2SQLAgent(ro_con(), backend, max_attempts=max_att, self_correct=self_correct, error_log=UI_LOG,
                         log_source="arayuz", verbalize=verbalize, lang=L, ai=ai, fallback_rule=hybrid)


def ai_box(label: str, body_md: str):
    st.markdown(f"<div class='aibox'><div class='lbl'>🤖 {html.escape(label)} · {html.escape(ai.label)}</div>"
                f"</div>", unsafe_allow_html=True)
    st.markdown(body_md)
    st.caption(T("ai_badge"))


# =========================================================================== sayfa başlıkları ve gezinme
PAGES_TXT = {   # (gezinme adı, başlık, açıklama)
    "docs": {"tr": ("Belgeler", "Belgeler ve tablo çıkarımı",
                    "PDF, HTML, CSV ya da XLSX yükle; tabloları çıkar, birleşik başlıkları ve Türkçe sayıları çözümle, "
                    "kontrol edip bilgi tabanına aktar."),
             "en": ("Documents", "Documents and table extraction",
                    "Upload PDF, HTML, CSV or XLSX; extract the tables, resolve merged headers and Turkish numbers, "
                    "check them and import them into the knowledge base.")},
    "kb": {"tr": ("Bilgi tabanı", "Bilgi tabanı",
                  "Aktarılan tablolar, meta veriler ve salt-okunur SQL konsolu. Veritabanını SQLite, Excel, CSV ya da "
                  "SQL dökümü olarak indir."),
           "en": ("Knowledge base", "Knowledge base",
                  "Imported tables, metadata and a read-only SQL console. Download the database as SQLite, Excel, CSV "
                  "or an SQL dump.")},
    "ask": {"tr": ("Soru sor", "Soru sor",
                   "Türkçe ya da İngilizce sor; ajan SQL yazar, çalıştırır, hatasını kendisi düzeltir. Cevap tablolarda "
                   "yoksa bunu açıkça söyler."),
            "en": ("Ask", "Ask",
                   "Ask in Turkish or English; the agent writes SQL, runs it and repairs its own errors. If the answer "
                   "is not in the tables, it says so plainly.")},
    "err": {"tr": ("Hata kataloğu", "Hata kataloğu",
                   "Sistemin tanıdığı hata türleri ve senin sorularında ya da değerlendirmelerde yakalanan hatalar."),
            "en": ("Error catalog", "Error catalog",
                   "The error types the system recognises and the errors caught in your questions or evaluations.")},
    "eval": {"tr": ("Değerlendirme", "Değerlendirme",
                    "Modelleri cevap düzeyindeki test kümelerinde karşılaştır ya da TR-TabloSQL kıyaslamasını çalıştır."),
             "en": ("Evaluation", "Evaluation",
                    "Compare models on answer-level test sets or run the TR-TabloSQL benchmark.")},
    "oto": {"tr": ("Otomasyon", "Otomasyon",
                   "Seçtiğin testleri arka planda sırayla çalıştırır; modeller önce sınanır, her test için Türkçe ve "
                   "İngilizce HTML/PDF rapor üretilir. Başında beklemen gerekmez."),
            "en": ("Automation", "Automation",
                   "Runs the selected tests one after another in the background; models are checked first and Turkish "
                   "and English HTML/PDF reports are written for every test. You don't have to wait.")},
    "rep": {"tr": ("Raporlar", "Raporlar",
                   "Otomasyon çalıştırmalarının geçmişi: genel raporu burada görüntüle, raporları tek tek ya da ZIP "
                   "olarak indir."),
            "en": ("Reports", "Reports",
                   "History of automation runs: view the overall report here and download reports one by one or as "
                   "a ZIP.")},
}
PAGE_ICON = {"docs": ":material/upload_file:", "kb": ":material/database:", "ask": ":material/forum:",
             "err": ":material/bug_report:", "eval": ":material/analytics:", "oto": ":material/smart_toy:",
             "rep": ":material/folder_open:"}


def card(key: str, title: str | None = None, sub: str | None = None):
    """TestAjanı'ndaki gibi çerçeveli panel."""
    c = st.container(border=True, key=f"card_{key}")
    if title:
        c.markdown(f"<div class='t2s-card-title'>{html.escape(title)}</div>"
                   + (f"<div class='t2s-card-sub'>{html.escape(sub)}</div>" if sub else ""), unsafe_allow_html=True)
    return c


def empty_state(title: str, body: str = ""):
    body = re.sub(r"\*\*(.+?)\*\*", r"<b>\1</b>", html.escape(body))
    st.markdown(f"<div class='t2s-empty'><b>{html.escape(title)}</b><span>{body}</span></div>", unsafe_allow_html=True)


def page_head(key: str):
    _, title, desc = PAGES_TXT[key].get(L, PAGES_TXT[key]["tr"])
    st.markdown(f"<div class='t2s-page'><h1>{html.escape(title)}</h1><p>{html.escape(desc)}</p></div>",
                unsafe_allow_html=True)


OTO.finished_popup(L)    # otomasyon bittiyse uyarı penceresi (yalnızca bir kez)


# =========================================================================== ① belgeler
def raw_grid_html(raw, header_rows: int) -> str:
    rows = {}
    for c in sorted(raw.cells, key=lambda c: (c.row, c.col)):
        rows.setdefault(c.row, []).append(c)
    out = ["<div class='gridwrap'><table class='grid'>"]
    for r in range(raw.n_rows):
        cls = " class='h'" if r < header_rows else ""
        tds = []
        for c in rows.get(r, []):
            attrs = (f" rowspan={c.rowspan}" if c.rowspan > 1 else "") + (f" colspan={c.colspan}" if c.colspan > 1 else "")
            m = " class='m'" if (c.rowspan > 1 or c.colspan > 1) else ""
            tds.append(f"<td{attrs}{m}>{html.escape(c.text)}</td>")
        out.append(f"<tr{cls}>{''.join(tds)}</tr>")
    out.append("</table></div>")
    return "".join(out)


def apply_ai(bt, sug: dict | None, use: bool):
    if not (sug and use):
        return bt
    if sug.get("tablo_adi"):
        bt.name = sug["tablo_adi"]
    bt.summary = sug.get("ozet") or bt.summary
    for c in bt.columns:
        s = (sug.get("sutunlar") or {}).get(c.name) or {}
        c.description = s.get("aciklama") or c.description
        if not c.unit and s.get("birim"):
            c.unit = s["birim"]
    return bt


def page_docs():
    global backend, ai
    page_head("docs")
    left, right = st.columns([1.05, 2], gap="medium")
    left = left.container(border=True, key="card_docs_cfg")
    right = right.container(border=True, key="card_docs_res")
    with left:
        st.markdown(f"<div class='t2s-card-title'>{html.escape(T('pick_doc'))}</div>", unsafe_allow_html=True)
        backend, ai = tab_model("docs")
        ups = st.file_uploader(T("upload"), type=["pdf", "html", "htm", "csv", "xlsx"], accept_multiple_files=True,
                               key="up_w")
        samples = sorted(p.name for p in DOCS_DIR.iterdir() if p.suffix in (".pdf", ".html"))
        pick = st.multiselect(T("samples"), samples, default=samples[:1], key="pick_w")
        method = st.selectbox(T("pdf_extractor"), ["auto", "pdfplumber", "camelot-lattice", "camelot-stream"],
                              help=T("pdf_extractor_help"), key="meth_w")
        mode = st.radio(T("num_mode"), ["tr", "en", "text"], horizontal=True, help=T("num_mode_help"), key="mode_w")
        enrich = st.toggle(T("ai_enrich"), True, key="enrich_w") if ai.active else False
        if st.button(T("extract"), type="primary", use_container_width=True, key="extract_btn"):
            paths = [DOCS_DIR / n for n in pick]
            tmp = Path(tempfile.mkdtemp(prefix="t2s_up_"))
            for u in ups or []:
                p = tmp / u.name
                p.write_bytes(u.getvalue())
                paths.append(p)
            res = []
            with st.spinner(T("extracting")):
                for p in paths:
                    try:
                        for rt in merge_continuations([resolve(raw) for raw in extract_any(p, method)]):
                            if len(rt.header_parts) >= 2 and rt.body:
                                res.append({"src": p.name, "rt": rt, "bt": build_table(rt, mode=mode), "ai": None})
                    except Exception as e:  # noqa: BLE001
                        st.error(f"{p.name}: {e}")
            if enrich and res:
                bar = st.progress(0.0)
                for i, it in enumerate(res, 1):
                    bar.progress(i / len(res), T("enriching", i=i, n=len(res)))
                    bt = it["bt"]
                    cols = [{"ad": c.name, "orijinal": c.original, "tip": c.sqltype, "anlam": c.semantic,
                             "birim": c.unit, "ayristirilamayan": c.unparsed} for c in bt.columns]
                    it["ai"] = ai.enrich_table(bt.caption, cols, [list(map(str, r)) for r in bt.rows[:5]],
                                               it["rt"].context or "")
                bar.empty()
                if ai.last_error:
                    st.warning(T("ai_failed", e=ai.last_error))
            ss.extracted = res
            if not res:
                st.warning(T("no_tables"))
    with right:
        if not ss.extracted:
            empty_state("Henüz tablo çıkarılmadı" if L == "tr" else "No tables extracted yet", T("extract_hint"))
        else:
            st.markdown(f"#### {T('n_tables_found', n=len(ss.extracted))}")
            use_ai = st.toggle(T("ai_apply"), True, key="useai_w") if any(it["ai"] for it in ss.extracted) else False
            if st.button(T("import_all"), key="import_all_btn"):
                c = con()
                names = []
                for it in ss.extracted:
                    bt_ = apply_ai(it["bt"], it["ai"], use_ai)
                    drop_same_source(c, bt_)
                    names.append(write_table(c, bt_))
                bump_db()
                st.success(T("imported", x=", ".join(names)))
                st.caption(T("imported_dl"))
                download_row(con(), "after_import", compact=True)
            for i, it in enumerate(ss.extracted):
                rt, bt, src, sug = it["rt"], it["bt"], it["src"], it["ai"]
                with st.expander(f"**{bt.name}** — {rt.caption or T('untitled')}", expanded=(i == 0)):
                    st.markdown(
                        f"<span class='pill'>{html.escape(src)}</span>"
                        f"<span class='pill'>{'s.' + str(rt.page) if rt.page else 'HTML'}</span>"
                        f"<span class='pill'>{rt.extractor}</span>"
                        f"<span class='pill'>{T('pill_header_rows', n=rt.n_header_rows)}</span>"
                        f"<span class='pill {'warn' if rt.merged_cells else ''}'>{T('pill_merged', n=rt.merged_cells)}</span>"
                        f"<span class='pill'>{T('pill_shape', r=len(bt.rows), c=len(bt.columns))}</span>"
                        + (f"<span class='pill ok'>{T('pill_total')}</span>" if any(
                            c.name == 'toplam_satiri' for c in bt.columns) else "")
                        + (f"<span class='pill ai'>🤖 AI</span>" if sug else ""),
                        unsafe_allow_html=True)
                    names = [T("raw_grid"), T("resolved"), T("schema")] + ([T("ai_tab")] if sug else [])
                    tabs = st.tabs(names)
                    with tabs[0]:
                        if rt.raw is not None:
                            st.markdown(raw_grid_html(rt.raw, rt.n_header_rows), unsafe_allow_html=True)
                            st.caption(T("grid_note"))
                    with tabs[1]:
                        st.dataframe(pd.DataFrame(bt.rows, columns=[c.name for c in bt.columns]),
                                     use_container_width=True, hide_index=True)
                    with tabs[2]:
                        st.dataframe(pd.DataFrame([{
                            T("col_column"): c.name, T("col_orig"): c.original, T("col_type"): c.sqltype,
                            T("col_sem"): c.semantic, T("col_unit"): c.unit or "", T("col_unparsed"): c.unparsed,
                            T("col_sample"): ", ".join(map(str, c.samples))} for c in bt.columns]),
                            use_container_width=True, hide_index=True)
                    if sug:
                        with tabs[3]:
                            st.markdown(f"<div class='aibox'><div class='lbl'>🤖 {T('ai_summary')} · "
                                        f"{html.escape(ai.label or '')}</div>{html.escape(sug.get('ozet') or '')}</div>",
                                        unsafe_allow_html=True)
                            st.markdown(f"**{T('ai_name')}:** `{sug.get('tablo_adi') or bt.name}`")
                            if sug.get("uyarilar"):
                                st.markdown(f"**{T('ai_warnings')}:**")
                                for w in sug["uyarilar"]:
                                    st.markdown(f"- ⚠️ {w}")
                            st.dataframe(pd.DataFrame([{T("col_column"): k, T("col_desc"): v.get("aciklama", ""),
                                                        T("col_unit"): v.get("birim", "")}
                                                       for k, v in (sug.get("sutunlar") or {}).items()]),
                                         use_container_width=True, hide_index=True)
                            st.caption(T("ai_badge"))
                    default_name = (sug.get("tablo_adi") if (sug and use_ai and sug.get("tablo_adi")) else bt.name)
                    nn = st.text_input(T("table_name"), default_name, key=f"nm{i}_{default_name}")
                    if st.button(T("import_one"), key=f"imp{i}"):
                        bt = apply_ai(bt, sug, use_ai)
                        bt.name = nn.strip() or bt.name
                        drop_same_source(con(), bt)
                        write_table(con(), bt)
                        bump_db()
                        st.success(T("imported_one", x=bt.name))


# =========================================================================== ② bilgi tabanı
def page_kb():
    global backend, ai
    page_head("kb")
    c = con()
    tabs_now = list_tables(c)
    with card("kb_tables"):
        h1, h2 = st.columns([1, 1.6])
        with h1:
            backend, ai = tab_model("kb")
        with h2:
            st.caption(f"{T('kb_count', n=len(tabs_now))} · `{UI_DB.relative_to(ROOT)}`")
            b1, b2 = st.columns(2)
            if b1.button(T("reset_samples"), use_container_width=True, key="reset_btn"):
                get_con.clear()
                UI_DB.unlink(missing_ok=True)
                build_database(UI_DB, mode="tr")
                bump_db()
                st.rerun()
            if b2.button(T("empty_db"), use_container_width=True, key="empty_btn"):
                get_con.clear()
                UI_DB.unlink(missing_ok=True)
                connect(UI_DB).close()
                bump_db()
                st.rerun()
        if not tabs_now:
            st.info(T("kb_empty"))
        else:
            meta = pd.read_sql(
                f"SELECT tablo AS '{T('meta_table')}', baslik AS '{T('meta_caption')}', kaynak AS '{T('meta_source')}', "
                f"sayfa AS '{T('meta_page')}', birim AS '{T('meta_unit')}', satir_sayisi AS '{T('meta_rows')}', "
                f"sutun_sayisi AS '{T('meta_cols')}', birlesik_hucre AS '{T('meta_merged')}', "
                f"cikarici AS '{T('meta_extractor')}', sayi_kipi AS '{T('meta_mode')}', ozet AS '{T('meta_summary')}' "
                "FROM _tablolar", c)
            if meta[T("meta_summary")].isna().all():
                meta = meta.drop(columns=[T("meta_summary")])
            st.dataframe(meta, use_container_width=True, hide_index=True)

            st.markdown(f"#### {T('export_title')}")
            st.caption(T("export_help"))
            download_row(c, "kb")

    if tabs_now:
        l, r = st.columns([1, 1.4], gap="medium")
        l = l.container(border=True, key="card_kb_cols")
        r = r.container(border=True, key="card_kb_data")
        with l:
            tsel = st.selectbox(T("table"), tabs_now, key="kb_table")
            summ = c.execute("SELECT ozet, baslik FROM _tablolar WHERE tablo=?", (tsel,)).fetchone()
            if summ and summ[0]:
                st.markdown(f"<div class='aibox'><div class='lbl'>{T('meta_summary')}</div>{html.escape(summ[0])}</div>",
                            unsafe_allow_html=True)
            elif ai.active and st.button(T("ai_describe"), key=f"desc_{tsel}"):
                with st.spinner("🤖"):
                    d = ai.describe_table(summ[1] if summ else tsel, describe_schema(c, [tsel]))
                if d:
                    set_table_summary(c, tsel, d)
                    bump_db()
                    st.rerun()
                else:
                    st.warning(T("ai_failed", e=ai.last_error or "-"))
            cols = pd.read_sql(f"SELECT sutun AS '{T('col_column')}', orijinal_baslik AS '{T('col_orig')}', "
                               f"sql_tipi AS '{T('col_type')}', anlam AS '{T('col_sem')}', birim AS '{T('col_unit')}', "
                               f"aciklama AS '{T('col_desc')}' FROM _sutunlar WHERE tablo=? ORDER BY sira", c,
                               params=(tsel,))
            if cols[T("col_desc")].isna().all():
                cols = cols.drop(columns=[T("col_desc")])
            st.dataframe(cols, use_container_width=True, hide_index=True)
            d1, d2, d3 = st.columns(3)
            d1.download_button("⬇️ CSV", lambda tsel=tsel: _ro(lambda c: table_csv_bytes(c, tsel)), f"{tsel}.csv", "text/csv", key=f"tcsv_{tsel}",
                               help=T("dl_table"), use_container_width=True)
            d2.download_button("⬇️ XLSX", lambda tsel=tsel: _ro(lambda c: xlsx_bytes(c, [tsel])), f"{tsel}.xlsx",
                               "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", key=f"txlsx_{tsel}",
                               help=T("dl_table"), use_container_width=True)
            if d3.button(T("delete_table"), key=f"del_{tsel}", use_container_width=True):
                c.execute(f'DROP TABLE IF EXISTS "{tsel}"')
                c.execute("DELETE FROM _tablolar WHERE tablo=?", (tsel,))
                c.execute("DELETE FROM _sutunlar WHERE tablo=?", (tsel,))
                c.commit()
                bump_db()
                st.rerun()
        with r:
            st.dataframe(pd.read_sql(f'SELECT * FROM "{tsel}"', c), use_container_width=True, hide_index=True, height=330)
        with card("kb_sql"):
            st.markdown(f"#### {T('sql_console')}")
            q = st.text_area("SQL", f'SELECT * FROM "{tabs_now[0]}" LIMIT 5', height=90, help=T("sql_help"), key="sql_w")
            if st.button(T("run"), key="sql_run"):
                if not q.strip().lower().startswith(("select", "with")):
                    st.error(T("select_only"))
                else:
                    try:
                        cols_, rows_ = _exec(ro_con(), q)
                        st.dataframe(pd.DataFrame(rows_, columns=cols_), use_container_width=True, hide_index=True)
                    except Exception as e:  # noqa: BLE001
                        st.error(str(e))


# =========================================================================== ③ soru sor
STATUS_PILL = {"cevap": ("ok", "st_answer"), "cevapsiz": ("warn", "st_abstain"),
               "bulunamadi": ("warn", "st_notfound"), "hata": ("bad", "st_error")}
STEP_ICON = {"sema": "🧭", "analiz": "🔎", "uretim": "✍️", "uyari": "⚠️", "hata": "⛔", "geribildirim": "🔁",
             "sonuc": "▶️", "cevap": "✅", "cevapsiz": "🚫", "ai": "🤖", "geridusme": "↩️"}


def step_title(s: dict) -> str:
    tur, asama = s["tur"], s.get("asama")
    tip = s.get("hata_tipi", "")
    if tur == "uretim":
        return T("step_uretim", k=s.get("k", "?"))
    if tur in ("uyari", "hata") and asama == "statik":
        return T("step_uyari_statik", tip=tip)
    if tur == "uyari" and asama == "sonuc":
        return T("step_uyari_sonuc", tip=tip)
    if tur == "hata" and asama == "yurutme":
        return T("step_hata_yurutme", tip=tip)
    if tur == "hata" and asama == "model":
        return T("step_hata_model")
    if tur == "sonuc":
        return T("step_sonuc", n=s.get("n", "?"))
    key = {"sema": "step_sema", "analiz": "step_analiz", "geribildirim": "step_geribildirim", "cevap": "step_cevap",
           "cevapsiz": "step_cevapsiz", "ai": "step_ai", "geridusme": "step_fallback"}.get(tur)
    return T(key) if key else s.get("baslik", tur)


def step_body(s: dict) -> str:
    if s["tur"] == "sema" and s.get("tablolar") is not None:
        return T("schema_linked", n=len(s["tablolar"]), x=", ".join(s["tablolar"]))
    if s["tur"] == "geribildirim":
        return T("feedback_note")
    return str(s.get("icerik", ""))


def show_result(res):
    kind, key = STATUS_PILL.get(res.status, ("", res.status))
    pills = (f"<span class='pill {kind}'>{T(key)}</span><span class='pill'>{T('attempts', n=res.attempts)}</span>"
             f"<span class='pill {'ai' if ':' in res.backend else ''}'>{html.escape(res.backend)}</span>"
             f"<span class='pill'>{T('seconds', s=f'{res.duration:.2f}')}</span>")
    if getattr(res, "fallback", None):
        pills += f"<span class='pill warn'>↩️ {T('fallback_used')}</span>"
    st.markdown(pills, unsafe_allow_html=True)
    st.markdown(f"<div class='answer'>{html.escape(res.answer)}</div>", unsafe_allow_html=True)
    if getattr(res, "short_answer", ""):
        st.markdown(f"<div class='answer-sub'>{html.escape(res.short_answer)}</div>", unsafe_allow_html=True)
    if res.rows and (len(res.rows) > 1 or len(res.columns) > 1):
        df = pd.DataFrame(res.rows, columns=res.columns)
        st.dataframe(df.style.format(lambda v: format_num(v, L) if isinstance(v, (int, float)) else v),
                     use_container_width=True, hide_index=True)
    if res.sql:
        st.code(res.sql, language="sql")
    with st.expander(T("agent_steps")):
        for s in res.steps:
            st.markdown(f"<div class='step {s['tur']}'><b>{STEP_ICON.get(s['tur'], '•')} {html.escape(step_title(s))}</b>"
                        f"<div>{html.escape(step_body(s)[:1200])}</div></div>", unsafe_allow_html=True)
            if s.get("sql") and s["tur"] == "uretim":
                st.code(s["sql"], language="sql")
            if s.get("sema"):
                with st.popover(T("model_schema")):
                    st.code(s["sema"], language="sql")


STATIC_Q = ["İzmir'in 2024 elektrik tüketimi ne kadar?", "2024'te en çok elektrik tüketen il hangisi?",
            "Faiz giderlerinin bütçesi 2023'ten 2024'e yüzde kaç arttı?", "Ankara'nın 2024 elektrik tüketimi kaç MWh?",
            "Yürürlük tarihi 15.03.2025 olan hizmetlerin ortalama ücreti nedir?", "Mersin'in 2024 elektrik tüketimi ne kadar?"]

def page_ask():
    global backend, ai
    page_head("ask")
    left, right = st.columns([1, 2.1], gap="medium")
    with left.container(border=True, key="card_ask_cfg"):
        backend, ai = tab_model("ask")
        examples, ai_ex = STATIC_Q, False
        if ai.active and list_tables(con()):
            ck = f"{ss.db_version}|{L}|{ai.label}|{ss.q_seed}"
            if ss.get("ai_q_key") != ck:
                with st.spinner("🤖"):
                    ss.ai_q = ai.suggest_questions(describe_schema(con()), 6, ss.q_seed)
                ss.ai_q_key = ck
            if ss.get("ai_q"):
                examples, ai_ex = ss.ai_q, True
        st.markdown(f"<div class='t2s-card-title' style='margin-top:.4rem'>"
                    f"{html.escape(T('ai_examples') if ai_ex else T('examples'))}</div>", unsafe_allow_html=True)
        if not ai.active and L == "en":
            st.caption(T("ask_rule_note"))
        clicked = None
        for i, q in enumerate(examples):
            if st.button(q, key=f"ex{i}_{q[:20]}", use_container_width=True):
                clicked = q
        c1, c2 = st.columns(2)
        if ai_ex and c1.button(T("regen_examples"), key="regen_q", use_container_width=True):
            ss.q_seed += 1
            st.rerun()
        if ss.chat and c2.button(T("clear_chat"), key="clear_chat", use_container_width=True):
            ss.chat = []
            st.rerun()
    q = st.chat_input(T("ask_placeholder")) or clicked
    with right.container(border=True, key="card_ask_chat"):
        if q:
            with st.spinner(T("thinking")):
                res = make_agent().run(q)
            ss.chat.append({"q": q, "res": res})
        if not ss.chat:
            empty_state("Henüz soru sorulmadı" if L == "tr" else "No questions yet",
                        "Aşağıya bir soru yazın ya da soldaki örneklerden birine tıklayın. Ajan SQL yazar, "
                        "çalıştırır, hatasını düzeltir; cevap tablolarda yoksa bunu söyler." if L == "tr" else
                        "Type a question below or click an example on the left. The agent writes SQL, runs it and "
                        "repairs it; if the answer is not in the tables, it says so.")
        for m in reversed(ss.chat):
            with st.chat_message("user"):
                st.markdown(m["q"])
            with st.chat_message("assistant"):
                show_result(m["res"])


# =========================================================================== ④ hata kataloğu
def page_err():
    global backend, ai
    page_head("err")
    st.markdown(f"#### {T('err_types')}")
    CAT = catalog(L)
    keys = list(CAT)
    for row in range(0, len(keys), 3):
        cc = st.columns(3)
        for k, col in zip(keys[row:row + 3], cc):
            v = CAT[k]
            col.markdown(f"<div class='card'><h4>{html.escape(v['ad'])}</h4><code>{k}</code>"
                         f"<p>{html.escape(v['tanim'])}</p><p><b>{T('example')}:</b> <code>{html.escape(v['ornek'])}</code></p>"
                         f"<p><b>{T('remedy')}:</b> {html.escape(v['cozum'])}</p></div>", unsafe_allow_html=True)
        st.write("")
    st.markdown(f"#### {T('logged_errors')}")
    m1, _m2 = st.columns([1, 1.6])
    with m1:
        backend, ai = tab_model("err")
    src = st.radio(T("source"), ["ui", "eval"], horizontal=True, key="err_src",
                   format_func=lambda k: T("src_ui" if k == "ui" else "src_eval"))
    recs = UI_LOG.read() if src == "ui" else ErrorLog(EVAL_LOG).read()
    if not recs:
        st.info(T("no_records"))
    else:
        df = pd.DataFrame(recs)
        cnt = df["tip"].value_counts().rename_axis("tip").reset_index(name=T("count"))
        a, b = st.columns([1, 2])
        a.dataframe(cnt, use_container_width=True, hide_index=True)
        b.bar_chart(cnt.set_index("tip"), horizontal=True, height=260)
        tsel = st.multiselect(T("filter_type"), sorted(df["tip"].unique()), key="err_filter")
        view = df[df["tip"].isin(tsel)] if tsel else df
        st.dataframe(view[[c for c in ["zaman", "tip", "asama", "soru", "mesaj", "sql", "oneriler", "kaynak"]
                           if c in view.columns]].iloc[::-1], use_container_width=True, hide_index=True)
        if ai.active:
            recent = view.iloc[::-1].head(40).to_dict("records")
            idx = st.selectbox(T("explain_pick"), range(len(recent)), key="err_pick",
                               format_func=lambda i: f"{recent[i].get('zaman', '')} · {recent[i]['tip']} · "
                                                     f"{str(recent[i].get('soru', ''))[:70]}")
            if st.button(T("explain_btn"), key="explain_btn"):
                with st.spinner("🤖"):
                    ex = ai.explain_error(recent[idx])
                if ex:
                    ai_box(T("explain_btn").replace("🤖 ", ""), ex)
                else:
                    st.warning(T("ai_failed", e=ai.last_error or "-"))
        else:
            st.caption(T("explain_off"))


# =========================================================================== ⑤ değerlendirme
def compare_section():
    """Birden çok modeli aynı cevap düzeyindeki test kümesinde karşılaştırır."""
    from t2s.qa_eval import SETS, run_set
    st.markdown(f"#### {T('cmp_title')}")
    st.caption(T("cmp_help"))
    avail = [k for k in ("agir_hizli", "agir", "stres", "pilot") if SETS[k].available()]
    if not avail:
        st.info(T("cmp_no_sets"))
        return
    nq = {k: len(SETS[k].questions()) for k in avail}
    a, b = st.columns([1.3, 2])
    set_key = a.selectbox(T("cmp_set"), avail, format_func=lambda k: T(SET_KEYS[k], n=nq[k]), key=f"cmp_set_{L}")
    choices = CFG.choices()
    saved = [c for c in (CFG.get("secim", "sekmeler", "eval_models") or []) if c in choices]
    picks = b.multiselect(T("cmp_models"), choices, default=saved or [GLOBAL_CHOICE], format_func=model_label,
                          key=f"cmp_models_{L}")
    if any(p not in choices for p in picks):
        ss.pop(f"cmp_models_{L}", None)
        picks = [p for p in picks if p in choices] or [GLOBAL_CHOICE]
    if picks != saved:
        CFG.set("secim", "sekmeler", "eval_models", picks)
    c1, c2, c3 = st.columns([1.2, 1.4, 1])
    limit = c1.number_input(T("cmp_limit"), 0, nq[set_key], 0, step=10, key="cmp_limit", help=T("cmp_limit_help"))
    dev = None
    if any(p.startswith("ollama|") for p in picks):
        dev = c2.segmented_control(T("device_title"), ["auto", "gpu", "cpu"],
                                   default=CFG.conn("ollama").get("cihaz", "auto"), format_func=lambda k: T(DEV_KEYS[k]),
                                   key=f"cmp_dev_{L}")
    n_run = (limit or nq[set_key])
    n_llm = sum(1 for p in picks if p != RULE)
    if n_llm:
        st.caption(T("cmp_cost", n=n_run, m=n_llm, tok=format_num(round(n_run * 4.5), L)))
    go = c3.button(T("cmp_run"), type="primary", use_container_width=True, key="cmp_go", disabled=not picks)
    base = OUT / "qa" / set_key
    if go:
        bar = st.progress(0.0, T("starting"))
        for i, ch in enumerate(picks):
            be, err = build_backend(ch, device=dev)
            if err:
                st.error(f"{model_label(ch)}: {err}")
                continue
            lab = model_label(ch)
            tag = ch.replace("|", "_").replace(":", "_").replace("/", "_")

            def prog(k, n, r, lab=lab, i=i):
                bar.progress((i + k / n) / len(picks), f"{lab} · {k}/{n} · {r['id']}")
            with st.spinner(T("cmp_building")):
                run_set(SETS[set_key], be, base / tag, progress=prog, limit=limit or None,
                        max_attempts=max_att, self_correct=self_correct)
        bar.empty()
    # ---- kayıtlı sonuçlar
    results = []
    if base.exists():
        for p in sorted(base.glob("*/sonuclar.json")):
            try:
                d = json.loads(p.read_text(encoding="utf-8"))
                results.append((p.parent.name, d))
            except Exception:  # noqa: BLE001
                pass
    if not results:
        st.info(T("cmp_none"))
        return
    rows = []
    for tag, d in results:
        o = d["ozet"]
        lv = o.get("seviyeye_gore", {})
        name = o.get("arka_uc", tag)
        r = {T("cmp_col_model"): T("prov_rule") if name in ("kural", "kural_tabanli") else name, T("cmp_col_n"): o["soru"], T("cmp_col_acc"): o["basari"]}
        for k in ("L1", "L2", "L3", "L4", "L5"):
            if k in lv:
                r[k] = lv[k]["basari"]
        r.update({T("cmp_col_abst"): o.get("cevapsiz_dogru_red"), T("cmp_col_silent"): o.get("sessiz_yanlis"),
                  T("cmp_col_tok"): o.get("girdi_belirteci", 0) + o.get("cikti_belirteci", 0),
                  T("cmp_col_time"): o.get("sure_sn"), T("cmp_col_when"): o.get("zaman", "")})
        rows.append(r)
    df = pd.DataFrame(rows)
    order = [T("cmp_col_model"), T("cmp_col_n"), T("cmp_col_acc")] + [c for c in ("L1", "L2", "L3", "L4", "L5")
                                                                      if c in df.columns]
    df = df[order + [c for c in df.columns if c not in order]].sort_values(T("cmp_col_acc"), ascending=False)
    st.dataframe(df.astype(object).where(df.notna(), "—"), use_container_width=True, hide_index=True)
    lvl = [c for c in ("L1", "L2", "L3", "L4", "L5") if c in df.columns]
    if lvl:
        st.markdown(f"**{T('cmp_by_level')}**")
        st.bar_chart(df.set_index(T("cmp_col_model"))[lvl].T, height=260, stack=False)
    st.caption(T("cmp_levels"))
    tags = [t_ for t_, _ in results]
    pick = st.selectbox(T("detail"), tags, key="cmp_detail",
                        format_func=lambda t_: next(d["ozet"].get("arka_uc", t_) for x, d in results if x == t_))
    recs = next(d["kayitlar"] for x, d in results if x == pick)
    rd = pd.DataFrame(recs)
    f1, f2 = st.columns(2)
    only_bad = f1.toggle(T("only_wrong"), True, key="cmp_bad")
    lv_f = f2.multiselect(T("cmp_level_filter"), sorted(rd["seviye"].dropna().unique()) if "seviye" in rd else [],
                          key="cmp_lv")
    view = rd[~rd["dogru"]] if only_bad else rd
    if lv_f:
        view = view[view["seviye"].isin(lv_f)]
    cols = [c for c in ("id", "seviye", "dil", "aile", "soru", "cevap", "dogru", "aciklama", "durum", "sql") if c in view]
    st.dataframe(view[cols], use_container_width=True, hide_index=True)
    ozet_pick = next(d["ozet"] for x, d in results if x == pick)
    st.download_button(T("cmp_dl"), lambda o=ozet_pick, r=recs: json.dumps({"ozet": o, "kayitlar": r}, ensure_ascii=False,
                                                                           indent=1, default=str),
                       f"sonuclar_{set_key}_{pick}.json", "application/json", key="cmp_dl_btn")


def page_eval():
    global backend, ai
    page_head("eval")
    with card("eval_cmp"):
        compare_section()
    with card("eval_bench"):
        ozet_p = OUT / "ozet.json"
        st.markdown(f"#### {T('bench_title')}")
        backend, ai = tab_model("eval")
        bench = json.loads((ROOT / "benchmark" / "tr_tablosoru.json").read_text(encoding="utf-8"))["sorular"]
        bdf = pd.DataFrame(bench)
        m1, m2, m3, m4 = st.columns(4)
        m1.metric(T("m_questions"), len(bdf))
        m2.metric(T("m_heldout"), int((bdf["bolum"] == "test").sum()))
        m3.metric(T("m_unans"), int((~bdf["cevaplanabilir"]).sum()))
        m4.metric(T("m_cats"), len({c for k in bdf["kategoriler"] for c in k}))
        with st.expander(T("show_q")):
            st.dataframe(bdf[["id", "bolum", "soru", "zorluk", "kategoriler", "kanit", "altin_sql"]],
                         use_container_width=True, hide_index=True)

        st.markdown(f"#### {T('run_eval')}")
        cA, cB, cC = st.columns([1.2, 1.2, 1])
        modes = cA.multiselect(T("modes"), ["tr", "en", "text"], default=["tr"], key="ev_modes")
        split = cB.selectbox(T("split"), ["all", "gelistirme", "test"], key="ev_split",
                             format_func=lambda k: T("split_all") if k == "all" else k)
        run = cC.button(T("run_with"), type="primary", use_container_width=True, key="ev_run")
        if run:
            from t2s.evaluate import SystemSpec, evaluate_system, load_benchmark
            from t2s.pipeline import build_reference_db
            items = [it for it in load_benchmark() if split == "all" or it["bolum"] == split]
            build_reference_db(OUT / "db" / "referans.db")
            dbp = {}
            for md in modes:
                dbp[md] = OUT / "db" / f"sistem_{md}.db"
                if not dbp[md].exists():
                    build_database(dbp[md], mode=md)
            results = []
            bar = st.progress(0.0, T("starting"))
            for md in modes:
                nm = ("kural" if backend == "kural" else backend.name.replace(":", "_").replace("/", "_")) + f"_{md}_arayuz"
                spec = SystemSpec(nm, backend, md, self_correct, True, max_att)
                s = evaluate_system(spec, items, dbp, OUT / "db" / "referans.db", OUT / "sonuclar",
                                    progress=lambda k, n, r: bar.progress(k / n, f"{md}: {k}/{n} · {r['id']}"))
                results.append(s)
            bar.empty()
            ss.last_eval = results
        if ss.get("last_eval"):
            st.dataframe(pd.DataFrame([{k: s[k] for k in ("sistem", "sayi_kipi", "n", "EX", "EX_ilk_deneme",
                                                           "cevapsiz_dogru_red", "yanlis_red_orani", "ort_deneme")}
                                       for s in ss.last_eval]), use_container_width=True, hide_index=True)
            pick_s = st.selectbox(T("detail"), [s["sistem"] for s in ss.last_eval], key="ev_detail")
            p = OUT / "sonuclar" / f"sonuclar_{pick_s}.jsonl"
            recs = [json.loads(x) for x in p.read_text(encoding="utf-8").splitlines()]
            rd = pd.DataFrame(recs)
            only_bad = st.toggle(T("only_wrong"), True, key="ev_bad")
            st.dataframe((rd[~rd["dogru"]] if only_bad else rd)[
                ["id", "soru", "dogru", "hata_tipi", "durum", "cevap", "tahmin_sql", "altin_sql", "deneme"]],
                use_container_width=True, hide_index=True)
            if ai.active and st.button(T("ai_eval"), key="ai_eval_btn"):
                with st.spinner("🤖"):
                    summ = ai.summarize_failures(recs)
                if summ:
                    ai_box(T("ai_eval").replace("🤖 ", ""), summ)
                else:
                    st.info(ai.last_error or "—")

        st.markdown(f"#### {T('last_full')}")
        if ozet_p.exists():
            oz = json.loads(ozet_p.read_text(encoding="utf-8"))
            sdf = pd.DataFrame([{"sistem": s["sistem"], "kip": s["sayi_kipi"], "EX": s["EX"],
                                 "gelistirme": s["bolum"].get("gelistirme"), "test": s["bolum"].get("test"),
                                 "ilk deneme / first try": s["EX_ilk_deneme"], "cevapsız→red": s["cevapsiz_dogru_red"]}
                                for s in oz["sistemler"]])
            st.dataframe(sdf, use_container_width=True, hide_index=True)
            x, y = st.columns(2)
            with x:
                st.markdown(f"**{T('sys_ex')}**")
                st.bar_chart(sdf.set_index("sistem")["EX"], horizontal=True, height=230)
            with y:
                st.markdown(T("gold_by_mode"))
                st.bar_chart(pd.DataFrame(oz["sayi_ablasyonu"]).set_index("kip")["EX"], horizontal=True, height=230)
            rp = OUT / "basarim_raporu.html"
            if rp.exists():
                st.download_button(T("dl_report"), lambda rp=rp: rp.read_bytes(), "basarim_raporu.html", "text/html", key="dl_rep")
        else:
            st.info(T("no_full"))




# =========================================================================== ⑥ otomasyon
def page_oto():
    page_head("oto")
    OTO.render(L)

# =========================================================================== ⑦ raporlar
def page_rep():
    page_head("rep")
    OTO.render_reports(L)


# =========================================================================== gezinme (üstte)
NAV.run()


# =========================================================================== AI durum özeti (en son)
ai = AI_DEFAULT
if ai.active:
    status_ph.markdown(f"<div class='status'><span class='pill ai'>● {T('ai_status_on')}</span><b>{html.escape(ai.label)}</b>"
                       f"<span style='opacity:.7'>{T('ai_usage', c=USAGE.calls, i=format_num(USAGE.input_tokens, L), o=format_num(USAGE.output_tokens, L))}"
                       f"</span></div>", unsafe_allow_html=True)
else:
    status_ph.markdown(f"<div class='status'><span class='pill'>○ {T('ai_status_off')}</span></div>", unsafe_allow_html=True)