trading-platform-database/schemas/01_public_schema.sql

-- ============================================================================
-- OrbiQuant IA - Esquema PUBLIC
-- ============================================================================
-- Archivo: 01_public_schema.sql
-- Descripción: Usuarios, perfiles, autenticación y configuración
-- Fecha: 2025-12-05
-- ============================================================================

SET search_path TO public;

-- ============================================================================
-- TABLA: users
-- Descripción: Usuarios base del sistema (compatible con Supabase Auth)
-- ============================================================================
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) NOT NULL UNIQUE,
    email_verified BOOLEAN DEFAULT FALSE,
    phone VARCHAR(20),
    phone_verified BOOLEAN DEFAULT FALSE,
    encrypted_password VARCHAR(255),

    -- Metadata de auth
    confirmation_token VARCHAR(255),
    confirmation_sent_at TIMESTAMPTZ,
    confirmed_at TIMESTAMPTZ,
    recovery_token VARCHAR(255),
    recovery_sent_at TIMESTAMPTZ,

    -- 2FA
    totp_secret VARCHAR(255),
    totp_enabled BOOLEAN DEFAULT FALSE,
    backup_codes TEXT[],

    -- Control de acceso
    role user_role_enum DEFAULT 'investor',
    status status_enum DEFAULT 'pending',
    failed_login_attempts INT DEFAULT 0,
    locked_until TIMESTAMPTZ,
    last_login_at TIMESTAMPTZ,
    last_login_ip INET,

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

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_role ON users(role);

-- ============================================================================
-- TABLA: profiles
-- Descripción: Información extendida del usuario
-- ============================================================================
CREATE TABLE IF NOT EXISTS profiles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,

    -- Datos personales
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    display_name VARCHAR(100),
    avatar_url TEXT,
    date_of_birth DATE,

    -- Ubicación
    country_code CHAR(2),
    timezone VARCHAR(50) DEFAULT 'UTC',
    language VARCHAR(5) DEFAULT 'es',

    -- Preferencias
    preferred_currency CHAR(3) DEFAULT 'USD',

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

CREATE INDEX idx_profiles_user_id ON profiles(user_id);

-- ============================================================================
-- TABLA: user_settings
-- Descripción: Configuraciones y preferencias del usuario
-- ============================================================================
CREATE TABLE IF NOT EXISTS user_settings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,

    -- Notificaciones
    email_notifications BOOLEAN DEFAULT TRUE,
    push_notifications BOOLEAN DEFAULT TRUE,
    sms_notifications BOOLEAN DEFAULT FALSE,

    -- Alertas de trading
    price_alerts BOOLEAN DEFAULT TRUE,
    signal_alerts BOOLEAN DEFAULT TRUE,
    portfolio_alerts BOOLEAN DEFAULT TRUE,

    -- Reportes
    weekly_report BOOLEAN DEFAULT TRUE,
    monthly_report BOOLEAN DEFAULT TRUE,

    -- Privacidad
    profile_public BOOLEAN DEFAULT FALSE,
    show_portfolio_value BOOLEAN DEFAULT FALSE,

    -- UI
    theme VARCHAR(20) DEFAULT 'dark',
    chart_preferences JSONB DEFAULT '{}',

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

-- ============================================================================
-- TABLA: kyc_verifications
-- Descripción: Verificación de identidad (KYC)
-- ============================================================================
CREATE TYPE kyc_status_enum AS ENUM ('pending', 'submitted', 'approved', 'rejected', 'expired');
CREATE TYPE kyc_document_type AS ENUM ('passport', 'national_id', 'drivers_license', 'residence_permit');

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

    -- Estado
    status kyc_status_enum DEFAULT 'pending',
    level INT DEFAULT 0, -- 0: ninguno, 1: básico, 2: completo

    -- Documentos
    document_type kyc_document_type,
    document_number VARCHAR(50),
    document_country CHAR(2),
    document_expiry DATE,
    document_front_url TEXT,
    document_back_url TEXT,
    selfie_url TEXT,
    proof_of_address_url TEXT,

    -- Verificación
    verified_at TIMESTAMPTZ,
    verified_by UUID REFERENCES users(id),
    rejection_reason TEXT,

    -- Metadata
    verification_provider VARCHAR(50),
    external_verification_id VARCHAR(100),

    -- Timestamps
    submitted_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_kyc_user_id ON kyc_verifications(user_id);
CREATE INDEX idx_kyc_status ON kyc_verifications(status);

-- ============================================================================
-- TABLA: risk_profiles
-- Descripción: Perfil de riesgo del usuario (cuestionario)
-- ============================================================================
CREATE TABLE IF NOT EXISTS risk_profiles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    -- Resultado
    profile risk_profile_enum NOT NULL,
    score INT, -- 0-100

    -- Respuestas del cuestionario (JSON)
    questionnaire_answers JSONB NOT NULL,

    -- Parámetros derivados
    max_drawdown_tolerance DECIMAL(5,2), -- % máximo de pérdida tolerada
    investment_horizon_months INT,       -- Horizonte en meses
    experience_level INT,                -- 1-5

    -- Validez
    valid_until TIMESTAMPTZ,

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

CREATE INDEX idx_risk_profiles_user_id ON risk_profiles(user_id);

-- ============================================================================
-- TABLA: sessions
-- Descripción: Sesiones activas de usuarios
-- ============================================================================
CREATE TABLE IF NOT EXISTS sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    -- Token
    refresh_token VARCHAR(255) NOT NULL UNIQUE,

    -- Información del dispositivo
    user_agent TEXT,
    ip_address INET,
    device_info JSONB,

    -- Validez
    expires_at TIMESTAMPTZ NOT NULL,
    revoked_at TIMESTAMPTZ,

    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    last_active_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_sessions_user_id ON sessions(user_id);
CREATE INDEX idx_sessions_refresh_token ON sessions(refresh_token);
CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);

-- ============================================================================
-- TABLA: notifications
-- Descripción: Notificaciones del sistema
-- ============================================================================
CREATE TYPE notification_type_enum AS ENUM (
    'system', 'trading', 'investment', 'education', 'payment', 'security', 'marketing'
);

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

    -- Contenido
    type notification_type_enum NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    data JSONB,

    -- Estado
    read_at TIMESTAMPTZ,

    -- Canales enviados
    sent_email BOOLEAN DEFAULT FALSE,
    sent_push BOOLEAN DEFAULT FALSE,
    sent_sms BOOLEAN DEFAULT FALSE,

    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_notifications_user_id ON notifications(user_id);
CREATE INDEX idx_notifications_read ON notifications(user_id, read_at);
CREATE INDEX idx_notifications_created ON notifications(created_at DESC);

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

CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_profiles_updated_at
    BEFORE UPDATE ON profiles
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_user_settings_updated_at
    BEFORE UPDATE ON user_settings
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_kyc_updated_at
    BEFORE UPDATE ON kyc_verifications
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_risk_profiles_updated_at
    BEFORE UPDATE ON risk_profiles
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();