SET WRITE_DELAY 500;
SET CLUSTER '';
SET DEFAULT_TABLE_TYPE 0;
SET DEFAULT_LOCK_TIMEOUT 2000;
SET CACHE_SIZE 16384;

CREATE USER IF NOT EXISTS SA SALT '91c7ebbdc70192c4' HASH 'd43cc31624cba208f8d5f2abda982ab1671f8dfefe052f15355c3d1c0f250af8' ADMIN;

create table BISSAPPROJECT
(
    ID bigint generated by default as identity,
    FILEPATH varchar(500),
    COMMENT varchar(3000),
    NAME varchar(1500),    
    primary key (ID)
);

alter table BISSAPPROJECT add unique(NAME);

create table APPLICATIONDOCUMENT
(
    ID bigint generated by default as identity,
    BISSAPPROJECT_ID bigint not null,
    FILEREFERENCEID varchar(260),
    LANG varchar(260),
    RO varchar(260),
    PRODUCEDBY varchar(260),
    NUMBER_OF_SEQUENCES bigint not null,
    primary key (ID)
);

alter table APPLICATIONDOCUMENT add constraint APPLICATIONDOCUMENT_BISSAPPROJECT_FK foreign key(BISSAPPROJECT_ID) references BISSAPPROJECT(ID) on delete cascade;

create table INVENTIONTITLE
(
    ID bigint generated by default as identity,
    APPLICATIONDOCUMENT_ID bigint not null,
    INVENTIONTITLEID varchar(260),
    LANG varchar(260),
    TITLE varchar(1500),
    primary key (ID)
);

alter table INVENTIONTITLE add constraint APPLICATIONDOCUMENT_INVENTIONTITLE_FK foreign key(APPLICATIONDOCUMENT_ID) references APPLICATIONDOCUMENT(ID) on delete cascade;

CREATE TABLE APPLICATIONREFERENCE
(
    ID bigint generated by default as identity,
    APPLICATIONDOCUMENT_ID bigint not null,
    LINENUMBER bigint,
    primary key (ID)
);

alter table APPLICATIONREFERENCE add constraint APPLICATIONREFERENCE_APPLICATIONDOCUMENT_FK foreign key(APPLICATIONDOCUMENT_ID) references APPLICATIONDOCUMENT(ID) on delete cascade;

create table DOCUMENTID
(
    ID bigint generated by default as identity,
    REFERENCE_ID bigint not null,
    DATE date,
    LANGUAGE varchar(260),
    DOCNUMBER varchar(260),
    NAME varchar(260),
    COUNTRY varchar(260),
    KIND varchar(260),
    primary key (ID)
);

alter table DOCUMENTID add constraint APPLICATIONREFERENCE_DOCUMENTID_FK foreign key(REFERENCE_ID) references APPLICATIONREFERENCE(ID) on delete cascade;

create table PRIORITYCLAIM
(
    ID bigint generated by default as identity,
    APPLICATIONDOCUMENT_ID bigint not null,
    DATE date,
    DOC_NUMBER varchar(260),
    OFFICE_OF_FILING varchar(260),
    COUNTRY varchar(260),
    SEQUENCE bigint,
    CLAIM_KIND varchar(15),
    LINENUMBER bigint,
    primary key (ID)
);

alter table PRIORITYCLAIM add constraint PRIORITYCLAIM_APPLICATIONDOCUMENT_FK foreign key(APPLICATIONDOCUMENT_ID) references APPLICATIONDOCUMENT(ID) on delete cascade;

create table SEQUENCE
(
    ID bigint generated by default as identity,
    DESCRIPTION varchar(260) not null,
    MOLECULETYPE varchar(260),
    APPLICATIONDOCUMENT_ID bigint not null,
    LENGTH bigint,
    SEQUENCE clob not null,
    ORIGINAL_SEQUENCE clob,
    SEQUENCE_HASH varchar(260),
    SEQUENCENUMBER bigint not null,
    ORGANISM varchar(260),
    LINENUMBER bigint,
    MODIFY_DATE smalldatetime not null,
    primary key (ID)
);

alter table SEQUENCE add constraint SEQUENCE_APPLICATIONDOCUMENT_FK foreign key(APPLICATIONDOCUMENT_ID) references APPLICATIONDOCUMENT(ID) on delete cascade;

create table SEQUENCEREFERENCE
(
    ID bigint generated by default as identity,
    SEQUENCE_ID bigint not null,
    AUTHORS varchar(1000),
    TITLE varchar(1000),
    JOURNAL varchar(260),
    VOLUME varchar(260),
    ISSUE varchar(1000),
    PAGES varchar(260),
    DATE varchar(260),
    DB_ACCESSION_NB varchar(260),
    DB_ENTRY_DATE varchar(260),
    DOC_NUMBER varchar(260),
    FILING_DATE varchar(260),
    PUBLICATION_DATE varchar(260),
    RELEVANT_RESIDUES varchar(1000),
    primary key (ID)
);

create table FEATURE
(
    ID bigint generated by default as identity,
    SEQUENCE_ID bigint not null,
    TRANSLATED_SEQUENCE_ID bigint,
    TRANSLATED_SEQUENCE_NUMBER bigint,
    KEY varchar(260),
    LOCATION varchar(1500),
    LINENUMBER bigint,
    primary key (ID)
);

