-- Preserve names in order_items for historical data
ALTER TABLE order_items ADD COLUMN product_name VARCHAR(255) AFTER product_id;
ALTER TABLE order_items ADD COLUMN variant_name VARCHAR(255) AFTER variant_id;

-- Populate existing names
UPDATE order_items oi JOIN products p ON oi.product_id = p.id SET oi.product_name = p.name;
UPDATE order_items oi JOIN product_variants pv ON oi.variant_id = pv.id SET oi.variant_name = pv.name;

-- Modify foreign key to allow hard delete while preserving historical info
ALTER TABLE order_items DROP FOREIGN KEY order_items_ibfk_2;
ALTER TABLE order_items ADD CONSTRAINT order_items_ibfk_2 FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL;