# /app/web/pages/gov/motie/changes.py
from __future__ import annotations
from datetime import datetime, timezone, timedelta
from typing import Optional, Dict, List, Set
import hashlib

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

from app.web.deps import templates, sb, motie_person_keys, motie_status_key_raw

router = APIRouter()

KST = timezone(timedelta(hours=9))


def _to_kst(dt: Optional[str]) -> Optional[str]:
    if not dt:
        return None
    try:
        d = datetime.fromisoformat(dt.replace("Z", "+00:00"))
        return d.astimezone(KST).strftime("%Y-%m-%d %H:%M")
    except Exception:
        return dt

def _to_kst_date_disp(dt: Optional[str]) -> Optional[str]:
    if not dt:
        return None
    try:
        d = datetime.fromisoformat(dt.replace("Z", "+00:00")).astimezone(KST)
        # remove leading zeros in month/day
        return f"{d.year}.{d.month}.{d.day}"
    except Exception:
        try:
            # fallback for already-kst string 'YYYY-MM-DD HH:MM'
            s = (dt or '').split(' ')[0]
            y,m,d2 = s.split('-')
            return f"{int(y)}.{int(m)}.{int(d2)}"
        except Exception:
            return dt


def _status_key(name: Optional[str], phone: Optional[str]) -> str:
    try:
        return (motie_person_keys(name, phone) or {}).get("status") or ""
    except Exception:
        base = f"{(name or '').strip()}|{(phone or '').strip()}"
        return hashlib.sha1(base.encode("utf-8")).hexdigest()


def _shorten(s: Optional[str], n: int = 36) -> str:
    if not s:
        return "-"
    s = s.strip()
    return s if len(s) <= n else (s[: n - 1] + "…")


@router.get("/gov/motie/changes_raw", response_class=HTMLResponse)
async def motie_changes_raw(
    request: Request,
    days: int = Query(default=14, ge=1, le=365),
    event: Optional[str] = Query(default=None),       # added/updated/closed
    department: Optional[str] = Query(default=None),
    position: Optional[str] = Query(default=None),
    limit: int = Query(default=200, ge=10, le=2000),
    page: int = Query(default=1, ge=1),
):
    """
    motie_org_events 뷰에서 가져와서 '사람(이름+전화)' 단위로 최신 1건만 보여줌.
    표 컬럼: 일시(KST) / 이름 / 부서/직위 / 이벤트 / 변경 내역 / 상세보기
    """
    since = (datetime.now(timezone.utc) - timedelta(days=days)).isoformat()

    q = sb.table("motie_org_events").select("*").gte("valid_from", since)
    if event:
        q = q.eq("event_type", event)
    if department:
        q = q.ilike("department", f"%{department}%")
    if position:
        q = q.ilike("position", f"%{position}%")

    # 넉넉히 끌어와서 메모리에서 인물 단위 축약
    rows: List[dict] = (
        q.order("valid_from", desc=True)
        .limit(5000)
        .execute()
        .data
        or []
    )

    grouped: Dict[str, dict] = {}
    for r in rows:
        pkey = _person_key(r.get("name"), r.get("phone"))
        if pkey not in grouped:  # 최신(valid_from desc)만 선택
            grouped[pkey] = {
                "person_key": pkey,
                "latest_valid_from_kst": _to_kst(r.get("valid_from")),
                "name": r.get("name"),
                "department": r.get("department"),
                "position": r.get("position"),
                "phone": r.get("phone"),
                "task": r.get("task"),
                "event_type": r.get("event_type"),
                "changed_fields": r.get("changed_fields") or [],
            }

    # 페이지네이션(사람 기준)
    people = list(grouped.values())
    total_people = len(people)
    page_size = limit
    start = (page - 1) * page_size
    end = start + page_size
    page_rows = people[start:end]
    pages = (total_people + page_size - 1) // page_size if total_people else 1
    if page > pages:
        page_rows = []

    return templates.TemplateResponse(
        "gov/motie/changes.html",
        {
            "request": request,
            "rows": page_rows,
            "days": days,
            "event": event or "",
            "department": department or "",
            "position": position or "",
            "limit": limit,
            "page": page,
            "pages": pages,
            "total": total_people,
        },
    )


