# server.py
from __future__ import annotations

import logging
import json
import re
import calendar
import time
from datetime import datetime, date, timedelta
from pathlib import Path
from typing import Optional, List, Dict, Any, Tuple
from collections import defaultdict

import requests
from fastapi import FastAPI, Request, Query, Form, HTTPException
from fastapi.responses import HTMLResponse, RedirectResponse
from starlette.templating import Jinja2Templates

from app.services.supabase_service import get_client
from app.sync2.pipeline import apply_decisions  # 결정 적용

# -------------------------------------------------
# App & Templates
# -------------------------------------------------
app = FastAPI(title="GovBot Web")
logger = logging.getLogger("govbot.web")
TEMPLATE_DIR = Path(__file__).resolve().parents[2] / "templates"
templates = Jinja2Templates(directory=str(TEMPLATE_DIR))
sb = get_client()

# -------------------------------------------------
# Constants
# -------------------------------------------------
COMPANY_ORDER = [
    "한국전력공사", "한국남동발전", "한국중부발전", "한국서부발전", "한국남부발전",
    "한국동서발전", "한국수력원자력", "한전KPS", "한전KDN", "한국전력기술", "한전원자력연료",
]
POS_ORDER = ["상임기관장", "상임감사", "상임이사", "비상임이사"]
TODAY = date.today()

# -------------------------------------------------
# Generic utils
# -------------------------------------------------
def _current_map_by_person_source():
    rows = sb.table("gov_staff_current").select("*").execute().data or []
    return {(r["person_id"], r["source"]): r for r in rows}

def _person_name_map(person_ids: list[int]) -> dict[int, str]:
    if not person_ids:
        return {}
    uniq = sorted(set([int(x) for x in person_ids if x is not None]))
    rows = sb.table("person_registry").select("person_id,canonical_name").in_("person_id", uniq).execute().data or []
    return {r["person_id"]: (r.get("canonical_name") or str(r["person_id"])) for r in rows}

_EVENT_KO = {
    "NEW": "입사(신규)",
    "DEPARTED": "퇴사",
    "MOVE": "부서이동",
    "CHANGE_POSITION": "직위변경",
    "CHANGE_PHONE": "전화변경",
    "CHANGE_NAME": "개명(권장: 퇴사/입사로 처리)",
}
def _ev_label(t: str) -> str:
    return _EVENT_KO.get(t, t)

def _group_count(items, key):
    c = defaultdict(int)
    for it in items:
        c[it.get(key)] += 1
    return dict(c)

def _as_list(val) -> List[str]:
    if val is None:
        return []
    if isinstance(val, list):
        return [str(x).strip() for x in val if str(x).strip()]
    if isinstance(val, str):
        s = val.strip()
        if not s or s.lower() == "none":
            return []
        if s.startswith("[") and s.endswith("]"):
            try:
                v = json.loads(s)
                if isinstance(v, list):
                    return [str(x).strip() for x in v if str(x).strip()]
            except Exception:
                pass
        if "\n" in s:
            return [t.strip() for t in s.splitlines() if t.strip()]
        if "|" in s:
            return [t.strip() for t in s.split("|") if t.strip()]
        return [s]
    return [str(val).strip()]

def _normalize_multiline(val) -> str:
    arr = _as_list(val)
    return "\n".join(arr) if arr else "-"

def _to_int_safe(v) -> int:
    try:
        return int(v)
    except Exception:
        return 10**12  # 큰 값으로 뒤로 밀기

def _pack_row_for_table(r: dict | None) -> dict:
    if not r:
        return {"source": "-", "department": "-", "position": "-", "name": "-", "phone": "-", "task": "-"}
    def _s(x): return (x or "").strip() or "-"
    return {
        "source": _s(r.get("source")),
        "department": _s(r.get("department")),
        "position": _s(r.get("position")),
        "name": _s(r.get("name")),
        "phone": _s(r.get("phone")),
        "task": _s(r.get("task")),
    }

# -------------------------------------------------
# Company / Position helpers
# -------------------------------------------------
def _norm_company_label(dep: str | None) -> str | None:
    if not dep:
        return None
    s = dep.strip()
    s0 = re.sub(r"(?:\(주\)|㈜|주식회사|\s+)", "", s, flags=re.I)
    sU = s0.upper()

    for lbl in COMPANY_ORDER:
        if lbl.replace(" ", "") in s0:
            return lbl
    if "KPS" in sU: return "한전KPS"
    if "KDN" in sU: return "한전KDN"
    if "KEPCO" in sU or "한국전력" in s0 or "한전" in s0: return "한국전력공사"
    if "남동발전" in s0: return "한국남동발전"
    if "중부발전" in s0: return "한국중부발전"
    if "서부발전" in s0: return "한국서부발전"
    if "남부발전" in s0: return "한국남부발전"
    if "동서발전" in s0: return "한국동서발전"
    if "수력원자력" in s0: return "한국수력원자력"
    if "전력기술" in s0: return "한국전력기술"
    if "원자력연료" in s0: return "한전원자력연료"
    return None

def _normalize_position(pos: str | None) -> Optional[str]:
    if not pos:
        return None
    p = pos.strip()
    p_no_space = p.replace(" ", "")
    if "비상임" in p:
        return "비상임이사"
    if "상임감사" in p_no_space:
        return "상임감사"
    if "감사위원" in p and "비상임" not in p:
        return "상임감사"
    if "상임이사" in p:
        return "상임이사"
    if p in POS_ORDER:
        return p
    return None

