erp-core-database-v2/ddl/29-cfdi-rls-functions.sql

-- =============================================================
-- ARCHIVO: 29-cfdi-rls-functions.sql
-- DESCRIPCION: Modulo CFDI - RLS policies, funciones auxiliares,
--              triggers y vistas
-- VERSION: 1.0.0
-- PROYECTO: ERP-Core V2
-- FECHA: 2026-02-03
-- DEPENDE DE: 27-cfdi-core.sql, 28-cfdi-operations.sql
-- =============================================================

-- =====================
-- ROW LEVEL SECURITY (RLS)
-- =====================

-- cfdi_certificates
ALTER TABLE fiscal.cfdi_certificates ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation_cfdi_certificates ON fiscal.cfdi_certificates;
CREATE POLICY tenant_isolation_cfdi_certificates ON fiscal.cfdi_certificates
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- cfdi_pac_configurations
ALTER TABLE fiscal.cfdi_pac_configurations ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation_cfdi_pac ON fiscal.cfdi_pac_configurations;
CREATE POLICY tenant_isolation_cfdi_pac ON fiscal.cfdi_pac_configurations
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- cfdi_invoices
ALTER TABLE fiscal.cfdi_invoices ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation_cfdi_invoices ON fiscal.cfdi_invoices;
CREATE POLICY tenant_isolation_cfdi_invoices ON fiscal.cfdi_invoices
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- cfdi_cancellation_requests
ALTER TABLE fiscal.cfdi_cancellation_requests ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation_cfdi_cancellations ON fiscal.cfdi_cancellation_requests;
CREATE POLICY tenant_isolation_cfdi_cancellations ON fiscal.cfdi_cancellation_requests
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- cfdi_operation_logs
ALTER TABLE fiscal.cfdi_operation_logs ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation_cfdi_logs ON fiscal.cfdi_operation_logs;
CREATE POLICY tenant_isolation_cfdi_logs ON fiscal.cfdi_operation_logs
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- cfdi_payment_complements
ALTER TABLE fiscal.cfdi_payment_complements ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation_cfdi_payment_comp ON fiscal.cfdi_payment_complements;
CREATE POLICY tenant_isolation_cfdi_payment_comp ON fiscal.cfdi_payment_complements
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- cfdi_stamp_queue
ALTER TABLE fiscal.cfdi_stamp_queue ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation_cfdi_queue ON fiscal.cfdi_stamp_queue;
CREATE POLICY tenant_isolation_cfdi_queue ON fiscal.cfdi_stamp_queue
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- =====================
-- COMENTARIOS RLS
-- =====================
COMMENT ON POLICY tenant_isolation_cfdi_certificates ON fiscal.cfdi_certificates IS 'Aislamiento multi-tenant para certificados CSD';
COMMENT ON POLICY tenant_isolation_cfdi_invoices ON fiscal.cfdi_invoices IS 'Aislamiento multi-tenant para CFDIs';
COMMENT ON POLICY tenant_isolation_cfdi_cancellations ON fiscal.cfdi_cancellation_requests IS 'Aislamiento multi-tenant para cancelaciones';
COMMENT ON POLICY tenant_isolation_cfdi_logs ON fiscal.cfdi_operation_logs IS 'Aislamiento multi-tenant para logs de operaciones';
COMMENT ON POLICY tenant_isolation_cfdi_payment_comp ON fiscal.cfdi_payment_complements IS 'Aislamiento multi-tenant para complementos de pago';

-- =====================
-- FUNCIONES AUXILIARES
-- =====================

-- Funcion: Obtener certificado activo por defecto del tenant
CREATE OR REPLACE FUNCTION fiscal.get_default_certificate(p_tenant_id UUID)
RETURNS UUID AS $$
DECLARE
    v_certificate_id UUID;
BEGIN
    SELECT id INTO v_certificate_id
    FROM fiscal.cfdi_certificates
    WHERE tenant_id = p_tenant_id
      AND is_default = TRUE
      AND status = 'active'
      AND deleted_at IS NULL
      AND expires_at > CURRENT_TIMESTAMP
    LIMIT 1;

    RETURN v_certificate_id;
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION fiscal.get_default_certificate IS 'Obtiene el ID del certificado CSD activo y vigente por defecto del tenant';

