\set db_name annotdb
\set db_username imex
\set db_password hs0201
\set quoted_db_password '\'' :db_password '\''

CREATE DATABASE :db_name;

\c :db_name 

CREATE TABLE public."annotation"
(
    annot_id bigserial NOT NULL,
    annot_type character varying(256) NOT NULL,
    created_by character varying(256),
    creation_date timestamp,
    data json,
    instance_id character varying(512),
    modality character varying(256),
    series_id character varying(512),
    slice integer,
    study_id character varying(512),
    title character varying(512),
    parameters json,
	slices_number integer,
	version integer,
    PRIMARY KEY (annot_id)
)
WITH (
    OIDS = FALSE
);

	
CREATE TABLE public."annotation_version"
(
    annot_version_id bigserial NOT NULL,
    study_id character varying(512) COLLATE pg_catalog."default" NOT NULL,
    version integer NOT NULL,
    created_by character varying(256) COLLATE pg_catalog."default",
    creation_date timestamp,
    CONSTRAINT "AnnotationVersion_pkey" PRIMARY KEY (annot_version_id)
)
WITH (
    OIDS = FALSE
);


CREATE USER :db_username WITH PASSWORD :quoted_db_password;

GRANT CONNECT ON DATABASE :db_name TO :db_username;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE annotation TO :db_username;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE annotation_version TO :db_username;

GRANT USAGE, SELECT ON SEQUENCE annotation_annot_id_seq TO :db_username;

GRANT USAGE, SELECT ON SEQUENCE annotation_version_annot_version_id_seq TO :db_username;