# -------------------------------------------------
# Date helpers
# -------------------------------------------------
_DATE_PATTERNS: List[Tuple[re.Pattern, str]] = [
    (re.compile(r"^\s*(\d{4})[.\-\/년\s]*(\d{1,2})[.\-\/월\s]*(\d{1,2})[일\s]*\s*$"), "ymd"),
    (re.compile(r"^\s*(\d{4})[.\-\/년\s]*(\d{1,2})[.\-\/월\s]*\s*$"), "ym"),
    (re.compile(r"^\s*(\d{4})\s*$"), "y"),
]
def _parse_date_parts(s: str | None) -> Tuple[Optional[int], Optional[int], Optional[int]]:
    if not s:
        return None, None, None
    t = str(s).strip()
    for pat, kind in _DATE_PATTERNS:
        m = pat.match(t)
        if m:
            y = int(m.group(1))
            if kind == "ymd": return y, int(m.group(2)), int(m.group(3))
            if kind == "ym":  return y, int(m.group(2)), None
            if kind == "y":   return y, None, None
    try:
        d = datetime.fromisoformat(t[:10]).date()
        return d.year, d.month, d.day
    except Exception:
        return None, None, None

def _parts_to_date(y: Optional[int], m: Optional[int], d: Optional[int], assume_last: bool) -> Optional[date]:
    if y is None:
        return None
    if m is None:
        m = 12 if assume_last else 1
    if d is None:
        d = calendar.monthrange(y, m)[1] if assume_last else 1
    try:
        return date(y, m, d)
    except Exception:
        return None

def _fmt_date_display(y: Optional[int], m: Optional[int], d: Optional[int]) -> str:
    if y is None:
        return "-"
    mm = f"{m:02d}" if m else "??"
    dd = f"{d:02d}" if d else "??"
    return f"{y}-{mm}-{dd}"

def _date_display_and_compare(raw: Any) -> tuple[str, Optional[date]]:
    if raw is None:
        return "-", None
    s = str(raw).strip()
    if not s or s.lower() == "none":
        return "-", None
    y, m, d = _parse_date_parts(s)
    disp = _fmt_date_display(y, m, d)
    cmp_d = _parts_to_date(y, m, d, assume_last=True)
    return disp, cmp_d

# -------------------------------------------------
# Gender / career helpers
# -------------------------------------------------
def _gender_label(g: Any) -> str:
    s = str(g or "").strip().lower()
    if not s: return "-"
    if s in {"f", "female", "여", "여성", "여자", "女"}: return "여"
    if s in {"m", "male", "남", "남성", "남자", "男"}: return "남"
    return str(g)

def _is_female(g: Any) -> bool:
    return _gender_label(g) == "여"

_KEPCO_WORD_RE = re.compile(r"(?<![가-힣A-Za-z0-9])한전(?![가-힣A-Za-z0-9])")
def _is_kepco_alum_text(career: Any) -> bool:
    if not career: return False
    t = str(career)
    if "한국전력공사" in t: return True
    if _KEPCO_WORD_RE.search(t): return True
    return False

def _ping_status(url: str, timeout: float = 3.0) -> dict:
    try:
        t0 = time.time()
        r = requests.get(url, timeout=timeout, headers={"User-Agent": "govbot/1.0"})
        ms = int((time.time() - t0) * 1000)
        if 200 <= r.status_code < 400:
            return {"label": ("OK" if ms < 1500 else "지연"), "ms": ms}
        return {"label": "불량", "ms": ms}
    except Exception:
        return {"label": "불량", "ms": None}

def _count_last_24h(table: str) -> int:
    cutoff = datetime.utcnow() - timedelta(hours=24)
    try:
        res = sb.table(table).select("id,created_at").gte("created_at", cutoff.isoformat()).execute()
        return len(res.data or [])
    except Exception:
        try:
            res = sb.table(table).select("*").order("id", desc=True).limit(1000).execute()
            data = res.data or []
        except Exception:
            return 0
        cnt = 0
        for r in data:
            ts = r.get("created_at") or r.get("posted_at")
            if not ts:
                continue
            dt = None
            s = str(ts).strip()
            try:
                dt = datetime.fromisoformat(s[:19])
            except Exception:
                try:
                    dt = datetime.strptime(s[:10], "%Y-%m-%d")
                except Exception:
                    dt = None
            if dt and dt >= cutoff:
                cnt += 1
        return cnt

def _today_items_from(table: str, source_label: str, title_key: str = "title",
                      url_key: str = "url", tag_key: Optional[str] = "tag",
                      limit_scan: int = 150) -> List[Dict[str, Any]]:
    today_str = date.today().isoformat()
    try:
        res = sb.table(table).select("*").order("id", desc=True).limit(limit_scan).execute()
        rows = res.data or []
    except Exception:
        rows = []
    items: List[Dict[str, Any]] = []
    for r in rows:
        d = str(r.get("posted_at") or r.get("created_at") or "")[:10]
        if d == today_str:
            items.append({
                "src": source_label,
                "title": r.get(title_key) or "-",
                "tag": (r.get(tag_key) if tag_key else None),
                "url": r.get(url_key),
                "date": d,
            })
    return items

