#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""공개 매물을 기준으로 중개사별 현재 영업지역·단지 스냅샷을 갱신한다."""

import hashlib
import re
import uuid


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


def normalize_address(value):
    parts = clean(value).split()
    output = []
    for part in parts:
        if part and (not output or output[-1] != part):
            output.append(part)
    return " ".join(output)


def region_scope(value):
    address = normalize_address(value)
    parts = address.split()
    province = parts[0] if parts else ""
    province = {
        "서울시":"서울특별시", "부산시":"부산광역시", "대구시":"대구광역시",
        "인천시":"인천광역시", "광주시":"광주광역시", "대전시":"대전광역시",
        "울산시":"울산광역시", "세종시":"세종특별자치시",
    }.get(province, province)
    district = ""
    neighborhood = ""
    for part in parts[1:6]:
        if not district and re.search(r"(?:시|군|구)$", part):
            district = part
            continue
        if not neighborhood and re.search(r"(?:읍|면|동|가|리)$", part):
            neighborhood = part
            break
    if province.startswith("세종"):
        district = ""
    label = " ".join(x for x in (province, district, neighborhood) if x) or address
    key = re.sub(r"[^0-9a-z가-힣]+", "-", label.lower()).strip("-")
    return {
        "region_key": key[:160], "region_name": label[:255],
        "province_name": province[:80], "district_name": district[:100],
        "neighborhood_name": neighborhood[:100],
    }


def scope_address(normalized, lookup):
    normalized = normalize_address(normalized)
    lookup = normalize_address(lookup)
    if re.search(r"(?:읍|면|동|가|리)(?:\s|$)", normalized):
        return normalized
    if re.search(r"(?:읍|면|동|가|리)(?:\s|$)", lookup):
        return lookup
    return normalized or lookup


def _active_sites(conn, realtor_id=None):
    sql = """SELECT s.id AS site_id,s.realtor_id FROM multi_sites s
             INNER JOIN blog_realtors r ON r.id=s.realtor_id
             WHERE s.status='active' AND r.status='active' AND COALESCE(r.is_deleted,0)=0"""
    params = []
    if realtor_id:
        sql += " AND s.realtor_id=%s"
        params.append(int(realtor_id))
    with conn.cursor() as cur:
        cur.execute(sql, params)
        return cur.fetchall() or []


def _site_listings(conn, site_id):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT ma.id,ma.title,ma.primary_image_url,ma.published_at,
                   a.building_name,a.real_estate_type,a.trade_type,a.price_text,a.area_info,
                   l.normalized_address,l.lookup_address
              FROM multi_articles ma
              INNER JOIN blog_realtor_articles a
                ON a.realtor_id=ma.realtor_id AND BINARY a.article_no=BINARY ma.source_article_no
              LEFT JOIN multi_article_locations l
                ON l.article_id=ma.id AND l.geocode_status='success'
             WHERE ma.site_id=%s AND ma.status='published'
               AND COALESCE(a.article_status,'active') NOT IN ('removed','closed')
             ORDER BY ma.published_at DESC,ma.id DESC
        """, (site_id,))
        return cur.fetchall() or []


def _site_nearby_places(conn, site_id):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT p.*,ma.id AS source_article_id,
                   l.normalized_address,l.lookup_address
              FROM multi_article_nearby_places p
              INNER JOIN multi_articles ma ON ma.id=p.article_id
              LEFT JOIN multi_article_locations l
                ON l.article_id=ma.id AND l.geocode_status='success'
              INNER JOIN blog_realtor_articles a
                ON a.realtor_id=ma.realtor_id AND BINARY a.article_no=BINARY ma.source_article_no
             WHERE ma.site_id=%s AND ma.status='published'
               AND COALESCE(a.article_status,'active') NOT IN ('removed','closed')
             ORDER BY p.distance_m ASC,p.id ASC
        """, (site_id,))
        return cur.fetchall() or []


