text: -- creates a new empty schema, which will be used to manage all objects
  -- which are required for CATGenome Browser
  CREATE SCHEMA IF NOT EXISTS CATGENOME;
  
  -- creates a sequence used to generate primary key values for "bam" table
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_BAM_FILE START WITH 1 INCREMENT BY 1;
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_BED START WITH 1 INCREMENT BY 1;
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_BED_GRAPH START WITH 1 INCREMENT BY 1;
  
  -- creates a sequence used to generate primary key values for "biological_data_item" table
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_BIOLOGICAL_DATA_ITEM START WITH 1 INCREMENT BY 1;
  
  -- sequence for bookmark ids
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_BOOKMARK START WITH 1 INCREMENT BY 1;
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_BOOKMARK_ITEM START WITH 1 INCREMENT BY 1;
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_BUCKET START WITH 1 INCREMENT BY 1;
  
  -- creates a sequence used to generate primary key values for "chromosome" table
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_CHROMOSOME START WITH 1 INCREMENT BY 1;
  
  -- creates a sequence used to generate primary key values for "gene_item" table
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_GENE_FILE START WITH 1 INCREMENT BY 1;
  
  -- creates a sequence used only for technical reasons, e.g. to generate ID for a list of values
  -- that e.g. have to be inserted into temporary local table
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_LIST START WITH 1 INCREMENT BY 1;
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_MAF START WITH 1 INCREMENT BY 1;
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_PERSON START WITH 1 INCREMENT BY 1;
  
  -- creates a sequence used to generate primary key values for "project" table
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_PROJECT START WITH 1 INCREMENT BY 1;
  
  -- creates a sequence used to generate primary key values for "project_item" table
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_PROJECT_ITEM START WITH 1 INCREMENT BY 1;
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_PROTEIN_SEQUENCE START WITH 1 INCREMENT BY 1;
  
  -- creates a sequence used to generate primary key values for "reference_genome" table
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_REFERENCE_GENOME START WITH 1 INCREMENT BY 1;
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_SEG START WITH 1 INCREMENT BY 1;
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_SEG_SAMPLE START WITH 1 INCREMENT BY 1;
  
  -- creates a sequence used to generate primary key values for "vcf" table
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_VCF_FILE START WITH 1 INCREMENT BY 1;
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_VCF_SAMPLE START WITH 1 INCREMENT BY 1;
  CREATE SEQUENCE IF NOT EXISTS CATGENOME.S_VG START WITH 1 INCREMENT BY 1;
  
  CREATE TABLE IF NOT EXISTS CATGENOME.BUCKET (
    BUCKET_ID          BIGINT         NOT NULL PRIMARY KEY,
    BUCKET_NAME        VARCHAR(250)   NOT NULL,
    ACCESS_KEY_ID      VARCHAR(250)   NOT NULL,
    SECRET_ACCESS_KEY  VARCHAR(250)   NOT NULL
  );
  
  CREATE TABLE IF NOT EXISTS CATGENOME.BIOLOGICAL_DATA_ITEM (
    BIO_DATA_ITEM_ID  BIGINT         NOT NULL PRIMARY KEY,
    NAME              VARCHAR(250)   NOT NULL,
    TYPE              BIGINT         NOT NULL,
    PATH              VARCHAR(500)   NOT NULL,
    FORMAT            BIGINT         NOT NULL,
    CREATED_BY        BIGINT,
    CREATED_DATE      TIMESTAMP,
    BUCKET_ID         BIGINT,
    CONSTRAINT bucket_id_fkey FOREIGN KEY (bucket_id) REFERENCES catgenome.bucket(bucket_id)
  );
  
  -- creates "reference_genome" table, used to handle metadata concerned with reference
  -- genomes which has been registered in the system
  CREATE TABLE IF NOT EXISTS CATGENOME.REFERENCE_GENOME (
    REFERENCE_GENOME_ID    BIGINT   NOT NULL,
    REFERENCE_GENOME_SIZE  BIGINT   NOT NULL,
    BIO_DATA_ITEM_ID       BIGINT   NOT NULL,
    CONSTRAINT reference_genome_id_pkey PRIMARY KEY (reference_genome_id),
    CONSTRAINT BIO_DATA_ITEM_ID_FKEY FOREIGN KEY (BIO_DATA_ITEM_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID)
  );
  
  -- creates "chromosome" table, used to handle metadata concerned with chromosomes
  -- which belongs to a reference
  CREATE TABLE IF NOT EXISTS CATGENOME.CHROMOSOME (
    CHROMOSOME_ID          BIGINT         NOT NULL,
    REFERENCE_GENOME_ID    BIGINT         NOT NULL,
    CHROMOSOME_NAME        VARCHAR(250)   NOT NULL,
    CHROMOSOME_SIZE        INTEGER        NOT NULL,
    CHROMOSOME_HEADER      VARCHAR(250),
    CONTENT_RESOURCE_PATH  VARCHAR(500)   NOT NULL,
    CONSTRAINT chromosome_id_pkey PRIMARY KEY (chromosome_id),
    CONSTRAINT referred_genome_id_fkey FOREIGN KEY (reference_genome_id) REFERENCES catgenome.reference_genome (reference_genome_id)
  );
  
  -- creates "vcf" table, used to handle metadata concerned with VCF files
  -- which has been stored in the system
  CREATE TABLE IF NOT EXISTS CATGENOME.VCF (
    VCF_ID               BIGINT    NOT NULL,
    BIO_DATA_ITEM_ID     BIGINT    NOT NULL,
    REFERENCE_GENOME_ID  BIGINT    NOT NULL,
    COMPRESSED           BOOLEAN   DEFAULT FALSE,
    INDEX_ID             BIGINT    NOT NULL,
    CONSTRAINT vcf_id_pkey PRIMARY KEY (vcf_id),
    CONSTRAINT vcf_biological_data_item_id_fkey FOREIGN KEY (BIO_DATA_ITEM_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT vcf_index_id_fkey FOREIGN KEY (index_id) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT vcf_reference_genome_id FOREIGN KEY (REFERENCE_GENOME_ID) REFERENCES CATGENOME.REFERENCE_GENOME (REFERENCE_GENOME_ID)
  );
  
  CREATE TABLE IF NOT EXISTS CATGENOME.VCF_SAMPLE (
    VCF_SAMPLE_ID  BIGINT         NOT NULL,
    VCF_ID         BIGINT         NOT NULL,
    SAMPLE_NAME    VARCHAR(250)   NOT NULL,
    ORDER_INDEX    INTEGER,
    CONSTRAINT vcf_sample_id_pkey PRIMARY KEY (VCF_SAMPLE_ID),
    CONSTRAINT vcf_sample_vcf_id_fkey FOREIGN KEY (VCF_ID) REFERENCES CATGENOME.VCF (VCF_ID),
  );
  
  CREATE TABLE IF NOT EXISTS CATGENOME.BAM (
    BAM_ID               BIGINT   NOT NULL,
    BIO_DATA_ITEM_ID     BIGINT   NOT NULL,
    REFERENCE_GENOME_ID  BIGINT   NOT NULL,
    INDEX_ID             BIGINT   NOT NULL,
    CONSTRAINT bam_id_pkey PRIMARY KEY (BAM_ID),
    CONSTRAINT BAM_INDEX_ID_FKEY FOREIGN KEY (INDEX_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT bam_biological_data_item_fkey FOREIGN KEY (BIO_DATA_ITEM_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT bam_reference_genome_id_fkey FOREIGN KEY (REFERENCE_GENOME_ID) REFERENCES CATGENOME.REFERENCE_GENOME (REFERENCE_GENOME_ID)
  );
  
  CREATE TABLE IF NOT EXISTS CATGENOME.BED (
    BED_ID               BIGINT    NOT NULL,
    BIO_DATA_ITEM_ID     BIGINT    NOT NULL,
    REFERENCE_GENOME_ID  BIGINT    NOT NULL,
    INDEX_ID             BIGINT    NOT NULL,
    COMPRESSED           BOOLEAN   DEFAULT FALSE,
    CONSTRAINT bed_id_pkey PRIMARY KEY (BED_ID),
    CONSTRAINT bed_index_id_fkey FOREIGN KEY (INDEX_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT bed_biological_data_item_fkey FOREIGN KEY (BIO_DATA_ITEM_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT bed_reference_genome_id_fkey FOREIGN KEY (REFERENCE_GENOME_ID) REFERENCES CATGENOME.REFERENCE_GENOME (REFERENCE_GENOME_ID)
  );
  
  CREATE TABLE IF NOT EXISTS CATGENOME.BED_GRAPH (
    BED_GRAPH_ID         BIGINT   NOT NULL,
    BIO_DATA_ITEM_ID     BIGINT   NOT NULL,
    REFERENCE_GENOME_ID  BIGINT   NOT NULL,
    CONSTRAINT bed_graph_id_pkey PRIMARY KEY (BED_GRAPH_ID),
    CONSTRAINT bed_graph_biological_data_item_fkey FOREIGN KEY (BIO_DATA_ITEM_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT bed_graph_reference_genome_id_fkey FOREIGN KEY (REFERENCE_GENOME_ID) REFERENCES CATGENOME.REFERENCE_GENOME (REFERENCE_GENOME_ID)
  );
  
  -- creates "project" table, used to handle data related to every project
  -- that is created by a user in the system
  CREATE TABLE IF NOT EXISTS CATGENOME.PROJECT (
    PROJECT_ID        BIGINT         NOT NULL,
    PROJECT_NAME      VARCHAR(250)   NOT NULL,
    CREATED_BY        BIGINT         NOT NULL,
    CREATED_DATE      TIMESTAMP      NOT NULL,
    LAST_OPENED_DATE  TIMESTAMP      NOT NULL,
    CONSTRAINT PROJECT_ID_PKEY PRIMARY KEY (PROJECT_ID),
    CONSTRAINT project_name_unique UNIQUE (project_name)
  );
  
  -- creates "project_item" table, used to handle data related to a single track that
  -- has been added to the given project
  CREATE TABLE IF NOT EXISTS CATGENOME.PROJECT_ITEM (
    PROJECT_ITEM_ID            BIGINT     NOT NULL,
    REFERRED_PROJECT_ID        BIGINT     NOT NULL,
    REFERRED_BIO_DATA_ITEM_ID  BIGINT     NOT NULL,
    HIDDEN                     BOOLEAN    DEFAULT FALSE NOT NULL,
    ORDINAL_NUMBER             SMALLINT   NOT NULL,
    CONSTRAINT PROJECT_ITEM_ID_PKEY PRIMARY KEY (PROJECT_ITEM_ID),
    CONSTRAINT REFERRED_PROJECT_BIO_DATA_ITEM_ID_FKEY FOREIGN KEY (REFERRED_BIO_DATA_ITEM_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT REFERRED_PROJECT_ID_FKEY FOREIGN KEY (REFERRED_PROJECT_ID) REFERENCES CATGENOME.PROJECT (PROJECT_ID),
    CONSTRAINT BIO_ITEM_UNIQUE UNIQUE (referred_project_id, referred_bio_data_item_id)
  );
  
  -- a table to store bookmarks
  CREATE TABLE IF NOT EXISTS CATGENOME.BOOKMARK (
    BOOKMARK_ID             BIGINT         NOT NULL,
    BOOKMARK_NAME           VARCHAR(250)   NOT NULL,
    START_INDEX             INTEGER        NOT NULL,
    END_INDEX               INTEGER        NOT NULL,
    REFERRED_PROJECT_ID     BIGINT         NOT NULL,
    REFERRED_CHROMOSOME_ID  BIGINT         NOT NULL,
    CONSTRAINT bookmark_id_pkey PRIMARY KEY (BOOKMARK_ID),
    CONSTRAINT BOOKMARK_REFERRED_CHROMOSOME_ID_FKEY FOREIGN KEY (REFERRED_CHROMOSOME_ID) REFERENCES CATGENOME.CHROMOSOME (CHROMOSOME_ID),
    CONSTRAINT BOOKMARK_REFERRED_PROJECT_ID_FKEY FOREIGN KEY (REFERRED_PROJECT_ID) REFERENCES CATGENOME.PROJECT (PROJECT_ID)
  );
  
  
  CREATE TABLE IF NOT EXISTS CATGENOME.BOOKMARK_ITEM (
    BOOKMARK_ITEM_ID  BIGINT   NOT NULL,
    BOOKMARK_ID       BIGINT   NOT NULL,
    BIO_DATA_ITEM_ID  BIGINT   NOT NULL,
    CONSTRAINT bookmark_item_id_pkey PRIMARY KEY (BOOKMARK_ITEM_ID),
    CONSTRAINT bookmark_bio_data_item_id FOREIGN KEY (BIO_DATA_ITEM_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT bookmark_bookmark_id FOREIGN KEY (BOOKMARK_ID) REFERENCES CATGENOME.BOOKMARK (BOOKMARK_ID),
    CONSTRAINT bookmark_item_unique UNIQUE (bookmark_id, bio_data_item_id)
  );
  
  -- creates "gene_item" table, used to handle metadata concerned with gene files
  -- which has been stored in the system
  CREATE TABLE IF NOT EXISTS CATGENOME.GENE_ITEM (
    GENE_ITEM_ID          BIGINT         NOT NULL,
    BIO_DATA_ITEM_ID      BIGINT         NOT NULL,
    REFERENCE_GENOME_ID   BIGINT         NOT NULL,
    COMPRESSED            BOOLEAN        DEFAULT FALSE,
    INDEX_ID              BIGINT         NOT NULL,
    EXTERNAL_DB_TYPE_ID   BIGINT,
    EXTERNAL_DB_ID        BIGINT,
    EXTERNAL_DB_ORGANISM  VARCHAR(256),
    CONSTRAINT gene_item_id_pkey PRIMARY KEY (GENE_ITEM_ID),
    CONSTRAINT gene_item_bio_data_item_id_fkey FOREIGN KEY (BIO_DATA_ITEM_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT GENE_INDEX_ID_FKEY FOREIGN KEY (INDEX_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT gene_item_reference_genome_id_fkey FOREIGN KEY (REFERENCE_GENOME_ID) REFERENCES CATGENOME.REFERENCE_GENOME (REFERENCE_GENOME_ID)
  );
  
  CREATE TABLE IF NOT EXISTS CATGENOME.MAF
  (
    MAF_ID               BIGINT         NOT NULL,
    BIO_DATA_ITEM_ID     BIGINT         NOT NULL,
    REFERENCE_GENOME_ID  BIGINT         NOT NULL,
    INDEX_ID             BIGINT         NOT NULL,
    REAL_PATH            VARCHAR(500),
    COMPRESSED           BOOLEAN        DEFAULT FALSE,
    CONSTRAINT maf_id_pkey PRIMARY KEY (MAF_ID),
    CONSTRAINT maf_bio_data_item_id_fkey FOREIGN KEY (BIO_DATA_ITEM_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT MAF_INDEX_ID_FKEY FOREIGN KEY (INDEX_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT maf_reference_genome_id_fkey FOREIGN KEY (REFERENCE_GENOME_ID) REFERENCES CATGENOME.REFERENCE_GENOME (REFERENCE_GENOME_ID)
  );
  
  CREATE TABLE IF NOT EXISTS CATGENOME.PERSON_ROLE (
    ROLE_ID  BIGINT         NOT NULL,
    CODE     VARCHAR(100)   NOT NULL,
    CONSTRAINT role_id_pkey PRIMARY KEY (ROLE_ID),
  );
  
  CREATE TABLE IF NOT EXISTS CATGENOME.PERSON (
    PERSON_ID  BIGINT         NOT NULL,
    NAME       VARCHAR(250)   NOT NULL,
    PASSWORD   VARCHAR(250)   NOT NULL,
    EMAIL      VARCHAR(250)   NOT NULL,
    ROLE_ID    BIGINT         NOT NULL,
    CONSTRAINT person_id_pkey PRIMARY KEY (PERSON_ID),
    CONSTRAINT PERSON_ROLE_ID_FKEY FOREIGN KEY (ROLE_ID) REFERENCES CATGENOME.PERSON_ROLE (ROLE_ID),
    CONSTRAINT NAME_UNIQUE UNIQUE(name)
  );
  
  -- creates 'ROLE_APP' and 'ROLE_ADMIN' roles, if they do not exist
  DROP TABLE IF EXISTS catgenome.mutex;
  CREATE TEMPORARY TABLE IF NOT EXISTS catgenome.mutex(
    i INT NOT NULL PRIMARY KEY
  );
  INSERT INTO catgenome.mutex(i) VALUES (0), (1);
  
  INSERT INTO catgenome.person_role (role_id, code)
    SELECT 1, 'ROLE_APP'
    FROM catgenome.mutex m
      LEFT OUTER JOIN catgenome.person_role r
        ON r.code = 'ROLE_APP'
    WHERE m.i = 1 AND r.code IS NULL;
  
  INSERT INTO catgenome.person_role (role_id, code)
    SELECT 2, 'ROLE_ADMIN'
    FROM catgenome.mutex m
      LEFT OUTER JOIN catgenome.person_role r
        ON r.code = 'ROLE_ADMIN'
    WHERE m.i = 1 AND r.code IS NULL;
  
  -- adds a default admin user if it does not exist
  insert into catgenome.person (person_id, name, role_id, password, email)
    select  nextval('catgenome.s_person'), 'default_admin', 2, 'admin', 'admin@admin.com'
    from catgenome.mutex m
      left outer join catgenome.person p
        on p.name = 'default_admin'
    where m.i = 1 and p.name is null;
  
  DROP TABLE IF EXISTS catgenome.mutex;
  
  -- table for protein sequences
  CREATE TABLE IF NOT EXISTS CATGENOME.PROTEIN_SEQUENCE (
    PROTEIN_SEQUENCE_ID  BIGINT   NOT NULL,
    GENE_ITEM_ID         BIGINT,
    CDS_START_INDEX      BIGINT   NOT NULL,
    CDS_END_INDEX        BIGINT   NOT NULL,
    TRIPLE_START_INDEX   BIGINT   NOT NULL,
    TRIPLE_END_INDEX     BIGINT   NOT NULL,
    REFERENCE_GENOME_ID  BIGINT,
    PROTEIN_SEQUENCE     CLOB     NOT NULL,
    CONSTRAINT proteins_sequence_id_pkey PRIMARY KEY (PROTEIN_SEQUENCE_ID),
    CONSTRAINT GENE_ITEM_ID_FKEY FOREIGN KEY (GENE_ITEM_ID) REFERENCES CATGENOME.GENE_ITEM (GENE_ITEM_ID),
    CONSTRAINT REFERENCE_GENOME_ID_FKEY FOREIGN KEY (REFERENCE_GENOME_ID) REFERENCES CATGENOME.REFERENCE_GENOME (REFERENCE_GENOME_ID),
    CONSTRAINT protein_sequence_unique UNIQUE(gene_item_id, cds_start_index, cds_end_index, triple_start_index, triple_end_index, reference_genome_id)
  );
  
  CREATE TABLE IF NOT EXISTS CATGENOME.SEG
  (
    SEG_ID               BIGINT    NOT NULL,
    BIO_DATA_ITEM_ID     BIGINT    NOT NULL,
    REFERENCE_GENOME_ID  BIGINT    NOT NULL,
    INDEX_ID             BIGINT    NOT NULL,
    COMPRESSED           BOOLEAN   DEFAULT FALSE,
    CONSTRAINT seg_id_pkey PRIMARY KEY (SEG_ID),
    CONSTRAINT seg_bio_data_item_id_fkey FOREIGN KEY (BIO_DATA_ITEM_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT seg_index_id_fkey FOREIGN KEY (INDEX_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT seg_reference_genome_id_fkey FOREIGN KEY (REFERENCE_GENOME_ID) REFERENCES CATGENOME.REFERENCE_GENOME (REFERENCE_GENOME_ID)
  );
  
  CREATE TABLE IF NOT EXISTS CATGENOME.SEG_SAMPLE
  (
    SEG_SAMPLE_ID  BIGINT         NOT NULL,
    SEG_ID         BIGINT         NOT NULL,
    SAMPLE_NAME    VARCHAR(250)   NOT NULL,
    CONSTRAINT seg_sample_id_pkey PRIMARY KEY (SEG_SAMPLE_ID),
    CONSTRAINT seg_sample_seg_id_key FOREIGN KEY (SEG_ID) REFERENCES CATGENOME.SEG (SEG_ID)
  
  );
  
  CREATE TABLE IF NOT EXISTS CATGENOME.VG
  (
    VG_ID                BIGINT         NOT NULL,
    BIO_DATA_ITEM_ID     BIGINT         NOT NULL,
    REFERENCE_GENOME_ID  BIGINT         NOT NULL,
    REAL_PATH            VARCHAR(500),
    CONSTRAINT vg_id_pkey PRIMARY KEY (VG_ID),
    CONSTRAINT vg_bio_data_item_id_fkey FOREIGN KEY (BIO_DATA_ITEM_ID) REFERENCES CATGENOME.BIOLOGICAL_DATA_ITEM (BIO_DATA_ITEM_ID),
    CONSTRAINT vg_reference_genome_id_fkey FOREIGN KEY (REFERENCE_GENOME_ID) REFERENCES CATGENOME.REFERENCE_GENOME (REFERENCE_GENOME_ID)
  );
  