/*
 *  
 * Script to update the xtoss schema between version 15 and 16.
 */

/*
 * injury_phases modifications
 * */

/*
 * Weather additions
 * */

ALTER TABLE american_football_action_participants ADD COLUMN score_credit INTEGER; 
ALTER TABLE american_football_action_participants ADD COLUMN yards_gained INTEGER; 

ALTER TABLE weather_conditions ADD COLUMN weather_code VARCHAR(100); 
ALTER TABLE weather_conditions ADD COLUMN temperature_units VARCHAR(100); 

ALTER TABLE core_person_stats ALTER COLUMN position_id DROP NOT NULL;

ALTER TABLE baseball_action_pitches DROP CONSTRAINT FK_bas_act_pit_bas_eve_sta_id__bas_eve_sta_id;
DROP INDEX IDX_FK_bas_act_pit_bas_eve_sta_id__bas_eve_sta_id;
ALTER TABLE baseball_action_pitches RENAME COLUMN baseball_event_state_id TO baseball_action_play_id;
ALTER TABLE baseball_action_pitches ALTER COLUMN baseball_action_play_id SET NOT NULL;
ALTER TABLE baseball_action_pitches ADD CONSTRAINT FK_bas_act_pit_bas_eve_sta_id__bas_eve_sta_id FOREIGN KEY (baseball_action_play_id) REFERENCES baseball_action_plays (id);
CREATE INDEX IDX_FK_bas_act_pit_bas_eve_sta_id__bas_eve_sta_id ON baseball_action_pitches (baseball_action_play_id);

ALTER TABLE baseball_action_pitches ADD COLUMN ball_type VARCHAR(100);
ALTER TABLE baseball_action_pitches ADD COLUMN strike_type VARCHAR(100);
ALTER TABLE baseball_action_pitches ADD COLUMN sequence_number INTEGER;