-- Design Repository — SQLite Schema
-- Queryable metadata index across all design projects.
-- Layer 2 of the two-layer model (Layer 1 = file system artifacts).
-- Rebuildable from manifest.json files via: design.py rebuild-index

CREATE TABLE IF NOT EXISTS projects (
    slug TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TEXT NOT NULL,
    status TEXT DEFAULT 'active',
    description TEXT
);

CREATE TABLE IF NOT EXISTS phases (
    id TEXT PRIMARY KEY,
    project_slug TEXT REFERENCES projects(slug),
    number INTEGER NOT NULL,
    name TEXT NOT NULL,
    goal TEXT,
    outcome TEXT,
    started_at TEXT,
    completed_at TEXT
);

CREATE TABLE IF NOT EXISTS iterations (
    id TEXT PRIMARY KEY,
    phase_id TEXT REFERENCES phases(id),
    project_slug TEXT REFERENCES projects(slug),
    version INTEGER NOT NULL,
    tier INTEGER NOT NULL,
    status TEXT NOT NULL,
    intent TEXT,
    parent_id TEXT,
    created_at TEXT NOT NULL,
    tokens_json TEXT,
    tags TEXT,
    fonts TEXT,
    palette_bg TEXT,
    palette_accent TEXT,
    html_path TEXT,
    screenshot_path TEXT,
    plan_path TEXT,
    brief_path TEXT,
    build_path TEXT
);

CREATE TABLE IF NOT EXISTS feedback (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    iteration_id TEXT REFERENCES iterations(id),
    component TEXT,
    from_person TEXT DEFAULT 'Mark',
    timestamp TEXT NOT NULL,
    verbatim TEXT NOT NULL,
    interpreted TEXT,
    action_taken TEXT
);

CREATE TABLE IF NOT EXISTS favorites (
    iteration_id TEXT REFERENCES iterations(id),
    added_at TEXT NOT NULL,
    reason TEXT
);

-- Full-text search indexes for cross-project queries
CREATE VIRTUAL TABLE IF NOT EXISTS iterations_fts USING fts5(id, intent, tags, fonts);
CREATE VIRTUAL TABLE IF NOT EXISTS feedback_fts USING fts5(verbatim, interpreted);
