-- ============================================
-- FIX: Ambiguous column references
-- ============================================

-- Dropar função existente
DROP FUNCTION IF EXISTS get_user_conversations(UUID, VARCHAR);

-- Recriar função get_user_conversations com aliases qualificados
CREATE OR REPLACE FUNCTION get_user_conversations(
  p_user_id UUID,
  p_user_type VARCHAR
)
RETURNS TABLE (
  conversation_id UUID,
  other_user_id UUID,
  other_user_name TEXT,
  other_user_specialty TEXT,
  other_user_avatar TEXT,
  last_message TEXT,
  last_message_time TIMESTAMPTZ,
  unread_count BIGINT,
  is_online BOOLEAN,
  created_at TIMESTAMPTZ
) AS $$
BEGIN
  IF p_user_type = 'patient' THEN
    RETURN QUERY
    SELECT 
      c.id as conversation_id,
      c.external_doctor_id as other_user_id,
      NULL::TEXT as other_user_name,
      NULL::TEXT as other_user_specialty,
      NULL::TEXT as other_user_avatar,
      (SELECT m.content FROM messages m WHERE m.conversation_id = c.id ORDER BY m.created_at DESC LIMIT 1) as last_message,
      (SELECT m.created_at FROM messages m WHERE m.conversation_id = c.id ORDER BY m.created_at DESC LIMIT 1) as last_message_time,
      (SELECT COUNT(*) FROM messages m WHERE m.conversation_id = c.id AND m.sender_type = 'doctor' AND m.read_at IS NULL)::BIGINT as unread_count,
      COALESCE((SELECT up.online FROM user_presence up WHERE up.user_id = c.external_doctor_id AND up.user_type = 'doctor'), false) as is_online,
      c.created_at
    FROM conversations c
    WHERE c.external_patient_id = p_user_id
    ORDER BY last_message_time DESC NULLS LAST;
  ELSE
    RETURN QUERY
    SELECT 
      c.id as conversation_id,
      c.external_patient_id as other_user_id,
      NULL::TEXT as other_user_name,
      NULL::TEXT as other_user_specialty,
      NULL::TEXT as other_user_avatar,
      (SELECT m.content FROM messages m WHERE m.conversation_id = c.id ORDER BY m.created_at DESC LIMIT 1) as last_message,
      (SELECT m.created_at FROM messages m WHERE m.conversation_id = c.id ORDER BY m.created_at DESC LIMIT 1) as last_message_time,
      (SELECT COUNT(*) FROM messages m WHERE m.conversation_id = c.id AND m.sender_type = 'patient' AND m.read_at IS NULL)::BIGINT as unread_count,
      COALESCE((SELECT up.online FROM user_presence up WHERE up.user_id = c.external_patient_id AND up.user_type = 'patient'), false) as is_online,
      c.created_at
    FROM conversations c
    WHERE c.external_doctor_id = p_user_id
    ORDER BY last_message_time DESC NULLS LAST;
  END IF;
END;
$$ LANGUAGE plpgsql;