-- Migration 020: Normalize BOM Schema - Remove Redundant Fields & Add Constraints
-- This migration removes denormalized fields and adds proper constraints for ACID compliance

-- ============================================================================
-- Step 1: Remove redundant fields from boms table
-- ============================================================================

DELIMITER //
CREATE PROCEDURE RemoveBOMColumnsIfExists()
BEGIN
    -- Remove category column if exists
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_SCHEMA = DATABASE() 
               AND TABLE_NAME = 'boms' 
               AND COLUMN_NAME = 'category') THEN
        ALTER TABLE `boms` DROP COLUMN `category`;
    END IF;
    
    -- Remove type column if exists
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_SCHEMA = DATABASE() 
               AND TABLE_NAME = 'boms' 
               AND COLUMN_NAME = 'type') THEN
        ALTER TABLE `boms` DROP COLUMN `type`;
    END IF;
END //
DELIMITER ;

CALL RemoveBOMColumnsIfExists();
DROP PROCEDURE RemoveBOMColumnsIfExists;

-- Add unique constraint to ensure one BOM per product (if not exists)
DELIMITER //
CREATE PROCEDURE AddUniqueConstraintIfNotExists()
BEGIN
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
                   WHERE TABLE_SCHEMA = DATABASE() 
                   AND TABLE_NAME = 'boms' 
                   AND CONSTRAINT_NAME = 'unique_bom_per_product') THEN
        ALTER TABLE `boms` ADD UNIQUE KEY `unique_bom_per_product` (`fg_product_id`);
    END IF;
END //
DELIMITER ;

CALL AddUniqueConstraintIfNotExists();
DROP PROCEDURE AddUniqueConstraintIfNotExists;

-- ============================================================================
-- Step 2: Remove redundant field from bom_items table
-- ============================================================================

DELIMITER //
CREATE PROCEDURE RemoveBOMItemColumnsIfExists()
BEGIN
    -- Remove use_dimensions column if exists
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_SCHEMA = DATABASE() 
               AND TABLE_NAME = 'bom_items' 
               AND COLUMN_NAME = 'use_dimensions') THEN
        ALTER TABLE `bom_items` DROP COLUMN `use_dimensions`;
    END IF;
END //
DELIMITER ;

CALL RemoveBOMItemColumnsIfExists();
DROP PROCEDURE RemoveBOMItemColumnsIfExists;

-- ============================================================================
-- Step 3: Add CHECK constraint to enforce data integrity
-- ============================================================================

-- Drop existing constraint if exists (MySQL 8.0.19+)
-- For older versions, this might fail silently, which is fine
SET @constraint_exists = (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'bom_items' 
    AND CONSTRAINT_NAME = 'chk_bom_item_type'
);

SET @sql = IF(@constraint_exists > 0,
    'ALTER TABLE `bom_items` DROP CHECK `chk_bom_item_type`',
    'SELECT 1'); -- Do nothing if constraint doesn't exist
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

ALTER TABLE `bom_items`
ADD CONSTRAINT `chk_bom_item_type` CHECK (
  (quantity_per_unit IS NOT NULL AND required_length IS NULL AND required_width IS NULL AND dimension_unit IS NULL) OR
  (quantity_per_unit IS NULL AND required_length IS NOT NULL AND required_width IS NOT NULL AND dimension_unit IS NOT NULL)
);

-- ============================================================================
-- Step 4: Verify table structure
-- ============================================================================

DESCRIBE boms;
DESCRIBE bom_items;

-- Show indexes and constraints
SHOW INDEX FROM boms;
SHOW INDEX FROM bom_items;

-- Show table constraints
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME IN ('boms', 'bom_items')
ORDER BY TABLE_NAME, CONSTRAINT_TYPE;
