-- Safe migration for stronger school separation on students and preparations

SET @db := DATABASE();

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

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

UPDATE students s
JOIN classes c ON c.id = s.class_id
SET s.school_id = c.school_id
WHERE s.school_id IS NULL AND c.school_id IS NOT NULL;

UPDATE lesson_preparations lp
JOIN classes c ON c.id = lp.class_id
SET lp.school_id = c.school_id
WHERE lp.school_id IS NULL AND c.school_id IS NOT NULL;

SET @has_students_idx := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'students' AND INDEX_NAME = 'idx_students_school'
);
SET @sql := IF(@has_students_idx = 0,
  'ALTER TABLE students ADD INDEX idx_students_school (school_id)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_lp_idx := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'lesson_preparations' AND INDEX_NAME = 'idx_lp_school'
);
SET @sql := IF(@has_lp_idx = 0,
  'ALTER TABLE lesson_preparations ADD INDEX idx_lp_school (school_id)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
