-- 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';