-- ============================================================================
-- OrbiQuant IA - Esquema FINANCIAL
-- ============================================================================
-- Archivo: 05_financial_schema.sql
-- Descripción: Pagos, suscripciones, wallets y transacciones
-- Fecha: 2025-12-05
-- ============================================================================

SET search_path TO financial;

-- ============================================================================
-- TIPOS ENUMERADOS
-- ============================================================================

CREATE TYPE subscription_status_enum AS ENUM (
    'trialing',      -- Período de prueba
    'active',        -- Activa y pagando
    'past_due',      -- Pago vencido
    'cancelled',     -- Cancelada
    'unpaid',        -- Sin pago
    'paused'         -- Pausada
);

CREATE TYPE payment_status_enum AS ENUM (
    'pending',
    'processing',
    'succeeded',
    'failed',
    'refunded',
    'cancelled'
);

CREATE TYPE payment_method_enum AS ENUM (
    'card',
    'bank_transfer',
    'paypal',
    'crypto',
    'wallet_balance'
);

-- ============================================================================
-- TABLA: subscription_plans
-- Descripción: Planes de suscripción disponibles
-- ============================================================================
CREATE TABLE IF NOT EXISTS subscription_plans (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Identificación
    name VARCHAR(50) NOT NULL,
    slug VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,

    -- Pricing
    price_monthly DECIMAL(10,2) NOT NULL,
    price_yearly DECIMAL(10,2),
    currency CHAR(3) DEFAULT 'USD',

    -- Stripe
    stripe_price_id_monthly VARCHAR(100),
    stripe_price_id_yearly VARCHAR(100),
    stripe_product_id VARCHAR(100),

    -- Features (JSON array)
    features JSONB DEFAULT '[]', -- [{name, description, included: boolean}]

    -- Límites
    max_watchlists INT,
    max_alerts INT,
    ml_predictions_access BOOLEAN DEFAULT FALSE,
    signals_access BOOLEAN DEFAULT FALSE,
    backtesting_access BOOLEAN DEFAULT FALSE,
    api_access BOOLEAN DEFAULT FALSE,
    priority_support BOOLEAN DEFAULT FALSE,

    -- Educación
    courses_access VARCHAR(20) DEFAULT 'none', -- 'none', 'free_only', 'basic', 'all'

    -- Orden de display
    sort_order INT DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_plans_slug ON subscription_plans(slug);

-- ============================================================================
-- TABLA: stripe_customers
-- Descripción: Clientes de Stripe vinculados a usuarios
-- ============================================================================
CREATE TABLE IF NOT EXISTS stripe_customers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL UNIQUE REFERENCES auth.users(id) ON DELETE CASCADE,

    stripe_customer_id VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255),

    -- Método de pago por defecto
    default_payment_method_id VARCHAR(100),

    -- Metadata
    metadata JSONB,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_stripe_customers_user ON stripe_customers(user_id);
CREATE INDEX idx_stripe_customers_stripe ON stripe_customers(stripe_customer_id);

-- ============================================================================
-- TABLA: subscriptions
-- Descripción: Suscripciones activas de usuarios
-- ============================================================================
CREATE TABLE IF NOT EXISTS subscriptions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE RESTRICT,
    plan_id UUID NOT NULL REFERENCES subscription_plans(id),

    -- Stripe
    stripe_subscription_id VARCHAR(100) UNIQUE,
    stripe_customer_id VARCHAR(100) REFERENCES stripe_customers(stripe_customer_id),

    -- Estado
    status subscription_status_enum DEFAULT 'active',

    -- Período de facturación
    billing_cycle VARCHAR(20) DEFAULT 'monthly', -- 'monthly', 'yearly'
    current_period_start TIMESTAMPTZ,
    current_period_end TIMESTAMPTZ,

    -- Trial
    trial_start TIMESTAMPTZ,
    trial_end TIMESTAMPTZ,

    -- Cancelación
    cancel_at_period_end BOOLEAN DEFAULT FALSE,
    cancelled_at TIMESTAMPTZ,
    cancellation_reason TEXT,

    -- Precio actual (puede diferir del plan por descuentos)
    current_price DECIMAL(10,2),
    currency CHAR(3) DEFAULT 'USD',

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_subscriptions_user ON subscriptions(user_id);
CREATE INDEX idx_subscriptions_plan ON subscriptions(plan_id);
CREATE INDEX idx_subscriptions_status ON subscriptions(status);
CREATE INDEX idx_subscriptions_stripe ON subscriptions(stripe_subscription_id);

