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

import random
import re
from db import get_conn


GREEN_COLORS = [
    "#2E7D32",
    "#388E3C",
    "#43A047",
    "#4CAF50",
    "#66BB6A",
    "#1B5E20",
]


def safe_get(row, keys, default=""):
    for key in keys:
        if row and key in row and row.get(key):
            return str(row.get(key)).strip()
    return default


def safe_int(value, default=0):
    try:
        if value is None:
            return default
        return int(value)
    except Exception:
        return default


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


def get_realtor_info(realtor_id):
    if not realtor_id:
        return {}

    conn = get_conn()

    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT *
                FROM blog_realtors
                WHERE id = %s
                LIMIT 1
            """, (realtor_id,))
            return cur.fetchone() or {}
    finally:
        conn.close()


def get_article_info(realtor_id, article_no):
    conn = get_conn()

    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT *
                FROM blog_realtor_articles
                WHERE realtor_id = %s
                  AND article_no = %s
                LIMIT 1
            """, (
                realtor_id,
                str(article_no)
            ))
            return cur.fetchone() or {}
    finally:
        conn.close()


def get_draft_images(draft_id, article_no):
    conn = get_conn()

    try:
        with conn.cursor() as cur:
            try:
                cur.execute("""
                    SELECT *
                    FROM blog_article_images
                    WHERE draft_id = %s
                      AND image_url IS NOT NULL
                      AND image_url != ''
                    ORDER BY sort_order ASC, id ASC
                """, (draft_id,))
                rows = cur.fetchall()

                if rows:
                    print(f"[IMAGE SOURCE] blog_article_images count={len(rows)}")
                    return rows

            except Exception as e:
                print("[BLOG_ARTICLE_IMAGES SKIP]", str(e))

            try:
                cur.execute("""
                    SELECT *
                    FROM blog_realestate_article_images
                    WHERE article_no = %s
                      AND image_url IS NOT NULL
                      AND image_url != ''
                    ORDER BY
                        CASE image_type
                            WHEN 'inside' THEN 1
                            WHEN 'main' THEN 2
                            WHEN 'outside' THEN 3
                            WHEN 'view' THEN 4
                            WHEN 'building' THEN 5
                            WHEN 'floorplan' THEN 8
                            WHEN 'map' THEN 9
                            ELSE 7
                        END ASC,
                        CASE WHEN width IS NULL THEN 1 ELSE 0 END ASC,
                        width DESC,
                        sort_order ASC,
                        id ASC
                """, (article_no,))

                rows = cur.fetchall()

                print(f"[IMAGE SOURCE] blog_realestate_article_images count={len(rows)}")

                return rows

            except Exception as e:
                print("[BLOG_REALESTATE_IMAGES SKIP]", str(e))
                return []

    finally:
        conn.close()


def is_bad_image(img):
    image_url = safe_get(img, ["image_url"], "").lower()
    file_name = safe_get(img, ["file_name"], "").lower()
    image_type = safe_get(img, ["image_type"], "").lower()
    image_category = safe_get(img, ["image_category"], "").lower()
    raw_json = safe_get(img, ["raw_json"], "").lower()

    width = safe_int(img.get("width"), 0) if img else 0
    height = safe_int(img.get("height"), 0) if img else 0

    merged = " ".join([
        image_url,
        file_name,
        image_type,
        image_category,
        raw_json,
    ])

    bad_keywords = [
        "중개",
        "중개사",
        "공인중개",
        "대표자",
        "대표 사진",
        "프로필",
        "profile",
        "agent",
        "broker",
        "명함",
        "로고",
        "logo",
        "업체",
        "광고",
        "map",
        "지도",
    ]

    for keyword in bad_keywords:
        if keyword.lower() in merged:
            return True

    if image_type in ["map"]:
        return True

    if width and height:
        if width < 300 or height < 220:
            return True

    return False


