-- DROP SCHEMA public;

CREATE SCHEMA public AUTHORIZATION pg_database_owner;

-- DROP TYPE public.intbig_gkey;

CREATE TYPE public.intbig_gkey (
	INPUT = _intbig_in,
	OUTPUT = _intbig_out,
	ALIGNMENT = 4,
	STORAGE = plain,
	CATEGORY = U,
	DELIMITER = ',');

-- DROP TYPE public.query_int;

CREATE TYPE public.query_int (
	INPUT = bqarr_in,
	OUTPUT = bqarr_out,
	ALIGNMENT = 4,
	STORAGE = plain,
	CATEGORY = U,
	DELIMITER = ',');

-- DROP SEQUENCE public.action_id_seq;

CREATE SEQUENCE public.action_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.action_link_id_seq;

CREATE SEQUENCE public.action_link_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.afcnsend_id_seq;

CREATE SEQUENCE public.afcnsend_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.agelist_id_seq;

CREATE SEQUENCE public.agelist_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.audit_id_seq;

CREATE SEQUENCE public.audit_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.audit_record_id_seq;

CREATE SEQUENCE public.audit_record_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.audit_recordprocedure_id_seq;

CREATE SEQUENCE public.audit_recordprocedure_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.audit_subrecord_id_seq;

CREATE SEQUENCE public.audit_subrecord_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.audit_type_id_seq;

CREATE SEQUENCE public.audit_type_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.civilitylist_id_seq;

CREATE SEQUENCE public.civilitylist_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.default_read_needed_id_seq;

CREATE SEQUENCE public.default_read_needed_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.department_id_seq;

CREATE SEQUENCE public.department_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.device_id_seq;

CREATE SEQUENCE public.device_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.device_inspection_id_seq;

CREATE SEQUENCE public.device_inspection_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.device_installation_id_seq;

CREATE SEQUENCE public.device_installation_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.device_maintenance_id_seq;

CREATE SEQUENCE public.device_maintenance_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.document_id_seq;

CREATE SEQUENCE public.document_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.document_link_id_seq;

CREATE SEQUENCE public.document_link_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.document_master_id_seq;

CREATE SEQUENCE public.document_master_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.documentstatus_id_seq;

CREATE SEQUENCE public.documentstatus_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.documenttypelist_id_seq;

CREATE SEQUENCE public.documenttypelist_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.examident_id_seq;

CREATE SEQUENCE public.examident_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.examlist_id_seq;

CREATE SEQUENCE public.examlist_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.examwarning_id_seq;

CREATE SEQUENCE public.examwarning_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.external_link_id_seq;

CREATE SEQUENCE public.external_link_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.external_src_id_seq;

CREATE SEQUENCE public.external_src_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.institution_id_seq;

CREATE SEQUENCE public.institution_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.legaldocument_id_seq;

CREATE SEQUENCE public.legaldocument_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.login_role_id_seq;

CREATE SEQUENCE public.login_role_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.login_role_permission_id_seq;

CREATE SEQUENCE public.login_role_permission_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.procedure_id_seq;

CREATE SEQUENCE public.procedure_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.quaad_description_id_seq;

CREATE SEQUENCE public.quaad_description_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.quaad_point_id_seq;

CREATE SEQUENCE public.quaad_point_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.quaadpoint_tag_id_seq;

CREATE SEQUENCE public.quaadpoint_tag_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.quaadpoint_tag_name_id_seq;

CREATE SEQUENCE public.quaadpoint_tag_name_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.report_id_seq;

CREATE SEQUENCE public.report_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.sexlist_id_seq;

CREATE SEQUENCE public.sexlist_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.staff_formation_id_seq;

CREATE SEQUENCE public.staff_formation_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.staff_job_id_seq;

CREATE SEQUENCE public.staff_job_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.staff_member_id_seq;

CREATE SEQUENCE public.staff_member_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.staff_role_id_seq;

CREATE SEQUENCE public.staff_role_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.statuslist_id_seq;

CREATE SEQUENCE public.statuslist_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.survey_field_id_seq;

CREATE SEQUENCE public.survey_field_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.survey_field_type_id_seq;

CREATE SEQUENCE public.survey_field_type_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.survey_id_seq;

CREATE SEQUENCE public.survey_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.survey_resp_field_id_seq;

CREATE SEQUENCE public.survey_resp_field_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.survey_resp_id_seq;

CREATE SEQUENCE public.survey_resp_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.survey_type_id_seq;

CREATE SEQUENCE public.survey_type_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.tag_id_seq;

CREATE SEQUENCE public.tag_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.tag_link_id_seq;

CREATE SEQUENCE public.tag_link_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.task_id_seq;

CREATE SEQUENCE public.task_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.task_link_id_seq;

CREATE SEQUENCE public.task_link_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.task_master_id_seq;

CREATE SEQUENCE public.task_master_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.task_master_link_id_seq;

CREATE SEQUENCE public.task_master_link_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.task_task_master_id_seq;

CREATE SEQUENCE public.task_task_master_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;-- public.agelist definition

-- Drop table

-- DROP TABLE public.agelist;

CREATE TABLE public.agelist (
	id serial4 NOT NULL,
	"label" varchar NOT NULL,
	CONSTRAINT agelist_pkey PRIMARY KEY (id)
);


-- public.audit_type definition

-- Drop table

-- DROP TABLE public.audit_type;

CREATE TABLE public.audit_type (
	id serial4 NOT NULL,
	"name" varchar NOT NULL,
	removed varchar DEFAULT false NOT NULL,
	maintype varchar NULL,
	CONSTRAINT audit_type_pk PRIMARY KEY (id)
);


-- public.civilitylist definition

-- Drop table

-- DROP TABLE public.civilitylist;

CREATE TABLE public.civilitylist (
	id serial4 NOT NULL,
	"name" varchar NOT NULL
);


-- public.device definition

-- Drop table

-- DROP TABLE public.device;

CREATE TABLE public.device (
	id serial4 NOT NULL,
	"name" varchar NULL,
	brand varchar NULL,
	model varchar NULL,
	serialnumber varchar NULL,
	removed bool DEFAULT false NULL,
	ris_name varchar NULL,
	pacs_name varchar NULL,
	dacs_name varchar NULL,
	modalitytype varchar DEFAULT 'RX'::character varying NOT NULL,
	photo bytea NULL,
	CONSTRAINT device_pkey PRIMARY KEY (id)
);


-- public.document_master definition

-- Drop table

-- DROP TABLE public.document_master;

CREATE TABLE public.document_master (
	id serial4 NOT NULL,
	"name" varchar NOT NULL,
	removed bool DEFAULT false NULL,
	CONSTRAINT document_master_pkey PRIMARY KEY (id)
);


-- public.documenttypelist definition

-- Drop table

-- DROP TABLE public.documenttypelist;

CREATE TABLE public.documenttypelist (
	id serial4 NOT NULL,
	"name" varchar NOT NULL,
	CONSTRAINT documenttypelist_pkey PRIMARY KEY (id)
);


-- public.enum_listvalue definition

-- Drop table

-- DROP TABLE public.enum_listvalue;

CREATE TABLE public.enum_listvalue (
	"scope" varchar NULL,
	value int4 NULL,
	"name" varchar NULL,
	lang varchar NULL
);


-- public.examident definition

-- Drop table

-- DROP TABLE public.examident;

CREATE TABLE public.examident (
	id serial4 NOT NULL,
	"name" varchar NOT NULL,
	description varchar NOT NULL,
	removed bool DEFAULT false NULL,
	CONSTRAINT examident_pkey PRIMARY KEY (id)
);


-- public.examident_link definition

-- Drop table

-- DROP TABLE public.examident_link;

CREATE TABLE public.examident_link (
	exam_id int4 NULL,
	examident_id int4 NULL
);


-- public.examlist definition

-- Drop table

-- DROP TABLE public.examlist;

CREATE TABLE public.examlist (
	id serial4 NOT NULL,
	modality varchar NULL,
	nameid varchar NULL,
	description varchar NULL,
	removed bool DEFAULT false NULL,
	CONSTRAINT examlist_pkey PRIMARY KEY (id)
);


-- public.examwarning definition

-- Drop table

-- DROP TABLE public.examwarning;

CREATE TABLE public.examwarning (
	id serial4 NOT NULL,
	title varchar NULL,
	description varchar NULL,
	removed bool DEFAULT false NULL,
	sex_id int4 NULL,
	age_id int4 NULL,
	CONSTRAINT examwarning_pkey PRIMARY KEY (id)
);


-- public.external_src definition

-- Drop table

-- DROP TABLE public.external_src;

CREATE TABLE public.external_src (
	id serial4 NOT NULL,
	src_name varchar NOT NULL,
	CONSTRAINT external_src_pk PRIMARY KEY (id)
);


-- public.institution definition

-- Drop table

-- DROP TABLE public.institution;

CREATE TABLE public.institution (
	id serial4 NOT NULL,
	"name" varchar NOT NULL,
	removed bool DEFAULT false NULL,
	CONSTRAINT institution_pkey PRIMARY KEY (id)
);

-- Table Triggers

create trigger calltrg_init_init_tag_institution after
insert
    on
    public.institution for each row execute function trg_init_tag_institution();
create trigger calltrg_init_init_tag_institution_update after
update
    on
    public.institution for each row execute function trg_init_tag_institution_update();


-- public.legaldocument definition

-- Drop table

-- DROP TABLE public.legaldocument;

CREATE TABLE public.legaldocument (
	id serial4 NOT NULL,
	"name" varchar NULL,
	"type" varchar NULL,
	"comment" varchar NULL,
	removed bool DEFAULT false NULL,
	CONSTRAINT legaldocument_pkey PRIMARY KEY (id)
);


-- public.link_scope_view definition

-- Drop table

-- DROP TABLE public.link_scope_view;

CREATE TABLE public.link_scope_view (
	"scope" varchar NOT NULL,
	"view" varchar NULL
);


-- public.login_role definition

-- Drop table

-- DROP TABLE public.login_role;

CREATE TABLE public.login_role (
	id serial4 NOT NULL,
	"name" varchar NULL,
	removed bool DEFAULT false NULL,
	CONSTRAINT login_role_name_key UNIQUE (name),
	CONSTRAINT login_role_pkey PRIMARY KEY (id)
);

-- Table Triggers

create trigger calltrg_init_login_role_permission after
insert
    on
    public.login_role for each row execute function trg_init_login_role_permission();


-- public."procedure" definition

-- Drop table

-- DROP TABLE public."procedure";

