from __future__ import annotations

import logging, json, re, calendar, 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 starlette.templating import Jinja2Templates

from app.services.supabase_service import get_client

logger = logging.getLogger("govbot.web")

# ---------- 템플릿/클라이언트 ----------
def _find_templates_dir() -> Path:
    here = Path(__file__).resolve()
    candidates = [here.parents[2] / "templates",
                  here.parents[1] / "templates",
                  here.parents[0] / "templates"]
    for d in candidates:
        if d.exists():
            return d
    return candidates[0]

TEMPLATE_DIR = _find_templates_dir()
templates = Jinja2Templates(directory=str(TEMPLATE_DIR))
sb = get_client()

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

# ---------- 유틸(공용) ----------
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 {k:_s(r.get(k)) for k in ("source","department","position","name","phone","task")}

# --- 회사/직위 정규화 ---
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()
    p0 = p.replace(" ","")
    if "비상임" in p: return "비상임이사"
    if "상임감사" in p0: return "상임감사"
    if "감사위원" in p and "비상임" not in p: return "상임감사"
    if "상임이사" in p: return "상임이사"
    if p in POS_ORDER: return p
    return None

# --- 날짜 ---
_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

# --- 성별/경력 & 기타 ---
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 = []
    out = []
    for r in rows:
        d = str(r.get("posted_at") or r.get("created_at") or "")[:10]
        if d == today_str:
            out.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 out

# --- SYNC 전용 ---
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()

# --- 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 = _strip_invis(s).strip().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 = _strip_invis(s)                  # ← invisibles 제거
    t = _POS_STRIP_RE.sub("", t)         # 수식/괄호류 제거
    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)

# 위쪽 import 아래 어딘가에 추가
# Zero-width 및 비표준 공백 제거
_INVIS_RE = re.compile(r"[\u200B-\u200D\uFEFF\u2060]")  # ZWSP, ZWNJ, ZWJ, BOM, WORD JOINER
def _strip_invis(s: str) -> str:
    if not s:
        return s
    # NBSP(0xA0) → 일반 공백, 대시류 통일
    t = s.replace("\u00A0", " ").replace("—", "-").replace("–", "-")
    # zero-width류 제거
    t = _INVIS_RE.sub("", t)
    return t