erp-core-database-v2/ddl/30-settings.sql

-- =============================================================
-- ARCHIVO: 30-settings.sql
-- DESCRIPCION: Sistema de Settings para configuraciones multi-nivel
-- VERSION: 1.0.0
-- PROYECTO: ERP-Core
-- FECHA: 2026-01-26
-- MODULO: MGN-006 (Settings)
-- RF: RF-SETTINGS-001, RF-SETTINGS-002, RF-SETTINGS-003
-- NOTA: Feature Flags ya existen en 11-feature-flags.sql (schema flags)
-- =============================================================

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

-- =====================
-- TABLA: core_settings.system_settings
-- Configuraciones globales del sistema (RF-SETTINGS-001)
-- =====================
CREATE TABLE IF NOT EXISTS core_settings.system_settings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Identificacion
    key VARCHAR(100) UNIQUE NOT NULL,
    value JSONB NOT NULL,

    -- Metadata
    data_type VARCHAR(20) NOT NULL DEFAULT 'string'
        CHECK (data_type IN ('string', 'number', 'boolean', 'json', 'array', 'secret')),
    category VARCHAR(50) NOT NULL,
    description TEXT,

    -- Control
    is_public BOOLEAN NOT NULL DEFAULT false,
    is_editable BOOLEAN NOT NULL DEFAULT true,
    default_value JSONB,
    validation_rules JSONB DEFAULT '{}',

    -- Audit
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by UUID REFERENCES auth.users(id)
);

-- Indices para system_settings
CREATE INDEX IF NOT EXISTS idx_system_settings_category
    ON core_settings.system_settings(category);
CREATE INDEX IF NOT EXISTS idx_system_settings_public
    ON core_settings.system_settings(is_public) WHERE is_public = true;
CREATE INDEX IF NOT EXISTS idx_system_settings_editable
    ON core_settings.system_settings(is_editable) WHERE is_editable = true;

-- =====================
-- TABLA: core_settings.plan_settings
-- Configuraciones por defecto por plan de suscripcion (RF-SETTINGS-002)
-- =====================
CREATE TABLE IF NOT EXISTS core_settings.plan_settings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Relaciones
    plan_id UUID NOT NULL REFERENCES billing.subscription_plans(id) ON DELETE CASCADE,
    key VARCHAR(100) NOT NULL,
    value JSONB NOT NULL,

    -- Audit
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(plan_id, key)
);

-- Indices para plan_settings
CREATE INDEX IF NOT EXISTS idx_plan_settings_plan
    ON core_settings.plan_settings(plan_id);

-- =====================
-- TABLA: core_settings.tenant_settings
-- Configuraciones personalizadas por tenant (RF-SETTINGS-002)
-- =====================
CREATE TABLE IF NOT EXISTS core_settings.tenant_settings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Relaciones
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    key VARCHAR(100) NOT NULL,
    value JSONB NOT NULL,

    -- Herencia
    inherited_from VARCHAR(20) NOT NULL DEFAULT 'custom'
        CHECK (inherited_from IN ('system', 'plan', 'custom')),
    is_overridden BOOLEAN NOT NULL DEFAULT true,

    -- Audit
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(tenant_id, key)
);

-- Indices para tenant_settings
CREATE INDEX IF NOT EXISTS idx_tenant_settings_tenant
    ON core_settings.tenant_settings(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_settings_key
    ON core_settings.tenant_settings(key);

-- RLS para tenant_settings
ALTER TABLE core_settings.tenant_settings ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS tenant_isolation_settings ON core_settings.tenant_settings;
CREATE POLICY tenant_isolation_settings ON core_settings.tenant_settings
    FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- =====================
-- TABLA: core_settings.user_preferences
-- Preferencias personales de usuario (RF-SETTINGS-003)
-- =====================
CREATE TABLE IF NOT EXISTS core_settings.user_preferences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Relaciones
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    key VARCHAR(100) NOT NULL,
    value JSONB NOT NULL,

    -- Sync
    synced_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Audit
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(user_id, key)
);

-- Indices para user_preferences
CREATE INDEX IF NOT EXISTS idx_user_preferences_user
    ON core_settings.user_preferences(user_id);
CREATE INDEX IF NOT EXISTS idx_user_preferences_key
    ON core_settings.user_preferences(key);

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

