-- ============================================================================
-- Migration 028: Clean preguntas_clasificacion_multi on category soft-delete
--                or deactivation
-- Fecha: 2026-02-12
-- ============================================================================
-- When a categorias_combinables_v2 row is soft-deleted, remove its category_id
-- from preguntas_clasificacion_multi.categorias_participantes (int4[] array).
-- Also handles deactivation (is_active changes TRUE -> FALSE).
--
-- Two changes:
-- A) Extend fn_clean_v2_category_from_jsonb to ALSO clean preguntas array
-- B) New trigger function + trigger for is_active deactivation
-- ============================================================================
-- IMPORTANT: This script is idempotent - it can be executed multiple times
-- ============================================================================

-- ============================================================================
-- SECTION A: Extend fn_clean_v2_category_from_jsonb to clean preguntas
-- ============================================================================
-- This replaces the existing function (from migration 020) adding the
-- preguntas_clasificacion_multi cleanup while preserving existing logic
-- for clientes.categorias and conocimientos_empleado_digital.categorias.

CREATE OR REPLACE FUNCTION fn_clean_v2_category_from_jsonb()
RETURNS TRIGGER AS $$
BEGIN
    -- Only act when soft_delete changes from FALSE/NULL to TRUE
    IF NEW.soft_delete = TRUE AND (OLD.soft_delete IS NULL OR OLD.soft_delete = FALSE) THEN

        -- Remove category_id from clientes.categorias for this business
        UPDATE clientes
        SET
            categorias = (
                SELECT COALESCE(
                    jsonb_agg(elem),
                    '[]'::jsonb
                )
                FROM jsonb_array_elements(categorias) AS elem
                WHERE elem::int != NEW.category_id
            ),
            updated_at = NOW()
        WHERE business_id = NEW.business_id
          AND categorias @> to_jsonb(NEW.category_id);

        -- Remove category_id from conocimientos_empleado_digital.categorias for this business
        UPDATE conocimientos_empleado_digital
        SET
            categorias = (
                SELECT COALESCE(
                    jsonb_agg(elem),
                    '[]'::jsonb
                )
                FROM jsonb_array_elements(categorias) AS elem
                WHERE elem::int != NEW.category_id
            ),
            updated_at = NOW()
        WHERE business_id = NEW.business_id
          AND categorias @> to_jsonb(NEW.category_id);

        -- Remove category_id from preguntas_clasificacion_multi.categorias_participantes for this business
        UPDATE preguntas_clasificacion_multi
        SET categorias_participantes = array_remove(categorias_participantes, NEW.category_id),
            updated_at = NOW()
        WHERE business_id = NEW.business_id
          AND soft_delete IS NOT TRUE
          AND categorias_participantes @> ARRAY[NEW.category_id];

    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- The trigger trg_clean_v2_category_from_jsonb already exists on
-- categorias_combinables_v2 (from migration 020) and does NOT need
-- to be recreated — it calls fn_clean_v2_category_from_jsonb which
-- we just replaced in-place.

-- ============================================================================
-- SECTION B: New trigger for is_active deactivation
-- ============================================================================

CREATE OR REPLACE FUNCTION fn_clean_preguntas_on_category_deactivate()
RETURNS TRIGGER AS $$
BEGIN
    -- Only act when is_active changes from TRUE to FALSE
    IF NEW.is_active = FALSE AND OLD.is_active = TRUE THEN

        -- Remove category_id from preguntas_clasificacion_multi.categorias_participantes
        UPDATE preguntas_clasificacion_multi
        SET categorias_participantes = array_remove(categorias_participantes, NEW.category_id),
            updated_at = NOW()
        WHERE business_id = NEW.business_id
          AND soft_delete IS NOT TRUE
          AND categorias_participantes @> ARRAY[NEW.category_id];

    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- B.2: Create trigger on categorias_combinables_v2 for is_active deactivation
DROP TRIGGER IF EXISTS trg_clean_preguntas_on_category_deactivate ON categorias_combinables_v2;

CREATE TRIGGER trg_clean_preguntas_on_category_deactivate
    AFTER UPDATE OF is_active ON categorias_combinables_v2
    FOR EACH ROW
    WHEN (NEW.is_active = FALSE AND OLD.is_active = TRUE)
    EXECUTE FUNCTION fn_clean_preguntas_on_category_deactivate();

-- ============================================================================
-- SECTION C: Comments
-- ============================================================================

COMMENT ON FUNCTION fn_clean_v2_category_from_jsonb() IS
'Trigger function: when a categorias_combinables_v2 row is soft-deleted, removes its category_id from clientes.categorias JSONB, conocimientos_empleado_digital.categorias JSONB, and preguntas_clasificacion_multi.categorias_participantes int4[] array for the same business_id.';

COMMENT ON FUNCTION fn_clean_preguntas_on_category_deactivate() IS
'Trigger function: when a categorias_combinables_v2 row is deactivated (is_active TRUE -> FALSE), removes its category_id from preguntas_clasificacion_multi.categorias_participantes int4[] array for the same business_id.';

-- ============================================================================
-- END OF MIGRATION 028
-- ============================================================================
