michangarrito/database/schemas/12-integrations.sql

-- =============================================================================
-- MICHANGARRITO - 12 INTEGRATIONS (Multi-tenant Credentials)
-- =============================================================================
-- Credenciales de integraciones externas por tenant
-- Soporta: WhatsApp, LLM (OpenAI, OpenRouter, Anthropic), Stripe, etc.
-- =============================================================================

-- Tipo de integración
CREATE TYPE integration_type AS ENUM (
    'whatsapp',
    'llm',
    'stripe',
    'mercadopago',
    'clip'
);

-- Proveedor de integración
CREATE TYPE integration_provider AS ENUM (
    -- WhatsApp
    'meta',
    -- LLM
    'openai',
    'openrouter',
    'anthropic',
    'ollama',
    'azure_openai',
    -- Payments
    'stripe',
    'mercadopago',
    'clip'
);

-- Tabla de credenciales de integración por tenant
CREATE TABLE IF NOT EXISTS public.tenant_integration_credentials (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Tipo y proveedor
    integration_type integration_type NOT NULL,
    provider integration_provider NOT NULL,

    -- Credenciales (almacenadas como JSON para flexibilidad)
    -- En producción considerar encriptación con pgcrypto
    credentials JSONB NOT NULL DEFAULT '{}',

    -- Configuración adicional
    config JSONB DEFAULT '{}',

    -- Estado
    is_active BOOLEAN DEFAULT true,
    is_verified BOOLEAN DEFAULT false,
    last_verified_at TIMESTAMPTZ,
    verification_error TEXT,

    -- Auditoría
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID,
    updated_by UUID,

    -- Constraint: un tenant solo puede tener una config por tipo+proveedor
    UNIQUE(tenant_id, integration_type, provider)
);

-- Índices para búsqueda rápida
CREATE INDEX idx_tenant_integrations_tenant ON public.tenant_integration_credentials(tenant_id);
CREATE INDEX idx_tenant_integrations_type ON public.tenant_integration_credentials(integration_type);
CREATE INDEX idx_tenant_integrations_active ON public.tenant_integration_credentials(tenant_id, is_active) WHERE is_active = true;

-- Trigger de updated_at
CREATE TRIGGER update_tenant_integration_credentials_updated_at
    BEFORE UPDATE ON public.tenant_integration_credentials
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Comentarios
COMMENT ON TABLE public.tenant_integration_credentials IS 'Credenciales de integraciones externas por tenant (WhatsApp, LLM, Pagos)';
COMMENT ON COLUMN public.tenant_integration_credentials.credentials IS 'JSON con credenciales: {access_token, api_key, phone_number_id, etc.}';
COMMENT ON COLUMN public.tenant_integration_credentials.config IS 'JSON con configuración: {model, temperature, max_tokens, etc.}';

-- =============================================================================
-- EJEMPLOS DE ESTRUCTURA DE CREDENTIALS Y CONFIG
-- =============================================================================
--
-- WhatsApp (Meta):
-- credentials: {
--   "access_token": "EAAxxxxx...",
--   "phone_number_id": "123456789",
--   "business_account_id": "987654321",
--   "verify_token": "my_verify_token"
-- }
-- config: {
--   "webhook_url": "https://michangarrito.com/webhook/whatsapp"
-- }
--
-- LLM (OpenAI):
-- credentials: {
--   "api_key": "sk-xxxxx..."
-- }
-- config: {
--   "model": "gpt-4o-mini",
--   "max_tokens": 1000,
--   "temperature": 0.7,
--   "system_prompt": "Eres un asistente de ventas..."
-- }
--
-- LLM (OpenRouter):
-- credentials: {
--   "api_key": "sk-or-xxxxx..."
-- }
-- config: {
--   "model": "anthropic/claude-3-haiku",
--   "max_tokens": 1000,
--   "base_url": "https://openrouter.ai/api/v1"
-- }
--
-- Stripe:
-- credentials: {
--   "secret_key": "sk_live_xxxxx",
--   "publishable_key": "pk_live_xxxxx",
--   "webhook_secret": "whsec_xxxxx"
-- }
-- =============================================================================

-- Agregar campos a tenants para configuración rápida de integración preferida
ALTER TABLE public.tenants
    ADD COLUMN IF NOT EXISTS preferred_llm_provider integration_provider DEFAULT 'openai',
    ADD COLUMN IF NOT EXISTS preferred_payment_provider integration_provider DEFAULT 'stripe';

-- Índice para buscar tenant por phone_number_id de WhatsApp (para webhooks)
CREATE TABLE IF NOT EXISTS public.tenant_whatsapp_numbers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    phone_number_id VARCHAR(50) NOT NULL UNIQUE,
    phone_number VARCHAR(20),
    display_name VARCHAR(100),
    is_platform_number BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id, phone_number_id)
);

CREATE INDEX idx_tenant_whatsapp_numbers_phone_id ON public.tenant_whatsapp_numbers(phone_number_id);

COMMENT ON TABLE public.tenant_whatsapp_numbers IS 'Mapeo de phone_number_id de WhatsApp a tenant (para resolver en webhooks)';