-- =====================================================================
--  THE QUINTILLIAN CRM  |  Daily automatic jobs
--  1) 8:00 am (Nigeria time) call reminders pushed to staff phones
--  2) 6:00 am social media analytics refresh
--
--  BEFORE RUNNING: replace the two placeholders below
--    YOUR-PROJECT-ID    -> from your Project URL
--    YOUR-CRON-SECRET   -> the same value you saved as the CRON_SECRET secret
--  Then paste into SQL Editor and click Run.
-- =====================================================================
create extension if not exists pg_cron;
create extension if not exists pg_net;

-- remove old copies if you run this again
select cron.unschedule(jobname) from cron.job where jobname in ('quintillian-call-reminders','quintillian-social-refresh');

-- 07:00 UTC = 8:00 am in Nigeria (WAT, UTC+1)
select cron.schedule('quintillian-call-reminders', '0 7 * * *', $$
  select net.http_post(
    url     := 'https://YOUR-PROJECT-ID.supabase.co/functions/v1/daily-reminders',
    headers := jsonb_build_object('Content-Type','application/json','x-cron-secret','YOUR-CRON-SECRET'),
    body    := '{}'::jsonb
  );
$$);

-- 05:00 UTC = 6:00 am in Nigeria
select cron.schedule('quintillian-social-refresh', '0 5 * * *', $$
  select net.http_post(
    url     := 'https://YOUR-PROJECT-ID.supabase.co/functions/v1/social-insights',
    headers := jsonb_build_object('Content-Type','application/json','x-cron-secret','YOUR-CRON-SECRET'),
    body    := '{}'::jsonb,
    timeout_milliseconds := 120000
  );
$$);

-- check: select jobname, schedule from cron.job;