-- Chat room membership RLS + DM room helper

CREATE INDEX IF NOT EXISTS idx_rooms_spot ON public.chat_rooms (spot_id);

DROP POLICY IF EXISTS "Members viewable by authenticated" ON public.chat_room_members;
CREATE POLICY "Members viewable by authenticated"
  ON public.chat_room_members FOR SELECT
  USING (auth.role() = 'authenticated');

DROP POLICY IF EXISTS "Users can join rooms" ON public.chat_room_members;
CREATE POLICY "Users can join rooms"
  ON public.chat_room_members FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE OR REPLACE FUNCTION public.get_or_create_dm_room(p_user_a UUID, p_user_b UUID)
RETURNS UUID AS $$
DECLARE
  v_room_id UUID;
  v_low UUID;
  v_high UUID;
BEGIN
  IF p_user_a IS NULL OR p_user_b IS NULL OR p_user_a = p_user_b THEN
    RAISE EXCEPTION 'Invalid DM participants';
  END IF;

  IF p_user_a < p_user_b THEN
    v_low := p_user_a;
    v_high := p_user_b;
  ELSE
    v_low := p_user_b;
    v_high := p_user_a;
  END IF;

  SELECT cr.id INTO v_room_id
  FROM public.chat_rooms cr
  JOIN public.chat_room_members m1 ON m1.room_id = cr.id AND m1.user_id = v_low
  JOIN public.chat_room_members m2 ON m2.room_id = cr.id AND m2.user_id = v_high
  WHERE cr.type = 'dm'
  LIMIT 1;

  IF v_room_id IS NOT NULL THEN
    RETURN v_room_id;
  END IF;

  INSERT INTO public.chat_rooms (name, type, created_by)
  VALUES ('Private Chat', 'dm', p_user_a)
  RETURNING id INTO v_room_id;

  INSERT INTO public.chat_room_members (room_id, user_id)
  VALUES (v_room_id, v_low), (v_room_id, v_high)
  ON CONFLICT DO NOTHING;

  RETURN v_room_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

GRANT EXECUTE ON FUNCTION public.get_or_create_dm_room(UUID, UUID) TO authenticated;