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

import os
import sys
import json
import argparse
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
from services.fin_land_service import collect_fin_land_article_detail
from services.image_downloader import download_images


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 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 fetch_pending_works(conn, limit=10, realtor_id=None):
    sql = """
        SELECT *
        FROM blog_article_work_queue
        WHERE work_type = 'new_article'
          AND work_status = 'pending'
    """

    params = []

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

    sql += " ORDER BY priority DESC, id ASC LIMIT %s "
    params.append(int(limit))

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


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


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


def fetch_article_detail(conn, article_no):
    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT *
                FROM blog_realestate_article_details
                WHERE article_no = %s
                LIMIT 1
            """, (str(article_no),))
            return cur.fetchone() or {}
    except Exception:
        return {}


def draft_exists(conn, article_no):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT id
            FROM blog_article_drafts
            WHERE article_no = %s
            LIMIT 1
        """, (str(article_no),))
        row = cur.fetchone()
        return row["id"] if row else None


def detail_exists(conn, article_no):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT id
            FROM blog_realestate_article_details
            WHERE article_no = %s
            LIMIT 1
        """, (str(article_no),))
        row = cur.fetchone()
        return bool(row)


def image_exists(conn, article_no):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT id
            FROM blog_realestate_article_images
            WHERE article_no = %s
            LIMIT 1
        """, (str(article_no),))
        row = cur.fetchone()
        return bool(row)


def delete_existing_images(conn, article_no):
    with conn.cursor() as cur:
        cur.execute("""
            DELETE FROM blog_realestate_article_images
            WHERE article_no = %s
        """, (str(article_no),))