def image_score(img):
    image_type = safe_get(img, ["image_type"], "").lower()
    image_category = safe_get(img, ["image_category"], "").lower()
    sort_order = safe_int(img.get("sort_order"), 999)
    width = safe_int(img.get("width"), 0)
    height = safe_int(img.get("height"), 0)
    is_representative = safe_int(img.get("is_representative"), 0)

    score = 0

    if image_type == "inside":
        score += 100
    elif image_type == "main":
        score += 85
    elif image_type == "outside":
        score += 70
    elif image_type == "view":
        score += 65
    elif image_type == "building":
        score += 45
    elif image_type == "floorplan":
        score += 20
    elif image_type == "map":
        score -= 100
    else:
        score += 35

    if "inside" in image_category or "실내" in image_category:
        score += 30

    if "living" in image_category or "거실" in image_category:
        score += 35

    if "room" in image_category or "방" in image_category:
        score += 25

    if "kitchen" in image_category or "주방" in image_category:
        score += 20

    if "bath" in image_category or "욕실" in image_category:
        score += 10

    if "building" in image_category or "전경" in image_category:
        score += 5

    if "floor" in image_category or "평면" in image_category:
        score -= 20

    if width and height:
        score += min(width, 1600) / 40
        score += min(height, 1200) / 60

    if is_representative:
        score += 5

    score -= sort_order * 2

    return score


def select_best_images(images, max_count=7):
    if not images:
        return []

    valid = []

    seen_urls = set()

    for img in images:
        image_url = safe_get(img, ["image_url"], "")

        if not image_url:
            continue

        if image_url in seen_urls:
            continue

        seen_urls.add(image_url)

        if is_bad_image(img):
            continue

        valid.append(img)

    if not valid:
        valid = [
            img for img in images
            if safe_get(img, ["image_url"], "")
        ]

    valid.sort(
        key=lambda x: image_score(x),
        reverse=True
    )

    selected = valid[:max_count]

    print("[IMAGE SELECTED COUNT]", len(selected))

    for idx, img in enumerate(selected):
        print(
            f"[IMAGE SELECTED] {idx + 1} "
            f"type={safe_get(img, ['image_type'], '')} "
            f"category={safe_get(img, ['image_category'], '')} "
            f"w={img.get('width')} h={img.get('height')} "
            f"score={image_score(img):.1f}"
        )

    return selected


def generate_image_html(images):
    selected = select_best_images(images, max_count=7)

    if not selected:
        return ""

    first_image = selected[0]
    first_url = safe_get(first_image, ["image_url"], "")

    html = """
<div style="font-size:28px; font-weight:800; margin:0 0 20px; color:#2E7D32;">
    매물 사진
</div>
"""

    html += f"""
<div style="margin-bottom:24px;">
    <img src="{first_url}" style="width:100%; border-radius:22px; display:block; object-fit:cover;">
</div>
"""

    remain = selected[1:7]

    if remain:
        html += """
<div style="display:grid; grid-template-columns:1fr 1fr; gap:12px; margin-bottom:24px;">
"""

        for img in remain:
            image_url = safe_get(img, ["image_url"], "")

            html += f"""
<div>
    <img src="{image_url}" style="width:100%; border-radius:16px; display:block; aspect-ratio:1.2; object-fit:cover;">
</div>
"""

        html += "</div>"

    return html



def clean_keyword(value):
    value = str(value or "").strip()
    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 split_seo_keywords(value):
    text = str(value or "").strip()
    if not text:
        return []

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

    for part in parts:
        keyword = clean_keyword(part)
        if keyword:
            results.append(keyword)

    return results


def add_unique_keyword(items, value):
    keyword = clean_keyword(value)
    if not keyword:
        return

    if len(keyword) < 2:
        return

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


def build_location_keywords(article):
    candidates = []

    raw_fields = [
        "city", "city_name", "cityName",
        "province", "province_name",
        "sigungu", "sigungu_name", "gu", "gu_name",
        "dong", "dong_name", "emd", "emd_name",
        "address", "jibun_address", "road_address",
        "article_name", "building_name",
    ]

    merged = " ".join([
        safe_get(article, [field], "")
        for field in raw_fields
    ])

    # 한국 행정동/구 단위 키워드 추출
    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):
            add_unique_keyword(candidates, match)

    return candidates


