-- Migration 007: Create RM Inventory Pieces table
-- This migration creates the table for tracking individual pieces of Raw Materials with dimensions

-- Create rm_inventory_pieces table
CREATE TABLE IF NOT EXISTS rm_inventory_pieces (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  product_id BIGINT NOT NULL,
  grn_item_id BIGINT NULL COMMENT 'Link to original GRN item that created this piece',
  piece_number INT NOT NULL COMMENT 'Piece number within a purchase (e.g., piece 1 of 2)',
  
  -- Dimensions
  length DECIMAL(12, 3) NOT NULL COMMENT 'Length of the piece',
  width DECIMAL(12, 3) NOT NULL COMMENT 'Width of the piece',
  unit ENUM('inch', 'cm', 'm') NOT NULL DEFAULT 'm' COMMENT 'Unit of measure for dimensions',
  
  -- Status tracking
  status ENUM('FULL', 'USABLE', 'WASTE', 'SCRAP') NOT NULL DEFAULT 'FULL' 
    COMMENT 'FULL=unused, USABLE=partially cut but usable, WASTE=reusable leftover, SCRAP=unusable',
  
  -- Current usable dimensions (after cuts)
  usable_length DECIMAL(12, 3) NULL COMMENT 'Current usable length after cuts (NULL for FULL pieces)',
  usable_width DECIMAL(12, 3) NULL COMMENT 'Current usable width after cuts (NULL for FULL pieces)',
  
  -- Scrap dimensions (to be written off)
  scrap_length DECIMAL(12, 3) DEFAULT 0 COMMENT 'Length of scrap portion',
  scrap_width DECIMAL(12, 3) DEFAULT 0 COMMENT 'Width of scrap portion',
  
  -- Metadata
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  -- Foreign key constraints
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (grn_item_id) REFERENCES grn_items(id) ON DELETE SET NULL,
  
  -- Indexes for performance
  INDEX idx_product_status (product_id, status),
  INDEX idx_status (status),
  INDEX idx_dimensions (length, width, unit),
  INDEX idx_usable_dimensions (usable_length, usable_width),
  INDEX idx_grn_item (grn_item_id),
  
  -- Composite index for allocation queries
  INDEX idx_allocation (product_id, status, usable_length, usable_width)
) COMMENT = 'Individual pieces of Raw Materials tracked by dimensions';

-- Add constraints to ensure data integrity
ALTER TABLE rm_inventory_pieces 
ADD CONSTRAINT chk_positive_dimensions 
CHECK (length > 0 AND width > 0);

ALTER TABLE rm_inventory_pieces 
ADD CONSTRAINT chk_usable_dimensions 
CHECK (
  (status = 'FULL' AND usable_length IS NULL AND usable_width IS NULL) OR
  (status != 'FULL' AND usable_length IS NOT NULL AND usable_width IS NOT NULL AND usable_length >= 0 AND usable_width >= 0)
);

ALTER TABLE rm_inventory_pieces 
ADD CONSTRAINT chk_scrap_dimensions 
CHECK (scrap_length >= 0 AND scrap_width >= 0);

-- Verify the table structure
DESCRIBE rm_inventory_pieces;

-- Show the indexes created
SHOW INDEX FROM rm_inventory_pieces;