#
# Copyright (c) 2018, 2020 Oracle and/or its affiliates. All rights reserved.
#
# This program and the accompanying materials are made available under the
# terms of the Eclipse Public License v. 2.0 which is available at
# http://www.eclipse.org/legal/epl-2.0,
# or the Eclipse Distribution License v. 1.0 which is available at
# http://www.eclipse.org/org/documents/edl-v10.php.
#
# SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
#


CREATE TABLE JPA21_XML_FORK (ID INTEGER NOT NULL, COLOR VARCHAR(255), SERIAL_TAG VARCHAR(255), STYLE VARCHAR(255), PRIMARY KEY (ID))
CREATE INDEX INDEX_JPA21_XML_FORK_STYLE_COLOR ON JPA21_XML_FORK (STYLE(15), COLOR(15))
CREATE TABLE JPA21_XML_FORK_COSTS (ID INTEGER NOT NULL, COST DOUBLE, RENTAL_COST DOUBLE, PRIMARY KEY (ID))
CREATE INDEX INDEX_JPA21_XML_FORK_COSTS ON JPA21_XML_FORK_COSTS (COST, RENTAL_COST)
CREATE TABLE JPA21_XML_DDL_ORGANIZER (ID INTEGER NOT NULL, NAME VARCHAR(255), RACE_ID INTEGER, DESCRIPTION VARCHAR(255), UNIQUEIDENTIFIER BIGINT, PRIMARY KEY (ID))
CREATE TABLE JPA21_XML_DDL_ENDORSER (ID INTEGER NOT NULL, NAME VARCHAR(255), PRIMARY KEY (ID))
CREATE TABLE JPA21_XML_DDL_RACE (ID INTEGER NOT NULL, NAME VARCHAR(255), PRIMARY KEY (ID))
CREATE TABLE JPA21_XML_DDL_RUNNER (ID INTEGER NOT NULL, DTYPE VARCHAR(31), AGE INTEGER, F_NAME VARCHAR(255), GENDER VARCHAR(255), L_NAME VARCHAR(255), R_HEALTH VARCHAR(255), R_LEVEL VARCHAR(255), R_STATUS VARCHAR(255), PRIMARY KEY (ID))
CREATE TABLE JPA21_XML_DDL_SHOE_TAG (ID INTEGER NOT NULL, TAG VARCHAR(255), PRIMARY KEY (ID))
CREATE TABLE JPA21_XML_FORK_USER (ID INTEGER NOT NULL, NAME VARCHAR(255), PRIMARY KEY (ID))
CREATE TABLE JPA21_XML_DDL_SHOE (ID INTEGER NOT NULL, BRAND VARCHAR(255), MODEL VARCHAR(255), SIZE INTEGER, RUNNER_ID INTEGER, TAG_ID INTEGER, PRIMARY KEY (ID))
CREATE TABLE JPA21_XML_DDL_SPRINTER (SPRINTER_ID INTEGER NOT NULL, PRIMARY KEY (SPRINTER_ID))
CREATE TABLE JPA21_XML_FORK_USES (XMLFork_ID INTEGER, DESCRIP VARCHAR(255))
CREATE INDEX JPA21_XML_FORK_USES_INDEX ON JPA21_XML_FORK_USES (DESCRIP)
CREATE TABLE JPA21_XML_FORK_FORK_USERS (FORK_ID INTEGER NOT NULL, FORK_USER_ID INTEGER NOT NULL, PRIMARY KEY (FORK_ID, FORK_USER_ID))
CREATE INDEX JPA21_XML_FORK_USERS_INDEX ON JPA21_XML_FORK_FORK_USERS (FORK_USER_ID, FORK_ID)
CREATE TABLE JPA21_XML_DDL_RUNNERS_RACES (RACE_ID INTEGER NOT NULL, RUNNER_ID INTEGER NOT NULL, PRIMARY KEY (RACE_ID, RUNNER_ID))
CREATE TABLE JPA21_XML_DDL_RUNNER_ACS (ATHLETE_ID INTEGER, THE_DATE BIGINT, ACCOMPLISHMENT VARCHAR(255))
CREATE TABLE JPA21_XML_DDL_ENDORSEMENTS (ATHLETE_ID INTEGER, ENDORSEMENT INTEGER, ENDORSER_ID INTEGER)
CREATE TABLE JPA21_XML_DDL_RUNNER_PBS (RUNNER_ID INTEGER, TIME VARCHAR(255), DISTANCE VARCHAR(255))

# A comment that should be ignored.
    
