-- Migration 013: Remove Product Variants System (FIXED VERSION)
-- Date: 2026-01-14
-- Description: 
--   Removes product_variants table and all variant_id foreign keys from related tables.
--   This is a major restructuring to simplify the system - variants are no longer needed.
--
-- WARNING: This migration will DELETE all product variant data!
-- Make sure to backup your database before running this migration.
--
-- FIXES:
--   - Properly handles unique index on inventories table
--   - Consolidates duplicate entries when removing variant_id
--   - Uses dynamic constraint name detection

-- ============================================
-- Step 1: Remove variant_id foreign keys from all tables
-- ============================================

-- Remove variant_id from sale_items
SET @fk_name = (
  SELECT CONSTRAINT_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'sale_items'
    AND COLUMN_NAME = 'variant_id'
    AND REFERENCED_TABLE_NAME = 'product_variants'
  LIMIT 1
);

SET @sql = IF(@fk_name IS NOT NULL,
  CONCAT('ALTER TABLE `sale_items` DROP FOREIGN KEY `', @fk_name, '`'),
  'SELECT "Foreign key for sale_items.variant_id does not exist" AS message'
);

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

-- Drop variant_id column from sale_items
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'sale_items'
    AND COLUMN_NAME = 'variant_id'
);

SET @sql = IF(@col_exists > 0,
  'ALTER TABLE `sale_items` DROP COLUMN `variant_id`',
  'SELECT "Column variant_id does not exist in sale_items table" AS message'
);

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

-- Remove variant_id from inventory_items
SET @fk_name = (
  SELECT CONSTRAINT_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'inventory_items'
    AND COLUMN_NAME = 'variant_id'
    AND REFERENCED_TABLE_NAME = 'product_variants'
  LIMIT 1
);

SET @sql = IF(@fk_name IS NOT NULL,
  CONCAT('ALTER TABLE `inventory_items` DROP FOREIGN KEY `', @fk_name, '`'),
  'SELECT "Foreign key for inventory_items.variant_id does not exist" AS message'
);

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

-- Drop variant_id column from inventory_items
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'inventory_items'
    AND COLUMN_NAME = 'variant_id'
);

SET @sql = IF(@col_exists > 0,
  'ALTER TABLE `inventory_items` DROP COLUMN `variant_id`',
  'SELECT "Column variant_id does not exist in inventory_items table" AS message'
);

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

-- Remove variant_id from inventories
-- First, consolidate duplicate entries (combine quantities for same product_id)
-- This handles the case where multiple variant_ids map to the same product_id
UPDATE `inventories` i1
INNER JOIN `inventories` i2 ON i1.product_id = i2.product_id AND i1.id < i2.id
SET i1.quantity = i1.quantity + i2.quantity,
    i1.reorder_level = GREATEST(i1.reorder_level, i2.reorder_level)
WHERE i1.variant_id IS NOT NULL AND i2.variant_id IS NOT NULL
  AND EXISTS (
    SELECT 1 FROM product_variants pv1, product_variants pv2
    WHERE pv1.id = i1.variant_id AND pv2.id = i2.variant_id
    AND pv1.product_id = pv2.product_id
  );

-- Delete duplicate entries (keep the first one with combined quantity)
DELETE i2 FROM `inventories` i1
INNER JOIN `inventories` i2 ON i1.product_id = i2.product_id AND i1.id < i2.id
WHERE i1.variant_id IS NOT NULL AND i2.variant_id IS NOT NULL
  AND EXISTS (
    SELECT 1 FROM product_variants pv1, product_variants pv2
    WHERE pv1.id = i1.variant_id AND pv2.id = i2.variant_id
    AND pv1.product_id = pv2.product_id
  );

-- Drop the unique index that includes variant_id
SET @idx_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'inventories'
    AND INDEX_NAME = 'inventories_product_id_variant_id'
);

SET @sql = IF(@idx_exists > 0,
  'ALTER TABLE `inventories` DROP INDEX `inventories_product_id_variant_id`',
  'SELECT "Index inventories_product_id_variant_id does not exist" AS message'
);

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

-- Now drop the foreign key constraint
SET @fk_name = (
  SELECT CONSTRAINT_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'inventories'
    AND COLUMN_NAME = 'variant_id'
    AND REFERENCED_TABLE_NAME = 'product_variants'
  LIMIT 1
);

SET @sql = IF(@fk_name IS NOT NULL,
  CONCAT('ALTER TABLE `inventories` DROP FOREIGN KEY `', @fk_name, '`'),
  'SELECT "Foreign key for inventories.variant_id does not exist" AS message'
);

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

