create or replace view v_fullinfo_department as
select institution.name || ' ' || department.name as fullname,institution.id as inst_id, institution.name as inst_name,department.id as dep_id,department.name as dep_name
from institution,department where institution.id = department.inst_id;

create index laststatus_per_document_index on documentstatus (doc_id, begdate desc);
create or replace view v_last_documentstatus as
SELECT DISTINCT ON (documentstatus.doc_id) * FROM documentstatus ORDER by doc_id,begdate desc;

insert into institution(id,name, removed) values (-1,'Undef',true);
insert into department(id,inst_id,name, removed) values (-1,-1,'Undef',true);
insert into staff_role(id,name, removed) values (-1,'Undef',true);
insert into login_role(id,name) values (0,'admin');
insert into login_role(name, removed) values ('User',false);
insert into staff_member(id,lastname,firstname,removed,login,password,login_role_id,active) values (0,'admin','admin',false,'admin',encode('admin','base64'),0,true);

insert into statuslist(id,status_name,type) values (-1,'','|device_inspection_result|device|');
insert into statuslist(status_name,type) values ('Actif','|device|');
insert into statuslist(status_name,type) values ('Obsolete','|device|');
insert into statuslist(status_name,type) values ('Planned','|device_inspection|staff_formation|action|audit|');
insert into statuslist(status_name,type) values ('Completed','|device_inspection|staff_formation|action|audit|');
insert into statuslist(status_name,type) values ('OK','|device_inspection_result|');
insert into statuslist(status_name,type) values ('KO','|device_inspection_result|');
insert into statuslist(status_name,type) values ('Open','|report|');
insert into statuslist(status_name,type) values ('Closed','|report|');

insert into procedure(name_en,name_fr,name_nl) values ('Document management','Gestion documentaire','Documentbeheer');
insert into procedure(name_en,name_fr,name_nl) values ('Installation and equipment','Installation et matériel','Installatie en uitrusting');
insert into procedure(name_en,name_fr,name_nl) values ('Exams','Examens','Examens');
insert into procedure(name_en,name_fr,name_nl) values ('Dosimetry','Dosimétrie','Dosimetrie');
insert into procedure(name_en,name_fr,name_nl) values ('Human ressources','Ressources humaines','Human resources');
insert into procedure(name_en,name_fr,name_nl) values ('Radiation protection','Radioprotection','Stralingsbescherming');
insert into procedure(name_en,name_fr,name_nl) values ('Incidents','Incidents','incidenten');
insert into procedure(name_en,name_fr,name_nl) values ('Audit','Audit','Audit');

  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,'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();

  CREATE OR REPLACE FUNCTION public.trg_init_login_role_permission()
    RETURNS trigger
    LANGUAGE plpgsql AS
  $BODY$
  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;
  $BODY$;

  CREATE TRIGGER calltrg_init_login_role_permission
  AFTER INSERT
  ON login_role
  FOR EACH ROW
  EXECUTE PROCEDURE trg_init_login_role_permission();


  CREATE OR REPLACE FUNCTION public.trg_init_tag_institution()
    RETURNS trigger
    LANGUAGE plpgsql AS
  $BODY$
  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;
  $BODY$;

  CREATE TRIGGER calltrg_init_init_tag_institution
  AFTER INSERT
  ON institution
  FOR EACH ROW
  EXECUTE PROCEDURE trg_init_tag_institution();

  CREATE OR REPLACE FUNCTION public.trg_init_tag_institution_update()
    RETURNS trigger
    LANGUAGE plpgsql AS
  $BODY$
  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;
  $BODY$;

  CREATE OR REPLACE FUNCTION public.trg_init_tag_department()
    RETURNS trigger
    LANGUAGE plpgsql AS
  $BODY$
  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;
  $BODY$;

  CREATE TRIGGER calltrg_init_init_tag_department
  AFTER INSERT
  ON department
  FOR EACH ROW
  EXECUTE PROCEDURE trg_init_tag_department();

  CREATE OR REPLACE FUNCTION public.trg_init_tag_department_update()
    RETURNS trigger
    LANGUAGE plpgsql AS
  $BODY$
  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;
  $BODY$;

  CREATE TRIGGER calltrg_init_init_tag_department_update
  AFTER UPDATE
  ON department
  FOR EACH ROW
  EXECUTE PROCEDURE trg_init_tag_department_update();

  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,''::varchar 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 || ' ' || staff_member.firstname 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,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,
          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
     FROM action
    WHERE action.removed = false and action.status_id = 3 AND action.deadline IS NOT NULL
   ;
 ALTER TABLE public.v_alert
   OWNER TO postgres;

   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
        FROM action
       WHERE action.removed = false and action.status_id = 3 AND action.deadline IS NOT null;
      ALTER TABLE public.v_alert_action
       OWNER TO postgres;

       CREATE OR REPLACE VIEW public.v_alert_staff_formation AS
       SELECT
          staff_member.lastname || ' ' || staff_member.firstname 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,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;
       ALTER TABLE public.v_alert_staff_formation
        OWNER TO postgres;

      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,''::varchar as title
        FROM device_inspection,
         device
       WHERE 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;
     ALTER TABLE public.v_alert_device_inspection
       OWNER TO postgres;

       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 as title
         FROM audit
        WHERE audit.removed = false and audit.status_id = 3 AND audit.date IS NOT null;
      ALTER TABLE public.v_alert_audit
        OWNER TO postgres;

      create or replace view v_alert_report as
      select title,coalesce(severity,1) as severity,reportdate from report  where status_id = 7 and removed = false order by severity desc nulls last,reportdate;
      ALTER TABLE public.v_alert_report
        OWNER TO postgres;


        CREATE OR REPLACE FUNCTION public.f_tag_link(in_exam_id integer)
 RETURNS varchar
 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$
;
