ALTER TABLE orders
    ADD COLUMN external_reference VARCHAR(120) NULL AFTER order_number,
    ADD COLUMN production_reference VARCHAR(120) NULL AFTER external_reference,
    ADD COLUMN internal_note TEXT NULL AFTER shipping_address_json,
    ADD COLUMN validated_at DATETIME NULL AFTER submitted_at,
    ADD COLUMN paid_at DATETIME NULL AFTER validated_at,
    ADD COLUMN shipped_at DATETIME NULL AFTER paid_at;

UPDATE orders
SET submitted_at = COALESCE(submitted_at, created_at)
WHERE status IN ('submitted', 'validated', 'paid', 'in_production', 'shipped', 'fulfilled');

UPDATE orders
SET validated_at = COALESCE(validated_at, submitted_at, created_at)
WHERE status IN ('validated', 'paid', 'in_production', 'shipped', 'fulfilled');

UPDATE orders
SET paid_at = COALESCE(paid_at, validated_at, submitted_at, created_at)
WHERE status IN ('paid', 'in_production', 'shipped', 'fulfilled');

UPDATE orders
SET shipped_at = COALESCE(shipped_at, paid_at, validated_at, submitted_at, created_at)
WHERE status IN ('shipped', 'fulfilled');