# -------------------------------------------------
# Routes: 홈/검색/타임라인/그룹
# -------------------------------------------------
@app.get("/", response_class=HTMLResponse)
async def index(request: Request):
    moef_today = _today_items_from("moef_id", "MOEF", title_key="title", url_key="url", tag_key="tag")
    motie_today = _today_items_from("motie_id", "MOTIE", title_key="title", url_key="url", tag_key=None)
    gov_today = moef_today + motie_today

    try:
        res_k = sb.table("kepco_id").select("department,posted_at,pdf_url").order("id", desc=True).limit(200).execute()
        k_rows = res_k.data or []
    except Exception:
        k_rows = []

    group_today = []
    today_str = date.today().isoformat()
    for r in k_rows:
        d = str(r.get("posted_at") or r.get("created_at") or "")[:10]
        if d == today_str:
            group_today.append({
                "department": r.get("department") or "-",
                "url": r.get("pdf_url"),
                "date": d,
            })

    status = {
        "MOEF": {"ping": _ping_status("https://www.moef.go.kr"), "count24": _count_last_24h("moef_id")},
        "MOTIE": {"ping": _ping_status("https://www.motie.go.kr"), "count24": _count_last_24h("motie_id")},
        "ALIO": {"ping": _ping_status("https://www.alio.go.kr"), "count24": _count_last_24h("kepco_id")},
    }

    return templates.TemplateResponse("index.html", {
        "request": request,
        "gov_today": gov_today,
        "group_today": group_today,
        "status": status,
    })

@app.get("/health")
async def health():
    return {"ok": True}

@app.get("/gov/search", response_class=HTMLResponse)
async def gov_search(request: Request, src: str = Query(default="all"), q: str = Query(default="")):
    q_norm = (q or "").strip()
    rows: List[Dict[str, Any]] = []
    if not q_norm:
        return templates.TemplateResponse("gov_search.html", {"request": request, "src": src, "q": q_norm, "rows": rows})

    try:
        query = sb.table("gov_staff").select("source,department,position,name,phone,task")
        if src in ("MOTIE", "MOEF"):
            query = query.eq("source", src)
        kw = f"%{q_norm}%"
        query = query.or_(f"name.ilike.{kw},department.ilike.{kw},position.ilike.{kw},task.ilike.{kw},phone.ilike.{kw}")
        res = query.limit(1000).execute()
        rows = res.data or []
    except Exception as e:
        logger.warning(f"gov_staff search fallback (reason: {e})")
        try:
            q2 = sb.table("gov_staff").select("source,department,position,name,phone,task")
            if src in ("MOTIE", "MOEF"):
                q2 = q2.eq("source", src)
            data = (q2.limit(5000).execute().data) or []
            key = q_norm.lower()
            def hit(r):
                return any(key in (r.get(f, "") or "").lower() for f in ("name", "department", "position", "task", "phone"))
            rows = [r for r in data if hit(r)]
        except Exception as e2:
            logger.error(f"gov_staff fallback failed: {e2}")
            rows = []

    rows.sort(key=lambda r: (r.get("department", "") or "", r.get("name", "") or ""))
    return templates.TemplateResponse("gov_search.html", {"request": request, "src": src, "q": q_norm, "rows": rows})

def _ensure_moef_url(row: Dict[str, Any]) -> Optional[str]:
    DETAIL_URL = "https://www.moef.go.kr/nw/notice/hrDetail.do"
    menuNo = "4050300"
    bbsId = row.get("bbsId")
    postId = row.get("postId")
    if not (bbsId and postId):
        rid = str(row.get("id") or "")
        if "-" in rid:
            parts = rid.split("-", 1)
            if len(parts) == 2:
                bbsId, postId = parts
    if bbsId and postId:
        return f"{DETAIL_URL}?searchBbsId1={bbsId}&searchNttId1={postId}&menuNo={menuNo}"
    return None

def _ensure_motie_url(row: Dict[str, Any]) -> Optional[str]:
    BASE = "https://www.motie.go.kr"
    aid = row.get("id")
    if aid is None:
        return None
    return f"{BASE}/kor/article/ATCL6e90bb9de/{aid}/view?"

