# -*- coding: utf-8 -*-

import os
import sys
import json
import random
import argparse
import re
import requests
import socket
import traceback
from datetime import datetime

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.append(BASE_DIR)

from db import get_conn


# ---------------------------------------------------------
# STEP29 OPERATION LOCK / PIPELINE LOG HELPERS
# - 기존 성공 흐름은 유지하고, DB 작업락/로그만 보강한다.
# - blog_app_locks 구조: lock_name, locked_at, expires_at, owner_token
# - JSON 타입 미지원 환경을 고려해 meta_json/context_json에는 문자열 저장
# ---------------------------------------------------------

def _safe_json_dumps(data):
    try:
        return json.dumps(data or {}, ensure_ascii=False, default=str)
    except Exception:
        return "{}"


def _worker_token(prefix):
    try:
        host = socket.gethostname()
    except Exception:
        host = "unknown-host"
    return f"{prefix}:{host}:{os.getpid()}:{datetime.now().strftime('%Y%m%d%H%M%S')}"


def acquire_db_lock(lock_name, ttl_minutes=180, owner_token=None):
    """
    DB 기반 작업락.
    - 만료된 락은 제거
    - 같은 lock_name이 살아 있으면 실행하지 않음
    - 실패해도 예외를 밖으로 던지지 않고 False 반환
    """
    owner_token = owner_token or _worker_token(lock_name)
    conn = None

    try:
        conn = get_conn()

        with conn.cursor() as cur:
            cur.execute("""
                DELETE FROM blog_app_locks
                WHERE expires_at < NOW()
            """)

            cur.execute("""
                INSERT INTO blog_app_locks
                (
                    lock_name,
                    locked_at,
                    expires_at,
                    owner_token
                )
                VALUES
                (
                    %s,
                    NOW(),
                    DATE_ADD(NOW(), INTERVAL %s MINUTE),
                    %s
                )
            """, (
                str(lock_name)[:100],
                int(ttl_minutes),
                str(owner_token)[:100],
            ))

        conn.commit()
        print(f"[DB LOCK ACQUIRED] {lock_name} owner={owner_token}")
        return True, owner_token

    except Exception as e:
        try:
            if conn:
                conn.rollback()
        except Exception:
            pass

        print(f"[DB LOCKED OR ERROR] {lock_name} / {e}")
        return False, owner_token

    finally:
        try:
            if conn:
                conn.close()
        except Exception:
            pass


def release_db_lock(lock_name, owner_token):
    conn = None

    try:
        conn = get_conn()

        with conn.cursor() as cur:
            cur.execute("""
                DELETE FROM blog_app_locks
                WHERE lock_name = %s
                  AND owner_token = %s
            """, (
                str(lock_name)[:100],
                str(owner_token)[:100],
            ))

            affected = cur.rowcount

        conn.commit()
        print(f"[DB LOCK RELEASED] {lock_name} affected={affected}")

    except Exception as e:
        print(f"[DB LOCK RELEASE ERROR] {lock_name} / {e}")

    finally:
        try:
            if conn:
                conn.close()
        except Exception:
            pass


def create_pipeline_run(run_type, message="", meta=None):
    conn = None

    try:
        conn = get_conn()

        with conn.cursor() as cur:
            cur.execute("""
                INSERT INTO blog_pipeline_runs
                (
                    run_type,
                    status,
                    started_at,
                    message,
                    meta_json,
                    created_at
                )
                VALUES
                (
                    %s,
                    'running',
                    NOW(),
                    %s,
                    %s,
                    NOW()
                )
            """, (
                str(run_type)[:50],
                str(message or "")[:2000],
                _safe_json_dumps(meta or {}),
            ))

            run_id = cur.lastrowid

        conn.commit()
        print(f"[PIPELINE RUN START] run_id={run_id}, run_type={run_type}")
        return run_id

    except Exception as e:
        print(f"[PIPELINE RUN START ERROR] {run_type} / {e}")
        return None

    finally:
        try:
            if conn:
                conn.close()
        except Exception:
            pass


def finish_pipeline_run(run_id, status, total=0, success=0, failed=0, skipped=0, message="", meta=None):
    if not run_id:
        return

    conn = None

    try:
        conn = get_conn()

        with conn.cursor() as cur:
            cur.execute("""
                UPDATE blog_pipeline_runs
                SET
                    status = %s,
                    finished_at = NOW(),
                    total_count = %s,
                    success_count = %s,
                    fail_count = %s,
                    skipped_count = %s,
                    message = %s,
                    meta_json = %s
                WHERE id = %s
            """, (
                str(status)[:30],
                int(total or 0),
                int(success or 0),
                int(failed or 0),
                int(skipped or 0),
                str(message or "")[:2000],
                _safe_json_dumps(meta or {}),
                int(run_id),
            ))

        conn.commit()
        print(f"[PIPELINE RUN FINISH] run_id={run_id}, status={status}")

    except Exception as e:
        print(f"[PIPELINE RUN FINISH ERROR] run_id={run_id} / {e}")

    finally:
        try:
            if conn:
                conn.close()
        except Exception:
            pass


def add_pipeline_log(run_id=None, level="info", step_name="", message="", article_no=None, realtor_id=None, context=None):
    conn = None

    try:
        conn = get_conn()

        with conn.cursor() as cur:
            cur.execute("""
                INSERT INTO blog_pipeline_logs
                (
                    run_id,
                    level,
                    step_name,
                    article_no,
                    realtor_id,
                    message,
                    context_json,
                    created_at
                )
                VALUES
                (
                    %s,
                    %s,
                    %s,
                    %s,
                    %s,
                    %s,
                    %s,
                    NOW()
                )
            """, (
                run_id,
                str(level or "info")[:20],
                str(step_name or "")[:100],
                str(article_no or "")[:30] if article_no else None,
                realtor_id,
                str(message or "")[:2000],
                _safe_json_dumps(context or {}),
            ))

        conn.commit()

    except Exception as e:
        print(f"[PIPELINE LOG ERROR] {step_name} / {e}")

    finally:
        try:
            if conn:
                conn.close()
        except Exception:
            pass


AI_SHORTS_API_URL = "http://61.32.69.107:9000"
AI_REALESTATE_API_URL = "http://61.32.69.107:9100"

AI_SHORTS_API_KEY = "honghee-shorts-secret-2026"


from services.ollama_writer import generate_ai_sections
from services.blog_layout_blocks import build_blog_html
from services.extra_image_service import get_extra_images_if_needed
from services.hee_policy_service import log_policy_recommendation
from services.cafe24_cdn_service import upload_header_image_to_cafe24


def json_default(obj):
    if isinstance(obj, datetime):
        return obj.strftime("%Y-%m-%d %H:%M:%S")
    return str(obj)


def clean_text(value):
    return str(value or "").strip()