def save_article_detail(conn, realtor_article_id, article_no, detail):
    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO blog_realestate_article_details
            (
                realtor_article_id,
                article_no,
                complex_number,
                complex_name,
                article_title,
                article_desc,
                trade_type,
                real_estate_type,
                price_text,
                building_name,
                address,
                road_address,
                floor_info,
                area_info,
                supply_area,
                exclusive_area,
                room_count,
                bathroom_count,
                direction,
                parking_info,
                move_in_date,
                building_usage,
                realtor_office_name,
                realtor_name,
                realtor_phone,
                collect_status,
                raw_json,
                created_at,
                updated_at
            )
            VALUES
            (
                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s, 'collected', %s, NOW(), NOW()
            )
            ON DUPLICATE KEY UPDATE
                realtor_article_id = VALUES(realtor_article_id),
                complex_number = VALUES(complex_number),
                complex_name = VALUES(complex_name),
                article_title = VALUES(article_title),
                article_desc = VALUES(article_desc),
                trade_type = VALUES(trade_type),
                real_estate_type = VALUES(real_estate_type),
                price_text = VALUES(price_text),
                building_name = VALUES(building_name),
                address = VALUES(address),
                road_address = VALUES(road_address),
                floor_info = VALUES(floor_info),
                area_info = VALUES(area_info),
                supply_area = VALUES(supply_area),
                exclusive_area = VALUES(exclusive_area),
                room_count = VALUES(room_count),
                bathroom_count = VALUES(bathroom_count),
                direction = VALUES(direction),
                parking_info = VALUES(parking_info),
                move_in_date = VALUES(move_in_date),
                building_usage = VALUES(building_usage),
                realtor_office_name = VALUES(realtor_office_name),
                realtor_name = VALUES(realtor_name),
                realtor_phone = VALUES(realtor_phone),
                collect_status = 'collected',
                raw_json = VALUES(raw_json),
                updated_at = NOW()
        """, (
            realtor_article_id,
            str(article_no),
            str(detail.get("complex_number") or ""),
            detail.get("complex_name", ""),
            detail.get("article_name", ""),
            detail.get("article_feature", ""),
            detail.get("trade_type_name") or detail.get("trade_type", ""),
            detail.get("real_estate_type_name") or detail.get("real_estate_type", ""),
            detail.get("price_text", ""),
            detail.get("building_name") or detail.get("complex_name") or detail.get("article_name", ""),
            detail.get("address", ""),
            detail.get("road_address", ""),
            detail.get("floor_info", ""),
            detail.get("area_info", ""),
            str(detail.get("supply_space") or ""),
            str(detail.get("exclusive_space") or ""),
            str(detail.get("room_count") or ""),
            str(detail.get("bathroom_count") or ""),
            detail.get("direction", ""),
            detail.get("parking_info", ""),
            detail.get("move_in_date", ""),
            detail.get("building_use", ""),
            detail.get("realtor_office_name", ""),
            detail.get("realtor_name", ""),
            detail.get("realtor_phone", ""),
            json.dumps(detail, ensure_ascii=False),
        ))


def save_article_images(conn, article_no, downloaded_images):
    if not downloaded_images:
        return 0

    with conn.cursor() as cur:
        for idx, img in enumerate(downloaded_images, start=1):
            image_category = img.get("image_category") or "building"

            cur.execute("""
                INSERT INTO blog_realestate_article_images
                (
                    article_no,
                    image_type,
                    image_category,
                    image_source,
                    image_url,
                    local_path,
                    file_name,
                    sort_order,
                    is_representative,
                    raw_json,
                    created_at,
                    updated_at
                )
                VALUES
                (
                    %s, %s, %s, 'naver_land', %s, %s, %s, %s, %s, %s, NOW(), NOW()
                )
                ON DUPLICATE KEY UPDATE
                    image_type = VALUES(image_type),
                    image_category = VALUES(image_category),
                    image_source = VALUES(image_source),
                    image_url = VALUES(image_url),
                    local_path = VALUES(local_path),
                    file_name = VALUES(file_name),
                    sort_order = VALUES(sort_order),
                    is_representative = VALUES(is_representative),
                    raw_json = VALUES(raw_json),
                    updated_at = NOW()
            """, (
                str(article_no),
                image_category,
                image_category,
                img.get("image_url", ""),
                img.get("local_path", ""),
                img.get("file_name", ""),
                idx,
                1 if idx == 1 else 0,
                json.dumps(img, ensure_ascii=False),
            ))

    return len(downloaded_images)


def update_realtor_article_status(conn, article_id, status, message=""):
    if not article_id:
        return

    with conn.cursor() as cur:
        cur.execute("""
            UPDATE blog_realtor_articles
            SET
                collect_status = %s,
                error_message = %s,
                updated_at = NOW()
            WHERE id = %s
        """, (
            status,
            message or "",
            article_id,
        ))


def collect_detail_for_article(conn, article, force=False):
    article_no = str(article["article_no"])
    article_id = article.get("id")

    already_detail = detail_exists(conn, article_no)
    already_image = image_exists(conn, article_no)

    if already_detail and already_image and not force:
        print(f"[DETAIL SKIP] already collected article_no={article_no}")
        return

    print(f"[DETAIL COLLECT START] article_no={article_no}")

    detail = collect_fin_land_article_detail(article_no)

    if force:
        delete_existing_images(conn, article_no)

    save_article_detail(
        conn=conn,
        realtor_article_id=article_id,
        article_no=article_no,
        detail=detail,
    )

    images = detail.get("images", []) or []

    downloaded_images = download_images(
        article_no=article_no,
        images=images,
    )

    image_count = save_article_images(
        conn=conn,
        article_no=article_no,
        downloaded_images=downloaded_images,
    )

    message = f"상세수집 완료 / 이미지 {image_count}건"

    update_realtor_article_status(
        conn=conn,
        article_id=article_id,
        status="collected",
        message=message,
    )

    print(f"[DETAIL COLLECT DONE] article_no={article_no}, images={image_count}")


def build_draft_title(article, detail, article_no):
    name = safe_get(detail, [
        "article_title",
        "complex_name",
        "building_name",
    ], "")

    if not name:
        name = safe_get(article, [
            "article_name",
            "building_name",
            "complex_name",
            "apt_name",
            "name",
        ], "추천 매물")

    trade_type = safe_get(detail, [
        "trade_type",
    ], "")

    if not trade_type:
        trade_type = safe_get(article, [
            "trade_type",
            "trade_type_name",
            "deal_type",
        ], "")

    real_type = safe_get(detail, [
        "real_estate_type",
    ], "")

    if not real_type:
        real_type = safe_get(article, [
            "real_estate_type",
            "real_estate_type_name",
        ], "")

    price = safe_get(detail, [
        "price_text",
    ], "")

    if not price:
        price = safe_get(article, [
            "price_text",
            "deal_price",
            "price",
            "deal_or_warrant_p",
        ], "")

    parts = []

    if name:
        parts.append(name)

    if real_type:
        parts.append(real_type)

    if trade_type:
        parts.append(trade_type)

    if price:
        parts.append(price)

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

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


def build_plain_text(article, detail, realtor):
    name = safe_get(detail, [
        "article_title",
        "complex_name",
        "building_name",
    ], "")

    if not name:
        name = safe_get(article, [
            "article_name",
            "building_name",
            "complex_name",
            "apt_name",
            "name",
        ], "해당 매물")

    desc = safe_get(detail, [
        "article_desc",
    ], "")

    trade_type = safe_get(detail, ["trade_type"], safe_get(article, ["trade_type"], ""))
    price = safe_get(detail, ["price_text"], safe_get(article, ["price_text"], ""))
    area = safe_get(detail, ["area_info", "exclusive_area", "supply_area"], safe_get(article, ["area_info"], ""))
    floor = safe_get(detail, ["floor_info"], safe_get(article, ["floor_info"], ""))
    direction = safe_get(detail, ["direction"], "")
    parking = safe_get(detail, ["parking_info"], "")
    move_in = safe_get(detail, ["move_in_date"], "")

    office_name = safe_get(realtor, [
        "office_name",
        "name",
    ], "부동산")

    lines = []

    lines.append(f"{name} 매물을 소개드립니다.")
    lines.append("")

    if desc:
        lines.append(desc)
        lines.append("")
    else:
        lines.append("실제 현장에서 확인해보면 공간감과 구조가 안정적으로 느껴지는 매물입니다.")
        lines.append("")

    if trade_type:
        lines.append(f"거래유형: {trade_type}")

    if price:
        lines.append(f"가격: {price}")

    if area:
        lines.append(f"면적: {area}")

    if floor:
        lines.append(f"층수: {floor}")

    if direction:
        lines.append(f"방향: {direction}")

    if parking:
        lines.append(f"주차: {parking}")

    if move_in:
        lines.append(f"입주가능일: {move_in}")

    lines.append("")
    lines.append("주변 생활 인프라와 이동 동선을 함께 고려해보면 실거주 관점에서도 검토해볼 수 있는 매물입니다.")
    lines.append("자세한 상담은 등록 중개사무소를 통해 확인 가능합니다.")
    lines.append("")
    lines.append(f"문의: {office_name}")

    return "\n".join(lines)


def insert_draft(conn, realtor_id, article_no, title, plain_text):
    columns = table_columns(conn, "blog_article_drafts")

    data = {}

    if "realtor_id" in columns:
        data["realtor_id"] = realtor_id

    if "article_no" in columns:
        data["article_no"] = str(article_no)

    if "draft_title" in columns:
        data["draft_title"] = title

    if "plain_text" in columns:
        data["plain_text"] = plain_text

    if "status" in columns:
        data["status"] = "generated"

    if "created_at" in columns:
        data["created_at"] = datetime.now()

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

    keys = list(data.keys())
    placeholders = ", ".join(["%s"] * len(keys))
    key_sql = ", ".join(keys)

    sql = f"""
        INSERT INTO blog_article_drafts
        ({key_sql})
        VALUES ({placeholders})
    """

    with conn.cursor() as cur:
        cur.execute(sql, [data[k] for k in keys])
        return cur.lastrowid

def update_existing_draft(conn, draft_id, title, plain_text):
    with conn.cursor() as cur:
        cur.execute("""
            UPDATE blog_article_drafts
            SET
                draft_title = %s,
                plain_text = %s,
                updated_at = NOW()
            WHERE id = %s
        """, (
            title,
            plain_text,
            int(draft_id),
        ))

def update_work_status(conn, work_id, status, message=None):
    columns = table_columns(conn, "blog_article_work_queue")

    sets = ["work_status = %s"]
    params = [status]

    if "error_message" in columns and message:
        sets.append("error_message = %s")
        params.append(message)

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

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

    params.append(work_id)

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


def publish_queue_exists(conn, draft_id):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT id
            FROM blog_publish_queue
            WHERE draft_id = %s
              AND queue_status IN ('waiting', 'processing', 'published', 'hold')
            LIMIT 1
        """, (draft_id,))
        row = cur.fetchone()
        return bool(row)


