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

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

INSERT INTO public.xusercfgparameter (cfgparameter_id,vtype,"type",vorder,variable,"label",description,vdefault) VALUES 
(100,4,5,10000,'SplitterConfiguration','SplitterConfiguration','SplitterConfiguration',NULL)
,(110,4,5,11000,'DataTableConfiguration','DataTableConfiguration','DataTableConfiguration',NULL)
;

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

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

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

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

INSERT INTO public.documenttypelist (id,"name") VALUES 
(0,'External document link')
,(2,'Word document ( .docx )')
,(3,'Excel document ( .xlsx )')
,(1,'PDF document ( .pdf )')
;

alter table public.staff_role add scope_id int4 default 1 null;

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;
  
  
  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 (public.institution
     LEFT JOIN public.department ON ((department.inst_id = institution.id)));
	 

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 public.tag_link ti,
    public.tag_link td,
    public.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));
  
  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 public.staff_job,
    public.department,
    public.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;
  
  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 public.audit
  WHERE ((removed = false) AND (status_id = 3) AND (date IS NOT NULL));
  
  CREATE TABLE public.task_master_link (
    id integer NOT NULL,
    task_master_id integer NOT NULL,
    staff_member_id integer NOT NULL
);

CREATE TABLE public.action_status_link (
    staff_member_id integer NOT NULL,
    document_id integer NOT NULL,
    last_opened timestamp without time zone,
    action_at timestamp without time zone,
    action_id integer,
    date timestamp without time zone,
    last_reminder timestamp without time zone
);

CREATE 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 public.device_installation,
    public.department,
    public.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));
  
  CREATE 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 public.task t,
            public.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);
  
  CREATE TABLE public.civilitylist (
    id integer NOT NULL,
    name character varying NOT NULL
);

INSERT INTO public.civilitylist VALUES (1, 'Mrs.');
INSERT INTO public.civilitylist VALUES (0, 'Mr.');
INSERT INTO public.civilitylist VALUES (2, 'Miss');
INSERT INTO public.civilitylist VALUES (3, 'Dr.');
INSERT INTO public.civilitylist VALUES (4, 'Prof.');
INSERT INTO public.civilitylist VALUES (5, 'Mx.');

CREATE or replace VIEW public.v_alert_report AS
 SELECT title,
    COALESCE(severity, 1) AS severity,
    reportdate,
    dep_id
   FROM public.report
  WHERE ((status_id = 7) AND (removed = false))
  ORDER BY COALESCE(severity, 1) DESC NULLS LAST, reportdate;
  
 alter table public.document add sourcefile varchar null;
 alter table public.document add sourcerealfilename varchar null;
 alter table public.document add extern_link varchar null;
 alter table public.document add upload_date timestamptz default now() NOT NULL;
 alter table public.document add valid_until_date timestamptz default to_timestamp('29991231235959'::text, 'YYYYMMDDHH24MISS'::text) NOT NULL;
 alter table public.document add modification_date timestamptz default now() NULL;
 
 alter table public.staff_member add photo bytea null;
 alter table public.staff_member add email varchar null;
 alter table public.staff_member add civility_id int4 null;
 
 CREATE VIEW public.v_deptagidbyinsttagid AS
 SELECT i.tag_id AS insttagid,
    string_agg((d.tag_id)::text, ','::text) AS tagdepidforinsttagid
   FROM public.tag_link i,
    public.tag_link d,
    public.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;
  
  CREATE 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 public.tag_link,
            public.tag
          WHERE (tag.id = tag_link.tag_id)) tt
     LEFT JOIN public.v_deptagidbyinsttagid v1 ON ((v1.insttagid = ANY (tt.alltagidallow))))
     LEFT JOIN public.v_deptagidbyinsttagid v2 ON ((((('{'::text || v2.tagdepidforinsttagid) || '}'::text))::integer[] && tt.tagtomapinstid)))
  GROUP BY tt.id, tt.link_type, tt.ext_id, tt.tag_id, tt.type, tt.alltagidallow, tt.name, tt.removed;
  
  CREATE 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 public.device_maintenance,
    public.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));
  
  CREATE VIEW public.v_staff_member_historyjobdeplist AS
 SELECT DISTINCT vfd.dep_id,
    staff_job.staff_id
   FROM public.staff_job,
    public.staff_role,
    public.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));
  
 CREATE 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 public.v_staff_member_historyjobdeplist,
    public.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;

 CREATE TABLE public.devicetypelist (
    id integer NOT NULL,
    name character varying
);

