-- ==========================================
-- Switchboard Core — 001 Core
-- ==========================================
-- Users, auth, platform policies, global settings, presence, OIDC.
-- ==========================================

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ── Users ───────────────────────────────────

CREATE TABLE IF NOT EXISTS users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username        VARCHAR(50) NOT NULL,
    email           VARCHAR(255) NOT NULL,
    password_hash   TEXT,
    display_name    VARCHAR(100),
    avatar_url      TEXT,
    is_active       BOOLEAN DEFAULT true,
    settings        JSONB DEFAULT '{}'::jsonb,
    auth_source     VARCHAR(20) NOT NULL DEFAULT 'builtin'
                    CHECK (auth_source IN ('builtin', 'mtls', 'oidc')),
    external_id     TEXT,
    handle          VARCHAR(100) NOT NULL,
    encrypted_uek   BYTEA,
    uek_salt        BYTEA,
    uek_nonce       BYTEA,
    vault_set       BOOLEAN NOT NULL DEFAULT false,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW(),
    last_login_at   TIMESTAMPTZ
);

CREATE UNIQUE INDEX IF NOT EXISTS users_username_ci ON users (LOWER(username));
CREATE UNIQUE INDEX IF NOT EXISTS users_email_ci ON users (LOWER(email));
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_handle ON users(LOWER(handle));
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_external_id
    ON users(auth_source, external_id) WHERE external_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

DROP TRIGGER IF EXISTS users_updated_at ON users;
CREATE TRIGGER users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ── Refresh Tokens ──────────────────────────

CREATE TABLE IF NOT EXISTS refresh_tokens (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash  TEXT NOT NULL UNIQUE,
    expires_at  TIMESTAMPTZ NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    revoked_at  TIMESTAMPTZ
);

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) WHERE revoked_at IS NULL;

-- ── Platform Policies ───────────────────────

CREATE TABLE IF NOT EXISTS platform_policies (
    key         VARCHAR(50) PRIMARY KEY,
    value       TEXT NOT NULL,
    updated_by  UUID REFERENCES users(id),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO platform_policies (key, value) VALUES
    ('allow_registration',  'true'),
    ('default_user_active', 'false')
ON CONFLICT (key) DO NOTHING;

-- ── Global Settings ─────────────────────────

CREATE TABLE IF NOT EXISTS global_settings (
    key         VARCHAR(100) PRIMARY KEY,
    value       JSONB NOT NULL DEFAULT '{}'::jsonb,
    updated_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_by  UUID REFERENCES users(id)
);

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

-- ── User Presence ───────────────────────────

CREATE TABLE IF NOT EXISTS user_presence (
    user_id     UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    last_seen   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    status      VARCHAR(20) 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 DESC);

-- ── OIDC Auth State ─────────────────────────

CREATE TABLE IF NOT EXISTS oidc_auth_state (
    state       TEXT PRIMARY KEY,
    nonce       TEXT NOT NULL,
    redirect_to TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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