CREATE OR REPLACE FUNCTION public.upsert_provider_service(p_service_data jsonb)
 RETURNS jsonb
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
  v_provider_id UUID;
  v_result JSONB;
BEGIN
  -- Extract provider_id
  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;

  -- Ensure provider exists in profiles and has provider/admin role
  IF NOT EXISTS (
    SELECT 1 FROM profiles 
    WHERE id = v_provider_id 
    AND role IN ('provider', 'admin')
  ) THEN
    RAISE EXCEPTION 'Invalid provider_id or insufficient permissions';
  END IF;

  -- Upsert the data
  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(p_service_data->'destinations')),
    ARRAY(SELECT jsonb_array_elements_text(p_service_data->'activity_categories')),
    ARRAY(SELECT jsonb_array_elements_text(p_service_data->'daily_tour_services')),
    ARRAY(SELECT jsonb_array_elements_text(p_service_data->'vehicle_types')),
    ARRAY(SELECT jsonb_array_elements_text(p_service_data->'languages')),
    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$;

-- Grant access to public for development
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;
