-- Migration 008: Create RM Cutting Operations table
-- This migration creates the table for tracking cutting operations on RM inventory pieces

-- Create rm_cutting_operations table
CREATE TABLE IF NOT EXISTS rm_cutting_operations (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  production_order_id BIGINT NOT NULL COMMENT 'Production order that triggered this cutting operation',
  rm_piece_id BIGINT NOT NULL COMMENT 'Original RM piece being cut',
  bom_item_id BIGINT NOT NULL COMMENT 'BOM item that required this material',
  
  -- Dimensions cut from the original piece
  cut_length DECIMAL(12, 3) NOT NULL COMMENT 'Length of material cut for production',
  cut_width DECIMAL(12, 3) NOT NULL COMMENT 'Width of material cut for production',
  unit ENUM('inch', 'cm', 'm') NOT NULL DEFAULT 'm' COMMENT 'Unit of measure for cut dimensions',
  
  -- Resulting pieces after cutting
  remaining_piece_id BIGINT NULL COMMENT 'ID of remaining usable piece (if any)',
  waste_pieces JSON NULL COMMENT 'Array of waste pieces created: [{length, width, status}]',
  scrap_dimensions JSON NULL COMMENT 'Scrap dimensions that cannot be reused: {length, width}',
  
  -- Operation metadata
  cut_by_user_id BIGINT NULL COMMENT 'User who performed the cutting operation',
  cut_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'When the cutting operation was performed',
  notes TEXT NULL COMMENT 'Additional notes about the cutting operation',
  
  -- Audit trail
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  -- Foreign key constraints
  FOREIGN KEY (production_order_id) REFERENCES production_orders(id) ON DELETE CASCADE,
  FOREIGN KEY (rm_piece_id) REFERENCES rm_inventory_pieces(id) ON DELETE CASCADE,
  FOREIGN KEY (bom_item_id) REFERENCES bom_items(id) ON DELETE CASCADE,
  FOREIGN KEY (remaining_piece_id) REFERENCES rm_inventory_pieces(id) ON DELETE SET NULL,
  FOREIGN KEY (cut_by_user_id) REFERENCES users(id) ON DELETE SET NULL,
  
  -- Indexes for performance
  INDEX idx_production_order (production_order_id),
  INDEX idx_rm_piece (rm_piece_id),
  INDEX idx_bom_item (bom_item_id),
  INDEX idx_cut_at (cut_at),
  INDEX idx_remaining_piece (remaining_piece_id),
  
  -- Composite index for audit queries
  INDEX idx_audit_trail (production_order_id, rm_piece_id, cut_at)
) COMMENT = 'Tracks cutting operations on RM pieces with waste and scrap generation';

-- Add constraints to ensure data integrity
ALTER TABLE rm_cutting_operations 
ADD CONSTRAINT chk_positive_cut_dimensions 
CHECK (cut_length > 0 AND cut_width > 0);

-- Add constraint to ensure cut dimensions don't exceed original piece dimensions
-- Note: This will be enforced at application level since we need to join with rm_inventory_pieces

-- Verify the table structure
DESCRIBE rm_cutting_operations;

-- Show the indexes created
SHOW INDEX FROM rm_cutting_operations;