-- Fix messaging functions: add SET search_path = public to prevent SQL injection
-- These were created in add-messaging.sql without search_path protection

CREATE OR REPLACE FUNCTION get_conversation_previews(
  user_id uuid,
  page_limit integer DEFAULT 20,
  page_offset integer DEFAULT 0
)
RETURNS TABLE (
  conversation_id uuid,
  other_participant_name text,
  other_participant_avatar text,
  last_message text,
  last_message_time timestamptz,
  unread_count bigint,
  other_participant_is_online boolean
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    c.id as conversation_id,
    p.name as other_participant_name,
    p.avatar_url as other_participant_avatar,
    last_msg.content as last_message,
    last_msg.created_at as last_message_time,
    COALESCE(unread.unread_count, 0) as unread_count,
    true as other_participant_is_online -- TODO: Implement online status
  FROM conversations c
  INNER JOIN conversation_participants cp ON c.id = cp.conversation_id
  INNER JOIN conversation_participants other_cp ON c.id = other_cp.conversation_id
  INNER JOIN profiles p ON other_cp.profile_id = p.id
  LEFT JOIN LATERAL (
    SELECT content, created_at
    FROM messages
    WHERE conversation_id = c.id
    ORDER BY created_at DESC
    LIMIT 1
  ) last_msg ON true
  LEFT JOIN LATERAL (
    SELECT COUNT(*) as unread_count
    FROM messages
    WHERE conversation_id = c.id
      AND sender_id != user_id
      AND created_at > cp.last_read_at
  ) unread ON true
  WHERE cp.profile_id = user_id
    AND other_cp.profile_id != user_id
  ORDER BY last_msg.created_at DESC NULLS LAST
  LIMIT page_limit
  OFFSET page_offset;
END;
$$ LANGUAGE plpgsql STABLE

CREATE OR REPLACE FUNCTION get_or_create_conversation(user1_id uuid, user2_id uuid)
RETURNS uuid AS $$
DECLARE
  conversation_uuid uuid;
BEGIN
  -- Check if conversation already exists
  SELECT c.id INTO conversation_uuid
  FROM conversations c
  INNER JOIN conversation_participants cp1 ON c.id = cp1.conversation_id
  INNER JOIN conversation_participants cp2 ON c.id = cp2.conversation_id
  WHERE cp1.profile_id = user1_id
    AND cp2.profile_id = user2_id
  LIMIT 1;

  -- If conversation doesn't exist, create it
  IF conversation_uuid IS NULL THEN
    INSERT INTO conversations DEFAULT VALUES
    RETURNING id INTO conversation_uuid;

    INSERT INTO conversation_participants (conversation_id, profile_id)
    VALUES (conversation_uuid, user1_id), (conversation_uuid, user2_id);
  END IF;

  RETURN conversation_uuid;
END;
$$ LANGUAGE plpgsql
