CREATE OR REPLACE PROCEDURE RDS.sp_DispDirectDel(FileID uuid)
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_FileID uuid;
    cnt      INTEGER;
BEGIN

    v_FileID = FileID;
    DELETE
    FROM RDS.T_DISP_DIRECT_STATUS s
        USING RDS.T_DISP_DIRECT d
            LEFT JOIN RDS.T_DISP_DIRECT_FACT ddf ON d.DispDirectID = ddf.DispDirect
    WHERE
          s.DispDirect = d.DispDirectID
      AND d.DispDirectFile = FileID
      AND ddf.DispDirectFactID IS NULL;

    DELETE
    FROM RDS.T_DISP_DIRECT_INTERVIEW i
        USING RDS.T_DISP_DIRECT d
            LEFT JOIN RDS.T_DISP_DIRECT_FACT ddf ON d.DispDirectID = ddf.DispDirect
    WHERE
          i.DispDirect = d.DispDirectID
      AND d.DispDirectFile = FileID
      AND ddf.DispDirectFactID IS NULL;

    DELETE
    FROM RDS.T_DISP_DIRECT_INFO i
        USING RDS.T_DISP_DIRECT d
            LEFT JOIN RDS.T_DISP_DIRECT_FACT ddf ON d.DispDirectID = ddf.DispDirect
    WHERE
          i.DispDirect = d.DispDirectID
      AND d.DispDirectFile = FileID
      AND ddf.DispDirectFactID IS NULL;

    DELETE
    FROM RDS.T_DISP_DIRECT_ANKETA1 a1
        USING RDS.T_DISP_DIRECT_QUESTIONING q
            JOIN RDS.T_DISP_DIRECT d ON q.DispDirect = d.DispDirectID
            LEFT JOIN RDS.T_DISP_DIRECT_FACT ddf ON d.DispDirectID = ddf.DispDirect
    WHERE
          a1.DiapDirectQuestioningID = q.DiapDirectQuestioningID
      AND d.DispDirectFile = FileID
      AND ddf.DispDirectFactID IS NULL;

    DELETE
    FROM RDS.T_DISP_DIRECT_ANKETA2 a2
        USING RDS.T_DISP_DIRECT_QUESTIONING q
            JOIN RDS.T_DISP_DIRECT d ON q.DispDirect = d.DispDirectID
            LEFT JOIN RDS.T_DISP_DIRECT_FACT ddf ON d.DispDirectID = ddf.DispDirect
    WHERE
          a2.DiapDirectQuestioningID = q.DiapDirectQuestioningID
      AND d.DispDirectFile = FileID
      AND ddf.DispDirectFactID IS NULL;

    DELETE
    FROM RDS.T_DISP_DIRECT_QUESTIONING q
        USING RDS.T_DISP_DIRECT d
            LEFT JOIN RDS.T_DISP_DIRECT_FACT ddf ON d.DispDirectID = ddf.DispDirect
    WHERE
          q.DispDirect = d.DispDirectID
      AND d.DispDirectFile = FileID
      AND ddf.DispDirectFactID IS NULL;

    DELETE
    FROM RDS.T_PATIENT_CONTACT pc
        USING RDS.T_DISP_DIRECT d
            LEFT JOIN RDS.T_DISP_DIRECT_FACT ddf ON d.DispDirectID = ddf.DispDirect
    WHERE
          pc.DispDirect = d.DispDirectID
      AND d.DispDirectFile = FileID
      AND ddf.DispDirectFactID IS NULL;

    DELETE
    FROM RDS.T_DISP_DIRECT_FACT_DESTINATION fd
        USING RDS.T_DISP_DIRECT_FACT f
            JOIN RDS.T_DISP_DIRECT d ON f.DispDirect = d.DispDirectID
            LEFT JOIN RDS.T_DISP_DIRECT_FACT ddf ON d.DispDirectID = ddf.DispDirect
    WHERE
          fd.DispDirectFact = f.DispDirectFactID
      AND d.DispDirectFile = FileID
      AND ddf.DispDirectFactID IS NULL;

    DELETE
    FROM RDS.T_DISP_DIRECT_FACT f
        USING RDS.T_DISP_DIRECT d
            LEFT JOIN RDS.T_DISP_DIRECT_FACT ddf ON d.DispDirectID = ddf.DispDirect
    WHERE
          f.DispDirect = d.DispDirectID
      AND d.DispDirectFile = FileID
      AND ddf.DispDirectFactID IS NULL;

    DELETE
    FROM RDS.T_DISP_DIRECT d
    WHERE
          NOT EXISTS (SELECT
                          1
                      FROM RDS.T_DISP_DIRECT_FACT ddf
                      WHERE
                          d.DispDirectID = ddf.DispDirect)
      AND d.DispDirectFile = FileID;

    SELECT
        COUNT(*)
    INTO cnt
    FROM RDS.T_DISP_DIRECT d
    JOIN RDS.T_DISP_DIRECT_FACT ddf ON d.DispDirectID = ddf.DispDirect
    WHERE
        d.DispDirectFile = FileID;

    IF cnt = 0 THEN
        DELETE
        FROM RDS.T_DISP_DIRECT_FILE f
        WHERE
            f.FileId = v_FileID;
    END IF;
END;
$$
;
