-- Migration 009: add the M2 remote repair event and preserve uncertain
-- executions as a distinct terminal state. SQLite cannot ALTER either CHECK
-- constraint, so both tables are rebuilt under one foreign-key-disabled
-- transaction. The pragma MUST remain outside BEGIN.
PRAGMA foreign_keys = OFF;
BEGIN IMMEDIATE;

CREATE TABLE events_v2 (
    seq INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id TEXT NOT NULL REFERENCES sessions(id),
    kind TEXT NOT NULL CHECK(kind IN (
        'input','scratchpad','execution.started','execution.completed','execution.unknown',
        'local.sent','delivery.held','kernel.reset','session.waited',
        'session.woke','hooks.activated','kernel.placement','remote.repair'
    )),
    source_key TEXT,
    payload TEXT NOT NULL CHECK(json_valid(payload)),
    created_ms INTEGER NOT NULL CHECK(created_ms >= 0),
    UNIQUE(session_id, source_key)
) STRICT;

INSERT INTO events_v2 (seq, session_id, kind, source_key, payload, created_ms)
SELECT seq, session_id, kind, source_key, payload, created_ms FROM events;

CREATE TABLE executions_v2 (
    id TEXT PRIMARY KEY,
    session_id TEXT NOT NULL REFERENCES sessions(id),
    generation TEXT NOT NULL,
    hook_revision TEXT NOT NULL REFERENCES releases(id),
    state TEXT NOT NULL CHECK(state IN ('running','ok','error','yielded','interrupted','unknown')),
    started_event INTEGER NOT NULL UNIQUE REFERENCES events(seq),
    completed_event INTEGER UNIQUE REFERENCES events(seq)
) STRICT;

INSERT INTO executions_v2 (id, session_id, generation, hook_revision, state, started_event, completed_event)
SELECT id, session_id, generation, hook_revision, state, started_event, completed_event FROM executions;

DROP TABLE executions;
DROP TABLE events;
ALTER TABLE events_v2 RENAME TO events;
ALTER TABLE executions_v2 RENAME TO executions;

CREATE INDEX events_session_order ON events(session_id, seq);
CREATE TRIGGER events_no_update BEFORE UPDATE ON events BEGIN
    SELECT RAISE(ABORT, 'historical events are immutable');
END;
CREATE TRIGGER events_no_delete BEFORE DELETE ON events BEGIN
    SELECT RAISE(ABORT, 'historical events are immutable');
END;
CREATE UNIQUE INDEX one_running_execution ON executions(session_id) WHERE completed_event IS NULL;

-- FATAL orphan guard: any row from pragma_foreign_key_check aborts this
-- migration before COMMIT. This catches existing FK children (inbox,
-- executions, and effects), not only an empty fresh database.
CREATE TEMP TABLE _orphan_guard (reason TEXT CHECK (reason IS NULL));
INSERT INTO _orphan_guard (reason)
SELECT 'orphaned ' || "table" || ' row ' || rowid || ' via fk ' || fkid || ' -> parent ' || parent
FROM pragma_foreign_key_check;
DROP TABLE _orphan_guard;

PRAGMA user_version = 9;
COMMIT;
PRAGMA foreign_keys = ON;