CREATE TABLE public."procedure" (
	id serial4 NOT NULL,
	name_en varchar NULL,
	name_fr varchar NULL,
	name_nl varchar NULL,
	"comment" varchar NULL,
	removed bool DEFAULT false NULL,
	"type" int4 NULL,
	CONSTRAINT procedure_pkey PRIMARY KEY (id)
);


-- public.quaad_nameinscope definition

-- Drop table

-- DROP TABLE public.quaad_nameinscope;

CREATE TABLE public.quaad_nameinscope (
	scope_name varchar NOT NULL,
	"name" varchar NOT NULL,
	CONSTRAINT quaad_nameinscope_pk PRIMARY KEY (scope_name)
);


-- public.quaadpoint_tag definition

-- Drop table

-- DROP TABLE public.quaadpoint_tag;

CREATE TABLE public.quaadpoint_tag (
	id serial4 NOT NULL,
	procedure_needed varchar NULL,
	documents_needed int4 DEFAULT 0 NOT NULL,
	"scope" varchar NOT NULL,
	additional_param varchar NULL,
	"period" int4 DEFAULT '-1'::integer NOT NULL,
	scope2 varchar NULL,
	where_notagid varchar DEFAULT false NOT NULL,
	isglobalscope bool DEFAULT false NOT NULL,
	document_scope varchar DEFAULT 'institution'::character varying NOT NULL,
	CONSTRAINT quaadpoint_tag_pk PRIMARY KEY (id)
);


-- public.sequpdate_state definition

-- Drop table

-- DROP TABLE public.sequpdate_state;

CREATE TABLE public.sequpdate_state (
	updateid varchar NOT NULL,
	uid float8 NULL,
	CONSTRAINT sequpdate_state_pkey PRIMARY KEY (updateid)
);


-- public.sexlist definition

-- Drop table

-- DROP TABLE public.sexlist;

CREATE TABLE public.sexlist (
	id serial4 NOT NULL,
	"label" varchar NOT NULL,
	CONSTRAINT sexlist_pkey PRIMARY KEY (id)
);


-- public.staff_role definition

-- Drop table

-- DROP TABLE public.staff_role;

CREATE TABLE public.staff_role (
	id serial4 NOT NULL,
	"name" varchar NULL,
	removed bool DEFAULT false NULL,
	scope_id int4 DEFAULT 1 NULL,
	CONSTRAINT staff_role_pkey PRIMARY KEY (id)
);


-- public.statuslist definition

-- Drop table

-- DROP TABLE public.statuslist;

CREATE TABLE public.statuslist (
	id serial4 NOT NULL,
	status_name varchar NOT NULL,
	"type" varchar NULL,
	CONSTRAINT statuslist_pkey PRIMARY KEY (id)
);


-- public.survey definition

-- Drop table

-- DROP TABLE public.survey;

CREATE TABLE public.survey (
	id serial4 NOT NULL,
	title varchar NOT NULL,
	description varchar NULL,
	"type" int4 NOT NULL,
	status int4 NOT NULL,
	begindate timestamptz(0) NULL,
	enddate timestamptz(0) NULL,
	"comment" varchar NULL,
	conclusion varchar NULL,
	removed bool DEFAULT false NULL
);


-- public.survey_field definition

-- Drop table

-- DROP TABLE public.survey_field;

CREATE TABLE public.survey_field (
	id serial4 NOT NULL,
	"content" varchar NULL,
	"type" int4 NOT NULL,
	survey_id int4 NOT NULL,
	"position" int4 NOT NULL
);


-- public.survey_field_type definition

-- Drop table

-- DROP TABLE public.survey_field_type;

CREATE TABLE public.survey_field_type (
	id serial4 NOT NULL,
	"name" varchar NOT NULL
);


-- public.survey_resp definition

-- Drop table

-- DROP TABLE public.survey_resp;

CREATE TABLE public.survey_resp (
	id serial4 NOT NULL,
	survey_id int4 NOT NULL,
	rdate timestamptz(0) NULL
);


-- public.survey_resp_field definition

-- Drop table

-- DROP TABLE public.survey_resp_field;

CREATE TABLE public.survey_resp_field (
	id serial4 NOT NULL,
	field_id int4 NOT NULL,
	value int4 NOT NULL,
	resp_id int4 NOT NULL,
	value_text varchar NULL
);


-- public.survey_type definition

-- Drop table

-- DROP TABLE public.survey_type;

CREATE TABLE public.survey_type (
	id serial4 NOT NULL,
	"name" varchar NOT NULL
);


-- public.tag definition

-- Drop table

-- DROP TABLE public.tag;

CREATE TABLE public.tag (
	id serial4 NOT NULL,
	"type" varchar NULL,
	"name" varchar NOT NULL,
	removed bool DEFAULT false NULL,
	CONSTRAINT tag_pkey PRIMARY KEY (id)
);


-- public.task definition

-- Drop table

-- DROP TABLE public.task;

CREATE TABLE public.task (
	id serial4 NOT NULL,
	task_master_id serial4 NOT NULL,
	due_date timestamptz NOT NULL,
	title varchar NOT NULL,
	description varchar NULL,
	"comment" varchar NULL,
	status_id int4 DEFAULT 3 NULL,
	removed bool DEFAULT false NULL,
	closing_date timestamptz NULL,
	CONSTRAINT task_pk PRIMARY KEY (id)
);


-- public.task_link definition

-- Drop table

-- DROP TABLE public.task_link;

CREATE TABLE public.task_link (
	id serial4 NOT NULL,
	task_id int4 NOT NULL,
	staff_member_id int4 NOT NULL,
	status_id int4 NOT NULL,
	"comment" varchar NULL,
	status_date timestamptz NULL,
	last_reminder timestamptz NULL,
	last_reminder_staff_id int4 NULL
);


-- public.task_master definition

-- Drop table

-- DROP TABLE public.task_master;

CREATE TABLE public.task_master (
	id serial4 NOT NULL,
	title varchar NOT NULL,
	description varchar NULL,
	start_date timestamptz NOT NULL,
	end_date timestamptz DEFAULT '2099-12-31 00:00:00+01'::timestamp with time zone NULL,
	recurrence int4 DEFAULT 0 NOT NULL,
	day_of_week varchar DEFAULT '-1'::character varying NOT NULL,
	active bool DEFAULT true NOT NULL,
	dep_id int4 DEFAULT '-1'::integer NOT NULL,
	removed bool DEFAULT false NOT NULL,
	closing_type int4 DEFAULT 0 NULL,
	CONSTRAINT task_master_pk PRIMARY KEY (id)
);


-- public.task_master_link definition

-- Drop table

-- DROP TABLE public.task_master_link;

CREATE TABLE public.task_master_link (
	id serial4 NOT NULL,
	task_master_id int4 NOT NULL,
	staff_member_id int4 NOT NULL
);


-- public.xusercfgparameter definition

-- Drop table

-- DROP TABLE public.xusercfgparameter;

CREATE TABLE public.xusercfgparameter (
	cfgparameter_id int4 NOT NULL,
	vtype int4 NULL,
	"type" int4 DEFAULT 1 NULL,
	vorder int4 NULL,
	variable varchar NOT NULL,
	"label" varchar NOT NULL,
	description varchar NULL,
	vdefault varchar NULL,
	CONSTRAINT xusercfgparameter_pk PRIMARY KEY (cfgparameter_id)
);
CREATE INDEX xusercfgparameteridx ON public.xusercfgparameter USING btree (variable);


-- public.department definition

-- Drop table

-- DROP TABLE public.department;

CREATE TABLE public.department (
	id serial4 NOT NULL,
	"name" varchar NOT NULL,
	inst_id int4 NULL,
	removed bool DEFAULT false NULL,
	CONSTRAINT department_pkey PRIMARY KEY (id),
	CONSTRAINT department_inst_id_fkey FOREIGN KEY (inst_id) REFERENCES public.institution(id)
);

-- Table Triggers

create trigger calltrg_init_init_tag_department after
insert
    on
    public.department for each row execute function trg_init_tag_department();
create trigger calltrg_init_init_tag_department_update after
update
    on
    public.department for each row execute function trg_init_tag_department_update();


-- public.device_inspection definition

-- Drop table

-- DROP TABLE public.device_inspection;

CREATE TABLE public.device_inspection (
	id serial4 NOT NULL,
	device_id int4 NOT NULL,
	inspection_date timestamptz NULL,
	planned_date timestamptz NULL,
	"result" int4 NULL,
	"comment" varchar NULL,
	"type" varchar NULL,
	status_id int4 NULL,
	removed bool DEFAULT false NULL,
	CONSTRAINT device_inspection_pkey PRIMARY KEY (id),
	CONSTRAINT device_inspection_device_id_fkey FOREIGN KEY (device_id) REFERENCES public.device(id),
	CONSTRAINT device_inspection_status_id_fkey FOREIGN KEY (status_id) REFERENCES public.statuslist(id)
);


-- public.device_installation definition

-- Drop table

-- DROP TABLE public.device_installation;

CREATE TABLE public.device_installation (
	id serial4 NOT NULL,
	device_id int4 NOT NULL,
	dep_id int4 NOT NULL,
	installdate timestamptz NOT NULL,
	removeddate timestamptz NULL,
	removed bool DEFAULT false NULL,
	installedby varchar DEFAULT ''::character varying NULL,
	inchargeof varchar DEFAULT ''::character varying NULL,
	"comment" varchar DEFAULT ''::character varying NULL,
	CONSTRAINT device_installation_pkey PRIMARY KEY (id),
	CONSTRAINT device_installation_dep_id_fkey FOREIGN KEY (dep_id) REFERENCES public.department(id),
	CONSTRAINT device_installation_device_id_fkey FOREIGN KEY (device_id) REFERENCES public.device(id)
);


-- public.device_maintenance definition

-- Drop table

-- DROP TABLE public.device_maintenance;

CREATE TABLE public.device_maintenance (
	id serial4 NOT NULL,
	device_id int4 NOT NULL,
	maintenance_date timestamptz NULL,
	planned_date timestamptz NULL,
	"result" int4 NULL,
	"comment" varchar NULL,
	"type" varchar NULL,
	status_id int4 NULL,
	removed bool DEFAULT false NULL,
	CONSTRAINT device_maintenance_pkey PRIMARY KEY (id),
	CONSTRAINT device_maintenance_device_id_fkey FOREIGN KEY (device_id) REFERENCES public.device(id),
	CONSTRAINT device_maintenance_status_id_fkey FOREIGN KEY (status_id) REFERENCES public.statuslist(id)
);


-- public.examwarning_link definition

-- Drop table

-- DROP TABLE public.examwarning_link;

