#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""공공데이터포털의 공동주택 기본정보와 최근 아파트 매매 실거래를 단지별로 갱신한다."""

import argparse
import difflib
import json
import os
import re
import sys
import urllib.parse
import urllib.request
import urllib.error
import xml.etree.ElementTree as ET
from datetime import datetime
from pathlib import Path

BASE_DIR = Path(__file__).resolve().parents[1]
if str(BASE_DIR) not in sys.path:
    sys.path.insert(0, str(BASE_DIR))


def public_data_service_key():
    """환경변수를 우선 사용하고, 없으면 서버의 비공개 JSON 설정을 읽는다."""
    env_key = clean(os.getenv("DATA_GO_KR_SERVICE_KEY"))
    if env_key:
        return env_key
    config_path = BASE_DIR / "config" / "public_api_keys.json"
    try:
        payload = json.loads(config_path.read_text(encoding="utf-8-sig"))
        return clean(payload.get("DATA_GO_KR_SERVICE_KEY")) if isinstance(payload, dict) else ""
    except (OSError, ValueError, TypeError):
        return ""


def clean(value):
    return re.sub(r"\s+", " ", str(value or "")).strip()


def compact_name(value):
    text = clean(value).lower()
    text = re.sub(r"\b\d{1,4}동\b", "", text)
    return re.sub(r"(?:아파트|주상복합|단지|차|\s|[-_.·()])+", "", text)


def number(value):
    text = clean(value).replace(",", "")
    match = re.search(r"-?\d+(?:\.\d+)?", text)
    if not match:
        return None
    try:
        return int(float(match.group(0)))
    except (TypeError, ValueError, OverflowError):
        return None


def api_items(payload):
    if isinstance(payload, dict):
        for key in ("item", "items"):
            if key in payload:
                value = payload[key]
                if isinstance(value, list):
                    return value
                if isinstance(value, dict):
                    nested = api_items(value)
                    return nested or [value]
        for value in payload.values():
            found = api_items(value)
            if found:
                return found
    elif isinstance(payload, list):
        return payload
    return []


def request_api(url, params, service_key):
    query = dict(params)
    query["serviceKey"] = service_key
    query.setdefault("_type", "json")
    target = url + ("&" if "?" in url else "?") + urllib.parse.urlencode(query, safe="%")
    request = urllib.request.Request(target, headers={"User-Agent": "MaemulHOME-MultiWebzine/1.0"})
    try:
        with urllib.request.urlopen(request, timeout=25) as response:
            raw = response.read()
    except urllib.error.HTTPError as exc:
        try:
            body = exc.read().decode("utf-8", errors="replace")
        except Exception:
            body = ""
        # 인증키가 들어간 요청 URL은 로그에 노출하지 않고 공공 API 응답만 표시한다.
        detail = clean(re.sub(r"<[^>]+>", " ", body))[:700]
        raise RuntimeError(f"public_api_http_{exc.code}: {detail or exc.reason}") from exc
    text = raw.decode("utf-8", errors="replace").strip()
    if text.startswith("{") or text.startswith("["):
        payload = json.loads(text)
        return api_items(payload), payload
    root = ET.fromstring(text)
    result_code = clean(root.findtext(".//resultCode"))
    if result_code and result_code not in {"00", "000", "0"}:
        raise RuntimeError(clean(root.findtext(".//resultMsg")) or f"public_api_{result_code}")
    items = []
    for node in root.findall(".//item"):
        items.append({child.tag: clean(child.text) for child in list(node)})
    return items, {"xml": text[:100000]}


def request_api_candidates(base_url, operation_names, params, service_key):
    """공공 API 버전 교체 시 상세기능명 차이를 안전하게 순차 확인한다."""
    errors = []
    if base_url.rstrip("/").split("/")[-1].startswith("get"):
        return request_api(base_url, params, service_key)
    for operation in operation_names:
        try:
            return request_api(base_url.rstrip("/") + "/" + operation, params, service_key)
        except RuntimeError as exc:
            message = str(exc)
            errors.append(f"{operation}={message}")
            if "NO_OPENAPI_SERVICE_ERROR" not in message and "public_api_http_404" not in message:
                raise
    raise RuntimeError("public_api_operation_not_found: " + " | ".join(errors)[-1200:])