alter table public.device add type int4 null;
alter table public.device add photo bytea null; 
  
CREATE 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 public.device_installation,
    public.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; 
 
 
 alter table public.audit add start_date timestamptz(0) null;
 alter table public.audit add end_date timestamptz(0) null;
 
 alter table public.quaadpoint_tag add document_scope varchar default 'institution'::character varying not null;
 
 CREATE TABLE public.link_scope_view (
    scope character varying NOT NULL,
    view character varying
);

INSERT INTO public.link_scope_view VALUES ('device', 'VIEW_DEVICE');
INSERT INTO public.link_scope_view VALUES ('staff', 'VIEW_STAFF');
INSERT INTO public.link_scope_view VALUES ('staff_role', 'VIEW_STAFFROLE');
INSERT INTO public.link_scope_view VALUES ('examlist', 'VIEW_EXAMLIST');
INSERT INTO public.link_scope_view VALUES ('legaldocument', 'VIEW_LEGALDOCUMENT');
INSERT INTO public.link_scope_view VALUES ('institution', 'VIEW_INSTITUTIONDOCUMENT');
INSERT INTO public.link_scope_view VALUES ('department', 'VIEW_INSTITUTIONDOCUMENT');
INSERT INTO public.link_scope_view VALUES ('staff_member', 'VIEW_STAFF');
INSERT INTO public.link_scope_view VALUES ('staff_formation', 'VIEW_STAFF');

CREATE TABLE public.default_action_needed (
	document_type varchar NULL,
	staff_role_id int4 NULL,
	department_id int4 NULL,
	id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
	"action" int4 NOT NULL,
	CONSTRAINT default_read_needed_fk FOREIGN KEY (department_id) REFERENCES department(id),
	CONSTRAINT default_read_needed_fk_1 FOREIGN KEY (staff_role_id) REFERENCES staff_role(id)
);

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

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

CREATE TABLE public.external_link (
	id serial 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 external_src(id)
);

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

CREATE TABLE public.survey_field (
	id serial NOT NULL,
	"content" varchar NULL,
	"type" int4 NOT NULL,
	survey_id int4 NOT NULL,
	"position" int4 NOT NULL,
	ai_analysis bool NULL DEFAULT false
);

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

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

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

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

ALTER TABLE public."action" ADD dep_id int4 NULL DEFAULT '-1'::integer;
ALTER TABLE public."action" ADD "comment" varchar NULL;

ALTER TABLE public.staff_formation ADD staff_member_id int4 NULL;

CREATE OR REPLACE VIEW public.v_alert_action
AS SELECT action.description,
        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,
    action.origine AS title,
    action.dep_id
   FROM action
  WHERE action.removed = false AND action.status_id = 3 AND action.deadline IS NOT NULL;
  
  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;
  
  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;
  
  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;
  
  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;
  
  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;
  
  CREATE OR REPLACE VIEW public.v_document_departmentdocument
AS WITH t AS (
         SELECT vld.type AS document_type,
            dm.name,
            p.id AS ext_id,
            d.id AS doc_id,
            d.doc_master_id AS master_doc_id,
            d.type AS extension,
            d.creation_date AS date,
            d.revision,
            d.label AS document_label,
            dm.name AS document_name,
            d.valid_until_date,
            vlds.status_id,
            sl.status_name,
            vlds.begdate AS status_begdate,
            vlds.enddate AS status_enddate,
            p.id AS inst_id,
            '-1'::integer AS dep_id,
            '-1'::integer AS tag_ids,
            p.name AS department_fullname
           FROM department p,
            document_link vld,
            document d,
            document_master dm,
            v_last_documentstatus vlds,
            statuslist sl
          WHERE vlds.status_id <> 2 AND vlds.status_id <> 13 AND p.removed = false AND vld.type::text = 'department'::text AND p.id = vld.ext_id AND vld.doc_id = d.id AND d.id = vlds.doc_id AND d.doc_master_id = dm.id AND dm.removed = false AND vlds.status_id = sl.id AND d.removed = false
        )
 SELECT t.document_type,
    t.name,
    t.ext_id,
    t.doc_id,
    t.master_doc_id,
    t.extension,
    t.date,
    t.revision,
    t.document_label,
    t.document_name,
    t.valid_until_date,
    t.status_id,
    t.status_name,
    t.status_begdate,
    t.status_enddate,
    t.inst_id,
    '-1'::integer AS dep_id,
    tl.tag_id AS tag_ids,
    t.department_fullname
   FROM t
     LEFT JOIN tag_link tl ON t.ext_id = tl.ext_id AND tl.type::text = 'institution'::text;
	 
