DROP FUNCTION if exists sp_FreeBedsInformationLoad(xml);
DROP FUNCTION if exists sp_FreeBedsInformationLoad(xml, uuid, uuid);
CREATE OR REPLACE FUNCTION sp_FreeBedsInformationLoad
(
    x xml default null::xml
)
RETURNS TABLE (Res boolean, Err text)
LANGUAGE plpgsql
AS $function$
declare 
    _MO varchar(6);
    _TYPE varchar(6);
begin
    Drop Table If Exists TmpZGLV;
    Create Temp Table TmpZGLV
    As
    select
        (xpath('//MO[1]/text()', z))[1]::varchar(6)     as MO
    ,    (xpath('//TYPE[1]/text()', z))[1]::varchar(6)    as TYPE
    from
    (
        select unnest(xpath('/FREE_BEDS_INFORMATION//header', x)) as z
    ) t1;
    
    select 
        into _MO, _TYPE
        t.MO, t.TYPE
    from TmpZGLV t
    limit 1;
    
    Drop Table If Exists TmpZAP;
    Create Temp Table TmpZAP
    As
    select
        to_timestamp((xpath('//ACTUAL_DATE[1]/text()', z))[1]::text, 'YYYY-MM-DD')    as ACTUAL_DATE
    ,    (xpath('//AMOUNT[1]/text()', z))[1]::text::int                                as AMOUNT
    ,    (xpath('//FEMALE_BED[1]/text()', z))[1]::text::int                            as FEMALE_BED
    ,    (xpath('//MALE_BED[1]/text()', z))[1]::text::int                            as MALE_BED
    ,    (xpath('//CHILD_BED[1]/text()', z))[1]::text::int                            as CHILD_BED    
    ,    (xpath('//BR_CODE[1]/text()', z))[1]::varchar(16)                           as BR_CODE
    ,    (xpath('//DIVISION[1]/text()', z))[1]::varchar(16)                            as DIVISION
    ,    (xpath('//V020_STRUCTURE_BED[1]/text()', z))[1]::text::int                    as V020_STRUCTURE_BED
    ,    null::uuid                                                                    as FreeBedsInformationID
    ,    null::uuid                                                                    as AmountBedID
    from
    (
        select unnest(xpath('/FREE_BEDS_INFORMATION//body', x)) as z
    ) t1;
    
    Drop Table If Exists TmpErrors;
    Create Temp Table TmpErrors
    as
    select 1::int as CODE, 'МО не найдена'::varchar(250) as COMMENT
    from TmpZGLV zglv
    where not exists(select* from iszl.t_f003 lm where lm.mcod = zglv.MO);
    
    -- поиск отделения и филиала
    update TmpZAP zap
    set FreeBedsInformationID = 
    (
        select fi.FreeBedsInformationID
        from iszl.T_FREE_BEDS_INFORMATION fi
        inner join
            iszl.T_AMOUNT_BED ab
            on ab.AmountBedID = fi.AmountBed
        inner join
            iszl.T_DIVISION div
            on div.DivisionID = ab.DivisionObj
        left join
            iszl.T_BRANCH br
            on div.Branch = br.BranchID 
        where fi.ACTUAL_DATE = zap.ACTUAL_DATE
        and ab.V020_STRUCTURE_BED = zap.V020_STRUCTURE_BED 
        and div.CODE = zap.DIVISION 
        and (div.LpuMain = _MO OR br.F003Mo = _MO)
        limit 1
    )
    ,    AmountBedID = 
    (
        select ab.AmountBedID 
        from iszl.T_AMOUNT_BED ab
        inner join
            iszl.T_DIVISION div
            on div.DivisionID = ab.DivisionObj
        left join
            iszl.T_BRANCH br
            on div.Branch = br.BranchID 
        where ab.V020_STRUCTURE_BED = zap.V020_STRUCTURE_BED
        and div.CODE = zap.DIVISION
        and (div.LpuMain = _MO OR br.F003Mo = _MO)
        limit 1
    );
    
    insert into TmpErrors(CODE, COMMENT)
    select 3, 'Профиль койки не найден'
    from TmpZAP z
    where z.AmountBedID is null
    union all
    select 4, 'Неверный тип операции.'
    where _type not in ('Insert', 'Delete', 'Update');
    
    if _type = 'Insert'
    then
        -- удаляем, если есть
        delete from ISZL.T_FREE_BEDS_INFORMATION t
        where exists(select* from TmpZAP zap where t.FreeBedsInformationID = zap.FreeBedsInformationID);

        -- вставка, если нет ошибок
        INSERT INTO ISZL.T_FREE_BEDS_INFORMATION(FreeBedsInformationID, ACTUAL_DATE, AMOUNT, FEMALE_BED
        , MALE_BED, CHILD_BED, BR_CODE, DIVISION, V020_STRUCTURE_BED, AmountBed)
        select gen_random_uuid(), zap.ACTUAL_DATE, zap.AMOUNT, zap.FEMALE_BED, zap.MALE_BED
        , zap.CHILD_BED, zap.BR_CODE, zap.DIVISION, zap.V020_STRUCTURE_BED, zap.AmountBedID
        from TmpZAP zap
        where not exists(select* from TmpErrors);
    end if;

    if _type = 'Update'
    then
        insert into TmpErrors(CODE, COMMENT)
        select 4, 'Запись не найдена в ранее загруженных списках'
        from TmpZAP zap
        where zap.FreeBedsInformationID is null;

        UPDATE ISZL.T_FREE_BEDS_INFORMATION t
           SET ACTUAL_DATE = zap.ACTUAL_DATE
        ,    AMOUNT = zap.AMOUNT
        ,    FEMALE_BED = zap.FEMALE_BED
        ,    MALE_BED = zap.MALE_BED
        ,    CHILD_BED = zap.CHILD_BED
        ,    BR_CODE = zap.BR_CODE
        ,    DIVISION = zap.DIVISION
        ,    V020_STRUCTURE_BED = zap.V020_STRUCTURE_BED
        ,    AmountBed = zap.AmountBedID
        from TmpZAP zap
        where t.FreeBedsInformationID = zap.FreeBedsInformationID
        and not exists(select* from TmpErrors);
    end if;

    if _type = 'Delete'
    then
        insert into TmpErrors(CODE, COMMENT)
        select 5, 'Запись не найдена в ранее загруженных списках'
        from TmpZAP zap
        where zap.FreeBedsInformationID is null;

        delete from ISZL.T_FREE_BEDS_INFORMATION t
        where exists(select* from TmpZAP zap where t.FreeBedsInformationID = zap.FreeBedsInformationID)
        and not exists(select* from TmpErrors);
    end if;

    return query
    select case when exists(select* from TmpErrors) then '0'::boolean else '1'::boolean end
    , '<?xml version="1.0" encoding="Windows-1251"?>
    ' || 
    (
        xmlelement
        (
            name "ROOT"
        ,    (
                select
                xmlagg
                (
                    xmlelement
                    (
                        name "ERR"
                    ,    xmlforest
                        (
                            te.CODE AS "CODE"
                        ,    te.COMMENT AS "COMMENT"
                        )
                    )
                )
                FROM TmpErrors te
            )
        )
    )::text;

    DROP TABLE if exists TmpZGLV;
    DROP TABLE if exists TmpZAP;
    DROP TABLE if exists TmpErrors;
end;
$function$;
