-- Context Database Initialization Script
-- This script sets up the schema for tracking work across sessions

-- Iterations: Named work contexts (e.g., "monorepo-migration", "auth-refactor")
CREATE TABLE IF NOT EXISTS iterations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    description TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    last_accessed_at TEXT DEFAULT (datetime('now'))
);

-- Sessions: Your work periods - one per conversation
CREATE TABLE IF NOT EXISTS sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    iteration_id INTEGER NOT NULL,
    started_at TEXT DEFAULT (datetime('now')),
    ended_at TEXT,
    summary TEXT,
    FOREIGN KEY (iteration_id) REFERENCES iterations(id)
);

-- Indexes for common queries
CREATE INDEX IF NOT EXISTS idx_sessions_started ON sessions(started_at DESC);
END;

CREATE TRIGGER IF NOT EXISTS update_iteration_accessed
AFTER UPDATE OF last_accessed_at ON iterations
BEGIN
    UPDATE iterations SET last_accessed_at = datetime('now') WHERE id = NEW.id;
END;
