from __future__ import annotations
from typing import List, Dict, Optional
from datetime import datetime, timezone, timedelta

from fastapi import APIRouter, Request, Query
from fastapi.responses import HTMLResponse

from app.web.deps import templates, sb, _normalize_name, _canon_motie_department

router = APIRouter()


def _person_key(name: Optional[str], phone: Optional[str]) -> str:
    import hashlib
    base = f"{(name or '').strip()}|{(phone or '').strip()}"
    return hashlib.sha1(base.encode("utf-8")).hexdigest()


@router.get("/gov/motie/changes", response_class=HTMLResponse)
@router.get("/gov/motie/approved", response_class=HTMLResponse)
async def motie_approved(
    request: Request,
    days: int = Query(default=90, ge=1, le=365),
    limit: int = Query(default=100, ge=10, le=2000),
    page: int = Query(default=1, ge=1),
):
    since = (datetime.now(timezone.utc) - timedelta(days=days)).isoformat()

    # 0) 서버-사이드 뷰가 준비되어 있으면 단일 쿼리 + 정확 카운트로 바로 반환
    #    예상 뷰 스키마: motie_approved_v1(person_key, valid_from, name,
    #      dep_before, dep_after, pos_before, pos_after,
    #      category, changed_dep, changed_pos, is_current)
    try:
        start = (page - 1) * limit
        end = start + limit - 1
        base = (
            sb.table("motie_approved_v1")
            .select(
                "person_key,valid_from,name,is_current,"
                "dep_before,dep_after,pos_before,pos_after,"
                "category,changed_dep,changed_pos",
                count="exact",
            )
            .gte("valid_from", since)
            .order("valid_from", desc=True)
        )
        # 정확 카운트
        total_resp = base.range(0, 0).execute()
        total = getattr(total_resp, "count", None) or (len(total_resp.data or []))
        # 페이지 데이터
        rows_resp = base.range(start, end).execute()
        rows = rows_resp.data or []
        # 템플릿 표시 정규화: None → '-' (신규/종료 표기 조건과 일치시킴)
        for r in rows:
            r["dep_before"] = (r.get("dep_before") or "-")
            r["pos_before"] = (r.get("pos_before") or "-")
            r["dep_after"]  = (r.get("dep_after")  or "-")
            r["pos_after"]  = (r.get("pos_after")  or "-")
            # 호환성: 키 누락/NULL 방지
            r["name"] = r.get("name") or "-"
            r["person_key"] = r.get("person_key") or ""
            r["category"] = r.get("category") or ""
            r["changed_dep"] = bool(r.get("changed_dep"))
            r["changed_pos"] = bool(r.get("changed_pos"))
            r["is_current"] = bool(r.get("is_current"))

        has_prev = page > 1
        has_next = (start + len(rows)) < (total or 0)

        return templates.TemplateResponse(
            "gov/motie/changes.html",
            {
                "request": request,
                "rows": rows,
                "days": days,
                "limit": limit,
                "page": page,
                "total": total,
                "has_prev": has_prev,
                "has_next": has_next,
            },
        )
    except Exception:
        # 뷰가 없거나 오류 시 기존 로직으로 폴백
        pass

    # 1) 승인된 항목 수집 (서버사이드 페이징 + total count)
    start = (page - 1) * limit
    end = start + limit - 1
    total = 0
    acc: list[dict]
    try:
        base = (
            sb.table("admin_motie_change_status")
            .select("person_key,latest_valid_from,updated_at", count="exact")
            .eq("decision", "accept")
            .gte("latest_valid_from", since)
            .order("latest_valid_from", desc=True)
        )
        total_resp = base.range(0, 0).execute()
        total = getattr(total_resp, "count", None) or (len(total_resp.data or []))
        # Fetch a wider slice to compensate for entries filtered out (e.g., task-only)
        # We will later slice the displayed rows to 'limit'.
        widen = max(limit * 3, 150)
        acc_resp = base.range(start, start + widen - 1).execute()
        acc = acc_resp.data or []
    except Exception:
        acc = []
        total = 0
    if not acc:
        return templates.TemplateResponse(
            "gov/motie/changes.html",
            {"request": request, "rows": [], "days": days, "limit": limit, "page": page, "total": 0, "has_prev": page > 1, "has_next": False},
        )

    # 2) person_key -> (name, phone) 사전
    acc_keys = [a.get("person_key") for a in acc if a.get("person_key")]
    acc_ts   = [a.get("latest_valid_from") for a in acc if a.get("latest_valid_from")]
    key_map: Dict[str, Dict[str, Optional[str]]] = {}
    # (a) 우선: 승인 시점(valid_from)과 동일한 시점의 병합 이력에서 매핑
    if acc_ts:
        try:
            cur_rows = (
                sb.table("motie_org_hist_coalesced")
                .select("name,phone,valid_from")
                .in_("valid_from", list(set(acc_ts)))
                .limit(50000)
                .execute()
                .data
                or []
            )
            for r in cur_rows:
                k = _person_key(r.get("name"), r.get("phone"))
                if k in acc_keys and k not in key_map:
                    key_map[k] = {"name": r.get("name"), "phone": r.get("phone")}
        except Exception:
            key_map = {}
    # (b) 보조: 최근 이벤트에서 역산(유연한 매칭; 시간 불일치/전화 공란 등 대응)
    remaining = {k for k in acc_keys if k not in key_map}
    if remaining:
        try:
            evs = (
                sb.table("motie_org_events")
                .select("name,phone")
                .order("valid_from", desc=True)
                .limit(20000)
                .execute()
                .data
                or []
            )
            for e in evs:
                k = _person_key(e.get("name"), e.get("phone"))
                if k in remaining and k not in key_map:
                    key_map[k] = {"name": e.get("name"), "phone": e.get("phone")}
        except Exception:
            pass

    # 3) 현재 재직 집합 (해당 페이지의 이름들만 제한적으로 조회)
    cur_set = set()
    cur_name_set = set()
    try:
        # 이름 후보를 먼저 준비 (승인 매핑에서 나온 키 기준)
        uniq_names = sorted({(v or {}).get("name") for v in key_map.values() if (v or {}).get("name")})
        if uniq_names:
            cur = (
                sb.table("motie_org_cur")
                .select("name,phone")
                .in_("name", uniq_names)
                .limit(50000)
                .execute().data or []
            )
        else:
            cur = []
        for r in cur:
            cur_set.add(_person_key(r.get("name"), r.get("phone")))
            nm = _normalize_name(r.get("name") or "")
            if nm:
                cur_name_set.add(nm)
    except Exception:
        cur_set = set(); cur_name_set = set()

    rows: List[Dict] = []
    # 4) 이름 단위로 히스토리를 배치 조회 후, 각 승인 시점의 before/after 계산
    # build map: key -> ts
    ts_by_key: Dict[str, str] = {a.get("person_key"): a.get("latest_valid_from") for a in acc if a.get("person_key") and a.get("latest_valid_from")}
    # chunk names to limit query length
    uniq_names = sorted({(v or {}).get("name") for v in key_map.values() if (v or {}).get("name")})
    name_to_rows: Dict[tuple, List[Dict]] = {}
    name_rows_all: Dict[str, List[Dict]] = {}
    CHUNK = 50
    for i in range(0, len(uniq_names), CHUNK):
        part = uniq_names[i:i+CHUNK]
        try:
            data = (
                sb.table("motie_org_hist_coalesced")
                .select("name,phone,department,position,valid_from")
                .in_("name", part)
                .order("name", desc=False)
                .order("phone", desc=False)
                .order("valid_from", desc=True)
                .limit(50000)
                .execute()
                .data
                or []
            )
            for r in data:
                k = _person_key(r.get("name"), r.get("phone"))
                name_to_rows.setdefault((r.get("name"), r.get("phone")), []).append(r)
                nm2 = (r.get("name") or "").strip()
                name_rows_all.setdefault(nm2, []).append(r)
        except Exception:
            continue
    # ensure all name lists are sorted desc by valid_from
    for nm, lst in name_rows_all.items():
        lst.sort(key=lambda x: x.get("valid_from") or "", reverse=True)

    # also fetch names marked current in hist (stronger signal, restricted by names)
    hist_curr_name_set = set()
    try:
        hq = sb.table("motie_org_hist_coalesced").select("name").eq("is_current", True).is_("valid_to", "null")
        if uniq_names:
            hq = hq.in_("name", uniq_names)
        hrows = (hq.limit(50000).execute().data) or []
        for r in hrows:
            nm = _normalize_name(r.get("name") or "")
            if nm:
                hist_curr_name_set.add(nm)
    except Exception:
        hist_curr_name_set = set()

    # 3c) Batch fetch events changed_fields for this page's timestamps and names (to avoid per-row queries)
    changed_fields_map: Dict[tuple, set] = {}
    try:
        if acc_ts and uniq_names:
            evrows = (
                sb.table("motie_org_events")
                .select("name,phone,valid_from,changed_fields")
                .in_("valid_from", list(set(acc_ts)))
                .in_("name", uniq_names)
                .limit(50000)
                .execute().data or []
            )
            for e in evrows:
                key = ((e.get("name") or "").strip(), e.get("phone") or "", e.get("valid_from") or "")
                cf = set(e.get("changed_fields") or [])
                changed_fields_map[key] = cf
    except Exception:
        changed_fields_map = {}

    for pkey, pair in key_map.items():
        nm_raw = ((pair or {}).get("name") or "").strip(); ph = (pair or {}).get("phone")
        nm = _normalize_name(nm_raw)
        if not nm:
            continue
        lst = name_to_rows.get((nm_raw, ph)) or []
        ts = ts_by_key.get(pkey)
        cur_row = None; prev_row = None
        for i, r in enumerate(lst):
            rv = r.get("valid_from") or ""
            if ts and rv and rv <= ts:
                cur_row = r; prev_row = lst[i+1] if i+1 < len(lst) else None; break
        if not cur_row:
            if lst:
                cur_row = lst[0]; prev_row = lst[1] if len(lst) > 1 else None
            else:
                continue
        dep_before = (prev_row or {}).get("department"); dep_after = cur_row.get("department")
        pos_before = (prev_row or {}).get("position");   pos_after = cur_row.get("position")
        # normalize before compare to avoid false positives
        def _npos(s: Optional[str]) -> str:
            return (s or "").strip()
        dep_before_n = _canon_motie_department(dep_before)
        dep_after_n  = _canon_motie_department(dep_after)
        pos_before_n = _npos(pos_before)
        pos_after_n  = _npos(pos_after)
        changed_dep = (dep_before_n != dep_after_n) if (dep_before_n and dep_after_n) else False
        changed_pos = (pos_before_n != pos_after_n) if (pos_before_n and pos_after_n) else False
        # 보완: 동일 이름 기준으로 이전 이력과 비교(전화 변경으로 인한 누락 방지)
        if not (changed_dep or changed_pos):
            lst_all = name_rows_all.get(nm) or []
            if lst_all:
                # find current row index by timestamp match
                idx = next((i for i, r0 in enumerate(lst_all) if (r0.get("valid_from") or "") == (ts or "")), None)
                if idx is not None:
                    prev_any = lst_all[idx + 1] if (idx + 1) < len(lst_all) else None
                    if prev_any:
                        dep_before2 = prev_any.get("department"); pos_before2 = prev_any.get("position")
                        dep_before2_n = _canon_motie_department(dep_before2)
                        pos_before2_n = _npos(pos_before2)
                        if (dep_after_n and dep_before2_n and dep_after_n != dep_before2_n) or (pos_after_n and pos_before2_n and pos_after_n != pos_before2_n):
                            changed_dep = (dep_after_n != dep_before2_n) if (dep_after_n and dep_before2_n) else False
                            changed_pos = (pos_after_n != pos_before2_n) if (pos_after_n and pos_before2_n) else False
                            dep_before = dep_before2
                            pos_before = pos_before2
        # 이벤트 changed_fields로 최종 검증: 부서/직위가 아닌 변경이면 오탐 제거 (배치 맵 사용)
        cf = changed_fields_map.get((nm_raw, ph or "", ts or ""))
        if cf is not None:
            if ("department" in cf) or ("position" in cf):
                changed_dep = ("department" in cf)
                changed_pos = ("position" in cf)
            else:
                changed_dep = False
                changed_pos = False
        # 분류: 신규/종료 포함 (종료 판단은 이름 단위 현재 재직 여부로 보완)
        is_cur_name = (nm in cur_name_set) or (nm in hist_curr_name_set)
        if prev_row is None:
            cat = "신규"
        elif not is_cur_name:
            cat = "종료"
        elif changed_dep and changed_pos:
            cat = "부서+직위"
        elif changed_dep:
            cat = "부서 이동"
        elif changed_pos:
            cat = "직위 변경"
        else:
            # 변경 없음이면 스킵
            continue
        rows.append({
            "person_key": pkey,
            "valid_from": ts,
            "name": nm_raw,
            "is_current": is_cur_name,
            "dep_before": dep_before or "-",
            "dep_after": dep_after or "-",
            "pos_before": pos_before or "-",
            "pos_after": pos_after or "-",
            "category": cat,
            "changed_dep": changed_dep,
            "changed_pos": changed_pos,
        })

    # 최신순 정렬 + 페이지 크기에 맞춰 슬라이스
    rows.sort(key=lambda x: x.get("valid_from") or "", reverse=True)
    rows = rows[:limit]

    # 표시 총계는 실제 렌더링 행 수로 표시(페이지 단위 계산 간소화)
    total_display = len(rows)
    has_next_guess = (page * limit) < (total or 0)

    return templates.TemplateResponse(
        "gov/motie/changes.html",
        {
            "request": request,
            "rows": rows,
            "days": days,
            "limit": limit,
            "page": page,
            "total": total_display,
            "has_prev": page > 1,
            "has_next": has_next_guess,
        },
    )