CREATE TABLE public.examwarning_link (
	exam_id int4 NULL,
	examwarning_id int4 NULL,
	CONSTRAINT examwarning_link_exam_id_fkey FOREIGN KEY (exam_id) REFERENCES public.examlist(id),
	CONSTRAINT examwarning_link_examwarning_id_fkey FOREIGN KEY (examwarning_id) REFERENCES public.examwarning(id)
);


-- public.external_link definition

-- Drop table

-- DROP TABLE public.external_link;

CREATE TABLE public.external_link (
	id serial4 NOT NULL,
	table_name varchar NOT NULL,
	table_id int8 NOT NULL,
	ext_id varchar NOT NULL,
	ext_src int4 NOT NULL,
	CONSTRAINT external_link_pk PRIMARY KEY (id),
	CONSTRAINT external_link_fk FOREIGN KEY (ext_src) REFERENCES public.external_src(id)
);


-- public.login_role_permission definition

-- Drop table

-- DROP TABLE public.login_role_permission;

CREATE TABLE public.login_role_permission (
	id serial4 NOT NULL,
	ptype varchar DEFAULT ''::character varying NULL,
	nameid varchar NOT NULL,
	description varchar DEFAULT ''::character varying NULL,
	def_value bool DEFAULT false NULL,
	value bool DEFAULT false NULL,
	working_view bool DEFAULT false NULL,
	working_view_exist bool DEFAULT false NULL,
	login_role_id int4 NOT NULL,
	CONSTRAINT login_role_permission_pkey PRIMARY KEY (id),
	CONSTRAINT login_role_permission_login_role_id_fkey FOREIGN KEY (login_role_id) REFERENCES public.login_role(id)
);


-- public.quaad_point definition

-- Drop table

-- DROP TABLE public.quaad_point;

CREATE TABLE public.quaad_point (
	id serial4 NOT NULL,
	level1 int4 DEFAULT '-1'::integer NOT NULL,
	level2 int4 DEFAULT '-1'::integer NOT NULL,
	level3 int4 DEFAULT '-1'::integer NOT NULL,
	level4 int4 DEFAULT '-1'::integer NOT NULL,
	level5 int4 DEFAULT '-1'::integer NOT NULL,
	inlevelorder int4 DEFAULT 1 NOT NULL,
	istitle bool DEFAULT false NULL,
	datebeg date NULL,
	dateend date NULL,
	quaadversion int4 NULL,
	CONSTRAINT quaad_point_pk PRIMARY KEY (id),
	CONSTRAINT quaad_point_un UNIQUE (level1, level2, level3, level4, level5, inlevelorder, quaadversion),
	CONSTRAINT quaad_point_fk FOREIGN KEY (quaadversion) REFERENCES public.audit_type(id)
);


-- public.quaadpoint_tag_name definition

-- Drop table

-- DROP TABLE public.quaadpoint_tag_name;

CREATE TABLE public.quaadpoint_tag_name (
	id serial4 NOT NULL,
	"name" varchar NOT NULL,
	"language" varchar DEFAULT 'FR'::character varying NOT NULL,
	id_quaadpoint_tag int4 NOT NULL,
	CONSTRAINT quaadpoint_tag_name_pk PRIMARY KEY (id),
	CONSTRAINT quaadpoint_tag_name_un UNIQUE (id_quaadpoint_tag),
	CONSTRAINT quaadpoint_tag_name_fk FOREIGN KEY (id_quaadpoint_tag) REFERENCES public.quaadpoint_tag(id)
);


-- public.staff_member definition

-- Drop table

-- DROP TABLE public.staff_member;

CREATE TABLE public.staff_member (
	id serial4 NOT NULL,
	lastname varchar NULL,
	firstname varchar NULL,
	removed bool DEFAULT false NULL,
	login varchar NOT NULL,
	"password" varchar NOT NULL,
	login_role_id int4 NULL,
	active bool DEFAULT false NULL,
	ris_name varchar NULL,
	pacs_name varchar NULL,
	dacs_name varchar NULL,
	photo bytea NULL,
	email varchar NULL,
	civility_id int4 NULL,
	CONSTRAINT staff_member_login_key UNIQUE (login),
	CONSTRAINT staff_member_pkey PRIMARY KEY (id),
	CONSTRAINT staff_member_login_role_id_fkey FOREIGN KEY (login_role_id) REFERENCES public.login_role(id)
);


-- public.tag_link definition

-- Drop table

-- DROP TABLE public.tag_link;

CREATE TABLE public.tag_link (
	id serial4 NOT NULL,
	"type" varchar NOT NULL,
	ext_id int4 NULL,
	tag_id int4 NULL,
	CONSTRAINT tag_link_pkey PRIMARY KEY (id),
	CONSTRAINT tag_link_tag_id_fkey FOREIGN KEY (tag_id) REFERENCES public.tag(id)
);


-- public.xusercfg definition

-- Drop table

-- DROP TABLE public.xusercfg;

CREATE TABLE public.xusercfg (
	id int4 NOT NULL,
	"data" varchar NULL,
	cfgparameter_id int4 NOT NULL,
	CONSTRAINT staff_member_xusercfg_fk FOREIGN KEY (id) REFERENCES public.staff_member(id)
);


-- public."action" definition

-- Drop table

-- DROP TABLE public."action";

CREATE TABLE public."action" (
	id serial4 NOT NULL,
	description varchar NULL,
	status_id int4 NULL,
	origine varchar NULL,
	deadline timestamptz NULL,
	enddate timestamptz NULL,
	begdate timestamptz NOT NULL,
	personincharge int4 NULL,
	removed bool DEFAULT false NULL,
	dep_id int4 DEFAULT '-1'::integer NULL,
	CONSTRAINT action_pkey PRIMARY KEY (id),
	CONSTRAINT action_personincharge_fkey FOREIGN KEY (personincharge) REFERENCES public.staff_member(id),
	CONSTRAINT action_status_id_fkey FOREIGN KEY (status_id) REFERENCES public.statuslist(id)
);


-- public.action_link definition

-- Drop table

-- DROP TABLE public.action_link;

CREATE TABLE public.action_link (
	id serial4 NOT NULL,
	action_id int4 NOT NULL,
	ext_id int4 NOT NULL,
	"scope" varchar NOT NULL,
	CONSTRAINT action_link_pk PRIMARY KEY (id),
	CONSTRAINT action_link_un UNIQUE (action_id, ext_id, scope),
	CONSTRAINT action_link_fk FOREIGN KEY (action_id) REFERENCES public."action"(id)
);


-- public.afcnsend definition

-- Drop table

-- DROP TABLE public.afcnsend;

CREATE TABLE public.afcnsend (
	id serial4 NOT NULL,
	inst_id int4 NULL,
	dep_id int4 NULL,
	device_id int4 NULL,
	title varchar NULL,
	senddate timestamptz NOT NULL,
	sender int4 NULL,
	"comment" varchar NULL,
	removed bool DEFAULT false NULL,
	status_id int4 NULL,
	CONSTRAINT afcnsend_pkey PRIMARY KEY (id),
	CONSTRAINT afcnsend_dep_id_fkey FOREIGN KEY (dep_id) REFERENCES public.department(id),
	CONSTRAINT afcnsend_device_id_fkey FOREIGN KEY (device_id) REFERENCES public.device(id),
	CONSTRAINT afcnsend_inst_id_fkey FOREIGN KEY (inst_id) REFERENCES public.institution(id),
	CONSTRAINT afcnsend_sender_fkey FOREIGN KEY (sender) REFERENCES public.staff_member(id)
);


-- public.audit definition

-- Drop table

-- DROP TABLE public.audit;

CREATE TABLE public.audit (
	id serial4 NOT NULL,
	inst_id int4 DEFAULT '-1'::integer NULL,
	dep_id int4 DEFAULT '-1'::integer NULL,
	"date" timestamptz NULL,
	origine int4 NULL,
	title varchar NULL,
	"comment" varchar NULL,
	status_id int4 NULL,
	personincharge int4 NULL,
	removed bool DEFAULT false NULL,
	"type" int4 DEFAULT 2 NOT NULL,
	start_date timestamptz(0) NULL,
	end_date timestamptz(0) NULL,
	CONSTRAINT audit_pkey PRIMARY KEY (id),
	CONSTRAINT audit_dep_id_fkey FOREIGN KEY (dep_id) REFERENCES public.department(id),
	CONSTRAINT audit_fk FOREIGN KEY ("type") REFERENCES public.audit_type(id),
	CONSTRAINT audit_inst_id_fkey FOREIGN KEY (inst_id) REFERENCES public.institution(id),
	CONSTRAINT audit_personincharge_fkey FOREIGN KEY (personincharge) REFERENCES public.staff_member(id),
	CONSTRAINT audit_status_id_fkey FOREIGN KEY (status_id) REFERENCES public.statuslist(id)
);


-- public.audit_record definition

-- Drop table

-- DROP TABLE public.audit_record;

CREATE TABLE public.audit_record (
	id serial4 NOT NULL,
	audit_id int4 NOT NULL,
	quaad_point_id int4 NOT NULL,
	validated bool DEFAULT false NOT NULL,
	CONSTRAINT audit_record_pk PRIMARY KEY (id),
	CONSTRAINT audit_record_un UNIQUE (quaad_point_id, audit_id),
	CONSTRAINT audit_record_fk FOREIGN KEY (audit_id) REFERENCES public.audit(id),
	CONSTRAINT audit_record_fk_1 FOREIGN KEY (quaad_point_id) REFERENCES public.quaad_point(id)
);


-- public.audit_subrecord definition

-- Drop table

-- DROP TABLE public.audit_subrecord;

CREATE TABLE public.audit_subrecord (
	id serial4 NOT NULL,
	audit_record_id int4 NOT NULL,
	quaadpoint_tag_id int4 NOT NULL,
	scope_id int4 NOT NULL,
	scope2_id int4 NULL,
	force_doctext text NULL,
	force_docid int4 NULL,
	remark text NULL,
	status bool DEFAULT false NOT NULL,
	forcedstatus bool NULL,
	doc_id int4 NOT NULL,
	force_result varchar NULL,
	CONSTRAINT audit_subrecord_pk PRIMARY KEY (id),
	CONSTRAINT audit_subrecord_fk FOREIGN KEY (audit_record_id) REFERENCES public.audit_record(id),
	CONSTRAINT audit_subrecord_fk_1 FOREIGN KEY (quaadpoint_tag_id) REFERENCES public.quaadpoint_tag(id)
);


-- public.default_action_needed definition

-- Drop table

-- DROP TABLE public.default_action_needed;