@app.get("/gov/timeline", response_class=HTMLResponse)
async def gov_timeline(
    request: Request,
    src: str = Query(default="all"),
    q: Optional[str] = Query(default=None),
    start: Optional[str] = Query(default=None),
    end: Optional[str] = Query(default=None),
    page: Optional[int] = Query(default=1),
    page_size: int = Query(default=20, ge=5, le=100),
):
    q_norm = (q or "").strip().lower()

    def _to_date(s: Optional[str]) -> Optional[date]:
        if not s:
            return None
        try:
            return datetime.strptime(str(s)[:10], "%Y-%m-%d").date()
        except Exception:
            return None

    s_date = _to_date(start)
    e_date = _to_date(end)
    page = page or 1

    items: List[Dict[str, Any]] = []

    def fetch_motie() -> List[Dict[str, Any]]:
        try:
            res = sb.table("motie_id").select("*").order("id", desc=True).limit(1000).execute()
            data = res.data or []
        except Exception as e:
            logger.warning(f"motie_id fetch failed: {e}")
            data = []
        for r in data:
            r["source"] = "MOTIE"
            r["url"] = _ensure_motie_url(r)
        return data

    def fetch_moef() -> List[Dict[str, Any]]:
        try:
            res = sb.table("moef_id").select("*").order("id", desc=True).limit(1000).execute()
            data = res.data or []
        except Exception as e:
            logger.warning(f"moef_id fetch failed: {e}")
            data = []
        for r in data:
            r["source"] = "MOEF"
            r["url"] = _ensure_moef_url(r)
        return data

    if src in ("MOTIE", "all"):
        items.extend(fetch_motie())
    if src in ("MOEF", "all"):
        items.extend(fetch_moef())

    if q_norm:
        def hit_text(r):
            title = (r.get("title") or "").lower()
            tag = (r.get("tag") or "").lower()
            return q_norm in title or q_norm in tag
        items = [r for r in items if hit_text(r)]

    if s_date or e_date:
        def d_of(r) -> Optional[date]:
            p = r.get("posted_at") or r.get("created_at")
            try:
                return datetime.strptime(str(p)[:10], "%Y-%m-%d").date() if p else None
            except Exception:
                return None
        def in_range(r):
            d = d_of(r)
            if not d:
                return False
            if s_date and d < s_date:
                return False
            if e_date and d > e_date:
                return False
            return True
        items = [r for r in items if in_range(r)]

    def sort_key(r):
        def d_try(k):
            try:
                return datetime.strptime(str(r.get(k) or "")[:10], "%Y-%m-%d").date()
            except Exception:
                return date.min
        return (d_try("posted_at"), d_try("created_at"), str(r.get("id") or ""))
    items.sort(key=sort_key, reverse=True)

    total = len(items)
    start_idx = (page - 1) * page_size
    end_idx = start_idx + page_size
    page_rows = items[start_idx:end_idx]
    has_more = end_idx < total

    return templates.TemplateResponse("gov_timeline.html", {
        "request": request,
        "src": src, "q": q or "", "start": start or "", "end": end or "",
        "page": page, "rows": page_rows, "has_more": has_more,
    })

@app.get("/group/timeline", response_class=HTMLResponse)
async def group_timeline(request: Request):
    try:
        res = sb.table("kepco_id").select("department,posted_at,pdf_url,created_at").limit(3000).execute()
        rows = res.data or []
    except Exception as e:
        logger.warning(f"kepco_id fetch failed: {e}")
        rows = []

    order_map = {name: i for i, name in enumerate(COMPANY_ORDER)}
    def dept_order(dep: str | None) -> int:
        label = _norm_company_label(dep)
        return order_map.get(label or "", 999)

    def parse_dt(any_s: Any) -> date:
        y, m, d = _parse_date_parts(str(any_s) if any_s is not None else "")
        return _parts_to_date(y, m, d, assume_last=True) or date.min

    rows.sort(key=lambda r: (dept_order(r.get("department")), -parse_dt(r.get("posted_at")).toordinal()))
    return templates.TemplateResponse("group_timeline.html", {"request": request, "rows": rows})

@app.get("/group/executive", response_class=HTMLResponse)
async def group_executive(request: Request):
    try:
        res = sb.table("kepco_org").select("*").limit(5000).execute()
        raw = res.data or []
    except Exception as e:
        logger.error(f"kepco_org fetch failed: {e}")
        raw = []

    grid: dict[str, dict[str, list[dict]]] = {c: {p: [] for p in POS_ORDER} for c in COMPANY_ORDER}
    female_counts = {c: 0 for c in COMPANY_ORDER}
    total_counts = {c: 0 for c in COMPANY_ORDER}

    for r in raw:
        dep_raw = (r.get("department") or "").strip()
        dep = _norm_company_label(dep_raw)
        if dep not in grid:
            continue

        total_counts[dep] += 1
        if _is_female(r.get("gender")):
            female_counts[dep] += 1

        pos_norm = _normalize_position(r.get("position"))
        if pos_norm is None:
            continue

        y1, m1, d1 = _parse_date_parts(r.get("start"))
        y2, m2, d2 = _parse_date_parts(r.get("end"))
        end_dt = _parts_to_date(y2, m2, d2, assume_last=True)
        expired = bool(end_dt and end_dt < TODAY)

        person = dict(r)
        person["_expired"] = expired
        person["_is_kepco_alum"] = _is_kepco_alum_text(r.get("career"))
        person["_start_disp"] = _fmt_date_display(y1, m1, d1)
        person["_end_disp"] = _fmt_date_display(y2, m2, d2)

        gender = _gender_label(r.get("gender"))
        name = (r.get("name") or "-").strip()
        person["_name_line"] = f"{name}({gender})" if gender != "-" else name

        grid[dep][pos_norm].append(person)

    def start_key(p: dict) -> date:
        y, m, d = _parse_date_parts(p.get("start"))
        return _parts_to_date(y, m, d, assume_last=False) or date.min

    for dep in COMPANY_ORDER:
        for p in ("상임이사", "비상임이사"):
            grid[dep][p].sort(key=start_key)

    gender_stats = [{"female": female_counts[c], "total": total_counts[c]} for c in COMPANY_ORDER]
    columns = COMPANY_ORDER
    rows_for_tpl = [{"position": p, "cells": [grid[c][p] for c in columns]} for p in POS_ORDER]

    return templates.TemplateResponse("group_executive.html", {
        "request": request,
        "columns": columns,
        "rows": rows_for_tpl,
        "gender_stats": gender_stats,
    })

