-- Creation d'index pour faciliter le requetage basé sur les data_detail_value.data_detail_value et data_detail_value.upper_bound
CREATE INDEX data_detail_value_idx ON data_detail_value(data_detail_value);
CREATE INDEX upper_bound_idx ON data_detail_value(upper_bound);

-- Suppression des tranches délégataire
DELETE FROM  data_detail_value toDelete WHERE toDelete.upper_bound = 120 AND toDelete.id_lot_spea in ( SELECT ddv.id_lot_spea FROM data_detail_value ddv left join lot_spea ls on ddv.id_lot_spea = ls.id_lot_spea WHERE ls.id_exercise = 121 AND ddv.upper_bound = 120 AND ddv.id_data_detail = 2 AND ddv.id_lot_spea IN (SELECT ddv2.id_lot_spea FROM data_detail_value ddv2 left join lot_spea ls2 on ddv2.id_lot_spea = ls2.id_lot_spea WHERE ddv2.upper_bound = 1000000000 AND ls2.id_exercise = 121 AND ddv2.id_data_detail = 2 AND ( ddv2.data_detail_value = ddv.data_detail_value OR (ddv.data_detail_value IS NULL AND ddv2.data_detail_value IS NULL) ) ) AND ddv.id_lot_spea IN ( SELECT ddv3.id_lot_spea FROM data_detail_value ddv3 left join lot_spea ls3 on ddv3.id_lot_spea = ls3.id_lot_spea WHERE (ddv3.upper_bound <= 120 OR ddv3.upper_bound = 1000000000) AND ddv3.id_data_detail = 2 AND ls3.id_exercise = 121) );

-- Suppression des tranches collectivité
DELETE FROM  data_detail_value toDelete WHERE toDelete.upper_bound = 120 AND toDelete.id_lot_spea in ( SELECT ddv.id_lot_spea FROM data_detail_value ddv left join lot_spea ls on ddv.id_lot_spea = ls.id_lot_spea WHERE ls.id_exercise = 121 AND ddv.upper_bound = 120 AND ddv.id_data_detail = 4 AND ddv.id_lot_spea IN (SELECT ddv2.id_lot_spea FROM data_detail_value ddv2 left join lot_spea ls2 on ddv2.id_lot_spea = ls2.id_lot_spea WHERE ddv2.upper_bound = 1000000000 AND ls2.id_exercise = 121 AND ddv2.id_data_detail = 4 AND ( ddv2.data_detail_value = ddv.data_detail_value OR (ddv.data_detail_value IS NULL AND ddv2.data_detail_value IS NULL) ) ) AND ddv.id_lot_spea IN ( SELECT ddv3.id_lot_spea FROM data_detail_value ddv3 left join lot_spea ls3 on ddv3.id_lot_spea = ls3.id_lot_spea WHERE (ddv3.upper_bound <= 120 OR ddv3.upper_bound = 1000000000) AND ddv3.id_data_detail = 4 AND ls3.id_exercise = 121) );

-- Suppression des index précédemment créés
DROP INDEX data_detail_value_idx;
DROP INDEX upper_bound_idx;