
-- Schritt 3a: offer_positions migrieren
UPDATE pms_offer_positions op
JOIN pms_position_units pu ON pu.name = (CASE LOWER(TRIM(op.unit)) COLLATE utf8mb4_unicode_ci
    WHEN 'm2'   THEN CONCAT('m', CHAR(178 USING utf8mb4))
    WHEN 'tage' THEN 'Tage'
    WHEN 'psch' THEN 'Psch'
    WHEN 'std'  THEN 'Std'
    WHEN 'stk'  THEN 'Stk'
    WHEN 'lfm'  THEN 'lfm'
    WHEN 'kg'   THEN 'kg'
    ELSE TRIM(op.unit)
END) COLLATE utf8mb4_unicode_ci
SET op.fk_unit_id = pu.id;

-- Schritt 3b: order_positions migrieren
UPDATE pms_order_positions op
JOIN pms_position_units pu ON pu.name = (CASE LOWER(TRIM(op.unit)) COLLATE utf8mb4_unicode_ci
    WHEN 'm2'   THEN CONCAT('m', CHAR(178 USING utf8mb4))
    WHEN 'tage' THEN 'Tage'
    WHEN 'psch' THEN 'Psch'
    WHEN 'std'  THEN 'Std'
    WHEN 'stk'  THEN 'Stk'
    WHEN 'lfm'  THEN 'lfm'
    WHEN 'kg'   THEN 'kg'
    ELSE TRIM(op.unit)
END) COLLATE utf8mb4_unicode_ci
SET op.fk_unit_id = pu.id;

-- Schritt 3c: service_positions migrieren
UPDATE pms_service_positions sp
JOIN pms_position_units pu ON pu.name = (CASE LOWER(TRIM(sp.unit)) COLLATE utf8mb4_unicode_ci
    WHEN 'st'   THEN 'Stk'
    WHEN 'psch' THEN 'Psch'
    WHEN 'to'   THEN 't'
    WHEN 'sth'  THEN 'Std'
    WHEN 'd'    THEN 'Tage'
    ELSE TRIM(sp.unit)
END) COLLATE utf8mb4_unicode_ci
SET sp.fk_unit_id = pu.id;

-- Schritt 5: FK-Constraints setzen
ALTER TABLE pms_offer_positions
    ADD CONSTRAINT fk_offer_pos_unit
    FOREIGN KEY (fk_unit_id) REFERENCES pms_position_units(id)
    ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE pms_order_positions
    ADD CONSTRAINT fk_order_pos_unit
    FOREIGN KEY (fk_unit_id) REFERENCES pms_position_units(id)
    ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE pms_service_positions
    ADD CONSTRAINT fk_service_pos_unit
    FOREIGN KEY (fk_unit_id) REFERENCES pms_position_units(id)
    ON DELETE RESTRICT ON UPDATE CASCADE;

-- Schritt 6: Alte unit-Spalte loeschen
ALTER TABLE pms_offer_positions    DROP COLUMN unit;
ALTER TABLE pms_order_positions    DROP COLUMN unit;
ALTER TABLE pms_service_positions  DROP COLUMN unit;