@app.get("/group/executives", response_class=HTMLResponse)
async def group_executives_detail(request: Request, company: str | None = Query(default=None)):
    try:
        res = sb.table("kepco_org").select("*").limit(10000).execute()
        raw: list[dict] = res.data or []
    except Exception as e:
        logger.error(f"kepco_org fetch failed: {e}")
        raw = []

    tabs = COMPANY_ORDER[:]
    current = company if company in COMPANY_ORDER else tabs[0]

    def _key(s: str | None) -> str:
        return (s or "").replace(" ", "").strip()

    cur_key = _key(current)

    def _matches(dep: str | None) -> bool:
        if not dep:
            return False
        dep_key = _key(dep)
        if dep_key == cur_key:
            return True
        norm = _norm_company_label(dep)
        if norm and _key(norm) == cur_key:
            return True
        return cur_key in dep_key or dep_key in cur_key

    cur_rows = [r for r in raw if _matches(r.get("department"))]
    if not cur_rows and raw:
        cur_rows = [r for r in raw if _key(current) in _key(r.get("department"))]

    enriched: list[dict] = []
    for r in cur_rows:
        task = (r.get("task") or r.get("title") or r.get("position") or "-").strip() or "-"
        start_disp, start_cmp = _date_display_and_compare(r.get("start"))
        end_disp, end_cmp = _date_display_and_compare(r.get("end"))
        expired = bool(end_cmp and end_cmp < TODAY)
        person = {**r, "_task": task, "_start_str": start_disp, "_end_str": end_disp,
                  "_expired": expired, "_career_str": _normalize_multiline(r.get("career"))}
        enriched.append(person)

    enriched.sort(key=lambda x: _to_int_safe(x.get("id")))
    return templates.TemplateResponse("group_executive_detail.html", {
        "request": request,
        "tabs": tabs, "current": current, "rows": enriched,
    })

# -------------------------------------------------
# SYNC 대시보드/배치
# -------------------------------------------------
@app.get("/sync", response_class=HTMLResponse)
async def sync_index(request: Request):
    batches = sb.table("ingest_batches").select("*").order("started_at", desc=True).limit(20).execute().data or []
    if not batches:
        return templates.TemplateResponse("sync_index.html", {
            "request": request, "rows": [], "event_stats": {}, "pending_stats": {}
        })

    batch_ids = [b["batch_id"] for b in batches if b.get("batch_id")]
    ev = sb.table("gov_staff_events").select("batch_id,event_type").in_("batch_id", batch_ids).execute().data or []
    event_stats = defaultdict(lambda: defaultdict(int))
    for e in ev:
        event_stats[e["batch_id"]][e["event_type"]] += 1

    dec = sb.table("match_decisions").select("batch_id,decision").in_("batch_id", batch_ids).execute().data or []
    pending_stats = defaultdict(int)
    for d in dec:
        if d.get("decision") == "PENDING":
            pending_stats[d["batch_id"]] += 1

    return templates.TemplateResponse("sync_index.html", {
        "request": request, "rows": batches,
        "event_stats": dict(event_stats), "pending_stats": dict(pending_stats),
    })

def _event_labels_csv(csv_str: str) -> list[str]:
    s = (csv_str or "").strip()
    if not s:
        return []
    return [t.strip() for t in s.split(",") if t.strip()]

def _label_kor(k: str) -> str:
    return {
        "NEW": "신규", "DEPARTED": "퇴사", "MOVE": "부서이동",
        "CHANGE_POSITION": "직위변경", "CHANGE_PHONE": "전화변경",
        "CHANGE_TASK": "업무변경", "CHANGE_NAME": "이름변경",
        "NO_CHANGE": "변경없음",
    }.get(k, k)

def _normalize_name(s: str) -> str:
    return re.sub(r"\s+", "", (s or "")).upper()

@app.get("/sync/batch/{batch_id}", response_class=HTMLResponse)
async def sync_batch(request: Request, batch_id: str):
    st = sb.table("staging_gov_staff").select("*").eq("batch_id", batch_id).order("staging_id").execute().data or []
    cand_rows = sb.table("match_candidates").select("*")\
        .eq("batch_id", batch_id).order("staging_id").order("score", desc=True).limit(10000).execute().data or []
    dec_rows = sb.table("match_decisions").select("*").eq("batch_id", batch_id).execute().data or []
    dec_by_staging = {r["staging_id"]: r for r in dec_rows}
    cur_map = _current_map_by_person_source()

    cands_by_st: Dict[int, list] = {}
    for c in cand_rows:
        cands_by_st.setdefault(c["staging_id"], []).append(c)

    rows_pending, rows_auto = [], []
    for s in st:
        staging_id = s["staging_id"]
        src = s.get("source")
        dec = dec_by_staging.get(staging_id)

        same_name = []
        for c in cands_by_st.get(staging_id, []):
            pid = c.get("candidate_person_id")
            prev = cur_map.get((pid, src))
            if _normalize_name((prev or {}).get("name")) == _normalize_name(s.get("name")):
                same_name.append({
                    "person_id": pid,
                    "score": c.get("score"),
                    "prev_ctx": prev,
                    "name": (prev or {}).get("name") or "-"
                })

        item = {"staging": s, "candidates": same_name, "decision": dec}
        if dec and dec.get("decision") == "AUTO":
            rows_auto.append(item)
        else:
            rows_pending.append(item)

    confirmed_events = sb.table("gov_staff_events").select("*").eq("batch_id", batch_id).order("event_id").execute().data or []
    total = len(st)
    cnt_pending = len(rows_pending)
    cnt_auto = len(rows_auto)

    return templates.TemplateResponse("sync_batch.html", {
        "request": request, "batch_id": batch_id,
        "cards": {"total": total, "pending": cnt_pending, "auto": cnt_auto},
        "rows_pending": rows_pending, "rows_auto": rows_auto,
        "events": confirmed_events,
    })

