-- ============================================================================
-- Migration 020: Fix category triggers and UNIQUE constraint
-- Fecha: 2026-02-08
-- ============================================================================
-- Changes:
-- A) New trigger on categorias_combinables_v2: clean v2 category IDs from
--    clientes.categorias and conocimientos_empleado_digital.categorias JSONB
--    arrays when a v2 category is soft-deleted
-- B) Fix UNIQUE constraint on categorias_combinables_v2 to allow recreating
--    categories with the same name after soft-deleting the original
-- ============================================================================
-- IMPORTANT: This script is idempotent - it can be executed multiple times
-- ============================================================================

-- ============================================================================
-- SECTION A: Trigger to clean v2 category IDs from JSONB arrays on soft-delete
-- ============================================================================

-- A.1: Create function fn_clean_v2_category_from_jsonb
-- Mirrors the logic of fn_clean_category_from_jsonb (on categorias_conocimientos_clientes)
-- but operates on categorias_combinables_v2 soft-delete events.
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);

    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- A.2: Create trigger on categorias_combinables_v2
DROP TRIGGER IF EXISTS trg_clean_v2_category_from_jsonb ON categorias_combinables_v2;

CREATE TRIGGER trg_clean_v2_category_from_jsonb
    AFTER UPDATE OF soft_delete ON categorias_combinables_v2
    FOR EACH ROW
    WHEN (NEW.soft_delete = TRUE)
    EXECUTE FUNCTION fn_clean_v2_category_from_jsonb();

-- ============================================================================
-- SECTION B: Fix UNIQUE constraint to exclude soft-deleted rows
-- ============================================================================

-- B.1: Drop existing absolute UNIQUE constraint (must use ALTER TABLE, not DROP INDEX)
ALTER TABLE categorias_combinables_v2 DROP CONSTRAINT IF EXISTS uq_cat_v2_business_name;
DROP INDEX IF EXISTS uq_cat_v2_business_name;

-- B.2: Create partial UNIQUE index (only for active categories)
-- Uses IS NOT TRUE to handle both false and NULL values of soft_delete
CREATE UNIQUE INDEX uq_cat_v2_business_name
    ON categorias_combinables_v2 (business_id, name)
    WHERE soft_delete IS NOT TRUE;

-- ============================================================================
-- 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 and conocimientos_empleado_digital.categorias JSONB arrays for the same business_id.';

-- ============================================================================
-- END OF MIGRATION 020
-- ============================================================================
