-- Fix collation mismatch between transactions and payment_items tables
-- This script ensures both tables use the same collation for payment_item_id

-- First, check current collations (for reference)
-- SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
-- FROM INFORMATION_SCHEMA.COLUMNS 
-- WHERE TABLE_SCHEMA = DATABASE() 
-- AND COLUMN_NAME = 'payment_item_id';

-- Fix payment_items.payment_item_id collation
ALTER TABLE payment_items 
MODIFY COLUMN payment_item_id VARCHAR(50) 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci 
NOT NULL;

-- Fix transactions.payment_item_id collation
ALTER TABLE transactions 
MODIFY COLUMN payment_item_id VARCHAR(50) 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci 
NOT NULL;

-- Verify the fix
-- SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
-- FROM INFORMATION_SCHEMA.COLUMNS 
-- WHERE TABLE_SCHEMA = DATABASE() 
-- AND COLUMN_NAME = 'payment_item_id';

