/*
 *  
 * 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
    ERROR:  column "penalty_value" does not exist */
ALTER TABLE soccer_action_penalties RENAME COLUMN penalty_value TO penalty_level;

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

/* There may be an error for this alter table call if the column is already named properly
    ERROR:  column "ice_hockey_action_play_sid" does not exist */
ALTER TABLE ice_hockey_action_participants RENAME COLUMN ice_hockey_action_play_sid TO ice_hockey_action_play_id;

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 */
CREATE INDEX
    IDX_american_football_event_states_context ON american_football_event_states (context);

CREATE INDEX
    IDX_american_football_event_states_seq_num ON american_football_event_states (sequence_number);

/* Baseball */
CREATE INDEX
    IDX_baseball_event_states_context ON baseball_event_states (context);

CREATE INDEX
    IDX_baseball_event_states_seq_num ON baseball_event_states (sequence_number);

/* Basketball */
CREATE INDEX
    IDX_basketball_event_states_context ON basketball_event_states (context);

CREATE INDEX
    IDX_basketball_event_states_seq_num ON basketball_event_states (sequence_number);

/* Generic Event States */
CREATE INDEX
    IDX_event_states_context ON event_states (context);

CREATE INDEX
    IDX_event_states_seq_num ON event_states (sequence_number);

/* Ice Hockey */
CREATE INDEX
    IDX_ice_hockey_event_states_context ON ice_hockey_event_states (context);

CREATE INDEX
    IDX_ice_hockey_event_states_seq_num ON ice_hockey_event_states (sequence_number);

/* Motor Racing */
CREATE INDEX
    IDX_motor_racing_event_states_context ON motor_racing_event_states (context);

CREATE INDEX
    IDX_motor_racing_event_states_seq_num ON motor_racing_event_states (sequence_number);

/* Soccer */
CREATE INDEX
    IDX_soccer_event_states_context ON soccer_event_states (context);

CREATE INDEX
    IDX_soccer_event_states_seq_num ON soccer_event_states (sequence_number);

/* Tennis */
CREATE INDEX
    IDX_tennis_event_states_context ON tennis_event_states (context);

CREATE INDEX
    IDX_tennis_event_states_seq_num ON tennis_event_states (sequence_number);