-- Externalize apps and analysis to Gitea repositories.
-- Adds repo_url to apps, creates analysis table with FTS5.

ALTER TABLE apps ADD COLUMN repo_url TEXT NOT NULL DEFAULT '';

-- Analysis table: independent Jupyter/data explorations tracked in the registry.
CREATE TABLE IF NOT EXISTS analysis (
    id               TEXT PRIMARY KEY,
    name             TEXT NOT NULL,
    lang             TEXT NOT NULL,
    domain           TEXT NOT NULL,
    description      TEXT NOT NULL,
    tags             TEXT NOT NULL DEFAULT '[]',
    uses_functions   TEXT NOT NULL DEFAULT '[]',
    uses_types       TEXT NOT NULL DEFAULT '[]',
    framework        TEXT NOT NULL DEFAULT '',
    entry_point      TEXT NOT NULL DEFAULT '',
    documentation    TEXT NOT NULL DEFAULT '',
    notes            TEXT NOT NULL DEFAULT '',
    repo_url         TEXT NOT NULL DEFAULT '',
    dir_path         TEXT NOT NULL DEFAULT '',
    content_hash     TEXT NOT NULL DEFAULT '',
    created_at       TEXT NOT NULL,
    updated_at       TEXT NOT NULL
);

CREATE VIRTUAL TABLE IF NOT EXISTS analysis_fts USING fts5(
    id,
    name,
    description,
    tags,
    domain,
    documentation,
    notes,
    content='analysis',
    content_rowid='rowid'
);

CREATE TRIGGER IF NOT EXISTS analysis_ai AFTER INSERT ON analysis BEGIN
    INSERT INTO analysis_fts(rowid, id, name, description, tags, domain, documentation, notes)
    VALUES (new.rowid, new.id, new.name, new.description, new.tags, new.domain, new.documentation, new.notes);
END;

CREATE TRIGGER IF NOT EXISTS analysis_ad AFTER DELETE ON analysis BEGIN
    INSERT INTO analysis_fts(analysis_fts, rowid, id, name, description, tags, domain, documentation, notes)
    VALUES ('delete', old.rowid, old.id, old.name, old.description, old.tags, old.domain, old.documentation, old.notes);
END;

CREATE TRIGGER IF NOT EXISTS analysis_au AFTER UPDATE ON analysis BEGIN
    INSERT INTO analysis_fts(analysis_fts, rowid, id, name, description, tags, domain, documentation, notes)
    VALUES ('delete', old.rowid, old.id, old.name, old.description, old.tags, old.domain, old.documentation, old.notes);
    INSERT INTO analysis_fts(rowid, id, name, description, tags, domain, documentation, notes)
    VALUES (new.rowid, new.id, new.name, new.description, new.tags, new.domain, new.documentation, new.notes);
END;