erp-core-database-v2/ddl/09-notifications.sql

-- =============================================================
-- ARCHIVO: 09-notifications.sql
-- DESCRIPCION: Sistema de notificaciones, templates, preferencias
-- VERSION: 1.0.0
-- PROYECTO: ERP-Core V2
-- FECHA: 2026-01-10
-- EPIC: SAAS-NOTIFICATIONS (EPIC-SAAS-003)
-- HISTORIAS: US-040, US-041, US-042
-- =============================================================

-- =====================
-- SCHEMA: notifications
-- =====================
CREATE SCHEMA IF NOT EXISTS notifications;

-- =====================
-- TABLA: notifications.channels
-- Canales de notificacion disponibles
-- =====================
CREATE TABLE IF NOT EXISTS notifications.channels (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Identificacion
    code VARCHAR(30) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description TEXT,

    -- Tipo
    channel_type VARCHAR(30) NOT NULL, -- email, sms, push, whatsapp, in_app, webhook

    -- Configuracion del proveedor
    provider VARCHAR(50), -- sendgrid, twilio, firebase, meta, custom
    provider_config JSONB DEFAULT '{}',

    -- Limites
    rate_limit_per_minute INTEGER DEFAULT 60,
    rate_limit_per_hour INTEGER DEFAULT 1000,
    rate_limit_per_day INTEGER DEFAULT 10000,

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,
    is_default BOOLEAN DEFAULT FALSE,

    -- Metadata
    metadata JSONB DEFAULT '{}',

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- =====================
-- TABLA: notifications.templates
-- Templates de notificaciones
-- =====================
CREATE TABLE IF NOT EXISTS notifications.templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES auth.tenants(id) ON DELETE CASCADE, -- NULL = global template

    -- Identificacion
    code VARCHAR(100) NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    category VARCHAR(50), -- system, marketing, transactional, alert

    -- Canal objetivo
    channel_type VARCHAR(30) NOT NULL, -- email, sms, push, whatsapp, in_app

    -- Contenido
    subject VARCHAR(500), -- Para email
    body_template TEXT NOT NULL,
    body_html TEXT, -- Para email HTML

    -- Variables disponibles
    available_variables JSONB DEFAULT '[]',
    -- Ejemplo: ["user_name", "company_name", "action_url"]

    -- Configuracion
    default_locale VARCHAR(10) DEFAULT 'es-MX',

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,
    is_system BOOLEAN DEFAULT FALSE, -- Templates del sistema no editables

    -- Versionamiento
    version INTEGER DEFAULT 1,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),
    updated_by UUID REFERENCES auth.users(id),

    UNIQUE(tenant_id, code, channel_type)
);

-- =====================
-- TABLA: notifications.template_translations
-- Traducciones de templates
-- =====================
CREATE TABLE IF NOT EXISTS notifications.template_translations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    template_id UUID NOT NULL REFERENCES notifications.templates(id) ON DELETE CASCADE,

    -- Idioma
    locale VARCHAR(10) NOT NULL, -- es-MX, en-US, etc.

    -- Contenido traducido
    subject VARCHAR(500),
    body_template TEXT NOT NULL,
    body_html TEXT,

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(template_id, locale)
);

-- =====================
-- TABLA: notifications.preferences
-- Preferencias de notificacion por usuario
-- =====================
CREATE TABLE IF NOT EXISTS notifications.preferences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Preferencias globales
    global_enabled BOOLEAN DEFAULT TRUE,
    quiet_hours_start TIME,
    quiet_hours_end TIME,
    timezone VARCHAR(50) DEFAULT 'America/Mexico_City',

    -- Preferencias por canal
    email_enabled BOOLEAN DEFAULT TRUE,
    sms_enabled BOOLEAN DEFAULT TRUE,
    push_enabled BOOLEAN DEFAULT TRUE,
    whatsapp_enabled BOOLEAN DEFAULT FALSE,
    in_app_enabled BOOLEAN DEFAULT TRUE,

    -- Preferencias por categoria
    category_preferences JSONB DEFAULT '{}',
    -- Ejemplo: {"marketing": false, "alerts": true, "reports": {"email": true, "push": false}}

    -- Frecuencia de digest
    digest_frequency VARCHAR(20) DEFAULT 'instant', -- instant, hourly, daily, weekly
    digest_day INTEGER, -- 0-6 para weekly
    digest_hour INTEGER DEFAULT 9, -- Hora del dia para daily/weekly

    -- Metadata
    metadata JSONB DEFAULT '{}',

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(user_id, tenant_id)
);

