-- Migration 010: Enhance GRN Items with dimension fields
-- This migration adds dimension fields to GRN items for RM products

-- Add dimension fields to grn_items table
ALTER TABLE grn_items 
ADD COLUMN piece_length DECIMAL(12, 3) NULL COMMENT 'Length of each piece received',
ADD COLUMN piece_width DECIMAL(12, 3) NULL COMMENT 'Width of each piece received',
ADD COLUMN dimension_unit ENUM('inch', 'cm', 'm') NULL COMMENT 'Unit of measure for piece dimensions',
ADD COLUMN pieces_count INT DEFAULT 1 COMMENT 'Number of pieces with these dimensions';

-- Add indexes for dimension-based queries
ALTER TABLE grn_items 
ADD INDEX idx_piece_dimensions (piece_length, piece_width, dimension_unit),
ADD INDEX idx_pieces_count (pieces_count);

-- Add constraints to ensure data integrity
ALTER TABLE grn_items 
ADD CONSTRAINT chk_positive_piece_dimensions 
CHECK (
  (piece_length IS NULL OR piece_length > 0) AND 
  (piece_width IS NULL OR piece_width > 0)
);

ALTER TABLE grn_items 
ADD CONSTRAINT chk_positive_pieces_count 
CHECK (pieces_count > 0);

-- Verify the table structure
DESCRIBE grn_items;

-- Show the indexes created
SHOW INDEX FROM grn_items;