101 lines
4.1 KiB
PL/PgSQL
101 lines
4.1 KiB
PL/PgSQL
-- Move hardcoded price multipliers to a configurable table
|
|
CREATE TABLE IF NOT EXISTS pricing_rules (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
rule_name TEXT NOT NULL UNIQUE, -- e.g. 'base_price', 'balloon_multiplier'
|
|
rule_value NUMERIC NOT NULL,
|
|
description TEXT,
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Seed with current hardcoded values
|
|
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;
|
|
|
|
-- RLS: anyone can read, only admin can write
|
|
ALTER TABLE pricing_rules ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY "Anyone can read pricing rules"
|
|
ON pricing_rules FOR SELECT TO public USING (true);
|
|
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'));
|
|
|
|
-- Update calculate_lead_price to read from the table
|
|
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
|
|
-- Load rules
|
|
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;
|