-- =====================
-- TABLA: notifications.notifications
-- Notificaciones enviadas/pendientes
-- =====================
CREATE TABLE IF NOT EXISTS notifications.notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Destinatario
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    recipient_email VARCHAR(255),
    recipient_phone VARCHAR(20),
    recipient_device_id UUID REFERENCES auth.devices(id),

    -- Template usado
    template_id UUID REFERENCES notifications.templates(id),
    template_code VARCHAR(100),

    -- Canal
    channel_type VARCHAR(30) NOT NULL,
    channel_id UUID REFERENCES notifications.channels(id),

    -- Contenido renderizado
    subject VARCHAR(500),
    body TEXT NOT NULL,
    body_html TEXT,

    -- Variables usadas
    variables JSONB DEFAULT '{}',

    -- Contexto
    context_type VARCHAR(50), -- sale, attendance, inventory, system
    context_id UUID,

    -- Prioridad
    priority VARCHAR(20) DEFAULT 'normal', -- low, normal, high, urgent

    -- Estado
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    -- pending, queued, sending, sent, delivered, read, failed, cancelled

    -- Tracking
    queued_at TIMESTAMPTZ,
    sent_at TIMESTAMPTZ,
    delivered_at TIMESTAMPTZ,
    read_at TIMESTAMPTZ,
    failed_at TIMESTAMPTZ,

    -- Errores
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,
    max_retries INTEGER DEFAULT 3,
    next_retry_at TIMESTAMPTZ,

    -- Proveedor
    provider_message_id VARCHAR(255),
    provider_response JSONB DEFAULT '{}',

    -- Metadata
    metadata JSONB DEFAULT '{}',

    -- Expiracion
    expires_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- =====================
-- TABLA: notifications.notification_batches
-- Lotes de notificaciones masivas
-- =====================
CREATE TABLE IF NOT EXISTS notifications.notification_batches (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Identificacion
    name VARCHAR(200) NOT NULL,
    description TEXT,

    -- Template
    template_id UUID REFERENCES notifications.templates(id),
    channel_type VARCHAR(30) NOT NULL,

    -- Audiencia
    audience_type VARCHAR(30) NOT NULL, -- all_users, segment, custom
    audience_filter JSONB DEFAULT '{}',

    -- Contenido
    variables JSONB DEFAULT '{}',

    -- Programacion
    scheduled_at TIMESTAMPTZ,

    -- Estado
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    -- draft, scheduled, processing, completed, failed, cancelled

    -- Estadisticas
    total_recipients INTEGER DEFAULT 0,
    sent_count INTEGER DEFAULT 0,
    delivered_count INTEGER DEFAULT 0,
    failed_count INTEGER DEFAULT 0,
    read_count INTEGER DEFAULT 0,

    -- Tiempos
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id)
);

-- =====================
-- TABLA: notifications.in_app_notifications
-- Notificaciones in-app (centro de notificaciones)
-- =====================
CREATE TABLE IF NOT EXISTS notifications.in_app_notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    -- Contenido
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    icon VARCHAR(50),
    color VARCHAR(20),

    -- Accion
    action_type VARCHAR(30), -- link, modal, function
    action_url TEXT,
    action_data JSONB DEFAULT '{}',

    -- Categoria
    category VARCHAR(50), -- info, success, warning, error, task

    -- Contexto
    context_type VARCHAR(50),
    context_id UUID,

    -- Estado
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMPTZ,
    is_archived BOOLEAN DEFAULT FALSE,
    archived_at TIMESTAMPTZ,

    -- Prioridad y expiracion
    priority VARCHAR(20) DEFAULT 'normal',
    expires_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- =====================
-- INDICES
-- =====================

-- Indices para channels
CREATE INDEX IF NOT EXISTS idx_channels_type ON notifications.channels(channel_type);
CREATE INDEX IF NOT EXISTS idx_channels_active ON notifications.channels(is_active) WHERE is_active = TRUE;

