CREATE TABLE taxon (
       tax_id      integer,
       name        text NOT NULL,
       common_names text[],
       PRIMARY KEY (tax_id)
);

CREATE TABLE prot_seq_source (
        source_id       serial,
        source_name     text UNIQUE NOT NULL,
        PRIMARY KEY (source_id)
);

CREATE TABLE prot_seq_source_ver (
        source_ver_id   serial,
        source_id       integer NOT NULL REFERENCES prot_seq_source ON DELETE RESTRICT,
        version         text NOT NULL,
        date            timestamp NOT NULL,
        PRIMARY KEY (source_ver_id),
        UNIQUE (source_id, version, date)
);

-- these entries should be unique.  Unfortunately mysql cannot have a
-- unique entry longer than 767 bytes (if sequence were a varchar) or
-- include a text field.  BEWARE of (crc,length) conflicts!  This
-- restriction may later be removed if it poses a problem.  Length
-- need not be stored, but it could help in the event of conflicts.
-- adding molecular_weight eliminates all conflicts so far
CREATE TABLE prot_seq_str (
        seq_str_id              serial,
        sequence                text NOT NULL,
        crc                     text NOT NULL,
        length                  integer NOT NULL,
        molecular_weight        integer NOT NULL,
        PRIMARY KEY (seq_str_id),
        UNIQUE (crc, length, molecular_weight)
);

CREATE TABLE prot_seq (
        seq_id                  serial,
        seq_str_id              integer NOT NULL REFERENCES prot_seq_str ON DELETE RESTRICT,
        PRIMARY KEY (seq_id)
);

CREATE TABLE prot_seq_version (
        seq_id          integer NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
        source_ver_id   integer NOT NULL REFERENCES prot_seq_source_ver ON DELETE RESTRICT,
        primary_acc     text NOT NULL,
        PRIMARY KEY (seq_id, source_ver_id),
        UNIQUE (source_ver_id, primary_acc)
);
        
CREATE TABLE prot_seq_accession (
        seq_id          integer NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
        source_id       integer NOT NULL REFERENCES prot_seq_source ON DELETE RESTRICT,
        acc_order       smallint NOT NULL,  -- 0,1,2,...
        accession       text NOT NULL,
        PRIMARY KEY (seq_id, acc_order)
);

CREATE TABLE prot_seq_set (
        set_id          serial,
        name            text NOT NULL,
        description     text NOT NULL,
        PRIMARY KEY (set_id),
        UNIQUE (name, description)
);

CREATE TABLE prot_seq_set_member (
        set_id          integer NOT NULL REFERENCES prot_seq_set ON DELETE RESTRICT,
        seq_id          integer NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
        PRIMARY KEY (set_id, seq_id)
);

--------------------------------
-- Swiss-Prot specific info
--------------------------------
CREATE TABLE sp_gb_tax_id (
        seq_id          integer NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
        gb_tax_id       integer NOT NULL,
        PRIMARY KEY (seq_id)
);

CREATE TABLE sp_feature_key_str (
        feature_key_strid       serial,
        feature_key             text UNIQUE NOT NULL,
        PRIMARY KEY (feature_key_strid)
);

CREATE TABLE sp_feature (
        seq_id                  integer NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
        feature_key_strid       integer NOT NULL REFERENCES sp_feature_key_str ON DELETE RESTRICT,
        from_pos                text NOT NULL,
        to_pos                  text NOT NULL,
        comment                 text NOT NULL,
        ftid                    text NOT NULL
        -- PRIMARY  KEY (seq_id, feature_key_strid, from_pos, to_pos, comment, ftid)
);
CREATE INDEX seq_id_key on sp_feature(seq_id);

CREATE TABLE sp_keyword_str (
        keyword_strid   serial,
        keyword         text UNIQUE NOT NULL,
        PRIMARY KEY (keyword_strid)
);

CREATE TABLE sp_keyword (
        seq_id          integer NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
        keyword_strid   integer NOT NULL REFERENCES sp_keyword_str ON DELETE RESTRICT,
        PRIMARY KEY (seq_id, keyword_strid)
);

CREATE TABLE sp_xtra (
        seq_id          integer NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
        -- data_class              enum('STANDARD','PRELIMINARY') NOT NULL,
        entry_name              text NOT NULL,
        gene_name               text NOT NULL,
        description             text NOT NULL,
        -- created               datetime NOT NULL,
        -- sequence_update         datetime NOT NULL,
        -- annotation_update       datetime NOT NULL,
        PRIMARY KEY (seq_id)
);

-- store swissprot cross references
-- since the DR line does not seem well defined, simply store the
-- db name and next 4 semi-colon delimited fields
CREATE TABLE sp_cross_ref (
        seq_id          integer NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
        source_id       integer NOT NULL REFERENCES prot_seq_source ON DELETE RESTRICT,
        id_0            text NOT NULL,
        id_1            text NOT NULL,
        id_2            text NOT NULL,
        id_3            text NOT NULL,
        --PRIMARY KEY (seq_id, source_id, id_0, id_1, id_2, id_3)
);
-- a primary key of all columns is very large.  Just put a key for faster lookups
CREATE INDEX sp_cross_ref_seq_id on sp_cross_ref (seq_id, source_id);


--------------------------------
-- BLAST
--------------------------------
CREATE TABLE blast_run (
        br_id           serial,
        set_id          integer NOT NULL REFERENCES prot_seq_set ON DELETE RESTRICT,
        date            timestamp NOT NULL,
        num_sequences   integer NOT NULL,
        num_residues    integer NOT NULL,
        params          text NOT NULL,
        comment         text,
        blastall_path   text,
        query_set_id    integer NOT NULL REFERENCES prot_seq_set ON DELETE RESTRICT,
        PRIMARY KEY (br_id)
);

