/* Unique Ids sequence generator */

drop sequence SEQ_GEN_SEQUENCE;
create sequence SEQ_GEN_SEQUENCE INCREMENT BY 50;
    

/* METADATA */

DROP TABLE IF EXISTS attribute_group CASCADE;
CREATE TABLE attribute_group (
    id bigint NOT NULL PRIMARY KEY
);

DROP TABLE IF EXISTS attribute CASCADE;
CREATE TABLE attribute (
    id bigint NOT NULL PRIMARY KEY,
    name varchar(255),
    type varchar(255),
    value varchar(255),
    attribute_group_id bigint REFERENCES attribute_group (id),
    mandatory boolean
);


/* DATA */

DROP TABLE IF EXISTS gde_file CASCADE;
CREATE TABLE gde_file (
    id bigint NOT NULL PRIMARY KEY,
    name varchar(255),
    length bigint,
    checksum varchar(255),
    creation_date timestamp,
    update_date timestamp,
    valid boolean,
    deleted boolean,
    deletion_date timestamp,
    attribute_group_id bigint REFERENCES attribute_group (id),
    data_profile_id bigint REFERENCES profile (id)
);

DROP TABLE IF EXISTS chunk CASCADE;
CREATE TABLE chunk (
    id bigint NOT NULL PRIMARY KEY,
    file_id bigint NOT NULL REFERENCES gde_file (id),
    rank bigint,
    checksum varchar(255),
    size bigint,
    data bytea
);

/* STUDIES */

DROP TABLE IF EXISTS study CASCADE;
CREATE TABLE study (
    id bigint NOT NULL PRIMARY KEY,
    name varchar(255),
    creation_date timestamp,
    update_date timestamp,
    valid boolean,
    deleted boolean,
    deletion_date timestamp,
    attribute_group_id bigint REFERENCES attribute_group (id),
    profile_id bigint REFERENCES profile (id)
);

/* PROFILES */

DROP TABLE IF EXISTS profile CASCADE;
CREATE TABLE profile (
    id bigint NOT NULL PRIMARY KEY,
    name varchar(255)
);

DROP TABLE IF EXISTS profile_attribute CASCADE;
CREATE TABLE profile_attribute (
    id bigint NOT NULL PRIMARY KEY,
    name varchar(255),
    type varchar(255),
    mandatory boolean,
    profile_id bigint REFERENCES profile (id)
);
