/*
 *  
 * Script to update the sportsdb schema between version 23 and 24.
 */

ALTER TABLE baseball_action_pitches ALTER COLUMN comment VARCHAR(2048);
GO

ALTER TABLE ice_hockey_event_states ADD record_type VARCHAR(40);
GO
ALTER TABLE ice_hockey_event_states ADD power_play_team_id INTEGER;
GO
ALTER TABLE ice_hockey_event_states ADD power_play_player_advantage INTEGER;
GO
ALTER TABLE ice_hockey_event_states ADD score_team INTEGER;
GO
ALTER TABLE ice_hockey_event_states ADD score_team_opposing INTEGER;
GO
ALTER TABLE ice_hockey_event_states ADD score_team_home INTEGER;
GO
ALTER TABLE ice_hockey_event_states ADD score_team_away INTEGER;
GO
ALTER TABLE ice_hockey_event_states ADD action_key VARCHAR(100);
GO
/* This must be done with SQL Server to ensure proper indexing and to avoid error messages */
DROP INDEX ice_hockey_event_states.IDX_ice_hockey_event_states_seq_num;
GO
ALTER TABLE ice_hockey_event_states ALTER COLUMN sequence_number VARCHAR(100);
GO
CREATE INDEX IDX_ice_hockey_event_states_seq_num ON ice_hockey_event_states (sequence_number);
GO


ALTER TABLE ice_hockey_action_plays ADD penalty_time_remaining VARCHAR(40);
GO
ALTER TABLE ice_hockey_action_plays ADD location VARCHAR(40);
GO
ALTER TABLE ice_hockey_action_plays ADD zone VARCHAR(40);
GO


ALTER TABLE ice_hockey_action_participants ADD team_id INTEGER;
GO
ALTER TABLE ice_hockey_action_participants ADD goals_cumulative INTEGER;
GO
ALTER TABLE ice_hockey_action_participants ADD assists_cumulative INTEGER;
GO

ALTER TABLE penalty_stats ADD value INTEGER;
GO

ALTER TABLE ice_hockey_offensive_stats ADD goals INTEGER;
GO
ALTER TABLE ice_hockey_offensive_stats ADD shots INTEGER;
GO
ALTER TABLE ice_hockey_offensive_stats ADD shots_missed INTEGER;
GO
ALTER TABLE ice_hockey_offensive_stats ADD shots_blocked INTEGER;
GO
ALTER TABLE ice_hockey_offensive_stats ADD shots_power_play INTEGER;
GO
ALTER TABLE ice_hockey_offensive_stats ADD shots_short_handed INTEGER;
GO
ALTER TABLE ice_hockey_offensive_stats ADD shots_even_strength INTEGER;
GO
ALTER TABLE ice_hockey_offensive_stats ADD player_count INTEGER;
GO
ALTER TABLE ice_hockey_offensive_stats ADD player_count_opposing INTEGER;
GO

ALTER TABLE ice_hockey_defensive_stats ADD player_count INTEGER;
GO
ALTER TABLE ice_hockey_defensive_stats ADD player_count_opposing INTEGER;
GO
ALTER TABLE ice_hockey_defensive_stats ADD goaltender_losses_overtime INTEGER;
GO

CREATE TABLE ice_hockey_faceoff_stats (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
	player_count INTEGER,
	player_count_opposing INTEGER,
	faceoff_wins INTEGER,
	faceoff_losses INTEGER,
	faceoff_win_percentage DECIMAL(5,2),
	faceoffs_power_play_wins INTEGER,
	faceoffs_power_play_losses INTEGER,
	faceoffs_power_play_win_percentage DECIMAL(5,2),
	faceoffs_short_handed_wins INTEGER,
	faceoffs_short_handed_losses INTEGER,
	faceoffs_short_handed_win_percentage DECIMAL(5,2),
	faceoffs_even_strength_wins INTEGER,
	faceoffs_even_strength_losses INTEGER,
	faceoffs_even_strength_win_percentage DECIMAL(5,2),
	faceoffs_offensive_zone_wins INTEGER,
	faceoffs_offensive_zone_losses INTEGER,
	faceoffs_offensive_zone_win_percentage DECIMAL(5,2),
	faceoffs_defensive_zone_wins INTEGER,
	faceoffs_defensive_zone_losses INTEGER,
	faceoffs_defensive_zone_win_percentage DECIMAL(5,2),
	faceoffs_neutral_zone_wins INTEGER,
	faceoffs_neutral_zone_losses INTEGER,
	faceoffs_neutral_zone_win_percentage DECIMAL(5,2)
);
GO

