#!/usr/bin/env python
"""배포 전 audit — running status row 분석 + parse 실패 detection (Block C C4, spec §7.3).

V3 patch B1: SQL cast 0 — application 레벨 datetime parse. invalid text started_at
row 도 안전하게 회수. plan v2.1.3 §3640~3740.

Usage:
    cd backend && PYTHONPATH=. .venv/bin/python scripts/audit_stale_running.py
    cd backend && STEP_RUNNING_TIMEOUT_SECONDS=7200 PYTHONPATH=. .venv/bin/python scripts/audit_stale_running.py

Exit code: 0 (clean) / 1 (stale 또는 parse_failed 또는 null_started 존재).
"""
import os
import sys
from datetime import datetime, timezone

import sqlalchemy as sa


TIMEOUT_SECONDS = int(os.environ.get("STEP_RUNNING_TIMEOUT_SECONDS", "3600"))


def main() -> int:
    from app.core.database import SessionLocal

    with SessionLocal() as db:
        rows = db.execute(sa.text("""
            SELECT id, project_id, episode_id, step_id, run_id,
                   started_at, updated_at, recovery_count
            FROM step_run
            WHERE status = 'running'
            ORDER BY started_at ASC NULLS FIRST
        """)).fetchall()

    now = datetime.now(timezone.utc)
    healthy: list = []
    stale: list = []
    parse_failed: list = []
    null_started: list = []

    for r in rows:
        if r.started_at is None:
            null_started.append(r)
            continue
        try:
            st = datetime.fromisoformat(str(r.started_at).replace("Z", "+00:00"))
            if st.tzinfo is None:
                st = st.replace(tzinfo=timezone.utc)
            elapsed = (now - st).total_seconds()
            if elapsed > TIMEOUT_SECONDS:
                stale.append((r, elapsed))
            else:
                healthy.append((r, elapsed))
        except (ValueError, TypeError) as exc:
            parse_failed.append((r, str(exc)))

    print(f"=== Running step_run audit (timeout={TIMEOUT_SECONDS}s) ===\n")
    print(f"[HEALTHY] {len(healthy)} rows (elapsed < {TIMEOUT_SECONDS}s)")
    for r, elapsed in healthy:
        print(
            f"  step={r.step_id} run_id={r.run_id} elapsed={elapsed:.0f}s "
            f"pid={r.project_id} eid={r.episode_id}"
        )

    print(
        f"\n[STALE] {len(stale)} rows (elapsed > {TIMEOUT_SECONDS}s — "
        f"STALE_RUNNING_RECOVERY 대상)"
    )
    for r, elapsed in stale:
        print(
            f"  step={r.step_id} run_id={r.run_id} elapsed={elapsed:.0f}s "
            f"started_at={r.started_at}"
        )
        print(
            f"    수동 reset SQL:\n"
            f"    UPDATE step_run SET status='failed', "
            f"error_message='pre-B deploy stale running reset', updated_at=NOW()::text "
            f"WHERE id='{r.id}';"
        )

    print(f"\n[PARSE_FAILED] {len(parse_failed)} rows (수동 investigation 필수)")
    for r, err in parse_failed:
        print(f"  step={r.step_id} started_at={r.started_at!r} err={err}")

    print(f"\n[NULL_STARTED] {len(null_started)} rows (수동 investigation 필수)")
    for r in null_started:
        print(f"  step={r.step_id} run_id={r.run_id} updated_at={r.updated_at}")

    if stale or parse_failed or null_started:
        return 1
    return 0


if __name__ == "__main__":
    sys.exit(main())