-- EnglishSpeak
-- Bổ sung thông tin hồ sơ dùng cho thống kê:
-- school_name = Trường thực tế
-- class_name  = Lớp thực tế
-- Hai cột này KHÔNG liên kết với Grade và KHÔNG dùng để vận hành lớp học.

SET @db_name = DATABASE();

SET @sql_school = (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = @db_name
              AND TABLE_NAME = 'students'
              AND COLUMN_NAME = 'school_name'
        ),
        'SELECT 1',
        'ALTER TABLE students ADD COLUMN school_name VARCHAR(255) NULL AFTER province'
    )
);

PREPARE stmt_school FROM @sql_school;
EXECUTE stmt_school;
DEALLOCATE PREPARE stmt_school;

SET @sql_class = (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = @db_name
              AND TABLE_NAME = 'students'
              AND COLUMN_NAME = 'class_name'
        ),
        'SELECT 1',
        'ALTER TABLE students ADD COLUMN class_name VARCHAR(50) NULL AFTER school_name'
    )
);

PREPARE stmt_class FROM @sql_class;
EXECUTE stmt_class;
DEALLOCATE PREPARE stmt_class;

-- Index phục vụ thống kê và lọc báo cáo.
SET @sql_school_index = (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = @db_name
              AND TABLE_NAME = 'students'
              AND INDEX_NAME = 'idx_students_school_name'
        ),
        'SELECT 1',
        'CREATE INDEX idx_students_school_name ON students (school_name)'
    )
);

PREPARE stmt_school_index FROM @sql_school_index;
EXECUTE stmt_school_index;
DEALLOCATE PREPARE stmt_school_index;

SET @sql_class_index = (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = @db_name
              AND TABLE_NAME = 'students'
              AND INDEX_NAME = 'idx_students_class_name'
        ),
        'SELECT 1',
        'CREATE INDEX idx_students_class_name ON students (class_name)'
    )
);

PREPARE stmt_class_index FROM @sql_class_index;
EXECUTE stmt_class_index;
DEALLOCATE PREPARE stmt_class_index;
