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

/*
 * To deal with a participant_naming error in previous versions of FeedFetcher
 * Please run this ONLY in releases with dates following 23 June, 2008.
 * The incorrect code used prior to this date will result in duplicate records being created, the
 * updated ones with participant_type equal to 'persons' and all those created by FeedFetcher
 * equal to 'players'
 */
UPDATE participants_events SET participant_type='persons' WHERE participant_type='players';
GO

UPDATE records SET participant_type='persons' WHERE participant_type='players';
GO

/*
 * BEGIN new generic event states tables
 * */
CREATE TABLE event_states (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    event_id INTEGER NOT NULL,
    current_state INTEGER,
    sequence_number INTEGER,
    period_value VARCHAR(100),
    period_time_elapsed VARCHAR(100),
    period_time_remaining VARCHAR(100),
    minutes_elapsed VARCHAR(100),
    period_minutes_elapsed VARCHAR(100),
    context VARCHAR(40)
);
GO

CREATE TABLE event_action_fouls (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    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,
    comment VARCHAR(512)
);
GO

CREATE TABLE event_action_plays (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    event_state_id INTEGER NOT NULL,
    play_type VARCHAR(100),
    score_attempt_type VARCHAR(100),
    play_result VARCHAR(100),
    comment VARCHAR(100)
);
GO

CREATE TABLE event_action_participants (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    event_action_play_id INTEGER NOT NULL,
    person_id INTEGER NOT NULL,
    participant_role VARCHAR(100)
);
GO

CREATE TABLE event_action_penalties (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    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)
);
GO

CREATE TABLE event_action_substitutions (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    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)
);
GO
/*
 * END new generic event states tables
 * */


/*
 * tennis_service_stats changes/additions
 * */
DROP TABLE tennis_service_stats;

CREATE TABLE tennis_service_stats (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
/* Changes */
    services_played INTEGER,
    matches_played INTEGER,
    aces INTEGER,
    first_services_good INTEGER,
    first_services_good_pct INTEGER,
    first_service_points_won INTEGER,
    first_service_points_won_pct INTEGER,
    second_service_points_won INTEGER,
    second_service_points_won_pct INTEGER,
    service_games_played INTEGER,
    service_games_won INTEGER,
    service_games_won_pct INTEGER,
    break_points_played INTEGER,
    break_points_saved INTEGER,
    break_points_saved_pct INTEGER,
/* Additions */
    service_points_won INTEGER,
    service_points_won_pct INTEGER,
    double_faults INTEGER,
    first_service_top_speed VARCHAR(100),
    second_services_good INTEGER,
    second_services_good_pct INTEGER,

    second_service_top_speed VARCHAR(100),

    net_points_won INTEGER,
    net_points_played INTEGER,
    points_won INTEGER,
    winners INTEGER,
    unforced_errors INTEGER,
    winners_forehand INTEGER,
    winners_backhand INTEGER,
    winners_volley INTEGER
);
GO

/*
 * tennis_return_stats changes/additions
 * */
DROP TABLE tennis_return_stats;

CREATE TABLE tennis_return_stats (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
/* Changes */
    returns_played INTEGER,
    matches_played INTEGER,
    first_service_return_points_won INTEGER,
    first_service_return_points_won_pct INTEGER,
    second_service_return_points_won INTEGER,
    second_service_return_points_won_pct INTEGER,
    return_games_played INTEGER,
    return_games_won INTEGER,
    return_games_won_pct INTEGER,
    break_points_played INTEGER,
    break_points_converted INTEGER,
    break_points_converted_pct INTEGER,
/* Additions */
    net_points_won INTEGER,
    net_points_played INTEGER,
    points_won INTEGER,
    winners INTEGER,
    unforced_errors INTEGER,
    winners_forehand INTEGER,
    winners_backhand INTEGER,
    winners_volley INTEGER
);
GO

/*
 * New tennis_set_stats table
 * */
CREATE TABLE tennis_set_stats (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    net_points_won INTEGER,
    net_points_played INTEGER,
    points_won INTEGER,
    winners INTEGER,
    unforced_errors INTEGER,
    winners_forehand INTEGER,
    winners_backhand INTEGER,
    winners_volley INTEGER
);
GO

/*
 * New tennis_team_stats table
 * */
CREATE TABLE tennis_team_stats (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    net_points_won INTEGER,
    net_points_played INTEGER,
    points_won INTEGER,
    winners INTEGER,
    unforced_errors INTEGER,
    winners_forehand INTEGER,
    winners_backhand INTEGER,
    winners_volley INTEGER
);
GO

/*
 * New tennis_player_stats table
 * */
CREATE TABLE tennis_player_stats (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    net_points_won INTEGER,
    net_points_played INTEGER,
    points_won INTEGER,
    winners INTEGER,
    unforced_errors INTEGER,
    winners_forehand INTEGER,
    winners_backhand INTEGER,
    winners_volley INTEGER
);
GO

ALTER TABLE events ADD event_number VARCHAR(32); 
GO
ALTER TABLE events ADD round_number VARCHAR(32); 
GO

ALTER TABLE ice_hockey_defensive_stats ADD goaltender_wins INTEGER; 
GO
ALTER TABLE ice_hockey_defensive_stats ADD goaltender_losses INTEGER; 
GO
ALTER TABLE ice_hockey_defensive_stats ADD goaltender_ties INTEGER; 
GO

ALTER TABLE person_phases ADD duration VARCHAR(32); 
GO

/* Increase length of documents.revision_id and latest_revisions.revision_id to 255 - was 75, which was too short for some revision_id values */
ALTER TABLE documents ALTER COLUMN revision_id VARCHAR(255)
GO

ALTER TABLE latest_revisions ALTER COLUMN revision_id VARCHAR(255)
GO

UPDATE db_info SET version = '20'
GO