ALTER TABLE JPA21_XML_FORK_COSTS ADD CONSTRAINT FK_JPA21_XML_FORK_COSTS_ID FOREIGN KEY (ID) REFERENCES JPA21_XML_FORK (ID)
ALTER TABLE JPA21_XML_DDL_ORGANIZER ADD CONSTRAINT FK_JPA21_XMLOrganizer_Race FOREIGN KEY (RACE_ID) REFERENCES JPA21_XML_DDL_RACE (ID)
ALTER TABLE JPA21_XML_DDL_SHOE ADD CONSTRAINT FK_JPA21_XMLRunner_ShoeTag FOREIGN KEY (TAG_ID) REFERENCES JPA21_XML_DDL_SHOE_TAG (ID)
ALTER TABLE JPA21_XML_DDL_SHOE ADD CONSTRAINT FK_JPA21_XMLShoes_Runner FOREIGN KEY (RUNNER_ID) REFERENCES JPA21_XML_DDL_RUNNER (ID)
ALTER TABLE JPA21_XML_DDL_SPRINTER ADD CONSTRAINT FK_JPA21_XML_Sprinter FOREIGN KEY (SPRINTER_ID) REFERENCES JPA21_XML_DDL_RUNNER (ID)
ALTER TABLE JPA21_XML_FORK_USES ADD CONSTRAINT FK_JPA21_XML_FORK_USES_XMLFork_ID FOREIGN KEY (XMLFork_ID) REFERENCES JPA21_XML_FORK (ID)
ALTER TABLE JPA21_XML_FORK_FORK_USERS ADD CONSTRAINT FK_JPA21_XML_FORK_FORK_USERS_FORK_ID FOREIGN KEY (FORK_ID) REFERENCES JPA21_XML_FORK (ID)
ALTER TABLE JPA21_XML_FORK_FORK_USERS ADD CONSTRAINT FK_JPA21_XML_FORK_FORK_USERS_FORK_USER_ID FOREIGN KEY (FORK_USER_ID) REFERENCES JPA21_XML_FORK_USER (ID)
ALTER TABLE JPA21_XML_DDL_RUNNERS_RACES ADD CONSTRAINT FK_JPA21_XMLRunners_Races_Inverse FOREIGN KEY (RACE_ID) REFERENCES JPA21_XML_DDL_RACE (ID)
ALTER TABLE JPA21_XML_DDL_RUNNERS_RACES ADD CONSTRAINT FK_JPA21_XMLRunners_Races FOREIGN KEY (RUNNER_ID) REFERENCES JPA21_XML_DDL_RUNNER (ID)
ALTER TABLE JPA21_XML_DDL_RUNNER_ACS ADD CONSTRAINT FK_JPA21_XMLAccomplishments FOREIGN KEY (ATHLETE_ID) REFERENCES JPA21_XML_DDL_RUNNER (ID)
ALTER TABLE JPA21_XML_DDL_ENDORSEMENTS ADD CONSTRAINT FK_JPA21_XMLEndorsements_Key FOREIGN KEY (ENDORSER_ID) REFERENCES JPA21_XML_DDL_ENDORSER (ID)
ALTER TABLE JPA21_XML_DDL_ENDORSEMENTS ADD CONSTRAINT FK_JPA21_XMLEndorsements FOREIGN KEY (ATHLETE_ID) REFERENCES JPA21_XML_DDL_RUNNER (ID)
ALTER TABLE JPA21_XML_DDL_RUNNER_PBS ADD CONSTRAINT FK_JPA21_XML_Runner_PBS FOREIGN KEY (RUNNER_ID) REFERENCES JPA21_XML_DDL_RUNNER (ID)
    
    
CREATE TABLE SEQUENCE (SEQ_NAME VARCHAR(50) NOT NULL, SEQ_COUNT DECIMAL(38), PRIMARY KEY (SEQ_NAME))
CREATE TABLE JPA21_XML_PK_SEQ (XML_SEQ_NAME VARCHAR(50) NOT NULL, XML_SEQ_COUNT DECIMAL(38), PRIMARY KEY (XML_SEQ_NAME))
CREATE INDEX INDEX_JPA21_XML_TABLE_GENERATOR ON JPA21_XML_PK_SEQ (XML_SEQ_COUNT, XML_SEQ_NAME)
INSERT INTO SEQUENCE(SEQ_NAME, SEQ_COUNT) values ('SEQ_GEN', 0)
INSERT INTO JPA21_XML_PK_SEQ(XML_SEQ_NAME, XML_SEQ_COUNT) values ('XML_UTENSIL_SEQ', 0)