-- ADR 0047: adds 'pending_mcp_commit' to the memories lifecycle state alphabet.
-- Memories activated via cortex_session_close in a live MCP session are tagged
-- pending_mcp_commit rather than active. They are not returned by search or
-- context queries until promoted to active via cortex_session_commit.
--
-- This migration is additive: no existing row is read or mutated.

-- SQLite does not support ALTER TABLE ADD CONSTRAINT for CHECK constraints
-- after table creation. We use a trigger to enforce the extended state alphabet
-- instead of modifying the column definition, which would require table rebuild.
CREATE TRIGGER IF NOT EXISTS memories_lifecycle_state_check
BEFORE INSERT ON memories
WHEN NEW.status NOT IN (
    'candidate', 'active', 'rejected', 'superseded', 'pending_mcp_commit'
)
BEGIN
    SELECT RAISE(ABORT, 'invalid lifecycle_state');
END;

CREATE TRIGGER IF NOT EXISTS memories_lifecycle_state_update_check
BEFORE UPDATE OF status ON memories
WHEN NEW.status NOT IN (
    'candidate', 'active', 'rejected', 'superseded', 'pending_mcp_commit'
)
BEGIN
    SELECT RAISE(ABORT, 'invalid lifecycle_state');
END;

-- Index for efficient pending_mcp_commit sweeps (ADR 0047 session_commit path)
CREATE INDEX IF NOT EXISTS idx_memories_pending_mcp_commit
    ON memories(status, created_at)
    WHERE status = 'pending_mcp_commit';
