CREATE OR REPLACE PROCEDURE public.cleanup_soft_deleted()
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_cutoff timestamptz := now() - interval '1 day';
    v_deleted_count integer;
BEGIN
    -- Step 1: Delete children that reference soft-deleted parent records first.

    -- Delete notas_internas that reference soft-deleted clientes.
    DELETE FROM notas_internas ni
    WHERE EXISTS (
        SELECT 1
        FROM clientes c
        WHERE c.id_cliente = ni.id_cliente
          AND c.soft_delete = true
          AND c.soft_delete_date::timestamptz <= v_cutoff
    );
    GET DIAGNOSTICS v_deleted_count = ROW_COUNT;
    RAISE NOTICE 'Deleted % rows from notas_internas (orphaned by soft-deleted clientes)', v_deleted_count;

    -- Delete pagos_planes that reference soft-deleted clientes.
    DELETE FROM pagos_planes pp
    WHERE EXISTS (
        SELECT 1
        FROM clientes c
        WHERE c.id_cliente = pp.id_cliente_pagador
          AND c.soft_delete = true
          AND c.soft_delete_date::timestamptz <= v_cutoff
    );
    GET DIAGNOSTICS v_deleted_count = ROW_COUNT;
    RAISE NOTICE 'Deleted % rows from pagos_planes (orphaned)', v_deleted_count;

    -- Delete planes_pago that reference soft-deleted clientes.
    DELETE FROM planes_pago pl
    WHERE EXISTS (
        SELECT 1
        FROM clientes c
        WHERE c.id_cliente = pl.id_cliente
          AND c.soft_delete = true
          AND c.soft_delete_date::timestamptz <= v_cutoff
    );
    GET DIAGNOSTICS v_deleted_count = ROW_COUNT;
    RAISE NOTICE 'Deleted % rows from planes_pago (orphaned)', v_deleted_count;

    -- Delete reportes that reference soft-deleted clientes.
    DELETE FROM reportes r
    WHERE r.id_cliente IS NOT NULL
      AND EXISTS (
        SELECT 1
        FROM clientes c
        WHERE c.id_cliente = r.id_cliente
          AND c.soft_delete = true
          AND c.soft_delete_date::timestamptz <= v_cutoff
    );
    GET DIAGNOSTICS v_deleted_count = ROW_COUNT;
    RAISE NOTICE 'Deleted % rows from reportes (orphaned)', v_deleted_count;

    -- Step 2: Delete rows marked soft_delete by retention window.
    DELETE FROM knowledge_items WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM backend_agent_test WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM category_rules_v2 WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM categorias_combinables_v2 WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM notas_internas WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM pagos_planes WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM planes_pago WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM reportes WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM citas WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM codigos_activacion WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM estadisticas WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM excepciones_vacaciones WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM inventario WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM transacciones WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM preguntas_clasificacion_multi WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;

    -- Parent tables last.
    DELETE FROM clientes WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM disponibilidad_usuarios WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM empleado_digital WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM usuarios WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM configuraciones WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;
    DELETE FROM login WHERE soft_delete = true AND soft_delete_date::timestamptz <= v_cutoff;

    RAISE NOTICE 'Cleanup completed successfully';
END;
$procedure$;
