-- Update orders table for VAT
ALTER TABLE orders ADD COLUMN vat_total DECIMAL(10, 2) DEFAULT 0.00 AFTER total_price;

-- Update order_items table for variants and VAT
ALTER TABLE order_items ADD COLUMN variant_id INT AFTER item_id;
ALTER TABLE order_items ADD COLUMN vat_amount DECIMAL(10, 2) DEFAULT 0.00 AFTER unit_price;
ALTER TABLE order_items ADD CONSTRAINT fk_order_item_variant FOREIGN KEY (variant_id) REFERENCES item_variants(id) ON DELETE SET NULL;