import os, datetime
from typing import List, Dict, Any, Optional, Iterable
from supabase import create_client, Client

# ----------------- core -----------------
def get_supabase() -> Client:
    url = os.getenv("SUPABASE_URL")
    key = os.getenv("SUPABASE_KEY")
    if not url or not key:
        raise RuntimeError("SUPABASE_URL / SUPABASE_KEY 미설정")
    return create_client(url, key)

# ----------------- appointments -----------------
def upsert_appointments(items: List[Dict[str, Any]]) -> int:
    if not items:
        return 0
    sb = get_supabase()

    sources = list({i["source"] for i in items if i.get("source")})
    source_ids = list({i["source_id"] for i in items if i.get("source_id")})
    if not sources or not source_ids:
        return 0

    existing = (
        sb.table("gov_appointments")
        .select("source,source_id")
        .in_("source", sources)
        .in_("source_id", source_ids)
        .execute()
    )
    existing_pairs = set((r["source"], r["source_id"]) for r in (existing.data or []))

    new_items = []
    now = datetime.datetime.utcnow().isoformat() + "Z"
    for i in items:
        pair = (i.get("source"), i.get("source_id"))
        if not pair[0] or not pair[1] or pair in existing_pairs:
            continue
        new_items.append({
            "source": i["source"],
            "source_id": i["source_id"],
            "title": i.get("title", ""),
            "url": i.get("url", ""),
            "published_at": i.get("published_at"),
            "extra": i.get("extra", {}),
            "crawled_at": now,
        })

    if not new_items:
        return 0

    get_supabase().table("gov_appointments").upsert(new_items, on_conflict="source,source_id").execute()
    return len(new_items)

# ----------------- subscribers: public.moef_subscribers -----------------
SUB_TABLE = "moef_subscribers"

def get_subscriber(chat_id: int) -> Optional[Dict[str, Any]]:
    sb = get_supabase()
    res = sb.table(SUB_TABLE).select("*").eq("chat_id", chat_id).limit(1).execute()
    rows = res.data or []
    return rows[0] if rows else None

def add_subscriber(chat_id: int, first_name: Optional[str]) -> None:
    sb = get_supabase()
    payload = {
        "chat_id": chat_id,
        "first_name": first_name,  # subscribed_at은 DB default now()
    }
    # chat_id(PK) 기준 멱등 처리
    sb.table(SUB_TABLE).upsert(payload, on_conflict="chat_id").execute()

def delete_subscriber(chat_id: int) -> None:
    sb = get_supabase()
    sb.table(SUB_TABLE).delete().eq("chat_id", chat_id).execute()

def get_active_chat_ids() -> List[int]:
    sb = get_supabase()
    res = sb.table(SUB_TABLE).select("chat_id").execute()
    return [int(r["chat_id"]) for r in (res.data or [])]

# ----------------- MOTIE -----------------
def has_motie_id(article_id: str) -> bool:
    sb = get_supabase()
    res = sb.table("motie_id").select("id").eq("id", article_id).limit(1).execute()
    return bool(res.data)

def insert_motie_id(article_id: str, title: str, posted_at: str | None = None) -> None:
    sb = get_supabase()
    payload = {"id": article_id, "title": title}
    if posted_at:
        payload["posted_at"] = posted_at  # DATE (YYYY-MM-DD)
    sb.table("motie_id").insert(payload).execute()

# ----------------- MOEF -----------------
def has_moef_id(item_id: str) -> bool:
    sb = get_supabase()
    res = sb.table("moef_id").select("id").eq("id", item_id).limit(1).execute()
    return bool(res.data)

def insert_moef_id(item_id: str, bbs_id: str, post_id: str, title: str,
                   tag: str | None = None, posted_at: str | None = None) -> None:
    sb = get_supabase()
    payload = {
        "id": item_id,
        "bbsId": bbs_id,     # 대소문자 주의
        "postId": post_id,   # 대소문자 주의
        "title": title,
    }
    if tag is not None:
        payload["tag"] = tag
    if posted_at is not None:
        payload["posted_at"] = posted_at  # DATE (YYYY-MM-DD)
    sb.table("moef_id").insert(payload).execute()

