#!/usr/bin/env bash
/usr/bin/mysql --defaults-extra-file=/root/.my.cnf -D nms -e "
UPDATE sensor_jobs
SET status='error', done_at=NOW(),
    error_message=CONCAT('stale running (auto) worker=', COALESCE(worker_id,'?'))
WHERE status='running'
  AND taken_at IS NOT NULL
  AND taken_at < DATE_SUB(NOW(), INTERVAL 15 MINUTE);

DELETE FROM sensor_jobs
WHERE status IN ('done','error')
  AND done_at IS NOT NULL
  AND done_at < DATE_SUB(NOW(), INTERVAL 7 DAY);

DELETE FROM sensor_results
WHERE ts < DATE_SUB(NOW(), INTERVAL 30 DAY);
"