-- Add last_seen_at to profiles for online status tracking
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS last_seen_at timestamptz;
CREATE INDEX IF NOT EXISTS idx_profiles_last_seen_at ON profiles (last_seen_at);

-- Update get_conversation_previews to use real online status (active in last 5 min)
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,
    COALESCE(p.last_seen_at > (now() - interval '5 minutes'), false) as other_participant_is_online
  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
SET search_path = public;
