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

import os
import sys
import argparse
import subprocess
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


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 run_job(script_path, args):
    cmd = [sys.executable, script_path] + args
    print("[COMMAND]", " ".join(cmd))

    result = subprocess.run(cmd, cwd=BASE_DIR, text=True)

    if result.returncode != 0:
        raise Exception(f"job failed: {script_path}")


def fetch_target_realtors(conn, limit_realtors=10, only_enabled=False, realtor_id=None):
    sql = """
        SELECT
            r.id,
            r.office_name,
            r.naver_realtor_id,
            COALESCE(s.auto_publish_enabled, 0) AS auto_publish_enabled,
            COALESCE(a.login_status, '') AS login_status
        FROM blog_realtors r
        LEFT JOIN blog_publish_settings s
            ON r.id = s.realtor_id
        LEFT JOIN blog_naver_accounts a
            ON r.id = a.realtor_id
        WHERE r.naver_realtor_id IS NOT NULL
          AND r.naver_realtor_id <> ''
          AND COALESCE(r.status, 'active') = 'active'
    """

    params = []

    if only_enabled:
        # 수집 대상은 자동발행/로그인 상태와 분리한다.
        # 단체아이디가 있고 자동수집 ON이면 상세·초안까지 진행한다.
        sql += " AND COALESCE(r.is_collect_enabled, 0) = 1 "

    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))

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


def create_search_run(conn, realtor):
    realtor_id = realtor["id"]
    columns = table_columns(conn, "blog_realtor_search_runs")

    data = {}

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

    for col in ["status", "run_status", "search_status"]:
        if col in columns:
            data[col] = "pending"
            break

    if "source_type" in columns:
        data["source_type"] = "detect_new_articles"

    if "keyword" in columns:
        data["keyword"] = realtor.get("naver_realtor_id", "")

    if "created_at" in columns:
        data["created_at"] = "NOW()"

    if "updated_at" in columns:
        data["updated_at"] = "NOW()"

    keys = list(data.keys())
    values = []
    params = []

    for key in keys:
        if data[key] == "NOW()":
            values.append("NOW()")
        else:
            values.append("%s")
            params.append(data[key])

    sql = f"""
        INSERT INTO blog_realtor_search_runs
        ({",".join(keys)})
        VALUES
        ({",".join(values)})
    """

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

    conn.commit()

    print(f"[RUN CREATED] realtor_id={realtor_id}")


def clear_candidates(conn, realtor_id):
    with conn.cursor() as cur:
        cur.execute("""
            DELETE FROM blog_realtor_article_candidates
            WHERE realtor_id = %s
        """, (realtor_id,))

    conn.commit()


def fetch_current_candidates(conn, realtor_id):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT DISTINCT article_no
            FROM blog_realtor_article_candidates
            WHERE realtor_id = %s
        """, (realtor_id,))
        rows = cur.fetchall()

    return set(str(row["article_no"]).strip() for row in rows if row.get("article_no"))


def article_exists(conn, realtor_id, article_no):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT id
            FROM blog_realtor_articles
            WHERE realtor_id = %s
              AND article_no = %s
            LIMIT 1
        """, (realtor_id, str(article_no)))
        row = cur.fetchone()

    return bool(row)


def insert_new_article(conn, realtor_id, article_no):
    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO blog_realtor_articles
            (
                realtor_id,
                article_no,
                collect_status,
                article_status,
                visibility_status,
                last_seen_at,
                created_at,
                updated_at
            )
            VALUES
            (
                %s,
                %s,
                'pending',
                'active',
                'public',
                NOW(),
                NOW(),
                NOW()
            )
            ON DUPLICATE KEY UPDATE
                article_status = 'active',
                visibility_status = 'public',
                last_seen_at = NOW(),
                updated_at = NOW()
        """, (realtor_id, str(article_no)))

    conn.commit()


def enqueue_article_work(conn, realtor_id, article_no):
    with conn.cursor() as cur:
        cur.execute("""
            INSERT IGNORE INTO blog_article_work_queue
            (
                realtor_id,
                article_no,
                work_type,
                work_status,
                priority,
                created_at,
                updated_at
            )
            VALUES
            (
                %s,
                %s,
                'new_article',
                'pending',
                100,
                NOW(),
                NOW()
            )
        """, (realtor_id, str(article_no)))

    conn.commit()


def mark_seen_article(conn, realtor_id, article_no):
    with conn.cursor() as cur:
        cur.execute("""
            UPDATE blog_realtor_articles
            SET
                article_status = 'active',
                last_seen_at = NOW(),
                updated_at = NOW()
            WHERE realtor_id = %s
              AND article_no = %s
        """, (realtor_id, str(article_no)))

    conn.commit()


def collect_current_articles(conn, realtor):
    realtor_id = int(realtor["id"])

    clear_candidates(conn, realtor_id)
    create_search_run(conn, realtor)

    run_job(
        os.path.join("jobs", "process_realtor_search_runs.py"),
        ["--realtor-id", str(realtor_id)]
    )

    return fetch_current_candidates(conn, realtor_id)


def process_realtor(conn, realtor):
    realtor_id = int(realtor["id"])
    office_name = realtor.get("office_name", "")
    naver_realtor_id = realtor.get("naver_realtor_id", "")

    print("-" * 80)
    print(f"[REALTOR] {office_name}")
    print(f"[REALTOR ID] {realtor_id}")
    print(f"[NAVER REALTOR ID] {naver_realtor_id}")

    current_article_nos = collect_current_articles(conn, realtor)

    print(f"[CURRENT ARTICLES] {len(current_article_nos)}")

    if not current_article_nos:
        print("[SKIP] current articleNo not found")
        return

    new_count = 0
    seen_count = 0

    for article_no in sorted(current_article_nos):
        if article_exists(conn, realtor_id, article_no):
            mark_seen_article(conn, realtor_id, article_no)
            seen_count += 1
            continue

        print(f"[NEW] {article_no}")

        insert_new_article(conn, realtor_id, article_no)

        new_count += 1

    print(f"[SUMMARY] new={new_count}, seen={seen_count}")


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

    parser.add_argument("--limit-realtors", type=int, default=10)
    parser.add_argument("--realtor-id", type=int, default=None)
    parser.add_argument("--only-enabled", action="store_true")

    args = parser.parse_args()

    conn = get_conn()

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

        realtors = fetch_target_realtors(
            conn,
            limit_realtors=args.limit_realtors,
            only_enabled=args.only_enabled,
            realtor_id=args.realtor_id,
        )

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

        for realtor in realtors:
            try:
                process_realtor(conn, realtor)
            except Exception as e:
                conn.rollback()
                print(f"[ERROR] realtor_id={realtor.get('id')} / {str(e)}")

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

    finally:
        conn.close()


if __name__ == "__main__":
    main()