def field(row, *names):
    lowered = {str(k).lower(): v for k, v in (row or {}).items()}
    for name in names:
        value = lowered.get(name.lower())
        if clean(value):
            return clean(value)
    return ""


def active_complexes(conn, realtor_id=None, limit=20, force=False):
    sql = """
        SELECT c.site_id,c.complex_key,MAX(c.complex_name) AS complex_name,MAX(c.region_name) AS region_name,
               MAX(rc.b_code) AS b_code,MAX(rc.h_code) AS h_code,
               MAX(p.fetched_at) AS fetched_at,MAX(p.last_attempt_at) AS last_attempt_at
          FROM multi_site_complexes c
          INNER JOIN multi_sites s ON s.id=c.site_id AND s.status='active'
          LEFT JOIN multi_site_complex_articles ca
            ON ca.site_id=c.site_id AND ca.complex_key=c.complex_key AND ca.status='active'
          LEFT JOIN multi_article_region_codes rc ON rc.article_id=ca.article_id
          LEFT JOIN multi_site_complex_public_data p
            ON p.site_id=c.site_id AND p.complex_key=c.complex_key
         WHERE c.status='active' AND c.is_excluded=0
    """
    params = []
    if realtor_id:
        sql += " AND s.realtor_id=%s"
        params.append(int(realtor_id))
    if not force:
        sql += " AND (p.fetched_at IS NULL OR p.fetched_at < DATE_SUB(NOW(),INTERVAL 7 DAY))"
    sql += " GROUP BY c.site_id,c.complex_key ORDER BY c.priority_score DESC,c.last_seen_at DESC LIMIT %s"
    params.append(max(1, int(limit)))
    with conn.cursor() as cur:
        cur.execute(sql, params)
        return cur.fetchall() or []


def best_match(complex_name, candidates):
    expected = compact_name(complex_name)
    ranked = []
    for item in candidates:
        name = field(item, "kaptName", "aptNm", "aptName")
        actual = compact_name(name)
        if not expected or not actual:
            continue
        ratio = difflib.SequenceMatcher(None, expected, actual).ratio()
        if expected in actual or actual in expected:
            ratio = max(ratio, 0.90)
        ranked.append((ratio, item))
    ranked.sort(key=lambda value: value[0], reverse=True)
    if not ranked or ranked[0][0] < 0.58:
        return None, 0.0, "not_found"
    if len(ranked) > 1 and ranked[0][0] - ranked[1][0] < 0.04 and ranked[0][0] < 0.90:
        return None, ranked[0][0], "ambiguous"
    return ranked[0][1], ranked[0][0], "matched"


def recent_months(count=3):
    year, month = datetime.now().year, datetime.now().month
    output = []
    for _ in range(count):
        output.append(f"{year:04d}{month:02d}")
        month -= 1
        if month == 0:
            month, year = 12, year - 1
    return output


def trade_summary(service_key, lawd_code, complex_name):
    url = os.getenv("MOLIT_APT_TRADE_API_URL", "https://apis.data.go.kr/1613000/RTMSDataSvcAptTradeDev/getRTMSDataSvcAptTradeDev")
    matched = []
    used_month = ""
    expected = compact_name(complex_name)
    for deal_month in recent_months(int(os.getenv("MULTI_TRADE_MONTHS", "3"))):
        items, _ = request_api(url, {"LAWD_CD": lawd_code, "DEAL_YMD": deal_month, "pageNo": 1, "numOfRows": 1000}, service_key)
        current = [item for item in items if difflib.SequenceMatcher(None, expected, compact_name(field(item, "aptNm", "아파트"))).ratio() >= 0.72]
        if current:
            matched.extend(current)
            used_month = deal_month
            break
    prices = []
    areas = set()
    for item in matched:
        price = number(field(item, "dealAmount", "거래금액"))
        if price is not None:
            prices.append(price)
        area = field(item, "excluUseAr", "전용면적")
        if area:
            areas.add(f"전용 {area}㎡")
    return {
        "trade_month": used_month or None,
        "trade_count": len(prices),
        "trade_min_price": min(prices) if prices else None,
        "trade_max_price": max(prices) if prices else None,
        "trade_avg_price": round(sum(prices) / len(prices)) if prices else None,
        "trade_area_summary": " · ".join(sorted(areas))[:500] or None,
    }, matched


