/**
 * Migration 018: Add category and type columns to boms table
 * 
 * This migration adds the `category` and `type` fields to the BOM table
 * to support the new BOM structure:
 * - category: BOM Category (e.g., "Dresses", "Shirts", "Pants")
 * - type: BOM Type (e.g., "Wrap maxi", "Button-up midi", "Circular wrap midi sleeves")
 * 
 * Created: 2026-01-16
 */

SET @dbname = DATABASE();
SET @tablename = 'boms';
SET @columnname1 = 'category';
SET @columnname2 = 'type';

-- Check if category column exists, if not add it
SET @preparedStatement = (
  SELECT IF(
    (
      SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
      WHERE
        (table_name = @tablename)
        AND (table_schema = @dbname)
        AND (column_name = @columnname1)
    ) > 0,
    'SELECT 1',
    CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @columnname1, '` VARCHAR(100) NULL COMMENT "BOM Category (e.g., \\"Dresses\\", \\"Shirts\\")" AFTER `fg_product_id`;')
  )
);
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Check if type column exists, if not add it
SET @preparedStatement = (
  SELECT IF(
    (
      SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
      WHERE
        (table_name = @tablename)
        AND (table_schema = @dbname)
        AND (column_name = @columnname2)
    ) > 0,
    'SELECT 1',
    CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @columnname2, '` VARCHAR(200) NULL COMMENT "BOM Type (e.g., \\"Wrap maxi\\", \\"Button-up midi\\")" AFTER `category`;')
  )
);
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;