@app.post("/sync/decide", response_class=HTMLResponse)
async def sync_decide(
    request: Request,
    batch_id: str = Form(...),
    staging_id: int = Form(...),
    choice: str = Form(...),  # "__NEW__" | "__SKIP__" | "<person_id>"
):
    decision = "MANUAL"
    person_id = None
    score = 1.0
    rationale = None

    if choice == "__NEW__":
        rationale = "manual-new"
    elif choice == "__SKIP__":
        decision = "SKIP"
        score = 0.0
        rationale = "manual-skip"
    else:
        try:
            person_id = int(choice)
        except Exception:
            person_id = None

    payload = {
        "batch_id": batch_id, "staging_id": staging_id, "decision": decision,
        "person_id": person_id, "score": score, "rationale": rationale,
    }
    sb.table("match_decisions").upsert(payload, on_conflict="batch_id,staging_id").execute()
    return RedirectResponse(f"/sync/batch/{batch_id}?saved=1", status_code=302)

# 적용 엔드포인트(둘 다 지원)
@app.post("/sync/batch/{batch_id}/apply", response_class=RedirectResponse)
@app.post("/sync/apply/{batch_id}", response_class=RedirectResponse)
async def sync_apply_batch(request: Request, batch_id: str):
    apply_decisions(batch_id)
    return RedirectResponse(url=f"/sync/batch/{batch_id}?applied=1", status_code=303)

