SET @databaseName = 'nerdy_pts';
SET @databaseUser= 'nerdy_pts';
SET @databasePwd= 'iHittgv01$';
SET @tblGame = 'game';
SET @tblBoxscore = 'boxscore';
SET @tblLineScore = 'linescore';
SET @tblFavorites = 'favorites';
SET @tblTeam = 'team';
SET @tblUser = 'user';
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 @query = CONCAT('DROP DATABASE IF EXISTS  ',@databaseName);
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
SET @query = CONCAT('CREATE DATABASE IF NOT EXISTS ',@databaseName);
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
--
set @query = CONCAT('DROP USER IF EXISTS `', @databaseUser, '`@\'%\'');
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
--
set @query = CONCAT('DROP USER IF EXISTS ', @databaseUser, '@\'',@databaseName, '\'');
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
--
SET @query = CONCAT('CREATE USER \'',@databaseUser,'\'@\'%\' IDENTIFIED BY \'',@databasePwd,'\'');
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
--
SET @query = CONCAT('CREATE USER \'',@databaseUser,'\'@\'',@databaseName,'\' IDENTIFIED BY \'',@databasePwd,'\'');
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
--
SET @query = CONCAT('GRANT ALL PRIVILEGES ON ',@databaseName,'.* TO \'',@databaseUser,'\'@\'%\'');
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
--
SET @query = CONCAT('GRANT ALL PRIVILEGES ON ',@databaseName,'.* TO \'',@databaseUser,'\'@\'%\' WITH GRANT OPTION');
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
--
SET @query = CONCAT('GRANT ALL PRIVILEGES ON ',@databaseName,'.* TO \'',@databaseUser,'\'@\'',@databaseName, '\' WITH GRANT OPTION');
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
--

-- SET @query = CONCAT('USE ',@databaseName);
-- SELECT @query;
-- PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;


SET @query = CONCAT('
            CREATE TABLE IF NOT EXISTS ',@databaseName,'.',@tblUser,' (
                `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,
                PRIMARY KEY(`userName`))
    ');
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
--
SET @query = CONCAT('
            INSERT INTO ',@databaseName,'.',@tblUser,
                    ' (`userName`,`password`,`firstName`,`lastName`,`emailAddress`,`isAdmin`, `isActive`,`activationKey`)
                VALUES (\'admin\',\'admin\',\'admin\', \'admin\',\'\',1,1,\'\')');
select @query;
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;

--
set @query = CONCAT('
            CREATE TABLE IF NOT EXISTS ',@databaseName,'.',@tblBoxscore,' (
            `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`))');
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
--
set @query = CONCAT('
            CREATE TABLE IF NOT EXISTS ',@databaseName,'.',@tblGame,' (
                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`))');
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
--
set @query = CONCAT('
            CREATE TABLE IF NOT EXISTS ',@databaseName,'.',@tblLineScore,' (
                gameId         INT,
                currentInning  INT,
                inningState    VARCHAR(6),
                isTop          INT,
                homeRuns       INT,
                homeHits       INT,
                homeErrs       INT,
                homeLob        INT,
                awayRuns       INT,
                awayHits       INT,
                awayErrs       INT,
                awayLob        INT,
            PRIMARY KEY(`gameId`))');
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
--
set @query = CONCAT('
            CREATE TABLE IF NOT EXISTS ',@databaseName,'.',@tblFavorites,' (
                userId         INT,
                teamId         INT,
            PRIMARY KEY(`userId`,`teamId`))');
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
--
set @query = CONCAT('
            CREATE TABLE IF NOT EXISTS ',@databaseName,'.',@tblTeam,' (
                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`))');
PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
--
CREATE TABLE IF NOT EXISTS `pts_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`));