-- 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 foreign key constraints and unique constraint
ALTER TABLE classes DROP FOREIGN KEY classes_ibfk_1;
ALTER TABLE classes DROP FOREIGN KEY classes_ibfk_2;
ALTER TABLE classes DROP INDEX unique_year_grade_class;

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

-- Add some common class names
INSERT IGNORE 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);

-- Update enrollments table to ensure it properly links
-- (The enrollments table already has separate year_id, grade_id, class_id columns)

-- Clean up backup table (optional - remove comment to keep backup)
-- DROP TABLE classes_backup;