def refresh_one_site(conn, site_id, realtor_id):
    token = uuid.uuid4().hex
    rows = _site_listings(conn, site_id)
    regions = {}
    complexes = {}
    article_complex = {}
    for row in rows:
        scope = region_scope(scope_address(row.get("normalized_address"), row.get("lookup_address")))
        region_key = scope["region_key"]
        if not region_key:
            continue
        region = regions.setdefault(region_key, dict(scope, active_listing_count=0, complexes=set(), latest_at=row.get("published_at")))
        region["active_listing_count"] += 1
        name = clean(row.get("building_name"))
        title_name = clean(str(row.get("title") or "").split("·", 1)[0])
        title_complex = re.sub(r"\s+\d{1,4}동$", "", title_name).strip()
        if title_complex and (not name or re.fullmatch(r"\d{1,4}동", name) or len(title_complex) > len(name)):
            name = title_complex
        if not name:
            continue
        complex_key = hashlib.sha1(f"{region_key}|{name.lower()}".encode("utf-8")).hexdigest()
        region["complexes"].add(complex_key)
        item = complexes.setdefault(complex_key, {
            "complex_key": complex_key, "complex_name": name[:255], **scope,
            "active_listing_count": 0, "sale_count": 0, "jeonse_count": 0, "monthly_count": 0,
            "representative_article_id": int(row["id"]),
            "representative_image_url": clean(row.get("primary_image_url"))[:700],
            "latest_at": row.get("published_at"),
            "property_types": set(), "areas": set(), "prices": set(),
            "nearby": {"school":set(), "subway":set(), "hospital":set(), "mart":set()},
        })
        article_complex[int(row["id"])] = complex_key
        item["active_listing_count"] += 1
        trade = clean(row.get("trade_type"))
        if "매매" in trade:
            item["sale_count"] += 1
        elif "전세" in trade:
            item["jeonse_count"] += 1
        elif "월세" in trade or "단기" in trade:
            item["monthly_count"] += 1
        if not item["representative_image_url"] and row.get("primary_image_url"):
            item["representative_image_url"] = clean(row.get("primary_image_url"))[:700]
        if clean(row.get("real_estate_type")):
            item["property_types"].add(clean(row.get("real_estate_type")))
        if clean(row.get("area_info")):
            item["areas"].add(clean(row.get("area_info")))
        if clean(row.get("price_text")):
            item["prices"].add(clean(row.get("price_text")))

    places = {}
    for place in _site_nearby_places(conn, site_id):
        scope = region_scope(scope_address(place.get("normalized_address"), place.get("lookup_address")))
        region_key = scope["region_key"]
        place_id = clean(place.get("place_id"))
        group = clean(place.get("place_group"))
        if not region_key or not place_id or group not in {"school", "subway", "hospital", "mart"}:
            continue
        key = (region_key, group, place_id)
        if key not in places or int(place.get("distance_m") or 0) < int(places[key].get("distance_m") or 0):
            places[key] = dict(place, **scope)
        complex_key = article_complex.get(int(place.get("source_article_id") or 0))
        if complex_key and complex_key in complexes:
            complexes[complex_key]["nearby"][group].add(place_id)

    if rows and not regions:
        raise RuntimeError("현재 공개 매물의 지역 좌표가 없어 기존 영업지역 스냅샷을 유지합니다.")

    with conn.cursor() as cur:
        for item in regions.values():
            complex_count = len(item["complexes"])
            score = item["active_listing_count"] * 100 + complex_count * 15
            cur.execute("""
                INSERT INTO multi_site_regions
                (site_id,region_key,region_name,province_name,district_name,neighborhood_name,
                 active_listing_count,complex_count,priority_score,status,refresh_token,last_seen_at,created_at,updated_at)
                VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,'active',%s,NOW(),NOW(),NOW())
                ON DUPLICATE KEY UPDATE region_name=VALUES(region_name),province_name=VALUES(province_name),
                 district_name=VALUES(district_name),neighborhood_name=VALUES(neighborhood_name),
                 active_listing_count=VALUES(active_listing_count),complex_count=VALUES(complex_count),
                 priority_score=VALUES(priority_score),status='active',refresh_token=VALUES(refresh_token),
                 last_seen_at=NOW(),updated_at=NOW()
            """, (site_id,item["region_key"],item["region_name"],item["province_name"],item["district_name"],
                  item["neighborhood_name"],item["active_listing_count"],complex_count,score,token))
        for item in complexes.values():
            score = item["active_listing_count"] * 100
            cur.execute("""
                INSERT INTO multi_site_complexes
                (site_id,complex_key,complex_name,region_key,region_name,active_listing_count,
                 sale_count,jeonse_count,monthly_count,representative_article_id,representative_image_url,
                 priority_score,status,refresh_token,last_seen_at,created_at,updated_at)
                VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,'active',%s,NOW(),NOW(),NOW())
                ON DUPLICATE KEY UPDATE complex_name=VALUES(complex_name),region_key=VALUES(region_key),
                 region_name=VALUES(region_name),active_listing_count=VALUES(active_listing_count),
                 sale_count=VALUES(sale_count),jeonse_count=VALUES(jeonse_count),monthly_count=VALUES(monthly_count),
                 representative_article_id=VALUES(representative_article_id),
                 representative_image_url=VALUES(representative_image_url),priority_score=VALUES(priority_score),
                 status='active',refresh_token=VALUES(refresh_token),last_seen_at=NOW(),updated_at=NOW()
            """, (site_id,item["complex_key"],item["complex_name"],item["region_key"],item["region_name"],
                  item["active_listing_count"],item["sale_count"],item["jeonse_count"],item["monthly_count"],
                  item["representative_article_id"],item["representative_image_url"] or None,score,token))
            cur.execute("""
                INSERT INTO multi_site_complex_details
                (site_id,complex_key,property_type,area_summary,price_summary,
                 school_count,subway_count,hospital_count,mart_count,status,refresh_token,created_at,updated_at)
                VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,'active',%s,NOW(),NOW())
                ON DUPLICATE KEY UPDATE property_type=VALUES(property_type),area_summary=VALUES(area_summary),
                 price_summary=VALUES(price_summary),school_count=VALUES(school_count),
                 subway_count=VALUES(subway_count),hospital_count=VALUES(hospital_count),mart_count=VALUES(mart_count),
                 status='active',refresh_token=VALUES(refresh_token),updated_at=NOW()
            """, (site_id,item["complex_key"]," · ".join(sorted(item["property_types"]))[:80] or None,
                  " · ".join(sorted(item["areas"]))[:500] or None," · ".join(sorted(item["prices"]))[:500] or None,
                  len(item["nearby"]["school"]),len(item["nearby"]["subway"]),
                  len(item["nearby"]["hospital"]),len(item["nearby"]["mart"]),token))
        for item in places.values():
            cur.execute("""
                INSERT INTO multi_site_region_places
                (site_id,region_key,region_name,place_group,place_id,place_name,category_name,
                 address_name,road_address_name,distance_m,place_url,latitude,longitude,
                 source_article_id,status,refresh_token,last_seen_at,created_at,updated_at)
                VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,'active',%s,NOW(),NOW(),NOW())
                ON DUPLICATE KEY UPDATE region_name=VALUES(region_name),place_name=VALUES(place_name),
                 category_name=VALUES(category_name),address_name=VALUES(address_name),
                 road_address_name=VALUES(road_address_name),distance_m=VALUES(distance_m),
                 place_url=VALUES(place_url),latitude=VALUES(latitude),longitude=VALUES(longitude),
                 source_article_id=VALUES(source_article_id),status='active',refresh_token=VALUES(refresh_token),
                 last_seen_at=NOW(),updated_at=NOW()
            """, (site_id,item["region_key"],item["region_name"],item["place_group"],item["place_id"],
                  clean(item.get("place_name"))[:255],clean(item.get("category_name"))[:500],
                  clean(item.get("address_name"))[:500],clean(item.get("road_address_name"))[:500],
                  int(item.get("distance_m") or 0),clean(item.get("place_url"))[:700] or None,
                  item.get("latitude"),item.get("longitude"),item.get("source_article_id"),token))
        for article_id, complex_key in article_complex.items():
            cur.execute("""
                INSERT INTO multi_site_complex_articles
                (site_id,complex_key,article_id,status,refresh_token,created_at,updated_at)
                VALUES (%s,%s,%s,'active',%s,NOW(),NOW())
                ON DUPLICATE KEY UPDATE status='active',refresh_token=VALUES(refresh_token),updated_at=NOW()
            """, (site_id,complex_key,article_id,token))
        cur.execute("UPDATE multi_site_regions SET status='active',active_listing_count=0,complex_count=0,updated_at=NOW() WHERE site_id=%s AND COALESCE(refresh_token,'')<>%s AND is_pinned=1", (site_id,token))
        cur.execute("UPDATE multi_site_complexes SET status='active',active_listing_count=0,updated_at=NOW() WHERE site_id=%s AND COALESCE(refresh_token,'')<>%s AND is_pinned=1", (site_id,token))
        cur.execute("UPDATE multi_site_regions SET status='inactive',active_listing_count=0,complex_count=0,updated_at=NOW() WHERE site_id=%s AND COALESCE(refresh_token,'')<>%s AND is_pinned=0", (site_id,token))
        cur.execute("UPDATE multi_site_complexes SET status='inactive',active_listing_count=0,updated_at=NOW() WHERE site_id=%s AND COALESCE(refresh_token,'')<>%s AND is_pinned=0", (site_id,token))
        cur.execute("UPDATE multi_site_region_places SET status='inactive',updated_at=NOW() WHERE site_id=%s AND COALESCE(refresh_token,'')<>%s", (site_id,token))
        cur.execute("UPDATE multi_site_complex_details SET status='inactive',updated_at=NOW() WHERE site_id=%s AND COALESCE(refresh_token,'')<>%s", (site_id,token))
        cur.execute("UPDATE multi_site_complex_articles SET status='inactive',updated_at=NOW() WHERE site_id=%s AND COALESCE(refresh_token,'')<>%s", (site_id,token))
    conn.commit()
    return len(rows), len(regions), len(complexes)


