-- Migration 006: Add dimension tracking fields to products table
-- This migration adds support for dimension-based inventory tracking for Raw Materials (RM)

-- Add unit_of_measure column for default unit of measure per product
ALTER TABLE products 
ADD COLUMN unit_of_measure ENUM('inch', 'cm', 'm') DEFAULT 'm' 
COMMENT 'Default unit of measure for this product (can be overridden per inventory piece)';

-- Add track_by_dimensions column to enable dimension-based tracking
ALTER TABLE products 
ADD COLUMN track_by_dimensions BOOLEAN DEFAULT FALSE 
COMMENT 'Whether this product uses dimension-based tracking (mandatory TRUE for RM products)';

-- Create index for dimension tracking queries (product_type + track_by_dimensions)
CREATE INDEX idx_products_dimensions ON products(product_type, track_by_dimensions);

-- Update existing RM products to use dimension tracking (as per requirements)
-- This enforces the business rule that all RM products MUST use dimension-based tracking
UPDATE products 
SET track_by_dimensions = TRUE 
WHERE product_type = 'RM';

-- Add a comment to document the business rule
ALTER TABLE products 
COMMENT = 'Products table with dimension tracking support. RM products MUST have track_by_dimensions = TRUE';

-- Verify the changes
SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    IS_NULLABLE, 
    COLUMN_DEFAULT, 
    COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'products' 
AND COLUMN_NAME IN ('unit_of_measure', 'track_by_dimensions')
ORDER BY ORDINAL_POSITION;

-- Show updated RM products
SELECT 
    id, 
    name, 
    product_type, 
    track_by_dimensions, 
    unit_of_measure 
FROM products 
WHERE product_type = 'RM' 
LIMIT 5;