create user thehyve with password 'thehyve';
create database dntp_portal;
grant all privileges on database dntp_portal to thehyve;

create index var_procinst_name_index on act_hi_varinst (proc_inst_id_, name_ );
create index var_task_name_index on act_hi_varinst (task_id_, name_ );

create user thehyve with password '<strong random db password>';
create database dntp_portal;
grant all privileges on database dntp_portal to thehyve;

create index var_procinst_name_index on act_hi_varinst (proc_inst_id_, name_ );
create index var_task_name_index on act_hi_varinst (task_id_, name_ );

update app_user set specialism = 'Gastroenterology' where specialism = 'Maag-darm-lever-ziekten';
update app_user set specialism = 'Gynaecology' where specialism = 'Gynaecologie';
update app_user set specialism = 'Dermatology' where specialism = 'Dermatologie';
update app_user set specialism = 'Medical Oncology' where specialism = 'Medische Oncologie';
update app_user set specialism = 'Internal Medicine' where specialism = 'Interne geneeskunde';
update app_user set specialism = 'Radiology' where specialism = 'Radiologie';
update app_user set specialism = 'Radiotherapy' where specialism = 'Radiotherapie';
update app_user set specialism = 'Haematology' where specialism = 'Hematologie';
update app_user set specialism = 'Throat-nose-ear' where specialism = 'Keel-neus-oor';
update app_user set specialism = 'Surgery' where specialism = 'Heelkunde';
update app_user set specialism = 'Epidemiology' where specialism = 'Epidemiologie';
update app_user set specialism = 'Primary care' where specialism = 'Eerstelijnsgeneeskunde';
update app_user set specialism = 'Cardiology' where specialism = 'Cardiologie';
update app_user set specialism = 'Pathology' where specialism = 'Pathologie';
update app_user set specialism = 'Lung Disease' where specialism = 'Longziekten';
update app_user set specialism = 'Urology' where specialism = 'Urologie';
update app_user set specialism = 'Neurology' where specialism = 'Neurologie';
update app_user set specialism = 'Endocrinology' where specialism = 'Endocrinologie';

alter table request_properties add column last_assignee character varying(255);
alter table request_properties add column request_type character varying(255);
alter table request_properties add column block_materials_request boolean;
alter table request_properties add column he_slice_materials_request boolean;
alter table request_properties add column others_materials_request character varying(255);

-- Update existing materials request to select blocks and slides
update request_properties
    set block_materials_request = true, he_slice_materials_request = true
    where process_instance_id in (
      select proc_inst_id_ from act_ru_variable where name_ = 'is_materials_request' and long_ = 1
    );

alter table lab_request add column return_date timestamp without time zone;
alter table lab_request add column sent_return_email boolean;

alter table request_properties add column biobank_request_number character varying(255);
alter table request_properties add column germline_mutation boolean;

CREATE TABLE request_properties_informed_consent_form_attachments (
    request_properties_id bigint NOT NULL,
    informed_consent_form_attachments_id bigint NOT NULL
);
ALTER TABLE request_properties_informed_consent_form_attachments OWNER TO thehyve;


ALTER TABLE ONLY request_properties_informed_consent_form_attachments
    ADD CONSTRAINT uk_46tqt7cfxfkb9p4coyjq4q6s4 UNIQUE (informed_consent_form_attachments_id);


ALTER TABLE ONLY request_properties_informed_consent_form_attachments
    ADD CONSTRAINT fk_46tqt7cfxfkb9p4coyjq4q6s4 FOREIGN KEY (informed_consent_form_attachments_id) REFERENCES file(id);

ALTER TABLE ONLY request_properties_informed_consent_form_attachments
    ADD CONSTRAINT fk_8i21ti5yf37152ttn4vljo275 FOREIGN KEY (request_properties_id) REFERENCES request_properties(id);

alter table lab_request alter reject_reason type varchar(10000);

alter table excerpt_list add column remark_column int4 not null default -1;

alter table request_properties add column date_submitted timestamp;

update request_properties set date_submitted =
(select t.start_time_
	from act_hi_taskinst t
	where request_properties.process_instance_id = t.proc_inst_id_
	and t.task_def_key_ = 'palga_request_review'
	limit 1
)
where date_submitted is null;

# Add columns
alter table lab_request_comments add column comments_order int4 not null default -1;
alter table request_properties_comments add column comments_order int4 not null default -1;
alter table request_properties_approval_comments add column approval_comments_order int4 not null default -1;

# For lab request comments:

# Check if there are existing records that need to be updated
select * from lab_request_comments join comment on lab_request_comments.comments_id = comment.id where comments_order = -1 order by time_created;

# Same for request comments:
select * from request_properties_comments join comment on request_properties_comments.comments_id = comment.id where comments_order = -1 order by time_created;

# Same for request approval comments:
select * from request_properties_approval_comments join comment on request_properties_approval_comments.approval_comments_id = comment.id where approval_comments_order = -1 order by time_created;

alter table request_properties alter search_criteria type varchar(10000);
alter table request_properties alter laboratory_techniques type varchar(10000);
alter table request_properties alter privacy_committee_rationale type varchar(10000);

alter table excerpt_list add column palga_patient_nr_column int4 not null default -1;
alter table excerpt_list add column palga_excerpt_nr_column int4 not null default -1;
alter table excerpt_list add column palga_excerpt_id_column int4 not null default -1;

update pathology_item
set sequence_number = (select e.sequence_number
    from excerpt_entry e
    join excerpt_list l on l.id = e.excerpt_list_id
    join lab_request r on r.process_instance_id = l.process_instance_id
    join pathology_item i on i.pa_number = e.pa_number and i.lab_request_id = r.id
    where i.id = pathology_item.id
    limit 1);

alter table lab add hub_assistance_enabled boolean default true;

alter table excerpt_entry add selected boolean;