-- Migration: Add dimension fields to Purchase Order Items
-- Date: 2026-01-XX
-- Description: 
--   Adds dimension tracking fields (piece_length, piece_width, dimension_unit, pieces_count) 
--   to purchase_order_items table to support dimension-based RM products in Purchase Orders
--
-- This migration is similar to migration 010 (GRN items) for consistency

-- ============================================
-- Add dimension fields to purchase_order_items table
-- ============================================

-- Add piece_length column
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'purchase_order_items'
    AND COLUMN_NAME = 'piece_length'
);

SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `purchase_order_items` ADD COLUMN `piece_length` DECIMAL(12, 3) NULL COMMENT ''Length of each piece ordered'' AFTER `unit_cost`',
  'SELECT "Column piece_length already exists in purchase_order_items table" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add piece_width column
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'purchase_order_items'
    AND COLUMN_NAME = 'piece_width'
);

SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `purchase_order_items` ADD COLUMN `piece_width` DECIMAL(12, 3) NULL COMMENT ''Width of each piece ordered'' AFTER `piece_length`',
  'SELECT "Column piece_width already exists in purchase_order_items table" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add dimension_unit column
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'purchase_order_items'
    AND COLUMN_NAME = 'dimension_unit'
);

SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `purchase_order_items` ADD COLUMN `dimension_unit` ENUM(''inch'', ''cm'', ''m'') NULL COMMENT ''Unit of measure for piece dimensions'' AFTER `piece_width`',
  'SELECT "Column dimension_unit already exists in purchase_order_items table" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add pieces_count column
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'purchase_order_items'
    AND COLUMN_NAME = 'pieces_count'
);

SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `purchase_order_items` ADD COLUMN `pieces_count` INT NULL COMMENT ''Number of pieces with these dimensions'' AFTER `dimension_unit`',
  'SELECT "Column pieces_count already exists in purchase_order_items table" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================
-- Migration complete
-- ============================================