CREATE TABLE blast_lock (
        br_id           integer NOT NULL REFERENCES blast_run ON DELETE RESTRICT,
        query_set_id    integer NOT NULL REFERENCES prot_seq_set ON DELETE RESTRICT,
        set_id          integer NOT NULL REFERENCES prot_seq_set ON DELETE RESTRICT,
        chunk_size      integer NOT NULL,
        max_chunk       integer NOT NULL,
        chunk           integer NOT NULL,
        start_time      timestamp,
        end_time        timestamp,
        hostname        text,
        PRIMARY KEY (br_id,chunk)
);


CREATE TABLE blast_hit_arr (
        br_id           integer NOT NULL REFERENCES blast_run ON DELETE RESTRICT,
        hit_rank        smallint NOT NULL,
        seq_id_0        integer NOT NULL REFERENCES prot_seq(seq_id) ON DELETE RESTRICT,
        hit_list        int[],     -- int array. allows indexing and fast searching
        bit_score       double precision[],
        e_value         double precision[],
        seq_start_0     integer[],
        seq_end_0       integer[],
        seq_start_1     integer[],
        seq_end_1       integer[],
        identities      integer[],
        gaps            integer[],
        positives       integer[],
        low_complexity  boolean[],
        coverage        double precision[],
        PRIMARY KEY (br_id, hit_rank, seq_id_0)
);

-- store symmetric scores.
CREATE TABLE blast_hit_symmetric_arr (
        br_id           integer NOT NULL REFERENCES blast_run ON DELETE RESTRICT,
        seq_id_0        integer NOT NULL REFERENCES prot_seq(seq_id) ON DELETE RESTRICT,
        hit_list        int[],
        bit_score       double precision[],
        e_value         double precision[],
        PRIMARY KEY (br_id, seq_id_0)
);

CREATE TABLE nc_run (
        nc_id           serial,
        br_id           integer NOT NULL REFERENCES blast_run ON DELETE RESTRICT,
        date            timestamp NOT NULL,
        e_thresh        double precision NOT NULL,
        bit_thresh      double precision,
        nc_thresh       double precision NOT NULL,
        blast_hit_limit integer,
        smin            double precision NOT NULL,
        smin_factor     double precision NOT NULL,
        use_symmetric   boolean NOT NULL,
        score_type      text NOT NULL,
        self_hits       smallint NOT NULL,
        PRIMARY KEY (nc_id),
        UNIQUE (br_id, date, e_thresh, bit_thresh, nc_thresh, blast_hit_limit,
                smin, smin_factor, use_symmetric, score_type)
);
       
CREATE TABLE blast_hit_nc_arr (
        nc_id             integer NOT NULL REFERENCES nc_run ON DELETE RESTRICT,
        seq_id_0          integer NOT NULL REFERENCES prot_seq(seq_id) ON DELETE RESTRICT,
        --num_match_seq_0   integer NOT NULL,
        hit_list          int[],
        -- Using a composite type for the record means that indexing
        -- the array takes O(i) time, as postgres assumes a varable
        -- length element, and does a scan through the array.  Using
        -- fixed types allows O(1) access
        -- record_list       nc_record[],
        nc_score          double precision[],
        --num_match_seq_1   integer[],
        --num_match_seq_both      integer[],
        PRIMARY KEY (nc_id, seq_id_0)
);

CREATE TABLE nc_lock (
        nc_id           integer NOT NULL REFERENCES nc_run ON DELETE RESTRICT,
        chunk           integer NOT NULL,
        chunk_min       integer NOT NULL,
        chunk_max       integer NOT NULL,
        start_time      timestamp,
        end_time        timestamp,
        hostname        text,
        PRIMARY KEY (nc_xid, chunk)
);

CREATE TABLE prot_seq_taxonomy (
       seq_id           integer NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
       gb_tax_id        integer NOT NULL,
       PRIMARY KEY (seq_id)
);

--------------------------------
-- Pfam
--------------------------------
CREATE TABLE prot_seq_pfam (
       seq_id           integer NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
       align_start      integer NOT NULL,
       align_end        integer NOT NULL,
       envelope_start   integer NOT NULL,
       envelope_end     integer NOT NULL,
       hmm_acc          text NOT NULL,
       hmm_name         text NOT NULL,
       hmm_type         text NOT NULL,
       hmm_start        integer NOT NULL,
       hmm_end          integer NOT NULL,
       hmm_length       integer NOT NULL,
       bit_score        double precision NOT NULL,
       e_value          double precision NOT NULL,
       significance     integer NOT NULL,
       clan             text NOT NULL,
       PRIMARY KEY (seq_id, hmm_acc, align_start, align_end)
);


--------------------------------
-- Generic (FASTA insertion)
--------------------------------
CREATE TABLE fa_descr (
        seq_id          integer NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
        descr           text,
        PRIMARY KEY (seq_id)
);

-- Allow mapping from FASTA sequence id to another id (typically
-- Uniprot with annotations)
CREATE TABLE fa_map (
       seq_id           integer NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
       seq_id_1         integer NOT NULL REFERENCES prot_seq(seq_id) ON DELETE RESTRICT,
       PRIMARY KEY (seq_id)
);

-- Annotation of FASTA sequences, primarily for those from PPOD
CREATE TABLE fa_annotate (
        seq_id         integer NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
        synonyms       text,
        descr          text,
        PRIMARY KEY (seq_id)
);


--------------------------------
-- Derived data
--------------------------------
-- PFAM domain promiscuity
CREATE TABLE sp_promisc (
       set_id           integer NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
       id_0             text NOT NULL,
       cnt              integer NOT NULL,
       promiscuity      double precision NOT NULL,
       PRIMARY KEY (set_id, id_0)
);