-- Funcion para obtener setting efectivo del tenant (herencia system -> plan -> tenant)
CREATE OR REPLACE FUNCTION core_settings.get_tenant_setting(
    p_tenant_id UUID,
    p_key VARCHAR
) RETURNS JSONB AS $$
DECLARE
    v_value JSONB;
    v_plan_id UUID;
BEGIN
    -- 1. Buscar en tenant_settings (override)
    SELECT value INTO v_value
    FROM core_settings.tenant_settings
    WHERE tenant_id = p_tenant_id
    AND key = p_key
    AND is_overridden = true;

    IF v_value IS NOT NULL THEN
        RETURN v_value;
    END IF;

    -- 2. Buscar en plan_settings
    SELECT t.plan_id INTO v_plan_id
    FROM auth.tenants t WHERE t.id = p_tenant_id;

    IF v_plan_id IS NOT NULL THEN
        SELECT value INTO v_value
        FROM core_settings.plan_settings
        WHERE plan_id = v_plan_id AND key = p_key;

        IF v_value IS NOT NULL THEN
            RETURN v_value;
        END IF;
    END IF;

    -- 3. Retornar system default
    SELECT COALESCE(value, default_value) INTO v_value
    FROM core_settings.system_settings
    WHERE key = p_key;

    RETURN v_value;
END;
$$ LANGUAGE plpgsql STABLE;

-- Funcion para obtener todas las preferencias de un usuario
CREATE OR REPLACE FUNCTION core_settings.get_user_preferences(p_user_id UUID)
RETURNS TABLE (
    key VARCHAR(100),
    value JSONB,
    synced_at TIMESTAMPTZ
) AS $$
BEGIN
    RETURN QUERY
    SELECT up.key, up.value, up.synced_at
    FROM core_settings.user_preferences up
    WHERE up.user_id = p_user_id
    ORDER BY up.key;
END;
$$ LANGUAGE plpgsql STABLE;

-- Funcion para obtener todos los settings de un tenant (merged)
CREATE OR REPLACE FUNCTION core_settings.get_all_tenant_settings(p_tenant_id UUID)
RETURNS TABLE (
    key VARCHAR(100),
    value JSONB,
    source VARCHAR(20)
) AS $$
BEGIN
    RETURN QUERY
    -- System settings publicos
    SELECT ss.key, COALESCE(ss.value, ss.default_value), 'system'::VARCHAR(20)
    FROM core_settings.system_settings ss
    WHERE ss.is_public = true
    AND NOT EXISTS (
        SELECT 1 FROM core_settings.tenant_settings ts
        WHERE ts.tenant_id = p_tenant_id AND ts.key = ss.key AND ts.is_overridden = true
    )
    UNION ALL
    -- Tenant overrides
    SELECT ts.key, ts.value, ts.inherited_from
    FROM core_settings.tenant_settings ts
    WHERE ts.tenant_id = p_tenant_id AND ts.is_overridden = true
    ORDER BY key;
END;
$$ LANGUAGE plpgsql STABLE;

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

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

CREATE TRIGGER trg_system_settings_updated_at
    BEFORE UPDATE ON core_settings.system_settings
    FOR EACH ROW
    EXECUTE FUNCTION core_settings.update_timestamp();

CREATE TRIGGER trg_plan_settings_updated_at
    BEFORE UPDATE ON core_settings.plan_settings
    FOR EACH ROW
    EXECUTE FUNCTION core_settings.update_timestamp();

CREATE TRIGGER trg_tenant_settings_updated_at
    BEFORE UPDATE ON core_settings.tenant_settings
    FOR EACH ROW
    EXECUTE FUNCTION core_settings.update_timestamp();

CREATE TRIGGER trg_user_preferences_updated_at
    BEFORE UPDATE ON core_settings.user_preferences
    FOR EACH ROW
    EXECUTE FUNCTION core_settings.update_timestamp();