def update_one(conn, row, service_key):
    b_code = clean(row.get("b_code"))
    if len(b_code) < 10:
        raise RuntimeError("법정동 코드가 없어 카카오 위치정보를 먼저 갱신해야 합니다.")
    list_url = os.getenv("KAPT_LIST_API_URL", "https://apis.data.go.kr/1613000/AptListService4")
    basis_url = os.getenv("KAPT_BASIC_API_URL", "https://apis.data.go.kr/1613000/AptBasisInfoServiceV5")
    candidates, list_raw = request_api_candidates(
        list_url, ("getLegaldongAptList4",),
        {"bjdCode": b_code[:10], "pageNo": 1, "numOfRows": 500}, service_key,
    )
    matched, confidence, status = best_match(row.get("complex_name"), candidates)
    if not matched:
        with conn.cursor() as cur:
            cur.execute("""INSERT INTO multi_site_complex_public_data
                (site_id,complex_key,match_status,match_confidence,legal_dong_code,error_message,last_attempt_at,created_at,updated_at)
                VALUES (%s,%s,%s,%s,%s,%s,NOW(),NOW(),NOW())
                ON DUPLICATE KEY UPDATE match_status=VALUES(match_status),match_confidence=VALUES(match_confidence),
                 legal_dong_code=VALUES(legal_dong_code),error_message=VALUES(error_message),last_attempt_at=NOW(),updated_at=NOW()""",
                (row["site_id"], row["complex_key"], status, confidence * 100, b_code[:10], "공동주택 단지코드 자동 매칭 실패"))
        conn.commit()
        return status
    kapt_code = field(matched, "kaptCode")
    basic_items, basic_raw = request_api_candidates(
        basis_url, ("getAphusBassInfoV5", "getAphusBassInfoV4", "getAphusBassInfoV3", "getAphusBassInfo"),
        {"kaptCode": kapt_code}, service_key,
    )
    detail_items, detail_raw = request_api_candidates(
        os.getenv("KAPT_DETAIL_API_URL", basis_url),
        ("getAphusDtlInfoV5", "getAphusDtlInfoV4", "getAphusDtlInfoV3", "getAphusDtlInfo"),
        {"kaptCode": kapt_code}, service_key,
    )
    basic = basic_items[0] if basic_items else {}
    detail = detail_items[0] if detail_items else {}
    merged = dict(matched)
    merged.update(basic)
    merged.update(detail)
    trades = {"trade_month": None, "trade_count": 0, "trade_min_price": None, "trade_max_price": None, "trade_avg_price": None, "trade_area_summary": None}
    trade_raw = []
    lawd_code = b_code[:5]
    try:
        trades, trade_raw = trade_summary(service_key, lawd_code, row.get("complex_name"))
    except Exception as exc:
        trade_raw = [{"warning": str(exc)}]
    area_summary = field(merged, "kaptMparea_60", "kaptMparea_85", "privArea") or trades["trade_area_summary"]
    source_json = json.dumps({"list_match": matched, "basic": basic_raw, "detail": detail_raw, "trades": trade_raw[:100]}, ensure_ascii=False, default=str)
    with conn.cursor() as cur:
        cur.execute("""INSERT INTO multi_site_complex_public_data
            (site_id,complex_key,kapt_code,match_status,match_confidence,matched_name,legal_dong_code,
             road_address,legal_address,household_count,building_count,approval_date,parking_count,
             heating_type,builder_name,management_type,area_summary,trade_month,trade_count,
             trade_min_price,trade_max_price,trade_avg_price,source_names,source_json,error_message,
             last_attempt_at,fetched_at,created_at,updated_at)
            VALUES (%s,%s,%s,'matched',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
                    '국토교통부 공동주택정보 · 아파트 실거래가',%s,NULL,NOW(),NOW(),NOW(),NOW())
            ON DUPLICATE KEY UPDATE kapt_code=VALUES(kapt_code),match_status='matched',match_confidence=VALUES(match_confidence),
             matched_name=VALUES(matched_name),legal_dong_code=VALUES(legal_dong_code),road_address=VALUES(road_address),
             legal_address=VALUES(legal_address),household_count=VALUES(household_count),building_count=VALUES(building_count),
             approval_date=VALUES(approval_date),parking_count=VALUES(parking_count),heating_type=VALUES(heating_type),
             builder_name=VALUES(builder_name),management_type=VALUES(management_type),area_summary=VALUES(area_summary),
             trade_month=VALUES(trade_month),trade_count=VALUES(trade_count),trade_min_price=VALUES(trade_min_price),
             trade_max_price=VALUES(trade_max_price),trade_avg_price=VALUES(trade_avg_price),source_names=VALUES(source_names),
             source_json=VALUES(source_json),error_message=NULL,last_attempt_at=NOW(),fetched_at=NOW(),updated_at=NOW()""",
            (row["site_id"], row["complex_key"], kapt_code, confidence * 100,
             field(merged, "kaptName", "aptNm"), b_code[:10], field(merged, "doroJuso", "roadAddress"),
             field(merged, "kaptAddr", "legalAddress"), number(field(merged, "kaptdaCnt", "householdCount")),
             number(field(merged, "kaptDongCnt", "buildingCount")), field(merged, "kaptUsedate", "useApprovalDate"),
             number(field(merged, "kaptdPcnt", "parkingCount")), field(merged, "codeHeatNm", "heatingType"),
             field(merged, "kaptBcompany", "builderName"), field(merged, "codeMgrNm", "managementType"),
             area_summary, trades["trade_month"], trades["trade_count"], trades["trade_min_price"],
             trades["trade_max_price"], trades["trade_avg_price"], source_json))
    conn.commit()
    return "matched"


