-- EnglishSpeak
-- Step 1: Add teacher-only CEFR Level metadata to Word Library
-- Safe migration: keeps the existing difficulty_level column and all current data.

SET @db_name := DATABASE();

SELECT COUNT(*) INTO @cefr_column_exists
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'words'
  AND COLUMN_NAME = 'cefr_level';

SET @sql := IF(
    @cefr_column_exists = 0,
    "ALTER TABLE `words`
     ADD COLUMN `cefr_level`
     ENUM('Pre-A1','A1','A2','B1','B2','C1','C2')
     NULL
     AFTER `difficulty_level`",
    "SELECT 'Column cefr_level already exists.' AS message"
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verification
SHOW COLUMNS FROM `words` LIKE 'cefr_level';
