SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;



DROP TABLE IF EXISTS extinfo;
CREATE TABLE `extinfo` (
  `ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `BallSpeedsMMSDelimited` varchar(255) DEFAULT NULL,
  `BallLaneNumbersDelimited` varchar(255) DEFAULT NULL,
  `OriginalScoresData` varchar(2500) DEFAULT NULL,
  `SessionGameID` int(11) DEFAULT NULL,
  `orgID` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `orgID` (`orgID`),
  KEY `SessionGameID` (`SessionGameID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS games;
CREATE TABLE `games` (
  `ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `GameType` varchar(50) DEFAULT NULL,
  `orgID` int(11) NOT NULL,
  `PlayerIdentifierBrunswick` varchar(50) DEFAULT NULL,
  `GameNumber` int(11) DEFAULT NULL,
  `TimeStart` datetime DEFAULT NULL,
  `TimeEnd` datetime DEFAULT NULL,
  `LaneIDNumber` int(11) DEFAULT NULL,
  `ScratchTotal` int(11) DEFAULT NULL,
  `BallScoresDelimited` varchar(2000) DEFAULT NULL,
  `BallMasksDelimited` varchar(1000) DEFAULT NULL,
  `StandingPinsDelimited` varchar(100) DEFAULT NULL,
  `FrameScoresDelimited` varchar(100) DEFAULT NULL,
  `FramesCompleted` int(11) DEFAULT NULL,
  `GameIdentifierBrunswick` varchar(500) DEFAULT NULL,
  `PrebowledScratchTotal` int(11) DEFAULT NULL,
  `DateStart` date DEFAULT NULL,
  `Position` int(11) DEFAULT NULL,
  `SessionID` int(11) DEFAULT NULL,
   PRIMARY KEY (`ID`),
   UNIQUE KEY `orgID` (`orgID`),
   KEY `ScratchTotal` (`ScratchTotal`),
   KEY `PlayerIdentifierBrunswick` (`PlayerIdentifierBrunswick`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS players;
CREATE TABLE `players` (
  `ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `IdentifierBrunswick` varchar(500) DEFAULT NULL,
  `NameDisplay` varchar(255) DEFAULT NULL,
  `SeriesScratchTotal` int(11) DEFAULT NULL,
  `SeriesHandicapTotal` int(11) DEFAULT NULL,
  `TeamPosition` int(11) DEFAULT NULL,
  `ProfileData` varchar(2000) DEFAULT NULL,
  `orgID` int(11) NOT NULL,
   PRIMARY KEY (`ID`),
   UNIQUE KEY `orgID` (`orgID`),
   KEY `IdentifierBrunswick` (`IdentifierBrunswick`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



DROP VIEW IF EXISTS v_komplett;
CREATE VIEW v_komplett As
SELECT 
GameType as G_GameType, 
games.ID as G_ID, 
players.ID as P_ID, 
extinfo.ID as E_ID, 
PlayerIdentifierBrunswick as G_PlayerIdentifierBrunswick, 
GameNumber as G_GameNumber, 
TimeStart as G_TimeStart, 
TimeEnd as G_TimeEnd, 
LaneIDNumber as G_LaneIDNumber, 
ScratchTotal as G_ScratchTotal, 
BallScoresDelimited as G_BallScoresDelimited, 
BallMasksDelimited as G_BallMasksDelimited, 
StandingPinsDelimited as G_StandingPinsDelimited, 
FrameScoresDelimited as G_FrameScoresDelimited, 
FramesCompleted as G_FramesCompleted, 
GameIdentifierBrunswick as G_GameIdentifierBrunswick, 
PrebowledScratchTotal as G_PrebowledScratchTotal, 
DateStart as G_DateStart, 
Position as G_Position, 
games.SessionID as G_SessionID, 
IdentifierBrunswick as P_IdentifierBrunswick, 
NameDisplay as P_NameDisplay, 
SeriesScratchTotal as P_SeriesScratchTotal, 
SeriesHandicapTotal as P_SeriesHandicapTotal, 
TeamPosition as P_TeamPosition, 
ProfileData as P_ProfileData, 
BallSpeedsMMSDelimited as E_BallSpeedsMMSDelimited, 
BallLaneNumbersDelimited as E_BallLaneNumbersDelimited, 
OriginalScoresData as E_OriginalScoresData, 
SessionGameID as E_SessionGameID
FROM games 
JOIN players on players.IdentifierBrunswick = games.PlayerIdentifierBrunswick 
JOIN extinfo on games.orgID =  extinfo.SessionGameID;

-- VIEW & TABLE for Livescoring
DROP VIEW IF EXISTS v_live;
CREATE VIEW v_live AS
SELECT 
players.IdentifierBrunswick, players.NameDisplay, players.SeriesScratchTotal, players.TeamPosition,
games.GameNumber, games.TimeStart, games.TimeEnd, games.LaneIDNumber, games.ScratchTotal, games.BallScoresDelimited, 
games.StandingPinsDelimited, games.FrameScoresDelimited, games.FramesCompleted, games.SessionID, extinfo.BallSpeedsMMSDelimited
FROM games
JOIN players ON players.IdentifierBrunswick = games.PlayerIdentifierBrunswick
JOIN extinfo ON games.orgID = extinfo.SessionGameID;

DROP TABLE IF EXISTS datenstand;
CREATE TABLE `datenstand` (
	`ID` INT(11) NOT NULL,
	`datenstand` DATETIME NULL,
	PRIMARY KEY (`ID`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;

INSERT INTO datenstand (ID) VALUES (1);