CREATE OR REPLACE VIEW public.v_document_device
AS SELECT vld.type AS document_type,
    ((((p.name::text || '('::text) || p.brand::text) || ' - '::text) || p.model::text) || ')'::text AS name,
    p.id AS ext_id,
    d.id AS doc_id,
    d.doc_master_id AS master_doc_id,
    d.type AS extension,
    d.creation_date AS date,
    d.revision,
    d.label AS document_label,
    dm.name AS document_name,
    d.valid_until_date,
    vlds.status_id,
    sl.status_name,
    vlds.begdate AS status_begdate,
    vlds.enddate AS status_enddate
   FROM device p,
    document_link vld,
    document d,
    document_master dm,
    v_last_documentstatus vlds,
    statuslist sl
  WHERE vlds.status_id <> 2 AND vlds.status_id <> 13 AND p.removed = false AND vld.type::text = 'device'::text AND p.id = vld.ext_id AND vld.doc_id = d.id AND d.id = vlds.doc_id AND d.doc_master_id = dm.id AND dm.removed = false AND vlds.status_id = sl.id AND d.removed = false
  ORDER BY p.name;
  
  CREATE OR REPLACE VIEW public.v_document_device_instdep
AS WITH t AS (
         SELECT device_installation.device_id,
            max(device_installation.installdate) AS lastdate
           FROM device_installation
          GROUP BY device_installation.device_id
        ), t1 AS (
         SELECT inst.name AS institution_name,
            dep.name AS department_name,
            dep.id AS dep_id,
            dep.inst_id
           FROM institution inst,
            department dep
          WHERE inst.id = dep.inst_id AND dep.removed = false
        ), t2 AS (
         SELECT di.device_id,
            di.dep_id
           FROM device_installation di,
            t
          WHERE t.device_id = di.device_id AND t.lastdate = di.installdate
        ), t3 AS (
         SELECT t2.device_id,
            t1.institution_name,
            t1.department_name,
            ((t1.institution_name::text || ' ('::text) || t1.department_name::text) || ')'::text AS department_fullname,
            t1.inst_id,
            t1.dep_id
           FROM t2
             LEFT JOIN t1 ON t1.dep_id = t2.dep_id
        ), t4 AS (
         SELECT vdd.document_type,
            vdd.name,
            vdd.ext_id,
            vdd.doc_id,
            vdd.master_doc_id,
            vdd.extension,
            vdd.date,
            vdd.revision,
            vdd.document_label,
            vdd.document_name,
            vdd.valid_until_date,
            vdd.status_id,
            vdd.status_name,
            vdd.status_begdate,
            vdd.status_enddate,
            t3.device_id,
            t3.institution_name,
            t3.department_name,
            t3.department_fullname,
            t3.inst_id,
            t3.dep_id
           FROM v_document_device vdd
             LEFT JOIN t3 ON vdd.ext_id = t3.device_id
        )
 SELECT t4.document_type,
    t4.name,
    t4.ext_id,
    t4.doc_id,
    t4.master_doc_id,
    t4.extension,
    t4.date,
    t4.revision,
    t4.document_label,
    t4.document_name,
    t4.valid_until_date,
    t4.status_id,
    t4.status_name,
    t4.status_begdate,
    t4.status_enddate,
    t4.device_id,
    t4.institution_name,
    t4.department_name,
    t4.department_fullname,
    t4.inst_id,
    t4.dep_id,
    tl.tag_id AS tag_ids
   FROM t4
     LEFT JOIN tag_link tl ON t4.dep_id = tl.ext_id AND tl.type::text = 'department'::text;
	 
	 CREATE OR REPLACE VIEW public.v_document_institutiondocument