def collapse_duplicate_address_segments(value):
    """주소 안에서 연속 반복된 행정구역 토큰/묶음을 한 번만 남긴다."""
    tokens = clean_text(value).split()
    if len(tokens) < 2:
        return clean_text(value)

    # 예: "세종시 세종시", "대전시 유성구 대전시 유성구"
    # 긴 반복 묶음부터 제거하고, 더 이상 바뀌지 않을 때까지 반복한다.
    changed = True
    while changed:
        changed = False
        max_span = min(5, len(tokens) // 2)
        for span in range(max_span, 0, -1):
            i = 0
            while i + (span * 2) <= len(tokens):
                if tokens[i:i + span] == tokens[i + span:i + (span * 2)]:
                    del tokens[i + span:i + (span * 2)]
                    changed = True
                    continue
                i += 1
    return clean_text(" ".join(tokens))


_PROPERTY_TYPE_DISPLAY_MAP = {
    "A01": "아파트",
    "APT": "아파트",
}

_TRADE_TYPE_DISPLAY_MAP = {
    "A1": "매매",
    "B1": "전세",
    "B2": "월세",
}


def normalize_property_type_display(value):
    text = clean_text(value)
    if not text:
        return ""
    mapped = _PROPERTY_TYPE_DISPLAY_MAP.get(text.upper())
    if mapped:
        return mapped
    # 알 수 없는 네이버 내부 코드는 사용자 화면에 노출하지 않는다.
    if re.fullmatch(r"[A-Z]+\d+", text.upper()):
        return ""
    return text


def normalize_trade_type_display(value):
    text = clean_text(value)
    if not text:
        return ""
    mapped = _TRADE_TYPE_DISPLAY_MAP.get(text.upper())
    if mapped:
        return mapped
    if re.fullmatch(r"[A-Z]+\d+", text.upper()):
        return ""
    return text


def normalize_detail_display_types(detail):
    """네이버 내부 코드가 제목·본문·대표이미지 문구에 노출되지 않게 한다."""
    detail = dict(detail or {})
    raw = parse_json_safely(detail.get("raw_json"))
    article_detail = raw.get("articleDetail") or {}
    article_addition = raw.get("articleAddition") or {}

    property_candidates = [
        detail.get("real_estate_type"),
        detail.get("real_estate_type_name"),
        article_detail.get("realestateTypeName"),
        article_addition.get("articleRealEstateTypeName"),
        article_addition.get("realEstateTypeName"),
        article_detail.get("articleTypeCode"),
    ]
    trade_candidates = [
        detail.get("trade_type"),
        article_detail.get("tradeTypeName"),
        article_addition.get("tradeTypeName"),
    ]

    property_label = next((v for v in (normalize_property_type_display(x) for x in property_candidates) if v), "")
    trade_label = next((v for v in (normalize_trade_type_display(x) for x in trade_candidates) if v), "")
    if property_label:
        detail["real_estate_type"] = property_label
        detail["real_estate_type_name"] = property_label
    if trade_label:
        detail["trade_type"] = trade_label
    for key in (
        "address", "road_address", "article_address", "exposure_address",
        "complex_resolved_address", "region_name", "region",
    ):
        if detail.get(key):
            detail[key] = collapse_duplicate_address_segments(detail.get(key))
    return detail


# ---------------------------------------------------------
# STEP272 HOTFIX: null 계열 문자열 방어 및 비단지 매물명 fallback
# - 기존 정상 매물명은 변경하지 않는다.
# - 연립/다세대/단독 등 complex_name이 없는 매물만 보강한다.
# ---------------------------------------------------------

_NULL_LIKE_TEXTS = {
    "",
    "null",
    "none",
    "undefined",
    "nan",
    "nil",
    "-",
}


def clean_nullable_text(value):
    text = clean_text(value)

    if not text:
        return ""

    lowered = text.lower()

    if lowered in _NULL_LIKE_TEXTS:
        return ""

    # STEP273:
    # 네이버 원천값/DB 조합 과정에서 "null 2동", "None 101동"처럼
    # placeholder 뒤에 동 정보가 붙은 경우도 빈 제목으로 취급한다.
    if re.fullmatch(
        r"(?i)(?:null|none|undefined|nan|nil)(?:\s+\d{1,4}동)?",
        text,
    ):
        return ""

    # 문장 앞에 null 계열 토큰이 붙은 경우 토큰만 제거한다.
    text = re.sub(
        r"(?i)^(?:null|none|undefined|nan|nil)\s+",
        "",
        text,
    ).strip()

    # 토큰 제거 후 2동/101동처럼 동 정보만 남으면 제목으로 쓰지 않는다.
    if re.fullmatch(r"\d{1,4}동", text):
        return ""

    return text


def extract_locality_name_for_display(detail):
    """
    주소에서 가장 구체적인 읍/면/동/리/가 지역명을 찾는다.
    예: 대전시 대덕구 신탄진동 -> 신탄진동
    """
    detail = detail or {}

    candidates = [
        detail.get("dong_name"),
        detail.get("emd_name"),
        detail.get("region_name"),
        detail.get("address"),
        detail.get("road_address"),
        detail.get("exposure_address"),
        detail.get("address_info"),
    ]

    raw = parse_json_safely(detail.get("raw_json")) if "parse_json_safely" in globals() else {}
    if isinstance(raw, dict):
        article_detail = raw.get("articleDetail") or {}
        candidates.extend([
            article_detail.get("exposureAddress"),
            article_detail.get("roadAddress"),
            article_detail.get("detailAddress"),
        ])

    for value in candidates:
        text = clean_nullable_text(value)
        if not text:
            continue

        matches = re.findall(r"([가-힣A-Za-z0-9]+(?:동|리|가|읍|면))", text)
        for item in reversed(matches):
            if not re.fullmatch(r"\d{1,4}동", item):
                return item

    return ""


def resolve_property_display_name(detail, default="해당 매물"):
    """
    단지명이 없는 매물의 공통 표시명 생성.

    우선순위:
    article_name -> building_name -> complex_name -> article_title
    -> 지역명 + 매물유형 -> 매물유형 + 매물 -> default
    """
    detail = detail or {}

    for key in [
        "article_name",
        "building_name",
        "complex_name",
        "article_title",
        "article_complex_name",
        "complex_title",
    ]:
        value = clean_nullable_text(detail.get(key))
        if not value:
            continue

        value = normalize_duplicate_dong_text(value)

        if re.fullmatch(r"\d{1,4}동", value):
            continue

        return value

    real_estate_type = clean_nullable_text(
        detail.get("real_estate_type")
        or detail.get("real_estate_type_name")
        or detail.get("_raw_article_real_estate_type_name")
        or detail.get("_raw_realestate_type_name")
    )

    locality = extract_locality_name_for_display(detail)

    if locality and real_estate_type:
        return f"{locality} {real_estate_type}"

    if real_estate_type:
        return f"{real_estate_type} 매물"

    return default


def apply_property_display_name_hotfix(detail):
    """
    기존 정상 제목은 그대로 두고 null/빈 제목만 보강한다.
    이후 대표이미지, 본문, SEO, 키워드가 같은 값을 사용하게 한다.
    """
    detail = dict(detail or {})

    original_article_name = clean_text(detail.get("article_name"))
    current = clean_nullable_text(original_article_name)

    if current:
        detail["article_name"] = current
        return detail

    # article_name 자체가 placeholder라면 다른 이름 후보에서도 같은 값을 제외하고
    # 지역명 + 매물유형 fallback을 사용한다.
    detail["article_name"] = ""
    recovered = resolve_property_display_name(detail, default="부동산 매물")

    detail["article_name"] = recovered

    print(
        "[PROPERTY DISPLAY NAME HOTFIX]",
        detail.get("article_no"),
        "->",
        recovered,
    )

    return detail



def normalize_duplicate_dong_text(value):
    """
    106동 106동 같은 중복 동 표시를 제거한다.
    """
    value = clean_text(value)

    if not value:
        return ""

    value = re.sub(r"(\b\d{1,4}동)\s+\1\b", r"\1", value)

    parts = value.split()
    cleaned = []

    for part in parts:
        if cleaned and cleaned[-1] == part and re.search(r"\d+동$", part):
            continue
        cleaned.append(part)

    return " ".join(cleaned)


def normalize_adjacent_title_duplicates(value):
    """
    제목 안에서 바로 이어지는 동일 단어/문구를 한 번만 남긴다.

    예:
    - 나릿재마을2단지복합상가 나릿재마을2단지복합상가 -> 나릿재마을2단지복합상가
    - A B A B -> A B

    거래유형·가격처럼 ``·``로 분리된 서로 다른 제목 요소는 유지한다.
    """
    text = clean_text(value)
    if not text:
        return ""

    def dedupe_segment(segment):
        tokens = re.sub(r"\s+", " ", segment).strip().split(" ")
        tokens = [token for token in tokens if token]

        changed = True
        while changed and len(tokens) >= 2:
            changed = False
            # 긴 반복 문구부터 제거해야 ``A B A B``도 한 번에 정리된다.
            for block_len in range(len(tokens) // 2, 0, -1):
                for start in range(0, len(tokens) - (block_len * 2) + 1):
                    first = tokens[start:start + block_len]
                    second = tokens[start + block_len:start + (block_len * 2)]
                    if first == second:
                        del tokens[start + block_len:start + (block_len * 2)]
                        changed = True
                        break
                if changed:
                    break

        return " ".join(tokens)

    parts = re.split(r"(\s*·\s*)", text)
    normalized = []
    for part in parts:
        if "·" in part:
            normalized.append(" · ")
        else:
            normalized.append(dedupe_segment(part))

    return re.sub(r"\s*·\s*", " · ", "".join(normalized)).strip()


def remove_trailing_dong_for_sentence(value):
    """
    본문 첫 문장에서는 106동 같은 동 정보가 반복되면 기계적으로 보이므로 제거한다.
    예: 해밀1단지마스터힐스 106동 -> 해밀1단지마스터힐스
    """
    value = normalize_duplicate_dong_text(value)
    value = re.sub(r"\s+\d{1,4}동\s*$", "", value).strip()
    return value


def format_floor_text_for_display(value):
    """
    9/16 -> 9층 / 총 16층
    이미 사람이 읽기 좋은 형식이면 그대로 둔다.
    """
    value = clean_text(value)

    if not value:
        return ""

    m = re.match(r"^\s*(\d+)\s*/\s*(\d+)\s*$", value)
    if m:
        return f"{m.group(1)}층 / 총 {m.group(2)}층"

    m = re.match(r"^\s*(\d+)층\s*/\s*(\d+)층\s*$", value)
    if m:
        return f"{m.group(1)}층 / 총 {m.group(2)}층"

    return value.replace("층층", "층")



def extract_exclusive_area_text(value):
    """
    대표이미지에는 공급/전용 전체보다 전용면적만 보여주는 것이 썸네일 가독성이 좋다.
    예: 공급 78.71㎡ / 전용 59.4㎡ -> 전용 59.4㎡
    """
    value = clean_text(value)

    if not value:
        return ""

    m = re.search(r"전용\s*([0-9.]+\s*(?:㎡|m²|m2))", value, flags=re.IGNORECASE)
    if m:
        return "전용 " + m.group(1).replace("m2", "㎡").replace("m²", "㎡").strip()

    m = re.search(r"/\s*([0-9.]+\s*(?:㎡|m²|m2))", value, flags=re.IGNORECASE)
    if m:
        return "전용 " + m.group(1).replace("m2", "㎡").replace("m²", "㎡").strip()

    return value


def compact_price_text(value):
    """
    대표이미지 가격 박스 가독성을 위해 공백을 줄인다.
    5억 2,000 -> 5억2,000
    """
    value = clean_text(value)
    value = re.sub(r"(\d+억)\s+(\d)", r"\1\2", value)
    return value



def extract_naver_realtor_id_from_anywhere(detail):
    """
    중개사 전체매물 링크 생성을 위해 naver realtor id를 최대한 찾는다.
    articleDetail.realtorId / source_json.detail.articleDetail.realtorId 등 대응.
    """
    candidates = [
        detail.get("naver_realtor_id"),
        detail.get("realtor_id_text"),
        detail.get("naver_realtor_code"),
        detail.get("realtor_account"),
        detail.get("realtorId"),
    ]

    realtor_info = detail.get("realtor_info") or {}
    if isinstance(realtor_info, dict):
        candidates.extend([
            realtor_info.get("naver_realtor_id"),
            realtor_info.get("realtor_id_text"),
            realtor_info.get("realtorId"),
        ])

    for json_key in ["source_json", "raw_json"]:
        try:
            raw = detail.get(json_key)
            if isinstance(raw, str):
                raw = json.loads(raw or "{}")
            if not isinstance(raw, dict):
                continue

            # source_json.detail.articleDetail.realtorId
            source_detail = raw.get("detail") or {}
            if isinstance(source_detail, dict):
                article_detail = source_detail.get("articleDetail") or source_detail.get("article_detail") or {}
                if isinstance(article_detail, dict):
                    candidates.append(article_detail.get("realtorId"))

                candidates.append(source_detail.get("realtorId"))
                candidates.append(source_detail.get("naver_realtor_id"))

            # raw_json.articleDetail.realtorId
            article_detail = raw.get("articleDetail") or raw.get("article_detail") or {}
            if isinstance(article_detail, dict):
                candidates.append(article_detail.get("realtorId"))

            candidates.append(raw.get("realtorId"))

        except Exception:
            pass

    for value in candidates:
        value = clean_text(value)
        if value:
            return value

    return ""



def select_header_background_from_article_images(images):
    """
    대표이미지 배경 선택 정책 V2.

    운영 정책:
    - 같은 단지 여러 매물이 발행되므로 수집이미지 후보 중 랜덤 선택
    - 단, 평면도/지도/중개사/실내사진은 절대 대표 배경으로 사용하지 않음
    - category뿐 아니라 url/local_path/filename까지 검사
    - 수집 후보가 없으면 빈 값 반환 → shorts_ai_api.py에서 MYBOX 랜덤 fallback
    """
    if not images:
        return ""

    import random

    include_keywords = [
        "ground_gallery",
        "complex_photo",
        "complex",
        "building",
        "outside",
        "view",
        "facility",
        "landscape",
        "community",
        "entrance",
        "gate",
    ]

    exclude_keywords = [
        "floorplan",
        "floor_plan",
        "floor-plan",
        "groundplan",
        "ground_plan",
        "평면",
        "평면도",
        "plan",
        "layout",
        "realtor",
        "profile",
        "broker",
        "중개사",
        "map",
        "location_map",
        "지도",
        "inside",
        "interior",
        "room",
        "article",
    ]

    # URL/파일명 기준 제외. category가 잘못 저장된 경우까지 방어.
    exclude_url_keywords = [
        "/floorplan/",
        "\\floorplan\\",
        "floorplan",
        "floor_plan",
        "groundplan",
        "ground_plan",
        "plan_",
        "_plan",
        "layout",
        "/realtor/",
        "\\realtor\\",
        "realtor_",
        "profile",
        "broker",
        "/map/",
        "\\map\\",
        "map_",
        "location_map",
        "/inside/",
        "\\inside\\",
        "inside_",
        "interior",
    ]

    def text_of(row):
        return " ".join([
            clean_text(row.get("image_category")),
            clean_text(row.get("category")),
            clean_text(row.get("type")),
            clean_text(row.get("image_type")),
            clean_text(row.get("filename")),
            clean_text(row.get("local_path")),
            clean_text(row.get("image_url")),
            clean_text(row.get("original_url")),
            clean_text(row.get("cdn_url")),
            clean_text(row.get("url")),
        ]).lower()

    def get_cat(row):
        return clean_text(
            row.get("image_category")
            or row.get("category")
            or row.get("type")
            or row.get("image_type")
            or ""
        ).lower()

    def get_url(row):
        return clean_text(
            row.get("image_url")
            or row.get("original_url")
            or row.get("cdn_url")
            or row.get("local_path")
            or row.get("url")
            or ""
        )

    weighted_pool = []
    seen_urls = set()

    for row in images:
        cat = get_cat(row)
        url = get_url(row)
        combined = text_of(row)

        if not url.startswith("http"):
            continue

        if any(x in combined for x in exclude_keywords):
            print("[HEADER BG EXCLUDE]", cat, url)
            continue

        if any(x in url.lower() for x in exclude_url_keywords):
            print("[HEADER BG EXCLUDE URL]", cat, url)
            continue

        if not any(x in cat for x in include_keywords):
            # category가 비어있거나 애매한 이미지는 대표 배경 후보에서 제외
            continue

        if url in seen_urls:
            continue

        seen_urls.add(url)

        weight = 1

        if "ground_gallery" in cat:
            weight = 5
        elif "complex_photo" in cat or "complex" in cat:
            weight = 5
        elif "building" in cat or "outside" in cat or "entrance" in cat or "gate" in cat:
            weight = 4
        elif "view" in cat or "landscape" in cat:
            weight = 3
        elif "facility" in cat or "community" in cat:
            weight = 2

        weighted_pool.extend([url] * weight)

    if not weighted_pool:
        print("[HEADER BACKGROUND RANDOM POOL] empty -> mybox fallback")
        return ""

    selected = random.choice(weighted_pool)

    print("[HEADER BACKGROUND RANDOM POOL]", len(seen_urls), "unique /", len(weighted_pool), "weighted")
    print("[HEADER BACKGROUND RANDOM SELECTED]", selected)

    return selected




def normalize_middle_banner_image_url(detail):
    """
    중간배너는 네이버 포스팅 안정성을 위해 CSS background-image보다 <img>가 안전하다.
    이미 생성되어 저장된 banner URL이 있으면 우선 사용한다.
    없으면 빈 값 반환 → HTML fallback 사용.
    """
    candidates = [
        detail.get("middle_banner_image_url"),
        detail.get("realtor_banner_image_url"),
        detail.get("cta_banner_image_url"),
        detail.get("banner_image_url"),
        detail.get("middle_banner_cdn_url"),
        detail.get("realtor_banner_cdn_url"),
    ]

    banner_asset = detail.get("banner_asset") or {}
    if isinstance(banner_asset, dict):
        candidates.extend([
            banner_asset.get("cdn_url"),
            banner_asset.get("url"),
            banner_asset.get("image_url"),
        ])

    realtor_info = detail.get("realtor_info") or {}
    if isinstance(realtor_info, dict):
        candidates.extend([
            realtor_info.get("middle_banner_image_url"),
            realtor_info.get("realtor_banner_image_url"),
            realtor_info.get("cta_banner_image_url"),
            realtor_info.get("banner_image_url"),
        ])

    for url in candidates:
        url = clean_text(url)
        if url.startswith("http"):
            return url

    return ""


def build_realtor_listing_url(detail):
    """
    중개사 전체매물 CTA 링크 후보를 최대한 찾는다.
    직접 fin.land URL이 있으면 우선 사용하고,
    없으면 naver_realtor_id 기반 기본 중개사 페이지를 사용한다.
    """
    candidates = [
        detail.get("realtor_total_listing_url"),
        detail.get("total_listing_url"),
        detail.get("agency_listing_url"),
        detail.get("fin_land_url"),
        detail.get("realtor_page_url"),
        detail.get("office_url"),
    ]

    realtor_info = detail.get("realtor_info") or {}
    if isinstance(realtor_info, dict):
        candidates.extend([
            realtor_info.get("realtor_total_listing_url"),
            realtor_info.get("total_listing_url"),
            realtor_info.get("agency_listing_url"),
            realtor_info.get("fin_land_url"),
            realtor_info.get("realtor_page_url"),
        ])

    for url in candidates:
        url = clean_text(url)
        if url.startswith("http"):
            return url

    naver_realtor_id = extract_naver_realtor_id_from_anywhere(detail)

    if naver_realtor_id:
        return f"https://m.land.naver.com/agency/info/{naver_realtor_id}"

    return ""



def parse_json_safely(value):
    if isinstance(value, dict):
        return value

    value = str(value or "").strip()

    if not value:
        return {}

    try:
        return json.loads(value)
    except Exception:
        return {}


def nested_get_safe(obj, path, default=""):
    cur = obj
    for key in path:
        if not isinstance(cur, dict):
            return default
        cur = cur.get(key)
        if cur is None:
            return default
    return cur if cur not in [None] else default


def recursive_find_first_safe(obj, key_names):
    key_names = {str(k).lower() for k in key_names}

    if isinstance(obj, dict):
        for key, value in obj.items():
            if str(key).lower() in key_names and value not in [None, "", 0, "0", "-", []]:
                return value

        for value in obj.values():
            found = recursive_find_first_safe(value, key_names)
            if found not in [None, "", 0, "0", "-", []]:
                return found

    elif isinstance(obj, list):
        for item in obj:
            found = recursive_find_first_safe(item, key_names)
            if found not in [None, "", 0, "0", "-", []]:
                return found

    return ""


def enrich_detail_from_raw_json_for_property_type(detail):
    """
    blog_realtor_articles 컬럼이 비어 있어도 raw_json의 네이버 원본 필드를 이용해
    매물유형/설명/면적/주소 등 초안 핵심 정보를 보강한다.
    """
    detail = dict(detail or {})
    raw = parse_json_safely(detail.get("raw_json"))

    if not raw:
        return normalize_detail_display_types(detail)

    article_detail = raw.get("articleDetail") or {}
    article_addition = raw.get("articleAddition") or {}
    article_space = raw.get("articleSpace") or {}
    article_ground = raw.get("articleGround") or {}
    article_facility = raw.get("articleFacility") or {}

    def set_if_empty(key, *values):
        current = clean_text(detail.get(key))
        if current and current not in ["-", "해당없음"]:
            return
        for value in values:
            text = clean_text(value)
            if text and text not in ["-", "해당없음"]:
                detail[key] = text
                return

    set_if_empty(
        "real_estate_type",
        detail.get("real_estate_type"),
        article_detail.get("realestateTypeName"),
        article_addition.get("articleRealEstateTypeName"),
        article_addition.get("realEstateTypeName"),
        article_detail.get("buildingTypeName"),
        article_detail.get("articleName"),
    )
    set_if_empty(
        "real_estate_type_name",
        detail.get("real_estate_type_name"),
        article_detail.get("realestateTypeName"),
        article_addition.get("articleRealEstateTypeName"),
        article_addition.get("realEstateTypeName"),
    )
    set_if_empty("trade_type", detail.get("trade_type"), article_detail.get("tradeTypeName"), article_addition.get("tradeTypeName"))
    set_if_empty("price_text", detail.get("price_text"), article_addition.get("dealOrWarrantPrc"))

    # 토지 면적 보강
    ground_space = article_space.get("groundSpace")
    if ground_space not in [None, "", 0, "0"]:
        try:
            if float(ground_space) > 0:
                set_if_empty("area_info", f"대지면적 {float(ground_space):g}㎡")
        except Exception:
            pass

    article_realtor = raw.get("articleRealtor") or {}
    complex_display_address = extract_complex_display_address(raw, article_detail)
    if complex_display_address:
        print("[COMPLEX DISPLAY ADDRESS USED]", complex_display_address.replace("\\n", " / "))
    set_if_empty(
        "address",
        complex_display_address,
        article_detail.get("exposureAddress"),
    )
    set_if_empty("building_usage", article_detail.get("currentUsage"), article_detail.get("recommendUsage"))
    set_if_empty("road_condition", "진입도로 없음" if article_ground.get("roadYN") == "N" else "")
    set_if_empty("building_allowed", "건축허가 없음" if article_ground.get("buildingAllowedYN") == "N" else "")

    # 중개사 원문 설명은 최우선 보존
    set_if_empty(
        "article_feature_desc",
        detail.get("article_feature_desc"),
        article_detail.get("detailDescription"),
        article_detail.get("articleFeatureDescription"),
        article_addition.get("articleFeatureDesc"),
    )

    # API로 넘길 때도 접근 가능하도록 원본 주요 필드 보관
    detail["_raw_article_real_estate_type_name"] = clean_text(article_addition.get("articleRealEstateTypeName"))
    detail["_raw_realestate_type_name"] = clean_text(article_detail.get("realestateTypeName") or article_addition.get("realEstateTypeName"))
    detail["_raw_trade_building_type_code"] = clean_text(article_detail.get("tradeBuildingTypeCode"))
    detail["_raw_article_type_code"] = clean_text(article_detail.get("articleTypeCode") or article_addition.get("articleRealEstateTypeCode"))
    detail["_raw_realestate_type_code"] = clean_text(article_detail.get("realestateTypeCode") or article_addition.get("realEstateTypeCode"))

    return normalize_detail_display_types(detail)



def normalize_placeholder_article_detail_for_draft(row):
    """
    C3-36:
    상세수집 완료(detail_collected=1)인데 article_name이
    '네이버 부동산 후보 매물'로 남아 있으면 초안 대상에서 제외되는 문제가 있다.

    MariaDB JSON 함수에 의존하지 않고 Python에서 raw_json을 읽어
    article_name / real_estate_type / trade_type / price_text 등을 보강한다.
    """
    row = dict(row or {})

    try:
        row = enrich_detail_from_raw_json_for_property_type(row)
    except Exception as e:
        print("[C3-36 RAW ENRICH WARN]", row.get("article_no"), str(e))

    bad_titles = {
        "",
        "네이버 부동산 후보 매물",
        "네이버 부동산 현재 매물",
        "네이버 부동산 매물",
        "부동산 매물",
        "현재 매물",
        "추천 매물",
    }

    article_name = clean_text(row.get("article_name"))

    if article_name in bad_titles:
        raw = parse_json_safely(row.get("raw_json"))
        article_detail = raw.get("articleDetail") if isinstance(raw, dict) else {}
        article_addition = raw.get("articleAddition") if isinstance(raw, dict) else {}

        candidates = [
            article_detail.get("articleName") if isinstance(article_detail, dict) else "",
            article_addition.get("articleName") if isinstance(article_addition, dict) else "",
            article_detail.get("aptName") if isinstance(article_detail, dict) else "",
            article_detail.get("complexName") if isinstance(article_detail, dict) else "",
            article_addition.get("complexName") if isinstance(article_addition, dict) else "",
            row.get("building_name"),
            row.get("complex_name"),
        ]

        for value in candidates:
            value = clean_text(value)
            if value and value not in bad_titles:
                row["article_name"] = value
                print("[C3-36 ARTICLE NAME RECOVERED]", row.get("article_no"), value)
                break

    return row



def extract_complex_display_address(raw, article_detail=None):
    """
    운영 복구 정책:
    - 매물 소재지는 article exposureAddress보다 단지/complex 주소를 우선 사용한다.
    - 네이버 단지 정보에서 도로명/지번 주소가 있으면 함께 표기한다.
    """
    article_detail = article_detail or {}
    raw = raw or {}

    if isinstance(raw, str):
        raw = parse_json_safely(raw)

    if not isinstance(raw, dict):
        raw = {}

    road_keys = {
        "roadAddress", "roadAddressName", "road_address", "road_address_name",
        "roadNameAddress", "roadAddr", "road_addr", "newAddress",
        "new_address", "rnAddr", "roadBaseAddress",
    }

    jibun_keys = {
        "jibunAddress", "jibunAddressName", "jibun_address", "jibun_address_name",
        "address", "addressName", "oldAddress", "old_address", "lotAddress",
        "lot_address", "dongAddress", "bunjiAddress",
    }

    general_keys = {
        "complexAddress", "complex_address", "fullAddress", "full_address",
        "address", "addressName", "exposureAddress", "detailAddress",
    }

    complex_hint_keys = {
        "complex", "hscp", "overview", "complexInfo", "complexDetail",
        "complexOverview", "complexArticle", "danji", "단지"
    }

    road = ""
    jibun = ""
    general = ""

    def clean_addr(v):
        v = clean_text(v)
        if not v:
            return ""
        if "http://" in v or "https://" in v or "<" in v or ">" in v:
            return ""
        if len(v) < 4:
            return ""
        if v in ["아파트", "오피스텔", "주상복합"]:
            return ""
        return v

    def visit(obj, in_complex=False):
        nonlocal road, jibun, general

        if isinstance(obj, dict):
            local_complex = in_complex or any(str(k) in complex_hint_keys for k in obj.keys())

            for k, v in obj.items():
                key = str(k)

                if isinstance(v, (dict, list)):
                    visit(v, local_complex or any(h.lower() in key.lower() for h in complex_hint_keys))
                    continue

                if not local_complex:
                    continue

                value = clean_addr(v)
                if not value:
                    continue

                if not road and key in road_keys:
                    road = value
                elif not jibun and key in jibun_keys:
                    jibun = value
                elif not general and key in general_keys:
                    general = value

        elif isinstance(obj, list):
            for item in obj:
                visit(item, in_complex)

    preferred_roots = [
        raw.get("complex"),
        raw.get("complexInfo"),
        raw.get("complexDetail"),
        raw.get("complexOverview"),
        raw.get("complexOverviewResponse"),
        raw.get("complexArticle"),
        raw.get("complexAddress"),
        raw.get("hscp"),
        raw.get("hscpInfo"),
        raw.get("overview"),
        raw.get("articleComplex"),
    ]

    for root_obj in preferred_roots:
        if root_obj:
            visit(root_obj, True)

    if not (road or jibun or general):
        visit(raw, False)

    if isinstance(article_detail, dict):
        for key in road_keys:
            if not road and clean_addr(article_detail.get(key)):
                road = clean_addr(article_detail.get(key))

        for key in jibun_keys:
            if not jibun and clean_addr(article_detail.get(key)):
                jibun = clean_addr(article_detail.get(key))

    if road and jibun and road != jibun:
        return f"{road}\n지번 {jibun}"

    if road:
        return road

    if jibun:
        return jibun

    if general:
        return general

    return ""


def extract_broker_description(detail):
    """
    네이버 부동산의 중개사 매물설명 텍스트를 최대한 찾는다.
    사람 작성 느낌을 살리기 위한 핵심 원문이다.
    """
    candidates = [
        detail.get("article_feature_desc"),
        detail.get("articleFeatureDescription"),
        detail.get("detailDescription"),
        detail.get("description"),
    ]

    raw = parse_json_safely(detail.get("raw_json"))

    if raw:
        article_detail = raw.get("articleDetail") or {}
        candidates.extend([
            article_detail.get("articleFeatureDescription"),
            article_detail.get("detailDescription"),
            raw.get("detailDescription"),
        ])

    for item in candidates:
        text = clean_text(item)

        if not text:
            continue

        # 전화번호/상호만 있는 너무 짧은 설명은 제외
        if len(text.replace("\n", "").replace(" ", "")) < 15:
            continue

        return text

    return ""


def build_broker_description_comment(detail):
    """
    운영 복구 정책:
    - 네이버 부동산 중개사가 직접 작성한 매물설명을 최우선 사용한다.
    - AI/템플릿 문장으로 대체하지 않는다.
    - 최대 1000자까지 보존한다.
    - 줄바꿈/기호/전화번호 정도만 정리한다.
    """
    raw = extract_broker_description(detail)

    if not raw:
        return {
            "title": "",
            "content": "",
        }

    text = str(raw or "")
    text = text.replace("\r\n", "\n").replace("\r", "\n")

    # 장식 기호 정리
    text = re.sub(r"[★☆■□◆◇▶▷●○◎※]+", " ", text)
    text = re.sub(r"[-=]{3,}", "\n", text)
    text = re.sub(r"\*+", " ", text)

    # 연락처는 법정 표시/중개사 정보에 별도 노출되므로 설명 본문에서는 제거
    text = re.sub(r"\b0\d{1,2}-\d{3,4}-\d{4}\b", "", text)
    text = re.sub(r"\b010-\d{3,4}-\d{4}\b", "", text)
    text = re.sub(r"\b010\d{7,8}\b", "", text)

    lines = []
    for line in text.split("\n"):
        line = clean_text(line)
        line = line.strip("-·ㆍ*• ")

        if not line:
            continue

        # 너무 짧은 상호/전화 안내성 줄 제거
        if re.search(r"부동산|공인중개사|중개사무소", line) and len(line) <= 14:
            continue

        # 동일 줄 반복 제거
        if line not in lines:
            lines.append(line)

    if not lines:
        return {
            "title": "",
            "content": "",
        }

    paragraphs = []
    current = ""

    for line in lines:
        if len(current) + len(line) + 1 <= 180:
            current = (current + " " + line).strip()
        else:
            if current:
                paragraphs.append(current)
            current = line

    if current:
        paragraphs.append(current)

    content_parts = []
    total = 0

    for p in paragraphs:
        if total >= 1000:
            break

        remain = 1000 - total
        part = p[:remain].strip()

        if part:
            content_parts.append(part)
            total += len(part)

    content = "\n\n".join(content_parts).strip()

    if not content:
        return {
            "title": "",
            "content": "",
        }

    print("[BROKER DESCRIPTION RAW USED]", f"len={len(content)}")

    return {
        "title": "중개사 매물설명",
        "content": content,
    }


def pick_one(items):
    if not items:
        return ""
    return random.choice(items)


def pick_many(items, count=3):
    items = list(items or [])
    random.shuffle(items)
    return items[:count]


def normalize_floor_text(floor_info):
    floor_info = clean_text(floor_info)

    if not floor_info:
        return ""

    return floor_info.replace("층층", "층")


def detect_floor_type(floor_info):
    floor_info = clean_text(floor_info)

    if "/" not in floor_info:
        return ""

    try:
        current, total = floor_info.split("/", 1)
        current = int(str(current).replace("층", "").strip())
        total = int(str(total).replace("층", "").strip())

        if total <= 0:
            return ""

        ratio = current / total

        if ratio >= 0.7:
            return "high"

        if ratio <= 0.3:
            return "low"

        return "middle"

    except Exception:
        return ""


def detect_direction_type(direction):
    direction = clean_text(direction)

    if not direction:
        return ""

    if "남" in direction:
        return "south"

    if "동" in direction:
        return "east"

    if "서" in direction:
        return "west"

    if "북" in direction:
        return "north"

    return ""

DIRECTION_MAP = {
    "E": "동향",
    "W": "서향",
    "S": "남향",
    "N": "북향",

    "ES": "동남향",
    "SE": "남동향",

    "WS": "서남향",
    "SW": "남서향",

    "EN": "동북향",
    "NE": "북동향",

    "WN": "서북향",
    "NW": "북서향",
}


def convert_direction(direction_code):

    direction_code = clean_text(
        direction_code
    ).upper()

    if not direction_code:
        return ""

    return DIRECTION_MAP.get(
        direction_code,
        direction_code
    )


COMMENT_TITLES = [
    "현장 설명",
    "중개사 코멘트",
    "실제 매물 설명",
    "현장 체크 포인트",
]


COMMENT_OPENERS = [
    "중개사 설명 기준으로는",
    "현장 안내 내용을 보면",
    "실제 등록된 설명에서는",
    "대표 설명 기준으로는",
]


FEATURE_REWRITE_POOL = {

    "채광": [
        "채광 방향을 중요하게 보시는 분들께 참고가 될 수 있습니다.",
        "실내 채광 흐름을 함께 체크해보시면 좋겠습니다.",
    ],

    "남향": [
        "남향 기준이라 일조량 부분도 함께 참고해보시면 좋겠습니다.",
        "채광과 개방감을 중요하게 보시는 분들께 잘 맞을 수 있습니다.",
    ],

    "로얄": [
        "선호도 높은 라인과 층 조건을 함께 참고해보시면 좋겠습니다.",
    ],

    "뷰": [
        "개방감과 전망 부분도 함께 체크해보시면 좋겠습니다.",
    ],

    "수리": [
        "실내 컨디션과 관리 상태를 함께 참고해보시면 좋겠습니다.",
    ],

    "깨끗": [
        "실내 관리 상태가 비교적 안정적으로 유지된 느낌입니다.",
    ],
}


def split_feature_sentences(text):

    text = clean_text(text)

    if not text:
        return []

    separators = [
        "\n",
        ".",
        ",",
        "/",
        "·",
    ]

    items = [text]

    for sep in separators:

        temp = []

        for item in items:
            temp.extend(item.split(sep))

        items = temp

    result = []

    for item in items:

        item = clean_text(item)

        if len(item) < 2:
            continue

        if item in result:
            continue

        result.append(item)

    return result


def rewrite_feature_sentence(sentence):

    sentence = clean_text(sentence)

    if not sentence:
        return ""

    for keyword, pool in FEATURE_REWRITE_POOL.items():

        if keyword in sentence:
            return pick_one(pool)

    generic_pool = [
        f"{sentence} 부분을 함께 참고해보시면 좋겠습니다.",
        f"실제 현장에서는 {sentence} 부분도 체크해보시면 좋겠습니다.",
        f"공간 활용 측면에서 {sentence} 조건도 함께 확인해보시면 좋겠습니다.",
    ]

    return pick_one(generic_pool)


def build_feature_comment_block(detail):

    broker_comment = build_broker_description_comment(detail)

    if broker_comment.get("content"):
        return broker_comment

    raw_text = clean_text(
        detail.get("article_feature_desc")
    )

    if not raw_text:
        return {
            "title": "",
            "content": "",
        }

    items = split_feature_sentences(raw_text)

    if not items:
        return {
            "title": "",
            "content": "",
        }

    selected = pick_many(items, 3)

    lines = []

    opener = pick_one(COMMENT_OPENERS)

    for idx, item in enumerate(selected):

        line = rewrite_feature_sentence(item)

        if idx == 0:
            line = f"{opener} {line}"

        lines.append(line)

    return {
        "title": pick_one(COMMENT_TITLES),
        "content": " ".join(lines)
    }

def build_intro_text(detail):
    article_name = (
        remove_trailing_dong_for_sentence(detail.get("article_name"))
        or remove_trailing_dong_for_sentence(detail.get("building_name"))
        or remove_trailing_dong_for_sentence(detail.get("complex_name"))
        or "해당 매물"
    )

    trade_type = clean_text(detail.get("trade_type"))
    real_estate_type = clean_text(detail.get("real_estate_type"))
    price_text = clean_text(detail.get("price_text"))
    area_info = clean_text(detail.get("area_info"))
    floor_info = format_floor_text_for_display(detail.get("floor_info"))
    direction = convert_direction(
        detail.get("direction")
        or detail.get("direction_code")
    )

    openers = [
        f"이번에 소개해드리는 매물은 {article_name} 매매 물건입니다.",
        f"{article_name} 매물을 찾고 계셨다면 참고해보셔도 좋겠습니다.",
        f"실거주를 고려하시는 분들께 {article_name} 매물을 안내드립니다.",
        f"생활 편의성과 구조를 함께 보실 수 있는 {article_name} 매물입니다.",
        f"문의가 꾸준히 이어지는 타입의 {article_name} 매물입니다.",
        f"조건과 위치를 함께 비교해보기 좋은 {article_name} 매물입니다.",
    ]

    second_lines = []

    if trade_type and real_estate_type:
        second_lines += [
            f"{real_estate_type} {trade_type} 매물로 현재 조건을 기준으로 안내드립니다.",
            f"{trade_type} 조건으로 나온 {real_estate_type} 매물입니다.",
            f"{real_estate_type}을 찾는 분들께 참고가 될 만한 {trade_type} 매물입니다.",
        ]
    elif trade_type:
        second_lines += [
            f"{trade_type} 조건으로 확인되는 매물입니다.",
            f"현재 {trade_type} 기준으로 안내 가능한 매물입니다.",
        ]

    if price_text:
        second_lines += [
            f"가격은 {price_text}입니다.",
            f"현재 안내되는 가격 조건은 {price_text}입니다.",
            f"예산을 맞춰 비교해보실 때 {price_text} 조건을 기준으로 비교해보시면 좋습니다.",
        ]

    if area_info:
        second_lines += [
            f"면적은 {area_info}입니다.",
            f"{area_info} 구조라 공간 활용을 함께 확인해보시면 좋습니다.",
            f"실사용 면적과 동선을 함께 보기 좋은 {area_info} 타입입니다.",
        ]

    if direction:
        second_lines += [
            f"방향은 {direction}입니다.",
            f"{direction} 기준이라 채광과 개방감을 중요하게 보시는 분들께 잘 맞을 수 있습니다.",
            f"{direction} 방향 기준으로 일조량 부분도 함께 참고해보시면 좋겠습니다.",
            f"{direction} 방향 특성을 고려해 실내 분위기를 함께 체크해보시면 좋겠습니다.",
        ]

    lines = [
        pick_one(openers),
        pick_one(second_lines) if second_lines else "",
    ]

    return "\n".join([x for x in lines if x])


def build_feature_text(detail):
    floor_info = format_floor_text_for_display(detail.get("floor_info"))
    direction = clean_text(detail.get("direction"))
    area_info = clean_text(detail.get("area_info"))
    real_estate_type = clean_text(detail.get("real_estate_type"))
    feature = clean_text(detail.get("article_feature") or detail.get("feature"))
    address = clean_text(detail.get("address") or detail.get("road_address"))

    floor_type = detect_floor_type(floor_info)
    direction_type = detect_direction_type(direction)

    pool = [
        "전체적으로 생활 동선이 무난하게 잡혀 있어 실거주 관점에서 보기 좋은 매물입니다.",
        "사진과 조건을 함께 비교해보면 기본기가 잘 갖춰진 매물로 볼 수 있습니다.",
        "처음 집을 보시는 분들도 구조와 조건을 비교하기 쉬운 타입입니다.",
        "실제 거주를 고려하신다면 채광, 동선, 주변 환경을 함께 보시면 좋습니다.",
        "가격 조건과 내부 컨디션을 함께 비교해볼 만한 매물입니다.",
        "같은 단지 안에서도 층수와 방향에 따라 체감이 달라질 수 있어 직접 확인을 추천드립니다.",
        "실사용 공간을 중요하게 보시는 분들께는 확인해보실 만한 매물입니다.",
        "주거 안정성과 생활 편의성을 함께 고려하시는 분들께 잘 맞을 수 있습니다.",
        "입주 일정과 조건만 맞는다면 충분히 문의해보실 만한 매물입니다.",
        "단순히 가격만 보기보다는 위치, 구조, 관리 상태를 함께 확인하시는 것이 좋습니다.",
        "실내 동선과 수납, 채광 조건을 함께 비교해보면 판단이 쉬운 매물입니다.",
        "주변 생활권과 단지 분위기를 함께 고려하면 장점이 더 잘 보이는 매물입니다.",
    ]

    if direction_type == "south":
        pool += [
            "남향 계열 방향이라 채광을 중요하게 보시는 분들께 특히 참고가 됩니다.",
            "햇빛이 들어오는 시간대와 거실 방향을 함께 확인해보시면 만족도가 높을 수 있습니다.",
            "채광을 선호하시는 분들에게는 장점으로 볼 수 있는 방향 조건입니다.",
        ]

    if floor_type == "high":
        pool += [
            "상대적으로 높은 층에 해당해 개방감과 조망감을 기대해볼 수 있습니다.",
            "고층 선호도가 있는 분들께는 우선적으로 검토해볼 만한 조건입니다.",
        ]
    elif floor_type == "middle":
        pool += [
            "중간층에 가까운 조건이라 생활 편의성과 안정감 사이의 균형을 기대할 수 있습니다.",
            "층수 부담이 크지 않아 실거주용으로 무난하게 검토하기 좋습니다.",
        ]
    elif floor_type == "low":
        pool += [
            "저층부에 가까워 이동 편의성을 중요하게 보시는 분들께 참고가 됩니다.",
            "엘리베이터 대기나 이동 동선을 줄이고 싶은 분들에게 맞을 수 있습니다.",
        ]

    if area_info:
        pool += [
            "면적 구성을 보면 가족 구성원 수와 생활 패턴에 맞춰 판단하기 좋습니다.",
            "공간을 어떻게 나누어 사용할지 상상해보며 보시면 더 도움이 됩니다.",
        ]

    if real_estate_type:
        pool += [
            f"{real_estate_type} 특성상 관리 상태와 주변 환경을 함께 확인하는 것이 중요합니다.",
            f"{real_estate_type}을 찾는 분들이 주로 보는 조건들을 기준으로 정리해볼 만합니다.",
        ]

    if feature:
        pool += [
            f"매물 특징으로는 {feature} 부분을 함께 참고하시면 좋습니다.",
            f"현장 확인 전에는 {feature} 내용을 먼저 체크해보시면 도움이 됩니다.",
        ]

    if address:
        pool += [
            "소재지 기준으로 주변 생활권과 이동 동선을 함께 확인해보시는 것을 추천드립니다.",
            "주소지를 기준으로 주변 편의시설과 교통 흐름을 함께 비교해보시면 좋습니다.",
        ]

    return "\n".join(pick_many(pool, 4))


def build_location_text(detail):
    address = clean_text(detail.get("address") or detail.get("road_address"))
    complex_name = clean_text(detail.get("complex_name") or detail.get("article_name"))

    pool = [
        "생활권은 실제 거주 만족도에 큰 영향을 주기 때문에 주변 편의시설과 이동 동선을 함께 확인해보시는 것이 좋습니다.",
        "주변 환경은 사진만으로 판단하기 어려운 부분이 있어 방문 시 단지 분위기와 도로 접근성을 함께 보시면 좋습니다.",
        "출퇴근 동선, 장보기, 병원, 학원 등 생활에 필요한 시설과의 거리를 함께 비교해보시면 판단이 쉬워집니다.",
        "단지 주변 분위기와 접근성은 실거주 만족도를 좌우하는 중요한 요소입니다.",
        "입지만 볼 때는 지도상 거리뿐 아니라 실제 이동 동선도 함께 확인하는 것이 좋습니다.",
        "주변 생활 인프라가 잘 맞는지 확인하면 장기 거주 관점에서도 도움이 됩니다.",
    ]

    if address:
        pool += [
            f"소재지는 {address}입니다.",
            f"{address} 생활권을 고려하시는 분들께 참고가 될 수 있는 매물입니다.",
        ]

    if complex_name:
        pool += [
            f"{complex_name} 주변 환경과 단지 분위기를 함께 비교해보시면 좋습니다.",
        ]

    return "\n".join(pick_many(pool, 3))


def build_space_text(detail):
    area_info = clean_text(detail.get("area_info"))
    floor_info = clean_text(detail.get("floor_info"))
    direction = clean_text(detail.get("direction"))

    pool = [
        "공간은 단순 면적보다 실제 동선과 가구 배치가 중요합니다.",
        "거실과 방의 배치, 수납 공간, 주방 동선을 함께 보시면 실제 사용감이 더 잘 보입니다.",
        "사진을 보실 때는 창 위치와 채광, 통풍 흐름을 함께 확인해보시면 좋습니다.",
        "같은 면적이라도 구조에 따라 체감 공간은 달라질 수 있습니다.",
        "실제 방문 시에는 거실 폭, 방 크기, 주방 동선을 중심으로 확인하시는 것을 추천드립니다.",
        "공간 활용도는 가족 구성원 수와 생활 패턴에 따라 다르게 느껴질 수 있습니다.",
        "수납과 동선이 잘 맞는지 확인하면 실제 거주 만족도를 판단하는 데 도움이 됩니다.",
    ]

    if area_info:
        pool += [
            f"면적은 {area_info} 기준으로, 실제 사용할 공간을 중심으로 보시면 좋습니다.",
            f"{area_info} 타입이라 방 구성과 거실 활용도를 함께 체크해보시면 좋습니다.",
        ]

    if floor_info:
        pool += [
            f"층수는 {floor_info} 기준으로 확인되며, 채광과 조망을 함께 비교해볼 수 있습니다.",
        ]

    if direction:
        pool += [
            f"방향은 {direction}으로 확인되며, 시간대별 채광을 함께 확인해보시면 좋습니다.",
        ]

    return "\n".join(pick_many(pool, 4))


def build_price_text_block(detail):
    price_text = clean_text(detail.get("price_text"))
    trade_type = clean_text(detail.get("trade_type"))

    pool = [
        "가격은 현재 시장 흐름과 주변 유사 매물 조건을 함께 비교해보시는 것이 좋습니다.",
        "매물 가격은 층수, 방향, 내부 상태, 입주 가능 시점에 따라 체감 가치가 달라질 수 있습니다.",
        "단순 가격만 보기보다는 조건 대비 만족도를 함께 확인하는 것이 중요합니다.",
        "같은 단지 안에서도 동, 층, 방향에 따라 가격 차이가 발생할 수 있습니다.",
        "예산 범위 안에서 구조와 위치가 잘 맞는지 함께 비교해보시면 좋습니다.",
        "현재 조건이 본인의 자금 계획과 맞는지 확인한 뒤 현장 방문을 잡는 것이 효율적입니다.",
    ]

    if price_text:
        pool += [
            f"현재 가격 조건은 {price_text}입니다.",
            f"{price_text} 조건을 기준으로 주변 매물과 함께 비교해보시면 좋습니다.",
        ]

    if trade_type:
        pool += [
            f"{trade_type} 매물은 조건 변경이 있을 수 있으므로 상담 전 현재 가능 여부를 확인하시는 것이 좋습니다.",
        ]

    return "\n".join(pick_many(pool, 3))


def build_school_text(detail):
    schools = detail.get("schools") or []

    if schools:
        lines = []

        for school in schools[:3]:
            school_name = clean_text(school.get("school_name"))
            school_type = clean_text(school.get("school_type"))
            distance = clean_text(school.get("distance_text") or school.get("distance"))

            if not school_name:
                continue

            label = " ".join([school_type, distance]).strip()

            if label:
                lines.append(f"인근 교육시설로는 {school_name}({label}) 정보를 참고하실 수 있습니다.")
            else:
                lines.append(f"인근 교육시설로는 {school_name} 정보를 참고하실 수 있습니다.")

        if lines:
            return "\n".join(lines)

    pool = [
        "학군과 교육환경은 가족 단위 실거주 수요에서 중요한 체크 포인트입니다.",
        "자녀 계획이 있으시다면 주변 학교와 학원 접근성을 함께 확인해보시면 좋습니다.",
        "교육환경은 실제 생활 동선과 밀접하므로 방문 전 지도 기준으로 한 번 더 확인을 추천드립니다.",
        "학교 배정과 통학 동선은 변동 가능성이 있어 상담 시 별도 확인이 필요합니다.",
    ]

    return "\n".join(pick_many(pool, 2))


def build_realtor_text(detail):
    info = detail.get("realtor_info") or {}
    office_name = clean_text(info.get("office_name"))

    pool = [
        "상담 시에는 현재 매물 가능 여부, 가격 조건, 입주 가능일을 먼저 확인하시면 좋습니다.",
        "방문 전 유선으로 매물 상태와 조건 변동 여부를 확인하시면 더 정확한 안내를 받을 수 있습니다.",
        "사진만으로 판단하기 어려운 부분은 현장 방문을 통해 직접 확인하시는 것이 가장 좋습니다.",
        "실제 매물 여부와 세부 조건은 상담 시점에 다시 확인해드리는 것이 안전합니다.",
    ]

    if office_name:
        pool += [
            f"{office_name}에서 현재 확인 가능한 조건을 기준으로 안내드립니다.",
            f"자세한 상담은 {office_name}을 통해 확인하실 수 있습니다.",
        ]

    return "\n".join(pick_many(pool, 3))


def build_closing_text(detail):
    pool = [
        "관심 있으신 분들은 현장 확인과 함께 자세한 상담을 받아보시길 권해드립니다.",
        "사진과 기본 정보만으로는 판단이 어려운 부분이 있으니 직접 확인해보시는 것을 추천드립니다.",
        "조건이 맞는 매물은 빠르게 변동될 수 있으니 문의 전 현재 가능 여부를 확인해 주세요.",
        "방문 상담을 통해 실제 구조와 주변 분위기를 함께 확인해보시면 좋습니다.",
        "궁금하신 점은 편하게 문의 주시면 현재 확인 가능한 내용 기준으로 안내드리겠습니다.",
        "매물 조건은 변동될 수 있으므로 상담 시점의 최신 정보를 기준으로 확인해 주세요.",
    ]

    return pick_one(pool)


def build_human_blog_context(detail_row):
    property_class = _hee_detect_property_class_for_layout(detail_row)

    if property_class in ("store", "office"):
        return build_non_residential_blog_context(detail_row, "commercial")
    if property_class == "factory_warehouse":
        return build_non_residential_blog_context(detail_row, "factory_warehouse")
    if property_class == "land":
        return build_non_residential_blog_context(detail_row, "land")

    intro_text = build_intro_text(detail_row)
    feature_text = build_feature_text(detail_row)
    location_text = build_location_text(detail_row)
    space_text = build_space_text(detail_row)
    price_text_block = build_price_text_block(detail_row)
    school_text = build_school_text(detail_row)
    realtor_text = build_realtor_text(detail_row)
    outro_text = build_closing_text(detail_row)

    return {
        "intro_text": intro_text,
        "feature_text": feature_text,
        "location_text": location_text,
        "space_text": space_text,
        "price_text_block": price_text_block,
        "school_text": school_text,
        "realtor_text": realtor_text,
        "outro_text": outro_text,
    }


def build_non_residential_blog_context(detail, property_class):
    name = clean_text(
        detail.get("article_name")
        or detail.get("building_name")
        or detail.get("complex_name")
        or "해당 매물"
    )
    kind = clean_text(
        detail.get("real_estate_type")
        or detail.get("real_estate_type_name")
    )
    trade = clean_text(detail.get("trade_type"))
    price = clean_text(detail.get("price_text"))
    area = clean_text(detail.get("area_info"))
    floor = format_floor_text_for_display(detail.get("floor_info"))
    address = clean_text(detail.get("address") or detail.get("road_address"))
    desc = clean_text(
        detail.get("article_feature_desc")
        or detail.get("article_desc")
        or detail.get("article_description")
    )

    facts = " / ".join(x for x in [trade, price, area, floor] if x)

    if property_class == "commercial":
        intro = f"{name} {kind or '상가·업무용'} 매물입니다."
        if facts:
            intro += f"\n현재 확인되는 조건은 {facts}입니다."
        point = (
            "상가 매물은 업종 적합성, 가시성, 고객 접근 동선, 주차 여건을 "
            "현장에서 함께 확인하는 것이 중요합니다."
        )
        location = (
            f"{address} 기준의 상권 흐름과 유동인구, 배후 수요, 도로 접근성을 "
            "영업 목적에 맞춰 확인해 주세요."
            if address else
            "상권 흐름과 유동인구, 배후 수요, 도로 접근성을 영업 목적에 맞춰 확인해 주세요."
        )
        space = (
            "전용면적과 층 위치, 출입구, 내부 동선, 전면 노출 범위를 확인하고 "
            "희망 업종에 필요한 설비 설치 가능 여부를 함께 살펴보는 것이 좋습니다."
        )
        price_text = (
            "보증금·임대료·관리비와 권리금 유무는 계약 전 최신 조건을 다시 확인해야 합니다."
        )
        caution = (
            "건축물 용도와 업종 제한, 주차, 관리규약, 시설물 상태를 계약 전에 확인해 주세요."
        )
    elif property_class == "factory_warehouse":
        intro = f"{name} {kind or '공장·창고'} 매물입니다."
        if facts:
            intro += f"\n현재 확인되는 조건은 {facts}입니다."
        point = (
            "공장·창고는 층고, 전력 용량, 차량 진입, 하역 공간과 실제 사용 용도가 "
            "사업 조건에 맞는지 확인하는 것이 핵심입니다."
        )
        location = (
            f"{address} 기준으로 대형차 진입로, 주요 도로와의 연결, 주변 민원 가능성을 확인해 주세요."
            if address else
            "대형차 진입로, 주요 도로와의 연결, 주변 민원 가능성을 확인해 주세요."
        )
        space = (
            "바닥 하중, 층고, 기둥 간격, 화물 동선, 호이스트·크레인 등 기존 설비와 "
            "추가 설치 가능 여부를 현장에서 확인해야 합니다."
        )
        price_text = (
            "매매·임대 조건 외에도 전력 증설, 설비 이전, 원상복구와 관리 비용을 함께 검토해 주세요."
        )
        caution = (
            "건축물대장상 용도, 공장등록 가능 여부, 위험물·소방 기준과 진입도로 조건을 계약 전에 확인해 주세요."
        )
    else:
        intro = f"{name} {kind or '토지·대지'} 매물입니다."
        if facts:
            intro += f"\n현재 확인되는 조건은 {facts}입니다."
        point = (
            "토지는 지목, 용도지역·지구, 면적, 형상과 도로 접면 조건을 원문 자료 기준으로 확인해야 합니다."
        )
        location = (
            f"{address} 기준으로 현황도로와 진입 조건, 주변 토지 이용 상태를 확인해 주세요."
            if address else
            "현황도로와 진입 조건, 주변 토지 이용 상태를 확인해 주세요."
        )
        space = (
            "공부상 면적과 현황 경계가 일치하는지 확인하고 경사도, 고저차, 기반시설 인입 조건을 살펴봐야 합니다."
        )
        price_text = (
            "가격은 면적과 입지뿐 아니라 용도지역, 접도, 개발행위 제한과 기반시설 조건을 함께 비교해야 합니다."
        )
        caution = (
            "토지이용계획확인서, 지적도, 등기사항과 허가 가능 여부는 관계 기관 및 전문가를 통해 확인해 주세요."
        )

    if desc:
        point += f"\n중개사 원문 설명: {desc}"

    return {
        "intro_text": intro,
        "feature_text": point,
        "location_text": location,
        "space_text": space,
        "price_text_block": price_text,
        "school_text": "",
        "realtor_text": caution,
        "outro_text": "방문 전 현재 매물 가능 여부와 세부 조건을 중개사무소에 다시 확인해 주세요.",
    }


def enrich_ai_sections(ai_sections, human_context):
    ai_sections = ai_sections or {}

    mapping = {
        "intro": "intro_text",
        "point": "feature_text",
        "location": "location_text",
        "space": "space_text",
        "price": "price_text_block",
        "school": "school_text",
        "realtor": "realtor_text",
        "closing": "outro_text",
    }

    bad_phrases = [
        "현장에서 확인했을 때 공간감과 구조가 안정적으로 느껴지는 매물입니다",
        "매물을 소개드립니다",
        "충분히 관심 있게 볼 수 있는 매물로 판단됩니다",
    ]

    for key, human_key in mapping.items():
        current = clean_text(ai_sections.get(key))
        replacement = clean_text(human_context.get(human_key))

        if not replacement:
            continue

        if not current:
            ai_sections[key] = replacement
            continue

        if any(phrase in current for phrase in bad_phrases):
            ai_sections[key] = replacement
            continue

        if len(current) < 25:
            ai_sections[key] = replacement

    return ai_sections



# ---------------------------------------------------------------------
# SEO 해시태그 / 검색 노출 보강
# ---------------------------------------------------------------------

def clean_seo_keyword(value):
    value = clean_text(value)
    value = value.replace("#", "")
    value = re.sub(r"[\s\t\r\n]+", "", value)
    value = re.sub(r"[^\w가-힣A-Za-z0-9]", "", value)
    return value.strip()


def add_seo_keyword(items, value):
    keyword = clean_seo_keyword(value)

    if not keyword:
        return

    if len(keyword) < 2:
        return

    if keyword not in items:
        items.append(keyword)


def split_realtor_seo_keywords(value):
    text = clean_text(value)

    if not text:
        return []

    parts = re.split(r"[,，\n\r/|]+", text)

    result = []
    for part in parts:
        keyword = clean_seo_keyword(part)
        if keyword and keyword not in result:
            result.append(keyword)

    return result


def is_building_dong_keyword(value):
    value = clean_text(value)
    return bool(re.fullmatch(r"\d{1,4}동", value or ""))


def extract_location_keywords_for_seo(detail):
    """
    SEO용 지역명만 추출한다.
    101동/206동 같은 건물 동 정보는 지역 키워드로 쓰지 않는다.
    """
    merged = " ".join([
        clean_text(detail.get("address")),
        clean_text(detail.get("road_address")),
        clean_text(detail.get("exposure_address")),
        clean_text(detail.get("address_info")),
        clean_text(detail.get("region_name")),
        clean_text(detail.get("city")),
        clean_text(detail.get("sigungu")),
        clean_text(detail.get("dong_name")),
        clean_text(detail.get("emd_name")),
    ])

    # 주소 컬럼이 빈 경우 raw_json에서 exposureAddress를 보강한다.
    raw = parse_json_safely(detail.get("raw_json"))
    if raw:
        article_detail = raw.get("articleDetail") or {}
        article_addition = raw.get("articleAddition") or {}
        merged += " " + " ".join([
            clean_text(article_detail.get("exposureAddress")),
            clean_text(article_detail.get("roadAddress")),
            clean_text(article_addition.get("lawUsage")),
        ])

    items = []

    for pattern in [
        r"([가-힣A-Za-z0-9]+시)",
        r"([가-힣A-Za-z0-9]+군)",
        r"([가-힣A-Za-z0-9]+구)",
        r"([가-힣A-Za-z0-9]+동)",
        r"([가-힣A-Za-z0-9]+읍)",
        r"([가-힣A-Za-z0-9]+면)",
    ]:
        for match in re.findall(pattern, merged):
            if is_building_dong_keyword(match):
                continue
            add_seo_keyword(items, match)

    return items


def extract_region_pair_for_seo(detail):
    """
    본문 문장용 지역 묶음 반환.
    예: 부산진구 양정동 / 세종시 해밀동
    """
    locations = extract_location_keywords_for_seo(detail)

    gu = ""
    dong = ""
    city = ""

    for item in locations:
        if item.endswith("동") and not dong:
            dong = item
        elif item.endswith("구") and not gu:
            gu = item
        elif item.endswith("시") and not city:
            city = item

    if gu and dong:
        return f"{gu} {dong}"
    if city and dong:
        return f"{city} {dong}"
    if city and gu:
        return f"{city} {gu}"
    if locations:
        return " ".join(locations[:2])

    return ""


def build_seo_title(detail):
    article_name = normalize_title_duplicate_dong(
        clean_text(
            detail.get("article_name")
            or detail.get("building_name")
            or detail.get("complex_name")
            or ""
        )
    )

    base_name = remove_trailing_dong_for_sentence(article_name) or article_name
    trade_type = clean_text(detail.get("trade_type") or "")
    real_estate_type = clean_text(detail.get("real_estate_type") or detail.get("real_estate_type_name") or "")
    region = extract_region_pair_for_seo(detail)

    parts = []

    if base_name:
        if trade_type:
            parts.append(f"{base_name} {trade_type}")
        else:
            parts.append(base_name)

    if region and real_estate_type and trade_type:
        parts.append(f"{region} {real_estate_type} {trade_type}")
    elif region and real_estate_type:
        parts.append(f"{region} {real_estate_type}")

    if not parts:
        return "매물 검색 안내"

    return " | ".join(parts[:2])[:120]


def build_region_seo_paragraph(detail):
    article_name = normalize_title_duplicate_dong(
        clean_text(
            detail.get("article_name")
            or detail.get("building_name")
            or detail.get("complex_name")
            or "해당 매물"
        )
    )
    base_name = remove_trailing_dong_for_sentence(article_name) or article_name

    trade_type = clean_text(detail.get("trade_type") or "")
    real_estate_type = clean_text(detail.get("real_estate_type") or detail.get("real_estate_type_name") or "")
    price_text = clean_text(detail.get("price_text") or "")
    area_info = clean_text(detail.get("area_info") or "")
    office_name = clean_text(detail.get("office_name") or "중개사무소")
    region = extract_region_pair_for_seo(detail)

    subject = " ".join([x for x in [base_name, trade_type, real_estate_type] if x]).strip() or "해당 매물"

    lines = []

    if region and real_estate_type:
        lines.append(f"{base_name}은 {region}에서 {real_estate_type}을 찾는 분들이 함께 살펴볼 만한 매물입니다.")
    else:
        lines.append(f"{subject} 정보를 찾고 계신 분들께 도움이 되는 매물 안내입니다.")

    if trade_type and real_estate_type:
        if region:
            lines.append(f"{region} {real_estate_type} {trade_type} 조건을 비교하고 계시다면 가격, 면적, 층수, 방향을 함께 확인해보시면 좋습니다.")
        else:
            lines.append(f"{real_estate_type} {trade_type} 조건을 비교하고 계시다면 가격, 면적, 층수, 방향을 함께 확인해보시면 좋습니다.")

    if price_text or area_info:
        detail_parts = []
        if price_text:
            detail_parts.append(f"가격은 {price_text}")
        if area_info:
            detail_parts.append(f"면적은 {area_info}")
        if detail_parts:
            lines.append(" / ".join(detail_parts) + " 기준으로 안내되는 매물입니다.")

    if base_name and trade_type:
        lines.append(f"{base_name} {trade_type} 문의 전에는 현재 매물 가능 여부와 세부 조건을 다시 확인하는 것이 안전합니다.")

    lines.append(f"자세한 상담은 {office_name}를 통해 현재 기준의 최신 정보로 안내받으실 수 있습니다.")

    return "\n".join(lines)


def build_seo_keywords(detail):
    keywords = []

    article_name = normalize_title_duplicate_dong(
        clean_text(
            detail.get("article_name")
            or detail.get("building_name")
            or detail.get("complex_name")
            or ""
        )
    )

    article_name_no_dong = remove_trailing_dong_for_sentence(article_name)

    real_estate_type = clean_text(
        detail.get("real_estate_type")
        or detail.get("real_estate_type_name")
        or ""
    )

    trade_type = clean_text(detail.get("trade_type") or "")
    office_name = clean_text(detail.get("office_name") or "")
    seo_keywords = clean_text(detail.get("seo_keywords") or "")

    location_keywords = extract_location_keywords_for_seo(detail)

    # 건물 동까지 붙은 단지명은 해시태그 가치가 낮아 기본 단지명 중심으로 사용한다.
    add_seo_keyword(keywords, article_name_no_dong)

    if article_name_no_dong and trade_type:
        add_seo_keyword(keywords, f"{article_name_no_dong}{trade_type}")

    if article_name_no_dong and real_estate_type:
        add_seo_keyword(keywords, f"{article_name_no_dong}{real_estate_type}")

    for loc in location_keywords:
        add_seo_keyword(keywords, loc)

        if real_estate_type:
            add_seo_keyword(keywords, f"{loc}{real_estate_type}")

        if trade_type:
            add_seo_keyword(keywords, f"{loc}{trade_type}")

        add_seo_keyword(keywords, f"{loc}부동산")

    if real_estate_type and trade_type:
        add_seo_keyword(keywords, f"{real_estate_type}{trade_type}")

    if real_estate_type:
        add_seo_keyword(keywords, real_estate_type)

    if trade_type:
        add_seo_keyword(keywords, trade_type)

    type_text = f"{article_name} {real_estate_type}".lower()

    if "apt" in type_text or "아파트" in type_text or re.search(r"(롯데캐슬|자이|래미안|푸르지오|힐스테이트|아이파크|더샵|e편한|단지)", type_text):
        if trade_type:
            add_seo_keyword(keywords, f"아파트{trade_type}")
        for keyword in ["아파트매매", "아파트전세", "아파트월세", "아파트추천"]:
            add_seo_keyword(keywords, keyword)

    if "오피스텔" in type_text:
        for keyword in ["오피스텔매매", "오피스텔전세", "오피스텔월세"]:
            add_seo_keyword(keywords, keyword)

    if "상가" in type_text:
        for keyword in ["상가매매", "상가임대", "상가월세"]:
            add_seo_keyword(keywords, keyword)

    if "토지" in type_text or "대지" in type_text or "임야" in type_text or real_estate_type in ["대", "전", "답"]:
        for keyword in ["토지매매", "대지매매", "토지추천"]:
            add_seo_keyword(keywords, keyword)

    if office_name:
        add_seo_keyword(keywords, office_name)
        add_seo_keyword(keywords, office_name.replace("공인중개사사무소", "공인중개사"))

    for keyword in split_realtor_seo_keywords(seo_keywords):
        add_seo_keyword(keywords, keyword)

    return keywords[:30]


def build_seo_text_block(detail):
    return build_region_seo_paragraph(detail)


def build_realtor_location_html(detail):
    office_name = clean_text(detail.get("office_name") or "")
    map_image_url = clean_text(detail.get("map_image_url") or "")

    if not map_image_url:
        return ""

    title = f"{office_name} 위치 안내" if office_name else "중개사무소 위치 안내"

    return f"""
<div class="realestate-realtor-map-image" style="margin:42px 0 28px; text-align:center; clear:both;">
  <div style="font-size:22px; font-weight:900; color:#111827; margin-bottom:16px; line-height:1.45; text-align:left;">
    {title}
  </div>
  <img src="{map_image_url}" alt="{title}" style="width:100%; max-width:900px; height:auto; display:block; margin:0 auto; border-radius:18px; border:1px solid #e5e7eb; box-shadow:0 14px 34px rgba(15,23,42,.12);">
  <div style="margin-top:12px; font-size:14px; line-height:1.7; color:#64748b; text-align:left;">
    방문 상담 전 매물 가능 여부와 상담 가능 시간을 먼저 확인해 주세요.
  </div>
</div>
"""


def insert_realtor_location_before_contact(draft_data, detail):
    if not draft_data:
        return draft_data

    location_html = build_realtor_location_html(detail)

    if not location_html.strip():
        print("[REALTOR MAP SKIP] map_image_url empty")
        return draft_data

    html_keys = [
        "draft_html",
        "clipboard_html",
        "preview_html",
        "content_html",
        "body_html",
        "html",
    ]

    updated = []

    for key in html_keys:
        html = str(draft_data.get(key) or "")

        if not html:
            continue

        if "realestate-realtor-map-image" in html:
            updated.append(f"{key}:already")
            continue

        anchor = "상담 및 중개사 안내"
        anchor_pos = html.find(anchor)

        if anchor_pos >= 0:
            # 상담 섹션 h2 바로 앞에 지도 삽입
            h2_pos = html.rfind("<h2", 0, anchor_pos)
            insert_pos = h2_pos if h2_pos >= 0 else anchor_pos
            draft_data[key] = html[:insert_pos] + "\n" + location_html + "\n" + html[insert_pos:]
            updated.append(key)
            continue

        # 상담 섹션을 못 찾으면 최외곽 closing div 앞에 삽입
        idx = html.rfind("</div>")
        if idx >= 0:
            draft_data[key] = html[:idx] + "\n" + location_html + "\n" + html[idx:]
        else:
            draft_data[key] = html + "\n" + location_html

        updated.append(f"{key}:fallback")

    print("[REALTOR MAP INSERT DONE]", ",".join(updated))

    return draft_data


def build_seo_footer_html(detail):
    keywords = build_seo_keywords(detail)
    seo_text = build_seo_text_block(detail)

    hashtags = " ".join([f"#{keyword}" for keyword in keywords])

    hashtag_html = ""
    if hashtags:
        hashtag_html = f"""
<div style="margin-top:22px; padding:20px; border:1px solid #e5e7eb; border-radius:14px; background:#ffffff;">
  <div style="font-size:18px; font-weight:800; margin-bottom:12px; color:#111827;">
    관련 키워드
  </div>
  <div style="font-size:15px; line-height:2.0; color:#374151; word-break:keep-all;">
    {hashtags}
  </div>
</div>
"""

    return f"""
<div class="realestate-seo-footer" style="margin:42px 0 20px; padding:22px; border:1px solid #d1d5db; border-radius:16px; background:#f8fafc;">
  <div style="font-size:20px; font-weight:800; margin-bottom:14px; color:#111827;">
    {build_seo_title(detail)}
  </div>
  <div style="font-size:15px; line-height:1.9; color:#374151; white-space:pre-wrap;">
    {seo_text}
  </div>
  {hashtag_html}
</div>
"""


def append_seo_footer_to_draft_data(draft_data, detail):
    if not draft_data:
        return draft_data

    seo_html = build_seo_footer_html(detail)

    if not seo_html.strip():
        return draft_data

    html_keys = [
        "draft_html",
        "clipboard_html",
        "preview_html",
        "content_html",
        "body_html",
        "html",
    ]

    updated = []

    for key in html_keys:
        html = str(draft_data.get(key) or "")

        if not html:
            continue

        if "realestate-seo-footer" in html:
            updated.append(f"{key}:already")
            continue

        # 최외곽 closing div 앞에 넣는 것이 레이아웃 유지에 가장 안전하다.
        idx = html.rfind("</div>")
        if idx >= 0:
            draft_data[key] = html[:idx] + "\n" + seo_html + "\n" + html[idx:]
        else:
            draft_data[key] = html + "\n" + seo_html

        updated.append(key)

    print("[SEO FOOTER APPEND DONE]", ",".join(updated), "keywords=", len(build_seo_keywords(detail)))

    return draft_data



# ---------------------------------------------------------------------
# 법정 표시광고 표준 표
# ---------------------------------------------------------------------

def first_non_empty(*values):
    for value in values:
        text = clean_text(value)
        if text:
            return text
    return ""


def normalize_unknown(value, default="확인 필요"):
    text = clean_text(value)
    if not text or text in ["-", "해당없음", "None", "null"]:
        return default
    return text


def extract_total_floor_for_law(detail):
    value = clean_text(detail.get("floor_info") or detail.get("floor_info_display") or "")
    if not value:
        return ""

    m = re.search(r"총\s*(\d+)\s*층", value)
    if m:
        return f"총 {m.group(1)}층"

    m = re.search(r"/\s*(\d+)\s*층?", value)
    if m:
        return f"총 {m.group(1)}층"

    return value


def extract_raw_value_for_law(detail, keys):
    for key in keys:
        value = clean_text(detail.get(key))
        if value:
            return value

    raw = parse_json_safely(detail.get("raw_json"))
    if raw:
        found = recursive_find_first_safe(raw, keys)
        if found not in [None, "", 0, "0", "-", []]:
            return clean_text(found)

    return ""


def format_ymd_for_law(value):
    text = clean_text(value)
    if not text:
        return ""

    if text == "NOW":
        return "즉시입주"

    digits = re.sub(r"[^0-9]", "", text)

    if len(digits) == 8:
        return f"{digits[:4]}년 {digits[4:6]}월 {digits[6:8]}일"

    if len(digits) == 6:
        return f"{digits[:4]}년 {digits[4:6]}월"

    return text


def format_area_for_law(detail, article_space=None):
    current = clean_text(detail.get("area_info"))
    if current:
        return current

    article_space = article_space or {}
    supply = ""
    exclusive = ""

    if isinstance(article_space, dict):
        supply = clean_text(article_space.get("supplySpace"))
        exclusive = clean_text(article_space.get("exclusiveSpace"))

    if supply and exclusive:
        return f"공급 {supply}㎡ / 전용 {exclusive}㎡"

    if exclusive:
        return f"전용 {exclusive}㎡"

    if supply:
        return f"공급 {supply}㎡"

    return ""


def format_parking_for_law(value, per_household=""):
    text = clean_text(value)
    per_household = clean_text(per_household)

    if not text:
        return ""

    if per_household:
        return f"총 {text}대 / 세대당 {per_household}대"

    return f"총 {text}대"


def format_maintenance_for_law(raw, admin_info=None):
    text = clean_text(raw)

    if text:
        return text

    admin_info = admin_info or {}

    if isinstance(admin_info, dict) and admin_info:
        charge_code = clean_text(admin_info.get("chargeCodeType"))
        unable = admin_info.get("unableCheckDetails") or {}

        if isinstance(unable, dict) and unable:
            return "네이버 매물정보 기준 별도 확인 필요"

        if charge_code:
            return "네이버 매물정보 기준 별도 확인 필요"

    return ""


def detect_law_target_type(detail):
    raw = parse_json_safely(detail.get("raw_json"))
    article_detail = raw.get("articleDetail") if isinstance(raw, dict) else {}
    article_addition = raw.get("articleAddition") if isinstance(raw, dict) else {}

    # STEP HOTFIX:
    # 법정표시사항 매물유형은 raw_json의 네이버 원천값을 최우선으로 신뢰한다.
    # 한화포레나대전월평공원2단지처럼 article_name에 '대전'이 들어가면
    # land_words의 '전'에 걸려 토지로 오판되는 문제를 막는다.
    raw_type_name = clean_text(
        article_detail.get("realestateTypeName") if isinstance(article_detail, dict) else ""
    ) or clean_text(
        article_addition.get("articleRealEstateTypeName") if isinstance(article_addition, dict) else ""
    ) or clean_text(
        article_addition.get("realEstateTypeName") if isinstance(article_addition, dict) else ""
    )

    raw_type_code = clean_text(
        article_detail.get("articleTypeCode") if isinstance(article_detail, dict) else ""
    ) or clean_text(
        article_addition.get("articleRealEstateTypeCode") if isinstance(article_addition, dict) else ""
    ) or clean_text(
        article_addition.get("realEstateTypeCode") if isinstance(article_addition, dict) else ""
    )

    if raw_type_name in ["아파트", "오피스텔", "빌라", "연립", "다세대", "주택", "상가", "사무실"] or raw_type_code in ["A01"]:
        return "building"

    if raw_type_name in ["토지", "대", "전", "답", "임야", "대지", "잡종지", "공장용지", "창고용지"]:
        return "land"

    code_text = " ".join([
        clean_text(detail.get("real_estate_type")),
        clean_text(detail.get("real_estate_type_name")),
        clean_text(detail.get("_raw_article_type_code")),
        clean_text(detail.get("_raw_realestate_type_code")),
        clean_text(article_detail.get("tradeBuildingTypeCode") if isinstance(article_detail, dict) else ""),
        clean_text(article_detail.get("realestateTypeCode") if isinstance(article_detail, dict) else ""),
        clean_text(article_addition.get("realEstateTypeCode") if isinstance(article_addition, dict) else ""),
        clean_text(article_addition.get("articleRealEstateTypeCode") if isinstance(article_addition, dict) else ""),
    ]).upper()

    name_text = " ".join([
        clean_text(detail.get("real_estate_type")),
        clean_text(detail.get("real_estate_type_name")),
        clean_text(detail.get("article_name")),
        clean_text(article_detail.get("realestateTypeName") if isinstance(article_detail, dict) else ""),
        clean_text(article_addition.get("realEstateTypeName") if isinstance(article_addition, dict) else ""),
        clean_text(article_addition.get("articleRealEstateTypeName") if isinstance(article_addition, dict) else ""),
    ])

    land_codes = ["TJ", "LAND", "TOJI"]
    land_words = ["토지", "대지", "임야", "전", "답", "잡종지", "공장용지", "창고용지"]

    if any(x in code_text for x in land_codes) or any(x in name_text for x in land_words):
        return "land"

    return "building"



def format_land_area_for_law(detail, article_space=None, article_addition=None):
    article_space = article_space or {}
    article_addition = article_addition or {}

    ground = ""
    if isinstance(article_space, dict):
        ground = clean_text(article_space.get("groundSpace"))

    if ground:
        try:
            if float(ground) > 0:
                return f"대지면적 {float(ground):g}㎡"
        except Exception:
            return f"대지면적 {ground}㎡"

    area1 = ""
    if isinstance(article_addition, dict):
        area1 = clean_text(article_addition.get("area1"))

    if area1:
        return f"토지면적 {area1}㎡"

    current = clean_text(detail.get("area_info"))
    if current:
        return current

    return ""


def extract_land_category_for_law(detail, article_detail=None, article_addition=None):
    article_detail = article_detail or {}
    article_addition = article_addition or {}

    candidates = [
        detail.get("land_category"),
        detail.get("land_use"),
        article_detail.get("articleName") if isinstance(article_detail, dict) else "",
        article_detail.get("buildingTypeName") if isinstance(article_detail, dict) else "",
        article_addition.get("articleName") if isinstance(article_addition, dict) else "",
        extract_raw_value_for_law(detail, ["landCategoryName", "jimokName", "landUseName", "lndcgrCodeNm"]),
    ]

    for value in candidates:
        text = clean_text(value)
        if text and text not in ["-", "토지", "토지/임야"]:
            return text

    return ""





def normalize_address_join_for_law(*values):
    parts = []
    for value in values:
        text = clean_text(value)
        if not text or text in ["-", "확인 필요", "None", "null"]:
            continue
        if parts and text in parts[-1]:
            continue
        if any(text == p for p in parts):
            continue
        parts.append(text)

    joined = " ".join(parts)
    joined = re.sub(r"\s+", " ", joined).strip()
    joined = re.sub(r"(\S+동)\s+\1", r"\1", joined)
    return collapse_duplicate_address_segments(joined)


def find_address_text_recursive_for_law(obj):
    address_keys = {
        "address",
        "jibunaddress",
        "jibunaddressname",
        "roadaddress",
        "roadaddressname",
        "locationaddress",
        "detailaddress",
        "exposureaddress",
        "hscpaddress",
        "complexaddress",
        "fulladdress",
        "addr",
        "jibunaddr",
        "roadaddr",
    }

    candidates = []

    def walk(value, path=""):
        if isinstance(value, dict):
            for key, child in value.items():
                key_l = str(key or "").lower()
                next_path = f"{path}.{key_l}" if path else key_l

                if key_l in address_keys and isinstance(child, str):
                    text = clean_text(child)
                    if text:
                        candidates.append((next_path, text))

                walk(child, next_path)

        elif isinstance(value, list):
            for idx, child in enumerate(value):
                walk(child, f"{path}[{idx}]")

    walk(obj)

    def score(item):
        path, text = item
        s = 0
        if any(x in text for x in ["시", "군", "구", "동", "읍", "면"]):
            s += 10
        if re.search(r"\d", text):
            s += 20
        if "jibun" in path:
            s += 8
        if "road" in path:
            s += 5
        if "address" in path:
            s += 3
        return s

    candidates.sort(key=score, reverse=True)

    if candidates:
        return candidates[0][1]

    return ""


def resolve_property_address_for_law(detail, article_detail=None, raw=None):
    article_detail = article_detail or {}
    raw = raw or {}

    complex_detail = raw.get("complexDetail") if isinstance(raw, dict) else {}
    complex_resolved = raw.get("complexResolvedAddress") if isinstance(raw, dict) else ""

    complex_address = ""
    if isinstance(complex_detail, dict) and complex_detail:
        complex_address = find_address_text_recursive_for_law(complex_detail)

    exposure = first_non_empty(
        # 상단 핵심정보/주소표에서 이미 확정된 상세 소재지를 법정표도 그대로 쓴다.
        # 이 필드를 빼면 법정표만 raw_json의 축약 주소로 되돌아갈 수 있다.
        detail.get("address"),
        detail.get("article_address"),
        detail.get("road_address"),
        detail.get("exposure_address"),
        article_detail.get("exposureAddress") if isinstance(article_detail, dict) else "",
        article_detail.get("roadAddress") if isinstance(article_detail, dict) else "",
    )

    detail_addr = first_non_empty(
        detail.get("detail_address"),
        article_detail.get("detailAddress") if isinstance(article_detail, dict) else "",
    )

    # article API/검색 보강 주소를 먼저 사용한다.
    # complexDetail의 축약 주소(예: "세종시 세종시 집현동")가 이미 수집된
    # 도로명 상세주소를 덮어쓰면 법정표만 짧게 표시되는 회귀가 발생한다.
    joined = normalize_address_join_for_law(exposure, detail_addr)

    def public_legal_address(value):
        value = clean_text(value)
        if not value:
            return ""

        # 동·호·상가명 등은 제외하고 도로명 건물번호까지만 법정표에 표시한다.
        # 예: 세종특별자치시 시청대로 500,수루배마을4단지 ...
        #  -> 세종특별자치시 시청대로 500
        road_match = re.match(
            r"^(.+?(?:대로|로|길)\s+\d+(?:-\d+)?)(?=\s*[,，]|\s+(?:[가-힣A-Za-z0-9]+동|\d+호|근린생활시설)|$)",
            value,
        )
        if road_match:
            return clean_text(road_match.group(1))
        return value

    # 숫자가 있는 수집/검색 주소는 축약 단지주소보다 우선한다.
    for candidate in [joined, complex_resolved, complex_address]:
        candidate = clean_text(candidate)
        if candidate and re.search(r"\d+(?:-\d+)?", candidate):
            return public_legal_address(candidate)

    # 번지/도로번호가 어느 후보에도 없으면 수집 주소를 우선 유지한다.
    for candidate in [joined, complex_resolved, complex_address]:
        candidate = clean_text(candidate)
        if candidate:
            return public_legal_address(candidate)

    return ""


def _address_contains_office_detail(value, office_detail_address):
    """사용자 상세주소가 중개대상물 주소 후보에 섞였는지 확인한다."""
    value_key = re.sub(r"[\s,，·]+", "", clean_text(value)).lower()
    detail_key = re.sub(
        r"[\s,，·]+", "",
        clean_text(office_detail_address),
    ).lower()
    return bool(value_key and detail_key and detail_key in value_key)


def _collapse_duplicate_address_tokens(value):
    """단일 토큰뿐 아니라 반복된 행정구역 묶음도 한 번만 남긴다."""
    return collapse_duplicate_address_segments(value)


def _address_has_sido(value):
    """주소 첫 토큰에 시·도 정보가 있는지 확인한다."""
    value = _collapse_duplicate_address_tokens(value)
    if not value:
        return False
    first = value.split()[0]
    short_sido = {
        "서울", "부산", "대구", "인천", "광주", "대전", "울산", "세종",
        "경기", "강원", "충북", "충남", "전북", "전남", "경북", "경남", "제주",
        "세종시",
    }
    return bool(
        first in short_sido
        or re.search(
            r"(?:특별자치도|특별자치시|특별시|광역시|도)$",
            first,
        )
    )


def _administrative_prefix_for_address(value):
    """시·도부터 시/군/구까지의 안전한 주소 접두부를 추출한다."""
    value = _collapse_duplicate_address_tokens(value)
    if not value:
        return ""
    prefix = []
    for raw_token in value.split():
        token = raw_token.strip(",，()[]")
        if not token:
            continue
        if re.search(r"(?:읍|면|동|리)$", token):
            break
        if re.search(r"\d|(?:대로|로|길|번길)$", token):
            break
        if (
            token in {
                "서울", "부산", "대구", "인천", "광주", "대전", "울산", "세종",
                "경기", "강원", "충북", "충남", "전북", "전남", "경북", "경남", "제주",
                "세종시",
            }
            or re.search(
                r"(?:특별자치도|특별자치시|특별시|광역시|도|시|군|구)$",
                token,
            )
        ):
            prefix.append(token)
            continue
        break
    return clean_text(" ".join(prefix))


def _ensure_property_address_sido(value, region_hint=""):
    """시·도가 빠진 네이버 주소에 검증된 행정구역 접두부를 복원한다."""
    value = _collapse_duplicate_address_tokens(value)
    region_hint = _collapse_duplicate_address_tokens(region_hint)
    if not value or _address_has_sido(value):
        return value

    completed = _complete_verified_address_prefix(region_hint, value)
    completed = _collapse_duplicate_address_tokens(completed)
    if completed != value and _address_has_sido(completed):
        return completed

    prefix = _administrative_prefix_for_address(region_hint)
    if prefix:
        return _collapse_duplicate_address_tokens(f"{prefix} {value}")
    return value


def resolve_property_address_without_office_detail(
    detail,
    article_detail=None,
    raw=None,
    office_detail_address="",
):
    """중개사 상세주소를 배제하고 네이버 매물 소재지만 선택한다."""
    article_detail = article_detail or {}
    raw = raw or {}

    enrichment = raw.get("naverSearchEnrichment") if isinstance(raw, dict) else {}
    if not isinstance(enrichment, dict):
        enrichment = {}
    facts = enrichment.get("complex_facts") or {}
    if not isinstance(facts, dict):
        facts = {}

    search_region = clean_text(enrichment.get("search_region"))
    region_hint = first_non_empty(
        search_region,
        detail.get("region_name"),
        detail.get("region"),
        detail.get("article_address"),
        detail.get("exposure_address"),
        detail.get("address"),
    )

    search_address = ""
    if enrichment.get("verified") is True:
        search_address = clean_text(facts.get("address"))
        if search_address and search_region:
            search_address = _ensure_property_address_sido(
                search_address,
                search_region,
            )

    complex_detail = raw.get("complexDetail") if isinstance(raw, dict) else {}
    complex_detail_address = (
        find_address_text_recursive_for_law(complex_detail)
        if isinstance(complex_detail, dict)
        else ""
    )

    candidates = [
        # 사용자가 요청한 네이버 검색 주소를 가장 먼저 사용한다.
        search_address,
        raw.get("complexResolvedAddress") if isinstance(raw, dict) else "",
        detail.get("complex_resolved_address"),
        detail.get("article_address"),
        detail.get("exposure_address"),
        detail.get("road_address"),
        article_detail.get("roadAddress") if isinstance(article_detail, dict) else "",
        article_detail.get("exposureAddress") if isinstance(article_detail, dict) else "",
        complex_detail_address,
        # 기존 최종 해석값은 마지막 후보로만 사용한다.
        resolve_property_address_for_law(
            detail,
            article_detail=article_detail,
            raw=raw,
        ),
    ]

    seen = set()
    for candidate in candidates:
        candidate = clean_text(candidate)
        candidate_key = re.sub(r"\s+", "", candidate).lower()
        if not candidate or candidate_key in seen:
            continue
        seen.add(candidate_key)
        if _address_contains_office_detail(candidate, office_detail_address):
            print(
                "[LEGAL PROPERTY ADDRESS REJECTED]",
                clean_text(detail.get("article_no")),
                candidate,
                "contains_office_detail_address",
            )
            continue
        return _ensure_property_address_sido(candidate, region_hint)

    return ""


def build_legal_disclosure_html(detail):
    """
    공인중개사법 표시광고 명시사항을 중개대상물 종류에 따라 분리해 표기한다.
    - 중개사무소 명시사항 5개는 공통
    - 건축물/토지 유형별 표시 항목을 다르게 구성
    - 데이터가 없으면 숨기지 않고 '확인 필요'로 표시
    """
    office_name = normalize_unknown(detail.get("office_name"))
    representative_name = normalize_unknown(detail.get("representative_name"))
    license_number = normalize_unknown(detail.get("license_number"))
    office_phone = normalize_unknown(detail.get("office_phone"))
    mobile_phone = clean_text(detail.get("mobile_phone"))

    raw = parse_json_safely(detail.get("raw_json"))
    article_detail = raw.get("articleDetail") if isinstance(raw, dict) else {}
    article_addition = raw.get("articleAddition") if isinstance(raw, dict) else {}
    article_space = raw.get("articleSpace") if isinstance(raw, dict) else {}
    article_facility = raw.get("articleFacility") if isinstance(raw, dict) else {}
    article_floor = raw.get("articleFloor") if isinstance(raw, dict) else {}
    article_realtor = raw.get("articleRealtor") if isinstance(raw, dict) else {}
    admin_info = raw.get("administrationCostInfo") if isinstance(raw, dict) else {}

    # 사용자 기본 주소와 상세주소는 중개사무소 소재지에만 결합한다.
    # 상세주소가 중개대상물 소재지로 흘러가지 않도록 property_address와
    # 완전히 분리해서 계산한다.
    office_base_address = first_non_empty(
        detail.get("office_address"),
        detail.get("realtor_address"),
        article_realtor.get("address") if isinstance(article_realtor, dict) else "",
        article_realtor.get("realtorAddress") if isinstance(article_realtor, dict) else "",
    )
    office_detail_address = clean_text(detail.get("office_detail_address"))
    realtor_address = normalize_unknown(
        normalize_address_join_for_law(
            office_base_address,
            office_detail_address,
        )
    )

    contact = office_phone
    if mobile_phone and mobile_phone not in contact:
        contact = f"{office_phone} / {mobile_phone}" if office_phone != "확인 필요" else mobile_phone

    law_type = detect_law_target_type(detail)

    property_address = normalize_unknown(
        resolve_property_address_without_office_detail(
            detail,
            article_detail=article_detail,
            raw=raw,
            office_detail_address=office_detail_address,
        )
    )

    area_info = normalize_unknown(format_area_for_law(detail, article_space))

    price_text = normalize_unknown(
        first_non_empty(
            detail.get("price_text"),
            article_addition.get("dealOrWarrantPrc") if isinstance(article_addition, dict) else "",
        )
    )

    real_estate_type = normalize_unknown(
        first_non_empty(
            detail.get("real_estate_type"),
            detail.get("real_estate_type_name"),
            article_addition.get("articleRealEstateTypeName") if isinstance(article_addition, dict) else "",
            article_detail.get("realestateTypeName") if isinstance(article_detail, dict) else "",
        )
    )

    trade_type = normalize_unknown(
        first_non_empty(
            detail.get("trade_type"),
            article_detail.get("tradeTypeName") if isinstance(article_detail, dict) else "",
            article_addition.get("tradeTypeName") if isinstance(article_addition, dict) else "",
        )
    )

    total_floor = normalize_unknown(
        first_non_empty(
            extract_total_floor_for_law(detail),
            f"총 {article_floor.get('totalFloorCount')}층" if isinstance(article_floor, dict) and clean_text(article_floor.get("totalFloorCount")) else "",
        )
    )

    move_in = normalize_unknown(
        first_non_empty(
            detail.get("move_in_date"),
            detail.get("move_in_possible_date"),
            detail.get("move_in_type"),
            article_detail.get("moveInTypeName") if isinstance(article_detail, dict) else "",
            format_ymd_for_law(article_detail.get("moveInPossibleYmd") if isinstance(article_detail, dict) else ""),
            format_ymd_for_law(extract_raw_value_for_law(detail, ["moveInPossibleYmd"])),
        )
    )

    room_count = first_non_empty(
        detail.get("room_count"),
        detail.get("room_cnt"),
        article_detail.get("roomCount") if isinstance(article_detail, dict) else "",
        extract_raw_value_for_law(detail, ["roomCount", "roomCnt"]),
    )

    bathroom_count = first_non_empty(
        detail.get("bathroom_count"),
        detail.get("bath_count"),
        article_detail.get("bathroomCount") if isinstance(article_detail, dict) else "",
        extract_raw_value_for_law(detail, ["bathroomCount", "bathCount", "bathroomCnt"]),
    )

    room_bath = "확인 필요"
    if room_count or bathroom_count:
        room_bath = f"{room_count or '확인 필요'}개 / {bathroom_count or '확인 필요'}개"

    approval_date = normalize_unknown(
        first_non_empty(
            format_ymd_for_law(detail.get("use_approval_date")),
            format_ymd_for_law(detail.get("approval_date")),
            format_ymd_for_law(detail.get("building_approval_date")),
            format_ymd_for_law(article_detail.get("aptUseApproveYmd") if isinstance(article_detail, dict) else ""),
            format_ymd_for_law(extract_raw_value_for_law(detail, ["useApproveYmd", "useApprovalDate", "approvalDate", "buildingUseAprvYmd", "aptUseApproveYmd"])),
        )
    )

    parking = normalize_unknown(
        format_parking_for_law(
            first_non_empty(
                detail.get("parking_count"),
                detail.get("parking_available_count"),
                article_detail.get("parkingCount") if isinstance(article_detail, dict) else "",
                article_detail.get("aptParkingCount") if isinstance(article_detail, dict) else "",
                extract_raw_value_for_law(detail, ["parkingCount", "aptParkingCount", "parkingPossibleCount", "parkingTotalCount"]),
            ),
            first_non_empty(
                article_detail.get("parkingPerHouseholdCount") if isinstance(article_detail, dict) else "",
                article_detail.get("aptParkingCountPerHousehold") if isinstance(article_detail, dict) else "",
            )
        )
    )

    maintenance = normalize_unknown(
        format_maintenance_for_law(
            first_non_empty(
                detail.get("maintenance_fee"),
                detail.get("management_fee"),
                detail.get("management_cost"),
                extract_raw_value_for_law(detail, ["managementCost", "manageCost", "maintenanceFee", "articleManageCost"]),
            ),
            admin_info,
        )
    )

    direction = normalize_unknown(
        first_non_empty(
            convert_direction(detail.get("direction") or detail.get("direction_code")),
            article_facility.get("directionTypeName") if isinstance(article_facility, dict) else "",
            extract_raw_value_for_law(detail, ["direction", "directionBaseTypeName", "directionTypeName"]),
        )
    )

    land_category = normalize_unknown(
        extract_land_category_for_law(
            detail,
            article_detail=article_detail,
            article_addition=article_addition,
        )
    )

    article_no = normalize_unknown(detail.get("article_no"))
    posting_time = datetime.now().strftime("%Y년 %m월 %d일 %H시 %M분")

    office_rows = [
        ("업체명", office_name),
        ("소재지", realtor_address),
        ("연락처", contact),
        ("등록번호", license_number),
        ("대표", representative_name),
    ]

    common_property_rows = [
        ("매물번호", article_no),
        ("소재지", property_address),
        ("가격", price_text),
        ("거래형태", trade_type),
    ]

    if law_type == "land":
        property_section_title = "중개대상물 명시사항 - 토지"
        land_area_info = normalize_unknown(
            format_land_area_for_law(
                detail,
                article_space=article_space,
                article_addition=article_addition,
            )
        )
        property_rows = common_property_rows + [
            ("면적", land_area_info),
            ("지목", land_category),
            ("포스팅 일시", posting_time),
        ]
    else:
        property_section_title = "중개대상물 명시사항 - 건축물"
        property_rows = common_property_rows + [
            ("면적", area_info),
            ("중개대상물 종류", real_estate_type),
            ("총 층수", total_floor),
            ("입주가능일", move_in),
            ("방 수/욕실 수", room_bath),
            ("사용승인일", approval_date),
            ("주차대수", parking),
            ("관리비", maintenance),
            ("방향", direction),
            ("포스팅 일시", posting_time),
        ]

    def render_rows(rows, label_width="34%"):
        html = ""
        for label, value in rows:
            html += f"""
<tr>
  <td style="width:{label_width}; padding:11px 12px; border:1px solid #d1d5db; background:#f8fafc; font-weight:800; color:#111827;">{label}</td>
  <td style="padding:11px 12px; border:1px solid #d1d5db; background:#ffffff; color:#374151;">{value}</td>
</tr>
"""
        return html

    return f"""
<div class="realestate-legal-disclosure-table" style="margin:42px 0 24px; padding:24px; border:2px solid #c7d2fe; border-radius:18px; background:#ffffff; font-size:14px; line-height:1.75; color:#374151;">
  <div style="font-size:14px; color:#6b7280; margin-bottom:18px;">
    * 공인중개사법 제18조의2 및 중개대상물의 표시·광고 명시사항 세부기준에 따라 아래와 같이 안내드립니다.
  </div>

  <div style="font-size:17px; font-weight:900; padding:10px 12px; background:#e0e7ff; color:#1e3a8a; border-radius:12px 12px 0 0;">
    중개사무소 및 개업공인중개사 명시사항
  </div>
  <table class="realestate-legal-office-table" style="width:100%; border-collapse:collapse; margin:0 0 22px; font-size:14px;">
    {render_rows(office_rows, "42%")}
  </table>

  <div style="font-size:17px; font-weight:900; padding:10px 12px; background:#dcfce7; color:#166534; border-radius:12px 12px 0 0;">
    {property_section_title}
  </div>
  <table class="realestate-legal-property-table" style="width:100%; border-collapse:collapse; margin:0 0 16px; font-size:14px;">
    {render_rows(property_rows, "34%")}
  </table>

  <div style="margin-top:14px; padding:16px; border:1px dashed #cbd5e1; border-radius:12px; background:#f8fafc; color:#475569;">
    본 포스팅의 매물 정보는 포스팅 당시 확인된 진성매물을 기준으로 작성되었습니다.
    다만 확인 시점에 따라 거래 완료, 가격 변경, 조건 변경 등이 있을 수 있으므로
    방문 또는 상담 전 현재 매물 존재 여부와 상세 조건을 반드시 다시 확인해 주시기 바랍니다.
  </div>
</div>
"""


def replace_legal_disclosure_block_in_html(html, detail):
    html = str(html or "")

    if not html:
        return html

    new_block = build_legal_disclosure_html(detail)

    if "realestate-legal-disclosure-table" in html:
        return html

    title = "중개대상물 표시 · 광고 안내"
    pos = html.find(title)

    if pos >= 0:
        # 기존 법정 안내 블록 시작점을 찾는다.
        start_candidates = [
            html.rfind('<div style="margin:40px 0 20px', 0, pos),
            html.rfind('<div style="margin:40px', 0, pos),
            html.rfind("<div", 0, pos),
        ]
        start = next((x for x in start_candidates if x >= 0), -1)

        # 기존 법정 안내 다음에 오는 하단 안내 블록 앞까지를 기존 법정 블록으로 본다.
        end_markers = [
            '<div style="margin:38px 0 20px',
            '<div class="realestate-realtor-location"',
            '<div class="realestate-seo-footer"',
        ]

        end = -1
        for marker in end_markers:
            idx = html.find(marker, pos)
            if idx >= 0:
                end = idx
                break

        if start >= 0 and end > start:
            return html[:start] + new_block + "\n" + html[end:]

    # 기존 블록을 못 찾으면 상담 및 중개사 안내 앞에 삽입
    anchor = "상담 및 중개사 안내"
    anchor_pos = html.find(anchor)
    if anchor_pos >= 0:
        start = html.rfind("<h2", 0, anchor_pos)
        if start >= 0:
            return html[:start] + new_block + "\n" + html[start:]

    # 그래도 못 찾으면 마지막 div 앞에 삽입
    idx = html.rfind("</div>")
    if idx >= 0:
        return html[:idx] + "\n" + new_block + "\n" + html[idx:]

    return html + "\n" + new_block


def replace_legal_disclosure_in_draft_data(draft_data, detail):
    if not draft_data:
        return draft_data

    html_keys = [
        "draft_html",
        "clipboard_html",
        "preview_html",
        "content_html",
        "body_html",
        "html",
    ]

    updated = []

    for key in html_keys:
        html = str(draft_data.get(key) or "")
        if not html:
            continue

        new_html = replace_legal_disclosure_block_in_html(html, detail)

        if new_html != html:
            draft_data[key] = new_html
            updated.append(key)

    print("[LEGAL DISCLOSURE REPLACED]", ",".join(updated))

    return draft_data



# ---------------------------------------------------------------------
# 대표이미지 자동 생성 / 저장 / 초안 삽입
# ---------------------------------------------------------------------

def table_exists(conn, table_name):
    try:
        with conn.cursor() as cur:
            cur.execute("SHOW TABLES LIKE %s", (table_name,))
            return cur.fetchone() is not None
    except Exception:
        return False


def table_columns(conn, table_name):
    try:
        with conn.cursor() as cur:
            cur.execute(f"SHOW COLUMNS FROM {table_name}")
            rows = cur.fetchall()
        return set(row["Field"] for row in rows)
    except Exception:
        return set()


def clean_header_value(value):
    value = clean_nullable_text(value)
    value = re.sub(r"\s+", " ", value)
    return value

def normalize_title_duplicate_dong(value):
    """
    해밀1단지마스터힐스 106동 106동 같은 중복 동 표시를 제거한다.
    """
    value = clean_header_value(value)

    if not value:
        return ""

    value = re.sub(r"(\b\d{1,4}동)\s+\1\b", r"\1", value)

    parts = value.split()
    cleaned = []

    for part in parts:
        if cleaned and cleaned[-1] == part and re.search(r"\d+동$", part):
            continue
        cleaned.append(part)

    return " ".join(cleaned)


def build_header_display_title(detail, complex_name, dong, ho, dong_ho):
    """
    대표이미지 API에 보낼 제목.
    API가 dong/ho를 별도 필드로 받기 때문에 title에는 중복 동을 최대한 제거한다.
    """
    title = clean_header_value(
        detail.get("article_name")
        or detail.get("building_name")
        or complex_name
        or resolve_property_display_name(detail, default="부동산 매물 안내")
    )

    title = normalize_title_duplicate_dong(title)

    if dong:
        title = re.sub(rf"\s+{re.escape(dong)}$", "", title).strip()

    if not title:
        title = complex_name or "부동산 매물 안내"

    return normalize_title_duplicate_dong(title)



def is_dong_like_text(value):
    value = clean_header_value(value)
    return bool(re.fullmatch(r"\d{1,4}동", value or ""))


def is_ho_like_text(value):
    value = clean_header_value(value)
    return bool(re.fullmatch(r"\d{1,5}호", value or ""))


def parse_article_name_for_header(detail):
    article_name = clean_header_value(
        detail.get("article_name")
        or detail.get("article_title")
        or ""
    )

    building_name = clean_header_value(
        detail.get("building_name")
        or detail.get("complex_name")
        or ""
    )

    dong_from_field = clean_header_value(
        detail.get("dong")
        or detail.get("building_dong")
        or detail.get("article_dong")
        or ""
    )

    complex_name = building_name
    dong = dong_from_field

    if article_name:
        m = re.search(r"(.+?)\s+(\d{1,4}동)\s*$", article_name)
        if m:
            if not complex_name or is_dong_like_text(complex_name):
                complex_name = clean_header_value(m.group(1))
            if not dong:
                dong = clean_header_value(m.group(2))
        else:
            if not complex_name:
                complex_name = article_name

    complex_name = re.sub(r"\s*[·|]\s*(매매|전세|월세|분양|임대)\s*[·|].*$", "", complex_name).strip()
    complex_name = re.sub(r"\s+(매매|전세|월세)\s+.*$", "", complex_name).strip()

    return {
        "article_name": article_name,
        "complex_name": complex_name,
        "dong": dong,
    }


def choose_complex_name_for_header(detail, parsed_name):
    parsed_complex = clean_header_value(parsed_name.get("complex_name") or "")

    candidates = [
        parsed_complex,
        detail.get("complex_name"),
        detail.get("article_complex_name"),
        detail.get("complex_title"),
        detail.get("building_name"),
        detail.get("article_name"),
    ]

    for item in candidates:
        name = clean_header_value(item)

        if not name:
            continue

        if is_dong_like_text(name) or is_ho_like_text(name):
            continue

        m = re.search(r"(.+?)\s+(\d{1,4}동)\s*$", name)
        if m:
            name = clean_header_value(m.group(1))

        name = re.sub(r"\s*[·|]\s*(매매|전세|월세|분양|임대)\s*[·|].*$", "", name).strip()
        name = re.sub(r"\s+(매매|전세|월세)\s+.*$", "", name).strip()

        if name and not is_dong_like_text(name):
            return name

    return clean_header_value(
        parsed_complex
        or resolve_property_display_name(detail, default="부동산 매물 안내")
    )


def detect_property_type_for_header(detail):
    """
    대표이미지/배너용 매물 유형 판별.
    LAND 계열은 아파트 키워드보다 먼저 판별한다.
    특히 네이버 토지 매물은 real_estate_type 이 '대', '전', '답'처럼 짧게 내려올 수 있다.
    """
    real_type = clean_text(
        detail.get("real_estate_type")
        or detail.get("real_estate_type_name")
        or detail.get("realEstateTypeName")
        or ""
    ).strip()

    land_exact = {"대", "전", "답", "임야", "잡종지", "공장용지", "창고용지", "토지", "대지", "도로", "구거"}
    if real_type in land_exact:
        return "land"

    text = " ".join([
        clean_text(detail.get("real_estate_type")),
        clean_text(detail.get("real_estate_type_name")),
        clean_text(detail.get("article_name")),
        clean_text(detail.get("building_name")),
        clean_text(detail.get("complex_name")),
        clean_text(detail.get("article_title")),
    ]).lower()

    land_words = ["land", "토지", "대지", "임야", "잡종지", "공장용지", "창고용지", "계획관리지역", "자연녹지", "생산녹지"]
    if any(x in text for x in land_words):
        return "land"

    if "officetel" in text or "오피스텔" in text:
        return "officetel"

    if "villa" in text or "빌라" in text or "연립" in text or "다세대" in text:
        return "villa"

    if "store" in text or "상가" in text or "retail" in text or "사무실" in text:
        return "store"

    if "apt" in text or "아파트" in text:
        return "apt"

    if re.search(r"(단지|자이|래미안|푸르지오|힐스테이트|아이파크|수자인|더샵|e편한|롯데캐슬|\d{1,4}동)", text):
        return "apt"

    return "etc"


def extract_dong_ho_for_header(detail):
    parsed = parse_article_name_for_header(detail)

    dong = clean_header_value(
        detail.get("dong")
        or detail.get("building_dong")
        or detail.get("article_dong")
        or parsed.get("dong")
        or ""
    )

    ho = clean_header_value(
        detail.get("ho")
        or detail.get("unit_ho")
        or detail.get("article_ho")
        or ""
    )

    dong_ho = clean_header_value(
        detail.get("dong_ho")
        or detail.get("dongho")
        or detail.get("dong_ho_text")
        or ""
    )

    return dong, ho, dong_ho


def call_header_image_api(payload):
    url = AI_REALESTATE_API_URL.rstrip("/") + "/generate-header-image"

    res = requests.post(
        url,
        json=payload,
        headers={
            "Content-Type": "application/json",
            "X-API-KEY": AI_SHORTS_API_KEY,
        },
        timeout=180,
    )

    try:
        data = res.json()
    except Exception:
        raise Exception(f"header image api json parse failed: HTTP {res.status_code} / {res.text[:500]}")

    if res.status_code >= 400 or not data.get("ok"):
        raise Exception(data.get("error") or data.get("message") or f"header image api failed: HTTP {res.status_code}")

    return data


def generate_header_image_for_article(detail, article_no):
    parsed_name = parse_article_name_for_header(detail)
    dong, ho, dong_ho = extract_dong_ho_for_header(detail)
    realtor_info = detail.get("realtor_info") or {}

    property_type = detect_property_type_for_header(detail)
    complex_name = choose_complex_name_for_header(detail, parsed_name)

    if not dong:
        possible_title = clean_header_value(detail.get("article_name") or "")
        m = re.search(r"(.+?)\s+(\d{1,4}동)\s*$", possible_title)
        if m:
            dong = clean_header_value(m.group(2))

    complex_name = normalize_title_duplicate_dong(complex_name)
    dong = clean_header_value(dong)
    ho = clean_header_value(ho)
    dong_ho = clean_header_value(dong_ho)

    if property_type == "land":
        header_template_code = "LAND_PHOTO_LAYOUT"
    else:
        header_template_code = random.choice([
            "APT_REAL_PHOTO_GLASS",
            "APT_BRIGHT_CARD",
            "APT_DARK_REC",
            "APT_PRICE_BAND",
            "APT_NOTE_CARD",
            "APT_RED_POSTER",
            "APT_CLEAN_WHITE",
            "APT_GREEN_LABEL",
        ])

    payload = {
        "article_no": str(article_no),
        "realtor_id": detail.get("realtor_id") or 0,
        "template_code": header_template_code,
        "property_type": property_type,
        "real_estate_type": clean_header_value(detail.get("real_estate_type") or ""),
        "real_estate_type_name": clean_header_value(detail.get("real_estate_type_name") or detail.get("real_estate_type") or ""),
        "article_real_estate_type_name": clean_header_value(detail.get("_raw_article_real_estate_type_name") or ""),
        "trade_building_type_code": clean_header_value(detail.get("_raw_trade_building_type_code") or ""),
        "article_type_code": clean_header_value(detail.get("_raw_article_type_code") or ""),
        "realestate_type_code": clean_header_value(detail.get("_raw_realestate_type_code") or ""),
        "transaction_type": clean_header_value(detail.get("trade_type") or ""),
        "complex_name": complex_name,
        "exclusive_area": extract_exclusive_area_text(
            detail.get("area_info")
            or detail.get("exclusive_area")
            or ""
        ),
        # 아파트 대표이미지는 동 정보가 있으면 제목에 함께 노출한다.
        # 호수는 노출하지 않는다.
        "dong": dong if property_type == "apt" else "",
        "ho": "",
        "dong_ho": dong if property_type == "apt" else "",
        "title": build_header_display_title(
            detail=detail,
            complex_name=complex_name,
            dong=dong,
            ho=ho,
            dong_ho=dong_ho,
        ),
        "price": compact_price_text(detail.get("price_text") or ""),
        "region_name": clean_header_value(
            detail.get("address")
            or detail.get("road_address")
            or ""
        ),
        "realtor_name": clean_header_value(
            realtor_info.get("office_name")
            or detail.get("office_name")
            or ""
        ),
        # 정사각형 대표이미지에서는 전화번호를 빼고 중개사무소명만 노출한다.
        "phone": "",
        "background_url": detail.get("header_background_url") or detail.get("background_url") or "",
        "background_image_url": detail.get("header_background_url") or detail.get("background_url") or "",
        "width": 800,
        "height": 800,
    }

    print("[HEADER IMAGE PROPERTY_TYPE]", property_type)
    print("[HEADER IMAGE COMPLEX_NAME]", complex_name)
    print("[HEADER IMAGE DONG/HO]", dong, ho, dong_ho)
    print("[HEADER IMAGE API PAYLOAD]", json.dumps(payload, ensure_ascii=False, default=json_default))

    result = call_header_image_api(payload)

    print("[HEADER IMAGE CREATED]", result.get("image_url"))
    print("[HEADER IMAGE BACKGROUND]", result.get("background_path"))
    print("[HEADER IMAGE THEME]", result.get("theme"))

    return result


def save_mybox_asset(conn, article_no, realtor_id, header_image):
    if not header_image or not header_image.get("image_url"):
        return None

    if not table_exists(conn, "blog_mybox_assets"):
        print("[MYBOX ASSET SKIP] blog_mybox_assets table not exists")
        return None

    columns = table_columns(conn, "blog_mybox_assets")

    data = {
        "asset_type": "header_image",
        "asset_category": "blog_header",
        "article_no": str(article_no),
        "realtor_id": int(realtor_id or 0) if realtor_id else None,
        "title": "블로그 대표이미지",
        "local_path": header_image.get("image_path") or "",
        "file_url": header_image.get("image_url") or "",
        "file_name": header_image.get("filename") or "",
        "mime_type": "image/png",
        "file_size": 0,
        "status": "pending_upload",
        "is_representative": 1,
        "created_at": datetime.now(),
        "updated_at": datetime.now(),
    }

    filtered = {
        key: value
        for key, value in data.items()
        if key in columns
    }

    if not filtered:
        print("[MYBOX ASSET SKIP] no matching columns")
        return None

    keys = list(filtered.keys())
    placeholders = ", ".join(["%s"] * len(keys))
    column_sql = ", ".join(keys)
    values = [filtered[key] for key in keys]

    update_sql = ", ".join([
        f"{key}=VALUES({key})"
        for key in keys
        if key not in ["id", "created_at"]
    ])

    sql = f"""
        INSERT INTO blog_mybox_assets
        ({column_sql})
        VALUES ({placeholders})
        ON DUPLICATE KEY UPDATE
            {update_sql}
    """

    with conn.cursor() as cur:
        cur.execute(sql, values)
        asset_id = cur.lastrowid

    conn.commit()

    print(f"[MYBOX ASSET SAVED] article_no={article_no}, asset_id={asset_id}, url={header_image.get('image_url')}")
    return asset_id


def save_header_image_to_article_images(conn, article_no, realtor_id, header_image):
    if not header_image or not header_image.get("image_url"):
        return None

    table_name = "blog_realestate_article_images"

    if not table_exists(conn, table_name):
        print("[HEADER ARTICLE IMAGE SKIP] blog_realestate_article_images table not exists")
        return None

    columns = table_columns(conn, table_name)

    try:
        category_parts = []

        if "image_category" in columns:
            category_parts.append("image_category = 'header_image'")

        if "image_type" in columns:
            category_parts.append("image_type = 'header_image'")

        if category_parts:
            with conn.cursor() as cur:
                cur.execute(
                    f"DELETE FROM {table_name} WHERE article_no = %s AND (" + " OR ".join(category_parts) + ")",
                    (str(article_no),)
                )
            conn.commit()
    except Exception as e:
        print("[HEADER ARTICLE IMAGE DELETE WARN]", str(e))

    image_url = header_image.get("image_url") or ""
    image_path = header_image.get("image_path") or ""
    filename = header_image.get("filename") or ""
    width = int(header_image.get("width") or 0)
    height = int(header_image.get("height") or 0)

    data = {
        "article_no": str(article_no),
        "realtor_id": int(realtor_id or 0) if realtor_id else None,

        "image_url": image_url,
        "source_url": image_url,
        "original_url": image_url,
        "cdn_url": image_url,
        "naver_cdn_url": image_url,

        "local_path": image_path,
        "file_path": image_path,
        "image_path": image_path,

        "file_name": filename,
        "filename": filename,

        "image_type": "header_image",
        "image_category": "header_image",
        "asset_type": "header_image",

        "sort_order": -1000,
        "display_order": -1000,

        "is_representative": 1,
        "is_used": 1,
        "is_downloaded": 1,

        "width": width,
        "height": height,
        "image_width": width,
        "image_height": height,

        "mime_type": "image/png",
        "status": "active",

        "created_at": datetime.now(),
        "updated_at": datetime.now(),
    }

    filtered = {
        key: value
        for key, value in data.items()
        if key in columns
    }

    if not filtered:
        print("[HEADER ARTICLE IMAGE SKIP] no matching columns")
        return None

    keys = list(filtered.keys())
    placeholders = ", ".join(["%s"] * len(keys))
    column_sql = ", ".join(keys)
    values = [filtered[key] for key in keys]

    sql = f"""
        INSERT INTO {table_name}
        ({column_sql})
        VALUES ({placeholders})
    """

    with conn.cursor() as cur:
        cur.execute(sql, values)
        image_id = cur.lastrowid

    conn.commit()

    print(f"[HEADER ARTICLE IMAGE SAVED] article_no={article_no}, image_id={image_id}, url={image_url}")
    return image_id




def save_cdn_asset(conn, article_no, realtor_id, header_image, cdn_result, status="uploaded", error_message=""):
    """
    Cafe24 CDN 업로드 결과를 blog_cdn_assets에 저장한다.
    테이블이 없으면 건너뛴다.
    """
    table_name = "blog_cdn_assets"

    if not table_exists(conn, table_name):
        print("[CDN ASSET SKIP] blog_cdn_assets table not exists")
        return None

    columns = table_columns(conn, table_name)

    local_path = ""
    file_name = ""

    if header_image:
        local_path = header_image.get("image_path") or ""
        file_name = header_image.get("filename") or ""

    cdn_result = cdn_result or {}

    data = {
        "article_no": str(article_no),
        "realtor_id": int(realtor_id or 0) if realtor_id else None,
        "asset_type": "header_image",
        "local_path": local_path,
        "remote_path": cdn_result.get("remote_path") or "",
        "public_path": cdn_result.get("public_path") or "",
        "cdn_url": cdn_result.get("cdn_url") or "",
        "file_name": file_name,
        "file_size": cdn_result.get("size") or 0,
        "mime_type": "image/png",
        "status": status,
        "error_message": error_message[:1000] if error_message else None,
        "uploaded_at": datetime.now() if status == "uploaded" else None,
        "created_at": datetime.now(),
        "updated_at": datetime.now(),
    }

    filtered = {
        key: value
        for key, value in data.items()
        if key in columns
    }

    if not filtered:
        print("[CDN ASSET SKIP] no matching columns")
        return None

    # 같은 매물 대표이미지는 1개만 유지
    try:
        with conn.cursor() as cur:
            cur.execute(
                f"""
                DELETE FROM {table_name}
                WHERE article_no = %s
                  AND asset_type = 'header_image'
                """,
                (str(article_no),)
            )
        conn.commit()
    except Exception as e:
        print("[CDN ASSET DELETE WARN]", str(e))

    keys = list(filtered.keys())
    placeholders = ", ".join(["%s"] * len(keys))
    column_sql = ", ".join(keys)
    values = [filtered[key] for key in keys]

    sql = f"""
        INSERT INTO {table_name}
        ({column_sql})
        VALUES ({placeholders})
    """

    with conn.cursor() as cur:
        cur.execute(sql, values)
        asset_id = cur.lastrowid

    conn.commit()

    print(
        f"[CDN ASSET SAVED] article_no={article_no}, "
        f"asset_id={asset_id}, status={status}, url={cdn_result.get('cdn_url') or ''}"
    )

    return asset_id


def replace_header_image_url(header_image, cdn_result):
    """
    header_image의 image_url을 Cafe24 CDN URL로 교체한다.
    이후 prepend_header_image_to_draft_data()가 CDN URL을 본문 첫 이미지로 넣는다.
    """
    if not header_image or not cdn_result:
        return header_image

    cdn_url = cdn_result.get("cdn_url") or ""
    if not cdn_url:
        return header_image

    header_image = dict(header_image)
    header_image["original_image_url"] = header_image.get("image_url", "")
    header_image["image_url"] = cdn_url
    header_image["cdn_url"] = cdn_url
    header_image["cdn_remote_path"] = cdn_result.get("remote_path", "")
    header_image["cdn_public_path"] = cdn_result.get("public_path", "")
    return header_image



def build_header_image_html(header_image):
    if not header_image or not header_image.get("image_url"):
        return ""

    image_url = clean_text(header_image.get("image_url"))

    if not image_url:
        return ""

    return f"""
<div class="realestate-representative-image" style="text-align:center; margin:0 0 32px 0;">
    <img src="{image_url}" alt="대표이미지" style="width:100%; max-width:800px; height:auto; border-radius:18px; display:block; margin:0 auto;">
</div>
"""


def prepend_header_image_to_draft_data(draft_data, header_image):
    """
    대표이미지를 draft_data 최상단에 삽입한다.
    step8 안정 구조 유지:
    build_blog_html -> header 생성 -> prepend -> save_draft
    """
    if not draft_data:
        return draft_data

    if not header_image:
        print("[REP PREPEND SKIP] header_image empty")
        return draft_data

    image_url = clean_text(
        header_image.get("image_url")
        or header_image.get("cdn_url")
        or header_image.get("file_url")
        or header_image.get("url")
        or ""
    )

    if not image_url:
        print("[REP PREPEND SKIP] image_url empty")
        return draft_data

    header_image = dict(header_image)
    header_image["image_url"] = image_url

    header_html = build_header_image_html(header_image)

    if not header_html:
        print("[REP PREPEND SKIP] header_html empty")
        return draft_data

    html_keys = [
        "draft_html",
        "clipboard_html",
        "preview_html",
        "content_html",
        "body_html",
        "html",
    ]

    inserted = []

    for key in html_keys:
        html = str(draft_data.get(key) or "")

        if not html:
            continue

        if "realestate-representative-image" in html:
            inserted.append(f"{key}:already")
            continue

        draft_data[key] = header_html + "\n\n" + html
        inserted.append(key)

    draft_data["header_image_url"] = image_url
    draft_data["representative_image_url"] = image_url
    draft_data["thumbnail_url"] = image_url

    print("[REP PREPEND DONE]", image_url, "keys=", ",".join(inserted))

    return draft_data



# ---------------------------------------------------------------------
# 중간배너 이미지 생성 / CDN 업로드
# ---------------------------------------------------------------------

def call_middle_banner_image_api(payload):
    """
    중간배너 이미지 생성 API.
    shorts_ai_api.py에 /generate-middle-banner-image가 있으면 사용한다.
    없거나 실패하면 None을 반환하여 기존 텍스트 fallback을 유지한다.
    """
    url = AI_REALESTATE_API_URL.rstrip("/") + "/generate-middle-banner-image"

    try:
        res = requests.post(
            url,
            json=payload,
            headers={
                "Content-Type": "application/json",
                "X-API-KEY": AI_SHORTS_API_KEY,
            },
            timeout=180,
        )

        try:
            data = res.json()
        except Exception:
            print("[MIDDLE BANNER API JSON ERROR]", res.status_code, res.text[:300])
            return None

        if res.status_code >= 400 or not data.get("ok"):
            print("[MIDDLE BANNER API FAIL]", data.get("error") or data.get("message") or res.status_code)
            return None

        return data

    except Exception as e:
        print("[MIDDLE BANNER API ERROR]", str(e))
        return None


def generate_middle_banner_image_for_article(detail, article_no):
    """
    중간 CTA 배너용 600x200 이미지 생성.
    생성 실패 시 None 반환 → 본문은 안전 텍스트 배너 fallback 사용.
    """
    realtor_info = detail.get("realtor_info") or {}

    office_name = clean_header_value(
        realtor_info.get("office_name")
        or detail.get("office_name")
        or "중개사무소"
    )

    phone = clean_header_value(
        realtor_info.get("office_phone")
        or realtor_info.get("mobile_phone")
        or detail.get("office_phone")
        or detail.get("mobile_phone")
        or detail.get("phone")
        or ""
    )

    payload = {
        "article_no": str(article_no),
        "realtor_id": detail.get("realtor_id") or 0,
        "property_type": detect_property_type_for_header(detail),
        "real_estate_type": clean_header_value(detail.get("real_estate_type") or ""),
        "real_estate_type_name": clean_header_value(detail.get("real_estate_type_name") or detail.get("real_estate_type") or ""),
        "article_real_estate_type_name": clean_header_value(detail.get("_raw_article_real_estate_type_name") or ""),
        "trade_building_type_code": clean_header_value(detail.get("_raw_trade_building_type_code") or ""),
        "article_type_code": clean_header_value(detail.get("_raw_article_type_code") or ""),
        "realestate_type_code": clean_header_value(detail.get("_raw_realestate_type_code") or ""),
        "office_name": office_name,
        "realtor_name": office_name,
        "phone": phone,
        "width": 600,
        "height": 200,
    }

    print("[MIDDLE BANNER API PAYLOAD]", json.dumps(payload, ensure_ascii=False, default=json_default))

    result = call_middle_banner_image_api(payload)

    if not result:
        print("[MIDDLE BANNER CREATED] skipped/fallback")
        return None

    print("[MIDDLE BANNER CREATED]", result.get("image_url"))
    print("[MIDDLE BANNER BACKGROUND]", result.get("background_path"))
    print("[MIDDLE BANNER THEME]", result.get("theme"))

    return result


def upload_middle_banner_to_cafe24(conn, article_no, banner_image):
    """
    중간배너도 대표이미지와 동일한 Cafe24 CDN으로 업로드한다.
    기존 upload_header_image_to_cafe24() 함수를 재사용하되 filename에 middle_banner가 들어가도록 한다.
    """
    if not banner_image:
        return None

    local_path = banner_image.get("image_path") or ""
    filename = banner_image.get("filename") or ""

    if not local_path:
        print("[MIDDLE BANNER CDN SKIP] local_path empty")
        return None

    if not filename:
        filename = f"middle_banner_{article_no}.png"

    try:
        cdn_result = upload_header_image_to_cafe24(
            conn=conn,
            article_no=article_no,
            local_path=local_path,
            filename=filename,
        )

        if cdn_result and cdn_result.get("cdn_url"):
            print("[MIDDLE BANNER CDN UPLOAD OK]", cdn_result.get("cdn_url"))
            return cdn_result

        print("[MIDDLE BANNER CDN UPLOAD EMPTY]")
        return None

    except Exception as e:
        print("[MIDDLE BANNER CDN UPLOAD ERROR]", str(e))
        return None


def replace_middle_banner_url(banner_image, cdn_result):
    if not banner_image:
        return banner_image

    banner_image = dict(banner_image)

    if cdn_result and cdn_result.get("cdn_url"):
        banner_image["original_image_url"] = banner_image.get("image_url", "")
        banner_image["image_url"] = cdn_result.get("cdn_url")
        banner_image["cdn_url"] = cdn_result.get("cdn_url")
        banner_image["cdn_remote_path"] = cdn_result.get("remote_path", "")
        banner_image["cdn_public_path"] = cdn_result.get("public_path", "")

    return banner_image



def get_realtor_draft_buffer_target(row, default_target=45):
    """
    중개사별 초안 버퍼 목표값.
    blog_realtors.draft_buffer_target이 있으면 우선 사용하고,
    없거나 0 이하이면 기본값을 사용한다.
    """
    try:
        value = int((row or {}).get("draft_buffer_target") or 0)
        if value > 0:
            return value
    except Exception:
        pass

    try:
        default_target = int(default_target or 45)
    except Exception:
        default_target = 45

    return max(1, default_target)


def count_active_draft_buffer(conn, realtor_id):
    """
    중개사별 현재 사용 가능한 초안/발행대기 재고 수.
    - published/private_done 등 완료성 큐는 재고로 보지 않는다.
    - draft만 있고 queue가 없는 경우도 재고로 본다.
    """
    if not table_exists(conn, "blog_article_drafts"):
        return 0

    has_queue = table_exists(conn, "blog_publish_queue")

    if not has_queue:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT COUNT(*) AS cnt
                FROM blog_article_drafts d
                WHERE d.realtor_id = %s
                  AND COALESCE(d.status, 'ready') IN ('ready', 'approved', 'pending')
            """, (int(realtor_id),))
            row = cur.fetchone() or {}
            return int(row.get("cnt") or 0)

    with conn.cursor() as cur:
        cur.execute("""
            SELECT COUNT(DISTINCT d.id) AS cnt
            FROM blog_article_drafts d
            LEFT JOIN blog_publish_queue q
                ON (
                    (q.draft_id IS NOT NULL AND q.draft_id = d.id)
                    OR (
                        CONVERT(q.article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
                        =
                        CONVERT(d.article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
                    )
                )
            WHERE d.realtor_id = %s
              AND COALESCE(d.status, 'ready') IN ('ready', 'approved', 'pending')
              AND (
                    q.id IS NULL
                    OR COALESCE(q.queue_status, 'waiting') IN (
                        'waiting',
                        'pending',
                        'ready',
                        'processing',
                        'failed',
                        'hold'
                    )
              )
        """, (int(realtor_id),))

        row = cur.fetchone() or {}
        return int(row.get("cnt") or 0)



def is_valid_collected_article_for_draft(row):
    """
    STEP89-B 초안 생성 방어 조건.

    C3-36:
    상세수집 완료 후에도 article_name만 placeholder로 남는 케이스가 있어
    raw_json에서 실제 매물명을 복구한 뒤 검증한다.
    """
    if not row:
        return False, "empty row"

    row = normalize_placeholder_article_detail_for_draft(row)

    article_no = str(row.get("article_no") or "").strip()
    article_name = str(row.get("article_name") or "").strip()
    price_text = str(row.get("price_text") or "").strip()
    collect_status = str(row.get("collect_status") or "").strip()

    bad_titles = {
        "",
        "네이버 부동산 후보 매물",
        "네이버 부동산 현재 매물",
        "네이버 부동산 매물",
        "부동산 매물",
        "현재 매물",
        "추천 매물",
    }

    try:
        detail_collected = int(row.get("detail_collected") or 0)
    except Exception:
        detail_collected = 0

    if not article_no:
        return False, "article_no empty"

    if collect_status and collect_status != "collected":
        return False, f"collect_status not collected: {collect_status}"

    if detail_collected != 1:
        return False, f"detail_collected not 1: {detail_collected}"

    if article_name in bad_titles:
        return False, f"bad article_name: {article_name}"

    if not price_text:
        return False, "price_text empty"

    return True, "ok"


def recover_forced_target_from_existing_draft(conn, row):
    """빈 API 응답으로 훼손된 지정 매물을 기존 초안 source_json에서 복구한다."""
    if not isinstance(row, dict):
        return row

    source = parse_json_safely(row.get("existing_draft_source_json"))
    recovered = source.get("detail") if isinstance(source, dict) else {}
    if not isinstance(recovered, dict) or not recovered:
        print(
            "[DRAFT FORCE SOURCE RECOVERY EMPTY]",
            row.get("article_no"),
        )
        return row

    bad_titles = {
        "", "네이버 부동산 후보 매물", "네이버 부동산 현재 매물",
        "네이버 부동산 매물", "부동산 매물", "현재 매물", "추천 매물",
    }
    restored = []
    for key, value in recovered.items():
        if key in {"id", "realtor_id", "article_no"}:
            continue
        current = row.get(key)
        should_restore = current in (None, "", [], {})
        if key == "article_name" and clean_text(current) in bad_titles:
            should_restore = True
        if key == "raw_json":
            current_raw = parse_json_safely(current)
            recovered_raw = parse_json_safely(value)
            current_detail = current_raw.get("articleDetail") if isinstance(current_raw, dict) else {}
            recovered_detail = recovered_raw.get("articleDetail") if isinstance(recovered_raw, dict) else {}
            should_restore = (
                not isinstance(current_detail, dict)
                or not current_detail
            ) and isinstance(recovered_detail, dict) and bool(recovered_detail)
        if should_restore and value not in (None, "", [], {}):
            row[key] = value
            restored.append(key)

    print(
        "[DRAFT FORCE SOURCE RECOVERED]",
        row.get("article_no"),
        "keys=", ",".join(restored) or "none",
    )

    # 빈 API 응답이 덮어쓴 현재 상세 행도 같은 값으로 복구한다.
    if restored:
        columns = table_columns(conn, "blog_realtor_articles")
        update_keys = [
            key for key in restored
            if key in columns and key not in {
                "id", "realtor_id", "article_no", "created_at", "updated_at"
            }
        ]
        if update_keys:
            sets = ", ".join([f"`{key}` = %s" for key in update_keys])
            values = [row.get(key) for key in update_keys]
            values.append(str(row.get("article_no") or ""))
            with conn.cursor() as cur:
                cur.execute(
                    f"UPDATE blog_realtor_articles SET {sets}, updated_at = NOW() WHERE article_no = %s",
                    values,
                )
            conn.commit()
            print(
                "[DRAFT FORCE ARTICLE ROW RESTORED]",
                row.get("article_no"),
                "keys=", ",".join(update_keys),
            )
    return row




def build_auto_publish_enabled_filter_sql(conn, article_alias="a", realtor_alias=None):
    """
    STEP123:
    STEP362: 자동발행 여부는 초안 대상 필터로 사용하지 않는다.
    발행 Queue 생성 시에만 별도로 검사한다.
    """
    try:
        return ""
    except Exception as e:
        print("[AUTO PUBLISH FILTER WARN]", str(e))
        return ""


def fetch_realtors_for_draft_buffer(conn, realtor_id=None, limit_realtors=100):
    """
    초안 보충 대상 중개사 목록.
    detail_collected가 완료되고 이미지가 있는 매물을 가진 중개사만 대상으로 한다.
    """
    columns = table_columns(conn, "blog_realtors")
    has_buffer_col = "draft_buffer_target" in columns

    select_buffer = "r.draft_buffer_target" if has_buffer_col else "NULL AS draft_buffer_target"

    sql = f"""
        SELECT DISTINCT
            r.id,
            r.office_name,
            {select_buffer}
        FROM blog_realtors r
        LEFT JOIN blog_naver_accounts na
            ON na.realtor_id = r.id
        WHERE r.status = 'active'
          AND COALESCE(r.is_collect_enabled, 0) = 1
          AND COALESCE(TRIM(r.naver_realtor_id), '') <> ''
          AND EXISTS (
            SELECT 1
            FROM blog_realtor_articles a
            WHERE a.realtor_id = r.id
              AND COALESCE(a.collect_status, '') = 'collected'
              AND COALESCE(a.detail_collected, 0) = 1
              AND COALESCE(a.article_name, '') NOT IN (
                    '',
                    '네이버 부동산 현재 매물',
                    '네이버 부동산 매물',
                    '부동산 매물',
                    '현재 매물',
                    '추천 매물'
              )
              AND COALESCE(a.price_text, '') <> ''
        )
    """

    sql += build_auto_publish_enabled_filter_sql(
        conn,
        article_alias="a",
        realtor_alias="r",
    )

    params = []

    if realtor_id:
        sql += " AND r.id = %s "
        params.append(int(realtor_id))

    sql += """
        ORDER BY r.id ASC
        LIMIT %s
    """
    params.append(int(limit_realtors or 100))

    with conn.cursor() as cur:
        cur.execute(sql, params)
        return cur.fetchall()


def fetch_target_articles_for_realtor(
    conn,
    realtor_id,
    limit=10,
    force=False
):
    sql = """
        SELECT
            a.*,

            r.office_name,
            r.representative_name,
            r.license_number,
            r.business_number,
            r.office_phone,
            r.mobile_phone,
            r.office_address,
            r.office_detail_address,
            r.address AS realtor_address,
            r.map_image_url,
            r.seo_keywords

        FROM blog_realtor_articles a

        LEFT JOIN blog_realtors r
            ON a.realtor_id = r.id

        WHERE a.realtor_id = %s
          AND COALESCE(a.collect_status, '') = 'collected'
          AND COALESCE(a.detail_collected, 0) = 1
          AND COALESCE(a.article_name, '') NOT IN (
                '',
                '네이버 부동산 현재 매물',
                '네이버 부동산 매물',
                '부동산 매물',
                '현재 매물',
                '추천 매물'
          )
          AND COALESCE(a.price_text, '') <> ''
    """

    sql += build_auto_publish_enabled_filter_sql(conn, article_alias="a")

    params = [int(realtor_id)]

    if not force:
        sql += """
            AND CONVERT(a.article_no USING utf8mb4)
                COLLATE utf8mb4_unicode_ci
            NOT IN (
                SELECT CONVERT(article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
                FROM blog_article_drafts
            )
        """

        if table_exists(conn, "blog_publish_queue"):
            sql += """
                AND CONVERT(a.article_no USING utf8mb4)
                    COLLATE utf8mb4_unicode_ci
                NOT IN (
                    SELECT CONVERT(article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
                    FROM blog_publish_queue
                    WHERE article_no IS NOT NULL
                      AND COALESCE(queue_status, '') IN (
                            'waiting',
                            'pending',
                            'ready',
                            'processing',
                            'published',
                            'success',
                            'done'
                      )
                )
            """

    # 상세수집 작업 Queue에서 넘어온 매물을 먼저 초안으로 연결한다.
    # Queue 대상이 없을 때는 기존 최신순 정렬을 그대로 유지한다.
    if table_exists(conn, "blog_article_work_queue"):
        sql += """
        ORDER BY
            CASE WHEN EXISTS (
                SELECT 1
                FROM blog_article_work_queue w
                WHERE w.realtor_id = a.realtor_id
                  AND BINARY w.article_no = BINARY a.article_no
                  AND COALESCE(w.work_type, 'new_article') = 'new_article'
                  AND COALESCE(w.work_status, 'pending') IN ('pending', 'processing')
            ) THEN 0 ELSE 1 END,
            COALESCE(a.first_posted_at, a.created_at) DESC,
            a.id DESC
        LIMIT %s
        """
    else:
        sql += """
        ORDER BY
            COALESCE(a.first_posted_at, a.created_at) DESC,
            a.id DESC
        LIMIT %s
        """

    params.append(int(limit))

    with conn.cursor() as cur:
        cur.execute(sql, params)
        return cur.fetchall()


def fetch_target_articles(
    conn,
    realtor_id=None,
    article_no=None,
    limit=10,
    force=False,
    draft_buffer=45,
    replenish_count=5,
    limit_realtors=100,
):
    """
    초안 생성 대상 조회 STEP84.

    기본 운영:
    - 중개사별 초안 재고가 draft_buffer_target 미만이면 부족분만 보충한다.
    - 한 번에 너무 많이 만들지 않도록 replenish_count로 중개사별 보충 상한을 둔다.
    - 전체 생성량은 --limit로 제한한다.

    테스트/특수:
    - --article-no 또는 --force는 기존 방식에 가깝게 동작한다.
    """
    if article_no:
        sql = """
            SELECT
                a.*,

                r.office_name,
                r.representative_name,
                r.license_number,
                r.business_number,
                r.office_phone,
                r.mobile_phone,
                r.office_address,
                r.office_detail_address,
                r.address AS realtor_address,
                r.map_image_url,
                r.seo_keywords,
                d.source_json AS existing_draft_source_json

            FROM blog_realtor_articles a

            LEFT JOIN blog_realtors r
                ON a.realtor_id = r.id

            LEFT JOIN blog_article_drafts d
                ON CONVERT(d.article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
                 = CONVERT(a.article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci

            WHERE a.article_no = %s
        """

        if not force:
            sql += """
              AND COALESCE(a.collect_status, '') = 'collected'
              AND COALESCE(a.detail_collected, 0) = 1
              AND COALESCE(a.article_name, '') NOT IN (
                    '',
                    '네이버 부동산 현재 매물',
                    '네이버 부동산 매물',
                    '부동산 매물',
                    '현재 매물',
                    '추천 매물'
              )
              AND COALESCE(a.price_text, '') <> ''
            """
            sql += build_auto_publish_enabled_filter_sql(conn, article_alias="a")

        sql += """
            LIMIT %s
        """

        params = [str(article_no), int(limit)]

        with conn.cursor() as cur:
            cur.execute(sql, params)
            return cur.fetchall()

    if force:
        # 강제 재생성은 지정 중개사 또는 전체에서 limit만큼 기존 방식으로 처리
        sql = """
            SELECT
                a.*,

                r.office_name,
                r.representative_name,
                r.license_number,
                r.business_number,
                r.office_phone,
                r.mobile_phone,
                r.office_address,
                r.office_detail_address,
                r.address AS realtor_address,
                r.map_image_url,
                r.seo_keywords

            FROM blog_realtor_articles a

            LEFT JOIN blog_realtors r
                ON a.realtor_id = r.id

            WHERE COALESCE(a.collect_status, '') = 'collected'
              AND COALESCE(a.detail_collected, 0) = 1
              AND COALESCE(a.article_name, '') NOT IN (
                    '',
                    '네이버 부동산 현재 매물',
                    '네이버 부동산 매물',
                    '부동산 매물',
                    '현재 매물',
                    '추천 매물'
              )
              AND COALESCE(a.price_text, '') <> ''
        """

        params = []

        if realtor_id:
            sql += " AND a.realtor_id = %s "
            params.append(int(realtor_id))

        sql += """
            ORDER BY
                COALESCE(a.first_posted_at, a.created_at) DESC,
                a.id DESC
            LIMIT %s
        """
        params.append(int(limit))

        with conn.cursor() as cur:
            cur.execute(sql, params)
            return cur.fetchall()

    total_limit = int(limit or 0)
    if total_limit <= 0:
        total_limit = 1

    try:
        replenish_count = int(replenish_count or 0)
    except Exception:
        replenish_count = 5

    if replenish_count <= 0:
        replenish_count = total_limit

    articles = []
    realtors = fetch_realtors_for_draft_buffer(
        conn=conn,
        realtor_id=realtor_id,
        limit_realtors=limit_realtors,
    )

    print(f"[DRAFT BUFFER REALTORS] {len(realtors)}")

    for realtor in realtors:
        rid = int(realtor.get("id") or 0)
        if not rid:
            continue

        target = get_realtor_draft_buffer_target(realtor, default_target=draft_buffer)
        current = count_active_draft_buffer(conn, rid)
        # 기존 발행대기/초안 재고와 관계없이 매 실행마다 최신 미초안 1건을 생성한다.
        need = 1

        remaining_global = total_limit - len(articles)
        if remaining_global <= 0:
            break

        take = min(1, replenish_count, remaining_global)

        print(
            f"[DRAFT BUFFER NEED] realtor_id={rid}, "
            f"current={current}, target={target}, need={need}, take={take}"
        )

        rows = fetch_target_articles_for_realtor(
            conn=conn,
            realtor_id=rid,
            limit=take,
            force=False,
        )

        print(
            f"[DRAFT BUFFER TARGETS] realtor_id={rid}, "
            f"rows={len(rows)}"
        )

        articles.extend(rows)

        if len(articles) >= total_limit:
            break

    return articles


def mark_article_work_completed(conn, realtor_id, article_no):
    """초안과 발행 Queue 생성이 끝난 작업만 완료 처리한다."""
    if not table_exists(conn, "blog_article_work_queue"):
        return 0

    with conn.cursor() as cur:
        cur.execute("""
            UPDATE blog_article_work_queue
            SET work_status = 'completed',
                updated_at = NOW()
            WHERE realtor_id = %s
              AND BINARY article_no = BINARY %s
              AND COALESCE(work_type, 'new_article') = 'new_article'
              AND COALESCE(work_status, 'pending') IN ('pending', 'processing')
        """, (int(realtor_id), str(article_no)))
        affected = cur.rowcount

    conn.commit()

    if affected:
        print(
            f"[ARTICLE WORK COMPLETED] realtor_id={realtor_id}, "
            f"article_no={article_no}, affected={affected}"
        )

    return affected



# ---------------------------------------------------------
# STEP111-13 HEE Layout Recommendation - Log Only
# ---------------------------------------------------------
def _hee_clean_text_for_layout(value):
    import re
    from html import unescape
    value = "" if value is None else str(value)
    value = unescape(value)
    value = re.sub(r"<[^>]+>", " ", value)
    value = re.sub(r"\s+", " ", value)
    return value.strip()


def _hee_detect_property_class_for_layout(detail):
    raw = parse_json_safely(detail.get("raw_json"))
    article_detail = raw.get("articleDetail") if isinstance(raw, dict) else {}
    article_addition = raw.get("articleAddition") if isinstance(raw, dict) else {}

    type_name = _hee_clean_text_for_layout(
        detail.get("real_estate_type")
        or detail.get("real_estate_type_name")
        or (article_detail.get("realestateTypeName") if isinstance(article_detail, dict) else "")
        or (article_addition.get("articleRealEstateTypeName") if isinstance(article_addition, dict) else "")
        or (article_addition.get("realEstateTypeName") if isinstance(article_addition, dict) else "")
    )

    type_code = _hee_clean_text_for_layout(
        (article_detail.get("articleTypeCode") if isinstance(article_detail, dict) else "")
        or (article_detail.get("realestateTypeCode") if isinstance(article_detail, dict) else "")
        or (article_addition.get("articleRealEstateTypeCode") if isinstance(article_addition, dict) else "")
        or (article_addition.get("realEstateTypeCode") if isinstance(article_addition, dict) else "")
    ).upper()

    if type_name in ["아파트"] or type_code in ["A01", "APT"]:
        return "apartment"
    if type_name in ["오피스텔"]:
        return "officetel"
    if type_name in ["빌라", "연립", "다세대", "주택"]:
        return "villa_house"
    if type_name in ["상가", "상가점포"]:
        return "store"
    if type_name in ["사무실"]:
        return "office"
    if type_name in ["공장", "창고"]:
        return "factory_warehouse"
    if type_name in ["토지", "대", "전", "답", "임야", "대지", "잡종지", "공장용지", "창고용지"] or type_code in ["TJ", "LAND", "TOJI", "E03"]:
        return "land"
    if type_name in ["분양권", "입주권"]:
        return "presale_right"

    return "unknown"


def _hee_layout_mode_from_detail(detail, images, ai_sections=None):
    property_class = _hee_detect_property_class_for_layout(detail)

    try:
        image_count = len(images or [])
    except Exception:
        image_count = 0

    broker_desc = _hee_clean_text_for_layout(
        detail.get("article_feature_desc")
        or detail.get("article_desc")
        or detail.get("article_description")
        or ""
    )
    broker_desc_len = len(broker_desc)

    if broker_desc_len <= 0 and ai_sections:
        broker_desc_len = len(_hee_clean_text_for_layout((ai_sections or {}).get("intro") or ""))

    if image_count <= 0 and broker_desc_len < 20:
        base = "minimal"
    elif image_count <= 1 or broker_desc_len < 40:
        base = "compact"
    elif image_count <= 4 or broker_desc_len < 80:
        base = "simple"
    elif image_count <= 10:
        base = "balanced"
    else:
        base = "rich"

    if property_class == "land":
        layout_mode = f"land_{base}"
    elif property_class == "factory_warehouse":
        layout_mode = f"factory_{base}"
    elif property_class in ["store", "office"]:
        layout_mode = f"business_{base}"
    elif property_class in ["apartment", "officetel", "villa_house", "presale_right"]:
        layout_mode = f"living_{base}"
    else:
        layout_mode = f"generic_{base}"

    return {
        "property_class": property_class,
        "image_count": image_count,
        "broker_desc_len": broker_desc_len,
        "layout_mode": layout_mode,
    }


def _hee_recommended_layout_type(layout_mode, realtor_id=None, article_no=None):
    import random

    candidates_map = {
        "living_rich": ["storytelling", "magazine_style", "local_life", "family_recommend", "premium_report", "gallery_story"],
        "living_balanced": ["story_first", "location_focus", "recommendation_focus", "storytelling", "local_life", "photo_grid"],
        "living_simple": ["summary_first", "minimal_modern", "short_review_style", "checklist_style"],
        "living_compact": ["summary_first", "minimal_modern", "checklist_style"],
        "living_minimal": ["summary_first", "minimal_modern"],

        "business_rich": ["office_focus", "location_focus", "broker_expert", "premium_report"],
        "business_balanced": ["office_focus", "location_focus", "summary_first", "checklist_style"],
        "business_simple": ["office_focus", "summary_first", "minimal_modern"],
        "business_compact": ["summary_first", "minimal_modern"],
        "business_minimal": ["summary_first", "minimal_modern"],

        "factory_rich": ["office_focus", "broker_expert", "checklist_style", "summary_first"],
        "factory_balanced": ["office_focus", "summary_first", "checklist_style"],
        "factory_simple": ["summary_first", "checklist_style", "minimal_modern"],
        "factory_compact": ["summary_first", "minimal_modern"],
        "factory_minimal": ["summary_first", "minimal_modern"],

        "land_rich": ["land_focus"],
        "land_balanced": ["land_focus"],
        "land_simple": ["land_focus"],
        "land_compact": ["land_focus"],
        "land_minimal": ["land_focus"],

        "generic_rich": ["story_first", "summary_first", "checklist_style"],
        "generic_balanced": ["summary_first", "checklist_style", "minimal_modern"],
        "generic_simple": ["summary_first", "minimal_modern"],
        "generic_compact": ["summary_first", "minimal_modern"],
        "generic_minimal": ["summary_first", "minimal_modern"],
    }

    candidates = candidates_map.get(layout_mode) or candidates_map.get("generic_simple")
    seed_text = f"{realtor_id or ''}:{article_no or ''}:{layout_mode}"
    rnd = random.Random(seed_text)

    return rnd.choice(candidates), candidates


def hee_log_layout_recommendation(detail, images, ai_sections, current_layout_type):
    try:
        info = _hee_layout_mode_from_detail(detail, images, ai_sections)
        recommended, candidates = _hee_recommended_layout_type(
            info.get("layout_mode"),
            realtor_id=detail.get("realtor_id"),
            article_no=detail.get("article_no"),
        )

        print(
            "[HEE LAYOUT RECOMMEND] "
            f"property_class={info.get('property_class')}, "
            f"image_count={info.get('image_count')}, "
            f"broker_desc_len={info.get('broker_desc_len')}, "
            f"layout_mode={info.get('layout_mode')}, "
            f"recommended={recommended}, "
            f"current={current_layout_type}, "
            f"apply=False"
        )
    except Exception as e:
        print("[HEE LAYOUT RECOMMEND ERROR]", str(e))


def fetch_article_images(conn, article_no):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT *
            FROM blog_realestate_article_images
            WHERE article_no = %s
            ORDER BY
                CASE
                    WHEN image_category = 'inside' THEN 1
                    WHEN image_category = 'article' THEN 2
                    WHEN image_category = 'building' THEN 3
                    WHEN image_category = 'floorplan' THEN 4
                    WHEN image_category = 'birdview' THEN 5
                    WHEN image_category = 'realtor' THEN 9
                    ELSE 6
                END,
                sort_order ASC,
                id ASC
        """, (article_no,))

        return cur.fetchall()


def filter_images_for_photo_missing_listing(detail, images):
    """
    수집 사진은 분류 컬럼이 불완전해도 유효한 URL이 있으면 보존한다.

    명백한 중개사 프로필·자동 생성 이미지·배너만 매물 사진 판정에서
    제외하고, 비주거 매물에는 잘못 연결된 아파트 단지 공용사진만
    제거한다. articleAddition.siteImageCount는 실제 articlePhotos와
    불일치할 수 있으므로 사진 유무 판정에 사용하지 않는다.
    """
    rows = list(images or [])
    real_estate_type = clean_text(
        detail.get("real_estate_type")
        or detail.get("realEstateTypeName")
        or detail.get("real_estate_type_name")
        or detail.get("article_type")
        or ""
    ).lower()
    non_residential_listing = any(
        keyword in real_estate_type
        for keyword in (
            "상가", "상업", "건물", "빌딩", "토지", "공장", "창고",
            "지식산업센터", "사무실", "commercial", "retail", "land",
            "factory", "warehouse",
        )
    )

    def image_text(row):
        return " ".join([
            clean_text(row.get("image_category")),
            clean_text(row.get("image_type")),
            clean_text(row.get("asset_type")),
            clean_text(row.get("image_url")),
            clean_text(row.get("source_url")),
            clean_text(row.get("original_url")),
            clean_text(row.get("cdn_url")),
            clean_text(row.get("local_path")),
            clean_text(row.get("file_name")),
            clean_text(row.get("filename")),
        ]).lower()

    complex_gallery_keywords = (
        "ground_gallery",
        "complex_photo",
        "apt_realimage",
        "hscp_img",
    )
    generated_or_auxiliary_keywords = (
        "header_image",
        "middle_banner",
        "blog_header",
        "representative_image",
        "map_image",
        "location_map",
    )
    realtor_profile_keywords = (
        "realtor",
        "profile",
        "broker",
        "rltr_profile",
    )

    has_listing_photo = False
    safe_rows = []

    for row in rows:
        text = image_text(row)
        if any(keyword in text for keyword in realtor_profile_keywords):
            safe_rows.append(row)
            continue

        if any(keyword in text for keyword in generated_or_auxiliary_keywords):
            safe_rows.append(row)
            continue

        if (
            non_residential_listing
            and any(keyword in text for keyword in complex_gallery_keywords)
        ):
            continue

        url = clean_text(
            row.get("image_url")
            or row.get("source_url")
            or row.get("original_url")
            or row.get("cdn_url")
            or row.get("public_url")
            or row.get("local_path")
            or ""
        )
        if not url:
            continue

        # articlePhotos에서 저장된 정상 행은 물론, 과거 DB에서
        # image_category/image_type이 일반값으로 저장된 행도 실제 사진으로
        # 취급한다. 분류 키워드가 없다는 이유만으로 사진을 버리지 않는다.
        safe_rows.append(row)
        has_listing_photo = True

    detail["_has_listing_photo"] = has_listing_photo

    if rows and not has_listing_photo:
        print(
            "[DRAFT NO LISTING PHOTO]",
            detail.get("article_no") or "",
            f"input={len(rows)}",
            f"kept_auxiliary={len(safe_rows)}",
            "header_realtor_profile_and_banner_only",
        )

    return safe_rows


def fetch_article_prices(conn, article_no):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT *
            FROM blog_realestate_article_prices
            WHERE article_no = %s
            ORDER BY id ASC
            LIMIT 20
        """, (article_no,))

        return cur.fetchall()


def fetch_article_schools(conn, article_no):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT *
            FROM blog_realestate_article_schools
            WHERE article_no = %s
            ORDER BY id ASC
            LIMIT 20
        """, (article_no,))

        return cur.fetchall()


def filter_verified_article_schools(schools, article_no=""):
    """
    초안에는 구조화된 정식 학교명만 전달한다.

    과거 수집기의 매물 설명 보조 추출 결과에는 distance_text 등에
    '매물 설명 기준 인근'이 기록된다. 이 자료는 일반 문장의 '고'를
    학교급으로 오인할 수 있으므로 초안에서 사용하지 않는다.
    """
    verified = []

    for school in schools or []:
        school_name = clean_text(school.get("school_name"))
        evidence_text = " ".join(
            clean_text(school.get(key))
            for key in (
                "distance_text",
                "distance",
                "source",
                "source_type",
                "data_source",
                "memo",
            )
        )

        reject_reason = ""
        if "매물 설명 기준 인근" in evidence_text:
            reject_reason = "description_fallback"
        elif not re.search(r"(?:초등학교|중학교|고등학교)$", school_name):
            reject_reason = "invalid_school_name"

        if reject_reason:
            print(
                f"[DRAFT SCHOOL FILTERED] {article_no} "
                f"reason={reject_reason} name={school_name}"
            )
            continue

        verified.append(school)

    return verified


def save_draft(
    conn,
    realtor_id,
    article_no,
    draft_data,
    ai_sections,
    source_json,
):
    # 렌더러 또는 원천 데이터 어느 쪽에서 중복이 유입되어도 DB 저장 전에
    # 최종 제목을 한 번 더 정리한다.
    draft_title = normalize_adjacent_title_duplicates(
        draft_data.get("draft_title", "")
    )[:255]
    draft_data["draft_title"] = draft_title
    layout_type = draft_data.get("layout_type", "default")

    draft_html = draft_data.get("draft_html", "")
    clipboard_html = draft_data.get("clipboard_html", "")
    plain_text = draft_data.get("plain_text", "")

    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO blog_article_drafts
            (
                realtor_id,
                article_no,
                draft_title,
                layout_type,
                draft_html,
                clipboard_html,
                plain_text,
                ai_summary,
                ai_sections,
                source_json,
                status,
                created_at,
                updated_at
            )
            VALUES
            (
                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                'ready',
                NOW(),
                NOW()
            )

            ON DUPLICATE KEY UPDATE
                draft_title = VALUES(draft_title),
                layout_type = VALUES(layout_type),
                draft_html = VALUES(draft_html),
                clipboard_html = VALUES(clipboard_html),
                plain_text = VALUES(plain_text),
                ai_summary = VALUES(ai_summary),
                ai_sections = VALUES(ai_sections),
                source_json = VALUES(source_json),
                status = 'ready',
                updated_at = NOW()
        """, (
            realtor_id,
            article_no,
            draft_title,
            layout_type,
            draft_html,
            clipboard_html,
            plain_text,
            ai_sections.get("intro", "")[:1000],
            json.dumps(ai_sections, ensure_ascii=False, default=json_default),
            json.dumps(source_json, ensure_ascii=False, default=json_default),
        ))

    conn.commit()

    # 대표이미지 URL 컬럼이 존재하는 경우 별도 업데이트
    try:
        columns = table_columns(conn, "blog_article_drafts")
        update_data = {}

        if "header_image_url" in columns:
            update_data["header_image_url"] = draft_data.get("header_image_url", "")

        if "representative_image_url" in columns:
            update_data["representative_image_url"] = draft_data.get("representative_image_url", "")

        if update_data:
            sets = ", ".join([f"{key} = %s" for key in update_data.keys()])
            values = list(update_data.values())
            values.append(article_no)

            with conn.cursor() as cur:
                cur.execute(
                    f"UPDATE blog_article_drafts SET {sets}, updated_at = NOW() WHERE article_no = %s",
                    values
                )

            conn.commit()
    except Exception as e:
        print("[DRAFT HEADER IMAGE URL UPDATE WARN]", str(e))





def get_publish_execution_target(conn, realtor_id):
    """
    C3-35:
    자동발행 ON/OFF는 수집/초안/Queue 생성 여부를 제어하고,
    execution_target은 실제 발행 주체(server/client)를 제어한다.

    blog_publish_settings.execution_target 값:
    - server: 기존 서버 publish_worker 발행
    - client: 클라이언트 셋업 프로그램 발행

    컬럼/테이블이 없거나 값이 비정상이면 기존 운영 안전을 위해 server 기본값.
    """
    default_target = "server"

    try:
        if not table_exists(conn, "blog_publish_settings"):
            return default_target

        columns = table_columns(conn, "blog_publish_settings")
        if "execution_target" not in columns:
            return default_target

        with conn.cursor() as cur:
            cur.execute("""
                SELECT execution_target
                FROM blog_publish_settings
                WHERE realtor_id = %s
                LIMIT 1
            """, (int(realtor_id or 0),))
            row = cur.fetchone() or {}

        value = clean_text(row.get("execution_target")).lower()

        if value in ["server", "client"]:
            return value

        return default_target

    except Exception as e:
        print("[PUBLISH EXECUTION TARGET WARN]", realtor_id, str(e))
        return default_target


def is_publish_queue_enabled(conn, realtor_id):
    """자동발행 ON인 중개사에만 실제 발행 Queue를 생성한다."""
    try:
        if not table_exists(conn, "blog_publish_settings"):
            return False
        columns = table_columns(conn, "blog_publish_settings")
        select_parts = [
            "COALESCE(auto_publish_enabled, 0) AS auto_publish_enabled"
            if "auto_publish_enabled" in columns else "0 AS auto_publish_enabled",
            "COALESCE(daily_post_limit, 0) AS daily_post_limit"
            if "daily_post_limit" in columns else "1 AS daily_post_limit",
        ]
        with conn.cursor() as cur:
            cur.execute(
                f"SELECT {', '.join(select_parts)} FROM blog_publish_settings WHERE realtor_id=%s LIMIT 1",
                (int(realtor_id or 0),),
            )
            row = cur.fetchone() or {}
        return int(row.get("auto_publish_enabled") or 0) == 1 and int(row.get("daily_post_limit") or 0) > 0
    except Exception as e:
        print("[PUBLISH QUEUE POLICY WARN]", realtor_id, str(e))
        return False


def create_publish_queue(conn, realtor_id, article_no, draft_id):
    """
    blog_article_drafts 생성 후 발행 대기 큐를 만든다.
    이미 같은 draft_id 또는 article_no의 pending/processing/published 큐가 있으면 중복 생성하지 않는다.
    failed/hold 큐가 있으면 pending으로 복구한다.
    """
    if not table_exists(conn, "blog_publish_queue"):
        print("[PUBLISH QUEUE SKIP] blog_publish_queue table not exists")
        return None

    columns = table_columns(conn, "blog_publish_queue")

    # 1) 기존 큐 확인
    where_parts = []
    params = []

    if "draft_id" in columns:
        where_parts.append("draft_id = %s")
        params.append(int(draft_id))

    if "article_no" in columns:
        where_parts.append("article_no = %s")
        params.append(str(article_no))

    existing = None

    if where_parts:
        sql = f"""
            SELECT *
            FROM blog_publish_queue
            WHERE ({' OR '.join(where_parts)})
            ORDER BY id DESC
            LIMIT 1
        """

        with conn.cursor() as cur:
            cur.execute(sql, params)
            existing = cur.fetchone()

    if existing:
        existing_id = existing.get("id")
        existing_status = str(existing.get("queue_status") or existing.get("status") or "").strip()

        if existing_status in ["waiting", "pending", "ready", "processing", "published"]:
            print(
                f"[PUBLISH QUEUE EXISTS] queue_id={existing_id}, "
                f"status={existing_status}, article_no={article_no}, "
                f"execution_target={existing.get('execution_target')}"
            )
            return existing_id

        # hold/failed 등은 테스트와 재생성 편의를 위해 pending 복구
        update_data = {}

        if "queue_status" in columns:
            update_data["queue_status"] = "pending"
        elif "status" in columns:
            update_data["status"] = "pending"

        if "error_message" in columns:
            update_data["error_message"] = None

        if "started_at" in columns:
            update_data["started_at"] = None

        if "finished_at" in columns:
            update_data["finished_at"] = None

        if "execution_target" in columns:
            update_data["execution_target"] = get_publish_execution_target(conn, realtor_id)

        if "updated_at" in columns:
            update_data["updated_at"] = datetime.now()

        if update_data:
            sets = ", ".join([f"{key} = %s" for key in update_data.keys()])
            values = list(update_data.values())
            values.append(existing_id)

            with conn.cursor() as cur:
                cur.execute(
                    f"UPDATE blog_publish_queue SET {sets} WHERE id = %s",
                    values
                )

            conn.commit()

        print(
            f"[PUBLISH QUEUE RESET] queue_id={existing_id}, "
            f"status={existing_status} -> pending, article_no={article_no}"
        )
        return existing_id

    # 2) 신규 큐 생성
    execution_target = get_publish_execution_target(conn, realtor_id)

    data = {
        "realtor_id": int(realtor_id or 0),
        "execution_target": execution_target,
        "article_no": str(article_no),
        "draft_id": int(draft_id),
        "queue_status": "pending",
        "status": "pending",
        "priority": 0,
        "error_message": None,
        "created_at": datetime.now(),
        "updated_at": datetime.now(),
    }

    filtered = {
        key: value
        for key, value in data.items()
        if key in columns
    }

    if not filtered:
        print("[PUBLISH QUEUE SKIP] no matching columns")
        return None

    keys = list(filtered.keys())
    column_sql = ", ".join(keys)
    placeholders = ", ".join(["%s"] * len(keys))
    values = [filtered[key] for key in keys]

    sql = f"""
        INSERT INTO blog_publish_queue
        ({column_sql})
        VALUES ({placeholders})
    """

    with conn.cursor() as cur:
        cur.execute(sql, values)
        queue_id = cur.lastrowid

    conn.commit()

    print(
        f"[PUBLISH QUEUE CREATED] queue_id={queue_id}, "
        f"draft_id={draft_id}, realtor_id={realtor_id}, "
        f"article_no={article_no}, execution_target={execution_target}"
    )

    return queue_id



# ---------------------------------------------------------
# STEP102-13: 초안 생성 전 소재지 지번 주소 강제 보강
# ---------------------------------------------------------
def _extract_complex_address_keys_for_draft(detail):
    """
    raw_json / detail_row에서 주소 보강에 필요한 값을 최대한 추출한다.
    - hscpNo/complexNo가 있으면 캐시 조회는 complex_no 우선
    - aptName/building_name/article_name을 단지명 후보로 사용
    - exposureAddress를 region_name으로 사용
    """
    raw = parse_json_safely(detail.get("raw_json"))
    article_detail = raw.get("articleDetail") if isinstance(raw, dict) else {}
    article_addition = raw.get("articleAddition") if isinstance(raw, dict) else {}

    hscp_no = first_non_empty(
        detail.get("complex_no"),
        detail.get("hscp_no"),
        article_detail.get("hscpNo") if isinstance(article_detail, dict) else "",
        article_detail.get("complexNo") if isinstance(article_detail, dict) else "",
        article_addition.get("hscpNo") if isinstance(article_addition, dict) else "",
        raw.get("hscpNo") if isinstance(raw, dict) else "",
        raw.get("complexNo") if isinstance(raw, dict) else "",
    )

    region_name = first_non_empty(
        detail.get("article_address"),
        detail.get("exposure_address"),
        article_detail.get("exposureAddress") if isinstance(article_detail, dict) else "",
        detail.get("address"),
        detail.get("road_address"),
    )

    complex_name = first_non_empty(
        detail.get("complex_name"),
        article_detail.get("aptName") if isinstance(article_detail, dict) else "",
        article_detail.get("complexName") if isinstance(article_detail, dict) else "",
        article_addition.get("complexName") if isinstance(article_addition, dict) else "",
        article_addition.get("aptName") if isinstance(article_addition, dict) else "",
        detail.get("building_name"),
        detail.get("article_name"),
        article_detail.get("articleName") if isinstance(article_detail, dict) else "",
    )

    article_name = first_non_empty(
        detail.get("article_name"),
        article_detail.get("articleName") if isinstance(article_detail, dict) else "",
        detail.get("building_name"),
        complex_name,
    )

    return {
        "raw": raw,
        "article_detail": article_detail if isinstance(article_detail, dict) else {},
        "hscp_no": clean_text(hscp_no),
        "region_name": clean_text(region_name),
        "complex_name": clean_text(complex_name),
        "article_name": clean_text(article_name),
    }


def _fetch_complex_address_cache_for_draft(conn, region_name, complex_name, hscp_no=""):
    """
    초안 생성 단계에서는 단지명보다 complex_no/hscpNo를 우선한다.
    같은 단지명이 여러 지역에 있을 수 있으므로 complex_no가 있으면 가장 안정적이다.
    """
    region_name = clean_text(region_name)
    complex_name = clean_text(complex_name)
    hscp_no = clean_text(hscp_no)

    with conn.cursor() as cur:
        if hscp_no:
            cur.execute("""
                SELECT *
                FROM blog_complex_address_cache
                WHERE complex_no = %s
                ORDER BY last_checked_at DESC, id DESC
                LIMIT 1
            """, (hscp_no,))
            row = cur.fetchone()
            if row:
                return row

        if region_name and complex_name:
            compact = re.sub(r"[\\s\\(\\)\\[\\]\\-_/·ㆍ,]+", "", complex_name)
            compact_no_apt = re.sub(r"아파트$", "", compact)
            names = []
            for v in [complex_name, compact, compact_no_apt, compact_no_apt + "아파트" if compact_no_apt else ""]:
                v = clean_text(v)
                if v and v not in names:
                    names.append(v)

            if names:
                placeholders = ",".join(["%s"] * len(names))
                cur.execute(f"""
                    SELECT *
                    FROM blog_complex_address_cache
                    WHERE region_name = %s
                      AND complex_name IN ({placeholders})
                    ORDER BY last_checked_at DESC, id DESC
                    LIMIT 1
                """, [region_name, *names])
                row = cur.fetchone()
                if row:
                    return row

    return None


def _row_get(row, key, default=""):
    if not row:
        return default
    if isinstance(row, dict):
        return row.get(key, default)
    return default


def _is_apartment_for_complex_address(detail, keys):
    """Limit the complex-address override to Naver apartment articles."""
    raw = keys.get("raw") or {}
    article_detail = keys.get("article_detail") or {}
    article_addition = raw.get("articleAddition") if isinstance(raw, dict) else {}
    if not isinstance(article_addition, dict):
        article_addition = {}

    type_code = first_non_empty(
        detail.get("_raw_realestate_type_code"),
        detail.get("_raw_article_type_code"),
        article_detail.get("realestateTypeCode"),
        article_detail.get("articleTypeCode"),
        article_addition.get("realEstateTypeCode"),
        article_addition.get("articleRealEstateTypeCode"),
    ).upper()
    type_name = first_non_empty(
        detail.get("real_estate_type"),
        detail.get("real_estate_type_name"),
        detail.get("_raw_realestate_type_name"),
        detail.get("_raw_article_real_estate_type_name"),
        article_detail.get("realestateTypeName"),
        article_addition.get("realEstateTypeName"),
        article_addition.get("articleRealEstateTypeName"),
    )
    return type_code == "APT" or type_name == "아파트"


def _is_suspicious_apartment_address(value):
    """아파트 소재지에 중개업소 상가 호수가 섞인 주소를 판별한다."""
    value = clean_text(value)
    return bool(value and re.search(
        r"상가\s*(?:제?\s*\d+\s*동|동)?\s*제?\s*\d+\s*호",
        value,
    ))


def _original_article_address_is_suspicious(keys):
    raw = keys.get("raw") or {}
    article_detail = raw.get("articleDetail") if isinstance(raw, dict) else {}
    article_addition = raw.get("articleAddition") if isinstance(raw, dict) else {}
    if not isinstance(article_detail, dict):
        article_detail = {}
    if not isinstance(article_addition, dict):
        article_addition = {}
    return _is_suspicious_apartment_address(first_non_empty(
        article_detail.get("exposureAddress"),
        article_addition.get("exposureAddress"),
    ))


def _collected_complex_address_for_draft(detail, keys):
    """Use the address already fetched from Naver's matching complex API."""
    if not _is_apartment_for_complex_address(detail, keys):
        return ""

    raw = keys.get("raw") or {}
    if not isinstance(raw, dict):
        return ""
    complex_detail = raw.get("complexDetail") or {}
    candidate = first_non_empty(
        raw.get("complexResolvedAddress"),
        find_address_text_recursive_for_law(complex_detail)
        if isinstance(complex_detail, dict) else "",
    )
    candidate = clean_text(candidate)
    if not candidate or not re.search(r"\d+(?:-\d+)?", candidate):
        return ""

    # A complex API address is a site address, never a realtor's suite.
    # Reject a suspicious office-unit value instead of altering it by regex.
    if _is_suspicious_apartment_address(candidate):
        return ""
    return candidate


def _collected_article_address_for_draft(detail, keys):
    """네이버 매물 원본 주소에 번지/도로번호가 있으면 검색보다 먼저 사용한다."""
    if not _is_apartment_for_complex_address(detail, keys):
        return ""

    raw = keys.get("raw") or {}
    article_detail = raw.get("articleDetail") if isinstance(raw, dict) else {}
    article_addition = raw.get("articleAddition") if isinstance(raw, dict) else {}
    if not isinstance(article_detail, dict):
        article_detail = {}
    if not isinstance(article_addition, dict):
        article_addition = {}

    exposure_address = clean_text(first_non_empty(
        article_detail.get("exposureAddress"),
        article_addition.get("exposureAddress"),
    ))
    candidate = clean_text(first_non_empty(
        article_detail.get("roadAddress"),
        exposure_address,
        article_addition.get("roadAddress"),
    ))
    if not candidate or not re.search(r"\d+(?:-\d+)?", candidate):
        return ""
    if _is_suspicious_apartment_address(candidate):
        return ""
    if exposure_address and candidate != exposure_address:
        candidate = _complete_verified_address_prefix(exposure_address, candidate)
    return candidate


def _safe_administrative_address(value):
    """검색 실패 시 중개업소 번지/호수를 버리고 행정구역까지만 남긴다."""
    value = clean_text(value)
    if not value:
        return ""
    province_short = {
        "서울", "부산", "대구", "인천", "광주", "대전", "울산", "세종",
        "경기", "강원", "충북", "충남", "전북", "전남", "경북", "경남", "제주",
    }
    result = []
    for raw_token in value.split():
        token = raw_token.strip(",()[]")
        if token in province_short or re.search(
            r"(?:특별자치도|특별자치시|특별시|광역시|도|시|군|구)$", token
        ):
            result.append(token)
            continue
        locality = re.match(r"^([가-힣A-Za-z0-9]+?(?:읍|면|동))(?:\d.*)?$", token)
        if locality:
            name = locality.group(1)
            if name not in {"상가", "상가동"}:
                result.append(name)
            break
        if re.search(r"\d|(?:로|길)$|(?:상가|상가동|호)$", token):
            break
    return clean_text(" ".join(result))


def _complete_verified_address_prefix(current, candidate):
    """Restore omitted province/city tokens from the collected article address."""
    current_parts = clean_text(current).split()
    candidate_parts = clean_text(candidate).split()
    if not current_parts or not candidate_parts:
        return clean_text(candidate)

    first = candidate_parts[0]
    try:
        matched_index = current_parts.index(first)
    except ValueError:
        matched_index = -1
    if matched_index > 0:
        return clean_text(" ".join(current_parts[:matched_index] + candidate_parts))
    return clean_text(candidate)


def _verified_naver_search_address(detail, keys):
    """Return a more detailed Naver address only when the same result has schools."""
    raw = keys.get("raw") or {}
    enrichment = raw.get("naverSearchEnrichment") if isinstance(raw, dict) else {}
    if not isinstance(enrichment, dict) or enrichment.get("verified") is not True:
        return ""

    original_suspicious = _original_article_address_is_suspicious(keys)
    search_region = clean_text(enrichment.get("search_region"))
    search_query = clean_text(enrichment.get("query"))
    searched_complex = clean_text(enrichment.get("complex_name"))
    trusted_sanitized_search = bool(
        original_suspicious
        and search_region
        and search_region in search_query
        and searched_complex
        and searched_complex in search_query
    )

    # 오염 주소에서 과거 '단지명만' 검색한 보강값은 거부한다. 최신 수집기가
    # 행정구역+공식 단지명으로 검색한 결과만 별도 신뢰한다.
    if original_suspicious and not trusted_sanitized_search:
        return ""

    schools = enrichment.get("assigned_schools") or []
    has_verified_school = any(
        isinstance(item, dict)
        and clean_text(item.get("school_name") or item.get("name"))
        for item in schools
    )
    if not trusted_sanitized_search and not has_verified_school:
        return ""

    facts = enrichment.get("complex_facts") or {}
    candidate = clean_text(facts.get("address") if isinstance(facts, dict) else "")
    current = clean_text(
        detail.get("article_address")
        or detail.get("exposure_address")
        or detail.get("address")
        or detail.get("road_address")
        or keys.get("region_name")
    )
    if not candidate or not re.search(r"\d+(?:-\d+)?", candidate):
        return ""
    if _is_suspicious_apartment_address(candidate):
        return ""

    if trusted_sanitized_search:
        # 진해구 행암로 25처럼 검색 결과가 일부 행정구역을 생략하면 검색에
        # 사용한 안전한 행정구역을 복원한다.
        candidate_with_prefix = _complete_verified_address_prefix(search_region, candidate)
        if candidate_with_prefix == candidate and not re.match(
            r"^[가-힣]+(?:특별자치도|특별자치시|특별시|광역시|도)\b",
            candidate,
        ):
            candidate_with_prefix = clean_text(f"{search_region} {candidate}")
        return candidate_with_prefix

    # Some apartment article responses expose the realtor office suite as the
    # property address (for example, "상가동102호").  When Naver search has
    # already verified the official complex and its schools, that contaminated
    # address must not veto the verified complex address merely because it is
    # longer or contains an extra locality token.
    current_is_realtor_suite = bool(
        _is_apartment_for_complex_address(detail, keys)
        and _is_suspicious_apartment_address(current)
    )
    if current_is_realtor_suite:
        return _complete_verified_address_prefix(current, candidate)

    # 구/읍/면/동 단위가 서로 다르면 동명이거나 다른 단지일 수 있으므로 사용하지 않는다.
    def locality_tokens(value):
        return set(re.findall(r"[가-힣A-Za-z0-9]+(?:구|읍|면|동)", clean_text(value)))

    current_locality = locality_tokens(current)
    candidate_locality = locality_tokens(candidate)
    if current_locality and not current_locality.issubset(candidate_locality):
        return ""

    # 이미 번지/도로번호가 있는 주소를 더 짧거나 같은 수준의 검색값으로 덮지 않는다.
    if re.search(r"\d+(?:-\d+)?", current) and len(candidate) <= len(current):
        return ""
    return candidate


def _apply_resolved_address(detail, keys, resolved_address, source, complex_no=""):
    detail["address"] = resolved_address
    detail["article_address"] = resolved_address
    detail["exposure_address"] = resolved_address
    detail["complex_resolved_address"] = resolved_address
    detail["complex_address_source"] = source
    if complex_no:
        detail["complex_no"] = complex_no
        detail["hscp_no"] = complex_no

    raw = keys.get("raw") or {}
    if isinstance(raw, dict):
        raw["complexResolvedAddress"] = resolved_address
        raw["complexAddressSource"] = source
        if _is_apartment_for_complex_address(detail, keys):
            # 이후 법정표/SEO가 오염된 원본 주소를 다시 읽지 않도록, 초안용
            # raw_json 안에서도 확정 소재지를 동일하게 사용한다.
            article_detail = raw.get("articleDetail") or {}
            if isinstance(article_detail, dict):
                article_detail["exposureAddress"] = resolved_address
                article_detail["detailAddress"] = ""
            detail["detail_address"] = ""
        if complex_no:
            raw["complexNo"] = complex_no
        try:
            detail["raw_json"] = json.dumps(raw, ensure_ascii=False, default=json_default)
        except Exception:
            pass
    return detail


def apply_complex_resolved_address_to_detail(conn, detail):
    """
    STEP102-13 핵심 보강.
    build_blog_html / 법정표 생성 전에 detail['address']를 지번 포함 주소로 덮어쓴다.
    그래야 핵심정보 표, 법정표, SEO 영역이 모두 같은 주소를 사용한다.
    """
    article_no = clean_text(detail.get("article_no"))
    keys = _extract_complex_address_keys_for_draft(detail)

    region_name = keys["region_name"]
    complex_name = keys["complex_name"]
    hscp_no = keys["hscp_no"]
    article_name = keys["article_name"]

    collected_article_address = _collected_article_address_for_draft(detail, keys)
    if collected_article_address:
        _apply_resolved_address(
            detail,
            keys,
            collected_article_address,
            "naver_article_address",
            hscp_no,
        )
        print(
            f"[DRAFT ADDRESS ARTICLE API] {article_no} "
            f"{collected_article_address} naver_article_address"
        )
        return detail

    # The detail collector already queried Naver with this article's hscpNo.
    # Use that verified complex address before exposureAddress-based search or
    # cache lookup.  exposureAddress can contain the realtor office suite for
    # some apartment listings.
    collected_complex_address = _collected_complex_address_for_draft(detail, keys)
    if collected_complex_address:
        source = clean_text(
            (keys.get("raw") or {}).get("complexAddressSource")
            or "naver_complex_api"
        )
        _apply_resolved_address(
            detail,
            keys,
            collected_complex_address,
            source,
            hscp_no,
        )
        print(
            f"[DRAFT ADDRESS COMPLEX API] {article_no} "
            f"{collected_complex_address} {source}"
        )
        return detail

    search_address = _verified_naver_search_address(detail, keys)
    if search_address:
        _apply_resolved_address(
            detail,
            keys,
            search_address,
            "naver_search_verified_with_schools",
            hscp_no,
        )
        print(
            f"[DRAFT ADDRESS SEARCH VERIFIED] {article_no} "
            f"{search_address} address_and_schools"
        )
        return detail

    if not region_name or not complex_name:
        fallback = clean_text(detail.get("address") or detail.get("road_address") or region_name)
        print(f"[DRAFT ADDRESS RESOLVED] {article_no} {fallback} fallback_missing_keys")
        return detail

    row = _fetch_complex_address_cache_for_draft(
        conn,
        region_name=region_name,
        complex_name=complex_name,
        hscp_no=hscp_no,
    )

    rejected_cached_address = ""
    cached_address = clean_text(_row_get(row, "resolved_address"))
    original_address_suspicious = (
        _is_apartment_for_complex_address(detail, keys)
        and _original_article_address_is_suspicious(keys)
    )
    if (
        row
        and _is_apartment_for_complex_address(detail, keys)
        and (
            _is_suspicious_apartment_address(cached_address)
            or original_address_suspicious
        )
    ):
        rejected_cached_address = cached_address
        print(
            f"[DRAFT ADDRESS CACHE REJECTED] {article_no} "
            f"{cached_address} suspicious_article_or_cache_address"
        )
        row = None

    if not row:
        try:
            from services.naver_search_complex_resolver import resolve_and_cache_complex_address

            row = resolve_and_cache_complex_address(
                conn,
                region_name,
                complex_name,
                force_refresh=bool(rejected_cached_address),
                hscp_no=hscp_no,
                article_name=article_name,
            )
        except Exception as e:
            print(f"[DRAFT ADDRESS RESOLVE ERROR] {article_no} {region_name} / {complex_name} / {e}")
            row = None

    resolved_address = clean_text(_row_get(row, "resolved_address"))
    complex_no = clean_text(_row_get(row, "complex_no") or hscp_no)
    source = clean_text(_row_get(row, "source") or "complex_address_cache")

    if (
        resolved_address
        and _is_apartment_for_complex_address(detail, keys)
        and _is_suspicious_apartment_address(resolved_address)
    ):
        print(
            f"[DRAFT ADDRESS SEARCH REJECTED] {article_no} "
            f"{resolved_address} suspicious_realtor_suite"
        )
        resolved_address = ""

    if resolved_address:
        _apply_resolved_address(detail, keys, resolved_address, source, complex_no)

        print(f"[DRAFT ADDRESS RESOLVED] {article_no} {resolved_address} {source}")
        return detail

    fallback = clean_text(detail.get("address") or detail.get("road_address") or region_name)
    if (
        _is_apartment_for_complex_address(detail, keys)
        and _is_suspicious_apartment_address(fallback)
    ):
        fallback = _safe_administrative_address(fallback)
        if fallback:
            _apply_resolved_address(
                detail,
                keys,
                fallback,
                "safe_administrative_fallback",
                hscp_no,
            )
        print(f"[DRAFT ADDRESS RESOLVED] {article_no} {fallback} safe_administrative_fallback")
        return detail
    print(f"[DRAFT ADDRESS RESOLVED] {article_no} {fallback} fallback_exposureAddress")
    return detail

def process_article(conn, detail_row):
    detail_row = normalize_placeholder_article_detail_for_draft(detail_row)

    article_no = str(detail_row.get("article_no"))
    realtor_id = detail_row.get("realtor_id") or 0

    print("-" * 80)
    print(f"[DRAFT START] {article_no}")

    # STEP102-13: 초안 생성 전에 소재지 지번 주소를 detail_row 전체에 먼저 반영한다.
    detail_row = apply_complex_resolved_address_to_detail(conn, detail_row)

    # STEP272 HOTFIX:
    # 단지명이 없는 연립/다세대/단독 등에서 문자열 "null"이
    # 대표이미지·본문·SEO·해시태그로 전파되는 것을 초안 생성 초기에 차단한다.
    detail_row = apply_property_display_name_hotfix(detail_row)

    # 네이버 원천값에서 매물명/단지명이 중복 조합되는 경우를 초안 렌더링 전에
    # 제거한다. 대표이미지·본문·SEO 제목도 동일한 정리값을 사용하게 된다.
    for title_key in (
        "article_name",
        "building_name",
        "complex_name",
        "article_title",
        "article_complex_name",
        "complex_title",
    ):
        if detail_row.get(title_key):
            detail_row[title_key] = normalize_adjacent_title_duplicates(
                detail_row.get(title_key)
            )

    images = fetch_article_images(conn, article_no)
    images = filter_images_for_photo_missing_listing(detail_row, images)
    prices = fetch_article_prices(conn, article_no)
    schools = filter_verified_article_schools(
        fetch_article_schools(conn, article_no),
        article_no=article_no,
    )
    feature_comment = build_feature_comment_block(
        detail_row
    )

    if not images:
        print(
            "[DRAFT IMAGES EMPTY]",
            article_no,
            "continue with header and realtor banner templates only",
        )

    naver_realtor_id = clean_text(
        detail_row.get("naver_realtor_id")
        or detail_row.get("realtor_id_text")
        or detail_row.get("naver_realtor_code")
        or detail_row.get("realtor_account")
        or ""
    )

    total_listing_url = clean_text(
        detail_row.get("realtor_total_listing_url")
        or detail_row.get("total_listing_url")
        or detail_row.get("agency_listing_url")
        or detail_row.get("fin_land_url")
        or ""
    )

    if not total_listing_url and naver_realtor_id:
        total_listing_url = f"https://m.land.naver.com/agency/info/{naver_realtor_id}"

    detail_row["realtor_total_listing_url"] = total_listing_url
    detail_row["total_listing_url"] = total_listing_url

    # CTA 배너용 전체매물 링크를 한 번 더 보강한다.
    fallback_listing_url = build_realtor_listing_url(detail_row)
    if fallback_listing_url:
        detail_row["realtor_total_listing_url"] = fallback_listing_url
        detail_row["total_listing_url"] = fallback_listing_url
        total_listing_url = fallback_listing_url

    detail_row["middle_banner_image_url"] = normalize_middle_banner_image_url(detail_row)

    detail_row["realtor_info"] = {
        "office_name": detail_row.get("office_name", ""),
        "representative_name": detail_row.get("representative_name", ""),
        "license_number": detail_row.get("license_number", ""),
        "business_number": detail_row.get("business_number", ""),
        "office_phone": detail_row.get("office_phone", ""),
        "mobile_phone": detail_row.get("mobile_phone", ""),
        "office_address": detail_row.get("office_address", ""),
        "office_detail_address": detail_row.get("office_detail_address", ""),
        "address": detail_row.get("realtor_address", ""),
        "map_image_url": detail_row.get("map_image_url", ""),
        "seo_keywords": detail_row.get("seo_keywords", ""),
        "naver_realtor_id": naver_realtor_id,
        "realtor_total_listing_url": total_listing_url,
        "total_listing_url": total_listing_url,
        "realtor_listing_url": total_listing_url,
    }

    detail_row["market_prices"] = prices
    detail_row["schools"] = schools
    detail_row["floor_info_display"] = format_floor_text_for_display(detail_row.get("floor_info"))
    if detail_row["floor_info_display"]:
        detail_row["floor_info"] = detail_row["floor_info_display"]

    human_context = build_human_blog_context(detail_row)

    ai_sections = generate_ai_sections({
        **detail_row,
        **human_context,
    })

    ai_sections = enrich_ai_sections(
        ai_sections,
        human_context,
    )

    property_class = _hee_detect_property_class_for_layout(detail_row)
    property_layouts = {
        "store": ["office_focus", "location_focus", "checklist_style", "summary_first"],
        "office": ["office_focus", "location_focus", "checklist_style", "summary_first"],
        "factory_warehouse": ["office_focus", "checklist_style", "summary_first"],
        "land": ["land_focus"],
    }
    layout_type = random.choice(property_layouts.get(property_class) or [
        "story_first",
        "photo_first",
        "summary_first",
        "gallery_story",
        "location_focus",
        "recommendation_focus",
        "magazine_style",
        "interview_style",
        "checklist_style",
        "quiet_luxury",
        "short_review_style",
        "wide_visual",
        "photo_grid",
        "minimal_modern",
        "storytelling",
        "local_life",
        "broker_expert",
        "premium_report",
    ])
    print(
        "[DRAFT PROPERTY LAYOUT] "
        f"article_no={article_no} class={property_class} layout={layout_type} apply=True"
    )

    # STEP111-15 HEE Policy Service - Log Only
    # 실제 layout_type은 아직 변경하지 않는다. 운영 안전을 위해 apply=False 유지.
    log_policy_recommendation(
        detail=detail_row,
        images=images,
        ai_sections=ai_sections,
        current_layout_type=layout_type,
        apply=False,
    )

    if detail_row.get("_has_listing_photo"):
        extra_images = get_extra_images_if_needed(
            conn=conn,
            realtor_id=realtor_id,
            article_no=article_no,
            images=images,
        )
    else:
        # 사진 없는 매물에는 본문 보강용 임의 이미지를 추가하지 않는다.
        # 대표이미지·중개사 대표자 사진·전체매물 링크 배너만 유지한다.
        extra_images = []

    print(
        f"[EXTRA IMAGES] "
        f"original={len(images)}, "
        f"extra={len(extra_images)}"
    )

    header_background_url = select_header_background_from_article_images(images)
    if header_background_url:
        detail_row["header_background_url"] = header_background_url
        detail_row["background_url"] = header_background_url
        print("[HEADER BACKGROUND SOURCE] collected random:", header_background_url)
    else:
        print("[HEADER BACKGROUND SOURCE] mybox fallback")


    # -------------------------------------------------
    # 중간배너 이미지 생성 → CDN 업로드 → 본문 <img> 배너로 사용
    # -------------------------------------------------
    middle_banner_image = None
    try:
        middle_banner_image = generate_middle_banner_image_for_article(
            detail=detail_row,
            article_no=article_no,
        )

        if middle_banner_image and middle_banner_image.get("image_url"):
            middle_banner_cdn = upload_middle_banner_to_cafe24(
                conn=conn,
                article_no=article_no,
                banner_image=middle_banner_image,
            )

            middle_banner_image = replace_middle_banner_url(
                banner_image=middle_banner_image,
                cdn_result=middle_banner_cdn,
            )

            middle_banner_url = clean_text(
                middle_banner_image.get("cdn_url")
                or middle_banner_image.get("image_url")
                or ""
            )

            if middle_banner_url:
                detail_row["middle_banner_image_url"] = middle_banner_url
                detail_row["realtor_banner_image_url"] = middle_banner_url
                detail_row["banner_image_url"] = middle_banner_url
                print("[MIDDLE BANNER FINAL URL]", middle_banner_url)

                if isinstance(detail_row.get("realtor_info"), dict):
                    detail_row["realtor_info"]["middle_banner_image_url"] = middle_banner_url
                    detail_row["realtor_info"]["realtor_banner_image_url"] = middle_banner_url

    except Exception as e:
        print("[MIDDLE BANNER ERROR]", str(e))

    draft_data = build_blog_html(
        detail=detail_row,
        images=images,
        ai_sections=ai_sections,
        layout_type=layout_type,
        feature_comment=feature_comment,
        extra_images=extra_images,
    )

    if not draft_data:
        raise Exception("draft_data empty")

    # 서버 초안 간소화 정책:
    # 선택 문장/섹션이 제외되어 본문이 짧거나 매물 사진이 적더라도 초안 생성과
    # 발행대기 저장을 막지 않는다. 완전히 빈 HTML만 실제 생성 실패로 본다.
    minimal_draft_html = clean_text(
        draft_data.get("clipboard_html") or draft_data.get("draft_html") or ""
    )
    if not minimal_draft_html:
        raise Exception("draft html empty")

    print(
        "[DRAFT LENGTH AFTER BUILD]",
        "draft_html=", len(str(draft_data.get("draft_html") or "")),
        "clipboard_html=", len(str(draft_data.get("clipboard_html") or "")),
        "plain_text=", len(str(draft_data.get("plain_text") or "")),
    )

    # -------------------------------------------------
    # 대표이미지 V3 생성 → DB 저장 → 본문 첫 이미지로 삽입
    # -------------------------------------------------
    header_image = None
    header_image_error = ""

    try:
        header_image = generate_header_image_for_article(
            detail=detail_row,
            article_no=article_no,
        )

        if header_image and header_image.get("image_url"):
            save_mybox_asset(
                conn=conn,
                article_no=article_no,
                realtor_id=realtor_id,
                header_image=header_image,
            )

            save_header_image_to_article_images(
                conn=conn,
                article_no=article_no,
                realtor_id=realtor_id,
                header_image=header_image,
            )

            # Cafe24 CDN 업로드 후 대표이미지 URL을 CDN URL로 교체
            cdn_result = None
            try:
                cdn_result = upload_header_image_to_cafe24(
                    conn=conn,
                    article_no=article_no,
                    local_path=header_image.get("image_path") or "",
                    filename=header_image.get("filename") or "",
                )

                save_cdn_asset(
                    conn=conn,
                    article_no=article_no,
                    realtor_id=realtor_id,
                    header_image=header_image,
                    cdn_result=cdn_result,
                    status="uploaded",
                )

                header_image = replace_header_image_url(
                    header_image=header_image,
                    cdn_result=cdn_result,
                )

                print("[CDN UPLOAD OK]", cdn_result.get("cdn_url"))

            except Exception as e:
                print("[CDN UPLOAD ERROR]", str(e))

                save_cdn_asset(
                    conn=conn,
                    article_no=article_no,
                    realtor_id=realtor_id,
                    header_image=header_image,
                    cdn_result={},
                    status="failed",
                    error_message=str(e),
                )

                # CDN 실패 시에도 초안 생성은 중단하지 않는다.
                # 단, 이 경우 Python 서버 URL이 들어가므로 운영 발행 전 확인 필요.

            draft_data = prepend_header_image_to_draft_data(
                draft_data=draft_data,
                header_image=header_image,
            )

    except Exception as e:
        header_image_error = str(e)
        print("[HEADER IMAGE ERROR]", header_image_error)

    # -------------------------------------------------
    # 법정 표시광고 표준 표 교체
    # -------------------------------------------------
    draft_data = replace_legal_disclosure_in_draft_data(
        draft_data=draft_data,
        detail=detail_row,
    )

    # -------------------------------------------------
    # 중개사무소 위치 지도 이미지 삽입
    # 법정 표시광고 아래 / 상담 및 중개사 안내 위에 배치
    # -------------------------------------------------
    draft_data = insert_realtor_location_before_contact(
        draft_data=draft_data,
        detail=detail_row,
    )

    # -------------------------------------------------
    # SEO 해시태그 / 검색 노출용 문단 하단 삽입
    # -------------------------------------------------
    draft_data = append_seo_footer_to_draft_data(
        draft_data=draft_data,
        detail=detail_row,
    )

    print(
        "[DRAFT LENGTH BEFORE SAVE]",
        "draft_html=", len(str(draft_data.get("draft_html") or "")),
        "clipboard_html=", len(str(draft_data.get("clipboard_html") or "")),
        "plain_text=", len(str(draft_data.get("plain_text") or "")),
    )

    source_json = {
        "detail": detail_row,
        "images": images,
        "prices": prices,
        "schools": schools,
        "human_context": human_context,
        "feature_comment": feature_comment,
        "header_image": header_image,
        "header_image_error": header_image_error,
        "middle_banner_image": middle_banner_image if 'middle_banner_image' in locals() else None,
    }

    save_draft(
        conn=conn,
        realtor_id=realtor_id,
        article_no=article_no,
        draft_data=draft_data,
        ai_sections=ai_sections,
        source_json=source_json,
    )

    with conn.cursor() as cur:
        cur.execute("""
            SELECT id
            FROM blog_article_drafts
            WHERE article_no = %s
            LIMIT 1
        """, (article_no,))

        saved = cur.fetchone()

    if not saved:
        raise Exception(f"draft save failed: {article_no}")

    draft_id = saved.get("id")

    if is_publish_queue_enabled(conn, realtor_id):
        create_publish_queue(
            conn=conn,
            realtor_id=realtor_id,
            article_no=article_no,
            draft_id=draft_id,
        )
    else:
        print(
            f"[PUBLISH QUEUE SKIP POLICY] realtor_id={realtor_id}, "
            f"article_no={article_no}, draft_id={draft_id}, auto_publish=OFF"
        )

    print(f"[DRAFT SAVED] {article_no}")
    print(f"[LAYOUT] {layout_type}")
    print(f"[IMAGES] {len(images)}")
    print(f"[PRICES] {len(prices)}")
    print(f"[SCHOOLS] {len(schools)}")



def main():
    parser = argparse.ArgumentParser()

    parser.add_argument("--realtor-id", type=int, default=None)
    parser.add_argument("--article-no", type=str, default=None)
    parser.add_argument("--limit", type=int, default=9999)
    parser.add_argument("--draft-buffer", type=int, default=45, help="중개사별 초안 재고 목표 기본값")
    parser.add_argument("--replenish-count", type=int, default=5, help="중개사별 1회 최대 초안 보충 수")
    parser.add_argument("--limit-realtors", type=int, default=100, help="초안 보충 대상 중개사 수 제한")
    parser.add_argument("--force", action="store_true", help="기존 초안이 있어도 강제로 재생성")
    parser.add_argument("--lock-ttl-minutes", type=int, default=240)
    parser.add_argument("--no-db-lock", action="store_true")

    args = parser.parse_args()

    lock_name = (
        f"generate_blog_drafts_{args.realtor_id}"
        if args.realtor_id
        else "generate_blog_drafts"
    )
    lock_owner = None
    run_id = None
    success = 0
    failed = 0
    skipped = 0
    total = 0
    final_status = "success"
    final_message = "generate_blog_drafts completed"

    if not args.no_db_lock:
        locked, lock_owner = acquire_db_lock(
            lock_name,
            ttl_minutes=args.lock_ttl_minutes,
        )

        if not locked:
            print("[SKIP] generate_blog_drafts already running")
            return

    conn = get_conn()

    try:
        run_id = create_pipeline_run(
            "generate_blog_drafts",
            message="generate_blog_drafts started",
            meta={
                "realtor_id": args.realtor_id,
                "article_no": args.article_no,
                "limit": args.limit,
                "draft_buffer": args.draft_buffer,
                "replenish_count": args.replenish_count,
                "limit_realtors": args.limit_realtors,
                "force": args.force,
            },
        )

        print("=" * 80)
        print("[START] generate_blog_drafts")
        print("TIME:", datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
        print("=" * 80)
        print("FORCE:", "ON" if args.force else "OFF")
        print("REALTOR_ID:", args.realtor_id if args.realtor_id else "ALL")
        print("ARTICLE_NO:", args.article_no if args.article_no else "-")

        articles = fetch_target_articles(
            conn=conn,
            realtor_id=args.realtor_id,
            article_no=args.article_no,
            limit=args.limit,
            force=args.force,
            draft_buffer=args.draft_buffer,
            replenish_count=args.replenish_count,
            limit_realtors=args.limit_realtors,
        )

        total = len(articles)
        print(f"[TARGET ARTICLES] {total}")

        add_pipeline_log(
            run_id=run_id,
            level="info",
            step_name="target_articles",
            message=f"target articles: {total}",
            article_no=args.article_no,
            realtor_id=args.realtor_id,
            context={"target_count": total},
        )

        for row in articles:
            row = normalize_placeholder_article_detail_for_draft(row)
            if args.article_no and args.force:
                row = recover_forced_target_from_existing_draft(conn, row)
                row = normalize_placeholder_article_detail_for_draft(row)
            valid_article, valid_reason = is_valid_collected_article_for_draft(row)

            if not valid_article:
                print(
                    "[DRAFT SKIP INVALID DETAIL]",
                    row.get("article_no"),
                    valid_reason
                )
                continue

            article_no = str(row.get("article_no") or "")
            realtor_id = row.get("realtor_id")

            try:
                process_article(conn, row)
                mark_article_work_completed(
                    conn,
                    realtor_id=realtor_id,
                    article_no=article_no,
                )
                success += 1

                add_pipeline_log(
                    run_id=run_id,
                    level="info",
                    step_name="draft_success",
                    message=f"draft saved: {article_no}",
                    article_no=article_no,
                    realtor_id=realtor_id,
                )

            except Exception as e:
                failed += 1
                try:
                    conn.rollback()
                except Exception:
                    pass

                print(f"[DRAFT ERROR] article_no={article_no} / {str(e)}")
                add_pipeline_log(
                    run_id=run_id,
                    level="error",
                    step_name="draft_error",
                    message=str(e),
                    article_no=article_no,
                    realtor_id=realtor_id,
                    context={"traceback": traceback.format_exc()},
                )

        print("=" * 80)
        print("[DONE] generate_blog_drafts")
        print("=" * 80)

        if failed > 0:
            final_status = "failed"
            final_message = f"completed with failures: success={success}, failed={failed}"

    except Exception as e:
        final_status = "failed"
        final_message = str(e)[:2000]
        print("[FATAL]", final_message)
        traceback.print_exc()
        add_pipeline_log(
            run_id=run_id,
            level="error",
            step_name="fatal",
            message=final_message,
            context={"traceback": traceback.format_exc()},
        )

    finally:
        try:
            conn.close()
        except Exception:
            pass

        finish_pipeline_run(
            run_id,
            final_status,
            total=total,
            success=success,
            failed=failed,
            skipped=skipped,
            message=final_message,
            meta={
                "realtor_id": args.realtor_id,
                "article_no": args.article_no,
                "limit": args.limit,
                "draft_buffer": args.draft_buffer,
                "replenish_count": args.replenish_count,
                "limit_realtors": args.limit_realtors,
                "force": args.force,
            },
        )

        if lock_owner:
            release_db_lock(lock_name, lock_owner)


if __name__ == "__main__":
    main()