-- Enable required extensions for cron jobs and HTTP requests
create extension if not exists pg_cron;
create extension if not exists pg_net;

-- Enable the cron extension to be used in all databases
-- This needs to be run with superuser privileges or via Supabase Dashboard
grant usage on schema cron to postgres;
grant all privileges on all tables in schema cron to postgres;

-- Function to safely schedule the cron job with environment variables
-- This approach allows for easier management and avoids hardcoding sensitive values
create or replace function schedule_sync_analysis_results_cron(
  api_url text,
  api_token text
) returns text as $$
declare
  job_exists boolean;
begin
  -- Check if job already exists
  select exists(
    select 1 from cron.job 
    where jobname = 'sync-analysis-results-every-15-minutes'
  ) into job_exists;
  
  -- If job exists, unschedule it first
  if job_exists then
    perform cron.unschedule('sync-analysis-results-every-15-minutes');
  end if;
  
  -- Schedule the new job
  perform cron.schedule(
    'sync-analysis-results-every-15-minutes',
    '*/15 * * * *', -- Every 15 minutes
    format(
      'select net.http_post(url := ''%s/api/job/sync-analysis-results'', headers := jsonb_build_object(''Content-Type'', ''application/json'', ''x-jobs-api-key'', ''%s''), body := jsonb_build_object(''triggered_by'', ''supabase_cron'', ''timestamp'', now())) as request_id;',
      api_url, api_token
    )
  );
  
  return 'Cron job scheduled successfully';
end;
$$ language plpgsql;

-- Example usage (replace with your actual values):
-- select schedule_sync_analysis_results_cron(
--   'https://your-domain.com',
--   'your-jobs-api-token-here'
-- );

-- Utility queries for managing the cron job:

-- 1. Check if the job is scheduled
-- select * from cron.job where jobname = 'sync-analysis-results-every-15-minutes';

-- 2. View job execution history
-- select * from cron.job_run_details 
-- where jobid = (select jobid from cron.job where jobname = 'sync-analysis-results-every-15-minutes')
-- order by start_time desc limit 10;

-- 3. Unschedule the job if needed
-- select cron.unschedule('sync-analysis-results-every-15-minutes');

-- 4. Check all scheduled jobs
-- select jobid, schedule, active, jobname from cron.job;