# -------------------------------------------------
# MOTIE 부서별 최고 책임자
# -------------------------------------------------
# 기대 순서/들여쓰기/기대 직위 (그대로 재사용)
_MOTIE_ORDER_POS: list[tuple[str, int, str]] = [
    ("장관실", 0, "장관"),
    ("대변인", 1, "대변인"),
    ("홍보담당관", 2, "과장"),
    ("홍보지원팀", 2, "팀장"),
    ("감사관", 1, "감사관"),
    ("감사담당관", 2, "과장"),
    ("제 1차관", 0, "1차관"),
    ("운영지원과", 1, "과장"),
    ("기획조정실", 1, "실장"),
      ("정책기획관", 2, "국장"),
        ("기획재정담당관", 3, "과장"),
        ("혁신행정담당관", 3, "과장"),
        ("규제개혁법무담당관", 3, "과장"),
        ("정보관리담당관", 3, "과장"),
        ("정보보호담당관", 3, "과장"),
      ("비상안전기획관", 2, "국장"),
        ("산업재난담당관", 3, "과장"),
    ("산업정책실", 1, "실장"),
      ("산업정책관", 2, "국장"),
        ("산업정책과", 3, "과장"),
        ("산업일자리혁신과", 3, "과장"),
        ("산업환경과", 3, "과장"),
        ("산업인공지능혁신과", 3, "과장"),
        ("기업정책팀", 3, "팀장"),
      ("산업공급망정책관", 2, "국장"),
        ("산업공급망정책과", 3, "과장"),
        ("소재부품장비개발과", 3, "과장"),
        ("철강세라믹과", 3, "과장"),
        ("화학산업팀", 3, "과장"),
      ("제조산업정책관", 2, "국장"),
        ("기계로봇제조정책과", 3, "과장"),
        ("자동차과", 3, "과장"),
        ("조선해양플랜트과", 3, "과장"),
        ("엔지니어링디자인과", 3, "과장"),
        ("첨단민군혁신지원과", 3, "과장"),
      ("첨단산업정책관", 2, "국장"),
        ("반도체과", 3, "과장"),
        ("배터리전기전자과", 3, "과장"),
        ("바이오융합산업과", 3, "과장"),
        ("섬유탄소나노과", 3, "과장"),
        ("디스플레이가전팀", 3, "팀장"),
    ("산업기반실", 1, "실장"),
      ("산업기술융합정책관", 2, "국장"),
        ("산업기술정책과", 3, "과장"),
        ("산업기술개발과", 3, "과장"),
        ("산업기술시장혁신과", 3, "과장"),
        ("규제샌드박스팀", 3, "팀장"),
      ("지역경제정책관", 2, "국장"),
        ("지역경제총괄과", 3, "과장"),
        ("지역경제진흥과", 3, "과장"),
        ("입지총괄과", 3, "과장"),
      ("중견기업정책관", 2, "국장"),
        ("중견기업정책과", 3, "과장"),
        ("중견기업지원과", 3, "과장"),
        ("유통물류과", 3, "과장"),
    ("제 2차관", 0, "2차관"),
    ("에너지정책실", 1, "실장"),
      ("에너지정책관", 2, "국장"),
        ("에너지정책과", 3, "과장"),
        ("에너지효율과", 3, "과장"),
        ("에너지기술과", 3, "과장"),
      ("전력정책관", 2, "국장"),
        ("전력산업정책과", 3, "과장"),
        ("전력시장과", 3, "과장"),
        ("신산업분산에너지과", 3, "과장"),
        ("전력계통혁신과", 3, "과장"),
      ("재생에너지정책관", 2, "국장"),
        ("재생에너지정책과", 3, "과장"),
        ("재생에너지산업과", 3, "과장"),
        ("재생에너지보급과", 3, "과장"),
      ("수소경제정책관", 2, "국장"),
        ("수소경제정책과", 3, "과장"),
        ("수소산업과", 3, "과장"),
        ("에너지안전과", 3, "과장"),
    ("자원산업정책국", 1, "국장"),
      ("자원안보정책과", 2, "과장"),
      ("석유산업과", 2, "과장"),
      ("가스산업과", 2, "과장"),
      ("석탄산업과", 2, "과장"),
      ("광물자원팀", 2, "팀장"),
    ("원전산업정책국", 1, "국장"),
      ("원전산업정책과", 2, "과장"),
      ("원전환경과", 2, "과장"),
      ("원전지역협력과", 2, "과장"),
    ("원전전략기획관", 1, "국장"),
      ("원전수출진흥과", 2, "과장"),
      ("원전수출협력과", 2, "과장"),
    ("통상교섭본부장", 0, "통상교섭본부장"),
      ("통상차관보", 1, "통상차관보"),
    ("통상정책국", 1, "국장"),
      ("통상정책총괄과", 2, "과장"),
      ("미주통상과", 2, "과장"),
      ("구주통상과", 2, "과장"),
      ("중남미대양주통상팀", 2, "팀장"),
    ("신통상전략지원관", 1, "국장"),
      ("신통상전략과", 2, "과장"),
      ("디지털경제통상과", 2, "과장"),
      ("기후에너지통상과", 2, "과장"),
    ("통상협력국", 1, "국장"),
      ("통상협력총괄과", 2, "과장"),
      ("동북아통상과", 2, "과장"),
      ("아주통상과", 2, "과장"),
      ("중동아프리카통상과", 2, "과장"),
    ("통상교섭실", 1, "실장"),
      ("자유무역협정정책관", 2, "국장"),
        ("자유무역협정정책기획과", 3, "과장"),
        ("자유무역협정이행과", 3, "과장"),
        ("통상협정활용과", 3, "과장"),
        ("인도태평양통상기획팀", 3, "팀장"),
      ("자유무역협정교섭관", 2, "국장"),
        ("자유무역협정협상총괄과", 3, "과장"),
        ("자유무역협정상품과", 3, "과장"),
        ("자유무역협정서비스투자과", 3, "과장"),
        ("자유무역협정무역규범과", 3, "과장"),
      ("다자통상법무관", 2, "국장"),
        ("통상법무기획과", 3, "과장"),
        ("세계무역기구과", 3, "과장"),
        ("다자통상협력과", 3, "과장"),
        ("통상분쟁대응과", 3, "과장"),
    ("무역투자실", 1, "실장"),
      ("무역정책관", 2, "국장"),
        ("무역정책과", 3, "과장"),
        ("무역진흥과", 3, "과장"),
        ("수출입과", 3, "과장"),
      ("투자정책관", 2, "국장"),
        ("투자정책과", 3, "과장"),
        ("투자유치과", 3, "과장"),
        ("해외투자과", 3, "과장"),
      ("무역안보정책관", 2, "국장"),
        ("무역안보정책과", 3, "과장"),
        ("무역안보심사과", 3, "과장"),
        ("기술안보과", 3, "과장"),
    ("국가기술표준원", 1, "원장"),
      ("원장실", 2, "실장"),
      ("지원총괄과", 2, "과장"),
      ("표준정책국", 2, "국장"),
        ("표준정책과", 3, "과장"),
        ("국제표준협력과", 3, "과장"),
        ("산업표준혁신과", 3, "과장"),
        ("전기전자정보표준과", 3, "과장"),
        ("기계융합산업표준과", 3, "과장"),
        ("바이오화학서비스표준과", 3, "과장"),
        ("국제표준화기구전략대응팀", 3, "팀장"),
      ("제품안전정책국", 2, "국장"),
        ("제품안전정책과", 3, "과장"),
        ("제품시장관리과", 3, "과장"),
        ("제품안전정보과", 3, "과장"),
        ("전기통신제품안전과", 3, "과장"),
        ("생활어린이제품안전과", 3, "과장"),
      ("적합성정책국", 2, "국장"),
        ("시험인증정책과", 3, "과장"),
        ("적합성평가과", 3, "과장"),
        ("인증산업진흥과", 3, "과장"),
        ("계량측정제도과", 3, "과장"),
      ("기술규제대응국", 2, "국장"),
        ("기술규제정책과", 3, "과장"),
        ("무역기술장벽협상과", 3, "과장"),
        ("기술규제조정과", 3, "과장"),
        ("기술규제협력과", 3, "과장"),
    ("무역위원회", 1, "위원장"),
      ("상임위원", 2, "상임위원"),
      ("무역조사실", 2, "국장"),
        ("무역구제정책과", 3, "과장"),
        ("산업피해조사과", 3, "과장"),
        ("덤핑조사과", 3, "과장"),
        ("덤핑조사지원과", 3, "과장"),
        ("불공정무역조사과", 3, "과장"),
        ("판정지원과", 3, "과장"),
    ("광업등록사무소", 1, "소장"),
      ("등록팀", 2, "팀장"),
      ("심사팀", 2, "팀장"),
    ("동부광산안전사무소", 1, "소장"),
    ("중부광산안전사무소", 1, "소장"),
    ("서부광산안전사무소", 1, "소장"),
    ("남부광산안전사무소", 1, "소장"),
    ("전기위원회", 1, "위원장"),
      ("사무국", 2, "사무국장"),
    ("경제자유구역기획단", 1, "단장"),
      ("정책기획팀", 2, "팀장"),
      ("혁신지원팀", 2, "팀장"),
      ("서비스투자지원팀", 2, "팀장"),
      ("개발투자지원팀", 2, "팀장"),
    ("마산자유무역지역관리원", 1, "원장"),
      ("관리과", 2, "과장"),
      ("수출산업과", 2, "과장"),
      ("투자홍보과", 2, "과장"),
    ("군산자유무역지역관리원", 1, "원장"),
      ("관리과", 2, "과장"),
      ("수출산업과", 2, "과장"),
      ("비상계획과", 2, "과장"),
    ("대불자유무역지역관리원", 1, "원장"),
      ("관리과", 2, "과장"),
      ("수출산업과", 2, "과장"),
    ("동해자유무역지역관리원", 1, "원장"),
    ("율촌자유무역지역관리원", 1, "원장"),
    ("김제자유무역지역관리원", 1, "원장"),
      ("관리과", 2, "과장"),
      ("수출산업과", 2, "과장"),
    ("울산자유무역지역관리원", 1, "원장"),
      ("관리과", 2, "과장"),
      ("수출산업과", 2, "과장"),
]

