delimiter ;;
drop procedure if exists deleter;;
create procedure deleter(in pMasterAccount varchar(100), in pStoreAccount varchar(100))
begin

set @vMinId:='';
set @vMaxId:='';

prepare stmt from concat("select min(idrec), max(idrec) into @vMinId, @vMaxId from ",pStoreAccount,".table1 i where i.date < DATE_SUB(CURDATE(), INTERVAL 30 DAY)");
execute stmt ;
   
while @vMinId <= @vMaxId
do
  
  prepare stmt from concat("DELETE a.*
  FROM ",pStoreAccount,".table1 i, ",pMasterAccount,".table2 a 
  WHERE i.idDat = a.obj_id
  AND a.obj_table = 'table1'
  AND i.state = 1
  AND i.date < DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND i.idrec BETWEEN ? AND ? + 1000");

  
  
  execute stmt using @vMinId, @vMinId;
  DO sleep(1);

  
  prepare stmt from concat("DELETE a.*
  FROM ",pStoreAccount,".table1 i, ",pStoreAccount,".table3 p, ",pMasterAccount,".table2 a 
  WHERE i.idDat = p.table1_idDat
  AND p.id = a.obj_id
  AND a.obj_table = 'table3'
  AND i.state = 1
  AND i.date < DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND i.idrec BETWEEN ? AND ? + 1000");

  execute stmt using @vMinId, @vMinId;
  DO sleep(1);

  
  prepare stmt from concat("DELETE la.*
  FROM ",pStoreAccount,".table1 i, ",pStoreAccount,".table3 p, ",pMasterAccount,".longtable2 la 
  WHERE i.idDat = p.table1_idDat
  AND p.id = la.obj_id
  AND la.obj_table = 'table3'
  AND i.state = 1
  AND i.date < DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND i.idrec BETWEEN ? AND ? + 1000");

  execute stmt using @vMinId, @vMinId;
  DO sleep(1);

  
  prepare stmt from concat("DELETE p.*
  FROM ",pStoreAccount,".table1 i, ",pStoreAccount,".table3 p
  WHERE i.idDat = p.table1_idDat
  AND i.state = 1
  AND i.date < DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND i.idrec BETWEEN ? AND ? + 1000");

  execute stmt using @vMinId, @vMinId;
  DO sleep(1);

  
  prepare stmt from concat("DELETE ir.*
  FROM ",pStoreAccount,".table1 i, ",pStoreAccount,".table7 ir
  WHERE i.idDat = ir.reference_id
  AND ir.reference_type = 1
  AND i.state = 1
  AND i.date < DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND i.idrec BETWEEN ? AND ? + 1000");

  execute stmt using @vMinId, @vMinId;
  DO sleep(1);

  
  prepare stmt from concat("DELETE pu.*
  FROM ",pStoreAccount,".table1 i, ",pStoreAccount,".table4 ir, ",pStoreAccount,".table8 pu
  WHERE i.idRec = ir.table1_idRec
  AND ir.id = pu.customfield1
  AND i.date < DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND i.idrec BETWEEN ? AND ? + 1000");

  execute stmt using @vMinId, @vMinId;
  DO sleep(1);

  
  prepare stmt from concat("DELETE ir.*
  FROM ",pStoreAccount,".table1 i, ",pStoreAccount,".table4 ir
  WHERE i.idRec = ir.table1_idRec
  AND i.date < DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND i.idrec BETWEEN ? AND ? + 1000");

  execute stmt using @vMinId, @vMinId;
  DO sleep(1);
  
  
  prepare stmt from concat("DELETE i.*
  FROM ",pStoreAccount,".table1 i
  WHERE i.date < DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND i.idrec BETWEEN ? AND ? + 1000");

  execute stmt using @vMinId, @vMinId;
  DO sleep(1);

  set @vMinId := @vMinId + 1001;

end while;

prepare stmt from concat("select min(id), max(id) into @vMinId, @vMaxId from ",pStoreAccount,".table5 WHERE `timestamp` < UNIX_TIMESTAMP() - 31 * 24 * 3600");
execute stmt ;

while @vMinId <= @vMaxId
do
  
  prepare stmt from concat("DELETE r.*
  FROM ",pStoreAccount,".table5 r
  WHERE `timestamp` < UNIX_TIMESTAMP() - 31 * 24 * 3600
    AND id BETWEEN ? AND ? + 1000");

  execute stmt using @vMinId, @vMinId;
  DO sleep(1);

  set @vMinId := @vMinId + 1001;

end while;

prepare stmt from concat("select min(id), max(id) into @vMinId, @vMaxId from ",pStoreAccount,".table6 WHERE `datetime` < DATE_SUB(NOW(), INTERVAL 30 DAY)");
execute stmt ;

while @vMinId <= @vMaxId
do
  
  prepare stmt from concat("DELETE c.*
  FROM ",pStoreAccount,".table6 c
  WHERE `datetime` < DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND id BETWEEN ? AND ? + 1000");

  execute stmt using @vMinId, @vMinId;
  DO sleep(1);

  set @vMinId := @vMinId + 1001;

end while;

deallocate prepare stmt;

end;
;;
delimiter ;

call deleter('schema1','schema2');