"""
S31-S33: Execution health and cost integrity checks.

Checks:
  S31. zombie_shots — Shots stuck in in-flight status for >45 minutes
  S32. asset_desync — Ghost shots (DB complete, file missing) + orphan files
  S33. cost_leakage — Shots at max attempts still in-flight (burning money)
"""

import os
import sqlite3
import sys
import time

# Bootstrap path: RECOIL_ROOT first (so `from core.X` resolves to recoil/core/
# top-level, not recoil/pipeline/core/). Then ensure_pipeline_importable adds
# PIPELINE_ROOT in the right order.
_PIPELINE_ROOT = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
_RECOIL_ROOT = os.path.dirname(_PIPELINE_ROOT)
if _RECOIL_ROOT not in sys.path:
    sys.path.insert(0, _RECOIL_ROOT)

from recoil.core.paths import STATE_NAMESPACE, ensure_pipeline_importable
ensure_pipeline_importable()
from recoil_checks import register_check


def _find_execution_dbs(base):
    """Find all execution.db files across projects."""
    dbs = []
    # Check projects root (sibling to starsend)
    projects_root = os.path.join(os.path.dirname(base), "projects")
    if os.path.isdir(projects_root):
        for proj in os.listdir(projects_root):
            if proj.startswith(("_", ".")):
                continue
            db_path = os.path.join(projects_root, proj, "state", STATE_NAMESPACE, "execution.db")
            if os.path.exists(db_path):
                dbs.append((proj, db_path))
    return dbs


def _query_db(db_path, sql, params=()):
    """Run a read-only query against an execution DB."""
    try:
        conn = sqlite3.connect(db_path, timeout=5.0)
        conn.row_factory = sqlite3.Row
        rows = conn.execute(sql, params).fetchall()
        conn.close()
        return [dict(r) for r in rows]
    except sqlite3.Error:
        return []


IN_FLIGHT_STATUSES = (
    "previs_generating", "keyframe_generating",
    "video_submitted", "video_processing", "video_downloading",
)

COMPLETED_STATUSES = (
    "previs_generated", "previs_approved",
    "keyframe_generated", "keyframe_approved",
    "video_ready", "video_complete",
)


def check_zombie_shots(base, discovered):
    """S31: Detect shots stuck in in-flight status for >45 minutes."""
    results = {"pass": [], "fail": [], "warn": []}

    dbs = _find_execution_dbs(base)
    if not dbs:
        results["pass"].append("No execution databases found")
        return results

    threshold = time.time() - (45 * 60)  # 45 minutes ago
    total_zombies = 0

    for project, db_path in dbs:
        placeholders = ",".join("?" for _ in IN_FLIGHT_STATUSES)
        rows = _query_db(
            db_path,
            f"""SELECT shot_id, status, updated_at
                FROM shots
                WHERE status IN ({placeholders})
                  AND updated_at < ?""",
            (*IN_FLIGHT_STATUSES, threshold),
        )
        if rows:
            total_zombies += len(rows)
            for row in rows[:5]:  # Cap per-project detail
                age_min = int((time.time() - row["updated_at"]) / 60)
                results["warn"].append(
                    f"[{project}] Zombie: {row['shot_id']} stuck in "
                    f"'{row['status']}' for {age_min} min"
                )
            if len(rows) > 5:
                results["warn"].append(
                    f"[{project}] ...and {len(rows) - 5} more zombie shots"
                )

    if total_zombies == 0:
        results["pass"].append("No zombie shots detected across all projects")

    return results


