# -*- coding: utf-8 -*-
"""
STEP116 repair_realtor.py

운영 점검/복구 도구.

주요 기능:
1) 단일 중개사 점검
    python tools\repair_realtor.py --realtor-id 7

2) 단일 중개사 자동 복구
    python tools\repair_realtor.py --realtor-id 7 --fix

3) 단일 중개사 발행 테스트
    python tools\repair_realtor.py --realtor-id 7 --fix --test-publish

4) 전체 중개사 일괄 점검
    python tools\repair_realtor.py --all

5) 전체 중개사 일괄 점검 + CSV 리포트
    python tools\repair_realtor.py --all --report-csv storage\reports\repair_report.csv

주의:
- --test-publish 없이는 실제 발행하지 않는다.
- --fix는 hold/failed queue를 pending으로 돌리고, 지도 누락 시 생성 명령을 실행한다.
- --all --fix는 대량 변경이므로 운영 중에는 신중하게 사용한다.
"""

import os
import sys
import csv
import argparse
import subprocess
from datetime import datetime

CURRENT_DIR = os.path.dirname(os.path.abspath(__file__))
ROOT_DIR = os.path.dirname(CURRENT_DIR)

if ROOT_DIR not in sys.path:
    sys.path.append(ROOT_DIR)

from db import get_conn
from config import STORAGE_DIR


SESSION_DIR = os.path.join(STORAGE_DIR, "naver_sessions")
REPORT_DIR = os.path.join(STORAGE_DIR, "reports")


def print_line():
    print("=" * 80)


def safe_str(v):
    return "" if v is None else str(v)


def get_one(sql, params=None):
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute(sql, params or [])
            return cur.fetchone()
    finally:
        conn.close()


def get_all(sql, params=None):
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute(sql, params or [])
            return cur.fetchall() or []
    finally:
        conn.close()


def exec_sql(sql, params=None):
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute(sql, params or [])
            affected = cur.rowcount
        conn.commit()
        return affected
    finally:
        conn.close()


def run_cmd(cmd, cwd=None):
    print("[CMD]", " ".join(cmd))
    try:
        p = subprocess.run(
            cmd,
            cwd=cwd or ROOT_DIR,
            text=True,
            encoding="utf-8",
            errors="replace",
            capture_output=True,
        )
        if p.stdout:
            print(p.stdout.strip())
        if p.stderr:
            print("[STDERR]", p.stderr.strip())
        return p.returncode == 0, p.returncode
    except Exception as e:
        print("[CMD ERROR]", e)
        return False, -1


def get_realtor(realtor_id):
    return get_one("""
        SELECT
            r.id,
            r.office_name,
            r.status,
            r.address,
            r.office_address,
            r.map_image_url,
            r.map_generated_at,
            ps.auto_publish_enabled,
            ps.session_status,
            ps.session_last_checked_at
        FROM blog_realtors r
        LEFT JOIN blog_publish_settings ps
            ON ps.realtor_id = r.id
        WHERE r.id = %s
        LIMIT 1
    """, [realtor_id])


def get_naver_account(realtor_id):
    return get_one("""
        SELECT
            realtor_id,
            naver_id,
            blog_id,
            consent_status,
            login_status,
            last_login_at,
            last_login_error,
            naver_password_plain
        FROM blog_naver_accounts
        WHERE realtor_id = %s
        LIMIT 1
    """, [realtor_id])


def get_latest_draft(realtor_id):
    return get_one("""
        SELECT
            id,
            article_no,
            status,
            draft_status,
            blog_url,
            blog_id,
            updated_at,
            CHAR_LENGTH(clipboard_html) AS clipboard_len,
            CHAR_LENGTH(draft_html) AS draft_len
        FROM blog_article_drafts
        WHERE realtor_id = %s
        ORDER BY id DESC
        LIMIT 1
    """, [realtor_id])


def get_queue_summary(realtor_id):
    rows = get_all("""
        SELECT
            queue_status,
            worker_status,
            last_error_type,
            COUNT(*) AS cnt
        FROM blog_publish_queue
        WHERE realtor_id = %s
        GROUP BY queue_status, worker_status, last_error_type
        ORDER BY queue_status
    """, [realtor_id])
    return rows


def get_recent_queues(realtor_id, limit=5):
    return get_all("""
        SELECT
            id,
            draft_id,
            article_no,
            queue_status,
            worker_status,
            last_error_type,
            retry_count,
            error_message,
            publish_result_url,
            updated_at
        FROM blog_publish_queue
        WHERE realtor_id = %s
        ORDER BY id DESC
        LIMIT %s
    """, [realtor_id, int(limit)])


