DROP TABLE EngineVersion;

CREATE TABLE EngineVersion (
       VersionID            INTEGER NOT NULL,
       EngineVersion        VARCHAR(255),
       VersionDate          DATE,
       PRIMARY KEY (VersionID)
);


DROP TABLE Engine;

CREATE TABLE Engine (
       EngineID             INTEGER NOT NULL,
       VersionID            INTEGER NOT NULL,
       EngineName           VARCHAR(255),
       EngineDate           TIMESTAMP,
       PRIMARY KEY (EngineID, VersionID), 
       FOREIGN KEY (VersionID)
                             REFERENCES EngineVersion
                             ON DELETE RESTRICT
);


DROP TABLE Country;

CREATE TABLE Country (
       CountryID            INTEGER NOT NULL,
       Abbreviation         VARCHAR(8),
       CountryName          VARCHAR(255),
       PRIMARY KEY (CountryID)
);


DROP TABLE EngineAuthor;

CREATE TABLE EngineAuthor (
       AuthorID             INTEGER NOT NULL,
       FirstName            VARCHAR(255),
       LastName             VARCHAR(255),
       MiddleName           VARCHAR(255),
       Title                VARCHAR(255),
       CountryID            INTEGER NOT NULL,
       PRIMARY KEY (AuthorID), 
       FOREIGN KEY (CountryID)
                             REFERENCES Country
                             ON DELETE SET NULL
);

CREATE INDEX _WA_Sys_CountryID_ ON EngineAuthor
(
       CountryID                      ASC
);

CREATE INDEX _WA_Sys_FirstName_ ON EngineAuthor
(
       FirstName                      ASC
);

CREATE INDEX _WA_Sys_LastName_0 ON EngineAuthor
(
       LastName                       ASC
);


DROP TABLE AuthorList;

CREATE TABLE AuthorList (
       AuthorListID         INTEGER NOT NULL,
       AuthorID             INTEGER NOT NULL,
       EngineID             INTEGER NOT NULL,
       VersionID            INTEGER NOT NULL,
       PRIMARY KEY (AuthorListID, AuthorID, EngineID, VersionID), 
       FOREIGN KEY (EngineID, VersionID)
                             REFERENCES Engine
                             ON DELETE RESTRICT, 
       FOREIGN KEY (AuthorID)
                             REFERENCES EngineAuthor
                             ON DELETE RESTRICT
);


DROP TABLE Machine;

CREATE TABLE Machine (
       MachineID            INTEGER NOT NULL,
       MB_Hash_Total        INTEGER NOT NULL,
       MachineName          VARCHAR(25) NOT NULL,
       CPU_Vendor           VARCHAR(25),
       CPU_Model            VARCHAR(25),
       CPU_ID_NameString    VARCHAR(25),
       CPU_ID_Signature     CHAR(4),
       MB_RAM_Total         INTEGER,
       PRIMARY KEY (MachineID, MB_Hash_Total)
);


DROP TABLE BoardPosition;

CREATE TABLE BoardPosition (
       HashCode             CHAR(9) NOT NULL,
       EpdPosition          VARCHAR(255) NOT NULL,
       PRIMARY KEY (HashCode),
       UNIQUE (
              EpdPosition
       )
);

CREATE UNIQUE INDEX XAK1BoardPosition ON BoardPosition
(
       EpdPosition                    ASC
);


DROP TABLE EngineEvaluation;

CREATE TABLE EngineEvaluation (
       HashCode             CHAR(9) NOT NULL,
       MachineID            INTEGER NOT NULL,
       VersionID            INTEGER NOT NULL,
       MB_Hash_Total        INTEGER NOT NULL,
       Flags                INTEGER,
       Centipawns           INTEGER,
       PlyDepth             INTEGER,
       LogNodes             FLOAT,
       EngineID             INTEGER NOT NULL,
       bm                   CHAR(8),
       er                   CHAR(8),
       pm                   CHAR(8),
       am                   CHAR(8),
       AnalysisSeconds      INTEGER,
       PRIMARY KEY (HashCode, MachineID, MB_Hash_Total, VersionID, 
              EngineID), 
       FOREIGN KEY (MachineID, MB_Hash_Total)
                             REFERENCES Machine
                             ON DELETE RESTRICT, 
       FOREIGN KEY (HashCode)
                             REFERENCES BoardPosition
                             ON DELETE RESTRICT, 
       FOREIGN KEY (EngineID, VersionID)
                             REFERENCES Engine
                             ON DELETE RESTRICT
);

CREATE INDEX _WA_Sys_EngineID_1 ON EngineEvaluation
(
       EngineID                       ASC
);


DROP TABLE PositionStatistic;

CREATE TABLE PositionStatistic (
       HashCode             CHAR(9) NOT NULL,
       Wins                 INTEGER,
       Losses               INTEGER,
       Draws                INTEGER,
       Unfinished           INTEGER,
       EloSum               FLOAT,
       EloCount             INTEGER,
       PRIMARY KEY (HashCode), 
       FOREIGN KEY (HashCode)
                             REFERENCES BoardPosition
                             ON DELETE RESTRICT
);


DROP TABLE ChessBook;

CREATE TABLE ChessBook (
       BookCode             INTEGER NOT NULL,
       BookName             VARCHAR(255),
       ISBN                 VARCHAR(20),
       LCCN                 VARCHAR(20),
       UPC                  VARCHAR(20),
       PRIMARY KEY (BookCode)
);


DROP TABLE BookOpinion;

CREATE TABLE BookOpinion (
       BookCode             INTEGER NOT NULL,
       HashCode             CHAR(9) NOT NULL,
       ce                   INTEGER,
       NAG                  VARCHAR(20),
       bm                   CHAR(8),
       er                   CHAR(8),
       pm                   CHAR(8),
       am                   CHAR(8),
       PRIMARY KEY (BookCode, HashCode), 
       FOREIGN KEY (HashCode)
                             REFERENCES BoardPosition
                             ON DELETE RESTRICT, 
       FOREIGN KEY (BookCode)
                             REFERENCES ChessBook
                             ON DELETE RESTRICT
);