-- 012_bundled_source.sql — v0.3.8
-- Adds 'bundled' to the packages.source CHECK constraint for auto-installed packages.
-- SQLite doesn't support ALTER CHECK — must recreate the table.

CREATE TABLE IF NOT EXISTS packages_new (
    id           TEXT PRIMARY KEY,
    title        TEXT NOT NULL,
    type         TEXT NOT NULL DEFAULT 'surface'
                 CHECK (type IN ('surface', 'extension', 'full', 'workflow', 'library')),
    version      TEXT NOT NULL DEFAULT '0.0.0',
    description  TEXT NOT NULL DEFAULT '',
    author       TEXT NOT NULL DEFAULT '',
    tier         TEXT NOT NULL DEFAULT 'browser'
                 CHECK (tier IN ('browser', 'starlark', 'sidecar')),
    is_system    INTEGER NOT NULL DEFAULT 0,
    scope        TEXT NOT NULL DEFAULT 'global'
                 CHECK (scope IN ('global', 'team', 'personal')),
    team_id      TEXT REFERENCES teams(id) ON DELETE CASCADE,
    installed_by TEXT REFERENCES users(id) ON DELETE SET NULL,
    manifest     TEXT NOT NULL DEFAULT '{}',
    enabled      INTEGER NOT NULL DEFAULT 1,
    status       TEXT NOT NULL DEFAULT 'active'
                 CHECK (status IN ('active', 'pending_review', 'suspended', 'dormant')),
    schema_version INTEGER NOT NULL DEFAULT 0,
    package_settings TEXT NOT NULL DEFAULT '{}',
    source       TEXT NOT NULL DEFAULT 'core'
                 CHECK (source IN ('core', 'builtin', 'extension', 'registry', 'bundled')),
    installed_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at   TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT OR IGNORE INTO packages_new SELECT * FROM packages;
DROP TABLE packages;
ALTER TABLE packages_new RENAME TO packages;

CREATE INDEX IF NOT EXISTS idx_packages_type ON packages(type);
CREATE INDEX IF NOT EXISTS idx_packages_enabled ON packages(enabled);
CREATE INDEX IF NOT EXISTS idx_packages_team ON packages(team_id);
CREATE INDEX IF NOT EXISTS idx_packages_source ON packages(source);
