-- EnglishSpeak: chuẩn hóa phạm vi học sinh theo province_id + school_id
-- Chạy 1 lần trong phpMyAdmin / database englishspeak.

SET @has_province_id := (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'students'
      AND column_name = 'province_id'
);
SET @sql := IF(
    @has_province_id = 0,
    'ALTER TABLE students ADD COLUMN province_id INT UNSIGNED NULL AFTER academic_year_id',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_school_id := (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'students'
      AND column_name = 'school_id'
);
SET @sql := IF(
    @has_school_id = 0,
    'ALTER TABLE students ADD COLUMN school_id BIGINT UNSIGNED NULL AFTER province_id',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Đồng bộ ID từ tên cũ.
UPDATE students st
LEFT JOIN provinces p
       ON TRIM(p.name) = TRIM(st.province)
LEFT JOIN schools s
       ON s.province_id = p.id
      AND TRIM(s.name) = TRIM(st.school_name)
SET st.province_id = COALESCE(st.province_id, p.id),
    st.school_id   = COALESCE(st.school_id, s.id)
WHERE (st.province_id IS NULL OR st.school_id IS NULL)
  AND p.id IS NOT NULL
  AND s.id IS NOT NULL;

-- Index phục vụ lọc theo trường.
SET @has_scope_index := (
    SELECT COUNT(*) FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'students'
      AND index_name = 'idx_students_school_scope'
);
SET @sql := IF(
    @has_scope_index = 0,
    'ALTER TABLE students ADD INDEX idx_students_school_scope (province_id, school_id)',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Kiểm tra kết quả.
SELECT
    id,
    full_name,
    province_id,
    school_id,
    province,
    school_name
FROM students
ORDER BY id;
