with params as(
	select
		$1::int8 as p_lpu_id,
		$2::int8 as p_lpuattachtype_id,
		$3::int8 as p_lpuregiontype_id,
		$4::int8 as p_lpuregion_id
),
query as (
    select 
        rtrim(lr.LpuRegion_Name)::bigint as lr_num,
        repeat('0', 30 - length(rtrim(lr.LpuRegion_Name))) || rtrim(lr.LpuRegion_Name) || ' ' || rtrim(lrt.LpuRegionType_Name) as str_reg,
        lpu.Lpu_Nick as Lpu_Name, --МО
        lpu.lpu_id as lpu_id,
        lr.LpuRegion_id as LpuRegion_id,
        lb.LpuBuilding_Code || ' ' || lb.LpuBuilding_Name as LpuBuilding_NameA, --подразделение
        case
          when (kls1.klstreet_name is null or address.Address_House is null) then rtrim(lu.LpuUnit_Code) || ' ' || rtrim(lu.LpuUnit_Name)
          else (rtrim(lu.LpuUnit_Code) || ' ' || rtrim(lu.LpuUnit_Name) || ' (' || COALESCE((rtrim(kls1.klstreet_name) || ', ' || address.Address_House), '') || ')')
        end as LpuUnit_Name, --группа отделений (код, наименование, адрес)
        rtrim(ls.lpusection_code) || '. ' || rtrim(ls.lpusection_name) as lpusectionA, --отделение (код, наименование)
        rtrim(lr.LpuRegion_Name) || ' ' || rtrim(lrt.LpuRegionType_Name) as lpureg, --участок
        rtrim(lrt.LpuRegionType_Name) as lrType,
        rtrim(lr.LpuRegion_Name) as lrName,
        case
          when (kls1.klstreet_name is null or address.Address_House is null) then 'отсутствует'
          else (rtrim(kls1.klstreet_name) || ', ' || address.Address_House)
        end as Lpu_Address, --адрес группы отделений
        COALESCE(case
                   when msfa.MedStaffRegion_isMain = 2 then msfa.MedPersonal_FIO
                   else null
                 end, '') as MedPersonal_FIO, --ФИО врача
        COALESCE(case
                   when msfa.MedStaffRegion_isMain = 2 then to_char(cast (msfa.MedStaffRegion_begDate as date), 'DD.MM.YYYY')
                   else null
                 end, '') as start_date,
        case
          when kla2.klarea_name is not null then rtrim(kla2.klarea_name) || ' ' || rtrim(klas.KLSocr_Nick) || ', ' || rtrim(kls2.klstreet_name) || ' ' || rtrim(klas1.KLSocr_Nick) || ', ' || COALESCE(lrs.lpuregionstreet_HouseSet, '')
          else rtrim(kls2.klstreet_name) || ' ' || rtrim(klas1.KLSocr_Nick) || ', ' || COALESCE(lrs.lpuregionstreet_HouseSet, '')
        end as Street, --адрес участка из справочника
        rtrim(kla2.klarea_name) || ' ' || rtrim(klas.KLSocr_Nick) as KLTown_Name, --город из справочника
        COALESCE(lr.LpuRegion_Descr, '') as Descr --описание
    from v_Lpu lpu
	join params on true
    join v_LpuBuilding lb 
        on lb.Lpu_id = lpu.lpu_id
    join v_LpuUnit lu 
        on lu.lpu_id = lpu.lpu_id 
        and lu.LpuBuilding_id = lb.LpuBuilding_id
    join v_lpusection ls 
        on ls.Lpu_id = lpu.lpu_id 
        and ls.LpuBuilding_id = lb.LpuBuilding_id 
        and ls.LpuUnit_id = lu.LpuUnit_id
    join v_lpusectionprofile lsp 
        on ls.lpusectionprofile_id = lsp.lpusectionprofile_id
    left join v_lpuregion lr on lr.LpuSection_id = ls.LpuSection_id
    left join v_lpuregiontype lrt on lr.lpuregiontype_id = lrt.lpuregiontype_id
    left join v_address as address on address.address_id = lu.address_id
    left join v_klstreet kls1 on kls1.klstreet_id = address.klstreet_id
    left join v_klsocr klas2 on klas2.klsocr_id = kls1.klsocr_id
    left join v_lpuregionstreet lrs on lrs.lpuregion_id = lr.lpuregion_id
    left join v_klstreet kls2 on kls2.klstreet_id = lrs.klstreet_id
    left join v_klarea kla2 on kla2.klarea_id = coalesce(lrs.kltown_id, lrs.klcity_id, address.KLCity_id)
    left join v_klsocr klas on klas.klsocr_id = kla2.klsocr_id
    left join v_klsocr klas1 on klas1.klsocr_id = kls2.klsocr_id
    left join lateral (
        select 
            msr.LpuRegion_id,
            msf.MedPersonal_FIO,
            msr.MedStaffRegion_begDate,
            msr.MedStaffRegion_isMain
        from v_MedStaffRegion msr  
        join v_MedStaffFact_er msf 
            on msr.MedStaffFact_id = msf.MedStaffFact_id
        where 1 = 1
            and msr.MedStaffRegion_isMain = 2 
            and msr.LpuRegion_id = lr.LpuRegion_id 
            and msr.MedStaffRegion_begDate <= getdate()
		order by msr.MedStaffRegion_begDate desc
		limit 1
    ) msfa on true
    where (lpu.lpu_id = p_lpu_id or (p_lpu_id is null and COALESCE(lpu.Lpu_IsTest, 1) != 2)) 
        and lr.Lpu_id = lpu.Lpu_id 
        and lr.LpuRegion_endDate is null 
        and (lr.LpuRegion_id = p_lpuregion_id or p_lpuregion_id is null)
        -- нижеследующий сатанизм необходим, так как в базе нет прямой связи типа прикрепления с типом участка, а в отчете хотят указывать только тип прикрепления
        and ((
                (p_lpuattachtype_id = 4 and p_lpuregiontype_id is null and lrt.LpuRegionType_SysNick = 'slug') 
                or (p_lpuattachtype_id = 3 and p_lpuregiontype_id is null and lrt.LpuRegionType_SysNick = 'stom') 
                or (p_lpuattachtype_id = 2 and p_lpuregiontype_id is null and lrt.LpuRegionType_SysNick = 'gin') 
                or (p_lpuattachtype_id = 1 and p_lpuregiontype_id is null and lrt.LpuRegionType_SysNick in ('ter', 'ped', 'vop', 'feld'))
            ) or ((
                    lrt.LpuRegionType_id = p_lpuregiontype_id 
                    and p_lpuregiontype_id is not null 
                    and p_lpuregiontype_id in (1, 2, 4, 12, 14, 15, 17, 27, 28) 
                    and p_lpuattachtype_id = 1
                ) 
                or (lrt.LpuRegionType_id = p_lpuregiontype_id and p_lpuregiontype_id is not null and p_lpuregiontype_id in (3, 16) and p_lpuattachtype_id = 2) 
                or (lrt.LpuRegionType_id = p_lpuregiontype_id and p_lpuregiontype_id is not null and p_lpuregiontype_id in (5, 18) and p_lpuattachtype_id = 3) 
                or (lrt.LpuRegionType_id = p_lpuregiontype_id and p_lpuregiontype_id is not null and p_lpuregiontype_id in (6, 19) and p_lpuattachtype_id = 4)
            ) or (
                p_lpuregiontype_id is null and p_lpuattachtype_id is null
            )
        ) 
        and lpu.Lpu_pid is null
),
pcount as (
    select 
        pc.LpuRegion_id,
        pc.LpuAttachType_id,
        count(pc.PersonCard_id) as q
    from v_PersonCard pc
	left join params on true
    join v_PersonState ps 
        on ps.Person_id = pc.Person_id 
    join v_LpuRegion lr 
        on lr.LpuRegion_id = pc.LpuRegion_id
    where (pc.LpuAttachType_id = p_lpuattachtype_id or p_lpuattachtype_id is null) 
        and (pc.LpuRegion_id = p_lpuregion_id or p_lpuregion_id is null) 
        and (pc.Lpu_id = p_lpu_id or p_lpu_id is null)
    group by 
        pc.LpuRegion_id,
        pc.LpuAttachType_id
),
pcount_fap as (
    select 
        pc.LpuRegion_fapid,
        pc.LpuAttachType_id,
        count(pc.PersonCard_id) as q
    from v_PersonCard pc
	left join params on true
    join v_PersonState ps 
        on ps.Person_id = pc.Person_id 
        and (ps.Lpu_id = p_lpu_id or p_lpu_id is null)
    join v_LpuRegion lrf 
        on lrf.LpuRegion_id = pc.LpuRegion_fapid
    where pc.LpuRegion_fapid is not null 
        and (pc.LpuRegion_fapid = p_lpuregion_id or p_lpuregion_id is null) 
        and (pc.Lpu_id = p_lpu_id or p_lpu_id is null)
    group by 
        pc.LpuRegion_fapid,
        pc.LpuAttachType_id
)
select distinct 
    query.lr_num,
    query.str_reg,
    query.Lpu_Name as "Lpu_Name",
    query.LpuBuilding_NameA as "LpuBuilding_Name",
    query.LpuUnit_Name as "LpuUnit",
    query.lpureg as "LpuReg1",
    query.lpusectionA as "LpuSection",
    query.Lpu_Address as "Lpu_Addr",
    query.MedPersonal_FIO as "MedPersonal",
    query.start_date as beg_date,
    query.Descr as "Descript",
    query.lrType as "Reg_Type",
    query.lrName as "Reg_Name",
    pcount.LpuAttachType_id as p_att,
    pcount_fap.LpuAttachType_id as pf_att,
    case
        when query.street is null and query.KLTown_Name is null then 'отсутствуют'
        else COALESCE(query.street, COALESCE(query.KLTown_Name, 'отсутствуют'))
    end as zone,
    COALESCE(pcount.q, 0) + COALESCE(pcount_fap.q, 0) as q
from query
left join pcount 
    on pcount.LpuRegion_id = query.LpuRegion_id
left join pcount_fap 
    on pcount_fap.LpuRegion_fapid = query.LpuRegion_id
order by lr_num asc