@router.get("/gov/motie/person/{person_key}", response_class=HTMLResponse)
async def motie_person(request: Request, person_key: str):
    # events에서 (name, phone) 역참조
    def _resolve_from(table: str, cols: str, order_col: str = "valid_from", limit: int = 20000):
        try:
            q = sb.table(table).select(cols).order(order_col, desc=True).limit(limit)
            return q.execute().data or []
        except Exception:
            return []

    name, phone = None, None
    # 1) events 우선
    for e in _resolve_from("motie_org_events", "name,phone,department,position", order_col="valid_from", limit=20000):
        keys = motie_person_keys(e.get("name"), e.get("phone"), e.get("department"), e.get("position"))
        legacy = motie_status_key_raw(e.get("name"), e.get("phone"))
        if person_key in (keys.get("display"), keys.get("status"), legacy):
            name, phone = e.get("name"), e.get("phone")
            break
    # 2) cur 폴백
    if not name:
        for e in _resolve_from("motie_org_cur", "name,phone,department,position", order_col="name", limit=50000):
            keys = motie_person_keys(e.get("name"), e.get("phone"), e.get("department"), e.get("position"))
            legacy = motie_status_key_raw(e.get("name"), e.get("phone"))
            if person_key in (keys.get("display"), keys.get("status"), legacy):
                name, phone = e.get("name"), e.get("phone")
                break
    # 3) hist 폴백
    if not name:
        for e in _resolve_from("motie_org_hist_coalesced", "name,phone,department,position,valid_from", order_col="valid_from", limit=50000):
            keys = motie_person_keys(e.get("name"), e.get("phone"), e.get("department"), e.get("position"))
            legacy = motie_status_key_raw(e.get("name"), e.get("phone"))
            if person_key in (keys.get("display"), keys.get("status"), legacy):
                name, phone = e.get("name"), e.get("phone")
                break

    if not name:
        return templates.TemplateResponse(
            "gov/motie/person.html",
            {"request": request, "person_key": person_key, "cur": None,
             "timeline": [], "not_found": True},
        )

    target_keys: Set[str] = set()
    if person_key:
        target_keys.add(person_key)
    target_roles: Set[tuple[str, str]] = set()

    def _normalize_role(row: Dict[str, Optional[str]]) -> tuple[str, str]:
        dep = (row.get("department") or "").strip()
        pos = (row.get("position") or "").strip()
        return dep, pos

    legacy_initial = motie_status_key_raw(name, phone)
    if legacy_initial:
        target_keys.add(legacy_initial)

    def _extract_keys(row: Dict[str, Optional[str]]) -> Set[str]:
        keys = motie_person_keys(
            row.get("name"),
            row.get("phone"),
            row.get("department"),
            row.get("position"),
        ) or {}
        out: Set[str] = set()
        for k in (keys.get("display"), keys.get("status")):
            if k:
                out.add(k)
        legacy = motie_status_key_raw(row.get("name"), row.get("phone"))
        if legacy:
            out.add(legacy)
        return out

    def _register_keys(row: Dict[str, Optional[str]]) -> None:
        dep, pos = _normalize_role(row)
        if dep or pos:
            target_roles.add((dep, pos))
        for k in _extract_keys(row):
            target_keys.add(k)

    def _matches_target(row: Dict[str, Optional[str]]) -> bool:
        row_keys = _extract_keys(row)
        if not row_keys:
            # fallback to strict name + phone match
            row_name = (row.get("name") or "").strip()
            row_phone = (row.get("phone") or "").strip()
            return row_name == (name or "").strip() and row_phone == (phone or "").strip()
        if not target_keys.isdisjoint(row_keys):
            return True
        dep, pos = _normalize_role(row)
        if dep or pos:
            if (dep, pos) in target_roles:
                return True
        return False

    alt_phones: Set[str] = set()
    try:
        transitions = (
            sb.table("motie_org_transitions")
            .select("person_key,before_person_key,name,department_before,position_before,phone_before,task_before,department_after,position_after,phone_after,task_after,latest_valid_from")
            .eq("name", name)
            .order("latest_valid_from", desc=True)
            .limit(2000)
            .execute()
            .data
            or []
        )
    except Exception:
        transitions = []

    current_phone_norm = (phone or "").strip()
    for t in transitions:
        pk_after = t.get("person_key")
        if pk_after:
            target_keys.add(pk_after)
        after_payload = {
            "name": t.get("name"),
            "department": t.get("department_after"),
            "position": t.get("position_after"),
            "phone": t.get("phone_after"),
            "task": t.get("task_after"),
        }
        _register_keys(after_payload)
        after_phone = (after_payload.get("phone") or "").strip()
        if after_phone and after_phone != current_phone_norm:
            alt_phones.add(after_phone)

        before_payload = {
            "name": t.get("name"),
            "department": t.get("department_before"),
            "position": t.get("position_before"),
            "phone": t.get("phone_before"),
            "task": t.get("task_before"),
        }
        before_pk = t.get("before_person_key")
        if before_pk:
            target_keys.add(before_pk)
        if any((before_payload.get("department") or before_payload.get("position") or before_payload.get("phone"))):
            _register_keys(before_payload)
            before_phone = (before_payload.get("phone") or "").strip()
            if before_phone and before_phone != current_phone_norm:
                alt_phones.add(before_phone)

    def _fetch_hist_by_name_phone(nm: str, ph: Optional[str]) -> List[dict]:
        try:
            rows = (
                sb.table("motie_org_hist")
                .select("*")
                .eq("name", nm)
                .eq("phone", ph if ph is not None else "")
                .order("valid_from", desc=True)
                .limit(2000)
                .execute()
                .data
                or []
            )
            return rows
        except Exception:
            return []

    # 1차: 현재 phone 기준 이력
    hist = _fetch_hist_by_name_phone(name, phone)
    if hist:
        _register_keys(hist[0])

    # 승인된 전환에서 추출한 다른 전화/키 이력 병합
    if alt_phones:
        for ph2 in sorted(alt_phones):
            try:
                extra = (
                    sb.table("motie_org_hist")
                    .select("*")
                    .eq("name", name)
                    .eq("phone", ph2)
                    .order("valid_from", desc=True)
                    .limit(2000)
                    .execute()
                    .data
                    or []
                )
                if extra:
                    filtered = []
                    for row in extra:
                        if _matches_target(row):
                            _register_keys(row)
                            filtered.append(row)
                    hist.extend(filtered)
            except Exception:
                pass
    # 3차: 이름 전체 이력 병합 (중복은 이후 정리)
    try:
        name_rows = (
            sb.table("motie_org_hist")
            .select("*")
            .eq("name", name)
            .order("valid_from", desc=True)
            .limit(2000)
            .execute()
            .data
            or []
        )
    except Exception:
        name_rows = []
    if name_rows:
        filtered = []
        for row in name_rows:
            if _matches_target(row):
                _register_keys(row)
                filtered.append(row)
        hist.extend(filtered)
    # 정렬/중복 제거
    if hist:
        # unique by (department, position, phone, valid_from)
        seen = set()
        uniq = []
        for r in hist:
            k = ((r.get("department") or "").strip(), (r.get("position") or "").strip(), (r.get("phone") or "").strip(), r.get("valid_from") or "")
            if k in seen:
                continue
            seen.add(k)
            uniq.append(r)
        uniq.sort(key=lambda x: x.get("valid_from") or "", reverse=True)
        hist = uniq

    # 최종 폴백: 그래도 비면 승인된 전환 정보로 최소 타임라인 구성
    if not hist and transitions:
        synthetic: List[Dict] = []
        for t in transitions:
            after_row = {
                "name": t.get("name"),
                "department": t.get("department_after"),
                "position": t.get("position_after"),
                "phone": t.get("phone_after"),
                "task": t.get("task_after"),
                "valid_from": t.get("latest_valid_from"),
                "valid_to": None,
            }
            if any((after_row.get("department"), after_row.get("position"), after_row.get("phone"))):
                synthetic.append(after_row)

            before_row = {
                "name": t.get("name"),
                "department": t.get("department_before"),
                "position": t.get("position_before"),
                "phone": t.get("phone_before"),
                "task": t.get("task_before"),
                "valid_from": t.get("latest_valid_from"),
                "valid_to": t.get("latest_valid_from"),
            }
            if any((before_row.get("department"), before_row.get("position"), before_row.get("phone"))):
                synthetic.append(before_row)
        hist = synthetic

    for r in hist:
        r["_valid_from_kst"] = _to_kst(r.get("valid_from"))
        r["_valid_to_kst"] = _to_kst(r.get("valid_to"))
        r["_from_disp"] = _to_kst_date_disp(r.get("valid_from"))
        r["_to_disp"] = _to_kst_date_disp(r.get("valid_to")) if r.get("valid_to") else None

    def _changes(a: dict, b: Optional[dict]) -> List[dict]:
        if not b:
            return []
        fields = ["department", "position", "phone", "task"]
        out: List[dict] = []
        for f in fields:
            if a.get(f) != b.get(f):
                out.append({"field": f, "before": b.get(f), "after": a.get(f)})
        return out

    timeline: List[dict] = []
    for i, cur in enumerate(hist):
        prev = hist[i + 1] if i + 1 < len(hist) else None
        timeline.append({"cur": cur, "diff": _changes(cur, prev)})

    cur = hist[0] if hist else None

    return templates.TemplateResponse(
        "gov/motie/person.html",
        {"request": request, "person_key": person_key, "cur": cur,
         "timeline": timeline, "not_found": False},
    )


