/*
 *  
 * Script to fix problems or to add features that may already be present when making transition to new sportsdb version from older one
 * 
 * NOTE CAREFULLY
 * --------------
 * Run the commands one at a time. Some may not work since they may have been written to correct issues not present in your database schema
 * or problems already fixed.
 */

-- There may be an error for this alter table call if the column is already named properly
ALTER TABLE soccer_action_penalties CHANGE penalty_value penalty_level VARCHAR(100);

/*
 * An ALTER TABLE ice_hockey_action_participants change that also requires foreign key dropping
 * */
ALTER TABLE ice_hockey_action_participants
    DROP FOREIGN KEY FK_ice_hockey_action_plays_ice_hockey_action_participants;

ALTER TABLE ice_hockey_action_participants CHANGE ice_hockey_action_play_sid ice_hockey_action_play_id VARCHAR(100);

ALTER TABLE ice_hockey_action_participants
    ADD CONSTRAINT FK_ice_hockey_action_plays_ice_hockey_action_participants
    FOREIGN KEY (ice_hockey_action_play_id) REFERENCES ice_hockey_action_plays (id);

/*
 * Indexes that you may or may not have added to your sportsdb. You will get errors if you already have indexes
 * for the columns in question.
 * */

/* American Football */
ALTER TABLE american_football_event_states
ADD INDEX IDX_american_football_event_states_context (context);

ALTER TABLE american_football_event_states
ADD INDEX IDX_american_football_event_states_seq_num (sequence_number);

/* Baseball */
ALTER TABLE baseball_event_states
ADD INDEX IDX_baseball_event_states_context (context);

ALTER TABLE baseball_event_states
ADD INDEX IDX_baseball_event_states_seq_num (sequence_number);

/* Basketball */
ALTER TABLE basketball_event_states
ADD INDEX IDX_basketball_event_states_context (context);

ALTER TABLE basketball_event_states
ADD INDEX IDX_basketball_event_states_seq_num (sequence_number);

/* Generic Event States */
ALTER TABLE event_states
ADD INDEX IDX_event_states_context (context);

ALTER TABLE event_states
ADD INDEX IDX_event_states_seq_num (sequence_number);

/* Ice Hockey */
ALTER TABLE ice_hockey_event_states
ADD INDEX IDX_ice_hockey_event_states_context (context);

ALTER TABLE ice_hockey_event_states
ADD INDEX IDX_ice_hockey_event_states_seq_num (sequence_number);

/* Motor Racing */
ALTER TABLE motor_racing_event_states
ADD INDEX IDX_motor_racing_event_states_context (context);

ALTER TABLE motor_racing_event_states
ADD INDEX IDX_motor_racing_event_states_seq_num (sequence_number);

/* Soccer */
ALTER TABLE soccer_event_states
ADD INDEX IDX_soccer_event_states_context (context);

ALTER TABLE soccer_event_states
ADD INDEX IDX_soccer_event_states_seq_num (sequence_number);

/* Tennis */
ALTER TABLE tennis_event_states
ADD INDEX IDX_tennis_event_states_context (context);

ALTER TABLE tennis_event_states
ADD INDEX IDX_tennis_event_states_seq_num (sequence_number);