src-tauri/migrations/002_phase3_tables.sql

-- Library items table
CREATE TABLE IF NOT EXISTS library_items (
    id TEXT PRIMARY KEY NOT NULL,
    url TEXT NOT NULL,
    source_url TEXT NOT NULL DEFAULT '',
    title TEXT NOT NULL DEFAULT 'Untitled',
    hash TEXT NOT NULL,
    width INTEGER NOT NULL DEFAULT 0,
    height INTEGER NOT NULL DEFAULT 0,
    colors TEXT NOT NULL DEFAULT '[]',
    tags TEXT NOT NULL DEFAULT '[]',
    notes TEXT NOT NULL DEFAULT '',
    created_at INTEGER NOT NULL DEFAULT 0
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_library_hash ON library_items(hash);

-- FTS5 for library search
CREATE VIRTUAL TABLE IF NOT EXISTS library_fts USING fts5(
    title, tags, notes, source_url,
    content='library_items',
    content_rowid='rowid'
);

-- Board items table
CREATE TABLE IF NOT EXISTS board_items (
    id TEXT PRIMARY KEY NOT NULL,
    kind TEXT NOT NULL DEFAULT 'image',
    library_id TEXT,
    text TEXT,
    colors TEXT NOT NULL DEFAULT '[]',
    x REAL NOT NULL DEFAULT 0,
    y REAL NOT NULL DEFAULT 0,
    w REAL NOT NULL DEFAULT 200,
    h REAL NOT NULL DEFAULT 200,
    z INTEGER NOT NULL DEFAULT 0
);

-- Sessions
CREATE TABLE IF NOT EXISTS sessions (
    id TEXT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    tabs TEXT NOT NULL DEFAULT '[]',
    created_at INTEGER NOT NULL DEFAULT 0
);

-- Downloads
CREATE TABLE IF NOT EXISTS downloads (
    id TEXT PRIMARY KEY NOT NULL,
    url TEXT NOT NULL,
    filename TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'pending',
    saved_to_library INTEGER NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL DEFAULT 0
);

-- Credentials index (passwords stored in Stronghold, only metadata here)
CREATE TABLE IF NOT EXISTS credentials (
    id TEXT PRIMARY KEY NOT NULL,
    domain TEXT NOT NULL,
    username TEXT NOT NULL,
    vault_key TEXT NOT NULL,
    label TEXT NOT NULL DEFAULT '',
    last_used INTEGER NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_credentials_domain ON credentials(domain);

-- Adblock user rules
CREATE TABLE IF NOT EXISTS adblock_user_rules (
    id TEXT PRIMARY KEY NOT NULL,
    rule_text TEXT NOT NULL,
    enabled INTEGER NOT NULL DEFAULT 1,
    comment TEXT NOT NULL DEFAULT '',
    created_at INTEGER NOT NULL DEFAULT 0
);

-- Adblock subscriptions
CREATE TABLE IF NOT EXISTS adblock_subscriptions (
    id TEXT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    url TEXT NOT NULL UNIQUE,
    enabled INTEGER NOT NULL DEFAULT 1,
    last_updated INTEGER NOT NULL DEFAULT 0,
    is_builtin INTEGER NOT NULL DEFAULT 0
);

INSERT OR IGNORE INTO adblock_subscriptions (id, name, url, enabled, is_builtin) VALUES
('easylist', 'EasyList', 'https://easylist.to/easylist/easylist.txt', 1, 1),
('easyprivacy', 'EasyPrivacy', 'https://easylist.to/easylist/easyprivacy.txt', 1, 1),
('fanboy', 'Fanboy Annoyances', 'https://secure.fanboy.co.nz/fanboy-annoyance.txt', 1, 1),
('ublock', 'uBlock Filters', 'https://raw.githubusercontent.com/uBlockOrigin/uAssets/master/filters/filters.txt', 1, 1),
('peter-lowe', 'Peter Lowe', 'https://pgl.yoyo.org/adservers/serverlist.php?mimetype=plaintext', 1, 1);