AS WITH t AS (
         SELECT vld.type AS document_type,
            dm.name,
            p.id AS ext_id,
            d.id AS doc_id,
            d.doc_master_id AS master_doc_id,
            d.type AS extension,
            d.creation_date AS date,
            d.revision,
            d.label AS document_label,
            dm.name AS document_name,
            d.valid_until_date,
            vlds.status_id,
            sl.status_name,
            vlds.begdate AS status_begdate,
            vlds.enddate AS status_enddate,
            p.id AS inst_id,
            '-1'::integer AS dep_id,
            '-1'::integer AS tag_ids,
            p.name AS department_fullname
           FROM institution p,
            document_link vld,
            document d,
            document_master dm,
            v_last_documentstatus vlds,
            statuslist sl
          WHERE vlds.status_id <> 2 AND vlds.status_id <> 13 AND p.removed = false AND vld.type::text = 'institution'::text AND p.id = vld.ext_id AND vld.doc_id = d.id AND d.id = vlds.doc_id AND d.doc_master_id = dm.id AND dm.removed = false AND vlds.status_id = sl.id AND d.removed = false
        )
 SELECT t.document_type,
    t.name,
    t.ext_id,
    t.doc_id,
    t.master_doc_id,
    t.extension,
    t.date,
    t.revision,
    t.document_label,
    t.document_name,
    t.valid_until_date,
    t.status_id,
    t.status_name,
    t.status_begdate,
    t.status_enddate,
    t.inst_id,
    '-1'::integer AS dep_id,
    tl.tag_id AS tag_ids,
    t.department_fullname
   FROM t
     LEFT JOIN tag_link tl ON t.ext_id = tl.ext_id AND tl.type::text = 'institution'::text;
	 
	 CREATE OR REPLACE VIEW public.v_document_legaldocument
AS SELECT vld.type AS document_type,
    p.name,
    p.id AS ext_id,
    d.id AS doc_id,
    d.doc_master_id AS master_doc_id,
    d.type AS extension,
    d.creation_date AS date,
    d.revision,
    d.label AS document_label,
    dm.name AS document_name,
    d.valid_until_date,
    vlds.status_id,
    sl.status_name,
    vlds.begdate AS status_begdate,
    vlds.enddate AS status_enddate,
    '-1'::integer AS inst_id,
    '-1'::integer AS dep_id,
    '-1'::integer AS tag_ids
   FROM legaldocument p,
    document_link vld,
    document d,
    document_master dm,
    v_last_documentstatus vlds,
    statuslist sl
  WHERE vlds.status_id <> 2 AND vlds.status_id <> 13 AND p.removed = false AND vld.type::text = 'legaldocument'::text AND p.id = vld.ext_id AND vld.doc_id = d.id AND d.id = vlds.doc_id AND d.doc_master_id = dm.id AND dm.removed = false AND vlds.status_id = sl.id AND d.removed = false
  ORDER BY p.name;
  
  CREATE OR REPLACE VIEW public.v_last_document
AS SELECT DISTINCT ON (dl.ext_id) dl.id,
    dl.type,
    dl.ext_id,
    dl.doc_id
   FROM document_link dl
  ORDER BY dl.ext_id, dl.doc_id DESC;
  
  CREATE OR REPLACE VIEW public.v_document_procedure
AS SELECT vld.type AS document_type,
    p.name_fr AS name,
    dm.name AS document_name,
    p.id AS ext_id,
    d.id AS doc_id,
    d.doc_master_id AS master_doc_id,
    d.type AS extension,
    d.creation_date AS date,
    d.revision,
    d.label AS document_label,
    d.valid_until_date,
    vlds.status_id,
    sl.status_name,
    vlds.begdate AS status_begdate,
    vlds.enddate AS status_enddate
   FROM procedure p,
    v_last_document vld,
    document d,
    document_master dm,
    v_last_documentstatus vlds,
    statuslist sl
  WHERE p.removed = false AND vld.type::text = 'procedure'::text AND p.id = vld.ext_id AND vld.doc_id = d.id AND d.id = vlds.doc_id AND d.doc_master_id = dm.id AND dm.removed = false AND vlds.status_id = sl.id AND d.removed = false
  ORDER BY p.name_fr;
  
  CREATE OR REPLACE VIEW public.v_instdep_from_extid_type
