import sqlite3

from investibook.db import queries as dbq


def test_db_init_migrates_legacy_eval_case_runs_schema(tmp_path, monkeypatch):
    db_path = tmp_path / "investibook.db"
    conn = sqlite3.connect(db_path)
    conn.execute(
        """
        CREATE TABLE eval_runs (
            id                  TEXT PRIMARY KEY,
            suite_id            TEXT NOT NULL,
            suite_version       TEXT NOT NULL,
            registry_revision   TEXT NOT NULL DEFAULT '',
            trigger_mode        TEXT NOT NULL DEFAULT 'manual',
            execution_status    TEXT NOT NULL,
            comparison_status   TEXT NOT NULL DEFAULT 'pending',
            promote_eligibility TEXT NOT NULL DEFAULT 'unknown',
            baseline_id         TEXT,
            started_at          TEXT NOT NULL,
            finished_at         TEXT,
            case_count          INTEGER NOT NULL DEFAULT 0,
            passed_count        INTEGER NOT NULL DEFAULT 0,
            failed_count        INTEGER NOT NULL DEFAULT 0,
            invalid_count       INTEGER NOT NULL DEFAULT 0,
            summary_json        TEXT NOT NULL DEFAULT '{}'
        )
        """
    )
    conn.execute(
        """
        CREATE TABLE eval_case_runs (
            id                      TEXT PRIMARY KEY,
            run_id                  TEXT NOT NULL,
            case_id                 TEXT NOT NULL,
            case_version            TEXT NOT NULL,
            source_snapshot_id      TEXT NOT NULL,
            source_snapshot_digest  TEXT NOT NULL,
            agent_revision          TEXT NOT NULL DEFAULT '',
            environment_fingerprint TEXT NOT NULL DEFAULT '',
            judge_version           TEXT NOT NULL DEFAULT '',
            followup_policy_key     TEXT NOT NULL DEFAULT '',
            execution_status        TEXT NOT NULL,
            comparison_status       TEXT NOT NULL DEFAULT 'pending',
            started_at              TEXT NOT NULL,
            finished_at             TEXT,
            output_text             TEXT NOT NULL DEFAULT '',
            output_json             TEXT NOT NULL DEFAULT '{}',
            summary_json            TEXT NOT NULL DEFAULT '{}',
            UNIQUE(run_id, case_id)
        )
        """
    )
    conn.execute(
        """
        INSERT INTO eval_runs
            (id, suite_id, suite_version, execution_status, started_at)
        VALUES (?, ?, ?, ?, ?)
        """,
        (
            "evalrun-legacy",
            "core-daily-demo",
            "suite-v1",
            "completed",
            "2026-04-29T00:00:00+00:00",
        ),
    )
    conn.execute(
        """
        INSERT INTO eval_case_runs
            (id, run_id, case_id, case_version, source_snapshot_id, source_snapshot_digest,
             agent_revision, environment_fingerprint, judge_version, followup_policy_key,
             execution_status, comparison_status, started_at, output_text, output_json, summary_json)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """,
        (
            "evalcase-legacy",
            "evalrun-legacy",
            "component-restart-during-incident-window",
            "case-v1",
            "snapshot-1",
            "digest-1",
            "agent-v1",
            "env-v1",
            "judge-v1",
            "followup-v1",
            "completed",
            "pending",
            "2026-04-29T00:00:00+00:00",
            "legacy output",
            "{}",
            '{"workspace_id":"ws-legacy"}',
        ),
    )
    conn.commit()
    conn.close()

    monkeypatch.setattr(dbq, "DB_PATH", db_path)
    dbq._db_init()

    conn = sqlite3.connect(db_path)
    cols = {row[1] for row in conn.execute("PRAGMA table_info(eval_case_runs)").fetchall()}
    eval_run_cols = {row[1] for row in conn.execute("PRAGMA table_info(eval_runs)").fetchall()}
    row = conn.execute(
        """
        SELECT id, run_id, case_id, case_version, agent_revision,
               environment_fingerprint, judge_version, followup_policy_key
        FROM eval_case_runs
        WHERE id = 'evalcase-legacy'
        """
    ).fetchone()
    conn.close()

    assert "source_snapshot_id" not in cols
    assert "source_snapshot_digest" not in cols
    assert {"schedule_date", "idempotency_key", "attempt", "lease_owner", "lease_until", "skipped_reason", "job_exit_code", "job_started_at", "job_finished_at"} <= eval_run_cols
    assert row == (
        "evalcase-legacy",
        "evalrun-legacy",
        "component-restart-during-incident-window",
        "case-v1",
        "agent-v1",
        "env-v1",
        "judge-v1",
        "followup-v1",
    )