/*
 * This file accumulates multiple ad-hoc tests and table create statements.
 * It IS NOT meant to be run as a single script. Hence breaking command below
 */
INVALID_COMMAND;

CREATE DATABASE IF NOT EXISTS `scoreboard`
    CHARACTER SET 'utf8mb4'
    COLLATE 'utf8mb4_unicode_520_ci';

GRANT ALL ON `scoreboard`.* TO `scoreboard` WITH GRANT OPTION;

SHOW CHARACTER SET where charset like 'utf%';
SHOW COLLATION WHERE Charset = 'utf8mb4';

CREATE TABLE IF NOT EXISTS `scoreboard`.`team_data` (
    id INT(12) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    division VARCHAR(32),
    conference VARCHAR(64),
    fullname VARCHAR(64),
    filename VARCHAR(32),
    abbreviated VARCHAR(36),
    cfbd_div VARCHAR(12),
    cfbd_conf VARCHAR(36),
    cfbd_team VARCHAR(36),
    cfbd_nicknames VARCHAR(128),
    massey_div VARCHAR(32),
    massey_conf VARCHAR(64),
    massey_name VARCHAR(36),
    massey_team VARCHAR(82),
    massey_nicknames VARCHAR(24),
    KEY idx_fullname (`fullname`),
    KEY idx_abbreviated (`abbreviated`),
    KEY idx_division (`division`),
    KEY idx_conference (`conference`)
);

CREATE TABLE IF NOT EXISTS `scoreboard`.`conference_data` (
    division VARCHAR(16),
    fullname VARCHAR(48),
    filename VARCHAR(32),
    abbreviated VARCHAR(48),
    PRIMARY KEY (`fullname`,`abbreviated`)
);

CREATE TABLE IF NOT EXISTS `scoreboard`.`bowl_data` (
    level VARCHAR(16),
    fullname VARCHAR(48),
    filename VARCHAR(24),
    abbreviated VARCHAR(48),
    PRIMARY KEY (`fullname`,`abbreviated`)
);


/*
 * EX: add partitions in future:
 * ALTER TABLE cfbd_rankings
 * ADD PARTITION (
 *     PARTITION p_2051_2060 VALUES LESS THAN (2061)
 * );
 */

-- DROP TABLE IF EXISTS `scoreboard`.`cfbd_games`;
-- DROP TABLE IF EXISTS `scoreboard`.`massey_games`;
-- DROP TABLE IF EXISTS `scoreboard`.`cfbd_rankings`;
-- DROP TABLE IF EXISTS `scoreboard`.`formatted_games`;

select * from scoreboard.team_data td where massey_team like '%Pomona%';
SELECT * FROM scoreboard.conference_data cd ;

select * from scoreboard.team_data td LEFT JOIN scoreboard.conference_data cd ON cd.abbreviated = td.conference ;

SELECT
    td.fullname as team_name,
    td.filename as team_filename,
    td.abbreviated as team_abbreviated,
    cd.fullname as conf_name,
    cd.filename as conf_filename,
    cd.abbreviated as conf_abbreviated,
    td.cfbd_team as cfbd_team,
    td.massey_name as massey_name,
    td.massey_team as massey_team,
    td.massey_nicknames as massey_nicknames,
    cd.division as division
FROM scoreboard.team_data td
LEFT JOIN scoreboard.conference_data cd ON td.conference = cd.abbreviated
WHERE td.fullname IS NOT NULL
OR td.filename IS NOT NULL
OR td.abbreviated IS NOT NULL;

CREATE TABLE IF NOT EXISTS `scoreboard`.`cfbd_formatted` (
    id INT(12) UNSIGNED NOT NULL,
    season SMALLINT(4) UNSIGNED NOT NULL,
    week TINYINT(2) UNSIGNED NOT NULL,
    time DATETIME NOT NULL,
    dt DATE NOT NULL,
    home_name VARCHAR(128) NOT NULL,
    away_name VARCHAR(128) NOT NULL,
    home_logo VARCHAR(32),
    away_logo VARCHAR(32),
    home_division VARCHAR(32),
    away_division VARCHAR(32),
    home_conference VARCHAR(48),
    away_conference VARCHAR(48),
    home_conf_logo VARCHAR(32),
    away_conf_logo VARCHAR(32),
    home_score TINYINT(3) UNSIGNED,
    away_score TINYINT(3) UNSIGNED,
    home_line_scores VARCHAR(64),
    away_line_scores VARCHAR(64),
    home_rank TINYINT(2) UNSIGNED,
    away_rank TINYINT(2) UNSIGNED,
    home_record VARCHAR(8) DEFAULT NULL,
    away_record VARCHAR(8) DEFAULT NULL,
    status ENUM('Completed', 'Unknown', 'In Progress', 'Not Started'),
    overtime BOOL,
    spread SMALLINT(3),
    over_under SMALLINT(4) UNSIGNED,
    notes VARCHAR(128),
    clock VARCHAR(16) DEFAULT NULL,
    tv VARCHAR(32) DEFAULT NULL,
    period TINYINT(2) UNSIGNED DEFAULT NULL,
    situation VARCHAR(127) DEFAULT NULL,
    possession VARCHAR(16) DEFAULT NULL,
    last_play VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (season, week, id),
    KEY idx_game_id (id),
    KEY idx_season_week (season, week),
    KEY idx_home_team (home_name),
    KEY idx_away_team (away_name),
    KEY idx_game_dt (dt)
)
PARTITION BY RANGE (season) (
    PARTITION p_2001_2006 VALUES LESS THAN (2006),
    PARTITION p_2006_2011 VALUES LESS THAN (2011),
    PARTITION p_2011_2016 VALUES LESS THAN (2016),
    PARTITION p_2016_2021 VALUES LESS THAN (2021),
    PARTITION p_2021_2026 VALUES LESS THAN (2026)
)
;