-- Funcion: Obtener configuracion PAC activa del tenant
CREATE OR REPLACE FUNCTION fiscal.get_active_pac(p_tenant_id UUID)
RETURNS TABLE (
    pac_id UUID,
    pac_code VARCHAR,
    environment VARCHAR,
    api_url VARCHAR
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        pc.id,
        pc.pac_code,
        pc.environment,
        CASE
            WHEN pc.environment = 'production' THEN pc.production_url
            ELSE pc.sandbox_url
        END as api_url
    FROM fiscal.cfdi_pac_configurations pc
    WHERE pc.tenant_id = p_tenant_id
      AND pc.is_active = TRUE
      AND pc.is_default = TRUE
      AND pc.deleted_at IS NULL
    LIMIT 1;
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION fiscal.get_active_pac IS 'Obtiene la configuracion del PAC activo del tenant';

-- Funcion: Validar RFC mexicano
CREATE OR REPLACE FUNCTION fiscal.validate_rfc(p_rfc VARCHAR)
RETURNS BOOLEAN AS $$
DECLARE
    v_pattern_fisica VARCHAR := '^[A-Z&N]{4}[0-9]{6}[A-Z0-9]{3}$';
    v_pattern_moral VARCHAR := '^[A-Z&N]{3}[0-9]{6}[A-Z0-9]{3}$';
    v_rfc VARCHAR;
BEGIN
    -- Normalizar RFC (mayusculas, sin espacios)
    v_rfc := UPPER(TRIM(p_rfc));

    -- RFC generico publico en general
    IF v_rfc = 'XAXX010101000' THEN
        RETURN TRUE;
    END IF;

    -- RFC generico extranjero
    IF v_rfc = 'XEXX010101000' THEN
        RETURN TRUE;
    END IF;

    -- Validar patron persona fisica (13 caracteres)
    IF LENGTH(v_rfc) = 13 AND v_rfc ~ v_pattern_fisica THEN
        RETURN TRUE;
    END IF;

    -- Validar patron persona moral (12 caracteres)
    IF LENGTH(v_rfc) = 12 AND v_rfc ~ v_pattern_moral THEN
        RETURN TRUE;
    END IF;

    RETURN FALSE;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

COMMENT ON FUNCTION fiscal.validate_rfc IS 'Valida el formato de un RFC mexicano (persona fisica o moral)';

-- Funcion: Generar folio CFDI
CREATE OR REPLACE FUNCTION fiscal.generate_cfdi_folio(
    p_tenant_id UUID,
    p_serie VARCHAR DEFAULT NULL
)
RETURNS VARCHAR AS $$
DECLARE
    v_next_folio INTEGER;
    v_serie VARCHAR;
BEGIN
    -- Serie por defecto si no se proporciona
    v_serie := COALESCE(p_serie, 'A');

    -- Obtener siguiente folio
    SELECT COALESCE(MAX(folio::INTEGER), 0) + 1
    INTO v_next_folio
    FROM fiscal.cfdi_invoices
    WHERE tenant_id = p_tenant_id
      AND serie = v_serie
      AND folio ~ '^\d+$'; -- Solo folios numericos

    RETURN v_next_folio::VARCHAR;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION fiscal.generate_cfdi_folio IS 'Genera el siguiente folio para una serie de CFDI';

-- Funcion: Verificar si CFDI puede ser cancelado
CREATE OR REPLACE FUNCTION fiscal.can_cancel_cfdi(p_cfdi_id UUID)
RETURNS TABLE (
    can_cancel BOOLEAN,
    reason VARCHAR,
    requires_acceptance BOOLEAN
) AS $$
DECLARE
    v_cfdi RECORD;
    v_has_payments BOOLEAN;
    v_is_month_old BOOLEAN;
BEGIN
    -- Obtener datos del CFDI
    SELECT ci.*, bi.total, bi.amount_paid
    INTO v_cfdi
    FROM fiscal.cfdi_invoices ci
    JOIN billing.invoices bi ON ci.invoice_id = bi.id
    WHERE ci.id = p_cfdi_id;

    IF NOT FOUND THEN
        RETURN QUERY SELECT FALSE, 'CFDI no encontrado'::VARCHAR, FALSE;
        RETURN;
    END IF;

    -- Verificar estado
    IF v_cfdi.status != 'stamped' THEN
        RETURN QUERY SELECT FALSE, 'CFDI no esta timbrado'::VARCHAR, FALSE;
        RETURN;
    END IF;

    -- Verificar si ya tiene solicitud de cancelacion
    IF EXISTS (
        SELECT 1 FROM fiscal.cfdi_cancellation_requests
        WHERE cfdi_invoice_id = p_cfdi_id
          AND status NOT IN ('rejected', 'error')
    ) THEN
        RETURN QUERY SELECT FALSE, 'Ya existe solicitud de cancelacion'::VARCHAR, FALSE;
        RETURN;
    END IF;

    -- Verificar si tiene pagos aplicados
    v_has_payments := COALESCE(v_cfdi.amount_paid, 0) > 0;

    -- Verificar si tiene mas de un mes
    v_is_month_old := v_cfdi.stamp_date < (CURRENT_DATE - INTERVAL '1 month');

    -- Determinar si requiere aceptacion del receptor
    -- Segun reglas SAT 2022+
    IF v_cfdi.total > 5000 OR v_is_month_old OR v_has_payments THEN
        RETURN QUERY SELECT TRUE, 'Puede cancelar con aceptacion del receptor'::VARCHAR, TRUE;
    ELSE
        RETURN QUERY SELECT TRUE, 'Puede cancelar sin aceptacion'::VARCHAR, FALSE;
    END IF;
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION fiscal.can_cancel_cfdi IS 'Verifica si un CFDI puede ser cancelado y si requiere aceptacion del receptor';

-- Funcion: Calcular saldo pendiente de una factura con pagos parciales
CREATE OR REPLACE FUNCTION fiscal.get_invoice_payment_balance(p_cfdi_uuid VARCHAR)
RETURNS TABLE (
    total_amount DECIMAL,
    total_paid DECIMAL,
    remaining_balance DECIMAL,
    installment_count INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        ci.total,
        COALESCE(SUM(pcd.amount_paid), 0) as total_paid,
        ci.total - COALESCE(SUM(pcd.amount_paid), 0) as remaining_balance,
        COUNT(pcd.id)::INTEGER as installment_count
    FROM fiscal.cfdi_invoices ci
    LEFT JOIN fiscal.cfdi_payment_complement_documents pcd
        ON pcd.related_uuid = ci.uuid
    LEFT JOIN fiscal.cfdi_payment_complements pc
        ON pc.id = pcd.payment_complement_id
        AND pc.status = 'stamped'
    WHERE ci.uuid = p_cfdi_uuid
    GROUP BY ci.id, ci.total;
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION fiscal.get_invoice_payment_balance IS 'Obtiene el saldo pendiente de una factura considerando complementos de pago';

-- Funcion: Obtener resumen de CFDIs por periodo
CREATE OR REPLACE FUNCTION fiscal.get_cfdi_summary(
    p_tenant_id UUID,
    p_start_date DATE,
    p_end_date DATE
)
RETURNS TABLE (
    voucher_type fiscal.cfdi_voucher_type,
    status fiscal.cfdi_status,
    count BIGINT,
    total_amount DECIMAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        ci.voucher_type,
        ci.status,
        COUNT(*) as count,
        SUM(ci.total) as total_amount
    FROM fiscal.cfdi_invoices ci
    WHERE ci.tenant_id = p_tenant_id
      AND ci.created_at::DATE BETWEEN p_start_date AND p_end_date
      AND ci.deleted_at IS NULL
    GROUP BY ci.voucher_type, ci.status
    ORDER BY ci.voucher_type, ci.status;
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION fiscal.get_cfdi_summary IS 'Obtiene resumen de CFDIs agrupados por tipo y estado en un periodo';

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

-- Trigger: Actualizar updated_at en cfdi_invoices
CREATE OR REPLACE FUNCTION fiscal.update_cfdi_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_cfdi_invoices_timestamp ON fiscal.cfdi_invoices;
CREATE TRIGGER trg_cfdi_invoices_timestamp
    BEFORE UPDATE ON fiscal.cfdi_invoices
    FOR EACH ROW EXECUTE FUNCTION fiscal.update_cfdi_timestamp();

DROP TRIGGER IF EXISTS trg_cfdi_certificates_timestamp ON fiscal.cfdi_certificates;
CREATE TRIGGER trg_cfdi_certificates_timestamp
    BEFORE UPDATE ON fiscal.cfdi_certificates
    FOR EACH ROW EXECUTE FUNCTION fiscal.update_cfdi_timestamp();

DROP TRIGGER IF EXISTS trg_cfdi_pac_timestamp ON fiscal.cfdi_pac_configurations;
CREATE TRIGGER trg_cfdi_pac_timestamp
    BEFORE UPDATE ON fiscal.cfdi_pac_configurations
    FOR EACH ROW EXECUTE FUNCTION fiscal.update_cfdi_timestamp();

DROP TRIGGER IF EXISTS trg_cfdi_pcomp_timestamp ON fiscal.cfdi_payment_complements;
CREATE TRIGGER trg_cfdi_pcomp_timestamp
    BEFORE UPDATE ON fiscal.cfdi_payment_complements
    FOR EACH ROW EXECUTE FUNCTION fiscal.update_cfdi_timestamp();

DROP TRIGGER IF EXISTS trg_cfdi_cancel_timestamp ON fiscal.cfdi_cancellation_requests;
CREATE TRIGGER trg_cfdi_cancel_timestamp
    BEFORE UPDATE ON fiscal.cfdi_cancellation_requests
    FOR EACH ROW EXECUTE FUNCTION fiscal.update_cfdi_timestamp();

-- Trigger: Log automatico de cambios de estado
CREATE OR REPLACE FUNCTION fiscal.log_cfdi_status_change()
RETURNS TRIGGER AS $$
BEGIN
    -- Solo si cambio el status
    IF OLD.status IS DISTINCT FROM NEW.status THEN
        INSERT INTO fiscal.cfdi_operation_logs (
            tenant_id,
            cfdi_invoice_id,
            cfdi_uuid,
            operation_type,
            status_before,
            status_after,
            success,
            created_by
        ) VALUES (
            NEW.tenant_id,
            NEW.id,
            NEW.uuid,
            CASE
                WHEN NEW.status = 'stamped' THEN 'stamp'::fiscal.cfdi_operation_type
                WHEN NEW.status = 'cancelled' THEN 'cancel_complete'::fiscal.cfdi_operation_type
                WHEN NEW.status = 'error' THEN 'error'::fiscal.cfdi_operation_type
                ELSE 'create'::fiscal.cfdi_operation_type
            END,
            OLD.status::VARCHAR,
            NEW.status::VARCHAR,
            NEW.status NOT IN ('error'),
            NEW.updated_by
        );
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_cfdi_status_log ON fiscal.cfdi_invoices;
CREATE TRIGGER trg_cfdi_status_log
    AFTER UPDATE ON fiscal.cfdi_invoices
    FOR EACH ROW EXECUTE FUNCTION fiscal.log_cfdi_status_change();

-- Trigger: Validar RFC antes de insertar CFDI
CREATE OR REPLACE FUNCTION fiscal.validate_cfdi_rfc()
RETURNS TRIGGER AS $$
BEGIN
    -- Validar RFC emisor
    IF NOT fiscal.validate_rfc(NEW.issuer_rfc) THEN
        RAISE EXCEPTION 'RFC del emisor invalido: %', NEW.issuer_rfc;
    END IF;

    -- Validar RFC receptor
    IF NOT fiscal.validate_rfc(NEW.receiver_rfc) THEN
        RAISE EXCEPTION 'RFC del receptor invalido: %', NEW.receiver_rfc;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_cfdi_validate_rfc ON fiscal.cfdi_invoices;
CREATE TRIGGER trg_cfdi_validate_rfc
    BEFORE INSERT OR UPDATE ON fiscal.cfdi_invoices
    FOR EACH ROW EXECUTE FUNCTION fiscal.validate_cfdi_rfc();

-- =====================
-- VISTAS
-- =====================

-- Vista: CFDIs pendientes de timbrar
CREATE OR REPLACE VIEW fiscal.v_cfdi_pending_stamp AS
SELECT
    ci.id,
    ci.tenant_id,
    ci.invoice_id,
    ci.serie,
    ci.folio,
    ci.voucher_type,
    ci.status,
    ci.issuer_rfc,
    ci.receiver_rfc,
    ci.receiver_name,
    ci.total,
    ci.created_at,
    bi.invoice_number,
    bi.invoice_date
FROM fiscal.cfdi_invoices ci
JOIN billing.invoices bi ON ci.invoice_id = bi.id
WHERE ci.status IN ('draft', 'pending')
  AND ci.deleted_at IS NULL
ORDER BY ci.created_at;

COMMENT ON VIEW fiscal.v_cfdi_pending_stamp IS 'CFDIs pendientes de timbrar';

-- Vista: Cancelaciones pendientes de respuesta
CREATE OR REPLACE VIEW fiscal.v_cfdi_pending_cancellations AS
SELECT
    cr.id,
    cr.tenant_id,
    cr.cfdi_uuid,
    cr.cancellation_reason,
    cr.status,
    cr.requested_at,
    cr.submitted_at,
    cr.expires_at,
    ci.serie,
    ci.folio,
    ci.receiver_rfc,
    ci.receiver_name,
    ci.total
FROM fiscal.cfdi_cancellation_requests cr
JOIN fiscal.cfdi_invoices ci ON cr.cfdi_invoice_id = ci.id
WHERE cr.status IN ('submitted', 'in_process')
ORDER BY cr.expires_at;

COMMENT ON VIEW fiscal.v_cfdi_pending_cancellations IS 'Solicitudes de cancelacion pendientes de respuesta';

-- Vista: Certificados por vencer
CREATE OR REPLACE VIEW fiscal.v_certificates_expiring AS
SELECT
    cc.id,
    cc.tenant_id,
    cc.certificate_number,
    cc.rfc,
    cc.status,
    cc.expires_at,
    cc.is_default,
    t.name as tenant_name,
    (cc.expires_at - CURRENT_TIMESTAMP) as time_to_expire
FROM fiscal.cfdi_certificates cc
JOIN auth.tenants t ON cc.tenant_id = t.id
WHERE cc.deleted_at IS NULL
  AND cc.status = 'active'
  AND cc.expires_at < (CURRENT_TIMESTAMP + INTERVAL '30 days')
ORDER BY cc.expires_at;

COMMENT ON VIEW fiscal.v_certificates_expiring IS 'Certificados CSD que vencen en los proximos 30 dias';

-- Vista: Resumen de uso de timbres por PAC
CREATE OR REPLACE VIEW fiscal.v_pac_stamp_usage AS
SELECT
    pc.tenant_id,
    pc.pac_code,
    pc.pac_name,
    pc.environment,
    pc.monthly_stamp_limit,
    pc.stamps_used_this_month,
    CASE
        WHEN pc.monthly_stamp_limit IS NULL THEN NULL
        ELSE (pc.stamps_used_this_month::DECIMAL / pc.monthly_stamp_limit * 100)
    END as usage_percentage,
    pc.contract_expires_at,
    t.name as tenant_name
FROM fiscal.cfdi_pac_configurations pc
JOIN auth.tenants t ON pc.tenant_id = t.id
WHERE pc.is_active = TRUE
  AND pc.deleted_at IS NULL
ORDER BY pc.tenant_id, pc.pac_code;

COMMENT ON VIEW fiscal.v_pac_stamp_usage IS 'Uso de timbres por PAC y tenant';

-- Vista: Facturas con saldo pendiente (PPD)
CREATE OR REPLACE VIEW fiscal.v_invoices_pending_payment AS
SELECT
    ci.id,
    ci.tenant_id,
    ci.uuid,
    ci.serie,
    ci.folio,
    ci.receiver_rfc,
    ci.receiver_name,
    ci.total,
    ci.stamp_date,
    COALESCE(SUM(pcd.amount_paid), 0) as total_paid,
    ci.total - COALESCE(SUM(pcd.amount_paid), 0) as remaining_balance,
    COUNT(pcd.id) as installment_count
FROM fiscal.cfdi_invoices ci
LEFT JOIN fiscal.cfdi_payment_complement_documents pcd
    ON pcd.related_uuid = ci.uuid
LEFT JOIN fiscal.cfdi_payment_complements pc
    ON pc.id = pcd.payment_complement_id
    AND pc.status = 'stamped'
WHERE ci.payment_method = 'PPD'
  AND ci.status = 'stamped'
  AND ci.deleted_at IS NULL
GROUP BY ci.id
HAVING ci.total - COALESCE(SUM(pcd.amount_paid), 0) > 0
ORDER BY ci.stamp_date;

COMMENT ON VIEW fiscal.v_invoices_pending_payment IS 'Facturas PPD con saldo pendiente de complemento de pago';

-- =====================
-- FIN DEL ARCHIVO
-- =====================