/*
 *  
 * 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.
 */

/*
 * * NOTE
 * You may get a message like this
 * 
 * Error: The parameter @oldname is either ambiguous or the claimed @itemtype (COLUMN) was wrong.
 * 
 * Which indicates that the column in question was not mis-named
 * */

sp_rename "soccer_action_penalties.penalty_value", penalty_level, 'COLUMN';
GO

/*
 * 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;
GO

/*
 * * NOTE
 * You may get a message like this
 * 
 * Error: The parameter @oldname is either ambiguous or the claimed @itemtype (COLUMN) was wrong.
 * 
 * Which indicates that the column in question was not mis-named
 * */
sp_rename "ice_hockey_action_participants.ice_hockey_action_play_sid", ice_hockey_action_play_id, 'COLUMN';
GO

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)
GO

/*
 * 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)
GO

CREATE INDEX IDX_american_football_event_states_seq_num ON american_football_event_states (sequence_number)
GO

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

CREATE INDEX IDX_baseball_event_states_seq_num ON baseball_event_states (sequence_number)
GO

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

CREATE INDEX IDX_basketball_event_states_seq_num ON basketball_event_states (sequence_number)
GO

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

CREATE INDEX IDX_event_states_seq_num ON event_states (sequence_number)
GO

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

CREATE INDEX IDX_ice_hockey_event_states_seq_num ON ice_hockey_event_states (sequence_number)
GO

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

CREATE INDEX IDX_motor_racing_event_states_seq_num ON motor_racing_event_states (sequence_number)
GO

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

CREATE INDEX IDX_soccer_event_states_seq_num ON soccer_event_states (sequence_number)
GO

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

CREATE INDEX IDX_tennis_event_states_seq_num ON tennis_event_states (sequence_number)
GO