CREATE TABLE ice_hockey_time_on_ice_stats (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
	player_count INTEGER,
	player_count_opposing INTEGER,
	shifts INTEGER,
	time_total VARCHAR(40),
	time_power_play VARCHAR(40),
	time_short_handed VARCHAR(40),
	time_even_strength VARCHAR(40),
	time_empty_net VARCHAR(40),
	time_power_play_empty_net VARCHAR(40),
	time_short_handed_empty_net VARCHAR(40),
	time_even_strength_empty_net VARCHAR(40),
	time_average_per_shift VARCHAR(40)
);
GO

ALTER TABLE ice_hockey_action_plays DROP COLUMN location_x;
GO
ALTER TABLE ice_hockey_action_plays DROP COLUMN location_y;
GO
ALTER TABLE ice_hockey_action_plays DROP COLUMN location_zone;
GO
ALTER TABLE ice_hockey_action_plays DROP COLUMN empty_net;
GO
ALTER TABLE ice_hockey_action_plays DROP COLUMN goal_awarded;
GO

ALTER TABLE ice_hockey_action_participants DROP COLUMN goals_to_date;
GO
ALTER TABLE ice_hockey_action_participants DROP COLUMN assists_to_date;
GO
ALTER TABLE ice_hockey_action_participants DROP COLUMN points_to_date;
GO

ALTER TABLE persons ADD final_resting_location_id INTEGER;
GO

ALTER TABLE events ADD start_date_time_local DATETIME;
GO

ALTER TABLE american_football_action_plays ADD touchdown_type VARCHAR(100);
GO

ALTER TABLE american_football_action_plays ADD team_id INTEGER;
GO

/* Drop NOT NULL requirement for two position id fields */
ALTER TABLE soccer_action_substitutions ALTER COLUMN person_original_position_id INTEGER NULL;
GO
ALTER TABLE soccer_action_substitutions ALTER COLUMN person_replacing_position_id INTEGER NULL;
GO


ALTER TABLE american_football_defensive_stats ADD first_downs_against_total INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD first_downs_against_rushing INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD first_downs_against_passing INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD first_downs_against_penalty INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD conversions_third_down_against INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD conversions_third_down_against_attempts INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD conversions_third_down_against_percentage DECIMAL(5,2);
GO
ALTER TABLE american_football_defensive_stats ADD conversions_fourth_down_against INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD conversions_fourth_down_against_attempts INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD conversions_fourth_down_against_percentage DECIMAL(5,2);
GO
ALTER TABLE american_football_defensive_stats ADD two_point_conversions_against INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD two_point_conversions_against_attempts INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD offensive_plays_against_touchdown INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD offensive_plays_against_average_yards_per_game DECIMAL(5,2);
GO
ALTER TABLE american_football_defensive_stats ADD rushes_against_attempts INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD rushes_against_yards INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD rushing_against_average_yards_per_game DECIMAL(5,2);
GO
ALTER TABLE american_football_defensive_stats ADD rushes_against_touchdowns INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD rushes_against_average_yards_per DECIMAL(5,2);
GO
ALTER TABLE american_football_defensive_stats ADD rushes_against_longest INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD receptions_against_total INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD receptions_against_yards INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD receptions_against_touchdowns INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD receptions_against_average_yards_per DECIMAL(5,2);
GO
ALTER TABLE american_football_defensive_stats ADD receptions_against_longest INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD passes_against_yards_net INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD passes_against_yards_gross INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD passes_against_attempts INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD passes_against_completions INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD passes_against_percentage DECIMAL(5,2);
GO
ALTER TABLE american_football_defensive_stats ADD passes_against_average_yards_per_game DECIMAL(5,2);
GO
ALTER TABLE american_football_defensive_stats ADD passes_against_average_yards_per DECIMAL(5,2);
GO
ALTER TABLE american_football_defensive_stats ADD passes_against_touchdowns INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD passes_against_touchdowns_percentage DECIMAL(5,2);
GO
ALTER TABLE american_football_defensive_stats ADD passes_against_longest INTEGER;
GO
ALTER TABLE american_football_defensive_stats ADD passes_against_rating DECIMAL(5,2);
GO
ALTER TABLE american_football_defensive_stats ADD interceptions_percentage DECIMAL(5,2);
GO

ALTER TABLE american_football_scoring_stats ADD safeties_against_opponent INTEGER;
GO

