SET @has_dest_idx := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'bank_portability_policies'
      AND index_name = 'idx_bank_port_dest_fk'
);

SET @create_dest_idx_sql := IF(
    @has_dest_idx > 0,
    'DO 0',
    'ALTER TABLE bank_portability_policies ADD INDEX idx_bank_port_dest_fk (bank_destination_id)'
);

PREPARE dest_idx_stmt FROM @create_dest_idx_sql;
EXECUTE dest_idx_stmt;
DEALLOCATE PREPARE dest_idx_stmt;

SET @has_origin_idx := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'bank_portability_policies'
      AND index_name = 'idx_bank_port_origin_fk'
);

SET @create_origin_idx_sql := IF(
    @has_origin_idx > 0,
    'DO 0',
    'ALTER TABLE bank_portability_policies ADD INDEX idx_bank_port_origin_fk (bank_origin_id)'
);

PREPARE origin_idx_stmt FROM @create_origin_idx_sql;
EXECUTE origin_idx_stmt;
DEALLOCATE PREPARE origin_idx_stmt;

SET @has_unique := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'bank_portability_policies'
      AND index_name = 'uniq_bank_portability_pair'
);

SET @drop_unique_sql := IF(
    @has_unique = 0,
    'DO 0',
    'ALTER TABLE bank_portability_policies DROP INDEX uniq_bank_portability_pair'
);

PREPARE drop_stmt FROM @drop_unique_sql;
EXECUTE drop_stmt;
DEALLOCATE PREPARE drop_stmt;

SET @has_idx := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'bank_portability_policies'
      AND index_name = 'idx_bank_portability_destination_origin'
);

SET @create_idx_sql := IF(
    @has_idx > 0,
    'DO 0',
    'ALTER TABLE bank_portability_policies ADD INDEX idx_bank_portability_destination_origin (bank_destination_id, bank_origin_id)'
);

PREPARE idx_stmt FROM @create_idx_sql;
EXECUTE idx_stmt;
DEALLOCATE PREPARE idx_stmt;