-- FIWM performance / portability upgrade
-- Safe, additive indexes and charset normalization for MariaDB / MySQL.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE `ssm_corporation`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ADD INDEX `idx_ssm_corporation_name` (`ssm_name`(64)),
    ADD INDEX `idx_ssm_corporation_date` (`ssm_date`(20));

ALTER TABLE `ssm_member`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ADD INDEX `idx_ssm_member_corporation` (`ssm_corporation`),
    ADD INDEX `idx_ssm_member_name` (`ssm_name`(64)),
    ADD INDEX `idx_ssm_member_state` (`ssm_state`(16)),
    ADD INDEX `idx_ssm_member_type` (`ssm_type`);

ALTER TABLE `ssm_member_info`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ADD INDEX `idx_ssm_member_info_name` (`ssm_name`(64));

ALTER TABLE `ssm_crpprofile`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ADD INDEX `idx_ssm_crpprofile_corporation` (`ssm_corporation`),
    ADD INDEX `idx_ssm_crpprofile_name` (`ssm_name`(64));

ALTER TABLE `ssm_crpfreprofile`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ADD INDEX `idx_ssm_crpfreprofile_corporation` (`ssm_corporation`),
    ADD INDEX `idx_ssm_crpfreprofile_profile` (`ssm_profile`);

ALTER TABLE `ssm_crpallcards`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ADD INDEX `idx_ssm_crpallcards_corporation_status` (`ssm_corporation`, `ssm_status`),
    ADD INDEX `idx_ssm_crpallcards_corporation_profile` (`ssm_corporation`, `ssm_profile`),
    ADD INDEX `idx_ssm_crpallcards_date` (`ssm_date`);

ALTER TABLE `ssm_crpfrecards`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ADD INDEX `idx_ssm_crpfrecards_corporation_status` (`ssm_corporation`, `ssm_status`(16)),
    ADD INDEX `idx_ssm_crpfrecards_corporation_profile` (`ssm_corporation`, `ssm_profile`),
    ADD INDEX `idx_ssm_crpfrecards_date` (`ssm_date`(20));

ALTER TABLE `ssm_crpusrcards`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ADD INDEX `idx_ssm_crpusrcards_member_corporation` (`ssm_member_id`, `ssm_corporation`),
    ADD INDEX `idx_ssm_crpusrcards_corporation_profile` (`ssm_corporation`, `ssm_profile`),
    ADD INDEX `idx_ssm_crpusrcards_status` (`ssm_status`),
    ADD INDEX `idx_ssm_crpusrcards_date` (`ssm_date`);

ALTER TABLE `ssm_freusrcards`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ADD INDEX `idx_ssm_freusrcards_member_corporation` (`ssm_member_id`, `ssm_corporation`),
    ADD INDEX `idx_ssm_freusrcards_profile` (`ssm_profile`),
    ADD INDEX `idx_ssm_freusrcards_status` (`ssm_status`(16));

ALTER TABLE `ssm_prfusrcount`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ADD INDEX `idx_ssm_prfusrcount_member_profile` (`ssm_member_id`, `ssm_profile`),
    ADD INDEX `idx_ssm_prfusrcount_corporation` (`ssm_corporation`);

ALTER TABLE `ssm_permission`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ADD INDEX `idx_ssm_permission_corporation_member` (`ssm_corporation`, `ssm_member`);

ALTER TABLE `ssm_prepaid_profiles`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ADD INDEX `idx_ssm_prepaid_profiles_corporation` (`ssm_corporation`),
    ADD INDEX `idx_ssm_prepaid_profiles_name` (`ssm_name`);

ALTER TABLE `ssm_prepaid_cards`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ADD INDEX `idx_ssm_prepaid_cards_member_corporation` (`ssm_member_id`, `ssm_corporation`),
    ADD INDEX `idx_ssm_prepaid_cards_profile_status` (`ssm_profile`, `ssm_status`),
    ADD INDEX `idx_ssm_prepaid_cards_user` (`ssm_user`);

SET FOREIGN_KEY_CHECKS = 1;