DROP FUNCTION if exists sp_DispDirectFileDel(uuid);
CREATE OR REPLACE FUNCTION sp_DispDirectFileDel
(
    "FileID" uuid
)
RETURNS void
LANGUAGE plpgsql
AS $function$
begin
    
    DELETE 
    FROM ISZL.T_DISP_DIRECT_STATUS s
    using ISZL.T_DISP_DIRECT d 
    left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
    WHERE 
    d.DispDirectFile = "FileID"
    and s.DispDirect = d.DispDirectID
    and ddf.DispDirectFactID is null;

    delete
    FROM ISZL.T_DISP_DIRECT_INTERVIEW i
    using ISZL.T_DISP_DIRECT d
    left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
    WHERE d.DispDirectFile = "FileID" 
    and i.DispDirect = d.DispDirectID
    and ddf.DispDirectFactID is null;

    DELETE 
    FROM ISZL.T_DISP_DIRECT_INFO i
    using ISZL.T_DISP_DIRECT d
    left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
    WHERE d.DispDirectFile = "FileID"
    and i.DispDirect = d.DispDirectID
    and ddf.DispDirectFactID is null;
    
    DELETE 
    FROM ISZL.T_DISP_DIRECT_ANKETA1 a1
    using ISZL.T_DISP_DIRECT_QUESTIONING q
    join ISZL.T_DISP_DIRECT d on q.DispDirect = d.DispDirectID
    left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
    WHERE d.DispDirectFile = "FileID"
    and a1.DiapDirectQuestioningID = q.DiapDirectQuestioningID
    and ddf.DispDirectFactID is null;
    
    DELETE 
    FROM ISZL.T_DISP_DIRECT_ANKETA2 a2
    using ISZL.T_DISP_DIRECT_QUESTIONING q
    join ISZL.T_DISP_DIRECT d on q.DispDirect = d.DispDirectID
    left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
    WHERE d.DispDirectFile = "FileID"
    and a2.DiapDirectQuestioningID = q.DiapDirectQuestioningID
    and ddf.DispDirectFactID is null;
    
    delete
    FROM ISZL.T_DISP_DIRECT_QUESTIONING q
    using ISZL.T_DISP_DIRECT d
    left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
    WHERE d.DispDirectFile = "FileID"
    and q.DispDirect = d.DispDirectID
    and ddf.DispDirectFactID is null;

    DELETE 
    FROM ISZL.T_PATIENT_CONTACT pc
    using ISZL.T_DISP_DIRECT d
    left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
    WHERE d.DispDirectFile = "FileID"
    and pc.DispDirect = d.DispDirectID
    and ddf.DispDirectFactID is null;
    
    delete
    FROM ISZL.T_DISP_DIRECT_FACT_DESTINATION fd
    using ISZL.T_DISP_DIRECT_FACT f
    join ISZL.T_DISP_DIRECT d on f.DispDirect = d.DispDirectID
    left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
    WHERE d.DispDirectFile = "FileID"
    and f.DispDirectFactID = fd.DispDirectFact
    and ddf.DispDirectFactID is null;

    delete
    FROM ISZL.T_DISP_DIRECT_FACT f
    using ISZL.T_DISP_DIRECT d
    left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
    WHERE d.DispDirectFile = "FileID"
    and f.DispDirect = d.DispDirectID
    and ddf.DispDirectFactID is null;

    delete
    FROM ISZL.T_DISP_DIRECT d
    using ISZL.T_DISP_DIRECT d1
    left join ISZL.T_DISP_DIRECT_FACT ddf on d1.DispDirectID = ddf.DispDirect
    WHERE DispDirectFile = "FileID"
    and d.dispdirectid  = d1.dispdirectid
    and ddf.DispDirectFactID is null;

    if (
        select count(*) FROM ISZL.T_DISP_DIRECT d 
        left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
        WHERE DispDirectFile = "FileID" and ddf.DispDirectFactID is not null
        ) = 0 then
        begin 
            DELETE 
            FROM ISZL.T_DISP_DIRECT_FILE f
            WHERE FileId = "FileID";
        end;
    end if;
end;
$function$;
