-- Database: testsuite

-- DROP DATABASE IF EXISTS testsuite;

CREATE DATABASE testsuite
    WITH
    OWNER = fpc
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_GB.UTF-8'
    LC_CTYPE = 'en_GB.UTF-8'
    TABLESPACE = pg_default
    template = 'template0'
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

\c testsuite

CREATE SEQUENCE SEQ_TESTCATEGORY as INT start with 1;

CREATE TABLE TESTCATEGORY (
  TA_ID INT NOT NULL DEFAULT nextval('SEQ_TESTCATEGORY'),
  TA_NAME VARCHAR(20),
  constraint PK_TESTCATEGORY PRIMARY KEY (TA_ID)
);

CREATE UNIQUE INDEX UDX_TESTCATEGORYNAME ON TESTCATEGORY(TA_NAME);

CREATE SEQUENCE SEQ_TESTCPU as INT start with 1;

CREATE TABLE TESTCPU (
  TC_ID INT NOT NULL DEFAULT nextval('SEQ_TESTCPU'),
  TC_NAME varchar(20),
  constraint PK_TESTCPU PRIMARY KEY (TC_ID)
);

CREATE UNIQUE INDEX UDX_TESTCPU ON TESTCPU(TC_NAME);

CREATE SEQUENCE SEQ_TESTOS as INT start with 1;

CREATE TABLE TESTOS (
  TO_ID INT DEFAULT nextval('SEQ_TESTOS'),
  TO_NAME varchar(20),
  constraint PK_TESTOS PRIMARY KEY (TO_ID)
);

CREATE UNIQUE INDEX UDX_TESTOS ON TESTOS(TO_NAME);

CREATE SEQUENCE SEQ_TESTVERSION as INT start with 1;

CREATE TABLE TESTVERSION (
  TV_ID int NOT NULL default nextval('SEQ_TESTVERSION'),
  TV_VERSION varchar(10),
  TV_RELEASEDATE date DEFAULT CURRENT_TIMESTAMP,
  constraint PK_TESTVERSION PRIMARY KEY (TV_ID)
);

CREATE UNIQUE INDEX UDX_TESTVERSION ON TESTVERSION(TV_VERSION);

CREATE SEQUENCE SEQ_TESTS as INT start with 1;

CREATE TABLE TESTS (
  T_ID int NOT NULL default nextval('SEQ_TESTS'),
  T_NAME varchar(127),
  T_CPU varchar(127),
  T_OS varchar(127),
  T_VERSION varchar(10),
  T_ADDDATE date NOT NULL,
  T_GRAPH boolean NOT NULL default 'f',
  T_INTERACTIVE boolean NOT NULL default 'f',
  T_RESULT int NOT NULL default '0',
  T_FAIL boolean NOT NULL default 'f',
  T_RECOMPILE boolean NOT NULL default 'f',
  T_NORUN boolean NOT NULL default 'f',
  T_NEEDLIBRARY boolean NOT NULL default 'f',
  T_KNOWNRUNERROR INT NOT NULL default 0,
  T_KNOWN boolean NOT NULL default 'f',
  T_NOTE varchar(255) default NULL,
  T_DESCRIPTION text,
  T_SOURCE text,
  T_OPTS varchar(255) default '',
  T_DELOPTS varchar(255) default '',
  T_SKIPCPU VARCHAR(64) default '',
  T_SKIPEMU VARCHAR(64) default '',
  T_NEEDTARGET VARCHAR(64) default '',  
  T_SKIPTARGET VARCHAR(64) default '',  
  T_MAXVERSION VARCHAR(16) default '',  
  T_KNOWNRUNNOTE VARCHAR(127) default '',  
  T_KNOWNCOMPILENOTE VARCHAR(127) default '',
  T_RECOMPILEOPT VARCHAR(127) default '',
  T_KNOWNCOMPILEERROR INT DEFAULT 0,
  T_NEEDEDAFTER boolean default 'f',
  T_ISKNOWNRUNERROR Boolean default 'f',
  T_Timeout INT DEFAULT 0,
  T_CATEGORY VARCHAR(127) default '',
  T_FILES VARCHAR(127) default '',
  T_CONFIGFILESRC VARCHAR(127) default '',
  T_CONFIGFILEDST VARCHAR(127) default '',
  T_WPOPARAS VARCHAR(127) default '',
  T_WPOPASSES INT DEFAULT 0,
  T_DELFILES VARCHAR(127) default '',
  T_EXPECTMSGS VARCHAR(127) default '',
  constraint PK_TESTS PRIMARY KEY  (T_ID)
);