CREATE TABLE public.default_action_needed (
	document_type varchar NULL,
	staff_role_id int4 NULL,
	department_id int4 NULL,
	id int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
	"action" int4 NOT NULL,
	CONSTRAINT default_read_needed_fk FOREIGN KEY (department_id) REFERENCES public.department(id),
	CONSTRAINT default_read_needed_fk_1 FOREIGN KEY (staff_role_id) REFERENCES public.staff_role(id)
);


-- public."document" definition

-- Drop table

-- DROP TABLE public."document";

CREATE TABLE public."document" (
	id serial4 NOT NULL,
	file varchar NULL,
	realfilename varchar NULL,
	folder varchar NOT NULL,
	"type" varchar DEFAULT 'PDF'::character varying NOT NULL,
	creation_date timestamptz DEFAULT now() NOT NULL,
	removed bool DEFAULT false NULL,
	revision int4 DEFAULT 1 NOT NULL,
	"label" varchar DEFAULT ''::character varying NULL,
	"comment" varchar DEFAULT ''::character varying NULL,
	uploadedby int4 NOT NULL,
	doc_master_id int4 NOT NULL,
	sourcefile varchar NULL,
	sourcerealfilename varchar NULL,
	extern_link varchar NULL,
	upload_date timestamptz DEFAULT now() NOT NULL,
	valid_until_date timestamptz DEFAULT to_timestamp('29991231235959'::text, 'YYYYMMDDHH24MISS'::text) NOT NULL,
	modification_date timestamptz DEFAULT now() NULL,
	CONSTRAINT document_pkey PRIMARY KEY (id),
	CONSTRAINT document_doc_master_id_fkey FOREIGN KEY (doc_master_id) REFERENCES public.document_master(id),
	CONSTRAINT document_uploadedby_fkey FOREIGN KEY (uploadedby) REFERENCES public.staff_member(id)
);


-- public.document_link definition

-- Drop table

-- DROP TABLE public.document_link;

CREATE TABLE public.document_link (
	id serial4 NOT NULL,
	"type" varchar NOT NULL,
	ext_id int4 NULL,
	doc_id int4 NULL,
	CONSTRAINT document_link_pkey PRIMARY KEY (id),
	CONSTRAINT document_link_doc_id_fkey FOREIGN KEY (doc_id) REFERENCES public."document"(id)
);


-- public.documentstatus definition

-- Drop table

-- DROP TABLE public.documentstatus;

CREATE TABLE public.documentstatus (
	id serial4 NOT NULL,
	begdate timestamptz NOT NULL,
	enddate timestamptz DEFAULT '2999-12-31 00:00:00+01'::timestamp with time zone NOT NULL,
	doc_id int4 NOT NULL,
	status_id int4 NOT NULL,
	CONSTRAINT documentstatus_pkey PRIMARY KEY (id),
	CONSTRAINT documentstatus_doc_id_fkey FOREIGN KEY (doc_id) REFERENCES public."document"(id),
	CONSTRAINT documentstatus_status_id_fkey FOREIGN KEY (status_id) REFERENCES public.statuslist(id)
);
CREATE INDEX laststatus_per_document_index ON public.documentstatus USING btree (doc_id, begdate DESC);


-- public.link_quaad_tag definition

-- Drop table

-- DROP TABLE public.link_quaad_tag;

CREATE TABLE public.link_quaad_tag (
	id_quaadpoint int4 NOT NULL,
	id_tag int4 NOT NULL,
	CONSTRAINT link_quaad_tag_pk PRIMARY KEY (id_quaadpoint, id_tag),
	CONSTRAINT link_quaad_tag_fk FOREIGN KEY (id_tag) REFERENCES public.quaadpoint_tag(id),
	CONSTRAINT link_quaad_tag_fk_1 FOREIGN KEY (id_quaadpoint) REFERENCES public.quaad_point(id)
);


-- public.quaad_description definition

-- Drop table

-- DROP TABLE public.quaad_description;

CREATE TABLE public.quaad_description (
	id serial4 NOT NULL,
	id_quaadpoint int4 NOT NULL,
	"language" varchar NOT NULL,
	description varchar NULL,
	description_summary varchar NULL,
	CONSTRAINT quaad_description_pk PRIMARY KEY (id),
	CONSTRAINT quaad_description_un UNIQUE (id_quaadpoint, language),
	CONSTRAINT quaad_description_fk FOREIGN KEY (id_quaadpoint) REFERENCES public.quaad_point(id)
);


-- public.quaadtag_doc_link definition

-- Drop table

-- DROP TABLE public.quaadtag_doc_link;

CREATE TABLE public.quaadtag_doc_link (
	id_quaadtag int4 NOT NULL,
	id_doclink int4 NOT NULL,
	CONSTRAINT quaadtag_doc_link_pk PRIMARY KEY (id_doclink, id_quaadtag),
	CONSTRAINT quaadtag_doc_link_fk FOREIGN KEY (id_doclink) REFERENCES public.document_link(id),
	CONSTRAINT quaadtag_doc_link_fk_1 FOREIGN KEY (id_quaadtag) REFERENCES public.quaadpoint_tag(id)
);


-- public.report definition

-- Drop table

-- DROP TABLE public.report;

CREATE TABLE public.report (
	id serial4 NOT NULL,
	severity int4 DEFAULT 0 NULL,
	title varchar NULL,
	reportdate timestamptz NOT NULL,
	closeddate timestamptz NULL,
	reporter int4 NULL,
	"comment" varchar DEFAULT ''::character varying NULL,
	statuscomment varchar DEFAULT ''::character varying NULL,
	status_id int4 NULL,
	dep_id int4 NULL,
	removed bool DEFAULT false NULL,
	CONSTRAINT report_pkey PRIMARY KEY (id),
	CONSTRAINT report_dep_id_fkey FOREIGN KEY (dep_id) REFERENCES public.department(id),
	CONSTRAINT report_reporter_fkey FOREIGN KEY (reporter) REFERENCES public.staff_member(id),
	CONSTRAINT report_status_id_fkey FOREIGN KEY (status_id) REFERENCES public.statuslist(id)
);


-- public.staff_formation definition

-- Drop table

-- DROP TABLE public.staff_formation;

CREATE TABLE public.staff_formation (
	id serial4 NOT NULL,
	staff_id int4 NOT NULL,
	formation_date timestamptz NULL,
	planned_date timestamptz NULL,
	title varchar NULL,
	"comment" varchar NULL,
	"type" varchar NULL,
	status_id int4 NULL,
	removed bool DEFAULT false NULL,
	staff_member_id int4 NULL,
	CONSTRAINT staff_formation_pkey PRIMARY KEY (id),
	CONSTRAINT staff_formation_staff_id_fkey FOREIGN KEY (staff_id) REFERENCES public.staff_member(id),
	CONSTRAINT staff_formation_status_id_fkey FOREIGN KEY (status_id) REFERENCES public.statuslist(id)
);

-- Table Triggers

create trigger f_staffmember_equals_staff before
insert
    or
update
    on
    public.staff_formation for each row execute function f_staffmember_equals_staff();


-- public.staff_job definition

-- Drop table

-- DROP TABLE public.staff_job;

CREATE TABLE public.staff_job (
	id serial4 NOT NULL,
	dep_id int4 NOT NULL,
	staff_id int4 NOT NULL,
	begdate timestamptz NOT NULL,
	enddate timestamptz DEFAULT '2999-12-31 00:00:00+01'::timestamp with time zone NOT NULL,
	staff_role_id int4 NOT NULL,
	active bool DEFAULT false NULL,
	removed bool DEFAULT false NULL,
	CONSTRAINT staff_job_pkey PRIMARY KEY (id),
	CONSTRAINT staff_job_dep_id_fkey FOREIGN KEY (dep_id) REFERENCES public.department(id),
	CONSTRAINT staff_job_staff_id_fkey FOREIGN KEY (staff_id) REFERENCES public.staff_member(id),
	CONSTRAINT staff_job_staff_role_id_fkey FOREIGN KEY (staff_role_id) REFERENCES public.staff_role(id)
);


-- public.action_status_link definition

-- Drop table

-- DROP TABLE public.action_status_link;

CREATE TABLE public.action_status_link (
	staff_member_id int4 NOT NULL,
	document_id int4 NOT NULL,
	last_opened timestamp NULL,
	action_at timestamp NULL,
	action_id int4 NULL,
	"date" timestamp NULL,
	last_reminder timestamp NULL,
	CONSTRAINT read_status_link_pk UNIQUE (staff_member_id, document_id, action_id),
	CONSTRAINT read_status_link_fk FOREIGN KEY (staff_member_id) REFERENCES public.staff_member(id),
	CONSTRAINT read_status_link_fk_1 FOREIGN KEY (document_id) REFERENCES public."document"(id)
);


-- public.audit_recordprocedure definition

-- Drop table

-- DROP TABLE public.audit_recordprocedure;

CREATE TABLE public.audit_recordprocedure (
	id serial4 NOT NULL,
	audit_record_id int4 NOT NULL,
	procedure_id int4 NOT NULL,
	doc_id int4 NOT NULL,
	remark text NULL,
	status bool DEFAULT false NOT NULL,
	forcedstatus bool NULL,
	CONSTRAINT audit_recordprocedure_pk PRIMARY KEY (id),
	CONSTRAINT audit_recordprocedure_fk FOREIGN KEY (audit_record_id) REFERENCES public.audit_record(id),
	CONSTRAINT audit_recordprocedure_fk_1 FOREIGN KEY (procedure_id) REFERENCES public."procedure"(id),
	CONSTRAINT audit_recordprocedure_fk_2 FOREIGN KEY (doc_id) REFERENCES public."document"(id)
);


-- public.v_alert source

CREATE OR REPLACE VIEW public.v_alert
AS SELECT 'device_inspection'::character varying AS type,
    device.name,
        CASE
            WHEN now()::date >= device_inspection.planned_date::date THEN 3
            WHEN (now()::date + '3 days'::interval)::date >= device_inspection.planned_date::date THEN 2
            ELSE 1
        END AS level,
    device_inspection.planned_date,
    ''::character varying AS title
   FROM device_inspection,
    device
  WHERE device.id = device_inspection.device_id AND device_inspection.status_id = 3 AND device_inspection.planned_date IS NOT NULL
UNION ALL
 SELECT 'staff_formation'::character varying AS type,
    (staff_member.lastname::text || ' '::text) || staff_member.firstname::text AS name,
        CASE
            WHEN now()::date >= staff_formation.planned_date::date THEN 3
            WHEN (now()::date + '3 days'::interval)::date >= staff_formation.planned_date::date THEN 2
            ELSE 1
        END AS level,
    staff_formation.planned_date,
    staff_formation.title
   FROM staff_formation,
    staff_member
  WHERE staff_member.id = staff_formation.staff_id AND staff_formation.status_id = 3 AND staff_formation.planned_date IS NOT NULL
