trading-platform-database-v2/schemas/01b_oauth_providers.sql

-- ============================================================================
-- OrbiQuant IA - OAuth Providers
-- ============================================================================
-- Archivo: 01b_oauth_providers.sql
-- Descripción: Proveedores OAuth y vinculación de cuentas sociales
-- Fecha: 2025-12-05
-- ============================================================================

SET search_path TO public;

-- ============================================================================
-- ENUM: auth_provider_enum
-- ============================================================================
CREATE TYPE auth_provider_enum AS ENUM (
    'email',        -- Email/password tradicional
    'phone',        -- Teléfono (SMS/WhatsApp)
    'google',       -- Google OAuth
    'facebook',     -- Facebook OAuth
    'twitter',      -- X (Twitter) OAuth
    'apple',        -- Apple Sign In
    'github'        -- GitHub OAuth
);

-- ============================================================================
-- TABLA: oauth_accounts
-- Descripción: Cuentas OAuth vinculadas a usuarios
-- ============================================================================
CREATE TABLE IF NOT EXISTS oauth_accounts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    -- Proveedor
    provider auth_provider_enum NOT NULL,
    provider_account_id VARCHAR(255) NOT NULL,

    -- Tokens OAuth
    access_token TEXT,
    refresh_token TEXT,
    token_expires_at TIMESTAMPTZ,
    token_type VARCHAR(50),
    scope TEXT,

    -- Datos del perfil del proveedor
    provider_email VARCHAR(255),
    provider_name VARCHAR(255),
    provider_avatar_url TEXT,
    provider_profile JSONB,

    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    -- Constraints
    UNIQUE(provider, provider_account_id),
    UNIQUE(user_id, provider)
);

CREATE INDEX idx_oauth_user_id ON oauth_accounts(user_id);
CREATE INDEX idx_oauth_provider ON oauth_accounts(provider);
CREATE INDEX idx_oauth_provider_account ON oauth_accounts(provider, provider_account_id);

-- ============================================================================
-- TABLA: phone_verifications
-- Descripción: Verificaciones de teléfono (OTP via SMS/WhatsApp)
-- ============================================================================
CREATE TYPE phone_channel_enum AS ENUM ('sms', 'whatsapp', 'call');

CREATE TABLE IF NOT EXISTS phone_verifications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Teléfono
    phone_number VARCHAR(20) NOT NULL,
    country_code VARCHAR(5) NOT NULL,

    -- OTP
    otp_code VARCHAR(6) NOT NULL,
    otp_hash VARCHAR(255) NOT NULL,
    channel phone_channel_enum DEFAULT 'whatsapp',

    -- Estado
    verified BOOLEAN DEFAULT FALSE,
    attempts INT DEFAULT 0,
    max_attempts INT DEFAULT 3,

    -- Vinculación
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    purpose VARCHAR(50) NOT NULL DEFAULT 'login', -- login, register, verify, 2fa

    -- Validez
    expires_at TIMESTAMPTZ NOT NULL,
    verified_at TIMESTAMPTZ,

    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_phone_verif_phone ON phone_verifications(phone_number);
CREATE INDEX idx_phone_verif_expires ON phone_verifications(expires_at);
CREATE INDEX idx_phone_verif_user ON phone_verifications(user_id);

-- ============================================================================
-- TABLA: email_verifications
-- Descripción: Tokens de verificación de email
-- ============================================================================
CREATE TABLE IF NOT EXISTS email_verifications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Email
    email VARCHAR(255) NOT NULL,

    -- Token
    token VARCHAR(255) NOT NULL UNIQUE,
    token_hash VARCHAR(255) NOT NULL,

    -- Vinculación
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    purpose VARCHAR(50) NOT NULL DEFAULT 'verify', -- verify, reset_password, change_email

    -- Estado
    used BOOLEAN DEFAULT FALSE,

    -- Validez
    expires_at TIMESTAMPTZ NOT NULL,
    used_at TIMESTAMPTZ,

    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_email_verif_email ON email_verifications(email);
CREATE INDEX idx_email_verif_token ON email_verifications(token_hash);
CREATE INDEX idx_email_verif_user ON email_verifications(user_id);

-- ============================================================================
-- TABLA: auth_logs
-- Descripción: Logs de eventos de autenticación
-- ============================================================================
CREATE TYPE auth_event_enum AS ENUM (
    'login_success',
    'login_failed',
    'logout',
    'register',
    'password_reset_request',
    'password_reset_complete',
    'email_verified',
    'phone_verified',
    'oauth_linked',
    'oauth_unlinked',
    '2fa_enabled',
    '2fa_disabled',
    '2fa_verified',
    'session_revoked',
    'account_locked',
    'account_unlocked'
);

CREATE TABLE IF NOT EXISTS auth_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,

    -- Evento
    event auth_event_enum NOT NULL,
    provider auth_provider_enum,

    -- Contexto
    ip_address INET,
    user_agent TEXT,
    device_fingerprint VARCHAR(255),
    location JSONB, -- {country, city, region}

    -- Metadata
    metadata JSONB,

    -- Resultado
    success BOOLEAN DEFAULT TRUE,
    error_message TEXT,

    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_auth_logs_user ON auth_logs(user_id);
CREATE INDEX idx_auth_logs_event ON auth_logs(event);
CREATE INDEX idx_auth_logs_created ON auth_logs(created_at DESC);
CREATE INDEX idx_auth_logs_ip ON auth_logs(ip_address);

-- ============================================================================
-- MODIFICAR TABLA users: agregar campo auth_provider
-- ============================================================================
ALTER TABLE users ADD COLUMN IF NOT EXISTS primary_auth_provider auth_provider_enum DEFAULT 'email';

-- ============================================================================
-- TRIGGERS
-- ============================================================================

CREATE TRIGGER update_oauth_accounts_updated_at
    BEFORE UPDATE ON oauth_accounts
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- FUNCIONES DE UTILIDAD
-- ============================================================================

-- Función para generar OTP de 6 dígitos
CREATE OR REPLACE FUNCTION generate_otp()
RETURNS VARCHAR(6) AS $$
BEGIN
    RETURN LPAD(FLOOR(RANDOM() * 1000000)::TEXT, 6, '0');
END;
$$ LANGUAGE plpgsql;

-- Función para limpiar verificaciones expiradas
CREATE OR REPLACE FUNCTION cleanup_expired_verifications()
RETURNS void AS $$
BEGIN
    DELETE FROM phone_verifications WHERE expires_at < NOW() AND verified = FALSE;
    DELETE FROM email_verifications WHERE expires_at < NOW() AND used = FALSE;
END;
$$ LANGUAGE plpgsql;