379 lines
12 KiB
PL/PgSQL
379 lines
12 KiB
PL/PgSQL
-- 2. Register Provider Fix
|
||
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
|
||
SET search_path = public
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
DECLARE
|
||
v_profile_exists BOOLEAN;
|
||
v_service_id UUID;
|
||
v_wallet_id UUID;
|
||
BEGIN
|
||
IF p_user_id IS DISTINCT FROM auth.uid() THEN
|
||
RAISE EXCEPTION 'Unauthorized: cannot register provider on behalf of another user';
|
||
END IF;
|
||
|
||
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 profiles
|
||
SET role = 'provider', updated_at = NOW()
|
||
WHERE id = p_user_id;
|
||
|
||
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;
|
||
|
||
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;
|
||
|
||
RETURN json_build_object(
|
||
'success', true,
|
||
'provider_id', p_user_id,
|
||
'service_id', v_service_id
|
||
);
|
||
END;
|
||
$$;
|
||
|
||
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;
|
||
|
||
-- 3. Lead PII Masking View
|
||
CREATE OR REPLACE VIEW leads_for_providers AS
|
||
SELECT
|
||
l.id,
|
||
l.trip_id,
|
||
l.destination,
|
||
l.country,
|
||
l.start_date,
|
||
l.end_date,
|
||
l.number_of_travelers,
|
||
l.interests,
|
||
l.planned_activities,
|
||
l.base_price,
|
||
l.calculated_price,
|
||
l.final_price,
|
||
l.override_price,
|
||
l.status,
|
||
l.trigger_source,
|
||
l.created_at,
|
||
CASE
|
||
WHEN EXISTS (
|
||
SELECT 1 FROM lead_purchases lp
|
||
WHERE lp.lead_id = l.id AND lp.provider_id = auth.uid()
|
||
) THEN l.email
|
||
ELSE '***@***.***'
|
||
END AS email,
|
||
CASE
|
||
WHEN EXISTS (
|
||
SELECT 1 FROM lead_purchases lp
|
||
WHERE lp.lead_id = l.id AND lp.provider_id = auth.uid()
|
||
) THEN l.whatsapp
|
||
ELSE '+90 *** *** ****'
|
||
END AS whatsapp,
|
||
EXISTS (
|
||
SELECT 1 FROM lead_purchases lp
|
||
WHERE lp.lead_id = l.id AND lp.provider_id = auth.uid()
|
||
) AS is_purchased
|
||
FROM leads l
|
||
WHERE l.consent_given = true;
|
||
|
||
GRANT SELECT ON leads_for_providers TO authenticated;
|
||
|
||
-- 4. Audit/Rate Limit Logs Policy Fix
|
||
DROP POLICY IF EXISTS "Service role can insert audit logs" ON audit_logs;
|
||
DROP POLICY IF EXISTS "Only service_role inserts audit logs" ON audit_logs;
|
||
CREATE POLICY "Only service_role inserts audit logs"
|
||
ON audit_logs
|
||
FOR INSERT
|
||
WITH CHECK (auth.role() = 'service_role');
|
||
|
||
DROP POLICY IF EXISTS "Service role can insert rate limit logs" ON rate_limit_logs;
|
||
DROP POLICY IF EXISTS "Only service_role inserts rate limit logs" ON rate_limit_logs;
|
||
CREATE POLICY "Only service_role inserts rate limit logs"
|
||
ON rate_limit_logs
|
||
FOR INSERT
|
||
WITH CHECK (auth.role() = 'service_role');
|
||
|
||
-- 5. System Settings Keys
|
||
INSERT INTO site_settings (key, value) VALUES
|
||
('maintenance_mode', 'false'),
|
||
('registration_open', 'true'),
|
||
('email_notifications', 'true'),
|
||
('daily_reports', 'true'),
|
||
('hero_image', NULL),
|
||
('session_timeout_minutes', '30')
|
||
ON CONFLICT (key) DO NOTHING;
|
||
|
||
DROP POLICY IF EXISTS "Admins can insert site settings" ON site_settings;
|
||
CREATE POLICY "Admins can insert site settings"
|
||
ON site_settings FOR INSERT
|
||
TO authenticated
|
||
WITH CHECK (
|
||
EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin')
|
||
);
|
||
|
||
-- 6. Admin Set User Role
|
||
CREATE OR REPLACE FUNCTION admin_set_user_role(
|
||
p_target_user_id UUID,
|
||
p_new_role TEXT
|
||
)
|
||
RETURNS JSON
|
||
LANGUAGE plpgsql
|
||
SECURITY DEFINER
|
||
SET search_path = public
|
||
AS $$
|
||
DECLARE
|
||
v_old_role TEXT;
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
|
||
) THEN
|
||
RAISE EXCEPTION 'Unauthorized: admin access required';
|
||
END IF;
|
||
|
||
IF p_new_role NOT IN ('user', 'provider', 'admin') THEN
|
||
RAISE EXCEPTION 'Invalid role: must be user, provider, or admin';
|
||
END IF;
|
||
|
||
IF p_target_user_id = auth.uid() AND p_new_role != 'admin' THEN
|
||
RAISE EXCEPTION 'Cannot demote yourself from admin';
|
||
END IF;
|
||
|
||
SELECT role INTO v_old_role FROM profiles WHERE id = p_target_user_id;
|
||
|
||
UPDATE profiles
|
||
SET role = p_new_role, updated_at = NOW()
|
||
WHERE id = p_target_user_id;
|
||
|
||
RETURN json_build_object(
|
||
'success', true,
|
||
'old_role', v_old_role,
|
||
'new_role', p_new_role
|
||
);
|
||
END;
|
||
$$;
|
||
|
||
GRANT EXECUTE ON FUNCTION admin_set_user_role(UUID, TEXT) TO authenticated;
|
||
|
||
-- 7. Admin Adjust Provider Credits
|
||
CREATE OR REPLACE FUNCTION admin_adjust_provider_credits(
|
||
p_provider_id UUID,
|
||
p_amount INTEGER,
|
||
p_description TEXT
|
||
)
|
||
RETURNS JSON
|
||
LANGUAGE plpgsql
|
||
SECURITY DEFINER
|
||
SET search_path = public
|
||
AS $$
|
||
DECLARE
|
||
v_new_balance INTEGER;
|
||
v_transaction_type TEXT;
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
|
||
) THEN
|
||
RAISE EXCEPTION 'Unauthorized: admin access required';
|
||
END IF;
|
||
|
||
IF p_amount > 0 THEN
|
||
v_transaction_type := 'admin_credit';
|
||
ELSE
|
||
v_transaction_type := 'admin_debit';
|
||
END IF;
|
||
|
||
UPDATE provider_wallets
|
||
SET credit_balance = credit_balance + p_amount,
|
||
updated_at = NOW()
|
||
WHERE provider_id = p_provider_id
|
||
RETURNING credit_balance INTO v_new_balance;
|
||
|
||
INSERT INTO provider_wallet_transactions (
|
||
provider_id,
|
||
transaction_type,
|
||
amount,
|
||
description,
|
||
balance_after,
|
||
created_at
|
||
) VALUES (
|
||
p_provider_id,
|
||
v_transaction_type,
|
||
p_amount,
|
||
p_description,
|
||
v_new_balance,
|
||
NOW()
|
||
);
|
||
|
||
RETURN json_build_object(
|
||
'success', true,
|
||
'new_balance', v_new_balance
|
||
);
|
||
END;
|
||
$$;
|
||
|
||
GRANT EXECUTE ON FUNCTION admin_adjust_provider_credits(UUID, INTEGER, TEXT) TO authenticated;
|
||
|
||
-- 8. Pricing Rules
|
||
CREATE TABLE IF NOT EXISTS pricing_rules (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
rule_name TEXT NOT NULL UNIQUE,
|
||
rule_value NUMERIC NOT NULL,
|
||
description TEXT,
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
INSERT INTO pricing_rules (rule_name, rule_value, description) VALUES
|
||
('base_price', 20, 'Default base price in credits'),
|
||
('ai_multiplier', 1.75, 'AI-recommended lead price multiplier'),
|
||
('ai_min_price', 35, 'Minimum price for AI leads in credits'),
|
||
('balloon_multiplier', 2.0, 'Hot air balloon activity multiplier'),
|
||
('atv_multiplier', 1.5, 'ATV/horse riding activity multiplier'),
|
||
('guided_tour_multiplier',1.4, 'Guided tour activity multiplier')
|
||
ON CONFLICT (rule_name) DO NOTHING;
|
||
|
||
ALTER TABLE pricing_rules ENABLE ROW LEVEL SECURITY;
|
||
DROP POLICY IF EXISTS "Anyone can read pricing rules" ON pricing_rules;
|
||
CREATE POLICY "Anyone can read pricing rules"
|
||
ON pricing_rules FOR SELECT TO public USING (true);
|
||
DROP POLICY IF EXISTS "Admins can update pricing rules" ON pricing_rules;
|
||
CREATE POLICY "Admins can update pricing rules"
|
||
ON pricing_rules FOR UPDATE TO authenticated
|
||
USING (EXISTS (SELECT 1 FROM profiles WHERE id=auth.uid() AND role='admin'));
|
||
|
||
CREATE OR REPLACE FUNCTION calculate_lead_price(
|
||
p_base_price INTEGER,
|
||
p_planned_activities JSONB,
|
||
p_trigger_source TEXT DEFAULT NULL
|
||
)
|
||
RETURNS INTEGER AS $$
|
||
DECLARE
|
||
v_multiplier NUMERIC := 1.0;
|
||
v_activity JSONB;
|
||
v_activity_type TEXT;
|
||
v_activity_name TEXT;
|
||
v_final_price INTEGER;
|
||
v_base NUMERIC;
|
||
v_ai_mult NUMERIC;
|
||
v_ai_min NUMERIC;
|
||
v_balloon_mult NUMERIC;
|
||
v_atv_mult NUMERIC;
|
||
v_tour_mult NUMERIC;
|
||
BEGIN
|
||
SELECT rule_value INTO v_base FROM pricing_rules WHERE rule_name='base_price';
|
||
SELECT rule_value INTO v_ai_mult FROM pricing_rules WHERE rule_name='ai_multiplier';
|
||
SELECT rule_value INTO v_ai_min FROM pricing_rules WHERE rule_name='ai_min_price';
|
||
SELECT rule_value INTO v_balloon_mult FROM pricing_rules WHERE rule_name='balloon_multiplier';
|
||
SELECT rule_value INTO v_atv_mult FROM pricing_rules WHERE rule_name='atv_multiplier';
|
||
SELECT rule_value INTO v_tour_mult FROM pricing_rules WHERE rule_name='guided_tour_multiplier';
|
||
|
||
v_base := COALESCE(v_base, 20);
|
||
v_ai_mult := COALESCE(v_ai_mult, 1.75);
|
||
v_ai_min := COALESCE(v_ai_min, 35);
|
||
v_balloon_mult := COALESCE(v_balloon_mult, 2.0);
|
||
v_atv_mult := COALESCE(v_atv_mult, 1.5);
|
||
v_tour_mult := COALESCE(v_tour_mult, 1.4);
|
||
|
||
IF p_base_price IS NULL OR p_base_price <= 0 THEN
|
||
p_base_price := v_base::INTEGER;
|
||
END IF;
|
||
|
||
IF p_trigger_source = 'ai_route_recommendation' THEN
|
||
v_multiplier := v_multiplier * v_ai_mult;
|
||
END IF;
|
||
|
||
IF p_planned_activities IS NOT NULL AND jsonb_array_length(p_planned_activities) > 0 THEN
|
||
FOR v_activity IN SELECT * FROM jsonb_array_elements(p_planned_activities) LOOP
|
||
v_activity_type := LOWER(COALESCE(v_activity->>'type', ''));
|
||
v_activity_name := LOWER(COALESCE(v_activity->>'name', ''));
|
||
IF v_activity_type LIKE '%balloon%' OR v_activity_name LIKE '%balloon%'
|
||
OR v_activity_type LIKE '%hot air%' OR v_activity_name LIKE '%hot air%' THEN
|
||
v_multiplier := v_multiplier * v_balloon_mult;
|
||
END IF;
|
||
IF v_activity_type LIKE '%atv%' OR v_activity_name LIKE '%atv%'
|
||
OR v_activity_type LIKE '%horse%' OR v_activity_name LIKE '%horse%'
|
||
OR v_activity_type LIKE '%riding%' OR v_activity_name LIKE '%riding%' THEN
|
||
v_multiplier := v_multiplier * v_atv_mult;
|
||
END IF;
|
||
IF v_activity_type LIKE '%guided%' OR v_activity_name LIKE '%guided%'
|
||
OR v_activity_type LIKE '%tour%' OR v_activity_name LIKE '%tour%' THEN
|
||
v_multiplier := v_multiplier * v_tour_mult;
|
||
END IF;
|
||
END LOOP;
|
||
END IF;
|
||
|
||
v_final_price := ROUND(p_base_price * v_multiplier);
|
||
|
||
IF p_trigger_source = 'ai_route_recommendation'
|
||
AND v_final_price < v_ai_min::INTEGER THEN
|
||
v_final_price := v_ai_min::INTEGER;
|
||
END IF;
|
||
|
||
RETURN v_final_price;
|
||
END;
|
||
$$ LANGUAGE plpgsql STABLE;
|
||
|
||
-- 9. Admin Rate Limit Access
|
||
ALTER TABLE rate_limit_rules ENABLE ROW LEVEL SECURITY;
|
||
DROP POLICY IF EXISTS "Admins can read rate limit rules" ON rate_limit_rules;
|
||
DROP POLICY IF EXISTS "Public can read rate limit rules" ON rate_limit_rules;
|
||
DROP POLICY IF EXISTS "Admins can update rate limit rules" ON rate_limit_rules;
|
||
CREATE POLICY "Public can read rate limit rules"
|
||
ON rate_limit_rules FOR SELECT
|
||
TO public USING (true);
|
||
CREATE POLICY "Admins can update rate limit rules"
|
||
ON rate_limit_rules FOR UPDATE
|
||
TO authenticated
|
||
USING (EXISTS (SELECT 1 FROM profiles WHERE id=auth.uid() AND role='admin'));
|
||
|
||
ALTER TABLE rate_limit_logs ENABLE ROW LEVEL SECURITY;
|
||
DROP POLICY IF EXISTS "Admins can read rate limit logs" ON rate_limit_logs;
|
||
CREATE POLICY "Admins can read rate limit logs"
|
||
ON rate_limit_logs FOR SELECT
|
||
TO authenticated
|
||
USING (EXISTS (SELECT 1 FROM profiles WHERE id=auth.uid() AND role='admin'));
|
||
|
||
CREATE OR REPLACE FUNCTION admin_clear_rate_limit(p_user_id UUID)
|
||
RETURNS JSON
|
||
LANGUAGE plpgsql
|
||
SECURITY DEFINER
|
||
AS $$
|
||
DECLARE
|
||
v_deleted INTEGER;
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
|
||
) THEN
|
||
RAISE EXCEPTION 'Unauthorized';
|
||
END IF;
|
||
|
||
DELETE FROM rate_limit_logs WHERE user_id = p_user_id;
|
||
GET DIAGNOSTICS v_deleted = ROW_COUNT;
|
||
RETURN json_build_object('success', true, 'deleted', v_deleted);
|
||
END;
|
||
$$;
|
||
|
||
GRANT EXECUTE ON FUNCTION admin_clear_rate_limit(UUID) TO authenticated;
|