CREATE TABLE IF NOT EXISTS `scoreboard`.`massey_formatted` (
    season SMALLINT(4) UNSIGNED NOT NULL,
    week TINYINT(2) UNSIGNED NOT NULL,
    dt DATE NOT NULL,
    home_name VARCHAR(128) NOT NULL,
    away_name VARCHAR(128) NOT NULL,
    home_logo VARCHAR(32),
    away_logo VARCHAR(32),
    home_division VARCHAR(32),
    away_division VARCHAR(32),
    home_conference VARCHAR(48),
    away_conference VARCHAR(48),
    home_conf_logo VARCHAR(32),
    away_conf_logo VARCHAR(32),
    home_score TINYINT(3) UNSIGNED,
    away_score TINYINT(3) UNSIGNED,
    home_rank TINYINT(2) UNSIGNED,
    away_rank TINYINT(2) UNSIGNED,
    home_record VARCHAR(8) DEFAULT NULL,
    away_record VARCHAR(8) DEFAULT NULL,
    status ENUM('Completed', 'Unknown', 'In Progress', 'Not Started'),
    earliest_dt DATE NOT NULL,
    latest_dt DATE NOT NULL,
    PRIMARY KEY (season, week, home_name, away_name),
    KEY idx_game_dt (dt),
    KEY idx_season_week (season, week),
    KEY idx_home_team (home_name),
    KEY idx_away_team (away_name),
    KEY idx_time_range (earliest_dt, latest_dt)
)
PARTITION BY RANGE (season) (
    PARTITION p_before_2001 VALUES LESS THAN (2001),
    PARTITION p_2001_2006 VALUES LESS THAN (2006),
    PARTITION p_2006_2011 VALUES LESS THAN (2011),
    PARTITION p_2011_2016 VALUES LESS THAN (2016),
    PARTITION p_2016_2021 VALUES LESS THAN (2021),
    PARTITION p_2021_2026 VALUES LESS THAN (2026)
)
;

SELECT * FROM scoreboard.cfbd_formatted cf
WHERE season = 2023
AND 'Miami' in (home_name, away_name);

select * from scoreboard.massey_formatted mf where 'Yale' in (home_name, away_name);


-- add live data columns to the cfbd_formatted table
ALTER TABLE scoreboard.cfbd_formatted
ADD COLUMN clock VARCHAR(16) DEFAULT NULL,
ADD COLUMN tv VARCHAR(32) DEFAULT NULL,
ADD COLUMN period TINYINT(2) UNSIGNED DEFAULT NULL,
ADD COLUMN situation VARCHAR(127) DEFAULT NULL,
ADD COLUMN possession VARCHAR(16) DEFAULT NULL,
ADD COLUMN last_play VARCHAR(255) DEFAULT NULL;

SELECT season, count(1) as games from scoreboard.massey_formatted mf group by season order by season asc;

select * from scoreboard.massey_formatted mf where season in (2000,2004,2005,2006);

-- add columns for indexing the join more efficiently
ALTER TABLE scoreboard.cfbd_formatted
ADD COLUMN dt DATE AFTER `time`;

UPDATE scoreboard.cfbd_formatted
SET dt = DATE(`time`);

ALTER TABLE scoreboard.cfbd_formatted 
MODIFY COLUMN dt DATE NOT NULL;

CREATE INDEX idx_game_dt ON scoreboard.cfbd_formatted (`dt`);

ALTER TABLE scoreboard.massey_formatted
ADD COLUMN earliest_dt DATE,
ADD COLUMN latest_dt DATE;

CREATE INDEX idx_time_range ON scoreboard.massey_formatted (earliest_dt, latest_dt);

UPDATE scoreboard.massey_formatted
SET earliest_dt = dt - INTERVAL 2 DAY, latest_dt = dt + INTERVAL 2 DAY;

ALTER TABLE scoreboard.massey_formatted 
MODIFY COLUMN earliest_dt DATE NOT NULL,
MODIFY COLUMN latest_dt DATE NOT NULL;

SELECT * FROM scoreboard.formatted_games
WHERE season = 2024
AND week = 7
UNION
SELECT * FROM scoreboard.live_games
WHERE season = 2024
AND week = 7  