CREATE UNIQUE INDEX UDX_TESTS ON TESTS(T_NAME);

CREATE SEQUENCE SEQ_TESTPLATFORM as INT start with 1;

CREATE TABLE TESTPLATFORM (
  TP_ID INT NOT NULL default nextval('SEQ_TESTPLATFORM'),
  TP_CPU_FK INT NOT NULL,
  TP_OS_FK INT NOT NULL,
  TP_VERSION_FK INT NOT NULL,
  TP_CATEGORY_FK int NOT NULL,
  TP_CONFIG VARCHAR(255) NOT NULL,
  TP_MACHINE VARCHAR(64) NOT NULL,
  constraint PK_TESTPLATFORM PRIMARY KEY (TP_ID)
);

CREATE UNIQUE INDEX UDX_TESTPLATFORM ON TESTPLATFORM(TP_CPU_FK,TP_OS_FK,TP_VERSION_FK,TP_CATEGORY_FK,TP_CONFIG,TP_MACHINE);
CREATE INDEX IDX_TESTPLATFORMRELATIONS ON TESTPLATFORM(TP_CPU_FK,TP_OS_FK,TP_VERSION_FK,TP_CATEGORY_FK);

CREATE SEQUENCE SEQ_TESTRUN as BIGINT start with 1;

CREATE TABLE TESTRUN (
  TU_ID bigint NOT NULL default nextval('SEQ_TESTRUN'),
  TU_DATE DATE NOT NULL default CURRENT_DATE,
  TU_PLATFORM_FK int NOT NULL,
  TU_COMPILERDATE VARCHAR(15),
  TU_COMPILERFULLVERSION VARCHAR(50),
  TU_COMPILERREVISION VARCHAR(50),
  TU_TESTSREVISION VARCHAR(50),
  TU_RTLREVISION VARCHAR(50),
  TU_PACKAGESREVISION VARCHAR(50),
  TU_SUBMITTER varchar(128) NOT NULL default '',
  TU_FAILEDTOCOMPILE int NOT NULL default 0,
  TU_SUCCESSFULLYFAILED int NOT NULL default 0,
  TU_FAILEDTOFAIL int NOT NULL default 0,
  TU_SUCCESFULLYCOMPILED int NOT NULL default 0,
  TU_FAILEDTORUN int NOT NULL default 0,
  TU_KNOWNPROBLEM int NOT NULL default 0,
  TU_SUCCESSFULLYRUN int NOT NULL default 0,
  TU_SKIPPEDGRAPHTEST int NOT NULL default 0,
  TU_SKIPPEDINTERACTIVETEST int NOT NULL default 0,
  TU_KNOWNBUG int NOT NULL default 0,
  TU_COMPILERVERIONTOOLOW int NOT NULL default 0,
  TU_COMPILERVERIONTOOHIGH int NOT NULL default 0,
  TU_OTHERCPU int NOT NULL default 0,
  TU_OTHERTARGET int NOT NULL default 0,
  TU_UNIT int NOT NULL default 0,
  TU_SKIPPINGRUNTEST int NOT NULL default 0,
  CONSTRAINT PK_TESTRUN PRIMARY KEY (TU_ID)
);

CREATE UNIQUE INDEX UDX_TESTRUN ON TESTRUN(TU_DATE,TU_PLATFORM_FK);
CREATE INDEX IDX_TESTRUNPLATFORM ON TESTRUN(TU_PLATFORM_FK,TU_DATE);

CREATE SEQUENCE SEQ_TESTRESULTS as BIGINT start with 1;

