-- Schema verification and alignment for items table
-- Ensures the items table matches the canonical schema defined in database/schema.sql
-- This script is idempotent - safe to run multiple times

USE invcount_db;

-- Verify current items table structure matches canonical schema
-- Canonical columns: id, Item_Code, Item_Description, Item_Group, Stores, Rack, Level, Bin, Uom, System_Qty, Status, Created_At, Updated_At

SELECT 'Current items table structure:' as info;
DESCRIBE items;

-- Ensure all canonical columns exist (safe additions only)
-- These use IF NOT EXISTS patterns for MySQL 10.0.2+ / MariaDB 10.0.2+
-- For older versions, run each ADD COLUMN individually and ignore duplicate column errors

-- Verify indexes exist
SELECT 'Current indexes on items table:' as info;
SHOW INDEX FROM items;

SELECT 'Schema verification COMPLETE. Items table should have columns: id, Item_Code, Item_Description, Item_Group, Stores, Rack, Level, Bin, Uom, System_Qty, Status, Created_At, Updated_At' as info;
