-- Final fix for upsert_provider_service RPC to properly handle Clerk authentication
-- This version matches the secure implementation found in the database

CREATE OR REPLACE FUNCTION public.upsert_provider_service(p_service_data jsonb)
 RETURNS jsonb
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
DECLARE
  v_provider_id UUID;
  v_clerk_id TEXT;
  v_supabase_id UUID;
  v_result JSONB;
BEGIN
  -- Extract provider_id from the input
  v_provider_id := (p_service_data->>'provider_id')::UUID;
  
  -- Basic security check: ensure provider_id is provided
  IF v_provider_id IS NULL THEN
    RAISE EXCEPTION 'provider_id is required';
  END IF;

  -- Get current user identifiers
  v_clerk_id := get_clerk_user_id();
  
  -- Use auth.uid() safely
  BEGIN
    v_supabase_id := auth.uid();
  EXCEPTION
    WHEN OTHERS THEN
      v_supabase_id := NULL;
  END;

  -- Security check: Ensure the user is authorized to update this provider's services
  -- Check if the profile exists and matches either Clerk ID or Supabase ID
  IF NOT EXISTS (
    SELECT 1 FROM profiles 
    WHERE id = v_provider_id 
    AND (
      (v_clerk_id IS NOT NULL AND clerk_user_id = v_clerk_id)
      OR 
      (v_supabase_id IS NOT NULL AND id = v_supabase_id)
    )
    AND role IN ('provider', 'admin')
  ) THEN
    RAISE EXCEPTION 'Invalid provider_id or insufficient permissions. User not authorized to update this provider service.';
  END IF;

  -- Upsert the data (RLS will be bypassed due to SECURITY DEFINER)
  INSERT INTO public.provider_services (
    provider_id,
    business_name,
    business_description,
    destinations,
    activity_categories,
    daily_tour_services,
    vehicle_types,
    languages,
    updated_at
  )
  VALUES (
    v_provider_id,
    p_service_data->>'business_name',
    p_service_data->>'business_description',
    ARRAY(SELECT jsonb_array_elements_text(COALESCE(p_service_data->'destinations', '[]'::jsonb))),
    ARRAY(SELECT jsonb_array_elements_text(COALESCE(p_service_data->'activity_categories', '[]'::jsonb))),
    ARRAY(SELECT jsonb_array_elements_text(COALESCE(p_service_data->'daily_tour_services', '[]'::jsonb))),
    ARRAY(SELECT jsonb_array_elements_text(COALESCE(p_service_data->'vehicle_types', '[]'::jsonb))),
    ARRAY(SELECT jsonb_array_elements_text(COALESCE(p_service_data->'languages', '[]'::jsonb))),
    NOW()
  )
  ON CONFLICT (provider_id) DO UPDATE
  SET
    business_name = EXCLUDED.business_name,
    business_description = EXCLUDED.business_description,
    destinations = EXCLUDED.destinations,
    activity_categories = EXCLUDED.activity_categories,
    daily_tour_services = EXCLUDED.daily_tour_services,
    vehicle_types = EXCLUDED.vehicle_types,
    languages = EXCLUDED.languages,
    updated_at = NOW()
  RETURNING to_jsonb(public.provider_services.*) INTO v_result;

  RETURN v_result;
END;
$function$;

-- Ensure proper permissions
GRANT EXECUTE ON FUNCTION public.upsert_provider_service(jsonb) TO public;
GRANT EXECUTE ON FUNCTION public.upsert_provider_service(jsonb) TO anon;
GRANT EXECUTE ON FUNCTION public.upsert_provider_service(jsonb) TO authenticated;
