-- Fix Collation Issues for Special Access Links
-- Run these commands on your live database to fix collation mismatches

-- Step 1: Check your database's default collation
SELECT @@collation_database AS database_collation;

-- Step 2: Based on the result above, choose the appropriate commands:

-- If your database uses utf8mb4_general_ci, run these:
-- ALTER TABLE special_access_links CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- ALTER TABLE migrations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- ALTER TABLE registrations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- If your database uses utf8mb4_unicode_ci, run these:
-- ALTER TABLE special_access_links CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- ALTER TABLE migrations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- ALTER TABLE registrations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- If your database uses a different utf8mb4 collation, adapt the commands above

-- Step 3: Verify the fix worked
SHOW CREATE TABLE special_access_links;
SHOW CREATE TABLE migrations;
SHOW CREATE TABLE registrations;

-- Step 4: Test that queries work without collation errors
SELECT COUNT(*) FROM special_access_links LIMIT 1;

-- Test JOIN query (common source of collation errors)
SELECT COUNT(*) FROM special_access_links sal
LEFT JOIN registrations r ON r.referral_code = sal.link_code
LIMIT 1;