UNION ALL
 SELECT 'action'::character varying AS type,
    action.description AS name,
        CASE
            WHEN now()::date >= action.deadline::date THEN 3
            WHEN (now()::date + '3 days'::interval)::date >= action.deadline::date THEN 2
            ELSE 1
        END AS level,
    action.deadline AS planned_date,
    action.origine AS title
   FROM action
  WHERE action.removed = false AND action.status_id = 3 AND action.deadline IS NOT NULL;


-- public.v_alert_action source

CREATE OR REPLACE VIEW public.v_alert_action
AS SELECT description,
        CASE
            WHEN now()::date >= deadline::date THEN 3
            WHEN (now()::date + '3 days'::interval)::date >= deadline::date THEN 2
            ELSE 1
        END AS level,
    deadline,
    origine AS title,
    dep_id
   FROM action
  WHERE removed = false AND status_id = 3 AND deadline IS NOT NULL;


-- public.v_alert_afcnsend source

CREATE OR REPLACE VIEW public.v_alert_afcnsend
AS SELECT afcnsend.id,
        CASE
            WHEN now()::date >= afcnsend.senddate::date THEN 3
            WHEN (now()::date + '3 days'::interval)::date >= afcnsend.senddate::date THEN 2
            ELSE 1
        END AS level,
    afcnsend.senddate,
    afcnsend.comment,
    department.name AS department,
    department.id AS dep_id
   FROM afcnsend
     LEFT JOIN department ON afcnsend.dep_id = department.id
  WHERE afcnsend.removed = false AND afcnsend.status_id = 3 AND afcnsend.senddate IS NOT NULL;


-- public.v_alert_audit source

CREATE OR REPLACE VIEW public.v_alert_audit
AS SELECT id,
        CASE
            WHEN now()::date >= date::date THEN 3
            WHEN (now()::date + '3 days'::interval)::date >= date::date THEN 2
            ELSE 1
        END AS level,
    date,
    title,
    inst_id,
    dep_id
   FROM audit
  WHERE removed = false AND status_id = 3 AND date IS NOT NULL;


-- public.v_alert_device_inspection source

CREATE OR REPLACE VIEW public.v_alert_device_inspection
AS SELECT device.id,
    device.name,
        CASE
            WHEN now()::date >= device_inspection.planned_date::date THEN 3
            WHEN (now()::date + '3 days'::interval)::date >= device_inspection.planned_date::date THEN 2
            ELSE 1
        END AS level,
    device_inspection.planned_date,
    ''::character varying AS title
   FROM device_inspection,
    device
  WHERE device.removed = false AND device_inspection.removed = false AND device.id = device_inspection.device_id AND device_inspection.status_id = 3 AND device_inspection.planned_date IS NOT NULL;


-- public.v_alert_device_maintenance source

CREATE OR REPLACE VIEW public.v_alert_device_maintenance
AS SELECT device.id,
    device.name,
        CASE
            WHEN now()::date >= device_maintenance.planned_date::date THEN 3
            WHEN (now()::date + '3 days'::interval)::date >= device_maintenance.planned_date::date THEN 2
            ELSE 1
        END AS level,
    device_maintenance.planned_date,
    ''::character varying AS title
   FROM device_maintenance,
    device
  WHERE device.removed = false AND device_maintenance.removed = false AND device.id = device_maintenance.device_id AND device_maintenance.status_id = 3 AND device_maintenance.planned_date IS NOT NULL;


-- public.v_alert_report source

CREATE OR REPLACE VIEW public.v_alert_report
AS SELECT title,
    COALESCE(severity, 1) AS severity,
    reportdate,
    dep_id
   FROM report
  WHERE status_id = 7 AND removed = false
  ORDER BY (COALESCE(severity, 1)) DESC NULLS LAST, reportdate;


-- public.v_alert_staff_formation source

CREATE OR REPLACE VIEW public.v_alert_staff_formation
AS SELECT (staff_member.lastname::text || ' '::text) || staff_member.firstname::text AS name,
        CASE
            WHEN now()::date >= staff_formation.planned_date::date THEN 3
            WHEN (now()::date + '3 days'::interval)::date >= staff_formation.planned_date::date THEN 2
            ELSE 1
        END AS level,
    staff_formation.planned_date,
    staff_formation.title,
    staff_member.id AS staff_id
   FROM staff_formation,
    staff_member
  WHERE staff_member.id = staff_formation.staff_id AND staff_formation.status_id = 3 AND staff_formation.planned_date IS NOT NULL AND staff_formation.removed = false AND staff_member.removed = false;


-- public.v_alert_task source

CREATE OR REPLACE VIEW public.v_alert_task
AS WITH t1 AS (
         SELECT t.id,
            t.task_master_id,
            t.due_date,
            t.title,
            t.description,
            t.comment,
            t.status_id,
            t.removed,
            tl.id,
            tl.task_id,
            tl.staff_member_id,
            tl.status_id,
            tl.comment,
            tl.status_date,
            tl.last_reminder,
            tl.last_reminder_staff_id
           FROM task t,
            task_link tl
          WHERE t.id = tl.task_id AND tl.status_id <> 4
        )
 SELECT task_id,
    description,
        CASE
            WHEN now()::date >= due_date::date THEN 3
            WHEN (now()::date + '3 days'::interval)::date >= due_date::date THEN 2
            ELSE 1
        END AS level,
    due_date,
    title,
    staff_member_id,
    task_master_id,
    last_reminder,
    last_reminder_staff_id
   FROM t1 t1(id, task_master_id, due_date, title, description, comment, status_id, removed, id_1, task_id, staff_member_id, status_id_1, comment_1, status_date, last_reminder, last_reminder_staff_id)
  WHERE removed = false;


-- public.v_bquaad_deviceinspection source

CREATE OR REPLACE VIEW public.v_bquaad_deviceinspection
AS WITH t4 AS (
         WITH t3 AS (
                 WITH t2 AS (
                         WITH t AS (
                                 SELECT device_inspection.device_id,
                                    device_inspection.id,
CASE
 WHEN device_inspection.result = 5 THEN 'OK'::text
 ELSE 'KO'::text
END AS result_okko,
                                    device_inspection.result AS inspection_result,
                                    device_inspection.inspection_date,
                                    device_inspection.planned_date,
                                    action.id AS act_id,
                                    action.status_id AS act_status,
                                    action.description AS action_desc,
                                    device_inspection.comment AS inspection_comment
                                   FROM device_inspection
                                     LEFT JOIN ( SELECT action_link_1.id,
    action_link_1.action_id,
    action_link_1.ext_id,
    action_link_1.scope
   FROM action_link action_link_1
  WHERE action_link_1.scope::text = 'device_inspection'::text) action_link ON action_link.ext_id = device_inspection.id
                                     LEFT JOIN action ON action_link.action_id = action.id
                                  WHERE device_inspection.removed = false AND (action.removed = false OR action.removed IS NULL)
                                )
                         SELECT t.id,
                            t.result_okko,
                            t.inspection_result,
                            t.inspection_date,
                            t.planned_date,
                            t.act_id,
                            t.act_status,
                            t.action_desc AS inspection_comment,
                            doc_l.doc_id,
                            t.device_id
                           FROM t
                             LEFT JOIN ( SELECT document_link.id,
                                    document_link.type,
                                    document_link.ext_id,
                                    document_link.doc_id
                                   FROM document_link
                                  WHERE document_link.type::text = 'device_inspection'::text) doc_l ON doc_l.ext_id = t.id
                        )
                 SELECT
                        CASE
                            WHEN t2.result_okko = 'OK'::text THEN 'OK'::text
                            ELSE
                            CASE
                                WHEN t2.act_id IS NOT NULL THEN 'OK'::text
                                ELSE 'KO'::text
                            END
                        END AS force_result,
                    t2.id,
                    t2.result_okko,
                    t2.inspection_result,
                    t2.inspection_date,
                    t2.planned_date,
                    t2.act_id,
                    t2.act_status,
                    t2.inspection_comment,
                    t2.doc_id,
                    t2.device_id
                   FROM t2
                )
         SELECT t3.id,
            t3.device_id,
                CASE
                    WHEN t3.inspection_date IS NULL THEN NULL::integer
                    ELSE t3.doc_id
                END AS doc_id,
            t3.force_result,
                CASE
                    WHEN t3.force_result = 'KO'::text THEN ('Le contrôle a été effectué le '::text || to_char(t3.inspection_date, 'DD/MM/YYYY'::text)) || ' avec un résultat négatif et aucune tâche n''a été planifiée'::text
                    WHEN t3.result_okko = t3.force_result AND t3.result_okko = 'OK'::text THEN ('Le contrôle a été effectué le '::text || to_char(t3.inspection_date, 'DD/MM/YYYY'::text)) || ' et le résultat est positif'::text
                    ELSE (('Le contrôle a été effectué le '::text || to_char(t3.inspection_date, 'DD/MM/YYYY'::text)) || ' et le résultat est négatif<br><br> La tâche suivante a été planifiée dans le logiciel eoQUAL:<br>'::text) || t3.inspection_comment::text
                END AS force_doctext,
            NULL::text AS force_docid,
            t3.inspection_date,
            t3.planned_date
           FROM t3
          WHERE t3.inspection_date IS NOT NULL
        )
 SELECT t4.id,
    t4.device_id,
    t4.doc_id,
    t4.force_result,
    t4.force_doctext,
    t4.force_docid,
    t4.inspection_date,
    t4.planned_date,
    device_installation.id AS v_device_plus_deviceinstallation_devid_id,
    COALESCE(t4.inspection_date, t4.planned_date) AS tocheck_date
   FROM t4,
    device_installation
  WHERE t4.device_id = device_installation.id;


-- public.v_deptagidbyinsttagid source

CREATE OR REPLACE VIEW public.v_deptagidbyinsttagid
AS SELECT i.tag_id AS insttagid,
    string_agg(d.tag_id::text, ','::text) AS tagdepidforinsttagid
   FROM tag_link i,
    tag_link d,
    department
  WHERE i.type::text = 'institution'::text AND department.id = d.ext_id AND d.type::text = 'department'::text AND department.inst_id = i.ext_id
  GROUP BY i.tag_id;


-- public.v_device_actif_by_deptagid source

