-- Enable pg_cron and pg_net extensions if not already enabled
create extension if not exists pg_cron;
create extension if not exists pg_net;

-- Create the scheduled_campaigns table
CREATE TABLE IF NOT EXISTS public.scheduled_campaigns (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    template_id UUID NOT NULL REFERENCES public.templates(id) ON DELETE CASCADE,
    target_table TEXT NOT NULL,
    start_row INTEGER NOT NULL,
    batch_count INTEGER NOT NULL,
    test_email TEXT,
    sending_mode TEXT NOT NULL,
    scheduled_at TIMESTAMPTZ NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS (we disable it for testing, but setting it up correctly is best practice)
ALTER TABLE public.scheduled_campaigns ENABLE ROW LEVEL SECURITY;

-- Create an open policy for the dashboard
CREATE POLICY "Enable all access for authenticated users" 
ON public.scheduled_campaigns FOR ALL USING (true);

-- Create the CRON job to run every minute
-- This pings the edge function which checks for pending tasks
-- Make sure to replace <YOUR_PROJECT_REF> and <YOUR_ANON_KEY> with actual values when running in production
SELECT cron.schedule(
  'process-scheduled-campaigns',
  '* * * * *',
  $$
    SELECT net.http_post(
        url:='https://<YOUR_PROJECT_REF>.supabase.co/functions/v1/process-scheduler',
        headers:='{"Content-Type": "application/json", "Authorization": "Bearer <YOUR_ANON_KEY>"}'::jsonb,
        body:='{}'::jsonb
    )
  $$
);