-- ============================================================================
-- TABLA: wallets
-- Descripción: Wallets internos de usuarios
-- ============================================================================
CREATE TABLE IF NOT EXISTS wallets (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE RESTRICT,

    -- Balance
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    balance DECIMAL(15,2) NOT NULL DEFAULT 0,
    available_balance DECIMAL(15,2) NOT NULL DEFAULT 0,
    pending_balance DECIMAL(15,2) NOT NULL DEFAULT 0,

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,

    -- Límites
    daily_withdrawal_limit DECIMAL(15,2) DEFAULT 10000,
    monthly_withdrawal_limit DECIMAL(15,2) DEFAULT 50000,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(user_id, currency)
);

CREATE INDEX idx_wallets_user ON wallets(user_id);

-- ============================================================================
-- TABLA: wallet_transactions
-- Descripción: Transacciones en wallets
-- ============================================================================
CREATE TABLE IF NOT EXISTS wallet_transactions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    wallet_id UUID NOT NULL REFERENCES wallets(id) ON DELETE RESTRICT,
    user_id UUID NOT NULL REFERENCES auth.users(id),

    -- Tipo y monto
    transaction_type public.transaction_type_enum NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    currency CHAR(3) DEFAULT 'USD',

    -- Balance
    balance_before DECIMAL(15,2) NOT NULL,
    balance_after DECIMAL(15,2) NOT NULL,

    -- Referencia
    reference_type VARCHAR(50), -- 'payment', 'subscription', 'investment_account', 'refund'
    reference_id UUID,
    external_reference VARCHAR(100), -- ID externo (Stripe, etc.)

    -- Descripción
    description TEXT,

    -- Estado
    status payment_status_enum DEFAULT 'succeeded',

    -- Metadata
    metadata JSONB,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_wallet_tx_wallet ON wallet_transactions(wallet_id);
CREATE INDEX idx_wallet_tx_user ON wallet_transactions(user_id);
CREATE INDEX idx_wallet_tx_type ON wallet_transactions(transaction_type);
CREATE INDEX idx_wallet_tx_created ON wallet_transactions(created_at DESC);

-- ============================================================================
-- TABLA: payments
-- Descripción: Pagos procesados
-- ============================================================================
CREATE TABLE IF NOT EXISTS payments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id),

    -- Tipo
    payment_type VARCHAR(50) NOT NULL, -- 'subscription', 'course', 'deposit', 'one_time'

    -- Monto
    amount DECIMAL(10,2) NOT NULL,
    currency CHAR(3) DEFAULT 'USD',
    fee DECIMAL(10,2) DEFAULT 0,
    net_amount DECIMAL(10,2),

    -- Método
    payment_method payment_method_enum,
    payment_method_details JSONB, -- Últimos 4 dígitos, tipo de tarjeta, etc.

    -- Stripe
    stripe_payment_intent_id VARCHAR(100),
    stripe_charge_id VARCHAR(100),
    stripe_invoice_id VARCHAR(100),

    -- Estado
    status payment_status_enum DEFAULT 'pending',
    failure_reason TEXT,

    -- Referencia
    reference_type VARCHAR(50), -- 'subscription', 'course', 'wallet_deposit'
    reference_id UUID,

    -- Descripción
    description TEXT,

    -- Metadata
    metadata JSONB,
    ip_address INET,

    -- Facturación
    invoice_url TEXT,
    receipt_url TEXT,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_payments_user ON payments(user_id);
CREATE INDEX idx_payments_status ON payments(status);
CREATE INDEX idx_payments_type ON payments(payment_type);
CREATE INDEX idx_payments_stripe ON payments(stripe_payment_intent_id);
CREATE INDEX idx_payments_created ON payments(created_at DESC);

