-- Fix character encoding for Arabic text
-- Run this script to fix the character encoding issues

-- Check current database character set
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME = DATABASE();

-- Check current table character set
SELECT TABLE_NAME, TABLE_COLLATION 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'scenes';

-- Check current column character sets
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'scenes' 
AND COLUMN_NAME IN ('action', 'facilityName', 'facilityOwnerName', 'facilityLocation', 'applicantName', 'workDescription', 'notes');

-- Fix database character set (replace 'your_database_name' with your actual database name)
-- ALTER DATABASE your_database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Fix scenes table character set
ALTER TABLE scenes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Fix specific columns that contain Arabic text
ALTER TABLE scenes 
MODIFY COLUMN action VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
MODIFY COLUMN applicantName VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
MODIFY COLUMN technician VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
MODIFY COLUMN workDescription TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
MODIFY COLUMN notes TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- If the new facility fields exist, fix them too
-- ALTER TABLE scenes 
-- MODIFY COLUMN facilityName VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
-- MODIFY COLUMN facilityOwnerName VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
-- MODIFY COLUMN facilityLocation TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Verify the changes
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'scenes' 
AND COLUMN_NAME IN ('action', 'facilityName', 'facilityOwnerName', 'facilityLocation', 'applicantName', 'workDescription', 'notes');
