CREATE or replace FUNCTION delete_feed_jobs(in category varchar(255), in feed varchar(255))
RETURNS integer AS $$

DECLARE jobName TEXT;

BEGIN

jobName := CONCAT(category,'.',feed);

-- Delete NiFi jobs and steps (BATCH_NIFI_JOB, BATCH_NIFI_STEP)
DELETE FROM BATCH_NIFI_STEP
  USING BATCH_STEP_EXECUTION, BATCH_JOB_EXECUTION, BATCH_JOB_INSTANCE
  WHERE BATCH_NIFI_STEP.STEP_EXECUTION_ID = BATCH_STEP_EXECUTION.STEP_EXECUTION_ID
  AND BATCH_STEP_EXECUTION.JOB_EXECUTION_ID = BATCH_JOB_EXECUTION.JOB_EXECUTION_ID
  AND BATCH_JOB_INSTANCE.JOB_INSTANCE_ID = BATCH_JOB_EXECUTION.JOB_INSTANCE_ID
  AND BATCH_JOB_INSTANCE.JOB_NAME = jobName;

DELETE FROM BATCH_NIFI_JOB
 USING BATCH_JOB_EXECUTION, BATCH_JOB_INSTANCE
 WHERE BATCH_NIFI_JOB.JOB_EXECUTION_ID = BATCH_JOB_EXECUTION.JOB_EXECUTION_ID
 AND BATCH_JOB_EXECUTION.JOB_INSTANCE_ID = BATCH_JOB_INSTANCE.JOB_INSTANCE_ID
 AND BATCH_JOB_INSTANCE.JOB_NAME = jobName;

-- Delete step execution context (BATCH_EXECUTION_CONTEXT_VALUES, BATCH_STEP_EXECUTION_CONTEXT, BATCH_STEP_EXECUTION_CTX_VALS)
DELETE FROM BATCH_EXECUTION_CONTEXT_VALUES
 USING BATCH_STEP_EXECUTION, BATCH_JOB_EXECUTION, BATCH_JOB_INSTANCE
 WHERE BATCH_EXECUTION_CONTEXT_VALUES.STEP_EXECUTION_ID = BATCH_STEP_EXECUTION.STEP_EXECUTION_ID
 AND BATCH_STEP_EXECUTION.JOB_EXECUTION_ID = BATCH_JOB_EXECUTION.JOB_EXECUTION_ID
 AND BATCH_JOB_EXECUTION.JOB_INSTANCE_ID = BATCH_JOB_INSTANCE.JOB_INSTANCE_ID
 AND BATCH_JOB_INSTANCE.JOB_NAME = jobName;

DELETE FROM BATCH_STEP_EXECUTION_CTX_VALS
 USING BATCH_STEP_EXECUTION, BATCH_JOB_EXECUTION, BATCH_JOB_INSTANCE
 WHERE BATCH_STEP_EXECUTION_CTX_VALS.STEP_EXECUTION_ID = BATCH_STEP_EXECUTION.STEP_EXECUTION_ID
 AND BATCH_STEP_EXECUTION.JOB_EXECUTION_ID = BATCH_JOB_EXECUTION.JOB_EXECUTION_ID
 AND BATCH_JOB_EXECUTION.JOB_INSTANCE_ID = BATCH_JOB_INSTANCE.JOB_INSTANCE_ID
 AND BATCH_JOB_INSTANCE.JOB_NAME = jobName;


 -- Delete step executions (BATCH_STEP_EXECUTION)
DELETE FROM BATCH_STEP_EXECUTION
 USING BATCH_JOB_EXECUTION, BATCH_JOB_INSTANCE
 WHERE BATCH_STEP_EXECUTION.JOB_EXECUTION_ID = BATCH_JOB_EXECUTION.JOB_EXECUTION_ID
 AND BATCH_JOB_EXECUTION.JOB_INSTANCE_ID = BATCH_JOB_INSTANCE.JOB_INSTANCE_ID
 AND BATCH_JOB_INSTANCE.JOB_NAME = jobName;

-- Delete job execution context (BATCH_JOB_EXECUTION_CONTEXT, BATCH_JOB_EXECUTION_CTX_VALS)
DELETE FROM BATCH_JOB_EXECUTION_CTX_VALS
 USING BATCH_JOB_EXECUTION, BATCH_JOB_INSTANCE
 WHERE BATCH_JOB_EXECUTION_CTX_VALS.JOB_EXECUTION_ID = BATCH_JOB_EXECUTION.JOB_EXECUTION_ID
 AND BATCH_JOB_EXECUTION.JOB_INSTANCE_ID = BATCH_JOB_INSTANCE.JOB_INSTANCE_ID
 AND BATCH_JOB_INSTANCE.JOB_NAME = jobName;


-- Delete job executions (BATCH_JOB_EXECUTION, BATCH_JOB_EXECUTION_PARAMS)
DELETE FROM BATCH_JOB_EXECUTION_PARAMS
 USING BATCH_JOB_EXECUTION, BATCH_JOB_INSTANCE
 WHERE BATCH_JOB_EXECUTION_PARAMS.JOB_EXECUTION_ID = BATCH_JOB_EXECUTION.JOB_EXECUTION_ID
 AND BATCH_JOB_EXECUTION.JOB_INSTANCE_ID = BATCH_JOB_INSTANCE.JOB_INSTANCE_ID
 AND BATCH_JOB_INSTANCE.JOB_NAME = jobName;

DELETE FROM BATCH_JOB_EXECUTION
 USING BATCH_JOB_INSTANCE
 WHERE BATCH_JOB_EXECUTION.JOB_INSTANCE_ID = BATCH_JOB_INSTANCE.JOB_INSTANCE_ID
 AND BATCH_JOB_INSTANCE.JOB_NAME = jobName;

-- Delete job instance (BATCH_JOB_INSTANCE)
DELETE FROM BATCH_JOB_INSTANCE
WHERE BATCH_JOB_INSTANCE.JOB_NAME = jobName;

DELETE FROM NIFI_RELATED_ROOT_FLOW_FILES x
WHERE x.FLOW_FILE_ID in (SELECT NIFI_EVENT.FLOW_FILE_ID
FROM NIFI_EVENT
WHERE NIFI_EVENT.FM_FEED_NAME = jobName);

DELETE FROM NIFI_RELATED_ROOT_FLOW_FILES x
WHERE x.EVENT_FLOW_FILE_ID in (SELECT NIFI_EVENT.FLOW_FILE_ID
FROM NIFI_EVENT
WHERE NIFI_EVENT.FM_FEED_NAME = jobName);

DELETE FROM NIFI_EVENT
WHERE FM_FEED_NAME = jobName;

DELETE FROM NIFI_FEED_PROCESSOR_STATS
WHERE FM_FEED_NAME = jobName;

 --   need to return a value for this procedure calls to work with spring-data-jpa repositories and named queries
return 1;

END;
$$ LANGUAGE plpgsql;
