-- 9plan SQLite Schema
-- Requires SQLite with FTS5 support (built into Node.js 22.5.0+)

PRAGMA foreign_keys = ON;

-- Sessions table
CREATE TABLE IF NOT EXISTS sessions (
    name TEXT PRIMARY KEY,
    task_description TEXT,
    created_at TEXT DEFAULT (datetime('now'))
) STRICT;

-- Plans table with all lifecycle states
CREATE TABLE IF NOT EXISTS plans (
    id TEXT PRIMARY KEY,
    session_name TEXT NOT NULL REFERENCES sessions(name) ON DELETE CASCADE,
    status TEXT NOT NULL CHECK (status IN ('queued', 'active', 'completed', 'discarded')),
    queue_position INTEGER,

    goal TEXT NOT NULL,
    context TEXT,
    inputs TEXT,
    outputs TEXT,
    approach TEXT,
    success_criteria TEXT,
    notes TEXT,
    outcome TEXT,

    created_at TEXT DEFAULT (datetime('now')),
    completed_at TEXT
) STRICT;

-- Index for efficient queue queries (queued plans ordered by position)
CREATE INDEX IF NOT EXISTS idx_plans_queue
    ON plans(session_name, status, queue_position)
    WHERE status = 'queued';

-- Index for finding the active plan quickly
CREATE INDEX IF NOT EXISTS idx_plans_active
    ON plans(session_name, status)
    WHERE status = 'active';

-- FTS5 virtual table for history search
-- Searches across goal, context, inputs, outputs, and outcome
CREATE VIRTUAL TABLE IF NOT EXISTS plans_fts USING fts5(
    id, goal, context, inputs, outputs, outcome,
    content='plans', content_rowid='rowid'
);

-- Trigger: Keep FTS in sync on INSERT
CREATE TRIGGER IF NOT EXISTS plans_ai AFTER INSERT ON plans BEGIN
    INSERT INTO plans_fts(rowid, id, goal, context, inputs, outputs, outcome)
    VALUES (new.rowid, new.id, new.goal, new.context, new.inputs, new.outputs, new.outcome);
END;

-- Trigger: Keep FTS in sync on DELETE
CREATE TRIGGER IF NOT EXISTS plans_ad AFTER DELETE ON plans BEGIN
    INSERT INTO plans_fts(plans_fts, rowid, id, goal, context, inputs, outputs, outcome)
    VALUES ('delete', old.rowid, old.id, old.goal, old.context, old.inputs, old.outputs, old.outcome);
END;

-- Trigger: Keep FTS in sync on UPDATE
CREATE TRIGGER IF NOT EXISTS plans_au AFTER UPDATE ON plans BEGIN
    INSERT INTO plans_fts(plans_fts, rowid, id, goal, context, inputs, outputs, outcome)
    VALUES ('delete', old.rowid, old.id, old.goal, old.context, old.inputs, old.outputs, old.outcome);
    INSERT INTO plans_fts(rowid, id, goal, context, inputs, outputs, outcome)
    VALUES (new.rowid, new.id, new.goal, new.context, new.inputs, new.outputs, new.outcome);
END;