CREATE TABLE jj_cluster_run (
        cr_id            serial,
        date             timestamp not null,
        br_id            INTEGER REFERENCES blast_run ON DELETE RESTRICT,
        nc_id            INTEGER REFERENCES nc_run ON DELETE RESTRICT,
        stype            text,
        set_id_filter    INTEGER REFERENCES prot_seq_set ON DELETE RESTRICT,
        use_symmeric     BOOLEAN,
        score_threshold  DOUBLE PRECISION,
        params           text,
        comment          text,
        PRIMARY KEY (cr_id)
);
        
CREATE TABLE jj_hcluster (
        cr_id           INTEGER NOT NULL REFERENCES jj_cluster_run ON DELETE RESTRICT,
        cluster_id      INTEGER NOT NULL,
        distance        DOUBLE PRECISION NOT NULL,
        parent_distance DOUBLE PRECISION,
        parent_id       INTEGER,
        seq_id          INTEGER,
        lft             INTEGER,
        rgt             INTEGER,
        PRIMARY KEY     (cr_id, cluster_id)
);
CREATE INDEX jj_hcluster_parent ON jj_hcluster (cr_id, parent_id)
CREATE INDEX jj_hcluster_nested ON jj_hcluster (cr_id, lft, rgt);

CREATE INDEX jj_hcluster_nested_seq ON jj_hcluster (cr_id, lft, rgt) 
WHERE seq_id IS NOT NULL;

CREATE TABLE jj_flatcluster (
        cr_id           INTEGER NOT NULL REFERENCES jj_cluster_run ON DELETE RESTRICT,
        cluster_id      INTEGER NOT NULL,
        seq_id          INTEGER NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
        PRIMARY KEY     (cr_id, cluster_id, seq_id),
);