-- Indices para templates
CREATE INDEX IF NOT EXISTS idx_templates_tenant ON notifications.templates(tenant_id);
CREATE INDEX IF NOT EXISTS idx_templates_code ON notifications.templates(code);
CREATE INDEX IF NOT EXISTS idx_templates_channel ON notifications.templates(channel_type);
CREATE INDEX IF NOT EXISTS idx_templates_active ON notifications.templates(is_active) WHERE is_active = TRUE;

-- Indices para template_translations
CREATE INDEX IF NOT EXISTS idx_template_trans_template ON notifications.template_translations(template_id);
CREATE INDEX IF NOT EXISTS idx_template_trans_locale ON notifications.template_translations(locale);

-- Indices para preferences
CREATE INDEX IF NOT EXISTS idx_preferences_user ON notifications.preferences(user_id);
CREATE INDEX IF NOT EXISTS idx_preferences_tenant ON notifications.preferences(tenant_id);

-- Indices para notifications
CREATE INDEX IF NOT EXISTS idx_notifications_tenant ON notifications.notifications(tenant_id);
CREATE INDEX IF NOT EXISTS idx_notifications_user ON notifications.notifications(user_id);
CREATE INDEX IF NOT EXISTS idx_notifications_status ON notifications.notifications(status);
CREATE INDEX IF NOT EXISTS idx_notifications_channel ON notifications.notifications(channel_type);
CREATE INDEX IF NOT EXISTS idx_notifications_context ON notifications.notifications(context_type, context_id);
CREATE INDEX IF NOT EXISTS idx_notifications_pending ON notifications.notifications(status, next_retry_at)
    WHERE status IN ('pending', 'queued');
CREATE INDEX IF NOT EXISTS idx_notifications_created ON notifications.notifications(created_at DESC);

-- Indices para notification_batches
CREATE INDEX IF NOT EXISTS idx_batches_tenant ON notifications.notification_batches(tenant_id);
CREATE INDEX IF NOT EXISTS idx_batches_status ON notifications.notification_batches(status);
CREATE INDEX IF NOT EXISTS idx_batches_scheduled ON notifications.notification_batches(scheduled_at)
    WHERE status = 'scheduled';

-- Indices para in_app_notifications
CREATE INDEX IF NOT EXISTS idx_in_app_user ON notifications.in_app_notifications(user_id);
CREATE INDEX IF NOT EXISTS idx_in_app_tenant ON notifications.in_app_notifications(tenant_id);
CREATE INDEX IF NOT EXISTS idx_in_app_unread ON notifications.in_app_notifications(user_id, is_read)
    WHERE is_read = FALSE;
CREATE INDEX IF NOT EXISTS idx_in_app_created ON notifications.in_app_notifications(created_at DESC);

-- =====================
-- RLS POLICIES
-- =====================

-- Channels son globales (lectura publica, escritura admin)
ALTER TABLE notifications.channels ENABLE ROW LEVEL SECURITY;
CREATE POLICY public_read_channels ON notifications.channels
    FOR SELECT USING (true);

-- Templates: globales (tenant_id NULL) o por tenant
ALTER TABLE notifications.templates ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_or_global_templates ON notifications.templates
    FOR SELECT USING (
        tenant_id IS NULL
        OR tenant_id = current_setting('app.current_tenant_id', true)::uuid
    );

ALTER TABLE notifications.template_translations ENABLE ROW LEVEL SECURITY;
CREATE POLICY template_trans_access ON notifications.template_translations
    FOR SELECT USING (
        template_id IN (
            SELECT id FROM notifications.templates
            WHERE tenant_id IS NULL
            OR tenant_id = current_setting('app.current_tenant_id', true)::uuid
        )
    );

-- Preferences por tenant
ALTER TABLE notifications.preferences ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_preferences ON notifications.preferences
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- Notifications por tenant
ALTER TABLE notifications.notifications ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_notifications ON notifications.notifications
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- Batches por tenant
ALTER TABLE notifications.notification_batches ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_batches ON notifications.notification_batches
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- In-app notifications por tenant
ALTER TABLE notifications.in_app_notifications ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_in_app ON notifications.in_app_notifications
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- =====================
-- FUNCIONES
-- =====================