CREATE TABLE TESTRESULTS (
  TR_ID bigint NOT NULL default nextval('SEQ_TESTRESULTS'),
  TR_TESTRUN_FK bigint NOT NULL default 0,
  TR_TEST_FK int not NULL,
  TR_OK Boolean NOT NULL default 'f',
  TR_SKIP boolean NOT NULL default 'f',
  TR_RESULT int NOT NULL default 0,
  TR_LOG text,
  constraint PK_TESTRESULTS PRIMARY KEY (TR_ID)
);

CREATE UNIQUE INDEX UDX_TESTRESULTS ON TESTRESULTS (TR_TESTRUN_FK,TR_TEST_FK);

CREATE TABLE TESTLASTRESULTS (
  TL_TEST_FK int not NULL,
  TL_PLATFORM_FK int not NULL,
  TL_TESTRESULTS_FK bigint NOT NULL
);

CREATE UNIQUE INDEX UDX_TESTLASTRESULTS ON TESTLASTRESULTS(TL_PLATFORM_FK,TL_TEST_FK);

CREATE TABLE TESTPREVIOUSRESULTS (
  TPR_TEST_FK int not NULL,
  TPR_PLATFORM_FK int not NULL,
  TPR_TESTRESULTS_FK bigint NOT NULL
);

CREATE UNIQUE INDEX UDX_TESTPREVIOUSRESULTS ON TESTPREVIOUSRESULTS(TPR_PLATFORM_FK,TPR_TEST_FK);

CREATE TABLE TESTRUNHISTORY (
  TH_ID_FK BIGINT,
  TH_PREVIOUS_FK BIGINT,
  CONSTRAINT PK_TESTRUNHISTORY PRIMARY KEY (TH_ID_FK)	
);
CREATE INDEX IDX_PREVIOUS ON TESTRUNHISTORY(TH_PREVIOUS_FK);

create sequence seqCheckAll;

create table CHECKALLRTL (
  CA_ID BIGINT NOT NULL default nextval('seqCheckAll'),
  CA_PLATFORM_FK INT NOT NULL,
  CA_DATE DATE NOT NULL DEFAULT CURRENT_DATE,
  CA_STEP1 BOOLEAN NOT NULL DEFAULT 't',
  CA_STEP2 BOOLEAN NOT NULL DEFAULT 't',
  CA_STEP3 BOOLEAN NOT NULL DEFAULT 't',
  CA_STEP4 BOOLEAN NOT NULL DEFAULT 't',
  CA_STEP5 BOOLEAN NOT NULL DEFAULT 't',
  CA_STEP6 BOOLEAN NOT NULL DEFAULT 't',
  CONSTRAINT PK_CHECKALLRTL PRIMARY KEY (CA_ID)
);


create sequence seqCheckAllLog;

create table CHECKALLRTLLOG (
  CAL_ID BIGINT NOT NULL default nextval('seqCheckAllLog'),
  CAL_CHECKALLRTL_FK BIGINT NOT NULL,
  CAL_STEP SMALLINT NOT NULL,
  CAL_LOG text,
  CONSTRAINT PK_PK_CHECKALLRTLLOG PRIMARY KEY (CAL_ID)
);


CREATE UNIQUE INDEX UDX_CHECKALLRTLLOG ON CHECKALLRTLLOG(CAL_CHECKALLRTL_FK,CAL_STEP);


-- Create relations

ALTER TABLE testplatform ADD CONSTRAINT fk_plaform_os FOREIGN KEY (tp_os_fk) REFERENCES testos (to_id);
ALTER TABLE testplatform ADD CONSTRAINT fk_plaform_cpu FOREIGN KEY (tp_cpu_fk) REFERENCES testcpu (tc_id);
ALTER TABLE testplatform ADD CONSTRAINT fk_plaform_version FOREIGN KEY (tp_version_fk) REFERENCES testversion (tv_id);
ALTER TABLE testplatform ADD CONSTRAINT fk_plaform_category FOREIGN KEY (tp_category_fk) REFERENCES testcategory (ta_id);
ALTER TABLE testrun ADD CONSTRAINT fk_run_platform FOREIGN KEY (tu_platform_fk) REFERENCES testplatform (tp_id);
ALTER TABLE testresults ADD CONSTRAINT fk_results_run FOREIGN KEY (tr_testrun_fk) REFERENCES testrun (tu_id);
ALTER TABLE testresults ADD CONSTRAINT fk_results_test FOREIGN KEY (tr_test_fk) REFERENCES tests (t_id);
ALTER TABLE CHECKALLRTL ADD CONSTRAINT CHECKALLRTL_PLATFORM_FK FOREIGN KEY (CA_PLATFORM_FK) references testplatform (TP_ID);
ALTER TABLE CHECKALLRTLLOG ADD CONSTRAINT CHECKALLRTLLOG_CHECKALLRTL_FK FOREIGN KEY (CAL_CHECKALLRTL_FK) REFERENCES CHECKALLRTL (CA_ID);