ALTER TABLE american_football_special_teams_stats ADD punts_against_blocked INTEGER;
GO
ALTER TABLE american_football_special_teams_stats ADD field_goals_against_attempts_1_to_19 INTEGER;
GO
ALTER TABLE american_football_special_teams_stats ADD field_goals_against_made_1_to_19 INTEGER;
GO
ALTER TABLE american_football_special_teams_stats ADD field_goals_against_attempts_20_to_29 INTEGER;
GO
ALTER TABLE american_football_special_teams_stats ADD field_goals_against_made_20_to_29 INTEGER;
GO
ALTER TABLE american_football_special_teams_stats ADD field_goals_against_attempts_30_to_39 INTEGER;
GO
ALTER TABLE american_football_special_teams_stats ADD field_goals_against_made_30_to_39 INTEGER;
GO
ALTER TABLE american_football_special_teams_stats ADD field_goals_against_attempts_40_to_49 INTEGER;
GO
ALTER TABLE american_football_special_teams_stats ADD field_goals_against_made_40_to_49 INTEGER;
GO
ALTER TABLE american_football_special_teams_stats ADD field_goals_against_attempts_50_plus INTEGER;
GO
ALTER TABLE american_football_special_teams_stats ADD field_goals_against_made_50_plus INTEGER;
GO
ALTER TABLE american_football_special_teams_stats ADD field_goals_against_attempts INTEGER;
GO
ALTER TABLE american_football_special_teams_stats ADD extra_points_against_attempts INTEGER;
GO
ALTER TABLE american_football_special_teams_stats ADD tackles INTEGER;
GO
ALTER TABLE american_football_special_teams_stats ADD tackles_assists INTEGER;
GO

ALTER TABLE american_football_fumbles_stats ADD fumbles_own_touchdowns INTEGER;
GO
ALTER TABLE american_football_fumbles_stats ADD fumbles_opposing_touchdowns INTEGER;
GO
ALTER TABLE american_football_fumbles_stats ADD fumbles_committed_defense INTEGER;
GO
ALTER TABLE american_football_fumbles_stats ADD fumbles_committed_special_teams INTEGER;
GO
ALTER TABLE american_football_fumbles_stats ADD fumbles_committed_other INTEGER;
GO
ALTER TABLE american_football_fumbles_stats ADD fumbles_lost_defense INTEGER;
GO
ALTER TABLE american_football_fumbles_stats ADD fumbles_lost_special_teams INTEGER;
GO
ALTER TABLE american_football_fumbles_stats ADD fumbles_lost_other INTEGER;
GO
ALTER TABLE american_football_fumbles_stats ADD fumbles_forced_defense INTEGER;
GO
ALTER TABLE american_football_fumbles_stats ADD fumbles_recovered_defense INTEGER;
GO
ALTER TABLE american_football_fumbles_stats ADD fumbles_recovered_special_teams INTEGER;
GO
ALTER TABLE american_football_fumbles_stats ADD fumbles_recovered_other INTEGER;
GO
ALTER TABLE american_football_fumbles_stats ADD fumbles_recovered_yards_defense INTEGER;
GO
ALTER TABLE american_football_fumbles_stats ADD fumbles_recovered_yards_special_teams INTEGER;
GO
ALTER TABLE american_football_fumbles_stats ADD fumbles_recovered_yards_other INTEGER;
GO

ALTER TABLE american_football_offensive_stats ADD tackles INTEGER;
GO
ALTER TABLE american_football_offensive_stats ADD tackles_assists INTEGER;
GO

ALTER TABLE events ADD broadcast_listing VARCHAR(255);
GO

ALTER TABLE ice_hockey_event_states
    ADD CONSTRAINT FK_hockey_event_states_power_play_team_id_teams_id
    FOREIGN KEY (power_play_team_id) REFERENCES teams (id)
GO

ALTER TABLE ice_hockey_action_participants
    ADD CONSTRAINT FK_ice_hockey_action_participants_team_id_teams_id
    FOREIGN KEY (team_id) REFERENCES teams (id)
GO

ALTER TABLE american_football_action_plays
    ADD CONSTRAINT FK_american_football_action_plays_team_id_teams_id
    FOREIGN KEY (team_id) REFERENCES teams (id)
GO

ALTER TABLE persons
    ADD CONSTRAINT FK_persons_final_resting_location_id_locations_id
    FOREIGN KEY (final_resting_location_id) REFERENCES locations (id)
GO

ALTER TABLE outcome_totals ADD losses_overtime INTEGER;
GO

DELETE FROM db_info
GO

INSERT INTO db_info (version) VALUES ('24')
GO