SET @schema_name = DATABASE();

SET @ddl = IF (
    EXISTS (
        SELECT 1
        FROM information_schema.statistics
        WHERE table_schema = @schema_name
          AND table_name = 'student_registrations'
          AND index_name = 'student_registrations_otp_session_idx'
    ),
    'SELECT ''student_registrations_otp_session_idx already exists''',
    'ALTER TABLE `student_registrations` ADD INDEX `student_registrations_otp_session_idx` (`otp`, `session_no`)'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
    EXISTS (
        SELECT 1
        FROM information_schema.statistics
        WHERE table_schema = @schema_name
          AND table_name = 'student_registrations'
          AND index_name = 'student_registrations_user_exam_session_idx'
    ),
    'SELECT ''student_registrations_user_exam_session_idx already exists''',
    'ALTER TABLE `student_registrations` ADD INDEX `student_registrations_user_exam_session_idx` (`user_id`, `exam_id`, `session_no`)'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
    EXISTS (
        SELECT 1
        FROM information_schema.statistics
        WHERE table_schema = @schema_name
          AND table_name = 'student_registrations'
          AND index_name = 'student_registrations_school_exam_session_submit_idx'
    ),
    'SELECT ''student_registrations_school_exam_session_submit_idx already exists''',
    'ALTER TABLE `student_registrations` ADD INDEX `student_registrations_school_exam_session_submit_idx` (`school_id`, `exam_id`, `session_no`, `is_submitted`)'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
    EXISTS (
        SELECT 1
        FROM information_schema.statistics
        WHERE table_schema = @schema_name
          AND table_name = 'school_registerations'
          AND index_name = 'school_registerations_school_exam_idx'
    ),
    'SELECT ''school_registerations_school_exam_idx already exists''',
    'ALTER TABLE `school_registerations` ADD INDEX `school_registerations_school_exam_idx` (`school_id`, `exam_id`)'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
    EXISTS (
        SELECT 1
        FROM information_schema.statistics
        WHERE table_schema = @schema_name
          AND table_name = 'users_answers'
          AND index_name = 'users_answers_reg_question_idx'
    ),
    'SELECT ''users_answers_reg_question_idx already exists''',
    'ALTER TABLE `users_answers` ADD INDEX `users_answers_reg_question_idx` (`reg_id`, `question_id`)'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
    EXISTS (
        SELECT 1
        FROM information_schema.statistics
        WHERE table_schema = @schema_name
          AND table_name = 'users_answers'
          AND index_name = 'users_answers_user_reg_exam_idx'
    ),
    'SELECT ''users_answers_user_reg_exam_idx already exists''',
    'ALTER TABLE `users_answers` ADD INDEX `users_answers_user_reg_exam_idx` (`user_id`, `reg_id`, `exam_id`)'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
    EXISTS (
        SELECT 1
        FROM information_schema.statistics
        WHERE table_schema = @schema_name
          AND table_name = 'multimedia'
          AND index_name = 'multimedia_target_idx'
    ),
    'SELECT ''multimedia_target_idx already exists''',
    'ALTER TABLE `multimedia` ADD INDEX `multimedia_target_idx` (`target_type`, `target_id`)'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;