gistfile1.sql

start transaction;
-- step 1, log the deleted status
insert into tb_reports_statuslogs(user_id, report_id, token, reference_code, status, created)
       select r.user_id, r.id, r.token, r.reference_code, 'deleted', now()
       from tb_reports r
       where DATE_SUB(CURDATE(), INTERVAL 90 DAY) > r.created;
-- step 2, delete all related drug entries       
delete d from tb_reports_drugs d
       inner join tb_reports r on r.id = d.report_id
       where DATE_SUB(CURDATE(), INTERVAL 90 DAY) > r.created;
-- step 3, delete the actual reports
delete r from tb_reports r
       where DATE_SUB(CURDATE(), INTERVAL 90 DAY) > r.created;
commit;