def refresh_complex_public_data(conn, realtor_id=None, limit=20, force=False):
    service_key = public_data_service_key()
    if not service_key:
        print("[MULTI PUBLIC API SKIP] 환경변수 또는 config/public_api_keys.json에 DATA_GO_KR_SERVICE_KEY가 없습니다.")
        return []
    results = []
    for row in active_complexes(conn, realtor_id=realtor_id, limit=limit, force=force):
        try:
            status = update_one(conn, row, service_key)
            print(f"[MULTI PUBLIC API] site_id={row['site_id']} complex={row['complex_name']} status={status}")
            results.append(status)
        except Exception as exc:
            conn.rollback()
            try:
                with conn.cursor() as cur:
                    cur.execute("""INSERT INTO multi_site_complex_public_data
                        (site_id,complex_key,match_status,legal_dong_code,error_message,last_attempt_at,created_at,updated_at)
                        VALUES (%s,%s,'error',%s,%s,NOW(),NOW(),NOW())
                        ON DUPLICATE KEY UPDATE match_status='error',legal_dong_code=VALUES(legal_dong_code),
                         error_message=VALUES(error_message),last_attempt_at=NOW(),updated_at=NOW()""",
                        (row["site_id"], row["complex_key"], clean(row.get("b_code"))[:20] or None, str(exc)[:1000]))
                conn.commit()
            except Exception:
                conn.rollback()
            print(f"[MULTI PUBLIC API WARNING] complex={row['complex_name']} error={exc}")
            results.append("error")
    return results


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--realtor-id", type=int)
    parser.add_argument("--limit", type=int, default=20)
    parser.add_argument("--force", action="store_true")
    args = parser.parse_args()
    from db import get_conn
    conn = get_conn()
    try:
        refresh_complex_public_data(conn, args.realtor_id, args.limit, args.force)
    finally:
        conn.close()


if __name__ == "__main__":
    main()