-- ============================================================================
-- Migration 027: Cascade soft-delete variantes when category is soft-deleted
-- Fecha: 2026-02-12
-- ============================================================================
-- When a categorias_combinables_v2 row is soft-deleted, automatically
-- soft-delete all associated inventario_variantes_categoria rows that
-- share the same category_id. General variants (category_id IS NULL)
-- are NOT affected.
--
-- Follows the pattern of fn_cascade_soft_delete_rules_v2 but uses
-- IS NOT TRUE for NULL-safe soft_delete checks (project standard fix).
-- ============================================================================
-- IMPORTANT: This script is idempotent - it can be executed multiple times
-- ============================================================================

-- ============================================================================
-- SECTION A: Create trigger function
-- ============================================================================

CREATE OR REPLACE FUNCTION fn_cascade_soft_delete_variantes_on_category_delete()
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 NOT TRUE) THEN
        -- Cascade soft-delete to variantes linked to this category
        UPDATE inventario_variantes_categoria
        SET soft_delete = true,
            soft_delete_date = NOW()
        WHERE category_id = NEW.category_id
          AND soft_delete IS NOT TRUE;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- SECTION B: Create trigger on categorias_combinables_v2
-- ============================================================================

DROP TRIGGER IF EXISTS trg_cascade_soft_delete_variantes_v2 ON categorias_combinables_v2;

CREATE TRIGGER trg_cascade_soft_delete_variantes_v2
    AFTER UPDATE ON categorias_combinables_v2
    FOR EACH ROW
    WHEN (NEW.soft_delete = true AND OLD.soft_delete IS DISTINCT FROM true)
    EXECUTE FUNCTION fn_cascade_soft_delete_variantes_on_category_delete();

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

COMMENT ON FUNCTION fn_cascade_soft_delete_variantes_on_category_delete() IS
'Trigger function: when a categorias_combinables_v2 row is soft-deleted, cascade soft-delete to all inventario_variantes_categoria rows with matching category_id. General variants (category_id IS NULL) are not affected.';

-- ============================================================================
-- END OF MIGRATION 027
-- ============================================================================