def build_seo_keywords(article, realtor):
    keywords = []

    complex_name = safe_get(article, [
        "complex_name",
        "article_name",
        "building_name",
        "apt_name",
        "complexName",
        "articleName",
        "name",
    ], "")

    real_estate_type = safe_get(article, [
        "real_estate_type_name",
        "real_estate_type",
        "article_type",
        "article_type_name",
        "realEstateTypeName",
    ], "")

    trade_type = safe_get(article, [
        "trade_type_name",
        "trade_type",
        "deal_type",
        "tradeTypeName",
    ], "")

    office_name = safe_get(realtor, ["office_name"], "")
    seo_keywords = safe_get(realtor, ["seo_keywords"], "")

    location_keywords = build_location_keywords(article)

    add_unique_keyword(keywords, complex_name)

    if complex_name and trade_type:
        add_unique_keyword(keywords, f"{complex_name}{trade_type}")

    if complex_name and real_estate_type:
        add_unique_keyword(keywords, f"{complex_name}{real_estate_type}")

    for loc in location_keywords:
        add_unique_keyword(keywords, loc)

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

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

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

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

    if real_estate_type:
        add_unique_keyword(keywords, real_estate_type)

    if trade_type:
        add_unique_keyword(keywords, trade_type)

    # 상품/매물 유형별 일반 검색 키워드
    type_text = f"{real_estate_type} {complex_name}"

    if "아파트" in type_text or "APT" in type_text.upper():
        for k in ["아파트매매", "아파트전세", "아파트월세", "아파트추천"]:
            add_unique_keyword(keywords, k)

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

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

    if "토지" in type_text or "대지" in type_text or "임야" in type_text:
        for k in ["토지매매", "대지매매", "토지추천"]:
            add_unique_keyword(keywords, k)

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

    for keyword in split_seo_keywords(seo_keywords):
        add_unique_keyword(keywords, keyword)

    # 너무 많은 해시태그는 산만하므로 제한
    return keywords[:30]


def build_seo_paragraph(article, realtor):
    complex_name = safe_get(article, [
        "complex_name",
        "article_name",
        "building_name",
        "apt_name",
        "complexName",
        "articleName",
        "name",
    ], "")

    real_estate_type = safe_get(article, [
        "real_estate_type_name",
        "real_estate_type",
        "article_type",
        "article_type_name",
        "realEstateTypeName",
    ], "")

    trade_type = safe_get(article, [
        "trade_type_name",
        "trade_type",
        "deal_type",
        "tradeTypeName",
    ], "")

    office_name = safe_get(realtor, ["office_name"], "중개사무소")
    locations = build_location_keywords(article)

    subject_parts = []
    if complex_name:
        subject_parts.append(complex_name)
    if trade_type:
        subject_parts.append(trade_type)
    if real_estate_type:
        subject_parts.append(real_estate_type)

    subject = " ".join(subject_parts).strip() or "해당 매물"

    location_text = ""
    if locations:
        location_text = " ".join(locations[:3])

    lines = [
        f"{subject} 정보를 찾고 계신 분들께 도움이 되는 매물 안내입니다.",
    ]

    if location_text and real_estate_type:
        lines.append(f"{location_text} {real_estate_type} 관련 정보와 부동산 매물을 지속적으로 소개해 드리겠습니다.")
    elif location_text:
        lines.append(f"{location_text} 부동산 정보를 지속적으로 소개해 드리겠습니다.")

    lines.append(f"자세한 상담은 {office_name}로 문의해 주세요.")

    return "\n".join(lines)


def generate_seo_html(article, realtor, color):
    keywords = build_seo_keywords(article, realtor)
    seo_paragraph = build_seo_paragraph(article, realtor)

    hashtag_html = ""

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

        hashtag_html = f"""
    <div style="margin-top:26px; padding:22px; border-radius:16px; background:#F8FAF8; border:1px solid #E0E0E0;">
        <div style="font-size:20px; font-weight:800; margin-bottom:12px; color:{color};">
            관련 키워드
        </div>
        <div style="font-size:15px; line-height:2.0; color:#333; word-break:keep-all;">
            {hashtag_items}
        </div>
    </div>
"""

    html = f"""
    <div style="margin-top:30px; padding:24px; border-radius:18px; background:#FFFFFF; border:1px solid #E5E5E5;">
        <div style="font-size:22px; font-weight:800; margin-bottom:14px; color:{color};">
            매물 검색 안내
        </div>
        <div style="font-size:15px; line-height:1.9; color:#333; white-space:pre-wrap;">
            {seo_paragraph}
        </div>
    </div>

    {hashtag_html}
"""

    return html


