-- Add grade_level column to classes table
-- This helps identify higher classes (9th to 12th grade) for fourth subject management

ALTER TABLE `classes` 
ADD COLUMN `grade_level` INT DEFAULT NULL COMMENT 'Numeric grade level (1-12, where 11=Class XI, 12=Class XII)' 
AFTER `section`;

-- Add index for better query performance
ALTER TABLE `classes` 
ADD INDEX `idx_grade_level` (`grade_level`);

-- Update existing classes with grade levels based on name patterns
-- Update Class 11 (একাদশ, XI, 11, Eleven)
UPDATE `classes` 
SET `grade_level` = 11 
WHERE `name` LIKE '%একাদশ%' 
   OR `name` LIKE '%11%' 
   OR `name` LIKE '%XI%' 
   OR `name` LIKE '%eleven%';

-- Update Class 12 (দ্বাদশ, XII, 12, Twelve)
UPDATE `classes` 
SET `grade_level` = 12 
WHERE `name` LIKE '%দ্বাদশ%' 
   OR `name` LIKE '%12%' 
   OR `name` LIKE '%XII%' 
   OR `name` LIKE '%twelve%';

-- Update other classes based on common patterns
UPDATE `classes` SET `grade_level` = 1 WHERE `name` LIKE '%প্রথম%' OR `name` LIKE '%1st%' OR `name` LIKE '%One%';
UPDATE `classes` SET `grade_level` = 2 WHERE `name` LIKE '%দ্বিতীয়%' OR `name` LIKE '%2nd%' OR `name` LIKE '%Two%';
UPDATE `classes` SET `grade_level` = 3 WHERE `name` LIKE '%তৃতীয়%' OR `name` LIKE '%3rd%' OR `name` LIKE '%Three%';
UPDATE `classes` SET `grade_level` = 4 WHERE `name` LIKE '%চতুর্থ%' OR `name` LIKE '%4th%' OR `name` LIKE '%Four%';
UPDATE `classes` SET `grade_level` = 5 WHERE `name` LIKE '%পঞ্চম%' OR `name` LIKE '%5th%' OR `name` LIKE '%Five%';
UPDATE `classes` SET `grade_level` = 6 WHERE `name` LIKE '%ষষ্ঠ%' OR `name` LIKE '%6th%' OR `name` LIKE '%Six%';
UPDATE `classes` SET `grade_level` = 7 WHERE `name` LIKE '%সপ্তম%' OR `name` LIKE '%7th%' OR `name` LIKE '%Seven%';
UPDATE `classes` SET `grade_level` = 8 WHERE `name` LIKE '%অষ্টম%' OR `name` LIKE '%8th%' OR `name` LIKE '%Eight%';
UPDATE `classes` SET `grade_level` = 9 WHERE `name` LIKE '%নবম%' OR `name` LIKE '%9th%' OR `name` LIKE '%Nine%';
UPDATE `classes` SET `grade_level` = 10 WHERE `name` LIKE '%দশম%' OR `name` LIKE '%10th%' OR `name` LIKE '%Ten%';

-- Display results
SELECT 
    id, 
    name, 
    section, 
    grade_level,
    CASE 
        WHEN grade_level >= 11 THEN 'Higher Secondary (Fourth Subject Applicable)'
        WHEN grade_level >= 9 THEN 'Secondary'
        WHEN grade_level >= 6 THEN 'Junior Secondary'
        ELSE 'Primary'
    END as classification
FROM `classes` 
ORDER BY grade_level, name;
