DROP FUNCTION if exists sp_BranchLoad(xml);
DROP FUNCTION if exists sp_BranchLoad(xml, uuid, uuid);
CREATE OR REPLACE FUNCTION sp_BranchLoad
(
    x xml default null::xml
)
RETURNS TABLE (Res boolean, Err text)
LANGUAGE plpgsql
AS $function$
declare 
    _MO varchar(6);
    _TYPE varchar(6);
    _rc int;
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('/BRANCH//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
        (xpath('//BR_CODE[1]/text()', z))[1]::varchar(16)   as BR_CODE
    ,    (xpath('//NAME[1]/text()', z))[1]::varchar(266)     as NAME
    ,    (xpath('//ADDRESS[1]/text()', z))[1]::varchar(255)    as ADDRESS
    ,    (xpath('//PHONE[1]/text()', z))[1]::varchar(255)    as PHONE
    ,    (xpath('//RUCV[1]/text()', z))[1]::varchar(255)     as RUCV
    ,    (xpath('//ACTIVE[1]/text()', z))[1]::text::int        as ACTIVE
    ,    null::uuid                                            as BranchID
    from
    (
        select unnest(xpath('/BRANCH//body', x)) as z
    ) t1;
    
    update TmpZAP zap
    set BranchID = br.BranchID
    from ISZL.T_BRANCH br 
    where br.BR_CODE = zap.BR_CODE
    and exists(select* from ISZL.T_F003 lm where lm.MCOD = _MO);
    
    Drop Table If Exists TmpErrors;
    Create Temp Table TmpErrors
    As
    select 1::int as CODE, 'Обязательное поле BR_CODE'::varchar(250) as COMMENT
    from TmpZAP zap
    where zap.BR_CODE = '' or zap.BR_CODE is null
    union all
    select 4, 'Неверный тип операции.'
    where _type not in ('Insert', 'Update', 'Delete');
    
    if _type = 'Insert'
    then
        insert into TmpErrors(CODE, COMMENT)
        select 2, 'Подразделение было добавлено ранее' 
        from TmpZAP z
        where z.BranchID is not null;
    
        -- вставка, если нет ошибок
        INSERT INTO ISZL.T_BRANCH(BranchID, BR_CODE, NAME, PHONE, ADDRESS, RUCV, ACTIVE, F003Mo)
        select gen_random_uuid(), zap.BR_CODE, zap.NAME, zap.PHONE, zap.ADDRESS, zap.RUCV
        ,zap.ACTIVE::boolean, _MO
        from TmpZAP zap
        where not exists(select* from TmpErrors);
    end if;

    if _type = 'Update'
    then
        UPDATE ISZL.T_BRANCH t
           SET NAME = zap.Name
        ,    PHONE = zap.PHONE
        ,    ADDRESS = zap.ADDRESS
        ,    RUCV = zap.RUCV
        ,    ACTIVE = case when zap.ACTIVE = 0 then 0 else 1 end
        ,    F003Mo = _MO
        from TmpZAP zap
        where t.BranchID = zap.BranchID
        and not exists(select* from TmpErrors);
    
        GET DIAGNOSTICS _rc = ROW_COUNT;
        -- вставка, если не найдена
        if _rc = 0
        then
            INSERT INTO ISZL.T_BRANCH(BranchID, BR_CODE, NAME, PHONE, ADDRESS, RUCV, ACTIVE, F003Mo)
            select gen_random_uuid(), zap.BR_CODE, zap.NAME, zap.PHONE, zap.ADDRESS, zap.RUCV
            ,zap.ACTIVE::boolean, _MO
            from TmpZAP zap
            where not exists(select* from TmpErrors);
        end if;
    end if;

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

        delete from ISZL.T_BRANCH t
        where exists(select* from TmpZAP zap where t.BranchID = zap.BranchID)
        and not exists(select* from TmpErrors);
    end if;
    
    return query
    select case when exists(select* from TmpErrors) then '0'::boolean else '1'::boolean end
    , case when exists(select* from TmpErrors) then '<?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 end;

    DROP TABLE IF EXISTS TmpZGLV;
    DROP TABLE IF EXISTS TmpZAP;
    DROP TABLE IF EXISTS TmpErrors;

end;
$function$;