CREATE OR REPLACE VIEW public.v_device_actif_by_deptagid
AS SELECT device_installation.dep_id,
    tag_link.tag_id,
    now()::date >= device_installation.installdate::date AND COALESCE(device_installation.removeddate::date, now()::date) >= now()::date AS is_actif,
    department.name AS depname,
    device_installation.device_id,
    device_installation.installdate
   FROM device_installation,
    department,
    tag_link
  WHERE tag_link.type::text = 'department'::text AND tag_link.ext_id = department.id AND device_installation.dep_id = department.id AND device_installation.removed = false;


-- public.v_device_historyinstallationdeptagidlist source

CREATE OR REPLACE VIEW public.v_device_historyinstallationdeptagidlist
AS SELECT device_installation.device_id,
    (('{'::text || string_agg(DISTINCT tag_link.tag_id::text, ','::text)) || '}'::text)::integer[] AS deptagidlist,
    (('{'::text || string_agg(DISTINCT tag_link.ext_id::text, ','::text)) || '}'::text)::integer[] AS depidlist
   FROM device_installation,
    tag_link
  WHERE tag_link.type::text = 'department'::text AND tag_link.ext_id = device_installation.dep_id AND device_installation.removed = false
  GROUP BY device_installation.device_id;


-- public.v_device_plus_deviceinstallation_devid source

CREATE OR REPLACE VIEW public.v_device_plus_deviceinstallation_devid
AS SELECT device.id,
    device.brand,
    device.dacs_name,
    device.modalitytype,
    device.model,
    device.name,
    device.pacs_name,
    device.removed,
    device.ris_name,
    device.serialnumber,
    device_installation.id AS install_id,
    device_installation.comment,
    device_installation.dep_id,
    device_installation.inchargeof,
    device_installation.installdate,
    device_installation.installedby,
    device_installation.removed AS inst_removed,
    device_installation.removeddate
   FROM device_installation,
    device
  WHERE device.id = device_installation.device_id;


-- public.v_device_plus_deviceinstallation_instid source

CREATE OR REPLACE VIEW public.v_device_plus_deviceinstallation_instid
AS SELECT device.id AS dev_id,
    device.brand,
    device.dacs_name,
    device.modalitytype,
    device.model,
    device.name,
    device.pacs_name,
    device.removed,
    device.ris_name,
    device.serialnumber,
    device_installation.id,
    device_installation.comment,
    device_installation.dep_id,
    device_installation.inchargeof,
    device_installation.installdate,
    device_installation.installedby,
    device_installation.removed AS inst_removed,
    device_installation.removeddate
   FROM device_installation,
    device
  WHERE device.id = device_installation.device_id;


-- public.v_exam_risk_tag_doc source

CREATE OR REPLACE VIEW public.v_exam_risk_tag_doc
AS SELECT allexam.id,
    allexam.modality,
    allexam.nameid AS name,
    allexam.description,
    allexam.riskv AS risklist,
    allexam.taglist,
    allexamwithdoc.doc_id,
    allexamwithotherdoc.doc_otherdoc_id
   FROM ( SELECT examlist.id,
            examlist.modality,
            examlist.nameid,
            examlist.description,
            string_agg(examwarning_link.examwarning_id::text, ','::text) AS riskv,
            f_tag_link(examlist.id) AS taglist
           FROM examlist
             LEFT JOIN examwarning_link ON examwarning_link.exam_id = examlist.id
          WHERE examlist.removed = false AND (examwarning_link.examwarning_id IS NULL OR examwarning_link.examwarning_id IS NOT NULL)
          GROUP BY examlist.id, examlist.nameid, examlist.description) allexam
     LEFT JOIN ( SELECT document_link.doc_id,
            document_link.ext_id
           FROM v_last_documentstatus,
            document_link
          WHERE v_last_documentstatus.status_id = 1 AND v_last_documentstatus.doc_id = document_link.doc_id AND document_link.type::text = 'examlist'::text) allexamwithdoc ON allexamwithdoc.ext_id = allexam.id
     LEFT JOIN ( SELECT document_link.doc_id AS doc_otherdoc_id,
            document_link.ext_id
           FROM v_last_documentstatus,
            document_link
          WHERE v_last_documentstatus.status_id = 1 AND v_last_documentstatus.doc_id = document_link.doc_id AND document_link.type::text = 'examlist_otherdocument'::text) allexamwithotherdoc ON allexamwithotherdoc.ext_id = allexam.id
  ORDER BY allexam.id, allexamwithdoc.doc_id;


-- public.v_fullinfo_department source

CREATE OR REPLACE VIEW public.v_fullinfo_department
AS SELECT institution.name::text || COALESCE(' '::text || department.name::text, ''::text) AS fullname,
    institution.id AS inst_id,
    institution.name AS inst_name,
    department.id AS dep_id,
    department.name AS dep_name,
    institution.removed OR COALESCE(department.removed, false) AS instordepremoved
   FROM institution
     LEFT JOIN department ON department.inst_id = institution.id;


-- public.v_last_documentstatus source

CREATE OR REPLACE VIEW public.v_last_documentstatus
AS SELECT DISTINCT ON (doc_id) id,
    begdate,
    enddate,
    doc_id,
    status_id
   FROM documentstatus
  ORDER BY doc_id, begdate DESC;


-- public.v_notremoveddocument source

CREATE OR REPLACE VIEW public.v_notremoveddocument
AS SELECT document.id,
    document.file,
    document.realfilename,
    document.folder,
    document.type,
    document.creation_date,
    document.removed,
    document.revision,
    document.label,
    document.comment,
    document.uploadedby,
    document.doc_master_id,
    document.sourcefile,
    document.sourcerealfilename
   FROM document,
    document_master
  WHERE document.removed = false AND document_master.removed = false AND document.doc_master_id = document_master.id;


-- public.v_staff_member_depaccess source

CREATE OR REPLACE VIEW public.v_staff_member_depaccess
AS SELECT DISTINCT dp.id AS dep_id,
    idbj.staff_id
   FROM department dp,
    ( SELECT DISTINCT
                CASE
                    WHEN staff_role.scope_id = 0 THEN vfd.inst_id
                    ELSE NULL::integer
                END AS inst_id,
                CASE
                    WHEN staff_role.scope_id = 0 THEN NULL::integer
                    ELSE vfd.dep_id
                END AS dep_id,
            staff_job.staff_id
           FROM staff_job,
            staff_role,
            v_fullinfo_department vfd
          WHERE staff_job.removed = false AND staff_role.removed = false AND staff_role.id = staff_job.staff_role_id AND staff_job.begdate <= now() AND now() <= staff_job.enddate AND staff_job.dep_id <> '-1'::integer AND vfd.dep_id = staff_job.dep_id
        UNION
         SELECT NULL::integer AS int4,
            vfd.dep_id,
            staff_member.id
           FROM staff_member,
            v_fullinfo_department vfd
          WHERE staff_member.login_role_id = 0 AND vfd.dep_id <> '-1'::integer) idbj
  WHERE idbj.inst_id = dp.inst_id OR idbj.dep_id = dp.id;


-- public.v_staff_member_historyjobdeplist source

CREATE OR REPLACE VIEW public.v_staff_member_historyjobdeplist
AS SELECT DISTINCT vfd.dep_id,
    staff_job.staff_id
   FROM staff_job,
    staff_role,
    v_fullinfo_department vfd
  WHERE staff_job.removed = false AND staff_role.removed = false AND staff_role.id = staff_job.staff_role_id AND staff_job.dep_id <> '-1'::integer AND vfd.dep_id = staff_job.dep_id;


-- public.v_staff_member_historyjobdeptagidlist source

CREATE OR REPLACE VIEW public.v_staff_member_historyjobdeptagidlist
AS SELECT v_staff_member_historyjobdeplist.staff_id,
    (('{'::text || string_agg(tag_link.tag_id::text, ','::text)) || '}'::text)::integer[] AS deptagidlist,
    (('{'::text || string_agg(tag_link.ext_id::text, ','::text)) || '}'::text)::integer[] AS depidlist
   FROM v_staff_member_historyjobdeplist,
    tag_link
  WHERE tag_link.type::text = 'department'::text AND tag_link.ext_id = v_staff_member_historyjobdeplist.dep_id
  GROUP BY v_staff_member_historyjobdeplist.staff_id;


-- public.v_staffmember_taglistdep_active_staffjob source

CREATE OR REPLACE VIEW public.v_staffmember_taglistdep_active_staffjob
AS SELECT (('{'::text || COALESCE(string_agg(DISTINCT tag_link.tag_id::text, ','::text), ''::text)) || '}'::text)::integer[] AS taglistdep_id,
    staff_job.staff_id
   FROM staff_job,
    department,
    tag_link
  WHERE staff_job.removed = false AND department.removed = false AND staff_job.begdate <= now() AND staff_job.enddate >= now() AND department.id = staff_job.dep_id AND tag_link.ext_id = department.id AND tag_link.type::text = 'department'::text
  GROUP BY staff_job.staff_id;


-- public.v_survey source

CREATE OR REPLACE VIEW public.v_survey
AS SELECT survey_field.survey_id,
    survey_field.id AS field_id,
    survey_field.type AS field_type,
    survey_field."position"
   FROM survey,
    survey_field
  WHERE survey.id = survey_field.survey_id;


-- public.v_taginfo source

CREATE OR REPLACE VIEW public.v_taginfo
AS SELECT tt.id,
    tt.link_type,
    tt.ext_id,
    tt.tag_id,
    tt.type,
    tt.alltagidallow,
    tt.name,
    tt.removed,
    ((('{'::text || string_agg(v1.tagdepidforinsttagid, ','::text)) || '}'::text)::integer[]) || ((('{'::text || string_agg(v2.insttagid::text, ','::text)) || '}'::text)::integer[]) AS alltagidinducedallow
   FROM ( SELECT tag_link.id,
            tag_link.type AS link_type,
            tag_link.ext_id,
            tag_link.tag_id,
            tag.type,
            (('{'::text || string_agg(tag_link.tag_id::text, ','::text) OVER (PARTITION BY tag_link.type, tag_link.ext_id)) || '}'::text)::integer[] AS alltagidallow,
            (('{'::text || string_agg(
                CASE
                    WHEN tag.type::text = 'OR'::text THEN tag_link.tag_id::text
                    ELSE NULL::text
                END, ','::text) OVER (PARTITION BY tag_link.type, tag_link.ext_id)) || '}'::text)::integer[] AS tagtomapinstid,
            tag.name,
            tag.removed
           FROM tag_link,
            tag
          WHERE tag.id = tag_link.tag_id) tt
     LEFT JOIN v_deptagidbyinsttagid v1 ON v1.insttagid = ANY (tt.alltagidallow)
     LEFT JOIN v_deptagidbyinsttagid v2 ON ((('{'::text || v2.tagdepidforinsttagid) || '}'::text)::integer[]) OPERATOR(pg_catalog.&&) tt.tagtomapinstid
  GROUP BY tt.id, tt.link_type, tt.ext_id, tt.tag_id, tt.type, tt.alltagidallow, tt.name, tt.removed;


