-- Cleanup script: Remove any extra columns that may have been added to items table
-- This reverts to the canonical legacy structure defined in database/schema.sql
-- Only run this if extra lowercase columns were accidentally added

USE invcount_db;

-- Current structure check
SELECT 'Current items table structure before cleanup:' as info;
DESCRIBE items;

-- Drop extra columns if they exist (these are NOT part of the canonical schema)
-- The canonical schema uses: Item_Code, Item_Description, Item_Group, Stores, Rack, Level, Bin, Uom, System_Qty, Status
-- Extra columns that may have been added by mistake: description, category, unit_of_measure, location
SET @sql = '';

SELECT COUNT(*) INTO @col_exists FROM information_schema.columns 
WHERE table_schema = 'invcount_db' AND table_name = 'items' AND column_name = 'description';
IF @col_exists > 0 THEN
    SET @sql = CONCAT(@sql, 'DROP COLUMN `description`,');
END IF;

SELECT COUNT(*) INTO @col_exists FROM information_schema.columns 
WHERE table_schema = 'invcount_db' AND table_name = 'items' AND column_name = 'category';
IF @col_exists > 0 THEN
    SET @sql = CONCAT(@sql, 'DROP COLUMN `category`,');
END IF;

SELECT COUNT(*) INTO @col_exists FROM information_schema.columns 
WHERE table_schema = 'invcount_db' AND table_name = 'items' AND column_name = 'unit_of_measure';
IF @col_exists > 0 THEN
    SET @sql = CONCAT(@sql, 'DROP COLUMN `unit_of_measure`,');
END IF;

SELECT COUNT(*) INTO @col_exists FROM information_schema.columns 
WHERE table_schema = 'invcount_db' AND table_name = 'items' AND column_name = 'location';
IF @col_exists > 0 THEN
    SET @sql = CONCAT(@sql, 'DROP COLUMN `location`,');
END IF;

IF LENGTH(@sql) > 0 THEN
    SET @sql = CONCAT('ALTER TABLE items ', TRIM(TRAILING ',' FROM @sql));
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SELECT 'Extra columns removed.' as result;
ELSE
    SELECT 'No extra columns found. Table is clean.' as result;
END IF;

-- Final verification
SELECT 'Final items table structure:' as info;
DESCRIBE items;

SELECT COUNT(*) as total_items FROM items;
SELECT 'Cleanup COMPLETE - Canonical legacy columns only' as status;