# ----------------- GROUP / KEPCO -----------------
# 테이블: kepco_id
# id text PK, department text NOT NULL, disclosure_no text NOT NULL,
# title text NULL, tag text NULL, posted_at date NULL, pdf_url text NULL,
# created_at timestamptz default now(),
# UNIQUE(department, disclosure_no)

def _norm_date_to_iso(d: Optional[str]) -> Optional[str]:
    """
    'YYYY.MM.DD' / 'YYYY-MM-DD' / 'YYYY/MM/DD' -> 'YYYY-MM-DD'
    그 외/None 은 None
    """
    if not d:
        return None
    s = str(d).strip()
    s = s.replace(".", "-").replace("/", "-")
    parts = s[:10].split("-")
    if len(parts) != 3:
        return None
    y, m, dd = parts
    if not (y.isdigit() and m.isdigit() and dd.isdigit()):
        return None
    try:
        y2, m2, d2 = int(y), int(m), int(dd)
        return f"{y2:04d}-{m2:02d}-{d2:02d}"
    except Exception:
        return None

def kepco_unique_exists(department: str, disclosure_no: str) -> bool:
    sb = get_supabase()
    res = (
        sb.table("kepco_id")
          .select("id")
          .eq("department", department)
          .eq("disclosure_no", disclosure_no)
          .limit(1)
          .execute()
    )
    return bool(res.data)

def has_kepco_id(compound_id: str) -> bool:
    sb = get_supabase()
    res = sb.table("kepco_id").select("id").eq("id", compound_id).limit(1).execute()
    return bool(res.data)

def upsert_kepco_id(*,
                    compound_id: str,
                    department: str,
                    disclosure_no: str,
                    title: Optional[str] = None,
                    tag: Optional[str] = None,
                    posted_at: Optional[str] = None,
                    pdf_url: Optional[str] = None) -> None:
    """
    kepco_id에 (department, disclosure_no) 기준 멱등 upsert.
    NOT NULL 필드 누락을 절대 만들지 않도록 기본값/정규화 처리.
    """
    if not compound_id:
        raise ValueError("compound_id required")
    if not department:
        raise ValueError("department required")
    if not disclosure_no:
        raise ValueError("disclosure_no required")

    payload = {
        "id": compound_id,
        "department": department,
        "disclosure_no": disclosure_no,
        "title": title or "임원현황",
        "tag": tag,  # 필요 시 None 허용
        "posted_at": _norm_date_to_iso(posted_at) if posted_at else None,
        "pdf_url": pdf_url or f"https://alio.go.kr/download/pdf.json?disclosureNo={disclosure_no}",
    }

    sb = get_supabase()
    # (department, disclosure_no)로 멱등 처리
    sb.table("kepco_id").upsert(payload, on_conflict="department,disclosure_no").execute()

# ---- kepco_org 보조 함수 (스키마는 질문에서 제시한 대로 text 컬럼) ----
def clear_kepco_org_by_department(department: str) -> None:
    sb = get_supabase()
    sb.table("kepco_org").delete().eq("department", department).execute()

def insert_kepco_org_rows(rows: Iterable[Dict[str, Any]]) -> None:
    rows = list(rows)
    if not rows:
        return

    normed = []
    for r in rows:
        career = r.get("career")
        if isinstance(career, list):
            career_text = "\n".join(str(x).strip() for x in career if str(x).strip())
        else:
            career_text = str(career).strip() if career is not None else None

        normed.append({
            "department": r.get("department"),
            "name": r.get("name"),
            "position": r.get("position"),
            "gender": r.get("gender"),
            "start": r.get("start"),
            "end": r.get("end"),
            "task": r.get("task"),
            "career": career_text,
        })

    sb = get_supabase()
    CHUNK = 500
    for i in range(0, len(normed), CHUNK):
        sb.table("kepco_org").insert(normed[i:i+CHUNK]).execute()