CREATE OR REPLACE VIEW powerbi."GetReportRights1002_VIEW" AS 
 SELECT "FT_GetReportRights"."r_SubdivisionId" AS "SubdivisionId",
    "FT_GetReportRights"."r_UserId" AS "UserId",
    concat("FT_GetReportRights"."r_UserLogin", '@example.com') AS "UserLogin"
   FROM rights."FT_GetReportRights"(1002) "FT_GetReportRights"("r_SubdivisionId", "r_UserId", "r_UserLogin")
UNION ALL
 SELECT DISTINCT '-999'::integer AS "SubdivisionId",
    "FT_GetReportRights"."r_UserId" AS "UserId",
    concat("FT_GetReportRights"."r_UserLogin", '@example.com') AS "UserLogin"
   FROM rights."FT_GetReportRights"(1002) "FT_GetReportRights"("r_SubdivisionId", "r_UserId", "r_UserLogin");



CREATE OR REPLACE FUNCTION rights."FT_GetReportRights"(IN "par_ReportId" integer DEFAULT NULL::integer)
  RETURNS TABLE("r_SubdivisionId" integer, "r_UserId" integer, "r_UserLogin" character varying) AS
$BODY$
DECLARE
  --"par_ReportId" integer = 1002;

BEGIN


  DROP TABLE IF EXISTS "ListObject" ;
  CREATE temp TABLE IF NOT EXISTS "ListObject"(
    "idObject" INTEGER
  );

  WITH RECURSIVE "get_idObjects" AS (
    SELECT
         o.id
        ,o."ParentID"
        ,o.tag_int as "idObject_Subj"
      FROM
        rights."Objects" o
      WHERE
        o."idObjectType" = 1
    UNION
    SELECT
         o_parent.id
        ,o_parent."ParentID"
        ,o_slave."idObject_Subj"
      FROM
        "get_idObjects" as o_slave
      INNER JOIN rights."Objects" as o_parent
        ON o_parent.id = o_slave."ParentID"
        AND o_parent."idObjectType" = 2
  )
  INSERT INTO "ListObject"(
      "idObject"
    )
    SELECT
        gio.id
      FROM
        "get_idObjects" gio
      WHERE
        gio."idObject_Subj" = "par_ReportId"
  ;


  DROP TABLE IF EXISTS "FullRightsOfStructure" ;
  CREATE TEMP TABLE IF NOT EXISTS "FullRightsOfStructure"(
      "SubdivisionId" INTEGER
     ,"UserId" integer
     ,"UserLogin" VARCHAR(20)
   );

  INSERT INTO "FullRightsOfStructure"(
      "SubdivisionId"
     ,"UserId"
     ,"UserLogin"
    )
    SELECT DISTINCT 
        (CASE
          WHEN o."idObjectType" = 3 THEN o.tag_int
          --WHEN o."idObjectType" = 4 THEN ou."SubdivisionId"
          ELSE NULL::INTEGER
         END) as "SubdivisionId"
        ,ou."id"
        ,ou."Login"
      FROM
        "ListObject" lo
        JOIN rights."Rights" r ON r."idObject_Subj" = lo."idObject"
        JOIN rights."Users" u ON u.id = r."idRightsUser"
        JOIN rights."Objects" o ON o.id = r."idObject"
        JOIN dwh."OFAS_Org_User" ou ON ou.id = u."idUser_OFAS"


      union
      select
      	os.id as "SubdivisionId",
        ou.id as "UserId",
        ou."Login" as "UserLogin"
      from
      	dwh."User_See_All" as u_sall
        join dwh."OFAS_Org_User" as ou
        	on u_sall."UserLogin" = ou."Login"
        full join dwh."OFAS_Org_Structure" as os
        	on true
    ;

  DROP TABLE IF EXISTS "tt_Result";
  CREATE TEMP TABLE "tt_Result"(
      "SubdivisionId" INTEGER
     ,"UserId" integer
     ,"UserLogin" VARCHAR(20)
   );
  insert into "tt_Result" (
      "SubdivisionId"
     ,"UserId"
     ,"UserLogin"
   )
  SELECT
      coalesce(os_in."id",os.id) as "SubdivisionId"
     ,fros."UserId"
     ,fros."UserLogin"
   from
     "FullRightsOfStructure" as fros
   join dwh."OFAS_Org_Structure" as OS on os.id = fros."SubdivisionId"
   left join dwh."OFAS_Org_Structure" as OS_in on os_in."id_hierarchy" <@ os."id_hierarchy"
   												and fros."UserId" = OS."Chief" 

   group by
      coalesce(os_in."id",os.id)
     ,fros."UserId"
     ,fros."UserLogin"
  ;

  RETURN QUERY
    select
         "SubdivisionId" as "r_SubdivisionId"
        ,"UserId"        as "r_UserId"
        ,"UserLogin"     as "r_UserLogin"
      from
        "tt_Result"
  ;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;




CREATE INDEX "ix_DWH_OFAS_Org_Structure_id_hierarchy"
  ON dwh."OFAS_Org_Structure"
  USING gist
  (id_hierarchy)
  WITH (FILLFACTOR=98);