-- Insert default data

INSERT INTO TESTCATEGORY VALUES (1, 'Compiler/RTL');
INSERT INTO TESTCATEGORY VALUES (2, 'DB');
ALTER SEQUENCE SEQ_TESTCATEGORY RESTART WITH 3;

INSERT INTO TESTCPU VALUES (1, 'i386');
INSERT INTO TESTCPU VALUES (6, 'arm');
INSERT INTO TESTCPU VALUES (3, 'm68k');
INSERT INTO TESTCPU VALUES (4, 'sparc');
INSERT INTO TESTCPU VALUES (5, 'powerpc');
INSERT INTO TESTCPU VALUES (7, 'x86_64');
INSERT INTO TESTCPU VALUES (8, 'All');
INSERT INTO TESTCPU VALUES (9, 'powerpc64');
INSERT INTO TESTCPU VALUES (10, 'wasm32');
INSERT INTO TESTCPU VALUES (11, 'longsoon');
INSERT INTO TESTCPU VALUES (12, 'longsoon64');
INSERT INTO TESTCPU VALUES (13, 'mips');
INSERT INTO TESTCPU VALUES (14, 'mips64');
INSERT INTO TESTCPU VALUES (15, 'avr');
INSERT INTO TESTCPU VALUES (16, 'xtensa');
ALTER SEQUENCE SEQ_TESTCPU RESTART WITH 17;

INSERT INTO TESTOS VALUES (1, 'linux');
INSERT INTO TESTOS VALUES (2, 'win32');
INSERT INTO TESTOS VALUES (3, 'go32v2');
INSERT INTO TESTOS VALUES (4, 'os2');
INSERT INTO TESTOS VALUES (5, 'freebsd');
INSERT INTO TESTOS VALUES (6, 'netbsd');
INSERT INTO TESTOS VALUES (7, 'openbsd');
INSERT INTO TESTOS VALUES (8, 'amiga');
INSERT INTO TESTOS VALUES (9, 'atari');
INSERT INTO TESTOS VALUES (10, 'qnx');
INSERT INTO TESTOS VALUES (11, 'beos');
INSERT INTO TESTOS VALUES (12, 'solaris');
INSERT INTO TESTOS VALUES (13, 'darwin');
INSERT INTO TESTOS VALUES (14, 'macos');
INSERT INTO TESTOS VALUES (15, 'All');
INSERT INTO TESTOS VALUES (16, 'win64');
INSERT INTO TESTOS VALUES (17, 'wince');
INSERT INTO TESTOS VALUES (18, 'wasi');
ALTER SEQUENCE SEQ_TESTOS RESTART WITH 19;

INSERT INTO TESTVERSION VALUES (1, '2.0.4', '2006-08-22 22:38:20');
INSERT INTO TESTVERSION VALUES (2, '2.0.5', '2006-08-22 22:38:20');
INSERT INTO TESTVERSION VALUES (3, '2.3.1', '2007-03-04 23:40:07');
INSERT INTO TESTVERSION VALUES (4, '2.1.2', '2007-03-19 10:49:30');
INSERT INTO TESTVERSION VALUES (5, '2.1.3', '2007-03-19 10:49:47');
INSERT INTO TESTVERSION VALUES (6, '2.1.4', '2007-03-19 10:50:03');
INSERT INTO TESTVERSION VALUES (7, '3.2.2', '2020-04-01 10:50:03');
INSERT INTO TESTVERSION VALUES (8, '3.3.1', '2020-04-01 10:50:03');
ALTER SEQUENCE SEQ_TESTVERSION RESTART WITH 9;