-- =====================
-- SEED DATA: System Settings Base
-- =====================
INSERT INTO core_settings.system_settings (key, value, data_type, category, description, is_public, is_editable, default_value) VALUES
-- Security
('security.max_login_attempts', '5', 'number', 'security', 'Intentos maximos de login antes de bloqueo', true, true, '5'),
('security.lockout_duration_minutes', '15', 'number', 'security', 'Duracion de bloqueo en minutos', true, true, '15'),
('security.session_timeout_hours', '24', 'number', 'security', 'Timeout de sesion en horas', true, true, '24'),
('security.password_min_length', '8', 'number', 'security', 'Longitud minima de password', true, true, '8'),
('security.require_mfa', 'false', 'boolean', 'security', 'Requerir MFA para todos los usuarios', true, true, 'false'),
('security.allowed_ip_ranges', '[]', 'array', 'security', 'Rangos IP permitidos (vacio = todos)', false, true, '[]'),

-- Email
('email.smtp_host', '""', 'string', 'email', 'Host SMTP para envio de emails', false, true, '""'),
('email.smtp_port', '587', 'number', 'email', 'Puerto SMTP', false, true, '587'),
('email.smtp_secure', 'true', 'boolean', 'email', 'Usar TLS para conexion SMTP', false, true, 'true'),
('email.from_address', '"noreply@erp-core.local"', 'string', 'email', 'Email de origen por defecto', true, true, '"noreply@erp-core.local"'),
('email.from_name', '"ERP Core"', 'string', 'email', 'Nombre de remitente por defecto', true, true, '"ERP Core"'),

-- Storage
('storage.max_file_size_mb', '10', 'number', 'storage', 'Tamano maximo de archivo en MB', true, true, '10'),
('storage.allowed_extensions', '["pdf","jpg","jpeg","png","xlsx","docx","csv"]', 'array', 'storage', 'Extensiones de archivo permitidas', true, true, '["pdf","jpg","jpeg","png","xlsx","docx","csv"]'),
('storage.provider', '"local"', 'string', 'storage', 'Proveedor de almacenamiento (local, s3, r2)', false, true, '"local"'),

-- Performance
('performance.cache_ttl_seconds', '300', 'number', 'performance', 'TTL de cache en segundos', false, true, '300'),
('performance.pagination_default_limit', '20', 'number', 'performance', 'Items por pagina por defecto', true, true, '20'),
('performance.pagination_max_limit', '100', 'number', 'performance', 'Maximo items por pagina', true, false, '100'),

-- Localization
('localization.default_timezone', '"America/Mexico_City"', 'string', 'localization', 'Timezone por defecto', true, true, '"America/Mexico_City"'),
('localization.default_locale', '"es-MX"', 'string', 'localization', 'Locale por defecto', true, true, '"es-MX"'),
('localization.default_currency', '"MXN"', 'string', 'localization', 'Moneda por defecto', true, true, '"MXN"'),
('localization.date_format', '"DD/MM/YYYY"', 'string', 'localization', 'Formato de fecha', true, true, '"DD/MM/YYYY"'),

-- Business
('business.fiscal_year_start_month', '1', 'number', 'business', 'Mes de inicio del ano fiscal', true, true, '1'),
('business.default_tax_rate', '16', 'number', 'business', 'Tasa de IVA por defecto', true, true, '16'),
('business.invoice_prefix', '"INV-"', 'string', 'business', 'Prefijo de facturas', true, true, '"INV-"'),
('business.require_approval_amount', '10000', 'number', 'business', 'Monto que requiere aprobacion', true, true, '10000')

ON CONFLICT (key) DO UPDATE SET
    value = EXCLUDED.value,
    updated_at = CURRENT_TIMESTAMP;

-- =====================
-- COMENTARIOS
-- =====================
COMMENT ON SCHEMA core_settings IS 'Schema para configuraciones del sistema multi-nivel';
COMMENT ON TABLE core_settings.system_settings IS 'Configuraciones globales del sistema';
COMMENT ON TABLE core_settings.plan_settings IS 'Configuraciones por defecto por plan de suscripcion';
COMMENT ON TABLE core_settings.tenant_settings IS 'Configuraciones personalizadas por tenant';
COMMENT ON TABLE core_settings.user_preferences IS 'Preferencias personales de usuario';
COMMENT ON FUNCTION core_settings.get_tenant_setting IS 'Obtiene setting efectivo con herencia system->plan->tenant';
COMMENT ON FUNCTION core_settings.get_user_preferences IS 'Obtiene todas las preferencias de un usuario';
COMMENT ON FUNCTION core_settings.get_all_tenant_settings IS 'Obtiene todos los settings de un tenant merged con sistema';