-- Drop variant_id column from inventories
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'inventories'
    AND COLUMN_NAME = 'variant_id'
);

SET @sql = IF(@col_exists > 0,
  'ALTER TABLE `inventories` DROP COLUMN `variant_id`',
  'SELECT "Column variant_id does not exist in inventories table" AS message'
);

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

-- Remove variant_id from inventory_movements
SET @fk_name = (
  SELECT CONSTRAINT_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'inventory_movements'
    AND COLUMN_NAME = 'variant_id'
    AND REFERENCED_TABLE_NAME = 'product_variants'
  LIMIT 1
);

SET @sql = IF(@fk_name IS NOT NULL,
  CONCAT('ALTER TABLE `inventory_movements` DROP FOREIGN KEY `', @fk_name, '`'),
  'SELECT "Foreign key for inventory_movements.variant_id does not exist" AS message'
);

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

-- Drop variant_id column from inventory_movements
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'inventory_movements'
    AND COLUMN_NAME = 'variant_id'
);

SET @sql = IF(@col_exists > 0,
  'ALTER TABLE `inventory_movements` DROP COLUMN `variant_id`',
  'SELECT "Column variant_id does not exist in inventory_movements table" AS message'
);

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

-- Remove variant_id from product_prices
SET @fk_name = (
  SELECT CONSTRAINT_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'product_prices'
    AND COLUMN_NAME = 'variant_id'
    AND REFERENCED_TABLE_NAME = 'product_variants'
  LIMIT 1
);

SET @sql = IF(@fk_name IS NOT NULL,
  CONCAT('ALTER TABLE `product_prices` DROP FOREIGN KEY `', @fk_name, '`'),
  'SELECT "Foreign key for product_prices.variant_id does not exist" AS message'
);

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

-- Drop variant_id column from product_prices
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'product_prices'
    AND COLUMN_NAME = 'variant_id'
);

SET @sql = IF(@col_exists > 0,
  'ALTER TABLE `product_prices` DROP COLUMN `variant_id`',
  'SELECT "Column variant_id does not exist in product_prices table" AS message'
);

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

-- Remove variant_id from grn_items
SET @fk_name = (
  SELECT CONSTRAINT_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'grn_items'
    AND COLUMN_NAME = 'variant_id'
    AND REFERENCED_TABLE_NAME = 'product_variants'
  LIMIT 1
);

SET @sql = IF(@fk_name IS NOT NULL,
  CONCAT('ALTER TABLE `grn_items` DROP FOREIGN KEY `', @fk_name, '`'),
  'SELECT "Foreign key for grn_items.variant_id does not exist" AS message'
);

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

-- Drop variant_id column from grn_items
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'grn_items'
    AND COLUMN_NAME = 'variant_id'
);

SET @sql = IF(@col_exists > 0,
  'ALTER TABLE `grn_items` DROP COLUMN `variant_id`',
  'SELECT "Column variant_id does not exist in grn_items table" AS message'
);

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

-- Remove variant_id from purchase_order_items
SET @fk_name = (
  SELECT CONSTRAINT_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'purchase_order_items'
    AND COLUMN_NAME = 'variant_id'
    AND REFERENCED_TABLE_NAME = 'product_variants'
  LIMIT 1
);

SET @sql = IF(@fk_name IS NOT NULL,
  CONCAT('ALTER TABLE `purchase_order_items` DROP FOREIGN KEY `', @fk_name, '`'),
  'SELECT "Foreign key for purchase_order_items.variant_id does not exist" AS message'
);

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

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

SET @sql = IF(@col_exists > 0,
  'ALTER TABLE `purchase_order_items` DROP COLUMN `variant_id`',
  'SELECT "Column variant_id does not exist in purchase_order_items table" AS message'
);

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

-- ============================================
-- Step 2: Update BOMs table (fg_variant_id -> fg_product_id)
-- ============================================

-- Add fg_product_id column to boms (if it doesn't exist)
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'boms'
    AND COLUMN_NAME = 'fg_product_id'
);

SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `boms` ADD COLUMN `fg_product_id` BIGINT NULL COMMENT ''Foreign key to products table (replaces fg_variant_id)'' AFTER `fg_variant_id`',
  'SELECT "Column fg_product_id already exists in boms table" AS message'
);

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

-- Migrate data from fg_variant_id to fg_product_id
-- This assumes product_variants.product_id maps to products.id
UPDATE `boms` b
INNER JOIN `product_variants` pv ON b.`fg_variant_id` = pv.`id`
SET b.`fg_product_id` = pv.`product_id`
WHERE b.`fg_variant_id` IS NOT NULL AND b.`fg_product_id` IS NULL;