def check_asset_desync(base, discovered):
    """S32: Detect ghost shots (DB complete, file missing) and orphan files."""
    results = {"pass": [], "fail": [], "warn": []}

    dbs = _find_execution_dbs(base)
    if not dbs:
        results["pass"].append("No execution databases found")
        return results

    total_ghosts = 0
    total_orphans = 0

    for project, db_path in dbs:
        # Ghost detection: completed shots with output_path that doesn't exist
        placeholders = ",".join("?" for _ in COMPLETED_STATUSES)
        rows = _query_db(
            db_path,
            f"""SELECT shot_id, status, output_path
                FROM shots
                WHERE status IN ({placeholders})
                  AND output_path IS NOT NULL
                  AND output_path != ''""",
            COMPLETED_STATUSES,
        )

        for row in rows:
            output = row["output_path"]
            if output and not os.path.exists(output):
                total_ghosts += 1
                if total_ghosts <= 5:
                    results["warn"].append(
                        f"[{project}] Ghost: {row['shot_id']} status='{row['status']}' "
                        f"but output file missing"
                    )

        # Orphan detection: files in output dir not referenced by any shot
        projects_root = os.path.join(os.path.dirname(base), "projects")
        output_dir = os.path.join(projects_root, project, "output", "frames")
        if os.path.isdir(output_dir):
            all_outputs = _query_db(
                db_path,
                "SELECT output_path FROM shots WHERE output_path IS NOT NULL",
            )
            known_paths = {r["output_path"] for r in all_outputs if r["output_path"]}

            for ep_dir in os.listdir(output_dir):
                ep_path = os.path.join(output_dir, ep_dir)
                if not os.path.isdir(ep_path):
                    continue
                for fname in os.listdir(ep_path):
                    fpath = os.path.join(ep_path, fname)
                    if fpath not in known_paths and fname.endswith((".png", ".jpg", ".webp")):
                        total_orphans += 1

    if total_ghosts > 5:
        results["warn"].append(f"...and {total_ghosts - 5} more ghost shots total")
    if total_orphans > 0:
        results["warn"].append(
            f"{total_orphans} orphan file(s) in output dirs not referenced by any shot record"
        )

    if total_ghosts == 0 and total_orphans == 0:
        results["pass"].append("No asset desync detected")

    return results


def check_cost_leakage(base, discovered):
    """S33: Detect shots at max attempts still in-flight (wasting money on retries)."""
    results = {"pass": [], "fail": [], "warn": []}

    dbs = _find_execution_dbs(base)
    if not dbs:
        results["pass"].append("No execution databases found")
        return results

    total_leaking = 0

    for project, db_path in dbs:
        placeholders = ",".join("?" for _ in IN_FLIGHT_STATUSES)
        rows = _query_db(
            db_path,
            f"""SELECT shot_id, status, attempts, max_attempts, cost_incurred
                FROM shots
                WHERE status IN ({placeholders})
                  AND attempts >= max_attempts""",
            IN_FLIGHT_STATUSES,
        )

        if rows:
            total_leaking += len(rows)
            total_wasted = sum(r.get("cost_incurred", 0) for r in rows)
            for row in rows[:5]:
                results["warn"].append(
                    f"[{project}] Cost leak: {row['shot_id']} — "
                    f"{row['attempts']}/{row['max_attempts']} attempts, "
                    f"still '{row['status']}', ${row.get('cost_incurred', 0):.3f} spent"
                )
            if len(rows) > 5:
                results["warn"].append(
                    f"[{project}] ...and {len(rows) - 5} more leaking shots"
                )
            if total_wasted > 0:
                results["warn"].append(
                    f"[{project}] Total cost on exhausted shots: ${total_wasted:.2f}"
                )

    if total_leaking == 0:
        results["pass"].append("No cost leakage detected — all retries within limits")

    return results


# ═══════════════════════════════════════════════════════════════
# REGISTRATION
# ═══════════════════════════════════════════════════════════════

register_check("s31_zombie_shots", "Zombie Shots Detection", check_zombie_shots, section="store")
register_check("s32_asset_desync", "Execution ↔ File Desync", check_asset_desync, section="store")
register_check("s33_cost_leakage", "Cost Leakage Detection", check_cost_leakage, section="store")