-- Refactor classes table to be independent of year and grade
-- This makes classes reusable across all academic years and grades

USE student1;

-- Step 1: Temporarily disable foreign key checks to avoid constraint issues
SET FOREIGN_KEY_CHECKS = 0;

-- Step 2: Backup existing data
CREATE TABLE classes_backup AS SELECT * FROM classes;

-- Step 3: Drop the unique constraint 
ALTER TABLE classes DROP INDEX unique_class_per_year;

-- Step 4: Drop the grade_id index
ALTER TABLE classes DROP INDEX grade_id;

-- Step 5: Remove year_id and grade_id columns
ALTER TABLE classes DROP COLUMN year_id;
ALTER TABLE classes DROP COLUMN grade_id;

-- Step 6: Modify code column to be more descriptive (name) and longer
ALTER TABLE classes CHANGE COLUMN code name VARCHAR(50) NOT NULL;

-- Step 7: Add unique constraint on name only
ALTER TABLE classes ADD UNIQUE KEY unique_class_name (name);

-- Step 8: Clear existing data and add some common class names
DELETE FROM classes;

-- Step 9: Insert common class names
INSERT INTO classes (name, enabled) VALUES 
('A', 1),
('B', 1), 
('C', 1),
('D', 1),
('Red', 1),
('Blue', 1),
('Green', 1),
('Yellow', 1),
('Alpha', 1),
('Beta', 1),
('Gamma', 1),
('Delta', 1);

-- Step 10: Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;