-- ============================================================================
-- TABLA: refunds
-- Descripción: Reembolsos procesados
-- ============================================================================
CREATE TABLE IF NOT EXISTS refunds (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    payment_id UUID NOT NULL REFERENCES payments(id),
    user_id UUID NOT NULL REFERENCES auth.users(id),

    -- Monto
    amount DECIMAL(10,2) NOT NULL,
    currency CHAR(3) DEFAULT 'USD',

    -- Stripe
    stripe_refund_id VARCHAR(100),

    -- Razón
    reason VARCHAR(100),
    notes TEXT,

    -- Estado
    status payment_status_enum DEFAULT 'pending',

    -- Procesado por
    processed_by UUID REFERENCES auth.users(id),

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_refunds_payment ON refunds(payment_id);
CREATE INDEX idx_refunds_user ON refunds(user_id);

-- ============================================================================
-- TABLA: invoices
-- Descripción: Facturas generadas
-- ============================================================================
CREATE TABLE IF NOT EXISTS invoices (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id),

    -- Número de factura
    invoice_number VARCHAR(50) NOT NULL UNIQUE,

    -- Stripe
    stripe_invoice_id VARCHAR(100) UNIQUE,

    -- Montos
    subtotal DECIMAL(10,2) NOT NULL,
    tax DECIMAL(10,2) DEFAULT 0,
    total DECIMAL(10,2) NOT NULL,
    amount_paid DECIMAL(10,2) DEFAULT 0,
    amount_due DECIMAL(10,2),
    currency CHAR(3) DEFAULT 'USD',

    -- Estado
    status VARCHAR(20) DEFAULT 'draft', -- 'draft', 'open', 'paid', 'void', 'uncollectible'

    -- Fechas
    due_date DATE,
    paid_at TIMESTAMPTZ,

    -- Items
    line_items JSONB DEFAULT '[]', -- [{description, quantity, unit_price, amount}]

    -- PDFs
    pdf_url TEXT,
    hosted_invoice_url TEXT,

    -- Datos fiscales del cliente
    billing_details JSONB,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_invoices_user ON invoices(user_id);
CREATE INDEX idx_invoices_status ON invoices(status);
CREATE INDEX idx_invoices_number ON invoices(invoice_number);

-- ============================================================================
-- TABLA: payout_requests
-- Descripción: Solicitudes de retiro a cuenta bancaria/externa
-- ============================================================================
CREATE TABLE IF NOT EXISTS payout_requests (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id),
    wallet_id UUID NOT NULL REFERENCES wallets(id),

    -- Monto
    amount DECIMAL(15,2) NOT NULL,
    currency CHAR(3) DEFAULT 'USD',
    fee DECIMAL(10,2) DEFAULT 0,
    net_amount DECIMAL(15,2),

    -- Destino
    payout_method VARCHAR(50) NOT NULL, -- 'bank_transfer', 'paypal', 'crypto'
    destination_details JSONB, -- Cuenta bancaria, dirección, etc.

    -- Estado
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'processing', 'completed', 'rejected', 'cancelled'
    processed_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,

    -- Procesamiento
    processed_by UUID REFERENCES auth.users(id),
    rejection_reason TEXT,
    external_reference VARCHAR(100),

    -- Transacción de wallet
    wallet_transaction_id UUID REFERENCES wallet_transactions(id),

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_payouts_user ON payout_requests(user_id);
CREATE INDEX idx_payouts_wallet ON payout_requests(wallet_id);
CREATE INDEX idx_payouts_status ON payout_requests(status);

-- ============================================================================
-- TABLA: promo_codes
-- Descripción: Códigos promocionales y descuentos
-- ============================================================================
CREATE TABLE IF NOT EXISTS promo_codes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    code VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,

    -- Tipo de descuento
    discount_type VARCHAR(20) NOT NULL, -- 'percentage', 'fixed_amount'
    discount_value DECIMAL(10,2) NOT NULL,
    currency CHAR(3) DEFAULT 'USD',

    -- Aplicabilidad
    applies_to VARCHAR(50) DEFAULT 'all', -- 'all', 'subscription', 'course'
    applicable_plan_ids UUID[],
    applicable_course_ids UUID[],

    -- Límites
    max_uses INT,
    current_uses INT DEFAULT 0,
    max_uses_per_user INT DEFAULT 1,

    -- Validez
    valid_from TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    valid_until TIMESTAMPTZ,

    -- Requisitos
    min_purchase_amount DECIMAL(10,2),
    first_time_only BOOLEAN DEFAULT FALSE,

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_promo_codes_code ON promo_codes(code);
CREATE INDEX idx_promo_codes_active ON promo_codes(is_active) WHERE is_active = TRUE;

-- ============================================================================
-- TABLA: promo_code_uses
-- Descripción: Uso de códigos promocionales
-- ============================================================================
CREATE TABLE IF NOT EXISTS promo_code_uses (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    promo_code_id UUID NOT NULL REFERENCES promo_codes(id),
    user_id UUID NOT NULL REFERENCES auth.users(id),
    payment_id UUID REFERENCES payments(id),

    discount_applied DECIMAL(10,2) NOT NULL,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_promo_uses_code ON promo_code_uses(promo_code_id);
CREATE INDEX idx_promo_uses_user ON promo_code_uses(user_id);

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

CREATE TRIGGER update_plans_updated_at
    BEFORE UPDATE ON subscription_plans
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_stripe_customers_updated_at
    BEFORE UPDATE ON stripe_customers
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_subscriptions_updated_at
    BEFORE UPDATE ON subscriptions
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_wallets_updated_at
    BEFORE UPDATE ON wallets
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_payments_updated_at
    BEFORE UPDATE ON payments
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_invoices_updated_at
    BEFORE UPDATE ON invoices
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_payouts_updated_at
    BEFORE UPDATE ON payout_requests
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();