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

USE student1;

-- First, backup any existing data
CREATE TABLE classes_backup AS SELECT * FROM classes;

-- Drop the unique constraint (actual name from the schema)
ALTER TABLE classes DROP INDEX unique_class_per_year;

-- Drop the grade_id index
ALTER TABLE classes DROP INDEX grade_id;

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

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

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

-- Clear existing data and add some common class names
TRUNCATE TABLE classes;

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);