-- ==========================================
-- Switchboard Core — 001 Core (SQLite)
-- ==========================================

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS users (
    id              TEXT PRIMARY KEY,
    username        TEXT NOT NULL,
    email           TEXT NOT NULL,
    password_hash   TEXT,
    display_name    TEXT,
    avatar_url      TEXT,
    is_active       INTEGER DEFAULT 1,
    settings        TEXT DEFAULT '{}',
    auth_source     TEXT NOT NULL DEFAULT 'builtin',
    external_id     TEXT,
    handle          TEXT NOT NULL,
    encrypted_uek   BLOB,
    uek_salt        BLOB,
    uek_nonce       BLOB,
    vault_set       INTEGER NOT NULL DEFAULT 0,
    created_at      TEXT DEFAULT (datetime('now')),
    updated_at      TEXT DEFAULT (datetime('now')),
    last_login_at   TEXT
);

CREATE UNIQUE INDEX IF NOT EXISTS users_username_ci ON users (username COLLATE NOCASE);
CREATE UNIQUE INDEX IF NOT EXISTS users_email_ci ON users (email COLLATE NOCASE);
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_handle ON users(handle COLLATE NOCASE);
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_external_id ON users(auth_source, external_id);
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

CREATE TRIGGER IF NOT EXISTS users_updated_at AFTER UPDATE ON users
    FOR EACH ROW WHEN NEW.updated_at = OLD.updated_at
    BEGIN UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id; END;

CREATE TABLE IF NOT EXISTS refresh_tokens (
    id          TEXT PRIMARY KEY,
    user_id     TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash  TEXT NOT NULL UNIQUE,
    expires_at  TEXT NOT NULL,
    created_at  TEXT DEFAULT (datetime('now')),
    revoked_at  TEXT
);

CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user ON refresh_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_hash ON refresh_tokens(token_hash);

CREATE TABLE IF NOT EXISTS platform_policies (
    key         TEXT PRIMARY KEY,
    value       TEXT NOT NULL,
    updated_by  TEXT REFERENCES users(id),
    updated_at  TEXT DEFAULT (datetime('now'))
);

INSERT OR IGNORE INTO platform_policies (key, value) VALUES
    ('allow_registration',  'true'),
    ('default_user_active', 'false');

CREATE TABLE IF NOT EXISTS global_settings (
    key         TEXT PRIMARY KEY,
    value       TEXT NOT NULL DEFAULT '{}',
    updated_at  TEXT DEFAULT (datetime('now')),
    updated_by  TEXT REFERENCES users(id)
);

INSERT OR IGNORE INTO global_settings (key, value) VALUES
    ('registration', '{"enabled": true}'),
    ('site', '{"name": "Switchboard Core", "tagline": "Extension Platform"}'),
    ('banner', '{"enabled": false, "text": "", "bg": "#007a33", "fg": "#ffffff"}');

CREATE TABLE IF NOT EXISTS user_presence (
    user_id     TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    last_seen   TEXT NOT NULL DEFAULT (datetime('now')),
    status      TEXT NOT NULL DEFAULT 'online'
                CHECK (status IN ('online', 'away', 'offline'))
);

CREATE INDEX IF NOT EXISTS idx_user_presence_last_seen ON user_presence(last_seen);

CREATE TABLE IF NOT EXISTS oidc_auth_state (
    state       TEXT PRIMARY KEY,
    nonce       TEXT NOT NULL,
    redirect_to TEXT,
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_oidc_state_created ON oidc_auth_state(created_at);
