-- Fix register_provider function to run with SECURITY DEFINER
-- This allows the function to bypass RLS when updating profile role

-- Drop the existing function
DROP FUNCTION IF EXISTS register_provider(UUID, TEXT, TEXT, TEXT[], TEXT[]);

-- Recreate with SECURITY DEFINER
CREATE OR REPLACE FUNCTION register_provider(
  p_user_id UUID,
  p_business_name TEXT,
  p_business_description TEXT,
  p_destinations TEXT[],
  p_activity_categories TEXT[]
)
RETURNS JSON
SECURITY DEFINER -- This is the key fix!
SET search_path = public
LANGUAGE plpgsql
AS $$
DECLARE
  v_profile_exists BOOLEAN;
  v_service_id UUID;
  v_wallet_id UUID;
BEGIN
  -- Check if profile exists
  SELECT EXISTS(SELECT 1 FROM profiles WHERE id = p_user_id) INTO v_profile_exists;
  
  IF NOT v_profile_exists THEN
    RAISE EXCEPTION 'Profil bulunamadı';
  END IF;

  -- Update profile role to provider (will bypass RLS due to SECURITY DEFINER)
  UPDATE profiles
  SET role = 'provider', updated_at = NOW()
  WHERE id = p_user_id;

  -- Create provider service record
  INSERT INTO provider_services (
    provider_id,
    business_name,
    business_description,
    destinations,
    activity_categories,
    created_at,
    updated_at
  ) VALUES (
    p_user_id,
    p_business_name,
    p_business_description,
    p_destinations,
    p_activity_categories,
    NOW(),
    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,
    updated_at = NOW()
  RETURNING id INTO v_service_id;

  -- Create provider wallet if doesn't exist
  INSERT INTO provider_wallets (
    provider_id,
    credit_balance,
    created_at,
    updated_at
  ) VALUES (
    p_user_id,
    0,
    NOW(),
    NOW()
  )
  ON CONFLICT (provider_id) DO NOTHING
  RETURNING id INTO v_wallet_id;

  -- If wallet already existed, get its ID
  IF v_wallet_id IS NULL THEN
    SELECT id INTO v_wallet_id FROM provider_wallets WHERE provider_id = p_user_id;
  END IF;

  RETURN json_build_object(
    'success', true,
    'provider_id', p_user_id,
    'service_id', v_service_id,
    'wallet_id', v_wallet_id
  );
EXCEPTION
  WHEN OTHERS THEN
    RAISE EXCEPTION 'Provider kaydı başarısız: %', SQLERRM;
END;
$$;

-- Grant execute permission
GRANT EXECUTE ON FUNCTION register_provider(UUID, TEXT, TEXT, TEXT[], TEXT[]) TO authenticated;
GRANT EXECUTE ON FUNCTION register_provider(UUID, TEXT, TEXT, TEXT[], TEXT[]) TO service_role;