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

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

ALTER TABLE periods ADD COLUMN sub_score_key VARCHAR(100);
ALTER TABLE periods ADD COLUMN sub_score_type VARCHAR(100);
ALTER TABLE periods ADD COLUMN sub_score_name VARCHAR(100);
ALTER TABLE periods DROP COLUMN label;

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

ALTER TABLE events ADD COLUMN medal_event VARCHAR(100);

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

ALTER TABLE latest_revisions MODIFY revision_id VARCHAR(255);

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

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

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