-- Funcion para obtener template con fallback a global
CREATE OR REPLACE FUNCTION notifications.get_template(
    p_tenant_id UUID,
    p_code VARCHAR(100),
    p_channel_type VARCHAR(30),
    p_locale VARCHAR(10) DEFAULT 'es-MX'
)
RETURNS TABLE (
    template_id UUID,
    subject VARCHAR(500),
    body_template TEXT,
    body_html TEXT,
    available_variables JSONB
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        t.id as template_id,
        COALESCE(tt.subject, t.subject) as subject,
        COALESCE(tt.body_template, t.body_template) as body_template,
        COALESCE(tt.body_html, t.body_html) as body_html,
        t.available_variables
    FROM notifications.templates t
    LEFT JOIN notifications.template_translations tt
        ON tt.template_id = t.id AND tt.locale = p_locale AND tt.is_active = TRUE
    WHERE t.code = p_code
    AND t.channel_type = p_channel_type
    AND t.is_active = TRUE
    AND (t.tenant_id = p_tenant_id OR t.tenant_id IS NULL)
    ORDER BY t.tenant_id NULLS LAST -- Priorizar template del tenant
    LIMIT 1;
END;
$$ LANGUAGE plpgsql STABLE;

-- Funcion para verificar preferencias de usuario
CREATE OR REPLACE FUNCTION notifications.should_send(
    p_user_id UUID,
    p_tenant_id UUID,
    p_channel_type VARCHAR(30),
    p_category VARCHAR(50) DEFAULT NULL
)
RETURNS BOOLEAN AS $$
DECLARE
    v_prefs RECORD;
    v_channel_enabled BOOLEAN;
    v_category_enabled BOOLEAN;
    v_in_quiet_hours BOOLEAN;
BEGIN
    -- Obtener preferencias
    SELECT * INTO v_prefs
    FROM notifications.preferences
    WHERE user_id = p_user_id AND tenant_id = p_tenant_id;

    -- Si no hay preferencias, permitir por defecto
    IF NOT FOUND THEN
        RETURN TRUE;
    END IF;

    -- Verificar si las notificaciones estan habilitadas globalmente
    IF NOT v_prefs.global_enabled THEN
        RETURN FALSE;
    END IF;

    -- Verificar canal especifico
    v_channel_enabled := CASE p_channel_type
        WHEN 'email' THEN v_prefs.email_enabled
        WHEN 'sms' THEN v_prefs.sms_enabled
        WHEN 'push' THEN v_prefs.push_enabled
        WHEN 'whatsapp' THEN v_prefs.whatsapp_enabled
        WHEN 'in_app' THEN v_prefs.in_app_enabled
        ELSE TRUE
    END;

    IF NOT v_channel_enabled THEN
        RETURN FALSE;
    END IF;

    -- Verificar categoria si se proporciona
    IF p_category IS NOT NULL AND v_prefs.category_preferences ? p_category THEN
        v_category_enabled := (v_prefs.category_preferences->>p_category)::boolean;
        IF NOT v_category_enabled THEN
            RETURN FALSE;
        END IF;
    END IF;

    -- Verificar horas de silencio
    IF v_prefs.quiet_hours_start IS NOT NULL AND v_prefs.quiet_hours_end IS NOT NULL THEN
        v_in_quiet_hours := CURRENT_TIME BETWEEN v_prefs.quiet_hours_start AND v_prefs.quiet_hours_end;
        IF v_in_quiet_hours AND p_channel_type IN ('push', 'sms') THEN
            RETURN FALSE;
        END IF;
    END IF;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql STABLE;

-- Funcion para encolar notificacion
CREATE OR REPLACE FUNCTION notifications.enqueue_notification(
    p_tenant_id UUID,
    p_user_id UUID,
    p_template_code VARCHAR(100),
    p_channel_type VARCHAR(30),
    p_variables JSONB DEFAULT '{}',
    p_context_type VARCHAR(50) DEFAULT NULL,
    p_context_id UUID DEFAULT NULL,
    p_priority VARCHAR(20) DEFAULT 'normal'
)
RETURNS UUID AS $$
DECLARE
    v_template RECORD;
    v_notification_id UUID;
    v_subject VARCHAR(500);
    v_body TEXT;
    v_body_html TEXT;
BEGIN
    -- Verificar preferencias
    IF NOT notifications.should_send(p_user_id, p_tenant_id, p_channel_type) THEN
        RETURN NULL;
    END IF;

    -- Obtener template
    SELECT * INTO v_template
    FROM notifications.get_template(p_tenant_id, p_template_code, p_channel_type);

    IF v_template.template_id IS NULL THEN
        RAISE EXCEPTION 'Template not found: %', p_template_code;
    END IF;

    -- TODO: Renderizar template con variables (se hara en el backend)
    v_subject := v_template.subject;
    v_body := v_template.body_template;
    v_body_html := v_template.body_html;

    -- Crear notificacion
    INSERT INTO notifications.notifications (
        tenant_id, user_id, template_id, template_code,
        channel_type, subject, body, body_html,
        variables, context_type, context_id, priority,
        status, queued_at
    ) VALUES (
        p_tenant_id, p_user_id, v_template.template_id, p_template_code,
        p_channel_type, v_subject, v_body, v_body_html,
        p_variables, p_context_type, p_context_id, p_priority,
        'queued', CURRENT_TIMESTAMP
    ) RETURNING id INTO v_notification_id;

    RETURN v_notification_id;
END;
$$ LANGUAGE plpgsql;

-- Funcion para marcar notificacion como leida
CREATE OR REPLACE FUNCTION notifications.mark_as_read(p_notification_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
    UPDATE notifications.in_app_notifications
    SET is_read = TRUE, read_at = CURRENT_TIMESTAMP
    WHERE id = p_notification_id AND is_read = FALSE;

    RETURN FOUND;
END;
$$ LANGUAGE plpgsql;

-- Funcion para obtener conteo de no leidas
CREATE OR REPLACE FUNCTION notifications.get_unread_count(p_user_id UUID, p_tenant_id UUID)
RETURNS INTEGER AS $$
BEGIN
    RETURN (
        SELECT COUNT(*)::INTEGER
        FROM notifications.in_app_notifications
        WHERE user_id = p_user_id
        AND tenant_id = p_tenant_id
        AND is_read = FALSE
        AND is_archived = FALSE
        AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP)
    );
END;
$$ LANGUAGE plpgsql STABLE;

-- Funcion para limpiar notificaciones antiguas
CREATE OR REPLACE FUNCTION notifications.cleanup_old_notifications(p_days INTEGER DEFAULT 90)
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    -- Eliminar notificaciones enviadas antiguas
    DELETE FROM notifications.notifications
    WHERE created_at < CURRENT_TIMESTAMP - (p_days || ' days')::INTERVAL
    AND status IN ('sent', 'delivered', 'read', 'failed', 'cancelled');

    GET DIAGNOSTICS deleted_count = ROW_COUNT;

    -- Eliminar in-app archivadas antiguas
    DELETE FROM notifications.in_app_notifications
    WHERE archived_at IS NOT NULL
    AND archived_at < CURRENT_TIMESTAMP - (p_days || ' days')::INTERVAL;

    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

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

-- Trigger para updated_at
CREATE OR REPLACE FUNCTION notifications.update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_channels_updated_at
    BEFORE UPDATE ON notifications.channels
    FOR EACH ROW EXECUTE FUNCTION notifications.update_timestamp();

CREATE TRIGGER trg_templates_updated_at
    BEFORE UPDATE ON notifications.templates
    FOR EACH ROW EXECUTE FUNCTION notifications.update_timestamp();

CREATE TRIGGER trg_preferences_updated_at
    BEFORE UPDATE ON notifications.preferences
    FOR EACH ROW EXECUTE FUNCTION notifications.update_timestamp();

CREATE TRIGGER trg_notifications_updated_at
    BEFORE UPDATE ON notifications.notifications
    FOR EACH ROW EXECUTE FUNCTION notifications.update_timestamp();

CREATE TRIGGER trg_batches_updated_at
    BEFORE UPDATE ON notifications.notification_batches
    FOR EACH ROW EXECUTE FUNCTION notifications.update_timestamp();

-- =====================
-- SEED DATA: Canales
-- =====================
INSERT INTO notifications.channels (code, name, channel_type, provider, is_active, is_default) VALUES
('email_sendgrid', 'Email (SendGrid)', 'email', 'sendgrid', TRUE, TRUE),
('email_smtp', 'Email (SMTP)', 'email', 'smtp', TRUE, FALSE),
('sms_twilio', 'SMS (Twilio)', 'sms', 'twilio', TRUE, TRUE),
('push_firebase', 'Push (Firebase)', 'push', 'firebase', TRUE, TRUE),
('whatsapp_meta', 'WhatsApp (Meta)', 'whatsapp', 'meta', FALSE, FALSE),
('in_app', 'In-App', 'in_app', 'internal', TRUE, TRUE)
ON CONFLICT (code) DO NOTHING;

-- =====================
-- SEED DATA: Templates del Sistema
-- =====================
INSERT INTO notifications.templates (code, name, channel_type, subject, body_template, category, is_system, available_variables) VALUES
-- Email templates
('welcome', 'Bienvenida', 'email', 'Bienvenido a {{company_name}}',
 'Hola {{user_name}},\n\nBienvenido a {{company_name}}. Tu cuenta ha sido creada exitosamente.\n\nPuedes acceder desde: {{login_url}}\n\nSaludos,\nEl equipo de {{company_name}}',
 'system', TRUE, '["user_name", "company_name", "login_url"]'),

('password_reset', 'Recuperar Contraseña', 'email', 'Recupera tu contraseña - {{company_name}}',
 'Hola {{user_name}},\n\nHemos recibido una solicitud para recuperar tu contraseña.\n\nHaz clic aquí para restablecerla: {{reset_url}}\n\nEste enlace expira en {{expiry_hours}} horas.\n\nSi no solicitaste esto, ignora este correo.',
 'system', TRUE, '["user_name", "reset_url", "expiry_hours", "company_name"]'),

('invitation', 'Invitación', 'email', 'Has sido invitado a {{company_name}}',
 'Hola,\n\n{{inviter_name}} te ha invitado a unirte a {{company_name}} con el rol de {{role_name}}.\n\nAcepta la invitación aquí: {{invitation_url}}\n\nEsta invitación expira el {{expiry_date}}.',
 'system', TRUE, '["inviter_name", "company_name", "role_name", "invitation_url", "expiry_date"]'),

('mfa_code', 'Código de Verificación', 'email', 'Tu código de verificación: {{code}}',
 'Tu código de verificación es: {{code}}\n\nEste código expira en {{expiry_minutes}} minutos.\n\nSi no solicitaste esto, cambia tu contraseña inmediatamente.',
 'system', TRUE, '["code", "expiry_minutes"]'),

-- Push templates
('attendance_reminder', 'Recordatorio de Asistencia', 'push', NULL,
 '{{user_name}}, no olvides registrar tu {{attendance_type}} de hoy.',
 'transactional', TRUE, '["user_name", "attendance_type"]'),

('low_stock_alert', 'Alerta de Stock Bajo', 'push', NULL,
 'Stock bajo: {{product_name}} tiene solo {{quantity}} unidades en {{branch_name}}.',
 'alert', TRUE, '["product_name", "quantity", "branch_name"]'),

-- In-app templates
('task_assigned', 'Tarea Asignada', 'in_app', NULL,
 '{{assigner_name}} te ha asignado una nueva tarea: {{task_title}}',
 'transactional', TRUE, '["assigner_name", "task_title"]'),

('payment_received', 'Pago Recibido', 'in_app', NULL,
 'Se ha recibido un pago de ${{amount}} {{currency}} de {{customer_name}}.',
 'transactional', TRUE, '["amount", "currency", "customer_name"]')

ON CONFLICT (tenant_id, code, channel_type) DO NOTHING;

-- =====================
-- COMENTARIOS
-- =====================
COMMENT ON TABLE notifications.channels IS 'Canales de notificacion disponibles (email, sms, push, etc.)';
COMMENT ON TABLE notifications.templates IS 'Templates de notificaciones con soporte multi-idioma';
COMMENT ON TABLE notifications.template_translations IS 'Traducciones de templates de notificaciones';
COMMENT ON TABLE notifications.preferences IS 'Preferencias de notificacion por usuario';
COMMENT ON TABLE notifications.notifications IS 'Cola y log de notificaciones';
COMMENT ON TABLE notifications.notification_batches IS 'Lotes de notificaciones masivas';
COMMENT ON TABLE notifications.in_app_notifications IS 'Notificaciones in-app para centro de notificaciones';

COMMENT ON FUNCTION notifications.get_template IS 'Obtiene template con fallback a template global';
COMMENT ON FUNCTION notifications.should_send IS 'Verifica si se debe enviar notificacion segun preferencias';
COMMENT ON FUNCTION notifications.enqueue_notification IS 'Encola una notificacion para envio';
COMMENT ON FUNCTION notifications.get_unread_count IS 'Obtiene conteo de notificaciones no leidas';