
insert into sequpdate_state(updateid,uid) values('addmulticlient',3.0);



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

  select f_create_login_role_permission();

  DROP TRIGGER IF EXISTS calltrg_init_init_tag_institution_update ON public.institution;
  create trigger calltrg_init_init_tag_institution_update after
  update
      on
      public.institution for each row execute function trg_init_tag_institution_update();

  update institution set removed = removed;

  alter table staff_role add column scope_id  integer default 1;
  COMMENT ON TABLE public.staff_role IS 'Scope ID : {0 = institution}, {1 = departement}';

  create table enum_listvalue(
    scope varchar,
    value integer,
    name varchar,
    lang varchar
  );

  insert into enum_listvalue(scope,name,value,lang) values ('jobrolescope','Institution',0,'en');
  insert into enum_listvalue(scope,name,value,lang) values ('jobrolescope','Department',1,'en');


drop view v_fullinfo_department;

create or replace view v_fullinfo_department as
SELECT (institution.name::text) || coalesce(' '::text || department.name::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;

/*create or replace view v_fullinfo_department as
SELECT (institution.name::text) || coalesce(' '::text || department.name::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*/

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,
            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_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;


  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;



  create or replace view  v_deptagidbyinsttagid as
     select i.tag_id as insttagid, string_agg(d.tag_id::text,',') AS tagdepidforinsttagid from tag_link as i, tag_link as d, department
     where i.type = 'institution' and department.id = d.ext_id and d.type = 'department' and department.inst_id = i.ext_id
     group by i.tag_id;


     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[]) && tt.tagtomapinstid
     GROUP BY tt.id, tt.link_type, tt.ext_id, tt.tag_id, tt.type, tt.alltagidallow, tt.name, tt.removed;

  create or replace view v_device_historyinstallationdeptagidlist as
  select device_id,('{'||string_agg(distinct tag_id::text,',')||'}')::integer[] as deptagidlist,('{'||string_agg(distinct ext_id::text,',')||'}')::integer[] as depidlist
    from device_installation,tag_link where tag_link.type = 'department' and ext_id = dep_id
    and device_installation.removed = false
    group by device_id ;

  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;

  create extension intarray ;


alter table action add column dep_id integer default -1;

alter table audit alter column inst_id set default -1;

alter table audit alter column dep_id set default -1;

update audit set inst_id = -1 where inst_id is NULL;
update audit set dep_id = -1 where dep_id is NULL;


create or replace view v_taglink_instdep as
select inst_id,ti.tag_id as taginstid,dep_id,td.tag_id as tagdepid,v_fullinfo_department.instordepremoved from tag_link ti, tag_link td,v_fullinfo_department
where ti.type = 'institution' and td.type = 'department' and ti.ext_id = v_fullinfo_department.inst_id  and td.ext_id = v_fullinfo_department.dep_id;


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



drop view v_alert_staff_formation;
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;


create or replace view v_staffmember_taglistdep_active_staffjob as
select ('{'||coalesce(string_agg(distinct tag_link.tag_id::text,','),'')||'}')::integer[] as taglistdep_id,staff_id from staff_job,department,tag_link
where staff_job.removed = false and department.removed = false and begdate <= now() and enddate >= now()
and department.id = staff_job.dep_id and tag_link.ext_id  = department.id and tag_link.type = 'department' group by staff_id;



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



drop view v_alert_action;
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,
    dep_id as dep_id
   FROM action
  WHERE action.removed = false AND action.status_id = 3 AND action.deadline IS NOT NULL;


drop view v_alert_afcnsend;
  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 v_notremoveddocument as
select document.* 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_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;

alter table document add extern_link varchar default null;