@router.get("/gov/motie/person/{person_key}/debug", response_class=JSONResponse)
async def motie_person_debug(person_key: str):
    def _resolve_from(table: str, cols: str, order_col: str = "valid_from", limit: int = 20000):
        try:
            q = sb.table(table).select(cols).order(order_col, desc=True).limit(limit)
            return q.execute().data or []
        except Exception:
            return []

    resolved = {"source": None, "name": None, "phone": None}
    # events
    for e in _resolve_from("motie_org_events", "name,phone,department,position", order_col="valid_from", limit=20000):
        keys = motie_person_keys(e.get("name"), e.get("phone"), e.get("department"), e.get("position"))
        if person_key in (keys.get("display"), keys.get("status")):
            resolved = {"source": "events", "name": e.get("name"), "phone": e.get("phone")}
            break
    # cur
    if not resolved["name"]:
        for e in _resolve_from("motie_org_cur", "name,phone,department,position", order_col="name", limit=50000):
            keys = motie_person_keys(e.get("name"), e.get("phone"), e.get("department"), e.get("position"))
            if person_key in (keys.get("display"), keys.get("status")):
                resolved = {"source": "cur", "name": e.get("name"), "phone": e.get("phone")}
                break
    # hist
    if not resolved["name"]:
        for e in _resolve_from("motie_org_hist_coalesced", "name,phone,department,position,valid_from", order_col="valid_from", limit=50000):
            keys = motie_person_keys(e.get("name"), e.get("phone"), e.get("department"), e.get("position"))
            if person_key in (keys.get("display"), keys.get("status")):
                resolved = {"source": "hist", "name": e.get("name"), "phone": e.get("phone")}
                break

    # fetch hist count + events count for the name (if resolved)
    hist_len = 0
    events_len = 0
    
    if resolved["name"]:
        try:
            hist_len = len(
                sb.table("motie_org_hist_coalesced").select("id").eq("name", resolved["name"]).limit(1_000).execute().data or []
            )
        except Exception:
            pass
        try:
            events_len = len(
                sb.table("motie_org_events").select("name").eq("name", resolved["name"]).limit(1_000).execute().data or []
            )
        except Exception:
            pass

    return JSONResponse({
        "person_key": person_key,
        "resolved": resolved,
        "hist_len": hist_len,
        "events_len": events_len,
    })


