# -*- 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 fetch_target_realtors(conn, limit_realtors=10, only_enabled=False):
    sql = """
        SELECT
            r.id,
            r.office_name,
            r.naver_realtor_id,
            COALESCE(s.auto_publish_enabled, 0) AS auto_publish_enabled
        FROM blog_realtors r
        LEFT JOIN blog_publish_settings s
            ON r.id = s.realtor_id
        WHERE r.naver_realtor_id IS NOT NULL
          AND r.naver_realtor_id <> ''
    """

    params = []

    if only_enabled:
        sql += " AND COALESCE(s.auto_publish_enabled, 0) = 1 "

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

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


def has_pending_run(conn, realtor_id):
    columns = table_columns(conn, "blog_realtor_search_runs")

    status_col = None
    for col in ["status", "run_status", "search_status"]:
        if col in columns:
            status_col = col
            break

    if not status_col:
        return False

    with conn.cursor() as cur:
        cur.execute(f"""
            SELECT id
            FROM blog_realtor_search_runs
            WHERE realtor_id = %s
              AND {status_col} = 'pending'
            LIMIT 1
        """, (realtor_id,))
        row = cur.fetchone()

    return bool(row)


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

    if has_pending_run(conn, realtor_id):
        print(f"[RUN EXISTS] realtor_id={realtor_id} pending run already exists")
        return

    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"] = "realtor_id"

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

    if not data:
        raise Exception("blog_realtor_search_runs insert columns not found")

    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}, office={realtor.get('office_name')}")


def run_python_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 count_candidates(conn, realtor_id):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT COUNT(DISTINCT article_no) AS cnt
            FROM blog_realtor_article_candidates
            WHERE realtor_id = %s
        """, (realtor_id,))
        row = cur.fetchone()

    return int(row.get("cnt") or 0)


def count_final_articles(conn, realtor_id):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT COUNT(DISTINCT article_no) AS cnt
            FROM blog_realtor_articles
            WHERE realtor_id = %s
        """, (realtor_id,))
        row = cur.fetchone()

    return int(row.get("cnt") or 0)


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

    print("-" * 80)
    print(f"[REALTOR START] id={realtor_id}, office={office_name}, realtorId={naver_realtor_id}")

    create_search_run(conn, realtor)

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

    candidate_count = count_candidates(conn, realtor_id)
    print(f"[CANDIDATES] {candidate_count}")

    run_python_job(
        os.path.join("jobs", "verify_article_candidates.py"),
        [
            "--realtor-id", str(realtor_id),
            "--limit", str(verify_limit),
        ]
    )

    final_count = count_final_articles(conn, realtor_id)
    print(f"[FINAL ARTICLES] {final_count}")

    print(f"[REALTOR DONE] id={realtor_id}, final={final_count}")


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

    parser.add_argument("--limit-realtors", type=int, default=10)
    parser.add_argument("--verify-limit", type=int, default=300)
    parser.add_argument("--only-enabled", action="store_true")

    args = parser.parse_args()

    conn = get_conn()

    try:
        print("=" * 80)
        print("[START] run_realtor_article_pipeline")
        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,
        )

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

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

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

    finally:
        conn.close()


if __name__ == "__main__":
    main()