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

ALTER TABLE american_football_event_states ADD score_team INTEGER;
GO
ALTER TABLE american_football_event_states ADD score_team_opposing INTEGER;
GO
ALTER TABLE american_football_event_states ADD score_team_away INTEGER;
GO
ALTER TABLE american_football_event_states ADD score_team_home INTEGER;
GO

ALTER TABLE periods ADD sub_score_key VARCHAR(100);
GO
ALTER TABLE periods ADD sub_score_type VARCHAR(100);
GO
ALTER TABLE periods ADD sub_score_name VARCHAR(100);
GO
ALTER TABLE ice_hockey_action_plays DROP COLUMN location_x;
GO

ALTER TABLE participants_events ADD sort_order VARCHAR(100);
GO
ALTER TABLE participants_events ADD score_type VARCHAR(100);
GO

ALTER TABLE events ADD medal_event VARCHAR(100);
GO

CREATE TABLE sports_property (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    sports_property_type VARCHAR(100),
    sports_property_id INTEGER NOT NULL,
    formal_name VARCHAR(100),
    value VARCHAR(255)
);
GO

/* NOT TESTED YET (or valid for SQL Server) but needs to be in place for release */
ALTER TABLE latest_revisions ALTER COLUMN revision_id VARCHAR(255);

DELETE FROM db_info;
GO
INSERT INTO db_info (version) VALUES ('25');
GO

/* Drop duplicate indexes. These drops may already have been run. Messages that the indexes do not exist are OK. */
DROP INDEX stats.IDX_stats_8;
GO
DROP INDEX stats.IDX_stats_9;
GO

/* This call may have already been run or you may not have the field in your database (it should not be there anyway). */
/* A message that no such field exists is OK. */
ALTER TABLE ice_hockey_action_plays DROP COLUMN ice_hockey_event_statespower_play_team_id;
GO