def session_file_path(realtor_id):
    return os.path.join(SESSION_DIR, f"naver_session_{realtor_id}.json")


def queue_counts(rows):
    result = {
        "pending": 0,
        "published": 0,
        "hold": 0,
        "failed": 0,
        "processing": 0,
    }
    last_error_types = []

    for r in rows or []:
        status = safe_str(r.get("queue_status"))
        cnt = int(r.get("cnt") or 0)
        if status in result:
            result[status] += cnt
        err = safe_str(r.get("last_error_type"))
        if err:
            last_error_types.append(f"{err}:{cnt}")

    result["errors"] = ",".join(last_error_types)
    return result


def diagnose(realtor_id):
    realtor = get_realtor(realtor_id)
    account = get_naver_account(realtor_id)
    draft = get_latest_draft(realtor_id)
    q_rows = get_queue_summary(realtor_id)
    q_count = queue_counts(q_rows)

    session_file = session_file_path(realtor_id)
    has_session = os.path.exists(session_file)

    status = "OK"
    issues = []

    if not realtor:
        status = "FAIL"
        issues.append("realtor_missing")
    else:
        if safe_str(realtor.get("status")) != "active":
            status = "WARN"
            issues.append("realtor_not_active")
        if int(realtor.get("auto_publish_enabled") or 0) != 1:
            status = "WARN"
            issues.append("auto_publish_off")
        if not safe_str(realtor.get("map_image_url")).strip():
            status = "WARN"
            issues.append("map_missing")

    if not account:
        status = "FAIL"
        issues.append("naver_account_missing")
    else:
        if safe_str(account.get("consent_status")) != "agreed":
            status = "FAIL"
            issues.append("consent_not_agreed")
        if not safe_str(account.get("naver_id")).strip():
            status = "FAIL"
            issues.append("naver_id_missing")
        if not safe_str(account.get("blog_id")).strip():
            status = "WARN"
            issues.append("blog_id_missing")
        if safe_str(account.get("login_status")) in ["password_error", "naver_block", "disconnected"]:
            status = "FAIL"
            issues.append("login_status_" + safe_str(account.get("login_status")))
        if account.get("last_login_error"):
            issues.append("last_login_error")

    if not has_session:
        status = "WARN" if status == "OK" else status
        issues.append("session_file_missing")

    if not draft:
        status = "WARN" if status == "OK" else status
        issues.append("draft_missing")

    if q_count.get("hold", 0) > 0:
        status = "WARN" if status == "OK" else status
        issues.append("queue_hold")

    if q_count.get("failed", 0) > 0:
        status = "WARN" if status == "OK" else status
        issues.append("queue_failed")

    return {
        "realtor_id": realtor_id,
        "office_name": safe_str(realtor.get("office_name")) if realtor else "",
        "status": status,
        "issues": ",".join(issues),
        "realtor_status": safe_str(realtor.get("status")) if realtor else "",
        "auto_publish_enabled": realtor.get("auto_publish_enabled") if realtor else "",
        "naver_id": safe_str(account.get("naver_id")) if account else "",
        "blog_id": safe_str(account.get("blog_id")) if account else "",
        "consent_status": safe_str(account.get("consent_status")) if account else "",
        "login_status": safe_str(account.get("login_status")) if account else "",
        "has_session_file": "Y" if has_session else "N",
        "has_map": "Y" if realtor and safe_str(realtor.get("map_image_url")).strip() else "N",
        "latest_draft_id": draft.get("id") if draft else "",
        "latest_article_no": draft.get("article_no") if draft else "",
        "queue_pending": q_count.get("pending", 0),
        "queue_published": q_count.get("published", 0),
        "queue_hold": q_count.get("hold", 0),
        "queue_failed": q_count.get("failed", 0),
        "queue_processing": q_count.get("processing", 0),
        "last_error_types": q_count.get("errors", ""),
    }


