/*
 *  
 * Script to update the xtoss schema between version 18 and 19.
 */

ALTER TABLE baseball_action_plays ADD COLUMN  out_type VARCHAR(100);
ALTER TABLE baseball_action_plays ADD INDEX IDX_baseball_action_plays_2 (out_type);

/*
 * NOTE: This will work for mysql 5.03 and up
 * */
ALTER TABLE american_football_action_plays MODIFY comment VARCHAR(512);
ALTER TABLE baseball_action_plays MODIFY comment VARCHAR(512);
ALTER TABLE baseball_action_substitutions MODIFY comment VARCHAR(512);
ALTER TABLE baseball_action_contact_details MODIFY comment VARCHAR(512);
ALTER TABLE ice_hockey_action_plays MODIFY comment VARCHAR(512);

CREATE TABLE awards (
    id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
    participant_type VARCHAR(100) NOT NULL,
    participant_id INTEGER NOT NULL,
    award_type VARCHAR(100),
    name VARCHAR(100),
    total INTEGER,
    rank VARCHAR(100),
    award_value VARCHAR(100),
    currency VARCHAR(100),
    date_coverage_type VARCHAR(100),
    date_coverage_id INTEGER
);

CREATE TABLE records (
    id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
    participant_type VARCHAR(100) NOT NULL,
    participant_id INTEGER NOT NULL,
    record_type VARCHAR(100),
    record_label VARCHAR(100),
    record_value VARCHAR(100),
    previous_value VARCHAR(100),
    date_coverage_type VARCHAR(100),
    date_coverage_id INTEGER,
    comment VARCHAR(512)
);

ALTER TABLE participants_events ADD COLUMN  result_effect VARCHAR(100);
ALTER TABLE periods ADD COLUMN  label VARCHAR(100);
ALTER TABLE sites MODIFY site_key VARCHAR(128);

ALTER TABLE participants_events ADD COLUMN score_attempts INTEGER;
ALTER TABLE periods ADD COLUMN score_attempts INTEGER;
ALTER TABLE sub_periods ADD COLUMN score_attempts INTEGER;
			
	
-- ALTER TABLE baseball_action_substitutions DROP COLUMN sequence_number;

-- use baseball_event_state.sequence_number instead


-- Add new soccer tables
CREATE TABLE soccer_action_fouls (
    id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
    soccer_event_state_id INTEGER NOT NULL,
    foul_name VARCHAR(100),
    foul_result VARCHAR(100),
    foul_type VARCHAR(100),
    fouler_id VARCHAR(100),
    recipient_type VARCHAR(100),
    recipient_id INTEGER NOT NULL,
    comment VARCHAR(512)
);

CREATE TABLE soccer_action_plays (
    id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
    soccer_event_state_id INTEGER NOT NULL,
    play_type VARCHAR(100),
    score_attempt_type VARCHAR(100),
    play_result VARCHAR(100),
    comment VARCHAR(100)
);

CREATE TABLE soccer_action_participants (
    id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
    soccer_action_play_id INTEGER NOT NULL,
    person_id INTEGER NOT NULL,
    participant_role VARCHAR(100)
);

CREATE TABLE soccer_action_penalties (
    id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
    soccer_event_state_id INTEGER NOT NULL,
    penalty_type VARCHAR(100),
    penalty_level VARCHAR(100),
    caution_value VARCHAR(100),
    recipient_type VARCHAR(100),
    recipient_id INTEGER NOT NULL,
    comment VARCHAR(512)
);

CREATE TABLE soccer_action_substitutions (
    id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
    soccer_event_state_id INTEGER NOT NULL,
    person_type VARCHAR(100),
    person_original_id INTEGER NOT NULL,
    person_original_position_id INTEGER NOT NULL,
    person_replacing_id INTEGER NOT NULL,
    person_replacing_position_id INTEGER NOT NULL,
    substitution_reason VARCHAR(100),
    comment VARCHAR(512)
);

ALTER TABLE soccer_action_fouls
    ADD CONSTRAINT FK_soccer_event_states_soccer_action_fouls
    FOREIGN KEY (soccer_event_state_id) REFERENCES soccer_event_states (id);

ALTER TABLE soccer_action_fouls
    ADD CONSTRAINT FK_persons_soccer_action_fouls
    FOREIGN KEY (recipient_id) REFERENCES persons (id);

