-- Persona-based Lead Pricing Multiplier
-- Bu migration, lead fiyatlandırmasına persona bazlı çarpan ekler

-- 1. Persona bazlı fiyat çarpanı fonksiyonu
CREATE OR REPLACE FUNCTION apply_persona_multiplier(
  p_price INTEGER,
  p_tourist_persona JSONB
) RETURNS INTEGER AS $$
DECLARE
  v_spend_potential TEXT;
  v_multiplier NUMERIC := 1.0;
BEGIN
  -- Eğer persona bilgisi yoksa, orijinal fiyatı döndür
  IF p_tourist_persona IS NULL THEN
    RETURN p_price;
  END IF;
  
  -- Persona'nın harcama potansiyelini al
  v_spend_potential := p_tourist_persona->>'spend_potential';
  
  -- Harcama potansiyeline göre çarpan belirle
  v_multiplier := CASE v_spend_potential
    WHEN 'very_high' THEN 1.5   -- Romantic couple, luxury: 1.5x
    WHEN 'high'      THEN 1.3   -- Content creator, group: 1.3x
    WHEN 'medium'    THEN 1.0   -- Family, solo: normal
    WHEN 'low'       THEN 0.8   -- Budget backpacker: 0.8x
    ELSE 1.0
  END;
  
  -- Çarpanı uygula ve yuvarla
  RETURN ROUND(p_price * v_multiplier);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 2. calculate_lead_price fonksiyonunu güncelle (persona parametresi ekle)
CREATE OR REPLACE FUNCTION calculate_lead_price(
  p_base_price INTEGER,
  p_planned_activities JSONB,
  p_trigger_source TEXT DEFAULT NULL,
  p_tourist_persona JSONB 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
  -- Pricing rules'dan değerleri al
  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';
 
  -- Varsayılan değerler
  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);
 
  -- Base price kontrolü
  IF p_base_price IS NULL OR p_base_price <= 0 THEN
    p_base_price := v_base::INTEGER;
  END IF;
 
  -- AI recommendation premium
  IF p_trigger_source = 'ai_route_recommendation' THEN
    v_multiplier := v_multiplier * v_ai_mult;
  END IF;
 
  -- Activity-based multipliers
  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', ''));
      
      -- Balloon activity
      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;
      
      -- ATV/Horse riding activity
      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;
      
      -- Guided tour activity
      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;
 
  -- Temel fiyat hesaplama
  v_final_price := ROUND(p_base_price * v_multiplier);
 
  -- AI minimum fiyat kontrolü
  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;
 
  -- Persona bazlı çarpan uygula (son adım)
  IF p_tourist_persona IS NOT NULL THEN
    v_final_price := apply_persona_multiplier(v_final_price, p_tourist_persona);
  END IF;
 
  RETURN v_final_price;
END;
$$ LANGUAGE plpgsql STABLE;

-- 3. Yorum ekle
COMMENT ON FUNCTION apply_persona_multiplier(INTEGER, JSONB) IS 
  'Persona harcama potansiyeline göre lead fiyatına çarpan uygular: very_high=1.5x, high=1.3x, medium=1.0x, low=0.8x';

COMMENT ON FUNCTION calculate_lead_price(INTEGER, JSONB, TEXT, JSONB) IS 
  'Lead fiyatını hesaplar: base price + activity multipliers + AI premium + persona multiplier';

-- 4. update_lead_pricing trigger fonksiyonunu güncelle (persona desteği ekle)
CREATE OR REPLACE FUNCTION update_lead_pricing()
RETURNS TRIGGER AS $$
BEGIN
  -- Calculate price based on activities, trigger source, and persona
  NEW.calculated_price := calculate_lead_price(
    COALESCE(NEW.base_price, 20),
    NEW.planned_activities,
    NEW.trigger_source,
    NEW.tourist_persona
  );

  -- Set final price (override takes precedence)
  NEW.final_price := COALESCE(NEW.override_price, NEW.calculated_price);

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 5. Trigger'ı yeniden oluştur (persona değişikliklerinde de tetiklensin)
DROP TRIGGER IF EXISTS trigger_update_lead_pricing ON leads;
CREATE TRIGGER trigger_update_lead_pricing
  BEFORE INSERT OR UPDATE OF base_price, planned_activities, override_price, trigger_source, tourist_persona
  ON leads
  FOR EACH ROW
  EXECUTE FUNCTION update_lead_pricing();

-- 6. Mevcut lead'lerin fiyatlarını güncelle (persona bilgisi varsa)
UPDATE leads
SET 
  calculated_price = calculate_lead_price(
    COALESCE(base_price, 20), 
    planned_activities, 
    trigger_source,
    tourist_persona
  ),
  final_price = COALESCE(
    override_price, 
    calculate_lead_price(
      COALESCE(base_price, 20), 
      planned_activities, 
      trigger_source,
      tourist_persona
    )
  )
WHERE tourist_persona IS NOT NULL;

-- 7. Grant permissions
GRANT EXECUTE ON FUNCTION apply_persona_multiplier(INTEGER, JSONB) TO authenticated;
GRANT EXECUTE ON FUNCTION apply_persona_multiplier(INTEGER, JSONB) TO service_role;
GRANT EXECUTE ON FUNCTION calculate_lead_price(INTEGER, JSONB, TEXT, JSONB) TO authenticated;
GRANT EXECUTE ON FUNCTION calculate_lead_price(INTEGER, JSONB, TEXT, JSONB) TO service_role;
