** Neu machen mit Indizies für order und join! **

CREATE DATABASE sync;

DROP TABLE IF EXISTS games;
CREATE TABLE games (  
ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
GameType varchar(50), 
orgID int NOT NULL UNIQUE, 
PlayerIdentifierBrunswick varchar(50), 
GameNumber int, 
TimeStart datetime, 
TimeEnd datetime, 
LaneIDNumber int, 
ScratchTotal int, 
BallScoresDelimited varchar(100), 
BallMasksDelimited varchar(100), 
StandingPinsDelimited varchar(100), 
FrameScoresDelimited varchar(100), 
FramesCompleted int, 
GameIdentifierBrunswick varchar(100), 
PrebowledScratchTotal int, 
DateStart date, 
Position int, 
SessionID int, 
PRIMARY KEY (ID)) COLLATE='utf8mb4_general_ci';

DROP TABLE IF EXISTS players;
CREATE TABLE players (  
ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
IdentifierBrunswick varchar(50), 
NameDisplay varchar(255), 
SeriesScratchTotal int, 
SeriesHandicapTotal int, 
TeamPosition int, 
ProfileData varchar(255), 
orgID int NOT NULL UNIQUE, 
PRIMARY KEY (ID)) COLLATE='utf8mb4_general_ci';

DROP TABLE IF EXISTS extinfo;
CREATE TABLE extinfo (  
ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
BallSpeedsMMSDelimited varchar(255), 
BallLaneNumbersDelimited varchar(255), 
OriginalScoresData varchar(2500), 
SessionGameID int, 
orgID int NOT NULL UNIQUE, 
PRIMARY KEY (ID)) COLLATE='utf8mb4_general_ci';

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 
