USE trainer_attendance;

-- حقول إضافية للدورة من تقرير الدورات
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses' AND COLUMN_NAME = 'category');
SET @s := IF(@c = 0, "ALTER TABLE courses ADD COLUMN category VARCHAR(100) NULL AFTER title", 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses' AND COLUMN_NAME = 'type_name');
SET @s := IF(@c = 0, "ALTER TABLE courses ADD COLUMN type_name VARCHAR(255) NULL AFTER location_type", 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses' AND COLUMN_NAME = 'company');
SET @s := IF(@c = 0, "ALTER TABLE courses ADD COLUMN company VARCHAR(255) NULL AFTER type_name", 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses' AND COLUMN_NAME = 'sub_company');
SET @s := IF(@c = 0, "ALTER TABLE courses ADD COLUMN sub_company VARCHAR(255) NULL AFTER company", 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses' AND COLUMN_NAME = 'day_count');
SET @s := IF(@c = 0, "ALTER TABLE courses ADD COLUMN day_count INT NULL AFTER sub_company", 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses' AND COLUMN_NAME = 'delivery_mode');
SET @s := IF(@c = 0, "ALTER TABLE courses ADD COLUMN delivery_mode VARCHAR(100) NULL AFTER day_count", 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses' AND COLUMN_NAME = 'course_use');
SET @s := IF(@c = 0, "ALTER TABLE courses ADD COLUMN course_use TEXT NULL AFTER delivery_mode", 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses' AND COLUMN_NAME = 'import_key');
SET @s := IF(@c = 0, "ALTER TABLE courses ADD COLUMN import_key VARCHAR(191) NULL UNIQUE AFTER share_token", 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- حقول إضافية لتسجيل المتدرب من التقرير
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'enrollments' AND COLUMN_NAME = 'degree_score');
SET @s := IF(@c = 0, "ALTER TABLE enrollments ADD COLUMN degree_score VARCHAR(20) NULL AFTER formation", 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'enrollments' AND COLUMN_NAME = 'activity_role');
SET @s := IF(@c = 0, "ALTER TABLE enrollments ADD COLUMN activity_role VARCHAR(80) NULL AFTER degree_score", 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'enrollments' AND COLUMN_NAME = 'send_book');
SET @s := IF(@c = 0, "ALTER TABLE enrollments ADD COLUMN send_book VARCHAR(255) NULL AFTER activity_role", 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'enrollments' AND COLUMN_NAME = 'sub_book');
SET @s := IF(@c = 0, "ALTER TABLE enrollments ADD COLUMN sub_book VARCHAR(255) NULL AFTER send_book", 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'enrollments' AND COLUMN_NAME = 'result_book');
SET @s := IF(@c = 0, "ALTER TABLE enrollments ADD COLUMN result_book VARCHAR(255) NULL AFTER sub_book", 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'enrollments' AND COLUMN_NAME = 'participation_note');
SET @s := IF(@c = 0, "ALTER TABLE enrollments ADD COLUMN participation_note VARCHAR(255) NULL AFTER result_book", 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'enrollments' AND COLUMN_NAME = 'gender');
SET @s := IF(@c = 0, "ALTER TABLE enrollments ADD COLUMN gender VARCHAR(20) NULL AFTER participation_note", 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;