def refresh_site_area_snapshots(conn, realtor_id=None):
    results = []
    for site in _active_sites(conn, realtor_id):
        site_id = int(site["site_id"])
        current_realtor_id = int(site["realtor_id"])
        try:
            listing_count, region_count, complex_count = refresh_one_site(conn, site_id, current_realtor_id)
            with conn.cursor() as cur:
                cur.execute("""INSERT INTO multi_area_refresh_runs
                    (site_id,realtor_id,status,region_count,complex_count,listing_count,started_at,finished_at,created_at)
                    VALUES (%s,%s,'completed',%s,%s,%s,NOW(),NOW(),NOW())""",
                    (site_id,current_realtor_id,region_count,complex_count,listing_count))
            conn.commit()
            results.append({"site_id":site_id,"status":"completed","regions":region_count,"complexes":complex_count})
            print(f"[MULTI AREA UPDATED] site_id={site_id} listings={listing_count} regions={region_count} complexes={complex_count}")
        except Exception as exc:
            conn.rollback()
            try:
                with conn.cursor() as cur:
                    cur.execute("""INSERT INTO multi_area_refresh_runs
                        (site_id,realtor_id,status,error_message,started_at,finished_at,created_at)
                        VALUES (%s,%s,'failed',%s,NOW(),NOW(),NOW())""", (site_id,current_realtor_id,str(exc)[:2000]))
                conn.commit()
            except Exception:
                conn.rollback()
            results.append({"site_id":site_id,"status":"failed","error":str(exc)})
            print(f"[MULTI AREA ERROR] site_id={site_id} error={exc}")
    return results