-- Add pricing fields to leads table
ALTER TABLE leads ADD COLUMN IF NOT EXISTS base_price INTEGER DEFAULT 20;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS calculated_price INTEGER;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS override_price INTEGER;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS final_price INTEGER;

-- Add price_paid to lead_purchases to track actual price at purchase time
ALTER TABLE lead_purchases ADD COLUMN IF NOT EXISTS price_paid INTEGER;

-- Function to calculate lead price based on activities
CREATE OR REPLACE FUNCTION calculate_lead_price(
  p_base_price INTEGER,
  p_planned_activities JSONB
)
RETURNS INTEGER AS $$
DECLARE
  v_multiplier NUMERIC := 1.0;
  v_activity JSONB;
  v_activity_type TEXT;
  v_activity_name TEXT;
BEGIN
  -- Base price validation
  IF p_base_price IS NULL OR p_base_price <= 0 THEN
    p_base_price := 20;
  END IF;

  -- If no activities, return base price
  IF p_planned_activities IS NULL OR jsonb_array_length(p_planned_activities) = 0 THEN
    RETURN p_base_price;
  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)
  RETURN ROUND(p_base_price * v_multiplier);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Function to update lead pricing (called on insert/update)
CREATE OR REPLACE FUNCTION update_lead_pricing()
RETURNS TRIGGER AS $$
BEGIN
  -- Calculate price based on activities
  NEW.calculated_price := calculate_lead_price(
    COALESCE(NEW.base_price, 20),
    NEW.planned_activities
  );

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

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger to automatically update pricing
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
  ON leads
  FOR EACH ROW
  EXECUTE FUNCTION update_lead_pricing();

-- Update existing leads with calculated prices
UPDATE leads
SET 
  base_price = COALESCE(base_price, 20),
  calculated_price = calculate_lead_price(COALESCE(base_price, 20), planned_activities),
  final_price = COALESCE(override_price, calculate_lead_price(COALESCE(base_price, 20), planned_activities));

-- Function for admin to override lead price
CREATE OR REPLACE FUNCTION admin_override_lead_price(
  p_lead_id UUID,
  p_override_price INTEGER,
  p_admin_id UUID
)
RETURNS JSON AS $$
DECLARE
  v_old_price INTEGER;
  v_new_price INTEGER;
BEGIN
  -- Verify admin role
  IF NOT EXISTS (SELECT 1 FROM profiles WHERE id = p_admin_id AND role = 'admin') THEN
    RAISE EXCEPTION 'Unauthorized: Admin access required';
  END IF;

  -- Validate price
  IF p_override_price IS NOT NULL AND p_override_price < 0 THEN
    RAISE EXCEPTION 'Price cannot be negative';
  END IF;

  -- Get old price
  SELECT final_price INTO v_old_price FROM leads WHERE id = p_lead_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Lead not found';
  END IF;

  -- Update override price (NULL removes override)
  UPDATE leads
  SET 
    override_price = p_override_price,
    final_price = COALESCE(p_override_price, calculated_price),
    updated_at = NOW()
  WHERE id = p_lead_id
  RETURNING final_price INTO v_new_price;

  RETURN json_build_object(
    'success', true,
    'lead_id', p_lead_id,
    'old_price', v_old_price,
    'new_price', v_new_price,
    'override_applied', p_override_price IS NOT NULL
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Update the purchase_lead function to use final_price and record it
CREATE OR REPLACE FUNCTION purchase_lead(
  p_provider_id UUID,
  p_lead_id UUID
)
RETURNS JSON AS $$
DECLARE
  v_wallet_id UUID;
  v_current_balance INTEGER;
  v_lead_price INTEGER;
  v_new_balance INTEGER;
  v_purchase_id UUID;
BEGIN
  -- Get lead price
  SELECT final_price INTO v_lead_price
  FROM leads
  WHERE id = p_lead_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Lead not found';
  END IF;

  -- Default to 20 if price not set
  v_lead_price := COALESCE(v_lead_price, 20);

  -- Check if already purchased
  IF EXISTS (
    SELECT 1 FROM lead_purchases
    WHERE provider_id = p_provider_id AND lead_id = p_lead_id
  ) THEN
    RAISE EXCEPTION 'Lead already purchased';
  END IF;

  -- Get wallet and lock row
  SELECT id, credit_balance INTO v_wallet_id, v_current_balance
  FROM provider_wallets
  WHERE provider_id = p_provider_id
  FOR UPDATE;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Provider wallet not found';
  END IF;

  -- Check sufficient balance
  IF v_current_balance < v_lead_price THEN
    RAISE EXCEPTION 'Insufficient credits. Required: %, Available: %', v_lead_price, v_current_balance;
  END IF;

  -- Calculate new balance
  v_new_balance := v_current_balance - v_lead_price;

  -- Update wallet
  UPDATE provider_wallets
  SET 
    credit_balance = v_new_balance,
    updated_at = NOW()
  WHERE id = v_wallet_id;

  -- Record purchase with price paid
  INSERT INTO lead_purchases (provider_id, lead_id, credits_spent, price_paid)
  VALUES (p_provider_id, p_lead_id, v_lead_price, v_lead_price)
  RETURNING id INTO v_purchase_id;

  -- Record transaction
  INSERT INTO credit_transactions (
    wallet_id,
    transaction_type,
    amount,
    description,
    balance_after
  ) VALUES (
    v_wallet_id,
    'purchase',
    v_lead_price,
    'Lead purchase: ' || p_lead_id::TEXT,
    v_new_balance
  );

  RETURN json_build_object(
    'success', true,
    'purchase_id', v_purchase_id,
    'credits_spent', v_lead_price,
    'new_balance', v_new_balance
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Update admin_lead_stats view to include pricing information
DROP VIEW IF EXISTS admin_lead_stats;
CREATE OR REPLACE VIEW admin_lead_stats AS
SELECT 
  l.id,
  l.destination,
  l.country,
  l.start_date,
  l.end_date,
  l.number_of_travelers,
  l.interests,
  l.email,
  l.whatsapp,
  l.planned_activities,
  l.timeline_snapshot,
  l.base_price,
  l.calculated_price,
  l.override_price,
  l.final_price,
  l.created_at,
  COALESCE(COUNT(DISTINCT lp.provider_id), 0) as providers_unlocked,
  COALESCE(SUM(lp.credits_spent), 0) as total_revenue,
  CASE 
    WHEN COUNT(lp.id) = 0 THEN 'new'
    WHEN l.end_date < CURRENT_DATE THEN 'expired'
    ELSE 'sold'
  END as status
FROM leads l
LEFT JOIN lead_purchases lp ON l.id = lp.lead_id
GROUP BY l.id;

-- Grant permissions
GRANT EXECUTE ON FUNCTION calculate_lead_price(INTEGER, JSONB) TO authenticated;
GRANT EXECUTE ON FUNCTION admin_override_lead_price(UUID, INTEGER, UUID) TO authenticated;