-- Safe migration for classes scoped by school

SET @db := DATABASE();

SET @has_classes_school := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'classes' AND COLUMN_NAME = 'school_id'
);
SET @sql := IF(@has_classes_school = 0,
  'ALTER TABLE classes ADD COLUMN school_id INT NULL AFTER teacher_id',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_timetable_school := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'timetable_slots' AND COLUMN_NAME = 'school_id'
);
SET @sql := IF(@has_timetable_school = 0,
  'ALTER TABLE timetable_slots ADD COLUMN school_id INT NULL AFTER class_id',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE classes c
SET c.school_id = (
  SELECT us.school_id
  FROM user_schools us
  WHERE us.user_id = c.teacher_id
  ORDER BY us.is_primary DESC, us.id ASC
  LIMIT 1
)
WHERE c.school_id IS NULL AND c.teacher_id IS NOT NULL
  AND EXISTS (SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'user_schools');

UPDATE classes SET school_id = 1 WHERE school_id IS NULL;

UPDATE timetable_slots ts
JOIN classes c ON c.id = ts.class_id
SET ts.school_id = c.school_id
WHERE ts.school_id IS NULL;

CREATE INDEX idx_classes_school_id ON classes(school_id);
CREATE INDEX idx_timetable_school_id ON timetable_slots(school_id);
