-- (Suppression de la contrainte, du Trigger et de l'index)
ALTER TABLE bom_row_product_model DROP CONSTRAINT fk_brpm__pa__product_material_article_id;
DROP TRIGGER trg_for_flow_apo_on_bom_row_product_model ON bom_row_product_model;
DROP INDEX idx_brpm__product_material_article_id;

UPDATE bom_row_product_model SET free_color = infoMat.freeColor
FROM (
    SELECT ma.id, ma.display_name as freeColor
    FROM material_article_old_3445 ma
    LEFT JOIN product_article pa ON pa.tmp_material_article_id = ma.id
    WHERE pa.id IS NULL
) infoMat
WHERE bom_row_product_model.product_material_article_id IS NULL
AND bom_row_product_model.material_article_id_old_3445 = infoMat.id;

-- (La requête sur bom_row_product_model est passée, je remets le trigger, la contrainte et l'index)
ALTER TABLE bom_row_product_model
    ADD CONSTRAINT fk_brpm__pa__product_material_article_id FOREIGN KEY (product_material_article_id) REFERENCES product_article(id);

CREATE INDEX idx_brpm__product_material_article_id ON bom_row_product_model(product_material_article_id);

CREATE TRIGGER trg_for_flow_apo_on_bom_row_product_model
  AFTER UPDATE OF product_material_article_id
  ON bom_row_product_model
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_for_flow_apo_bom_update();


-- 2. Update Tables linked to a ElementConception

-- BOM (bom_row)

-- (Suppression des Triggers pour les perfs)
DROP TRIGGER trg_for_flow_apo_on_bom_row ON bom_row;
DROP TRIGGER trg_for_flow_apo_on_bom_row_delete ON bom_row;

UPDATE bom_row bom SET free_element_conception = infoBom.freeElementConception, element_conception_id = null, element_conception_id_old = element_conception_id
FROM (
    SELECT mc.id as ecId, mc.display_name as freeElementConception
    FROM material_conception_old_3445 mc
    LEFT JOIN product_conception pc ON pc.tmp_material_conception_id = mc.id
    WHERE pc.id IS NULL
) infoBom
WHERE bom.element_conception_id_old IS NULL AND infoBom.ecId = bom.element_conception_id;

UPDATE bom_row bom SET free_element_conception = infoBom.freeElementConception, element_conception_id = null, element_conception_id_old = element_conception_id
FROM (
    SELECT mm.id as ecId, mm.display_name as freeElementConception
    FROM material_model_old_3445 mm
    LEFT JOIN product_model pm ON pm.tmp_material_model_id = mm.id
    WHERE pm.id IS NULL
) infoBom
WHERE bom.element_conception_id_old IS NULL AND infoBom.ecId = bom.element_conception_id;

UPDATE bom_row bom SET free_element_conception = infoBom.freeElementConception, element_conception_id = null, element_conception_id_old = element_conception_id
FROM (
    SELECT ma.id as ecId, ma.display_name as freeElementConception
    FROM material_article_old_3445 ma
    LEFT JOIN product_article pa ON pa.tmp_material_article_id = ma.id
    WHERE pa.id IS NULL
) infoBom
WHERE bom.element_conception_id_old IS NULL AND infoBom.ecId = bom.element_conception_id;

-- (Les requêtes sur bom_row sont passées, je remets les triggers)
CREATE TRIGGER trg_for_flow_apo_on_bom_row
  AFTER UPDATE OF quantity, unit_id, element_conception_id
  ON bom_row
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_for_flow_apo_bom_update();
CREATE TRIGGER trg_for_flow_apo_on_bom_row_delete
  BEFORE UPDATE OF deleted_flag
  ON bom_row
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_for_flow_apo_bom_update();