@router.get("/gov/motie/person/{person_key}/timeline.json", response_class=JSONResponse)
async def motie_person_timeline_json(person_key: str):
    # reuse resolve and hist building logic from page handler
    # 1) resolve name/phone via events → cur → hist
    def _resolve_from(table: str, cols: str, order_col: str = "valid_from", limit: int = 20000):
        try:
            q = sb.table(table).select(cols).order(order_col, desc=True).limit(limit)
            return q.execute().data or []
        except Exception:
            return []

    name, phone = None, None
    for e in _resolve_from("motie_org_events", "name,phone,department,position", order_col="valid_from", limit=20000):
        keys = motie_person_keys(e.get("name"), e.get("phone"), e.get("department"), e.get("position"))
        legacy = motie_status_key_raw(e.get("name"), e.get("phone"))
        if person_key in (keys.get("display"), keys.get("status"), legacy):
            name, phone = e.get("name"), e.get("phone")
            break
    if not name:
        for e in _resolve_from("motie_org_cur", "name,phone,department,position", order_col="name", limit=50000):
            keys = motie_person_keys(e.get("name"), e.get("phone"), e.get("department"), e.get("position"))
            legacy = motie_status_key_raw(e.get("name"), e.get("phone"))
            if person_key in (keys.get("display"), keys.get("status"), legacy):
                name, phone = e.get("name"), e.get("phone")
                break
    if not name:
        for e in _resolve_from("motie_org_hist_coalesced", "name,phone,department,position,valid_from", order_col="valid_from", limit=50000):
            keys = motie_person_keys(e.get("name"), e.get("phone"), e.get("department"), e.get("position"))
            legacy = motie_status_key_raw(e.get("name"), e.get("phone"))
            if person_key in (keys.get("display"), keys.get("status"), legacy):
                name, phone = e.get("name"), e.get("phone")
                break

    if not name:
        return JSONResponse({"person_key": person_key, "timeline": [], "resolved": None})

    target_keys: Set[str] = set()
    if person_key:
        target_keys.add(person_key)
    target_roles: Set[tuple[str, str]] = set()

    def _normalize_role(row: Dict[str, Optional[str]]) -> tuple[str, str]:
        dep = (row.get("department") or "").strip()
        pos = (row.get("position") or "").strip()
        return dep, pos

    legacy_initial = motie_status_key_raw(name, phone)
    if legacy_initial:
        target_keys.add(legacy_initial)

    def _extract_keys(row: Dict[str, Optional[str]]) -> Set[str]:
        keys = motie_person_keys(
            row.get("name"),
            row.get("phone"),
            row.get("department"),
            row.get("position"),
        ) or {}
        out: Set[str] = set()
        for k in (keys.get("display"), keys.get("status")):
            if k:
                out.add(k)
        legacy = motie_status_key_raw(row.get("name"), row.get("phone"))
        if legacy:
            out.add(legacy)
        return out

    def _register_keys(row: Dict[str, Optional[str]]) -> None:
        dep, pos = _normalize_role(row)
        if dep or pos:
            target_roles.add((dep, pos))
        for k in _extract_keys(row):
            target_keys.add(k)

    def _matches_target(row: Dict[str, Optional[str]]) -> bool:
        row_keys = _extract_keys(row)
        if not row_keys:
            row_name = (row.get("name") or "").strip()
            row_phone = (row.get("phone") or "").strip()
            return row_name == (name or "").strip() and row_phone == (phone or "").strip()
        if not target_keys.isdisjoint(row_keys):
            return True
        dep, pos = _normalize_role(row)
        if dep or pos:
            if (dep, pos) in target_roles:
                return True
        return False

    alt_phones: Set[str] = set()
    try:
        transitions = (
            sb.table("motie_org_transitions")
            .select("person_key,before_person_key,name,department_before,position_before,phone_before,task_before,department_after,position_after,phone_after,task_after,latest_valid_from")
            .eq("name", name)
            .order("latest_valid_from", desc=True)
            .limit(2000)
            .execute().data or []
        )
    except Exception:
        transitions = []

    current_phone_norm = (phone or "").strip()
    for t in transitions:
        pk_after = t.get("person_key")
        if pk_after:
            target_keys.add(pk_after)
        after_payload = {
            "name": t.get("name"),
            "department": t.get("department_after"),
            "position": t.get("position_after"),
            "phone": t.get("phone_after"),
            "task": t.get("task_after"),
        }
        _register_keys(after_payload)
        after_phone = (after_payload.get("phone") or "").strip()
        if after_phone and after_phone != current_phone_norm:
            alt_phones.add(after_phone)

        before_payload = {
            "name": t.get("name"),
            "department": t.get("department_before"),
            "position": t.get("position_before"),
            "phone": t.get("phone_before"),
            "task": t.get("task_before"),
        }
        before_pk = t.get("before_person_key")
        if before_pk:
            target_keys.add(before_pk)
        if any((before_payload.get("department") or before_payload.get("position") or before_payload.get("phone"))):
            _register_keys(before_payload)
            before_phone = (before_payload.get("phone") or "").strip()
            if before_phone and before_phone != current_phone_norm:
                alt_phones.add(before_phone)

    def _fetch_hist_by_name_phone(nm: str, ph: Optional[str]):
        try:
            return (
                sb.table("motie_org_hist").select("*")
                .eq("name", nm)
                .eq("phone", ph if ph is not None else "")
                .order("valid_from", desc=True)
                .limit(2000)
                .execute().data or []
            )
        except Exception:
            return []

    # 1차: 현재 phone 기준
    hist = _fetch_hist_by_name_phone(name, phone)
    if hist:
        _register_keys(hist[0])

    if alt_phones:
        for ph2 in sorted(alt_phones):
            try:
                extra = (
                    sb.table("motie_org_hist").select("*")
                    .eq("name", name)
                    .eq("phone", ph2)
                    .order("valid_from", desc=True)
                    .limit(2000)
                    .execute().data or []
                )
                if extra:
                    filtered = []
                    for row in extra:
                        if _matches_target(row):
                            _register_keys(row)
                            filtered.append(row)
                    hist.extend(filtered)
            except Exception:
                pass
    # 3차: 이름 전체 이력 병합 (중복은 이후 정리)
    try:
        name_rows = (
            sb.table("motie_org_hist").select("*")
            .eq("name", name)
            .order("valid_from", desc=True)
            .limit(2000)
            .execute().data or []
        )
    except Exception:
        name_rows = []
    if name_rows:
        filtered = []
        for row in name_rows:
            if _matches_target(row):
                _register_keys(row)
                filtered.append(row)
        hist.extend(filtered)
    # 마지막 폴백: 승인된 전환 정보를 그대로 활용
    if not hist and transitions:
        synthetic: List[Dict] = []
        for t in transitions:
            after_row = {
                "name": t.get("name"),
                "department": t.get("department_after"),
                "position": t.get("position_after"),
                "phone": t.get("phone_after"),
                "task": t.get("task_after"),
                "valid_from": t.get("latest_valid_from"),
                "valid_to": None,
            }
            if any((after_row.get("department"), after_row.get("position"), after_row.get("phone"))):
                synthetic.append(after_row)

            before_row = {
                "name": t.get("name"),
                "department": t.get("department_before"),
                "position": t.get("position_before"),
                "phone": t.get("phone_before"),
                "task": t.get("task_before"),
                "valid_from": t.get("latest_valid_from"),
                "valid_to": t.get("latest_valid_from"),
            }
            if any((before_row.get("department"), before_row.get("position"), before_row.get("phone"))):
                synthetic.append(before_row)
        hist = synthetic
    # 정렬/중복 제거
    if hist:
        seen = set(); uniq = []
        for r in hist:
            k = ((r.get("department") or "").strip(), (r.get("position") or "").strip(), (r.get("phone") or "").strip(), r.get("valid_from") or "")
            if k in seen:
                continue
            seen.add(k); uniq.append(r)
        uniq.sort(key=lambda x: x.get("valid_from") or "", reverse=True)
        hist = uniq

    def _to_kst(dt: Optional[str]) -> Optional[str]:
        if not dt:
            return None
        try:
            from datetime import datetime, timezone, timedelta
            KST = timezone(timedelta(hours=9))
            d = datetime.fromisoformat(str(dt).replace("Z", "+00:00"))
            return d.astimezone(KST).strftime("%Y-%m-%d %H:%M")
        except Exception:
            return str(dt)

    def _changes(a: dict, b: Optional[dict]):
        if not b:
            return []
        fields = ["department", "position", "phone", "task"]
        return [{"field": f, "before": b.get(f), "after": a.get(f)} for f in fields if a.get(f) != b.get(f)]

    timeline = []
    for i, cur in enumerate(hist):
        prev = hist[i+1] if i+1 < len(hist) else None
        cur_out = dict(cur)
        cur_out["_valid_from_kst"] = _to_kst(cur.get("valid_from"))
        cur_out["_valid_to_kst"] = _to_kst(cur.get("valid_to")) if cur.get("valid_to") else None
        timeline.append({"cur": cur_out, "diff": _changes(cur, prev)})

    return JSONResponse({"person_key": person_key, "resolved": {"name": name, "phone": phone}, "timeline": timeline})