def enqueue_publish(conn, realtor_id, draft_id):
    if publish_queue_exists(conn, draft_id):
        print(f"[PUBLISH QUEUE SKIP] already exists draft_id={draft_id}")
        return

    columns = table_columns(conn, "blog_publish_queue")

    data = {}

    if "realtor_id" in columns:
        data["realtor_id"] = realtor_id

    if "draft_id" in columns:
        data["draft_id"] = draft_id

    if "queue_status" in columns:
        data["queue_status"] = "waiting"

    if "created_at" in columns:
        data["created_at"] = datetime.now()

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

    keys = list(data.keys())
    placeholders = ", ".join(["%s"] * len(keys))
    key_sql = ", ".join(keys)

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

    with conn.cursor() as cur:
        cur.execute(sql, [data[k] for k in keys])

    print(f"[PUBLISH QUEUE CREATED] draft_id={draft_id}")


def process_work(conn, work, auto_enqueue_publish=False, force_detail=False):
    work_id = work["id"]
    realtor_id = int(work["realtor_id"])
    article_no = str(work["article_no"])

    print("-" * 80)
    print(f"[WORK] id={work_id}, realtor_id={realtor_id}, article_no={article_no}")

    exists_id = draft_exists(conn, article_no)

    if exists_id and not force_detail:
        print(f"[SKIP] draft already exists: {exists_id}")

        if auto_enqueue_publish:
            enqueue_publish(conn, realtor_id, exists_id)
            conn.commit()

        update_work_status(conn, work_id, "done", f"이미 draft 존재: {exists_id}")
        conn.commit()
        return

    if exists_id and force_detail:
        print(f"[FORCE DETAIL UPDATE] existing draft_id={exists_id}")

    update_work_status(conn, work_id, "processing")
    conn.commit()

    article = fetch_article(conn, realtor_id, article_no)

    if not article:
        update_work_status(conn, work_id, "pending", "blog_realtor_articles에서 매물 없음")
        conn.commit()
        print("[WORK ERROR] blog_realtor_articles에서 매물 없음")
        return

    realtor = fetch_realtor(conn, realtor_id)

    try:
        collect_detail_for_article(
            conn=conn,
            article=article,
            force=force_detail,
        )

        conn.commit()

    except Exception as e:
        conn.rollback()
        update_work_status(conn, work_id, "pending", f"상세수집 실패: {str(e)}")
        conn.commit()
        print("[DETAIL ERROR]", str(e))
        return

    detail = fetch_article_detail(conn, article_no)

    title = build_draft_title(article, detail, article_no)
    plain_text = build_plain_text(article, detail, realtor)

    if exists_id:
        update_existing_draft(
            conn=conn,
            draft_id=exists_id,
            title=title,
            plain_text=plain_text,
        )

        draft_id = exists_id

        print(f"[DRAFT UPDATED] draft_id={draft_id}")

    else:
        draft_id = insert_draft(
            conn=conn,
            realtor_id=realtor_id,
            article_no=article_no,
            title=title,
            plain_text=plain_text,
        )

        print(f"[DRAFT CREATED] draft_id={draft_id}")

    update_work_status(conn, work_id, "done", f"draft 생성 완료: {draft_id}")

    if auto_enqueue_publish:
        enqueue_publish(conn, realtor_id, draft_id)

    conn.commit()

    print(f"[DRAFT CREATED] draft_id={draft_id}")
    print(f"[TITLE] {title}")


def main():
    parser = argparse.ArgumentParser()

    parser.add_argument("--limit", type=int, default=10)
    parser.add_argument("--realtor-id", type=int, default=None)
    parser.add_argument("--enqueue-publish", action="store_true")
    parser.add_argument("--force-detail", action="store_true")

    args = parser.parse_args()

    conn = get_conn()

    try:
        print("=" * 80)
        print("[START] generate_daily_drafts")
        print("TIME:", datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
        print("=" * 80)

        works = fetch_pending_works(
            conn=conn,
            limit=args.limit,
            realtor_id=args.realtor_id,
        )

        print(f"[PENDING WORKS] {len(works)}")

        for work in works:
            try:
                process_work(
                    conn=conn,
                    work=work,
                    auto_enqueue_publish=args.enqueue_publish,
                    force_detail=args.force_detail,
                )
            except Exception as e:
                conn.rollback()

                try:
                    update_work_status(conn, work["id"], "pending", str(e))
                    conn.commit()
                except Exception:
                    pass

                print("[WORK ERROR]", str(e))

        print("=" * 80)
        print("[DONE] generate_daily_drafts")
        print("=" * 80)

    finally:
        conn.close()


if __name__ == "__main__":
    main()