/* Unique Ids sequence generator */

drop sequence if exists SEQ_GEN_SEQUENCE;
create sequence SEQ_GEN_SEQUENCE START WITH 1000 INCREMENT BY 50;


CREATE TABLE GROUP_ (
    id bigint NOT NULL PRIMARY KEY,
    groupName varchar(255) NOT NULL
);

CREATE TABLE USERS (
    id bigint NOT NULL PRIMARY KEY,
    userName varchar(255) not null,
    userpassword varchar(255) not null
);
CREATE INDEX users_username_index ON USERS(userName);

CREATE TABLE USERGROUP (
    id bigint NOT NULL PRIMARY KEY,
    groupId bigint references GROUP_(id) on delete cascade,
    userId bigint references USERS(id)  on delete cascade
);
create index usergroup_groupId_idx ON USERGROUP(groupId);



CREATE TABLE GROUPPERMISSIONS (
    id bigint NOT NULL PRIMARY KEY,
    groupId bigint references GROUP_(id),
    serviceName varchar(255) not null,
    methodIndex int not null
);

/* METADATA */

CREATE TABLE attribute_group (
    id bigint NOT NULL PRIMARY KEY
);

CREATE TABLE attribute (
    id bigint NOT NULL PRIMARY KEY,
    name varchar(255),
    type varchar(255),
    value varchar(255),
    attribute_group_id bigint not null REFERENCES attribute_group (id)  on delete cascade on update cascade,
    mandatory boolean
);

/* PROFILE */

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

CREATE TABLE profile_attribute (
    id bigint NOT NULL PRIMARY KEY,
    name varchar(255),
    type varchar(255),
    mandatory boolean,
    profile_id bigint not null REFERENCES profile (id) on delete cascade on update cascade
);

/* DATA */

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)
);

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 */

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),
    locked boolean,
    lock_owner bigint references users(id)
);
/* PROFILES */


-- The 1000 first ids are reserved for initial data
INSERT INTO users (id,username,userpassword) VALUES (1,'admin','edf123');
INSERT INTO group_ (id,groupname) VALUES (1,'admins');
INSERT into usergroup(id,groupid,userid) VALUES (2,1,1);
/* User management permissions */
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (3, 1, 'UserService',1); -- Create user 
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (4, 1, 'UserService',2); -- Delete user
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (5, 1, 'UserService',3); -- Add to group
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (6, 1, 'UserService',4); -- Remove from group
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (7, 1, 'UserService',5); -- Create group
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (8, 1, 'UserService',6); -- Delete group
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (9, 1, 'UserService',7); -- Find user
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (10, 1, 'UserService',8); -- Find group
/* Studies permissions */
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (11, 1, 'StudyService',1); -- Create study
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (12, 1, 'StudyService',2); -- Set study state
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (13, 1, 'StudyService',3); -- Read study
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (14, 1, 'StudyService',4); -- Delete study
/* Profiles services */
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (15, 1, 'ProfilesService',1); -- Create profile
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (16, 1, 'ProfilesService',2); -- Delete profile
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (17, 1, 'ProfilesService',3); -- Read profile
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (18, 1, 'ProfilesService',4); -- Update profile
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (19, 1, 'ProfilesService',5); -- Create profile attribute
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (20, 1, 'ProfilesService',6); -- Delete profile attribute
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (21, 1, 'ProfilesService',7); -- Read profile attribute
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (22, 1, 'ProfilesService',8); -- Update profile attribute
/* Attributes services */
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (23, 1, 'AttributesService',1); -- Create attribute
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (24, 1, 'AttributesService',2); -- Delete attribute
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (25, 1, 'AttributesService',3); -- Read attribute
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (26, 1, 'AttributesService',4); -- Create attribute group
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (27, 1, 'AttributesService',5); -- Delete attribute group
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (28, 1, 'AttributesService',6); -- Update attribute group
INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (29, 1, 'AttributesService',7); -- Read attribute group
 