-- public.v_taglink_instdep source

CREATE OR REPLACE VIEW public.v_taglink_instdep
AS SELECT v_fullinfo_department.inst_id,
    ti.tag_id AS taginstid,
    v_fullinfo_department.dep_id,
    td.tag_id AS tagdepid,
    v_fullinfo_department.instordepremoved
   FROM tag_link ti,
    tag_link td,
    v_fullinfo_department
  WHERE ti.type::text = 'institution'::text AND td.type::text = 'department'::text AND ti.ext_id = v_fullinfo_department.inst_id AND td.ext_id = v_fullinfo_department.dep_id;



-- DROP FUNCTION public."_int_contained"(_int4, _int4);

CREATE OR REPLACE FUNCTION public._int_contained(integer[], integer[])
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_int_contained$function$
;

-- DROP FUNCTION public."_int_contained_joinsel"(internal, oid, internal, int2, internal);

CREATE OR REPLACE FUNCTION public._int_contained_joinsel(internal, oid, internal, smallint, internal)
 RETURNS double precision
 LANGUAGE c
 STABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_int_contained_joinsel$function$
;

-- DROP FUNCTION public."_int_contained_sel"(internal, oid, internal, int4);

CREATE OR REPLACE FUNCTION public._int_contained_sel(internal, oid, internal, integer)
 RETURNS double precision
 LANGUAGE c
 STABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_int_contained_sel$function$
;

-- DROP FUNCTION public."_int_contains"(_int4, _int4);

CREATE OR REPLACE FUNCTION public._int_contains(integer[], integer[])
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_int_contains$function$
;

-- DROP FUNCTION public."_int_contains_joinsel"(internal, oid, internal, int2, internal);

CREATE OR REPLACE FUNCTION public._int_contains_joinsel(internal, oid, internal, smallint, internal)
 RETURNS double precision
 LANGUAGE c
 STABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_int_contains_joinsel$function$
;

-- DROP FUNCTION public."_int_contains_sel"(internal, oid, internal, int4);

CREATE OR REPLACE FUNCTION public._int_contains_sel(internal, oid, internal, integer)
 RETURNS double precision
 LANGUAGE c
 STABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_int_contains_sel$function$
;

-- DROP FUNCTION public."_int_different"(_int4, _int4);

CREATE OR REPLACE FUNCTION public._int_different(integer[], integer[])
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_int_different$function$
;

-- DROP FUNCTION public."_int_inter"(_int4, _int4);

CREATE OR REPLACE FUNCTION public._int_inter(integer[], integer[])
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_int_inter$function$
;

-- DROP FUNCTION public."_int_matchsel"(internal, oid, internal, int4);

CREATE OR REPLACE FUNCTION public._int_matchsel(internal, oid, internal, integer)
 RETURNS double precision
 LANGUAGE c
 STABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_int_matchsel$function$
;

-- DROP FUNCTION public."_int_overlap"(_int4, _int4);

CREATE OR REPLACE FUNCTION public._int_overlap(integer[], integer[])
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_int_overlap$function$
;

-- DROP FUNCTION public."_int_overlap_joinsel"(internal, oid, internal, int2, internal);

CREATE OR REPLACE FUNCTION public._int_overlap_joinsel(internal, oid, internal, smallint, internal)
 RETURNS double precision
 LANGUAGE c
 STABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_int_overlap_joinsel$function$
;

-- DROP FUNCTION public."_int_overlap_sel"(internal, oid, internal, int4);

CREATE OR REPLACE FUNCTION public._int_overlap_sel(internal, oid, internal, integer)
 RETURNS double precision
 LANGUAGE c
 STABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_int_overlap_sel$function$
;

-- DROP FUNCTION public."_int_same"(_int4, _int4);

CREATE OR REPLACE FUNCTION public._int_same(integer[], integer[])
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_int_same$function$
;

-- DROP FUNCTION public."_int_union"(_int4, _int4);

CREATE OR REPLACE FUNCTION public._int_union(integer[], integer[])
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_int_union$function$
;

-- DROP FUNCTION public."_intbig_in"(cstring);

CREATE OR REPLACE FUNCTION public._intbig_in(cstring)
 RETURNS intbig_gkey
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_intbig_in$function$
;

-- DROP FUNCTION public."_intbig_out"(intbig_gkey);

CREATE OR REPLACE FUNCTION public._intbig_out(intbig_gkey)
 RETURNS cstring
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$_intbig_out$function$
;

-- DROP FUNCTION public.boolop(_int4, query_int);

CREATE OR REPLACE FUNCTION public.boolop(integer[], query_int)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$boolop$function$
;

-- DROP FUNCTION public.bqarr_in(cstring);

CREATE OR REPLACE FUNCTION public.bqarr_in(cstring)
 RETURNS query_int
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$bqarr_in$function$
;

-- DROP FUNCTION public.bqarr_out(query_int);

CREATE OR REPLACE FUNCTION public.bqarr_out(query_int)
 RETURNS cstring
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$bqarr_out$function$
;

-- DROP FUNCTION public.f_create_login_role_permission();

