-- Migration 009: Enhance BOM Items with dimension requirements
-- This migration adds dimension-based requirements to BOM items for RM products

-- Add dimension requirement fields to bom_items table
ALTER TABLE bom_items 
ADD COLUMN required_length DECIMAL(12, 3) NULL COMMENT 'Required length per unit of FG product',
ADD COLUMN required_width DECIMAL(12, 3) NULL COMMENT 'Required width per unit of FG product',
ADD COLUMN dimension_unit ENUM('inch', 'cm', 'm') NULL COMMENT 'Unit of measure for dimension requirements',
ADD COLUMN use_dimensions BOOLEAN DEFAULT FALSE COMMENT 'Flag to enable dimension-based BOM (vs quantity-based)';

-- Add indexes for dimension-based queries
ALTER TABLE bom_items 
ADD INDEX idx_use_dimensions (use_dimensions),
ADD INDEX idx_dimension_requirements (required_length, required_width, dimension_unit);

-- Add constraints to ensure data integrity
ALTER TABLE bom_items 
ADD CONSTRAINT chk_dimension_requirements 
CHECK (
  (use_dimensions = FALSE) OR 
  (use_dimensions = TRUE AND required_length IS NOT NULL AND required_width IS NOT NULL AND dimension_unit IS NOT NULL)
);

ALTER TABLE bom_items 
ADD CONSTRAINT chk_positive_dimension_requirements 
CHECK (
  (required_length IS NULL OR required_length > 0) AND 
  (required_width IS NULL OR required_width > 0)
);

-- Verify the table structure
DESCRIBE bom_items;

-- Show the indexes created
SHOW INDEX FROM bom_items;