def print_single_detail(realtor_id, show_password=False):
    print_line()
    print("STEP116 REPAIR REALTOR")
    print("realtor_id:", realtor_id)
    print("time:", datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    print_line()

    realtor = get_realtor(realtor_id)
    if not realtor:
        print("[FAIL] realtor not found:", realtor_id)
        return None

    print("[REALTOR]")
    print(" id:", realtor.get("id"))
    print(" office:", realtor.get("office_name"))
    print(" status:", realtor.get("status"))
    print(" auto_publish_enabled:", realtor.get("auto_publish_enabled"))
    print(" session_status:", realtor.get("session_status"))

    print_line()
    account = get_naver_account(realtor_id)
    if not account:
        print("[FAIL] naver account missing")
    else:
        print("[NAVER ACCOUNT]")
        print(" naver_id:", account.get("naver_id"))
        print(" blog_id:", account.get("blog_id"))
        print(" consent_status:", account.get("consent_status"))
        print(" login_status:", account.get("login_status"))
        print(" last_login_at:", account.get("last_login_at"))
        if account.get("last_login_error"):
            print(" last_login_error:", account.get("last_login_error"))
        if show_password:
            print("[PASSWORD PLAIN]", account.get("naver_password_plain"))

    print_line()
    sfile = session_file_path(realtor_id)
    print("[SESSION FILE]", sfile, "OK" if os.path.exists(sfile) else "MISSING")

    print_line()
    has_map = bool(safe_str(realtor.get("map_image_url")).strip())
    print("[MAP]", "OK" if has_map else "MISSING")
    if has_map:
        print(" map_image_url:", realtor.get("map_image_url"))
        print(" map_generated_at:", realtor.get("map_generated_at"))

    print_line()
    draft = get_latest_draft(realtor_id)
    if not draft:
        print("[DRAFT] MISSING")
    else:
        print("[LATEST DRAFT]")
        print(" draft_id:", draft.get("id"))
        print(" article_no:", draft.get("article_no"))
        print(" status:", draft.get("status"), "/", draft.get("draft_status"))
        print(" clipboard_len:", draft.get("clipboard_len"))

    print_line()
    print("[QUEUE SUMMARY]")
    rows = get_queue_summary(realtor_id)
    if not rows:
        print(" no queue")
    for r in rows:
        print(" ", r.get("queue_status"), r.get("worker_status"), r.get("last_error_type"), r.get("cnt"))

    print("[RECENT QUEUES]")
    for r in get_recent_queues(realtor_id):
        print(
            f" id={r.get('id')} article={r.get('article_no')} "
            f"status={r.get('queue_status')}/{r.get('worker_status')} "
            f"err={r.get('last_error_type')} updated={r.get('updated_at')}"
        )
        if r.get("error_message"):
            print("  message:", safe_str(r.get("error_message"))[:300])
        if r.get("publish_result_url"):
            print("  url:", r.get("publish_result_url"))

    print_line()
    diag = diagnose(realtor_id)
    print("[DIAGNOSE]", diag["status"], diag["issues"] or "no_issues")
    return diag


def generate_map(realtor_id):
    cmd = [sys.executable, "tools\\generate_realtor_naver_map.py", "--realtor-id", str(realtor_id)]
    return run_cmd(cmd)


def insert_map_into_draft(realtor_id):
    cmd = [sys.executable, "tools\\insert_realtor_map_into_draft.py", "--realtor-id", str(realtor_id)]
    return run_cmd(cmd)


def reset_hold_failed_to_pending(realtor_id):
    affected = exec_sql("""
        UPDATE blog_publish_queue
        SET
            queue_status = 'pending',
            worker_status = 'repair_retry',
            started_at = NULL,
            finished_at = NULL,
            last_error_type = NULL,
            error_message = CONCAT('[REPAIR RETRY] ', IFNULL(error_message, '')),
            updated_at = NOW()
        WHERE realtor_id = %s
          AND queue_status IN ('hold', 'failed')
    """, [realtor_id])
    print("[FIX] hold/failed -> pending affected=", affected)
    return affected


def do_fix(realtor_id):
    realtor = get_realtor(realtor_id)
    if not realtor:
        print("[FIX SKIP] realtor missing")
        return

    if not safe_str(realtor.get("map_image_url")).strip():
        print("[FIX] generating realtor map...")
        generate_map(realtor_id)
        print("[FIX] inserting map into draft...")
        insert_map_into_draft(realtor_id)

    reset_hold_failed_to_pending(realtor_id)


def run_publish_test(realtor_id):
    cmd = [
        sys.executable,
        "workers\\publish_worker.py",
        "--realtor-id", str(realtor_id),
        "--limit", "1",
        "--no-db-lock",
        "--no-lock",
    ]
    return run_cmd(cmd)


def get_all_target_realtor_ids(active_only=True):
    sql = """
        SELECT r.id
        FROM blog_realtors r
        LEFT JOIN blog_publish_settings ps
            ON ps.realtor_id = r.id
        WHERE 1=1
    """
    params = []

    if active_only:
        sql += " AND COALESCE(r.status, 'active') = 'active' "

    sql += " ORDER BY r.id ASC "

    rows = get_all(sql, params)
    return [int(r.get("id")) for r in rows]


def write_csv(path, rows):
    if not path:
        os.makedirs(REPORT_DIR, exist_ok=True)
        path = os.path.join(REPORT_DIR, "repair_report_" + datetime.now().strftime("%Y%m%d_%H%M%S") + ".csv")

    os.makedirs(os.path.dirname(os.path.abspath(path)), exist_ok=True)

    fields = [
        "realtor_id",
        "office_name",
        "status",
        "issues",
        "realtor_status",
        "auto_publish_enabled",
        "naver_id",
        "blog_id",
        "consent_status",
        "login_status",
        "has_session_file",
        "has_map",
        "latest_draft_id",
        "latest_article_no",
        "queue_pending",
        "queue_published",
        "queue_hold",
        "queue_failed",
        "queue_processing",
        "last_error_types",
    ]

    with open(path, "w", newline="", encoding="utf-8-sig") as f:
        w = csv.DictWriter(f, fieldnames=fields)
        w.writeheader()
        for r in rows:
            w.writerow({k: r.get(k, "") for k in fields})

    print("[CSV REPORT]", path)
    return path


def run_all(args):
    ids = get_all_target_realtor_ids(active_only=not args.include_inactive)

    if args.limit and int(args.limit) > 0:
        ids = ids[:int(args.limit)]

    print_line()
    print("STEP116 REPAIR ALL")
    print("targets:", len(ids))
    print("fix:", args.fix)
    print("time:", datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    print_line()

    reports = []

    summary = {
        "OK": 0,
        "WARN": 0,
        "FAIL": 0,
    }

    for idx, realtor_id in enumerate(ids, start=1):
        diag = diagnose(realtor_id)
        reports.append(diag)
        summary[diag["status"]] = summary.get(diag["status"], 0) + 1

        print(
            f"[{idx}/{len(ids)}]",
            f"id={diag['realtor_id']}",
            f"office={diag['office_name']}",
            f"status={diag['status']}",
            f"issues={diag['issues'] or '-'}",
        )

        if args.fix and not args.check_only:
            if diag["status"] in ["WARN", "FAIL"]:
                print("[AUTO FIX TARGET]", realtor_id)
                do_fix(realtor_id)

    print_line()
    print("REPAIR SUMMARY")
    print("total:", len(reports))
    print("OK:", summary.get("OK", 0))
    print("WARN:", summary.get("WARN", 0))
    print("FAIL:", summary.get("FAIL", 0))

    issue_counts = {}
    for r in reports:
        for issue in safe_str(r.get("issues")).split(","):
            issue = issue.strip()
            if not issue:
                continue
            issue_counts[issue] = issue_counts.get(issue, 0) + 1

    if issue_counts:
        print("[ISSUE COUNTS]")
        for k, v in sorted(issue_counts.items(), key=lambda x: (-x[1], x[0])):
            print(" ", k, v)

    if args.report_csv is not None:
        csv_path = args.report_csv.strip()
        write_csv(csv_path, reports)

    print_line()
    print("[DONE] repair all completed")
    return reports


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--realtor-id", type=int, default=None)
    parser.add_argument("--all", action="store_true", help="전체 활성 중개사 점검")
    parser.add_argument("--include-inactive", action="store_true", help="--all에서 inactive도 포함")
    parser.add_argument("--limit", type=int, default=None, help="--all 점검 개수 제한")
    parser.add_argument("--fix", action="store_true", help="가능한 자동 복구 수행")
    parser.add_argument("--check-only", action="store_true", help="조회만 수행")
    parser.add_argument("--test-publish", action="store_true", help="단일 중개사 수동 발행 테스트 1건 수행")
    parser.add_argument("--show-password", action="store_true", help="naver_password_plain 출력")
    parser.add_argument("--report-csv", nargs="?", const="", default=None, help="--all 결과 CSV 저장. 경로 생략 시 storage/reports 자동 생성")
    args = parser.parse_args()

    if args.all:
        run_all(args)
        return

    if not args.realtor_id:
        parser.error("--realtor-id 또는 --all 중 하나가 필요합니다.")

    realtor_id = int(args.realtor_id)

    print_single_detail(realtor_id, show_password=args.show_password)

    if args.fix and not args.check_only:
        print_line()
        do_fix(realtor_id)

    if args.test_publish:
        print_line()
        print("[TEST PUBLISH START]")
        run_publish_test(realtor_id)

    print_line()
    print("[DONE] repair check completed")
    print_line()


if __name__ == "__main__":
    main()