-- Migration: Update special hours cleanup cron job
-- Changes the cron job from simple DELETE to calling an edge function
-- The edge function will:
-- 1. Delete expired special hours
-- 2. Update VAPI system prompts for affected stores (removes expired hours from prompt)

-- Create trigger function that calls the edge function
CREATE OR REPLACE FUNCTION trigger_cleanup_expired_special_hours()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  internal_secret text;
  supabase_url text;
  response_status int;
BEGIN
  -- Get configuration values
  internal_secret := current_setting('app.internal_sync_secret', true);
  supabase_url := current_setting('app.supabase_url', true);

  -- Validate configuration
  IF internal_secret IS NULL OR supabase_url IS NULL THEN
    RAISE NOTICE '[Special Hours Cleanup] Missing configuration - internal_secret: %, supabase_url: %',
      internal_secret IS NOT NULL, supabase_url IS NOT NULL;
    RETURN;
  END IF;

  -- Call the edge function
  SELECT status INTO response_status
  FROM extensions.http((
    'POST',
    supabase_url || '/functions/v1/cleanup-expired-special-hours',
    ARRAY[
      extensions.http_header('Authorization', 'Bearer ' || internal_secret),
      extensions.http_header('Content-Type', 'application/json')
    ],
    'application/json',
    '{}'
  )::extensions.http_request);

  IF response_status >= 200 AND response_status < 300 THEN
    RAISE NOTICE '[Special Hours Cleanup] Edge function called successfully';
  ELSE
    RAISE WARNING '[Special Hours Cleanup] Edge function returned status: %', response_status;
  END IF;

EXCEPTION WHEN OTHERS THEN
  RAISE WARNING '[Special Hours Cleanup] Error calling edge function: %', SQLERRM;
END;
$$;

-- Update the cron job to call our new trigger function instead of direct DELETE
-- First, remove the old job
SELECT cron.unschedule('cleanup-expired-special-hours');

-- Create new job that calls the edge function
SELECT cron.schedule(
  'cleanup-expired-special-hours',
  '0 0 * * *', -- Run daily at midnight
  $$ SELECT trigger_cleanup_expired_special_hours(); $$
);

-- Add comment for documentation
COMMENT ON FUNCTION trigger_cleanup_expired_special_hours() IS
  'Triggers the cleanup-expired-special-hours edge function. Called by pg_cron daily at midnight. Deletes expired special hours and updates VAPI system prompts.';
