DROP FUNCTION IF EXISTS sp_SchetDelete(uuid);
CREATE OR REPLACE FUNCTION sp_SchetDelete
(
    "SchetID" uuid
)
RETURNS void 
LANGUAGE plpgsql
AS $function$
declare _FileID uuid;
begin
        delete from ISZL.T_DN_FACT del
    where exists
    (
        select* from ISZL.T_SCHET_SLUCH ss 
        inner join
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join
            iszl.T_SCHET_ZAP sz
            on sz.SchetZapID = ssa.SchetZap
        where ss.SchetSluchID = del.SchetSluch
        and sz.Schet = "SchetID"
    );
    
    delete from ISZL.T_DISP_DIRECT_FACT_DESTINATION del
    where exists
    (
        select* from ISZL.T_DISP_DIRECT_FACT ddf 
        inner join 
            ISZL.T_SCHET_SLUCH ss 
            on ss.SchetSluchID = ddf.SchetSluch
        inner join 
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join 
            iszl.T_SCHET_ZAP sz 
            on sz.SchetZapID = ssa.SchetZap
        where ddf.DispDirectFactID = del.DispDirectFact
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_DISP_DIRECT_FACT del
    where exists
    (
        select* from ISZL.T_SCHET_SLUCH ss 
        inner join 
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join 
            iszl.T_SCHET_ZAP sz
            on sz.SchetZapID = ssa.SchetZap
        where ss.SchetSluchID = del.SchetSluch
        and sz.Schet = "SchetID"
    );

    UPDATE iszl.t_disp_direct AS dd
    SET schetsluch = null
    FROM iszl.t_schet_sluch AS ss
    join iszl.t_schet_zap as sz on ss.schetzap = sz.schetzapid 
    where ss.schetsluchid = dd.schetsluch
    and sz.schet = "SchetID";
        
    delete from ISZL.T_SCHET_SLUCH_CONS del
    where exists
    (
        select* from ISZL.T_SCHET_SLUCH ss
        inner join 
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join 
            iszl.T_SCHET_ZAP sz 
            on sz.SchetZapID = ssa.SchetZap
        where ss.SchetSluchID = del.SchetSluch
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_SLUCH_CONS del
    where exists
    (
        select* from ISZL.T_SCHET_SLUCH ss 
        inner join 
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join 
            iszl.T_SCHET_ZAP sz 
            on sz.SchetZapID = ssa.SchetZap
        where ss.SchetSluchID = del.SchetSluch
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_SLUCH_DEST del
    where exists
    (
        select* from ISZL.T_SCHET_SLUCH ss
        inner join 
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join 
            iszl.T_SCHET_ZAP sz 
            on sz.SchetZapID = ssa.SchetZap
        where ss.SchetSluchID = del.OmsSchetSluch
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_SLUCH_DS del
    where exists
    (
        select* from ISZL.T_SCHET_SLUCH ss 
        inner join 
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join 
            iszl.T_SCHET_ZAP sz 
            on sz.SchetZapID = ssa.SchetZap
        where ss.SchetSluchID = del.SchetSluch
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_SLUCH_NAZ del
    where exists
    (
        select* from ISZL.T_SCHET_SLUCH ss 
        inner join 
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join 
            iszl.T_SCHET_ZAP sz 
            on sz.SchetZapID = ssa.SchetZap
        where ss.SchetSluchID = del.SchetSluch
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_SLUCH_ONK_DIAG del
    where exists
    (
        select* from ISZL.T_SCHET_SLUCH_ONK sso 
        inner join 
            ISZL.T_SCHET_SLUCH ss 
            on ss.SchetSluchID = sso.SchetSluch
        inner join 
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join 
            iszl.T_SCHET_ZAP sz 
            on sz.SchetZapID = ssa.SchetZap
        where sso.SchetSluchOnkID = del.SchetSluchOnk
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_SLUCH_ONK_PROT del
    where exists
    (
        select* from ISZL.T_SCHET_SLUCH_ONK sso 
        inner join 
            ISZL.T_SCHET_SLUCH ss 
            on ss.SchetSluchID = sso.SchetSluch
        inner join 
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join 
            iszl.T_SCHET_ZAP sz 
            on sz.SchetZapID = ssa.SchetZap
        where sso.SchetSluchOnkID = del.SchetSluchOnk
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_SLUCH_SANK del
    where exists
    (
        select* from iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
        inner join
            iszl.T_SCHET_ZAP sz 
            on sz.SchetZapID = ssa.SchetZap
        where ssa.SchetSluchAccomplishedID = del.SchetSluchAccomplished
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_SLUCH_SANK del
    where exists
    (
        select* from ISZL.T_SCHET_SLUCH ss 
        inner join 
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join 
            iszl.T_SCHET_ZAP sz
            on sz.SchetZapID = ssa.SchetZap
        where ss.SchetSluchID = del.SchetSluch
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_USL_NAPR del
    where exists
    (
        select* from ISZL.T_SCHET_SLUCH ss 
        inner join 
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join 
            iszl.T_SCHET_ZAP sz 
            on sz.SchetZapID = ssa.SchetZap
        where ss.SchetSluchID = del.SchetSluch
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_USL del
    where exists
    (
        select* from ISZL.T_SCHET_SLUCH ss 
        inner join 
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join 
            iszl.T_SCHET_ZAP sz 
            on sz.SchetZapID = ssa.SchetZap
        where ss.SchetSluchID = del.SchetSluch
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_USL_ONK_LEK_PR del
    where exists
    (
        select* from ISZL.T_SCHET_USL_ONK suo 
        inner join 
            ISZL.T_SCHET_SLUCH_ONK sso 
            on sso.SchetSluchOnkID = suo.SchetSluchOnk
        inner join 
            ISZL.T_SCHET_SLUCH ss 
            on ss.SchetSluchID = sso.SchetSluch
        inner join 
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join 
            iszl.T_SCHET_ZAP sz 
            on sz.SchetZapID = ssa.SchetZap
        where suo.SchetUslOnkID = del.SchetUslOnk
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_USL_ONK del
    where exists
    (
        select* from ISZL.T_SCHET_SLUCH_ONK sso 
        inner join 
            ISZL.T_SCHET_SLUCH ss 
            on ss.SchetSluchID = sso.SchetSluch
        inner join 
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join 
            iszl.T_SCHET_ZAP sz 
            on sz.SchetZapID = ssa.SchetZap
        where sso.SchetSluchOnkID = del.SchetSluchOnk
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_SLUCH_ONK del
    where exists
    (
        select* from ISZL.T_SCHET_SLUCH ss 
        inner join 
            iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
            on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
        inner join 
            iszl.T_SCHET_ZAP sz 
            on sz.SchetZapID = ssa.SchetZap
        where ss.SchetSluchID = del.SchetSluch
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_SLUCH del
    where exists
    (
        select* from iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa 
        inner join
            iszl.T_SCHET_ZAP sz
            on sz.SchetZapID = ssa.SchetZap
        where ssa.SchetSluchAccomplishedID = del.SchetSluchAccomplished
        and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_SLUCH_ACCOMPLISHED del
    where exists
    (
        select* from iszl.T_SCHET_ZAP sz where sz.SchetZapID = del.SchetZap and sz.Schet = "SchetID"
    );

    delete from ISZL.T_SCHET_ZAP del where del.Schet = "SchetID";
    
    _FileID := (select SchetFile from iszl.T_SCHET where SchetID = "SchetID" limit 1);

    delete from ISZL.T_SCHET del where del.SchetID = "SchetID";

    delete from ISZL.T_SCHET_FILE del where del.FileID = _FileID;

    delete from VClib.T_FILE del where FileId = _FileID;
end;
$function$;