AS WITH vtl1 AS (
         SELECT DISTINCT v_taglink_instdep.inst_id,
            v_taglink_instdep.taginstid AS tag_id,
            '-1'::integer AS dep_id
           FROM v_taglink_instdep
          WHERE v_taglink_instdep.instordepremoved = false
        ), vtl2 AS (
         SELECT v_taglink_instdep.inst_id,
            v_taglink_instdep.tagdepid AS tag_id,
            v_taglink_instdep.dep_id
           FROM v_taglink_instdep
          WHERE v_taglink_instdep.instordepremoved = false
        ), t AS (
         SELECT tag_link.id,
            tag_link.type,
            tag_link.ext_id,
            tag_link.tag_id
           FROM tag_link
        ), f AS (
         SELECT vtl1.inst_id,
            vtl1.dep_id,
            vtl1.tag_id,
            t.ext_id,
            t.type
           FROM vtl1,
            t
          WHERE vtl1.tag_id = t.tag_id
        UNION
         SELECT vtl2.inst_id,
            vtl2.dep_id,
            vtl2.tag_id,
            t.ext_id,
            t.type
           FROM vtl2,
            t
          WHERE vtl2.tag_id = t.tag_id
        )
 SELECT f.inst_id,
    f.dep_id,
    f.tag_id,
    f.ext_id,
    f.type,
    i.name AS institution_name,
    d.name AS department_name
   FROM f
     LEFT JOIN institution i ON f.inst_id = i.id
     LEFT JOIN department d ON f.dep_id = d.id;
	 
	 CREATE OR REPLACE VIEW public.v_instdep_fullname_from_extid_type
AS WITH r AS (
         SELECT v_instdep_from_extid_type.inst_id,
            v_instdep_from_extid_type.dep_id,
            v_instdep_from_extid_type.tag_id,
            v_instdep_from_extid_type.ext_id,
            v_instdep_from_extid_type.type,
            v_instdep_from_extid_type.institution_name,
            v_instdep_from_extid_type.department_name
           FROM v_instdep_from_extid_type
        ), r1 AS (
         SELECT r.type,
            string_agg(r.tag_id::text, ','::text) AS tag_ids,
            ((r.institution_name::text || ' ('::text) || string_agg(r.department_name::text, ','::text)) || ')'::text AS name,
            r.ext_id
           FROM r
          WHERE r.dep_id <> '-1'::integer
          GROUP BY r.type, r.institution_name, r.ext_id, r.inst_id
        UNION
         SELECT r.type,
            string_agg(r.tag_id::text, ','::text) AS tag_ids,
            r.institution_name AS name,
            r.ext_id
           FROM r
          WHERE r.dep_id = '-1'::integer
          GROUP BY r.type, r.institution_name, r.ext_id, r.inst_id
        )
 SELECT r1.type,
    r1.name,
    r1.ext_id,
    r1.tag_ids
   FROM r1;
   
   CREATE OR REPLACE VIEW public.v_document_procedure_instdep
AS SELECT v1.doc_id,
    v1.ext_id,
    v1.document_type,
    v1.name,
    v1.document_name,
    v1.document_label,
    v1.revision,
    v1.status_name,
    v1.status_id,
    v1.extension,
    v1.status_begdate,
    v2.name AS department_fullname,
    v2.tag_ids
   FROM v_document_procedure v1
     LEFT JOIN v_instdep_fullname_from_extid_type v2 ON v1.ext_id = v2.ext_id AND v1.document_type::text = v2.type::text;
	 
	 CREATE OR REPLACE VIEW public.v_document_staff_formation
AS SELECT vld.type AS document_type,
    ((((p.title::text || ' ('::text) || sm.lastname::text) || ' '::text) || sm.firstname::text) || ')'::text AS name,
    p.id AS ext_id,
    d.id AS doc_id,
    p.staff_id,
    d.doc_master_id AS master_doc_id,
    d.type AS extension,
    d.creation_date AS date,
    d.revision,
    d.label AS document_label,
    dm.name AS document_name,
    d.valid_until_date,
    vlds.status_id,
    sl.status_name,
    vlds.begdate AS status_begdate,
    vlds.enddate AS status_enddate
   FROM staff_formation p,
    document_link vld,
    document d,
    document_master dm,
    v_last_documentstatus vlds,
    statuslist sl,
    staff_member sm
  WHERE sm.id = p.staff_id AND vlds.status_id <> 2 AND vlds.status_id <> 13 AND p.removed = false AND vld.type::text = 'staff_formation'::text AND p.id = vld.ext_id AND vld.doc_id = d.id AND d.id = vlds.doc_id AND d.doc_master_id = dm.id AND dm.removed = false AND vlds.status_id = sl.id AND d.removed = false
  ORDER BY sm.lastname;
  
  CREATE OR REPLACE VIEW public.v_document_staff_formation_instdep
