35 lines
1.2 KiB
MySQL
35 lines
1.2 KiB
MySQL
|
-- Update inventory_movements table to support ingredients
|
||
|
|
ALTER TABLE inventory_movements
|
||
|
|
ADD COLUMN item_id UUID,
|
||
|
|
ADD COLUMN item_type VARCHAR(20);
|
||
|
|
|
||
|
|
-- Copy existing product_id data to item_id
|
||
|
|
UPDATE inventory_movements
|
||
|
|
SET item_id = product_id,
|
||
|
|
item_type = 'PRODUCT'
|
||
|
|
WHERE product_id IS NOT NULL;
|
||
|
|
|
||
|
|
-- Make item_id and item_type NOT NULL after data migration
|
||
|
|
ALTER TABLE inventory_movements
|
||
|
|
ALTER COLUMN item_id SET NOT NULL,
|
||
|
|
ALTER COLUMN item_type SET NOT NULL;
|
||
|
|
|
||
|
|
-- Drop the old product_id column
|
||
|
|
ALTER TABLE inventory_movements
|
||
|
|
DROP COLUMN product_id;
|
||
|
|
|
||
|
|
-- Update quantity columns to support decimal
|
||
|
|
ALTER TABLE inventory_movements
|
||
|
|
ALTER COLUMN quantity TYPE DECIMAL(12,3),
|
||
|
|
ALTER COLUMN previous_quantity TYPE DECIMAL(12,3),
|
||
|
|
ALTER COLUMN new_quantity TYPE DECIMAL(12,3);
|
||
|
|
|
||
|
|
-- Update cost columns to support higher precision
|
||
|
|
ALTER TABLE inventory_movements
|
||
|
|
ALTER COLUMN unit_cost TYPE DECIMAL(12,2),
|
||
|
|
ALTER COLUMN total_cost TYPE DECIMAL(12,2);
|
||
|
|
|
||
|
|
-- Add indexes for the new structure
|
||
|
|
CREATE INDEX idx_inventory_movements_item_id ON inventory_movements(item_id);
|
||
|
|
CREATE INDEX idx_inventory_movements_item_type ON inventory_movements(item_type);
|
||
|
|
CREATE INDEX idx_inventory_movements_item_id_type ON inventory_movements(item_id, item_type);
|