-- Enhance Material Master with additional fields for OEM Manufacturing
-- Run this in phpMyAdmin SQL tab
-- This migration adds new fields while keeping all existing data intact

-- Add thickness, grade, and sheet type references
ALTER TABLE `material` 
ADD COLUMN `thickness_id` INT NULL COMMENT 'Reference to Material Thickness master' AFTER `material_group_id`,
ADD COLUMN `grade_id` INT NULL COMMENT 'Reference to Material Grade master' AFTER `thickness_id`,
ADD COLUMN `sheet_type_id` INT NULL COMMENT 'Reference to Material Sheet Type master' AFTER `grade_id`;

-- Add dimensions fields
ALTER TABLE `material`
ADD COLUMN `length` DECIMAL(15,3) NULL COMMENT 'Length in mm' AFTER `volume_uom_id`,
ADD COLUMN `width` DECIMAL(15,3) NULL COMMENT 'Width in mm' AFTER `length`,
ADD COLUMN `height` DECIMAL(15,3) NULL COMMENT 'Height in mm' AFTER `width`,
ADD COLUMN `thickness` DECIMAL(10,3) NULL COMMENT 'Thickness in mm (for sheets/plates)' AFTER `height`,
ADD COLUMN `diameter` DECIMAL(15,3) NULL COMMENT 'Diameter in mm (for round materials)' AFTER `thickness`,
ADD COLUMN `dimension_unit` VARCHAR(10) NULL DEFAULT 'mm' COMMENT 'Unit for dimensions (default: mm)' AFTER `diameter`;

-- Add Priority 1 fields: Lot Sizing
ALTER TABLE `material`
ADD COLUMN `minimum_order_qty` DECIMAL(15,3) NULL COMMENT 'Minimum order quantity' AFTER `dimension_unit`,
ADD COLUMN `maximum_order_qty` DECIMAL(15,3) NULL COMMENT 'Maximum order quantity' AFTER `minimum_order_qty`,
ADD COLUMN `rounding_value` DECIMAL(15,3) NULL COMMENT 'Rounding value for lot sizing' AFTER `maximum_order_qty`,
ADD COLUMN `lot_size` DECIMAL(15,3) NULL COMMENT 'Standard lot size' AFTER `rounding_value`;

-- Add Priority 1 fields: Lead Time
ALTER TABLE `material`
ADD COLUMN `planned_delivery_time` INT NULL COMMENT 'Planned delivery time in days' AFTER `lot_size`,
ADD COLUMN `gr_processing_time` INT NULL COMMENT 'Goods receipt processing time in days' AFTER `planned_delivery_time`,
ADD COLUMN `in_house_production_time` INT NULL COMMENT 'In-house production time in days' AFTER `gr_processing_time`;

-- Add Priority 1 fields: Make/Buy & Procurement
ALTER TABLE `material`
ADD COLUMN `procurement_type` ENUM('E', 'F', 'X') NULL COMMENT 'E=External (Buy), F=In-house (Make), X=Both' AFTER `in_house_production_time`,
ADD COLUMN `make_buy_indicator` ENUM('MAKE', 'BUY', 'BOTH') NULL COMMENT 'Make or Buy indicator' AFTER `procurement_type`,
ADD COLUMN `special_procurement_type` VARCHAR(20) NULL COMMENT 'Special procurement type code' AFTER `make_buy_indicator`;

-- Add ABC classification
ALTER TABLE `material`
ADD COLUMN `abc_indicator` ENUM('A', 'B', 'C', 'X') NULL COMMENT 'ABC classification for inventory control' AFTER `special_procurement_type`;

-- Add manufacturer details
ALTER TABLE `material`
ADD COLUMN `manufacturer_part_number` VARCHAR(100) NULL COMMENT 'Manufacturer part number' AFTER `abc_indicator`,
ADD COLUMN `manufacturer_name` VARCHAR(200) NULL COMMENT 'Manufacturer name' AFTER `manufacturer_part_number`;

-- Add quality management
ALTER TABLE `material`
ADD COLUMN `inspection_required` TINYINT(1) NULL DEFAULT 0 COMMENT 'Quality inspection required' AFTER `manufacturer_name`,
ADD COLUMN `inspection_type` ENUM('NONE', 'OPTIONAL', 'MANDATORY') NULL DEFAULT 'NONE' COMMENT 'Type of inspection' AFTER `inspection_required`;

-- Add foreign key constraints
ALTER TABLE `material`
ADD CONSTRAINT `fk_material_thickness` FOREIGN KEY (`thickness_id`) REFERENCES `material_thickness` (`id`),
ADD CONSTRAINT `fk_material_grade` FOREIGN KEY (`grade_id`) REFERENCES `material_grade` (`id`),
ADD CONSTRAINT `fk_material_sheet_type` FOREIGN KEY (`sheet_type_id`) REFERENCES `material_sheet_type` (`id`);

-- Add indexes for new fields
ALTER TABLE `material`
ADD KEY `idx_material_thickness` (`thickness_id`),
ADD KEY `idx_material_grade` (`grade_id`),
ADD KEY `idx_material_sheet_type` (`sheet_type_id`),
ADD KEY `idx_material_procurement` (`procurement_type`),
ADD KEY `idx_material_abc` (`abc_indicator`),
ADD KEY `idx_material_mfr_part` (`manufacturer_part_number`);