def build_title(draft_row, article):
    current_title = safe_get(draft_row, ["draft_title"], "")

    if current_title and "네이버 부동산 추천 매물" not in current_title:
        return current_title[:100]

    complex_name = safe_get(article, [
        "complex_name",
        "article_name",
        "building_name",
        "apt_name",
        "complexName",
        "articleName",
        "name",
    ], "")

    real_estate_type = safe_get(article, [
        "real_estate_type_name",
        "real_estate_type",
        "article_type",
        "article_type_name",
        "realEstateTypeName",
    ], "")

    trade_type = safe_get(article, [
        "trade_type_name",
        "trade_type",
        "deal_type",
        "tradeTypeName",
    ], "")

    price = safe_get(article, [
        "price_text",
        "deal_price",
        "price",
        "dealOrWarrantPrc",
    ], "")

    parts = []

    if complex_name:
        parts.append(complex_name)

    if real_estate_type:
        parts.append(real_estate_type)

    if trade_type:
        parts.append(trade_type)

    if price:
        parts.append(price)

    if parts:
        return " · ".join(parts)[:100]

    article_no = safe_get(draft_row, ["article_no"], "")

    return f"네이버 부동산 추천 매물 {article_no}"


def generate_clipboard_html(draft_row):
    draft_id = draft_row["id"]
    realtor_id = draft_row.get("realtor_id")
    article_no = safe_get(draft_row, ["article_no"], "")

    realtor = get_realtor_info(realtor_id)
    article = get_article_info(realtor_id, article_no)
    images = get_draft_images(draft_id, article_no)

    title = build_title(draft_row, article)

    plain_text = safe_get(
        draft_row,
        ["plain_text"],
        "매물 상세 내용은 상담을 통해 안내드리겠습니다."
    )

    office_name = safe_get(realtor, ["office_name"], "부동산")
    realtor_name = safe_get(realtor, ["representative_name", "owner_name", "name"], office_name)
    realtor_tel = safe_get(realtor, ["office_phone", "phone", "mobile_phone", "mobile"], "문의")
    realtor_addr = safe_get(realtor, ["office_address", "address"], "")
    realtor_license = safe_get(realtor, ["license_number", "business_number"], "")

    color = random.choice(GREEN_COLORS)
    image_html = generate_image_html(images)

    html = f"""
<div style="max-width:760px; margin:0 auto; font-family:Arial,'Malgun Gothic',sans-serif; color:#222; line-height:1.7;">

    {image_html}

    <div style="background:{color}; color:#fff; padding:28px 24px; border-radius:18px; margin-bottom:24px;">
        <div style="font-size:14px; opacity:0.9; margin-bottom:10px;">네이버 부동산 추천 매물</div>
        <div style="font-size:30px; font-weight:800; line-height:1.4;">{title}</div>
    </div>

    <div style="padding:22px; border:1px solid #E0E0E0; border-radius:14px; margin-bottom:28px; background:#FAFAFA;">
        안녕하세요.<br>
        {office_name} 입니다.<br><br>
        오늘 소개해드리는 추천 매물입니다.<br>
        자세한 내용은 아래를 참고해주세요.
    </div>

    <div style="font-size:24px; font-weight:700; margin-bottom:14px; color:{color};">
        매물 기본 정보
    </div>

    <table style="width:100%; border-collapse:collapse; margin-bottom:30px; font-size:15px;">
        <tr>
            <td style="width:30%; padding:14px; background:{color}; color:#fff; border:1px solid #DDD; font-weight:700;">매물번호</td>
            <td style="padding:14px; border:1px solid #DDD; background:#FFF;">{article_no}</td>
        </tr>
        <tr>
            <td style="padding:14px; background:{color}; color:#fff; border:1px solid #DDD; font-weight:700;">중개사무소</td>
            <td style="padding:14px; border:1px solid #DDD; background:#FFF;">{office_name}</td>
        </tr>
    </table>

    <div style="font-size:24px; font-weight:700; margin-bottom:14px; color:{color};">
        상세 설명
    </div>

    <div style="padding:24px; border-radius:16px; border:1px solid #E5E5E5; background:#FFFFFF; margin-bottom:30px; white-space:pre-wrap;">
        {plain_text}
    </div>

    <div style="background:{color}; color:#fff; border-radius:18px; padding:28px; text-align:center; margin-top:30px; margin-bottom:30px;">
        <div style="font-size:26px; font-weight:800; margin-bottom:12px;">실매물 상담 가능</div>
        <div style="font-size:16px; line-height:1.7;">
            해당 매물에 대한 자세한 상담은<br>
            아래 연락처로 문의 주세요.
        </div>
    </div>

    <div style="border:2px solid {color}; border-radius:18px; padding:24px; background:#fff;">
        <div style="font-size:22px; font-weight:800; margin-bottom:18px; color:{color};">
            중개사 정보
        </div>

        <table style="width:100%; border-collapse:collapse; font-size:15px;">
            <tr>
                <td style="width:30%; padding:12px; border:1px solid #DDD; background:#F7F7F7; font-weight:700;">상호명</td>
                <td style="padding:12px; border:1px solid #DDD;">{office_name}</td>
            </tr>
            <tr>
                <td style="padding:12px; border:1px solid #DDD; background:#F7F7F7; font-weight:700;">대표자</td>
                <td style="padding:12px; border:1px solid #DDD;">{realtor_name}</td>
            </tr>
            <tr>
                <td style="padding:12px; border:1px solid #DDD; background:#F7F7F7; font-weight:700;">연락처</td>
                <td style="padding:12px; border:1px solid #DDD;">{realtor_tel}</td>
            </tr>
            <tr>
                <td style="padding:12px; border:1px solid #DDD; background:#F7F7F7; font-weight:700;">주소</td>
                <td style="padding:12px; border:1px solid #DDD;">{realtor_addr}</td>
            </tr>
            <tr>
                <td style="padding:12px; border:1px solid #DDD; background:#F7F7F7; font-weight:700;">등록번호</td>
                <td style="padding:12px; border:1px solid #DDD;">{realtor_license}</td>
            </tr>
        </table>
    </div>

    {generate_seo_html(article, realtor, color)}

</div>
"""

    return title, html.strip()


