
-- =============================================================
-- Schritt 1: Neue Einheiten einfuegen (ASCII-Namen als Platzhalter)
-- =============================================================
INSERT INTO pms_position_units (name, label, sort, is_active, is_deleted) VALUES
    ('Tage',  'Tage',                  14, 1, 0),
    ('cm',    'Zentimeter',            15, 1, 0),
    ('m2Wo',  'Quadratmeter / Woche',  16, 1, 0),
    ('StWo',  'Stueck / Woche',        17, 1, 0),
    ('mWo',   'Meter / Woche',         18, 1, 0),
    ('m3Wo',  'Kubikmeter / Woche',    19, 1, 0),
    ('StMt',  'Stueck / Monat',        20, 1, 0),
    ('m2Mt',  'Quadratmeter / Monat',  21, 1, 0);

-- =============================================================
-- Schritt 2: Spalte fk_unit_id hinzufuegen
-- =============================================================
ALTER TABLE pms_offer_positions   ADD COLUMN fk_unit_id INT UNSIGNED NULL DEFAULT NULL AFTER unit;
ALTER TABLE pms_order_positions   ADD COLUMN fk_unit_id INT UNSIGNED NULL DEFAULT NULL AFTER unit;
ALTER TABLE pms_service_positions ADD COLUMN fk_unit_id INT UNSIGNED NULL DEFAULT NULL AFTER unit;

-- =============================================================
-- Schritt 3: Werte migrieren
-- CHAR(178) = Zeichen hoch 2, CHAR(179) = Zeichen hoch 3
-- =============================================================

-- offer_positions
UPDATE pms_offer_positions op
JOIN pms_position_units pu ON pu.name = CASE LOWER(TRIM(op.unit))
    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
SET op.fk_unit_id = pu.id;

-- order_positions
UPDATE pms_order_positions op
JOIN pms_position_units pu ON pu.name = CASE LOWER(TRIM(op.unit))
    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
SET op.fk_unit_id = pu.id;

-- service_positions
-- m2Wo / m3Wo / m2Mt noch als ASCII-Platzhalter in pms_position_units
UPDATE pms_service_positions sp
JOIN pms_position_units pu ON pu.name = CASE LOWER(TRIM(sp.unit))
    WHEN 'st'   THEN 'Stk'
    WHEN 'psch' THEN 'Psch'
    WHEN 'to'   THEN 't'
    WHEN 'sth'  THEN 'Std'
    WHEN 'd'    THEN 'Tage'
    WHEN 'm2'   THEN CONCAT('m', CHAR(178 USING utf8mb4))
    WHEN CONCAT('m', CHAR(178 USING utf8mb4), 'wo') THEN 'm2Wo'
    WHEN CONCAT('m', CHAR(179 USING utf8mb4), 'wo') THEN 'm3Wo'
    WHEN CONCAT('m', CHAR(178 USING utf8mb4), 'mt') THEN 'm2Mt'
    WHEN 'stwo' THEN 'StWo'
    WHEN 'mwo'  THEN 'mWo'
    WHEN 'stmt' THEN 'StMt'
    ELSE TRIM(sp.unit)
END
SET sp.fk_unit_id = pu.id;

-- =============================================================
-- Schritt 4: Platzhalter-Namen auf korrekte Unicode-Namen setzen
-- =============================================================
UPDATE pms_position_units SET name = CONCAT('m', CHAR(178 USING utf8mb4), 'Wo'), label = 'Quadratmeter / Woche' WHERE name = 'm2Wo';
UPDATE pms_position_units SET name = CONCAT('m', CHAR(179 USING utf8mb4), 'Wo'), label = 'Kubikmeter / Woche'   WHERE name = 'm3Wo';
UPDATE pms_position_units SET name = CONCAT('m', CHAR(178 USING utf8mb4), 'Mt'), label = 'Quadratmeter / Monat' WHERE name = 'm2Mt';
UPDATE pms_position_units SET name = 'StWo', label = 'Stk / Woche'  WHERE name = 'StWo';
UPDATE pms_position_units SET name = 'StMt', label = 'Stk / Monat'  WHERE name = 'StMt';

-- =============================================================
-- 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;
