100 lines
2.4 KiB
PL/PgSQL
100 lines
2.4 KiB
PL/PgSQL
-- Function to register a new provider with business information
|
||
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 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
|
||
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;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- Grant execute permission
|
||
GRANT EXECUTE ON FUNCTION register_provider(UUID, TEXT, TEXT, TEXT[], TEXT[]) TO authenticated;
|
||
|
||
-- Function to check if user is a provider
|
||
CREATE OR REPLACE FUNCTION is_provider(p_user_id UUID)
|
||
RETURNS BOOLEAN AS $$
|
||
BEGIN
|
||
RETURN EXISTS(
|
||
SELECT 1 FROM profiles
|
||
WHERE id = p_user_id AND role = 'provider'
|
||
);
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
GRANT EXECUTE ON FUNCTION is_provider(UUID) TO authenticated; |