def get_target_drafts(limit=20):
    conn = get_conn()

    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT *
                FROM blog_article_drafts
                WHERE status IN ('generated', 'queued_publish', 'draft', '초안')
                ORDER BY id DESC
                LIMIT %s
            """, (int(limit),))

            return cur.fetchall()

    finally:
        conn.close()


def update_draft_content(draft_id, title, html):
    conn = get_conn()

    try:
        columns = table_columns(conn, "blog_article_drafts")

        sets = []
        params = []

        if "draft_title" in columns:
            sets.append("draft_title = %s")
            params.append(title)

        if "clipboard_html" in columns:
            sets.append("clipboard_html = %s")
            params.append(html)

        if "draft_html" in columns:
            sets.append("draft_html = %s")
            params.append(html)

        if "updated_at" in columns:
            sets.append("updated_at = NOW()")

        if not sets:
            print("[UPDATE SKIP] 업데이트할 컬럼 없음")
            return

        sql = f"""
            UPDATE blog_article_drafts
            SET {", ".join(sets)}
            WHERE id = %s
        """

        params.append(draft_id)

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

        conn.commit()

    finally:
        conn.close()


def run_builder():
    drafts = get_target_drafts(limit=20)

    print("=" * 80)
    print(f"[DRAFT COUNT] {len(drafts)}")
    print("=" * 80)

    for draft in drafts:
        draft_id = draft["id"]
        article_no = safe_get(draft, ["article_no"], "")

        print("-" * 80)
        print(f"[BUILD START] draft_id={draft_id}, article_no={article_no}")

        try:
            title, html = generate_clipboard_html(draft)

            update_draft_content(
                draft_id=draft_id,
                title=title,
                html=html
            )

            print(f"[BUILD SUCCESS] title={title}")
            print("[BUILD SUCCESS] clipboard_html / draft_html 저장 완료")

        except Exception as e:
            print("[BUILD ERROR]", str(e))


if __name__ == "__main__":
    run_builder()