-- Fix duplicate inventory records caused by size_id removal
-- This script consolidates duplicate records by summing quantities

-- Step 1: Create a temporary table with consolidated inventory
CREATE TEMPORARY TABLE temp_inventory_consolidated AS
SELECT 
    product_id,
    location_id,
    SUM(quantity) as total_quantity,
    MAX(min_quantity) as min_quantity,
    MAX(last_updated) as last_updated,
    GROUP_CONCAT(remarks SEPARATOR '; ') as combined_remarks
FROM inventory
GROUP BY product_id, location_id;

-- Step 2: Delete all existing inventory records
DELETE FROM inventory;

-- Step 3: Insert consolidated records back
INSERT INTO inventory (product_id, location_id, quantity, min_quantity, last_updated, remarks)
SELECT 
    product_id,
    location_id,
    total_quantity,
    min_quantity,
    last_updated,
    NULLIF(combined_remarks, '')
FROM temp_inventory_consolidated;

-- Step 4: Ensure the unique constraint exists
-- First, try to drop it if it exists (ignore error if it doesn't)
SET @exist := (SELECT COUNT(*) 
               FROM information_schema.statistics 
               WHERE table_name = 'inventory' 
               AND index_name = 'unique_product_location' 
               AND table_schema = DATABASE());
SET @sql := IF(@exist > 0, 
               'ALTER TABLE inventory DROP INDEX unique_product_location', 
               'SELECT "Index unique_product_location does not exist"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Now add the unique constraint
ALTER TABLE inventory ADD UNIQUE KEY `unique_product_location` (`product_id`, `location_id`);

-- Step 5: Verify the fix
SELECT 
    'Duplicate Check' as check_type,
    COUNT(*) as total_records,
    COUNT(DISTINCT CONCAT(product_id, '-', location_id)) as unique_combinations,
    COUNT(*) - COUNT(DISTINCT CONCAT(product_id, '-', location_id)) as duplicates
FROM inventory;

-- Show consolidated inventory
SELECT 
    p.part_number,
    p.name as product_name,
    l.name as location_name,
    i.quantity,
    i.min_quantity
FROM inventory i
JOIN products p ON i.product_id = p.id
JOIN locations l ON i.location_id = l.id
ORDER BY p.part_number, l.name;
