-- Migration 016: Add Low Stock Threshold
-- Date: 2026-01-14
-- Description: 
--   Adds low_stock_threshold field to products table for low stock alerts.
--   This allows setting a threshold per product to trigger low stock alerts.

-- ============================================
-- Add low_stock_threshold to products table
-- ============================================

SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'products'
    AND COLUMN_NAME = 'low_stock_threshold'
);

SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `products` ADD COLUMN `low_stock_threshold` DECIMAL(12, 3) NULL COMMENT ''Threshold quantity for low stock alerts'' AFTER `selling_price`',
  'SELECT "Column low_stock_threshold already exists in products table" AS message'
);

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

-- Add index for low stock queries
SET @idx_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'products'
    AND INDEX_NAME = 'idx_products_low_stock_threshold'
);

SET @sql = IF(@idx_exists = 0,
  'ALTER TABLE `products` ADD INDEX `idx_products_low_stock_threshold` (`low_stock_threshold`)',
  'SELECT "Index idx_products_low_stock_threshold already exists" AS message'
);

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

-- Verify the changes
SELECT 'Migration 016 completed: Low stock threshold added' AS status;
