CREATE VIEW scoreboard.formatted_games AS
SELECT
    cf.id as id,
    mf.season as season,
    mf.week as week,
    IFNULL(cf.time, mf.dt) as `time`,
    IFNULL(cf.home_name, IF(mf.away_name = cf.home_name, mf.away_name, mf.home_name)) AS home_name,
    IFNULL(cf.away_name, IF(mf.home_name = cf.away_name, mf.home_name, mf.away_name)) AS away_name,
    IFNULL(cf.home_logo, IF(mf.away_name = cf.home_name, mf.away_logo, mf.home_logo)) AS home_logo,
    IFNULL(cf.away_logo, IF(mf.home_name = cf.away_name, mf.home_logo, mf.away_logo)) AS away_logo,
    IFNULL(cf.home_division, IF(mf.away_name = cf.home_name, mf.away_division, mf.home_division)) AS home_division,
    IFNULL(cf.away_division, IF(mf.home_name = cf.away_name, mf.home_division, mf.away_division)) AS away_division,
    IFNULL(cf.home_conference, IF(mf.away_name = cf.home_name, mf.away_conference, mf.home_conference)) AS home_conference,
    IFNULL(cf.away_conference, IF(mf.home_name = cf.away_name, mf.home_conference, mf.away_conference)) AS away_conference,
    IFNULL(cf.home_conf_logo, IF(mf.away_name = cf.home_name, mf.away_conf_logo, mf.home_conf_logo)) AS home_conf_logo,
    IFNULL(cf.away_conf_logo, IF(mf.home_name = cf.away_name, mf.home_conf_logo, mf.away_conf_logo)) AS away_conf_logo,
    IFNULL(cf.home_score, IF(mf.away_name = cf.home_name, mf.away_score, mf.home_score)) AS home_score,
    IFNULL(cf.away_score, IF(mf.home_name = cf.away_name, mf.home_score, mf.away_score)) AS away_score,
    cf.home_line_scores AS home_line_scores,
    cf.away_line_scores AS away_line_scores,
    IFNULL(cf.home_rank, IF(mf.away_name = cf.home_name, mf.away_rank, mf.home_rank)) AS home_rank,
    IFNULL(cf.away_rank, IF(mf.home_name = cf.away_name, mf.home_rank, mf.away_rank)) AS away_rank,
    IFNULL(cf.home_record, IF(mf.away_name = cf.home_name, mf.away_record, mf.home_record)) AS home_record,
    IFNULL(cf.away_record, IF(mf.home_name = cf.away_name, mf.home_record, mf.away_record)) AS away_record,
    IFNULL(cf.`status`, mf.`status`) AS `status`,
    cf.overtime AS overtime,
    cf.spread AS spread,
    cf.over_under AS over_under,
    cf.notes AS notes,
    cf.clock as live_clock,
    cf.tv as live_tv,
    cf.period as live_period,
    cf.situation as live_situation,
    cf.possession as live_possession,
    cf.last_play as live_last_play
FROM scoreboard.massey_formatted mf
LEFT JOIN scoreboard.cfbd_formatted cf
    ON mf.season = cf.season
    AND mf.week = cf.week
    AND cf.dt >= mf.earliest_dt AND cf.dt <= mf.latest_dt 
    AND ((mf.home_name = cf.home_name AND mf.away_name = cf.away_name) 
    OR (mf.away_name = cf.home_name AND mf.home_name = cf.away_name));

CREATE VIEW scoreboard.live_games AS
SELECT
    cf.id as id,
    cf.season as season,
    cf.week as week,
    cf.time as `time`,
    cf.home_name AS home_name,
    cf.away_name AS away_name,
    cf.home_logo AS home_logo,
    cf.away_logo AS away_logo,
    cf.home_division AS home_division,
    cf.away_division AS away_division,
    cf.home_conference AS home_conference,
    cf.away_conference AS away_conference,
    cf.home_conf_logo AS home_conf_logo,
    cf.away_conf_logo AS away_conf_logo,
    cf.home_score AS home_score,
    cf.away_score AS away_score,
    cf.home_line_scores AS home_line_scores,
    cf.away_line_scores AS away_line_scores,
    cf.home_rank AS home_rank,
    cf.away_rank AS away_rank,
    cf.home_record AS home_record,
    cf.away_record AS away_record,
    cf.`status` AS `status`,
    cf.overtime AS overtime,
    cf.spread AS spread,
    cf.over_under AS over_under,
    cf.notes AS notes,
    cf.clock as live_clock,
    cf.tv as live_tv,
    cf.period as live_period,
    cf.situation as live_situation,
    cf.possession as live_possession,
    cf.last_play as live_last_play
FROM scoreboard.cfbd_formatted cf;

