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