-- 启用 pg_cron 扩展
CREATE EXTENSION IF NOT EXISTS pg_cron WITH SCHEMA extensions;

-- 移除原来的概率性清理触发器（如果存在）
DROP TRIGGER IF EXISTS trg_cleanup_old_perf_logs ON public.perf_logs;
DROP TRIGGER IF EXISTS cleanup_old_perf_logs_trigger ON public.perf_logs;

-- 清理已存在的同名 cron 任务（幂等）
DO $$
BEGIN
  PERFORM cron.unschedule('cleanup-error-logs-daily') 
    WHERE EXISTS (SELECT 1 FROM cron.job WHERE jobname = 'cleanup-error-logs-daily');
  PERFORM cron.unschedule('cleanup-perf-logs-daily') 
    WHERE EXISTS (SELECT 1 FROM cron.job WHERE jobname = 'cleanup-perf-logs-daily');
EXCEPTION WHEN OTHERS THEN NULL;
END $$;

-- 每天 03:00 UTC 清理 7 天前的 error_logs
SELECT cron.schedule(
  'cleanup-error-logs-daily',
  '0 3 * * *',
  $$ DELETE FROM public.error_logs WHERE created_at < now() - interval '7 days'; $$
);

-- 每天 03:05 UTC 清理 7 天前的 perf_logs
SELECT cron.schedule(
  'cleanup-perf-logs-daily',
  '5 3 * * *',
  $$ DELETE FROM public.perf_logs WHERE created_at < now() - interval '7 days'; $$
);