AS WITH t AS (
         SELECT sj.staff_id,
            sj.begdate AS lastdate
           FROM staff_job sj
          WHERE CURRENT_DATE >= sj.begdate AND CURRENT_DATE <= sj.enddate AND sj.removed = false
        ), t1 AS (
         SELECT inst.name AS institution_name,
            dep.name AS department_name,
            dep.id AS dep_id,
            dep.inst_id
           FROM institution inst,
            department dep
          WHERE inst.id = dep.inst_id AND dep.removed = false
        ), t2 AS (
         SELECT di.staff_id,
            di.dep_id
           FROM staff_job di,
            t
          WHERE t.staff_id = di.staff_id AND t.lastdate = di.begdate
        ), t3 AS (
         SELECT t2.staff_id,
            t1.institution_name,
            t1.department_name,
            ((t1.institution_name::text || ' ('::text) || t1.department_name::text) || ')'::text AS department_fullname,
            t1.inst_id,
            t1.dep_id
           FROM t2
             LEFT JOIN t1 ON t1.dep_id = t2.dep_id
        ), t4 AS (
         SELECT vdsm.document_type,
            vdsm.name,
            vdsm.ext_id,
            vdsm.doc_id,
            vdsm.master_doc_id,
            vdsm.extension,
            vdsm.date,
            vdsm.revision,
            vdsm.document_label,
            vdsm.document_name,
            vdsm.valid_until_date,
            vdsm.status_id,
            vdsm.status_name,
            vdsm.status_begdate,
            vdsm.status_enddate,
            t3.staff_id,
            t3.institution_name,
            t3.department_name,
            t3.department_fullname,
            t3.inst_id,
            t3.dep_id
           FROM v_document_staff_formation vdsm
             LEFT JOIN t3 ON vdsm.staff_id = t3.staff_id
        )
 SELECT t4.document_type,
    t4.name,
    t4.ext_id,
    t4.doc_id,
    t4.master_doc_id,
    t4.extension,
    t4.date,
    t4.revision,
    t4.document_label,
    t4.document_name,
    t4.valid_until_date,
    t4.status_id,
    t4.status_name,
    t4.status_begdate,
    t4.status_enddate,
    t4.staff_id,
    t4.institution_name,
    t4.department_name,
    t4.department_fullname,
    t4.dep_id,
    t4.inst_id,
    tl.tag_id AS tag_ids
   FROM t4
     LEFT JOIN tag_link tl ON t4.dep_id = tl.ext_id AND tl.type::text = 'department'::text;
	 
	 CREATE OR REPLACE VIEW public.v_document_staff_member
AS SELECT vld.type AS document_type,
    (p.lastname::text || ' '::text) || p.firstname::text AS name,
    p.id AS ext_id,
    d.id AS doc_id,
    d.doc_master_id AS master_doc_id,
    d.type AS extension,
    d.creation_date AS date,
    d.revision,
    d.label AS document_label,
    dm.name AS document_name,
    d.valid_until_date,
    vlds.status_id,
    sl.status_name,
    vlds.begdate AS status_begdate,
    vlds.enddate AS status_enddate
   FROM staff_member p,
    document_link vld,
    document d,
    document_master dm,
    v_last_documentstatus vlds,
    statuslist sl
  WHERE vlds.status_id <> 2 AND vlds.status_id <> 13 AND p.removed = false AND vld.type::text = 'staff_member'::text AND p.id = vld.ext_id AND vld.doc_id = d.id AND d.id = vlds.doc_id AND d.doc_master_id = dm.id AND dm.removed = false AND vlds.status_id = sl.id AND d.removed = false
  ORDER BY p.lastname;
  
  CREATE OR REPLACE VIEW public.v_document_staff_member_instdep
