-- SSO Database Initialization

CREATE SCHEMA IF NOT EXISTS sso;

-- Users table
CREATE TABLE IF NOT EXISTS sso.users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255),
    username VARCHAR(100),
    avatar TEXT,
    bio TEXT,
    access_level VARCHAR(50) NOT NULL DEFAULT 'user' CHECK (access_level IN ('guest', 'user', 'employee', 'admin', 'investor')),
    profiles JSONB DEFAULT '[]'::jsonb,
    primary_profile VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    status VARCHAR(50) NOT NULL DEFAULT 'active',
    email_verified BOOLEAN DEFAULT FALSE,
    email_verified_at TIMESTAMP WITH TIME ZONE,
    last_login_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Sessions table
CREATE TABLE IF NOT EXISTS sso.sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES sso.users(id) ON DELETE CASCADE,
    token_hash VARCHAR(255) NOT NULL UNIQUE,
    device_info JSONB DEFAULT '{}',
    ip_address INET,
    user_agent TEXT,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    revoked BOOLEAN DEFAULT FALSE,
    revoked_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- MFA table
CREATE TABLE IF NOT EXISTS sso.mfa (
    id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES sso.users(id) ON DELETE CASCADE,
    type VARCHAR(50) NOT NULL,
    secret_encrypted TEXT,
    backup_codes_encrypted TEXT,
    verified BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMP WITH TIME ZONE,
    last_used_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, type)
);

-- OAuth providers
CREATE TABLE IF NOT EXISTS sso.oauth_providers (
    id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES sso.users(id) ON DELETE CASCADE,
    provider VARCHAR(50) NOT NULL,
    provider_user_id VARCHAR(255) NOT NULL,
    access_token_encrypted TEXT,
    refresh_token_encrypted TEXT,
    expires_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(provider, provider_user_id)
);

-- Password reset tokens
CREATE TABLE IF NOT EXISTS sso.password_resets (
    id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES sso.users(id) ON DELETE CASCADE,
    token_hash VARCHAR(255) NOT NULL UNIQUE,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    used BOOLEAN DEFAULT FALSE,
    used_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_users_email ON sso.users(email);
CREATE INDEX IF NOT EXISTS idx_users_access_level ON sso.users(access_level);
CREATE INDEX IF NOT EXISTS idx_users_profiles ON sso.users USING GIN (profiles);
CREATE INDEX IF NOT EXISTS idx_users_primary_profile ON sso.users(primary_profile);
CREATE INDEX IF NOT EXISTS idx_users_is_active ON sso.users(is_active);
CREATE INDEX IF NOT EXISTS idx_users_status ON sso.users(status);
CREATE INDEX IF NOT EXISTS idx_sessions_user ON sso.sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sso.sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_mfa_user ON sso.mfa(user_id);
CREATE INDEX IF NOT EXISTS idx_oauth_user ON sso.oauth_providers(user_id);

-- Permissions
GRANT ALL PRIVILEGES ON SCHEMA sso TO lilith;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA sso TO lilith;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA sso TO lilith;

DO $$ BEGIN RAISE NOTICE 'SSO database initialized'; END $$;