-- SECURITY FIX: Use auth.uid() instead of caller-supplied p_admin_id
CREATE OR REPLACE FUNCTION admin_override_lead_price(
  p_lead_id UUID,
  p_override_price INTEGER
)
RETURNS JSON
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_old_price INTEGER;
  v_new_price INTEGER;
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
  ) THEN
    RAISE EXCEPTION 'Unauthorized: admin access required';
  END IF;

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

  SELECT final_price INTO v_old_price FROM leads WHERE id = p_lead_id;

  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,
    'old_price', v_old_price,
    'new_price', v_new_price
  );
END;
$$;

DROP FUNCTION IF EXISTS admin_override_lead_price(UUID, INTEGER, UUID);

GRANT EXECUTE ON FUNCTION admin_override_lead_price(UUID, INTEGER) TO authenticated;

-- Add admin function to change lead status manually
CREATE OR REPLACE FUNCTION admin_set_lead_status(
  p_lead_id UUID,
  p_status TEXT
)
RETURNS JSON
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
  ) THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  IF p_status NOT IN ('new', 'sold', 'expired', 'cancelled') THEN
    RAISE EXCEPTION 'Invalid status';
  END IF;

  UPDATE leads SET status = p_status, updated_at = NOW()
  WHERE id = p_lead_id;

  RETURN json_build_object('success', true, 'status', p_status);
END;
$$;

GRANT EXECUTE ON FUNCTION admin_set_lead_status(UUID, TEXT) TO authenticated;
