use kylo;
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `delete_job_execution`(in id bigint(20))
BEGIN
DECLARE output VARCHAR(4000) DEFAULT '';
DECLARE job_execution_instance_id bigint(20);
DECLARE job_execution_count bigint(20);

SELECT CONCAT(' STARTING DELETE JOB ',id,' FROM JOB OPERATIONS TABLES') into output;

DELETE FROM BATCH_JOB_EXECUTION_PARAMS WHERE JOB_EXECUTION_ID =id;
SELECT CONCAT(output,'\n','DELETED ',ROW_COUNT(),' FROM  BATCH_JOB_EXECUTION_PARAMS') into output;



DELETE FROM BATCH_EXECUTION_CONTEXT_VALUES WHERE JOB_EXECUTION_ID =id;
 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
WHERE se.JOB_EXECUTION_ID = id
 );
 SELECT CONCAT(output,'\n','DELETED ',ROW_COUNT(),' FROM  BATCH_STEP_EXECUTION_CONTEXT') into output;

 DELETE FROM BATCH_JOB_EXECUTION_CONTEXT WHERE JOB_EXECUTION_ID = id;
 SELECT CONCAT(output,'\n','DELETED ',ROW_COUNT(),' FROM  BATCH_JOB_EXECUTION_CONTEXT') into output;



DELETE FROM BATCH_STEP_EXECUTION WHERE JOB_EXECUTION_ID = id;
 SELECT CONCAT(output,'\n','DELETED ',ROW_COUNT(),' FROM  BATCH_STEP_EXECUTION') into output;


SELECT COUNT(*)
INTO job_execution_count
FROM BATCH_JOB_INSTANCE WHERE
JOB_EXECUTION_ID = id;

if(job_execution_count =1) then
SELECT JOB_INSTANCE_ID
into job_execution_instance_id
FROM BATCH_JOB_EXECUTION
WHERE JOB_EXECUTION_ID = id;
end if;

DELETE FROM BATCH_JOB_EXECUTION WHERE JOB_EXECUTION_ID = id;
 SELECT CONCAT(output,'\n','DELETED ',ROW_COUNT(),' FROM  BATCH_JOB_EXECUTION') into output;

if(job_execution_count =1 ) then

DELETE FROM BATCH_JOB_INSTANCE WHERE JOB_INSTANCE_ID = job_execution_instance_id ;
SELECT CONCAT(output,'\n','DELETED ',ROW_COUNT(),' FROM  BATCH_JOB_INSTANCE') into output;
end if;

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

SELECT output;

END$$
DELIMITER ;