CREATE OR REPLACE FUNCTION public.f_create_login_role_permission()
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$ 


  	  	    DECLARE 


  	  			v_admin_id integer; 


  	  	    BEGIN 






  	  		 v_admin_id := 0; 






  	         insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	          (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_INSTITUTION'::text as nameid, 'View institution settings' as description ,true as def_value, true as value) as rp_data 


  	          where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 


  	         insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	          (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_STAFFROLE'::text as nameid, 'View staff role settings' as description ,true as def_value, true as value) as rp_data 


  	          where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 






  	         insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	          (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_LOGINROLE'::text as nameid, 'View login role settings' as description ,true as def_value, true as value) as rp_data 


  	          where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 






  	         insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	          (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_STAFFMEMBER'::text as nameid, 'View staff member settings' as description ,true as def_value, true as value) as rp_data 


  	          where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 






  	          insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	           (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_TAG'::text as nameid, 'View tag settings' as description ,true as def_value, true as value) as rp_data 


  	           where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 






  	         insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	          (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_WARNING'::text as nameid, 'View warning settings' as description ,true as def_value, true as value) as rp_data 


  	          where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 






  	          insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	           (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_DASHBOARD'::text as nameid, 'View dashboard frame' as description ,true as def_value, true as value) as rp_data 


  	           where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 






  	          insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	            (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_AUDIT'::text as nameid, 'View audit frame' as description ,true as def_value, true as value) as rp_data 


  	            where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 






  	          insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value,working_view,working_view_exist) select login_role_id,ptype,nameid,description,def_value,value,false,true from 


  	            (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_PROCEDURE'::text as nameid, 'View procedure frame' as description ,true as def_value, true as value) as rp_data 


  	            where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 






  	          insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	            (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_DEVICE'::text as nameid, 'View device frame' as description ,true as def_value, true as value,false,true) as rp_data 


  	            where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 






  	          insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value,working_view,working_view_exist) select login_role_id,ptype,nameid,description,def_value,value,false,true from 


  	              (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_EXAMLIST'::text as nameid, 'View exam list frame' as description ,true as def_value, true as value) as rp_data 


  	              where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 






  	          insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	              (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_STAFF'::text as nameid, 'View staff frame' as description ,true as def_value, true as value) as rp_data 


  	              where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 






  	          insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	              (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_DOSIMETRY'::text as nameid, 'View dosimetry frame' as description ,true as def_value, true as value) as rp_data 


  	              where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 






  	          insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	              (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_LEGALDOCUMENT'::text as nameid, 'View legal document frame' as description ,true as def_value, true as value) as rp_data 


  	              where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 






  	          insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	              (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_SCHEDULEDTASK'::text as nameid, 'View scheduled task frame' as description ,true as def_value, true as value) as rp_data 


  	              where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 


  	          insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	              (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_INCIDENTREPORTING'::text as nameid, 'View incident reporting frame' as description ,true as def_value, true as value) as rp_data 


  	              where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 






  	          insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	          (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_INSTITUTIONDOCUMENT'::text as nameid, 'View institution document frame' as description ,true as def_value, true as value) as rp_data 


  	          where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 




  	           insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	          (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_EXAMIDENT'::text as nameid, 'View the exam link frame' as description ,true as def_value, true as value) as rp_data 


  	          where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 




  	          insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	          (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_EXAMLINK'::text as nameid, 'View the exam link frame' as description ,true as def_value, true as value) as rp_data 


  	          where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 




              insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


              (select v_admin_id as login_role_id,'VIEW' as ptype, 'VIEW_STATISTIC'::text as nameid, 'View the statistic frame' as description ,true as def_value, true as value) as rp_data 


              where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 




  	         -- insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	         --  (select v_admin_id as login_role_id,'ACTION' as ptype, 'ACTION_ADD_INSTITUTION'::text as nameid,'Can add an institution' as description ,true as def_value, true as value) as rp_data 


  	         --  where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 


  	         -- insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	         --  (select v_admin_id as login_role_id,'ACTION' as ptype, 'ACTION_MODIFY_INSTITUTION'::text as nameid,'Can modify an institution' as description ,true as def_value, true as value) as rp_data 


  	         --  where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 


  	         -- insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value) select login_role_id,ptype,nameid,description,def_value,value from 


  	         --  (select v_admin_id as login_role_id,'ACTION' as ptype, 'ACTION_DELETE_INSTITUTION'::text as nameid,'Can delete an institution' as description ,true as def_value, true as value) as rp_data 


  	         --  where not exists (select * from login_role_permission where v_admin_id = login_role_permission.login_role_id and login_role_permission.nameid = rp_data.nameid); 










  	  	    -- update other role if necessary 


  	  	    insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value,working_view,working_view_exist) 


  	          select roleid,alluserpermission.ptype,alluserpermission.nameid,alluserpermission.description,alluserpermission.def_value,alluserpermission.def_value,alluserpermission.working_view,alluserpermission.working_view_exist from (select login_role.id as roleid,ptype,nameid,description,def_value,value,working_view,working_view_exist from 


  	  	  (select login_role_permission.ptype,nameid,description,def_value,value,working_view,working_view_exist from login_role_permission,login_role where login_role_permission.login_role_id = login_role.id and login_role.id = v_admin_id) as adminpermission,login_role) as alluserpermission 


  	  	   left join login_role_permission on login_role_permission.login_role_id = roleid and  login_role_permission.nameid = alluserpermission.nameid 


  	  	   where id is null; 






  	    		return true; 


  	  	    END 


  	  	$function$
;

-- DROP FUNCTION public.f_staffmember_equals_staff();

CREATE OR REPLACE FUNCTION public.f_staffmember_equals_staff()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$ 
	begin  
		new.staff_member_id := new.staff_id; 
	 
	return new; 
	end; 
$function$
;

-- DROP FUNCTION public.f_tag_link(int4);

CREATE OR REPLACE FUNCTION public.f_tag_link(in_exam_id integer)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
  	    DECLARE
  			v_list varchar;
  	    BEGIN

  		 v_list := null;
		select STRING_AGG (tag_link.tag_id::text,',') into v_list from tag_link where type = 'examlist' and ext_id = in_exam_id;
		RETURN v_list;

  	    END
  	$function$
;

-- DROP FUNCTION public.g_int_compress(internal);

CREATE OR REPLACE FUNCTION public.g_int_compress(internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$g_int_compress$function$
;

-- DROP FUNCTION public.g_int_consistent(internal, _int4, int2, oid, internal);

CREATE OR REPLACE FUNCTION public.g_int_consistent(internal, integer[], smallint, oid, internal)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$g_int_consistent$function$
;

-- DROP FUNCTION public.g_int_decompress(internal);

CREATE OR REPLACE FUNCTION public.g_int_decompress(internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$g_int_decompress$function$
;

-- DROP FUNCTION public.g_int_options(internal);

CREATE OR REPLACE FUNCTION public.g_int_options(internal)
 RETURNS void
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/_int', $function$g_int_options$function$
;

-- DROP FUNCTION public.g_int_penalty(internal, internal, internal);

CREATE OR REPLACE FUNCTION public.g_int_penalty(internal, internal, internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$g_int_penalty$function$
;

-- DROP FUNCTION public.g_int_picksplit(internal, internal);

CREATE OR REPLACE FUNCTION public.g_int_picksplit(internal, internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$g_int_picksplit$function$
;

-- DROP FUNCTION public.g_int_same(_int4, _int4, internal);

CREATE OR REPLACE FUNCTION public.g_int_same(integer[], integer[], internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$g_int_same$function$
;

-- DROP FUNCTION public.g_int_union(internal, internal);

CREATE OR REPLACE FUNCTION public.g_int_union(internal, internal)
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$g_int_union$function$
;

-- DROP FUNCTION public.g_intbig_compress(internal);

CREATE OR REPLACE FUNCTION public.g_intbig_compress(internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$g_intbig_compress$function$
;

-- DROP FUNCTION public.g_intbig_consistent(internal, _int4, int2, oid, internal);

CREATE OR REPLACE FUNCTION public.g_intbig_consistent(internal, integer[], smallint, oid, internal)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$g_intbig_consistent$function$
;

-- DROP FUNCTION public.g_intbig_decompress(internal);

CREATE OR REPLACE FUNCTION public.g_intbig_decompress(internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$g_intbig_decompress$function$
;

-- DROP FUNCTION public.g_intbig_options(internal);

CREATE OR REPLACE FUNCTION public.g_intbig_options(internal)
 RETURNS void
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/_int', $function$g_intbig_options$function$
;

-- DROP FUNCTION public.g_intbig_penalty(internal, internal, internal);

CREATE OR REPLACE FUNCTION public.g_intbig_penalty(internal, internal, internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$g_intbig_penalty$function$
;

-- DROP FUNCTION public.g_intbig_picksplit(internal, internal);

CREATE OR REPLACE FUNCTION public.g_intbig_picksplit(internal, internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$g_intbig_picksplit$function$
;

-- DROP FUNCTION public.g_intbig_same(intbig_gkey, intbig_gkey, internal);

CREATE OR REPLACE FUNCTION public.g_intbig_same(intbig_gkey, intbig_gkey, internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$g_intbig_same$function$
;

-- DROP FUNCTION public.g_intbig_union(internal, internal);

CREATE OR REPLACE FUNCTION public.g_intbig_union(internal, internal)
 RETURNS intbig_gkey
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$g_intbig_union$function$
;

-- DROP FUNCTION public.ginint4_consistent(internal, int2, _int4, int4, internal, internal, internal, internal);

CREATE OR REPLACE FUNCTION public.ginint4_consistent(internal, smallint, integer[], integer, internal, internal, internal, internal)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$ginint4_consistent$function$
;

-- DROP FUNCTION public.ginint4_queryextract(_int4, internal, int2, internal, internal, internal, internal);

CREATE OR REPLACE FUNCTION public.ginint4_queryextract(integer[], internal, smallint, internal, internal, internal, internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$ginint4_queryextract$function$
;

-- DROP FUNCTION public.icount(_int4);

CREATE OR REPLACE FUNCTION public.icount(integer[])
 RETURNS integer
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$icount$function$
;

-- DROP FUNCTION public.idx(_int4, int4);

CREATE OR REPLACE FUNCTION public.idx(integer[], integer)
 RETURNS integer
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$idx$function$
;

-- DROP FUNCTION public.intarray_del_elem(_int4, int4);

CREATE OR REPLACE FUNCTION public.intarray_del_elem(integer[], integer)
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$intarray_del_elem$function$
;

-- DROP FUNCTION public.intarray_push_array(_int4, _int4);

CREATE OR REPLACE FUNCTION public.intarray_push_array(integer[], integer[])
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$intarray_push_array$function$
;

-- DROP FUNCTION public.intarray_push_elem(_int4, int4);

CREATE OR REPLACE FUNCTION public.intarray_push_elem(integer[], integer)
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$intarray_push_elem$function$
;

-- DROP FUNCTION public.intset(int4);

CREATE OR REPLACE FUNCTION public.intset(integer)
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$intset$function$
;

-- DROP FUNCTION public.intset_subtract(_int4, _int4);

CREATE OR REPLACE FUNCTION public.intset_subtract(integer[], integer[])
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$intset_subtract$function$
;

-- DROP FUNCTION public.intset_union_elem(_int4, int4);

CREATE OR REPLACE FUNCTION public.intset_union_elem(integer[], integer)
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$intset_union_elem$function$
;

-- DROP FUNCTION public.querytree(query_int);

CREATE OR REPLACE FUNCTION public.querytree(query_int)
 RETURNS text
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$querytree$function$
;

-- DROP FUNCTION public.rboolop(query_int, _int4);

CREATE OR REPLACE FUNCTION public.rboolop(query_int, integer[])
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$rboolop$function$
;

-- DROP FUNCTION public.sort(_int4, text);

CREATE OR REPLACE FUNCTION public.sort(integer[], text)
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$sort$function$
;

-- DROP FUNCTION public.sort(_int4);

CREATE OR REPLACE FUNCTION public.sort(integer[])
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$sort$function$
;

-- DROP FUNCTION public.sort_asc(_int4);

CREATE OR REPLACE FUNCTION public.sort_asc(integer[])
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$sort_asc$function$
;

-- DROP FUNCTION public.sort_desc(_int4);

CREATE OR REPLACE FUNCTION public.sort_desc(integer[])
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$sort_desc$function$
;

-- DROP FUNCTION public.subarray(_int4, int4);

CREATE OR REPLACE FUNCTION public.subarray(integer[], integer)
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$subarray$function$
;

-- DROP FUNCTION public.subarray(_int4, int4, int4);

CREATE OR REPLACE FUNCTION public.subarray(integer[], integer, integer)
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$subarray$function$
;

-- DROP FUNCTION public.trg_init_login_role_permission();

CREATE OR REPLACE FUNCTION public.trg_init_login_role_permission()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
  DECLARE
  v_admin_id integer;
  BEGIN

    v_admin_id := 0;
    insert into login_role_permission(login_role_id,ptype,nameid,description,def_value,value,working_view,working_view_exist)
    select NEW.id,ptype,nameid,description,def_value,def_value,working_view,working_view_exist from login_role_permission where login_role_id = v_admin_id;

    RETURN NEW;
  END;
  $function$
;

-- DROP FUNCTION public.trg_init_tag_department();

CREATE OR REPLACE FUNCTION public.trg_init_tag_department()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
  DECLARE
  tag_id integer;
  site_id integer;
  site_name varchar;
  BEGIN
    select institution.id,institution.name into site_id, site_name from institution,department where institution.id = department.inst_id and department.id = NEW.id;

    insert into tag(type,name,removed) values('OR',site_name || ' ' || NEW.name,false) RETURNING id INTO tag_id;
    insert into tag_link(type,ext_id,tag_id) values('department',NEW.id,tag_id);
    RETURN NEW;
  END;
  $function$
;

-- DROP FUNCTION public.trg_init_tag_department_update();

CREATE OR REPLACE FUNCTION public.trg_init_tag_department_update()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
  DECLARE
  tag_id integer;
  site_name varchar;
  site_id integer;
  BEGIN
    select institution.id,institution.name into site_id, site_name from institution,department where institution.id = department.inst_id and department.id = NEW.id;
    update tag set removed = NEW.removed, name = site_name || ' ' || NEW.name from tag_link where tag_link.type = 'department' and tag_link.ext_id = NEW.id and tag_link.tag_id = tag.id;
    RETURN NEW;
  END;
  $function$
;

-- DROP FUNCTION public.trg_init_tag_institution();

CREATE OR REPLACE FUNCTION public.trg_init_tag_institution()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
  DECLARE
  tag_id integer;
  BEGIN
    insert into tag(type,name,removed) values('OR',NEW.name,false) RETURNING id INTO tag_id;
    insert into tag_link(type,ext_id,tag_id) values('institution',NEW.id,tag_id);
    RETURN NEW;
  END;
  $function$
;

-- DROP FUNCTION public.trg_init_tag_institution_update();

CREATE OR REPLACE FUNCTION public.trg_init_tag_institution_update()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
  DECLARE
  tag_id integer;
  BEGIN
    update tag set removed = NEW.removed, name = NEW.name from tag_link where tag_link.type = 'institution' and tag_link.ext_id = NEW.id and tag_link.tag_id = tag.id;
    RETURN NEW;
  END;
  $function$
;

-- DROP FUNCTION public.uniq(_int4);

CREATE OR REPLACE FUNCTION public.uniq(integer[])
 RETURNS integer[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/_int', $function$uniq$function$
;