use `mysql`;
-- uninstall plugin validate_password;
install plugin validate_password soname 'validate_password.so';
SET FOREIGN_KEY_CHECKS = 0;
SET GLOBAL validate_password_policy = 0;
SET GLOBAL validate_password_length = 6;
SET GLOBAL validate_password_number_count = 0;
set GLOBAL max_connections = 1024;
SET PERSIST sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
-- DROP DATABASE IF EXISTS `nerdy_pts`;
CREATE DATABASE IF NOT EXISTS `nerdy_pts`;
--
-- DROP USER IF EXISTS 'nerdy_pts';
--
-- DROP USER IF EXISTS 'nerdy_pts';
DROP USER IF EXISTS pts@nerdy_pts;
--
CREATE USER IF NOT EXISTS 'pts' IDENTIFIED BY 'ptsP@ssw0rd';
--
CREATE USER '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 user (
                `userName`     VARCHAR(25) NOT NULL,
                `password`     VARCHAR(250) NOT NULL,
                `firstName`    VARCHAR(15) NOT NULL DEFAULT '',
                `lastName`     VARCHAR(25) NOT NULL DEFAULT '',
                `emailAddress` VARCHAR(50) NOT NULL,
                `isAdmin`      BOOLEAN DEFAULT FALSE,
                `isActive`     BOOLEAN DEFAULT FALSE,
                `activationKey` VARCHAR(50) NOT NULL,
                `userId`	   INT AUTO_INCREMENT,
                PRIMARY KEY (`userId`),
                UNIQUE KEY `userName` (`userName`)
);
--
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,
            `pickoffs`          INT NOT NULL DEFAULT 0,
            `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` - `pickoffs`)
                VIRTUAL,
            PRIMARY KEY(`teamId`,`playerId`,`gameId`));
--
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  VARCHAR(10) 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(25),
                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 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 `division` (
                divisionId   INT NOT NULL,
                divisionName VARCHAR(25),
                divisionAbbr VARCHAR(3),
                shortName    VARCHAR(25),
                league       INT NOT NULL,
        primary key(`divisionId`));
--
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`));
--
INSERT INTO nerdy_pts.user (`userName`,`password`,`firstName`,`lastName`,`emailAddress`,`isAdmin`, `isActive`,`activationKey`)
VALUES ('admin','admin','admin', 'admin','',1,1,'');