use mysql;
--
-- DROP DATABASE IF EXISTS `nerdy_pts`;
--
CREATE DATABASE IF NOT EXISTS `nerdy_pts`;
--
-- DROP USER IF EXISTS `pts`;
--
-- DROP USER IF EXISTS 'pts';
--
-- DROP USER IF EXISTS 'pts'@'nerdy_pts';
--
CREATE USER IF NOT EXISTS 'pts' IDENTIFIED BY 'ptsP@ssw0rd';
--
CREATE USER IF NOT EXISTS  'pts'@'nerdy_pts' IDENTIFIED BY 'ptsP@ssw0rd';
--
GRANT ALL PRIVILEGES ON nerdy_pts.* TO 'pts'@'%';
--
GRANT ALL PRIVILEGES ON nerdy_pts.* TO 'pts'@'%' WITH GRANT OPTION;
--
GRANT ALL PRIVILEGES ON nerdy_pts.* TO 'pts'@'nerdy_pts' WITH GRANT OPTION;
--
USE nerdy_pts;
--
CREATE TABLE IF NOT EXISTS `boxscore` (
    `teamId`            INT NOT NULL,
    `gameId`            INT NOT NULL,
    `playerId`          INT NOT NULL,
    `summary`           VARCHAR(25),
    `gameDate`          VARCHAR(10),
    `battingOrder`      INT,
    `fullName`          VARCHAR(30),
    `jerseyNumber`      INT,
    `pos`               VARCHAR(7),
    `runs`              INT,
    `doubles`           INT,
    `triples`           INT,
    `homeRuns`          INT,
    `strikeOuts`        INT,
    `walks`             INT,
    `hits`              INT,
    `atBats`            INT,
    `caughtStealing`    INT,
    `stolenBases`       INT,
    `doublePlay`        INT,
    `triplePlay`        INT,
    `rbi`               INT,
    `sacrifices`        INT,
    `errors`            INT,
    `points`            INT(11)
        GENERATED ALWAYS AS (
            (`hits` - `doubles` - `triples` - `homeRuns`) +
            (`doubles` * 2) +
            (`triples` * 3) +
            (`homeRuns` * 4) +
            `rbi` + `runs` + `walks` + `sacrifices` + `stolenBases` -
            (`doublePlay` * 2) -
            `strikeOuts` - `errors` - `caughtStealing`)
        VIRTUAL,
    PRIMARY KEY(`teamId`,`playerId`,`gameId`));
--
CREATE TABLE IF NOT EXISTS team (
                                              teamId      INT,
                                              teamName    VARCHAR(25),
                                              teamCode    VARCHAR(3),
                                              teamAbbr    VARCHAR(6),
                                              shortName   VARCHAR(25),
                                              league      INT,
                                              division    INT,
                                              wins        INT,
                                              losses      INT,
                                              pct         VARCHAR(7),
                                              gb          VARCHAR(5),
                                              PRIMARY KEY(`teamId`));
--
CREATE TABLE IF NOT EXISTS game (
                                              gameId     INT     NOT NULL,
                                              gameStatus VARCHAR(25),
                                              startTime  VARCHAR(8),
                                              homeTeam   INT NOT NULL,
                                              awayTeam   INT NOT NULL,
                                              gameDate   DATE NOT NULL,
                                              gameType   VARCHAR(50) DEFAULT 'X',
                                              innTxt     VARCHAR(100),
                                              PRIMARY KEY(`gameId`));
--
CREATE TABLE IF NOT EXISTS linescore (
                                                   gameId         INT,
                                                   inning         INT,
                                                   currentInning  INT,
                                                   description    VARCHAR(5),
                                                   inningState    VARCHAR(6),
                                                   isTop          INT,
                                                   homeRuns       INT,
                                                   homeHits       INT,
                                                   homeErrs       INT,
                                                   homeLob        INT,
                                                   awayRuns       INT,
                                                   awayHits       INT,
                                                   awayErrs       INT,
                                                   awayLob        INT,
                                                   PRIMARY KEY(`gameId`,`inning`));
--
CREATE TABLE IF NOT EXISTS favorites (
                                                   userId         INT,
                                                   teamId         INT,
                                                   PRIMARY KEY(`userId`,`teamId`));
--
CREATE TABLE IF NOT EXISTS `pick` (
                                          `uid` INT NOT NULL DEFAULT 0,
                                          `tid` INT NOT NULL DEFAULT 0,
                                          `pid` INT NOT NULL DEFAULT 0,
                                          `gid` INT NOT NULL DEFAULT 0,
                                          primary key(`uid`,`tid`,`gid`));