-- Migration 011: Enhance Inventories with dimension summary fields
-- This migration adds dimension summary fields to inventories table for RM products

-- Add dimension summary fields to inventories table
ALTER TABLE inventories 
ADD COLUMN total_full_area DECIMAL(15, 3) NULL COMMENT 'Total area of FULL pieces (denormalized for performance)',
ADD COLUMN total_usable_area DECIMAL(15, 3) NULL COMMENT 'Total usable area (USABLE + WASTE pieces)',
ADD COLUMN total_waste_area DECIMAL(15, 3) NULL COMMENT 'Total area of WASTE pieces',
ADD COLUMN total_scrap_area DECIMAL(15, 3) NULL COMMENT 'Total area of SCRAP pieces',
ADD COLUMN dimension_unit ENUM('inch', 'cm', 'm') NULL COMMENT 'Primary unit of measure for this product';

-- Add indexes for dimension-based queries
ALTER TABLE inventories 
ADD INDEX idx_dimension_areas (total_full_area, total_usable_area, total_waste_area),
ADD INDEX idx_dimension_unit (dimension_unit);

-- Add constraints to ensure data integrity
ALTER TABLE inventories 
ADD CONSTRAINT chk_non_negative_areas 
CHECK (
  (total_full_area IS NULL OR total_full_area >= 0) AND 
  (total_usable_area IS NULL OR total_usable_area >= 0) AND
  (total_waste_area IS NULL OR total_waste_area >= 0) AND
  (total_scrap_area IS NULL OR total_scrap_area >= 0)
);

-- Verify the table structure
DESCRIBE inventories;

-- Show the indexes created
SHOW INDEX FROM inventories;