ALTER TABLE soccer_action_plays
    ADD CONSTRAINT FK_soccer_event_states_soccer_action_plays
    FOREIGN KEY (soccer_event_state_id) REFERENCES soccer_event_states (id);

ALTER TABLE soccer_action_participants
    ADD CONSTRAINT FK_soccer_action_plays_soccer_action_participants
    FOREIGN KEY (soccer_action_play_id) REFERENCES soccer_action_plays (id);

ALTER TABLE soccer_action_participants
    ADD CONSTRAINT FK_persons_soccer_action_participants
    FOREIGN KEY (person_id) REFERENCES persons (id);

ALTER TABLE soccer_action_penalties
    ADD CONSTRAINT FK_soccer_event_states_soccer_action_penalties
    FOREIGN KEY (soccer_event_state_id) REFERENCES soccer_event_states (id);

ALTER TABLE soccer_action_penalties
    ADD CONSTRAINT FK_persons_soccer_action_penalties
    FOREIGN KEY (recipient_id) REFERENCES persons (id);

ALTER TABLE soccer_action_substitutions
    ADD CONSTRAINT FK_soccer_event_states_soccer_action_substitutions
    FOREIGN KEY (soccer_event_state_id) REFERENCES soccer_event_states (id);

ALTER TABLE soccer_action_substitutions
    ADD CONSTRAINT FK_persons_soccer_action_substitutions
    FOREIGN KEY (person_original_id) REFERENCES persons (id);

ALTER TABLE soccer_action_substitutions
    ADD CONSTRAINT FK_persons_soccer_action_substitutions1
    FOREIGN KEY (person_replacing_id) REFERENCES persons (id);

ALTER TABLE soccer_action_substitutions
    ADD CONSTRAINT FK_positions_soccer_action_substitutions
    FOREIGN KEY (person_original_position_id) REFERENCES positions (id);

ALTER TABLE soccer_action_substitutions
    ADD CONSTRAINT FK_positions_soccer_action_substitutions1
    FOREIGN KEY (person_replacing_position_id) REFERENCES positions (id);

-- Drop publisher id foreign key for standings table prior to dropping the field
ALTER TABLE standings DROP FOREIGN KEY FK_sta_pub_id__pub_id;

-- Drop unneeded standings table fields
ALTER TABLE standings DROP COLUMN alignment_scope;
ALTER TABLE standings DROP COLUMN competition_scope;
ALTER TABLE standings DROP COLUMN competition_scope_id;
ALTER TABLE standings DROP COLUMN duration_scope;
ALTER TABLE standings DROP COLUMN publisher_id;
ALTER TABLE standings DROP COLUMN scoping_label;
ALTER TABLE standings DROP COLUMN site_scope;

-- Add new standing_subgroup table fields
ALTER TABLE standing_subgroups ADD COLUMN alignment_scope VARCHAR(100);
ALTER TABLE standing_subgroups ADD COLUMN competition_scope VARCHAR(100);
ALTER TABLE standing_subgroups ADD COLUMN competition_scope_id VARCHAR(100);
ALTER TABLE standing_subgroups ADD COLUMN duration_scope VARCHAR(100);
ALTER TABLE standing_subgroups ADD COLUMN scoping_label VARCHAR(100);
ALTER TABLE standing_subgroups ADD COLUMN site_scope VARCHAR(100);

-- Add new outcome_totals table fields
ALTER TABLE outcome_totals ADD COLUMN events_played INTEGER;
ALTER TABLE outcome_totals ADD COLUMN games_back VARCHAR(100);
ALTER TABLE outcome_totals ADD COLUMN result_effect VARCHAR(100);
ALTER TABLE outcome_totals ADD COLUMN sets_against VARCHAR(100);
ALTER TABLE outcome_totals ADD COLUMN sets_for VARCHAR(100);

-- Add rank field to periods table
ALTER TABLE periods ADD COLUMN rank VARCHAR(100);

-- Modify types for runner_on_*_advance columns to be varchar, since non-numeric values are used
ALTER TABLE baseball_action_plays MODIFY runner_on_first_advance VARCHAR(40);
ALTER TABLE baseball_action_plays MODIFY runner_on_second_advance VARCHAR(40);
ALTER TABLE baseball_action_plays MODIFY runner_on_third_advance VARCHAR(40);