use kylo;
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `delete_feed_jobs`(in category varchar(255), in feed varchar(255))
BEGIN
DECLARE jobName VARCHAR(255) DEFAULT CONCAT(category,'.',feed);
DECLARE output VARCHAR(4000) DEFAULT '';
SELECT CONCAT(' STARTING DELETE FEED ',jobName,' FROM JOB OPERATIONS TABLES') into output;

DELETE FROM BATCH_JOB_EXECUTION_PARAMS WHERE JOB_EXECUTION_ID in
(SELECT je.JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION je
INNER JOIN BATCH_JOB_INSTANCE ji on ji.JOB_INSTANCE_ID = je.JOB_INSTANCE_ID
 WHERE ji.JOB_NAME = jobName
 );
SELECT CONCAT(output,'\n','DELETED ',ROW_COUNT(),' FROM  BATCH_JOB_EXECUTION_PARAMS') into output;



DELETE FROM BATCH_EXECUTION_CONTEXT_VALUES WHERE JOB_EXECUTION_ID in
(SELECT je.JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION je
INNER JOIN BATCH_JOB_INSTANCE ji on ji.JOB_INSTANCE_ID = je.JOB_INSTANCE_ID
 WHERE ji.JOB_NAME = jobName
 );
 SELECT CONCAT(output,'\n','DELETED ',ROW_COUNT(),' FROM  BATCH_EXECUTION_CONTEXT_VALUES') into output;

  DELETE FROM BATCH_STEP_EXECUTION_CONTEXT WHERE STEP_EXECUTION_ID in
(SELECT se.STEP_EXECUTION_ID FROM BATCH_STEP_EXECUTION se
INNER JOIN BATCH_JOB_EXECUTION je on je.JOB_EXECUTION_ID = se.JOB_EXECUTION_ID
INNER JOIN BATCH_JOB_INSTANCE ji on ji.JOB_INSTANCE_ID = je.JOB_INSTANCE_ID
 WHERE ji.JOB_NAME = jobName
 );
 SELECT CONCAT(output,'\n','DELETED ',ROW_COUNT(),' FROM  BATCH_STEP_EXECUTION_CONTEXT') into output;

 DELETE FROM BATCH_JOB_EXECUTION_CONTEXT WHERE JOB_EXECUTION_ID in
(SELECT je.JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION je
INNER JOIN BATCH_JOB_INSTANCE ji on ji.JOB_INSTANCE_ID = je.JOB_INSTANCE_ID
 WHERE ji.JOB_NAME = jobName
 );
 SELECT CONCAT(output,'\n','DELETED ',ROW_COUNT(),' FROM  BATCH_JOB_EXECUTION_CONTEXT') into output;



DELETE FROM BATCH_STEP_EXECUTION WHERE JOB_EXECUTION_ID in
(SELECT je.JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION je
INNER JOIN BATCH_JOB_INSTANCE ji on ji.JOB_INSTANCE_ID = je.JOB_INSTANCE_ID
 WHERE ji.JOB_NAME = jobName
 );
 SELECT CONCAT(output,'\n','DELETED ',ROW_COUNT(),' FROM  BATCH_STEP_EXECUTION') into output;


DELETE FROM BATCH_JOB_EXECUTION WHERE JOB_INSTANCE_ID IN (SELECT JOB_INSTANCE_ID from BATCH_JOB_INSTANCE WHERE JOB_NAME =jobName);
 SELECT CONCAT(output,'\n','DELETED ',ROW_COUNT(),' FROM  BATCH_JOB_EXECUTION') into output;

DELETE FROM BATCH_JOB_INSTANCE WHERE JOB_NAME = jobName ;
SELECT CONCAT(output,'\n','DELETED ',ROW_COUNT(),' FROM  BATCH_JOB_INSTANCE') into output;


SELECT CONCAT(output,'\n','FINISHED DELETING ',jobName,' FROM JOB OPERATIONS TABLES') into output;

SELECT output;

END$$
DELIMITER ;
