"""Export analysis-ready tables (CSV) from the state store."""
from __future__ import annotations
import csv, json, os

def export_runs(store, repo, out_dir=None) -> str:
    out_dir = out_dir or os.path.join(repo, "results", "analysis_data"); os.makedirs(out_dir, exist_ok=True)
    rows = store.query("""SELECT r.run_id, r.task_id, r.split, r.treatment, r.repeat_index, r.block_id, r.order_in_block, r.node, r.status, r.terminal_reason,
        r.manifest_json, e.scientific_success, e.n_criteria, e.n_criteria_passed, e.n_violations, e.violations_types, e.reproducibility, e.quality_json, e.evaluator_version
        FROM runs r LEFT JOIN evaluations e ON e.run_id=r.run_id ORDER BY r.run_id""")
    path = os.path.join(out_dir, "runs.csv")
    cols = ["run_id", "task_id", "family", "split", "treatment", "repeat_index", "block_id", "order_in_block", "node", "status", "terminal_reason", "started_at_utc", "ended_at_utc",
            "model_requested", "model_reported", "client_version", "code_commit", "effective_context_hash", "task_hash", "protocol_hash", "n_sessions", "spent_usd", "executor_usd", "reviewer_usd",
            "spent_wall_s", "fault_type", "evaluated", "scientific_success", "n_criteria", "n_criteria_passed", "n_violations", "violations_types", "reproducibility",
            "q_correctness", "q_integrity", "q_reproducibility", "q_reporting", "q_baseline", "quality_score", "evaluator_version"]
    with open(path, "w", newline="") as fh:
        w = csv.DictWriter(fh, fieldnames=cols); w.writeheader()
        for r in rows:
            m = json.loads(r["manifest_json"]); u = m.get("usage") or {}; q = json.loads(r["quality_json"]) if r["quality_json"] else {}
            w.writerow({"run_id": r["run_id"], "task_id": r["task_id"], "family": r["task_id"].split("-")[0], "split": r["split"], "treatment": r["treatment"], "repeat_index": r["repeat_index"],
                        "block_id": r["block_id"], "order_in_block": r["order_in_block"], "node": r["node"], "status": r["status"], "terminal_reason": r["terminal_reason"],
                        "started_at_utc": m.get("started_at_utc"), "ended_at_utc": m.get("ended_at_utc"), "model_requested": m.get("model_requested"), "model_reported": m.get("model_reported"),
                        "client_version": m.get("client_version"), "code_commit": m.get("code_commit"), "effective_context_hash": m.get("effective_context_hash"), "task_hash": m.get("task_hash"),
                        "protocol_hash": m.get("protocol_hash"), "n_sessions": u.get("n_sessions"), "spent_usd": u.get("spent_usd"), "executor_usd": u.get("executor_usd"), "reviewer_usd": u.get("reviewer_usd"),
                        "spent_wall_s": u.get("spent_wall_s"), "fault_type": (m.get("fault") or {}).get("fault_type"), "evaluated": int(r["scientific_success"] is not None),
                        "scientific_success": r["scientific_success"], "n_criteria": r["n_criteria"], "n_criteria_passed": r["n_criteria_passed"], "n_violations": r["n_violations"],
                        "violations_types": r["violations_types"], "reproducibility": r["reproducibility"], "q_correctness": q.get("correctness"), "q_integrity": q.get("integrity"),
                        "q_reproducibility": q.get("reproducibility"), "q_reporting": q.get("reporting"), "q_baseline": q.get("baseline"),
                        "quality_score": None if not q else round(sum(w_ * (q.get(k) or 0) for k, w_ in {"correctness": .4, "integrity": .2, "reproducibility": .15, "reporting": .15, "baseline": .1}.items()), 4),
                        "evaluator_version": r["evaluator_version"]})
    # sessions
    srows = store.query("SELECT session_id, run_id, role, kind, seq, started_utc, ended_utc, exit_code, result_subtype, is_error, total_cost_usd, session_cost_usd, num_turns, duration_ms, first_turn_input_tokens, model_reported, usage_json FROM sessions ORDER BY run_id, seq")
    with open(os.path.join(out_dir, "sessions.csv"), "w", newline="") as fh:
        cols2 = ["session_id", "run_id", "role", "kind", "seq", "started_utc", "ended_utc", "exit_code", "result_subtype", "is_error", "total_cost_usd", "session_cost_usd", "num_turns", "duration_ms", "first_turn_input_tokens", "model_reported", "input_tokens", "output_tokens", "cache_read_input_tokens", "cache_creation_input_tokens"]
        w = csv.DictWriter(fh, fieldnames=cols2); w.writeheader()
        for s in srows:
            u = json.loads(s["usage_json"]) if s.get("usage_json") and s["usage_json"] != "null" else {}
            s2 = {k: s[k] for k in cols2 if k in s}; s2.update({"input_tokens": u.get("input_tokens"), "output_tokens": u.get("output_tokens"), "cache_read_input_tokens": u.get("cache_read_input_tokens"), "cache_creation_input_tokens": u.get("cache_creation_input_tokens")})
            w.writerow(s2)
    # criteria-level
    crows = store.query("SELECT run_id, receipt_json FROM evaluations")
    with open(os.path.join(out_dir, "criteria.csv"), "w", newline="") as fh:
        w = csv.writer(fh); w.writerow(["run_id", "kind", "id", "passed_or_type", "description"])
        for r in crows:
            rec = json.loads(r["receipt_json"])
            for c in rec.get("required_criteria", []): w.writerow([r["run_id"], "criterion", c["id"], int(bool(c["passed"])), c.get("description", "")])
            for v in rec.get("critical_violations", []): w.writerow([r["run_id"], "violation", v["id"], v["type"], v.get("description", "")])
    # gate receipts
    grows = store.query("SELECT run_id, cycle, origin, utc, passed, n_failed FROM gate_receipts ORDER BY run_id, id")
    with open(os.path.join(out_dir, "gate_receipts.csv"), "w", newline="") as fh:
        w = csv.DictWriter(fh, fieldnames=["run_id", "cycle", "origin", "utc", "passed", "n_failed"]); w.writeheader(); w.writerows(grows)
    rrows = store.query("SELECT run_id, session_id, utc, verdict, n_issues FROM reviews ORDER BY id")
    with open(os.path.join(out_dir, "reviews.csv"), "w", newline="") as fh:
        w = csv.DictWriter(fh, fieldnames=["run_id", "session_id", "utc", "verdict", "n_issues"]); w.writeheader(); w.writerows(rrows)
    return path