AS WITH t AS (
         SELECT sj.staff_id,
            sj.begdate AS lastdate
           FROM staff_job sj
          WHERE CURRENT_DATE >= sj.begdate AND CURRENT_DATE <= sj.enddate AND sj.removed = false
        ), t1 AS (
         SELECT inst.name AS institution_name,
            dep.name AS department_name,
            dep.id AS dep_id,
            dep.inst_id
           FROM institution inst,
            department dep
          WHERE inst.id = dep.inst_id AND dep.removed = false
        ), t2 AS (
         SELECT di.staff_id,
            di.dep_id
           FROM staff_job di,
            t
          WHERE t.staff_id = di.staff_id AND t.lastdate = di.begdate
        ), t3 AS (
         SELECT t2.staff_id,
            t1.institution_name,
            t1.department_name,
            ((t1.institution_name::text || ' ('::text) || t1.department_name::text) || ')'::text AS department_fullname,
            t1.inst_id,
            t1.dep_id
           FROM t2
             LEFT JOIN t1 ON t1.dep_id = t2.dep_id
        ), t4 AS (
         SELECT vdsm.document_type,
            vdsm.name,
            vdsm.ext_id,
            vdsm.doc_id,
            vdsm.master_doc_id,
            vdsm.extension,
            vdsm.date,
            vdsm.revision,
            vdsm.document_label,
            vdsm.document_name,
            vdsm.valid_until_date,
            vdsm.status_id,
            vdsm.status_name,
            vdsm.status_begdate,
            vdsm.status_enddate,
            t3.staff_id,
            t3.institution_name,
            t3.department_name,
            t3.department_fullname,
            t3.inst_id,
            t3.dep_id
           FROM v_document_staff_member vdsm
             LEFT JOIN t3 ON vdsm.ext_id = t3.staff_id
        )
 SELECT t4.document_type,
    t4.name,
    t4.ext_id,
    t4.doc_id,
    t4.master_doc_id,
    t4.extension,
    t4.date,
    t4.revision,
    t4.document_label,
    t4.document_name,
    t4.valid_until_date,
    t4.status_id,
    t4.status_name,
    t4.status_begdate,
    t4.status_enddate,
    t4.staff_id,
    t4.institution_name,
    t4.department_name,
    t4.department_fullname,
    t4.inst_id,
    t4.dep_id,
    tl.tag_id AS tag_ids
   FROM t4
     LEFT JOIN tag_link tl ON t4.dep_id = tl.ext_id AND tl.type::text = 'department'::text;
	 
	 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;
  
  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;
  
  CREATE OR REPLACE VIEW public.test
AS WITH vtl1 AS (
         SELECT DISTINCT v_taglink_instdep.inst_id,
            v_taglink_instdep.taginstid AS tag_id,
            '-1'::integer AS dep_id
           FROM v_taglink_instdep
          WHERE v_taglink_instdep.instordepremoved = false
        ), vtl2 AS (
         SELECT v_taglink_instdep.inst_id,
            v_taglink_instdep.tagdepid AS tag_id,
            v_taglink_instdep.dep_id
           FROM v_taglink_instdep
          WHERE v_taglink_instdep.instordepremoved = false
        ), t AS (
         SELECT tag_link.id,
            tag_link.type,
            tag_link.ext_id,
            tag_link.tag_id
           FROM tag_link
        ), f AS (
         SELECT vtl1.inst_id,
            vtl1.dep_id,
            vtl1.tag_id,
            t.ext_id,
            t.type
           FROM vtl1,
            t
          WHERE vtl1.tag_id = t.tag_id
        UNION
         SELECT vtl2.inst_id,
            vtl2.dep_id,
            vtl2.tag_id,
            t.ext_id,
            t.type
           FROM vtl2,
            t
          WHERE vtl2.tag_id = t.tag_id
        )
 SELECT f.inst_id,
    f.dep_id,
    f.tag_id,
    f.ext_id,
    f.type,
    i.name AS institution_name,
    d.name AS department_name
   FROM f
     LEFT JOIN institution i ON f.inst_id = i.id
     LEFT JOIN department d ON f.dep_id = d.id;
  
ALTER TABLE public.audit ADD CONSTRAINT audit_fk FOREIGN KEY (type) REFERENCES audit_type(id);

ALTER TABLE public.action_status_link ADD CONSTRAINT read_status_link_fk FOREIGN KEY (staff_member_id) REFERENCES staff_member(id)
ALTER TABLE public.action_status_link ADD CONSTRAINT read_status_link_fk_1 FOREIGN KEY (document_id) REFERENCES document(id)
ALTER TABLE public.action_status_link ADD CONSTRAINT read_status_link_pk UNIQUE (staff_member_id, document_id, action_id)

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 TRIGGER f_staffmember_equals_staff ON public.staff_formation;

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