alter table FEATURE add constraint FEATURE_SEQUENCE_FK foreign key(SEQUENCE_ID) references SEQUENCE(ID) on delete cascade;

create table QUALIFIER
(
    ID bigint generated by default as identity,
    FEATURE_ID bigint not null,
    NAME varchar(260),
    VALUE clob,
    LINENUMBER bigint,
    primary key (ID)
);

alter table QUALIFIER add constraint QUALIFIER_FEATURE_FK foreign key(FEATURE_ID) references FEATURE(ID) on delete cascade;

create table APPLICANT
(
    ID bigint generated by default as identity,
    LINENUMBER bigint,
    primary key (ID)
);

create table ADDRESSBOOK
(
    ID bigint generated by default as identity,
    APPLICANT_ID bigint not null,
    NAME_TYPE varchar(260),
    ADDRESS_1 varchar(260),
    ADDRESS_2 varchar(260),
    ADDRESS_3 varchar(260),
    ADDRESS_FLOOR varchar(260),
    BUILDING varchar(260),
    CITY varchar(260),
    COUNTRY varchar(260),
    COUNTY varchar(260),
    DEPARTMENT varchar(260),
    DTEXT varchar(260),
    EAD varchar(260),
    EMAIL varchar(260),
    FAX varchar(260),
    FIRST_NAME varchar(260),
    IID varchar(260),
    ISORGANIZATION BIT,
    LANG varchar(260),
    MAILCODE varchar(260),
    MIDDLE_NAME varchar(260),
    NAME varchar(260),
    LAST_NAME varchar(260),
    ORGNAME varchar(260),
    PHONE varchar(260),
    POBOX varchar(260),
    POSTCODE varchar(260),
    PREFIX varchar(260),
    REGISTERED_NUMBER varchar(260),
    ROLE varchar(260),
    ROOM varchar(260),
    STATE varchar(260),
    STREET varchar(260),
    SUFFIX varchar(260),
    SYNONYM varchar(260),
    TEXT varchar(260),
    URL varchar(260),
    ISDEFAULT bit,
    primary key (ID)
);

alter table ADDRESSBOOK add constraint ADDRESSBOOK_APPLICANT_FK foreign key(APPLICANT_ID) references APPLICANT(ID) on delete cascade;

create table APPLICATIONDOCUMENT_APPLICANT
(
    APPLICATIONDOCUMENT_ID bigint NOT NULL,
    APPLICANT_ID bigint NOT NULL
);

alter table APPLICATIONDOCUMENT_APPLICANT add constraint APPLICANT_FK foreign key(APPLICANT_ID) references APPLICANT(ID) on delete cascade;
alter table APPLICATIONDOCUMENT_APPLICANT add constraint APPLICATIONDOCUMENT_FK foreign key(APPLICATIONDOCUMENT_ID) references APPLICATIONDOCUMENT(ID) on delete cascade;

create table VERSION
(
    VERSION varchar(255)
);

insert into VERSION values('1.2.3');

create table COUNTRY
(
    CODE varchar(3),
    NAME varchar(255),
    ENABLED bit,
    INTERNATIONAL bit,
    REGIONAL bit,
    NATIONAL bit,
    primary key (CODE)
);

create table LANGUAGE
(
    CODE varchar(2),
    NAME varchar(255),
    ENABLED bit,
    primary key (CODE)
);

create table FEATURE_KEY
(
    ID bigint,
    KEY varchar(255) not null,
    PROTEINS bit not null,
    NUCLEOTIDE bit not null,
    OBSOLETE bit not null,
    IS_ST25 bit not null,
    IS_ST26 bit not null,
    IS_EMBL bit not null,
    primary key (ID)
);

create table QUALIFIER_KEY
(
    ID bigint,
    KEY varchar(255) not null,
    VALUE_TYPE varchar(4000) not null,
    VALUE_REGEXP varchar(255),
    primary key (ID)
);

create table FEATURE_2_QUALIFIER_KEY
(
    FEATURE_KEY_ID bigint not null,
    QUALIFIER_KEY_ID bigint not null,
    MANDATORY bit not null,
    primary key (FEATURE_KEY_ID, QUALIFIER_KEY_ID)
);

alter table FEATURE_2_QUALIFIER_KEY add constraint FEATURE_FK foreign key(FEATURE_KEY_ID) references FEATURE_KEY(ID);
alter table FEATURE_2_QUALIFIER_KEY add constraint QUALIFIER_FK foreign key(QUALIFIER_KEY_ID) references QUALIFIER_KEY(ID);

create table QUALIFIER_VALUE
(
    QUALIFIER_KEY_ID bigint,
    VALUE varchar(255) not null,
    primary key (QUALIFIER_KEY_ID, VALUE)
);

alter table QUALIFIER_VALUE add constraint QUALIFIER_VALUE_FK foreign key(QUALIFIER_KEY_ID) references QUALIFIER_KEY(ID);

create table MOD_BASE
(
    NUCLEOTIDE char not null,
    MOD_BASE_ALLOWED varchar(255) not null
);

create table NAME_CLASS
(
    ID bigint generated by default as identity,
    NAME_CLASS varchar(255) not null,
    ENABLED bit not null,
    primary key (ID)
);

SET MAX_MEMORY_ROWS 500;