# -*- coding: utf-8 -*-
"""
STEP101-02: stale 운영상태 복구 도구

- 오래된 blog_pipeline_runs running -> aborted
- 오래된 blog_publish_queue processing -> pending
- 오래된 blog_post_private_queue processing -> pending
- 만료된 blog_app_locks 삭제

사용:
python tools\recover_stale_pipeline_runs.py
python tools\recover_stale_pipeline_runs.py --dry-run
python tools\recover_stale_pipeline_runs.py --threshold-minutes 30 --queue-threshold-minutes 90
"""

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


def scalar(cur, sql, params=None):
    cur.execute(sql, params or ())
    row = cur.fetchone() or {}
    if not row:
        return 0
    return int(list(row.values())[0] or 0)


def table_exists(cur, table_name):
    return scalar(cur, """
        SELECT COUNT(*)
        FROM information_schema.tables
        WHERE table_schema = DATABASE()
          AND table_name = %s
    """, (table_name,)) > 0


def column_exists(cur, table_name, column_name):
    return scalar(cur, """
        SELECT COUNT(*)
        FROM information_schema.columns
        WHERE table_schema = DATABASE()
          AND table_name = %s
          AND column_name = %s
    """, (table_name, column_name)) > 0


def recover_pipeline_runs(cur, threshold_minutes, dry_run):
    table = "blog_pipeline_runs"

    if not table_exists(cur, table):
        print("[SKIP] blog_pipeline_runs table missing")
        return 0

    cnt = scalar(cur, """
        SELECT COUNT(*)
        FROM blog_pipeline_runs
        WHERE status = 'running'
          AND started_at IS NOT NULL
          AND started_at < DATE_SUB(NOW(), INTERVAL %s MINUTE)
    """, (int(threshold_minutes),))

    print(f"[CHECK] stale pipeline_runs running={cnt}")

    if dry_run or cnt <= 0:
        return cnt

    cur.execute("""
        UPDATE blog_pipeline_runs
        SET
            status = 'aborted',
            finished_at = NOW(),
            message = CONCAT(
                '[AUTO RECOVERY] stale running run closed at ',
                DATE_FORMAT(NOW(), '%%Y-%%m-%%d %%H:%%i:%%s'),
                '. previous message: ',
                IFNULL(message, '')
            )
        WHERE status = 'running'
          AND started_at IS NOT NULL
          AND started_at < DATE_SUB(NOW(), INTERVAL %s MINUTE)
    """, (int(threshold_minutes),))

    print(f"[RECOVERED] blog_pipeline_runs aborted={cur.rowcount}")
    return cur.rowcount


def recover_processing_queue(cur, table, threshold_minutes, dry_run):
    if not table_exists(cur, table):
        print(f"[SKIP] {table} table missing")
        return 0

    if not column_exists(cur, table, "queue_status"):
        print(f"[SKIP] {table}.queue_status column missing")
        return 0

    time_col = "started_at" if column_exists(cur, table, "started_at") else "updated_at"

    cnt = scalar(cur, f"""
        SELECT COUNT(*)
        FROM {table}
        WHERE queue_status = 'processing'
          AND {time_col} IS NOT NULL
          AND {time_col} < DATE_SUB(NOW(), INTERVAL %s MINUTE)
    """, (int(threshold_minutes),))

    print(f"[CHECK] stale {table} processing={cnt} time_col={time_col}")

    if dry_run or cnt <= 0:
        return cnt

    set_parts = [
        "queue_status = 'pending'",
        "updated_at = NOW()",
    ]

    if column_exists(cur, table, "worker_status"):
        set_parts.append("worker_status = 'recovered_stale_processing'")

    if column_exists(cur, table, "error_message"):
        set_parts.append("""
            error_message = CONCAT(
                '[AUTO RECOVERY] stale processing reset to pending at ',
                DATE_FORMAT(NOW(), '%%Y-%%m-%%d %%H:%%i:%%s'),
                '. previous message: ',
                IFNULL(error_message, '')
            )
        """)

    if column_exists(cur, table, "started_at"):
        set_parts.append("started_at = NULL")

    if column_exists(cur, table, "finished_at"):
        set_parts.append("finished_at = NULL")

    sql = f"""
        UPDATE {table}
        SET {", ".join(set_parts)}
        WHERE queue_status = 'processing'
          AND {time_col} IS NOT NULL
          AND {time_col} < DATE_SUB(NOW(), INTERVAL %s MINUTE)
    """

    cur.execute(sql, (int(threshold_minutes),))
    print(f"[RECOVERED] {table} processing->pending={cur.rowcount}")
    return cur.rowcount


def cleanup_expired_app_locks(cur, dry_run):
    table = "blog_app_locks"

    if not table_exists(cur, table):
        print("[SKIP] blog_app_locks table missing")
        return 0

    if not column_exists(cur, table, "expires_at"):
        print("[SKIP] blog_app_locks.expires_at column missing")
        return 0

    cnt = scalar(cur, """
        SELECT COUNT(*)
        FROM blog_app_locks
        WHERE expires_at < NOW()
    """)

    print(f"[CHECK] expired app_locks={cnt}")

    if dry_run or cnt <= 0:
        return cnt

    cur.execute("""
        DELETE FROM blog_app_locks
        WHERE expires_at < NOW()
    """)

    print(f"[RECOVERED] expired app_locks deleted={cur.rowcount}")
    return cur.rowcount


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--threshold-minutes", type=int, default=30)
    parser.add_argument("--queue-threshold-minutes", type=int, default=90)
    parser.add_argument("--dry-run", action="store_true")
    args = parser.parse_args()

    print("=" * 80)
    print("[STEP101-02 RECOVERY START]", datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    print(
        "[OPTIONS]",
        f"threshold_minutes={args.threshold_minutes}",
        f"queue_threshold_minutes={args.queue_threshold_minutes}",
        f"dry_run={args.dry_run}",
    )
    print("=" * 80)

    conn = get_conn()

    try:
        with conn.cursor() as cur:
            recover_pipeline_runs(cur, args.threshold_minutes, args.dry_run)
            recover_processing_queue(cur, "blog_publish_queue", args.queue_threshold_minutes, args.dry_run)
            recover_processing_queue(cur, "blog_post_private_queue", args.queue_threshold_minutes, args.dry_run)
            cleanup_expired_app_locks(cur, args.dry_run)

        if args.dry_run:
            conn.rollback()
            print("[DRY RUN] rollback complete")
        else:
            conn.commit()
            print("[COMMIT] recovery changes saved")

    except Exception as e:
        conn.rollback()
        print("[ERROR]", str(e))
        raise

    finally:
        conn.close()

    print("=" * 80)
    print("[STEP101-02 RECOVERY DONE]")
    print("=" * 80)


if __name__ == "__main__":
    main()