# ---- 정규화 헬퍼 (부서/직위) ----
def _nk(s: str | None) -> str:
    return re.sub(r"\s+", "", (s or "")).lower()

def _norm_dept(s: str | None) -> str:
    """부서명 정규화: 공백/대소문자/자잘한 오타 보정(예: 베트남어 자모 등)"""
    if s is None:
        return ""
    t = s.strip()
    # 흔한 오타/문자 치환
    t = t.replace("এ", "에")  # 데이터에 섞여 들어온 벵골어 'এ' 보정
    t = re.sub(r"\s+", "", t)
    return t.lower()

_POS_STRIP_RE = re.compile(r"[()\[\]{}＜＞〈〉【】]|직무대리|대행|겸임|파견|서리")

def _pos_key(s: str | None) -> str:
    """직위 비교용 키: 괄호·수식(직무대리, 대행 등) 제거 + 공백제거소문자"""
    if not s:
        return ""
    t = _POS_STRIP_RE.sub("", s)
    t = re.sub(r"\s+", "", t)
    return t.lower()

def _pos_equal(actual: str | None, expected: str | None) -> bool:
    """직위 동등성: 완전일치 우선. (괄호/수식 제거 후 비교)"""
    return _pos_key(actual) == _pos_key(expected)

@app.get("/org/motie/heads", response_class=HTMLResponse)
async def org_motie_heads_ordered(request: Request, q: str = Query(default="")):
    # ORG 로드
    try:
        org = sb.table("motie_org").select("department,position,name,phone,task").limit(50000).execute().data or []
    except Exception:
        org = []

    # 부서별 인덱스 (정확·빠른 검색)
    by_dept: dict[str, list[dict]] = defaultdict(list)
    for r in org:
        by_dept[_norm_dept(r.get("department"))].append(r)

    key = (q or "").strip().lower()
    items = []

    for unit, indent, expected_pos in _MOTIE_ORDER_POS:
        if unit == "장관정책보좌관":  # 제외
            continue

        dept_key = _norm_dept(unit)
        pool = by_dept.get(dept_key, [])

        # === 핵심: 부서 완전일치 + 직위 동등성 모두 만족하는 것만 ===
        cands = [r for r in pool if _pos_equal(r.get("position"), expected_pos)]

        # 여러 명이면, 전화/이름 유무로 안정적으로 하나 선택(항상 같은 결과)
        def rank(r: dict) -> tuple:
            has_phone = 1 if (r.get("phone") and str(r.get("phone")).strip()) else 0
            name = (r.get("name") or "")
            return (has_phone, name)

        picked = sorted(cands, key=rank, reverse=True)[0] if cands else None

        name  = (picked.get("name") if picked else None) or "(공석)"
        pos   = (picked.get("position") if picked else None) or expected_pos
        phone = (picked.get("phone") if picked else None) or "-"
        task  = (picked.get("task") if picked else None) or "-"

        row = {
            "unit": unit, "indent": indent,
            "name": name, "position": pos, "phone": phone, "task": task,
            "_expected": expected_pos, "_src": ("org-strict" if picked else "none"),
        }

        if not key or key in (" ".join([unit, name, pos, phone, task]).lower()):
            items.append(row)

    return templates.TemplateResponse(
        "org_heads.html",
        {"request": request, "ministry": "산업통상자원부(MOTIE)", "q": q, "items": items},
    )


    
# --- 도구: 인강 자동 넘기기 (/tools/auto-lecture) ---
@app.get("/tools/auto-lecture", response_class=HTMLResponse)
async def tools_auto_lecture(request: Request):
    return templates.TemplateResponse("tools_auto_lecture.html", {"request": request})

# -------------------------------------------------
# (참고) SQL 수정 가이드
# -------------------------------------------------
# motie_units_expected PK 충돌 해결:
#   - unit_name이 중복되므로 PK를 unit_name이 아닌 id(serial)로 두세요.
#
# CREATE TABLE public.motie_units_expected (
#   id bigserial PRIMARY KEY,
#   unit_name text NOT NULL,
#   expected_position text NOT NULL,
#   indent int NOT NULL DEFAULT 0
# );
# -- (기존에 unit_name이 PK였다면, DROP CONSTRAINT 후 위 테이블 재생성/마이그레이션)