-- Migration: Add partial production confirmation support
-- Description: Adds fields to track partial production confirmation and remaining quantities
-- Date: 2026-01-19

-- Step 1: Modify status ENUM to include PARTIALLY_CONFIRMED
-- Note: MySQL doesn't support direct ALTER ENUM, so we need to recreate it
ALTER TABLE `production_orders` 
MODIFY COLUMN `status` ENUM('DRAFT', 'PARTIALLY_CONFIRMED', 'CONFIRMED') 
NOT NULL DEFAULT 'DRAFT';

-- Step 2: Add confirmed_quantity field to track how much has been confirmed so far
ALTER TABLE `production_orders` 
ADD COLUMN `confirmed_quantity` DECIMAL(12, 3) NULL DEFAULT 0.000 
AFTER `quantity`,
ADD COLUMN `remaining_quantity` DECIMAL(12, 3) NULL DEFAULT 0.000 
AFTER `confirmed_quantity`;

-- Step 3: Initialize confirmed_quantity and remaining_quantity for existing records
-- For existing CONFIRMED orders, confirmed_quantity = quantity, remaining_quantity = 0
UPDATE `production_orders` 
SET `confirmed_quantity` = `quantity`,
    `remaining_quantity` = 0.000
WHERE `status` = 'CONFIRMED';

-- For existing DRAFT orders, confirmed_quantity = 0, remaining_quantity = quantity
UPDATE `production_orders` 
SET `confirmed_quantity` = 0.000,
    `remaining_quantity` = `quantity`
WHERE `status` = 'DRAFT';

-- Step 4: Add index on remaining_quantity for filtering partially confirmed orders
ALTER TABLE `production_orders` 
ADD INDEX `idx_remaining_quantity` (`remaining_quantity`);
