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

ALTER TABLE baseball_action_plays ADD out_type VARCHAR(100); 
GO

CREATE INDEX IDX_baseball_action_plays_2 ON baseball_action_plays (out_type)
GO

-- Change the type of american_football_action_plays.comment from TEXT to VARCHAR(512)
-- In SQL Server and with the TEXT this requires more work
ALTER TABLE american_football_action_plays ADD comment_new VARCHAR(512);
GO
UPDATE american_football_action_plays SET comment_new=comment;
GO
ALTER TABLE american_football_action_plays DROP COLUMN comment;
GO
EXEC sp_rename 'american_football_action_plays.comment_new', 'comment', 'COLUMN';
GO


ALTER TABLE baseball_action_plays ALTER COLUMN comment VARCHAR(512);
GO

ALTER TABLE baseball_action_substitutions ALTER COLUMN comment VARCHAR(512);
GO

-- Change the type of baseball_action_contact_details.comment from TEXT to VARCHAR(512)
-- In SQL Server and with the TEXT this requires more work
ALTER TABLE baseball_action_contact_details ADD comment_new VARCHAR(512);
GO
UPDATE baseball_action_contact_details SET comment_new=comment;
GO
ALTER TABLE baseball_action_contact_details DROP COLUMN comment;
GO
EXEC sp_rename 'baseball_action_contact_details.comment_new', 'comment', 'COLUMN';
GO


ALTER TABLE ice_hockey_action_plays ALTER COLUMN comment VARCHAR(512);
GO

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

CREATE TABLE records (
    id INT IDENTITY (1,1) 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 INT,
    comment VARCHAR(512)
)
GO

ALTER TABLE participants_events ADD  result_effect VARCHAR(100);
GO

ALTER TABLE periods ADD  label VARCHAR(100);
GO

DROP INDEX sites.IDX_sites_1;
GO

ALTER TABLE sites ALTER COLUMN site_key VARCHAR(128);
GO

CREATE INDEX IDX_sites_1 ON sites (site_key)
GO

ALTER TABLE participants_events ADD score_attempts INTEGER;
GO

ALTER TABLE periods ADD score_attempts INTEGER;
GO

ALTER TABLE sub_periods ADD score_attempts INTEGER;
GO
    
-- 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 INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    soccer_event_state_id INT NOT NULL,
    foul_name VARCHAR(100),
    foul_result VARCHAR(100),
    foul_type VARCHAR(100),
    fouler_id VARCHAR(100),
    recipient_type VARCHAR(100),
    recipient_id INT NOT NULL,
    comment VARCHAR(512)
)
GO

CREATE TABLE soccer_action_plays (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    soccer_event_state_id INT NOT NULL,
    play_type VARCHAR(100),
    score_attempt_type VARCHAR(100),
    play_result VARCHAR(100),
    comment VARCHAR(100)
)
GO

CREATE TABLE soccer_action_participants (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    soccer_action_play_id INT NOT NULL,
    person_id INT NOT NULL,
    participant_role VARCHAR(100)
)
GO

CREATE TABLE soccer_action_penalties (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    soccer_event_state_id INT NOT NULL,
    penalty_type VARCHAR(100),
    penalty_level VARCHAR(100),
    caution_value VARCHAR(100),
    recipient_type VARCHAR(100),
    recipient_id INT NOT NULL,
    comment VARCHAR(512)
)
GO

CREATE TABLE soccer_action_substitutions (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    soccer_event_state_id INT NOT NULL,
    person_type VARCHAR(100),
    person_original_id INT NOT NULL,
    person_original_position_id INT NOT NULL,
    person_replacing_id INT NOT NULL,
    person_replacing_position_id INT NOT NULL,
    substitution_reason VARCHAR(100),
    comment VARCHAR(512)
)
GO

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)
GO

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

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)
GO

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)
GO

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

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)
GO

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

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)
GO

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

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

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

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


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

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

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

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

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

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