-- Remove foreign key constraint on fg_variant_id
SET @fk_name = (
  SELECT CONSTRAINT_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'boms'
    AND COLUMN_NAME = 'fg_variant_id'
    AND REFERENCED_TABLE_NAME = 'product_variants'
  LIMIT 1
);

SET @sql = IF(@fk_name IS NOT NULL,
  CONCAT('ALTER TABLE `boms` DROP FOREIGN KEY `', @fk_name, '`'),
  'SELECT "Foreign key for boms.fg_variant_id does not exist" AS message'
);

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

-- Drop fg_variant_id column from boms
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'boms'
    AND COLUMN_NAME = 'fg_variant_id'
);

SET @sql = IF(@col_exists > 0,
  'ALTER TABLE `boms` DROP COLUMN `fg_variant_id`',
  'SELECT "Column fg_variant_id does not exist in boms table" AS message'
);

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

-- Add foreign key constraint for fg_product_id
SET @fk_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'boms'
    AND COLUMN_NAME = 'fg_product_id'
    AND REFERENCED_TABLE_NAME = 'products'
);

SET @sql = IF(@fk_exists = 0,
  'ALTER TABLE `boms` ADD CONSTRAINT `boms_fg_product_id_fk` FOREIGN KEY (`fg_product_id`) REFERENCES `products` (`id`) ON UPDATE CASCADE ON DELETE CASCADE',
  'SELECT "Foreign key boms_fg_product_id_fk already exists" AS message'
);

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

-- Make fg_product_id NOT NULL (after data migration)
SET @sql = 'ALTER TABLE `boms` MODIFY COLUMN `fg_product_id` BIGINT NOT NULL';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================
-- Step 3: Update production_orders table (fg_variant_id -> fg_product_id)
-- ============================================

-- Add fg_product_id column to production_orders (if it doesn't exist)
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'production_orders'
    AND COLUMN_NAME = 'fg_product_id'
);

SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `production_orders` ADD COLUMN `fg_product_id` BIGINT NULL COMMENT ''Foreign key to products table (replaces fg_variant_id)'' AFTER `fg_variant_id`',
  'SELECT "Column fg_product_id already exists in production_orders table" AS message'
);

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

-- Migrate data from fg_variant_id to fg_product_id
UPDATE `production_orders` po
INNER JOIN `product_variants` pv ON po.`fg_variant_id` = pv.`id`
SET po.`fg_product_id` = pv.`product_id`
WHERE po.`fg_variant_id` IS NOT NULL AND po.`fg_product_id` IS NULL;

-- Remove foreign key constraint on fg_variant_id
SET @fk_name = (
  SELECT CONSTRAINT_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'production_orders'
    AND COLUMN_NAME = 'fg_variant_id'
    AND REFERENCED_TABLE_NAME = 'product_variants'
  LIMIT 1
);

SET @sql = IF(@fk_name IS NOT NULL,
  CONCAT('ALTER TABLE `production_orders` DROP FOREIGN KEY `', @fk_name, '`'),
  'SELECT "Foreign key for production_orders.fg_variant_id does not exist" AS message'
);

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

-- Drop fg_variant_id column from production_orders
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'production_orders'
    AND COLUMN_NAME = 'fg_variant_id'
);

SET @sql = IF(@col_exists > 0,
  'ALTER TABLE `production_orders` DROP COLUMN `fg_variant_id`',
  'SELECT "Column fg_variant_id does not exist in production_orders table" AS message'
);

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

-- Add foreign key constraint for fg_product_id
SET @fk_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'production_orders'
    AND COLUMN_NAME = 'fg_product_id'
    AND REFERENCED_TABLE_NAME = 'products'
);

SET @sql = IF(@fk_exists = 0,
  'ALTER TABLE `production_orders` ADD CONSTRAINT `production_orders_fg_product_id_fk` FOREIGN KEY (`fg_product_id`) REFERENCES `products` (`id`) ON UPDATE CASCADE ON DELETE CASCADE',
  'SELECT "Foreign key production_orders_fg_product_id_fk already exists" AS message'
);

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

-- Make fg_product_id NOT NULL (after data migration)
SET @sql = 'ALTER TABLE `production_orders` MODIFY COLUMN `fg_product_id` BIGINT NOT NULL';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================
-- Step 4: Drop product_variants table
-- ============================================

DROP TABLE IF EXISTS `product_variants`;

-- Verify the changes
SELECT 'Migration 013 completed: Product variants removed' AS status;
