104 lines
3.3 KiB
PL/PgSQL
104 lines
3.3 KiB
PL/PgSQL
-- Update the calculate_lead_price function to include AI recommendation premium
|
|
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;
|
|
BEGIN
|
|
-- Base price validation
|
|
IF p_base_price IS NULL OR p_base_price <= 0 THEN
|
|
p_base_price := 20;
|
|
END IF;
|
|
|
|
-- AI Recommendation Premium: +75% (1.75x multiplier)
|
|
-- This brings base 20 credits to 35 credits minimum
|
|
IF p_trigger_source = 'ai_route_recommendation' THEN
|
|
v_multiplier := v_multiplier * 1.75;
|
|
END IF;
|
|
|
|
-- If no activities, return base price with AI multiplier
|
|
IF p_planned_activities IS NULL OR jsonb_array_length(p_planned_activities) = 0 THEN
|
|
RETURN ROUND(p_base_price * v_multiplier);
|
|
END IF;
|
|
|
|
-- Loop through activities and apply multipliers
|
|
FOR v_activity IN SELECT * FROM jsonb_array_elements(p_planned_activities)
|
|
LOOP
|
|
-- Get activity type and name
|
|
v_activity_type := LOWER(COALESCE(v_activity->>'type', ''));
|
|
v_activity_name := LOWER(COALESCE(v_activity->>'name', ''));
|
|
|
|
-- Check for Hot Air Balloon (+100% = 2x multiplier)
|
|
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 * 2.0;
|
|
END IF;
|
|
|
|
-- Check for ATV or Horse Riding (+50% = 1.5x multiplier)
|
|
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 * 1.5;
|
|
END IF;
|
|
|
|
-- Check for Guided Tour (+40% = 1.4x multiplier)
|
|
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 * 1.4;
|
|
END IF;
|
|
END LOOP;
|
|
|
|
-- Calculate final price (rounded to nearest integer)
|
|
v_final_price := ROUND(p_base_price * v_multiplier);
|
|
|
|
-- Ensure AI leads have minimum 35 credits
|
|
IF p_trigger_source = 'ai_route_recommendation' AND v_final_price < 35 THEN
|
|
v_final_price := 35;
|
|
END IF;
|
|
|
|
RETURN v_final_price;
|
|
END;
|
|
$$ LANGUAGE plpgsql IMMUTABLE;
|
|
|
|
-- Update the trigger function to pass trigger_source
|
|
CREATE OR REPLACE FUNCTION update_lead_pricing()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
-- Calculate price based on activities and trigger source
|
|
NEW.calculated_price := calculate_lead_price(
|
|
COALESCE(NEW.base_price, 20),
|
|
NEW.planned_activities,
|
|
NEW.trigger_source
|
|
);
|
|
|
|
-- Set final price (override takes precedence)
|
|
NEW.final_price := COALESCE(NEW.override_price, NEW.calculated_price);
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Update existing leads to recalculate prices with new logic
|
|
UPDATE leads
|
|
SET
|
|
calculated_price = calculate_lead_price(
|
|
COALESCE(base_price, 20),
|
|
planned_activities,
|
|
trigger_source
|
|
),
|
|
final_price = COALESCE(
|
|
override_price,
|
|
calculate_lead_price(
|
|
COALESCE(base_price, 20),
|
|
planned_activities,
|
|
trigger_source
|
|
)
|
|
)
|
|
WHERE trigger_source = 'ai_route_recommendation'; |