324 lines
11 KiB
PL/PgSQL
324 lines
11 KiB
PL/PgSQL
-- ============================================
|
|
-- Sistema de Mensagens - MediConnect
|
|
-- ============================================
|
|
|
|
-- Tabela de conversas entre médicos e pacientes
|
|
-- Usa external_doctor_id e external_patient_id para referenciar dados do Supabase externo
|
|
CREATE TABLE IF NOT EXISTS conversations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
external_doctor_id UUID NOT NULL, -- ID do médico no Supabase externo
|
|
external_patient_id UUID NOT NULL, -- ID do paciente no Supabase externo
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(external_doctor_id, external_patient_id)
|
|
);
|
|
|
|
-- Índices para performance
|
|
CREATE INDEX IF NOT EXISTS idx_conversations_doctor ON conversations(external_doctor_id);
|
|
CREATE INDEX IF NOT EXISTS idx_conversations_patient ON conversations(external_patient_id);
|
|
|
|
-- Tabela de mensagens
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
|
|
sender_id UUID NOT NULL, -- ID do usuário (médico ou paciente) no Supabase externo
|
|
sender_type VARCHAR(10) NOT NULL CHECK (sender_type IN ('doctor', 'patient')),
|
|
content TEXT,
|
|
message_type VARCHAR(20) NOT NULL DEFAULT 'text' CHECK (message_type IN ('text', 'file', 'image', 'video', 'audio', 'report', 'appointment')),
|
|
read_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
-- Campos para anexos
|
|
attachment_name VARCHAR(255),
|
|
attachment_url TEXT,
|
|
attachment_type VARCHAR(100),
|
|
attachment_size INTEGER,
|
|
|
|
-- Metadados
|
|
metadata JSONB DEFAULT '{}'::jsonb
|
|
);
|
|
|
|
-- Índices para performance
|
|
CREATE INDEX IF NOT EXISTS idx_messages_conversation ON messages(conversation_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messages_sender ON messages(sender_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_messages_read_at ON messages(read_at);
|
|
|
|
-- Tabela de status online (usando Supabase Realtime)
|
|
CREATE TABLE IF NOT EXISTS user_presence (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL, -- ID do usuário no Supabase externo
|
|
user_type VARCHAR(10) NOT NULL CHECK (user_type IN ('doctor', 'patient', 'admin', 'secretaria')),
|
|
online BOOLEAN DEFAULT false,
|
|
last_seen TIMESTAMPTZ DEFAULT NOW(),
|
|
metadata JSONB DEFAULT '{}'::jsonb,
|
|
UNIQUE(user_id, user_type)
|
|
);
|
|
|
|
-- Índice para verificação rápida de status
|
|
CREATE INDEX IF NOT EXISTS idx_user_presence_user ON user_presence(user_id, user_type);
|
|
CREATE INDEX IF NOT EXISTS idx_user_presence_online ON user_presence(online);
|
|
|
|
-- Tabela de anexos compartilhados
|
|
CREATE TABLE IF NOT EXISTS message_attachments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
|
|
file_name VARCHAR(255) NOT NULL,
|
|
file_url TEXT NOT NULL,
|
|
file_type VARCHAR(100) NOT NULL,
|
|
file_size INTEGER NOT NULL,
|
|
mime_type VARCHAR(100),
|
|
thumbnail_url TEXT,
|
|
uploaded_by UUID NOT NULL, -- ID do usuário no Supabase externo
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
metadata JSONB DEFAULT '{}'::jsonb
|
|
);
|
|
|
|
-- Índices
|
|
CREATE INDEX IF NOT EXISTS idx_attachments_message ON message_attachments(message_id);
|
|
CREATE INDEX IF NOT EXISTS idx_attachments_uploader ON message_attachments(uploaded_by);
|
|
|
|
-- Tabela de notificações de mensagens
|
|
CREATE TABLE IF NOT EXISTS message_notifications (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL,
|
|
user_type VARCHAR(10) NOT NULL,
|
|
message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
|
|
read BOOLEAN DEFAULT false,
|
|
sent_at TIMESTAMPTZ DEFAULT NOW(),
|
|
read_at TIMESTAMPTZ
|
|
);
|
|
|
|
-- Índices
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_user ON message_notifications(user_id, user_type);
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_read ON message_notifications(read);
|
|
|
|
-- ============================================
|
|
-- Triggers para atualização automática
|
|
-- ============================================
|
|
|
|
-- Trigger para atualizar updated_at em conversations
|
|
CREATE OR REPLACE FUNCTION update_conversation_timestamp()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
UPDATE conversations
|
|
SET updated_at = NOW()
|
|
WHERE id = NEW.conversation_id;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER update_conversation_on_message
|
|
AFTER INSERT ON messages
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_conversation_timestamp();
|
|
|
|
-- Trigger para atualizar last_seen em user_presence
|
|
CREATE OR REPLACE FUNCTION update_last_seen()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.last_seen = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER update_user_presence_last_seen
|
|
BEFORE UPDATE ON user_presence
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_last_seen();
|
|
|
|
-- ============================================
|
|
-- Funções auxiliares
|
|
-- ============================================
|
|
|
|
-- Função para obter conversas de um usuário
|
|
-- Retorna apenas IDs - frontend busca nomes do Supabase externo
|
|
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
|
|
) 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, -- Frontend busca do Supabase externo
|
|
NULL::TEXT as other_user_specialty, -- Frontend busca do Supabase externo
|
|
NULL::TEXT as other_user_avatar,
|
|
(SELECT content FROM messages WHERE conversation_id = c.id ORDER BY created_at DESC LIMIT 1) as last_message,
|
|
(SELECT created_at FROM messages WHERE conversation_id = c.id ORDER BY 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) as unread_count,
|
|
COALESCE((SELECT online FROM user_presence WHERE user_id = c.external_doctor_id AND user_type = 'doctor'), false) as is_online
|
|
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, -- Frontend busca do Supabase externo
|
|
NULL::TEXT as other_user_specialty,
|
|
NULL::TEXT as other_user_avatar,
|
|
(SELECT content FROM messages WHERE conversation_id = c.id ORDER BY created_at DESC LIMIT 1) as last_message,
|
|
(SELECT created_at FROM messages WHERE conversation_id = c.id ORDER BY 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) as unread_count,
|
|
COALESCE((SELECT online FROM user_presence WHERE user_id = c.external_patient_id AND user_type = 'patient'), false) as is_online
|
|
FROM conversations c
|
|
WHERE c.external_doctor_id = p_user_id
|
|
ORDER BY last_message_time DESC NULLS LAST;
|
|
END IF;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Função para criar ou obter conversa
|
|
CREATE OR REPLACE FUNCTION get_or_create_conversation(
|
|
p_doctor_id UUID,
|
|
p_patient_id UUID
|
|
)
|
|
RETURNS UUID AS $$
|
|
DECLARE
|
|
v_conversation_id UUID;
|
|
BEGIN
|
|
SELECT id INTO v_conversation_id
|
|
FROM conversations
|
|
WHERE external_doctor_id = p_doctor_id AND external_patient_id = p_patient_id;
|
|
|
|
IF v_conversation_id IS NULL THEN
|
|
INSERT INTO conversations (external_doctor_id, external_patient_id)
|
|
VALUES (p_doctor_id, p_patient_id)
|
|
RETURNING id INTO v_conversation_id;
|
|
END IF;
|
|
|
|
RETURN v_conversation_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Função para marcar mensagens como lidas
|
|
CREATE OR REPLACE FUNCTION mark_messages_as_read(
|
|
p_conversation_id UUID,
|
|
p_user_id UUID,
|
|
p_user_type VARCHAR
|
|
)
|
|
RETURNS INTEGER AS $$
|
|
DECLARE
|
|
v_updated_count INTEGER;
|
|
BEGIN
|
|
UPDATE messages
|
|
SET read_at = NOW()
|
|
WHERE conversation_id = p_conversation_id
|
|
AND sender_id != p_user_id
|
|
AND sender_type != p_user_type
|
|
AND read_at IS NULL;
|
|
|
|
GET DIAGNOSTICS v_updated_count = ROW_COUNT;
|
|
RETURN v_updated_count;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- ============================================
|
|
-- Políticas RLS (Row Level Security)
|
|
-- ============================================
|
|
|
|
-- Habilitar RLS
|
|
ALTER TABLE conversations ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE user_presence ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE message_attachments ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE message_notifications ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Políticas para conversations
|
|
CREATE POLICY "Users can view their own conversations"
|
|
ON conversations FOR SELECT
|
|
USING (
|
|
external_doctor_id = auth.uid()
|
|
OR external_patient_id = auth.uid()
|
|
);
|
|
|
|
CREATE POLICY "Users can create conversations"
|
|
ON conversations FOR INSERT
|
|
WITH CHECK (
|
|
external_doctor_id = auth.uid()
|
|
OR external_patient_id = auth.uid()
|
|
);
|
|
|
|
-- Políticas para messages
|
|
CREATE POLICY "Users can view messages in their conversations"
|
|
ON messages FOR SELECT
|
|
USING (
|
|
conversation_id IN (
|
|
SELECT id FROM conversations
|
|
WHERE external_doctor_id = auth.uid()
|
|
OR external_patient_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Users can send messages in their conversations"
|
|
ON messages FOR INSERT
|
|
WITH CHECK (
|
|
conversation_id IN (
|
|
SELECT id FROM conversations
|
|
WHERE external_doctor_id = auth.uid()
|
|
OR external_patient_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Users can update their own messages"
|
|
ON messages FOR UPDATE
|
|
USING (
|
|
sender_id = auth.uid()
|
|
OR conversation_id IN (
|
|
SELECT id FROM conversations
|
|
WHERE external_doctor_id = auth.uid()
|
|
OR external_patient_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
-- Políticas para user_presence
|
|
CREATE POLICY "Everyone can view presence"
|
|
ON user_presence FOR SELECT
|
|
USING (true);
|
|
|
|
CREATE POLICY "Users can update their own presence"
|
|
ON user_presence FOR ALL
|
|
USING (user_id = auth.uid());
|
|
|
|
-- Políticas para attachments
|
|
CREATE POLICY "Users can view attachments in their conversations"
|
|
ON message_attachments FOR SELECT
|
|
USING (
|
|
message_id IN (
|
|
SELECT m.id FROM messages m
|
|
JOIN conversations c ON m.conversation_id = c.id
|
|
WHERE c.external_doctor_id = auth.uid()
|
|
OR c.external_patient_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Users can upload attachments"
|
|
ON message_attachments FOR INSERT
|
|
WITH CHECK (uploaded_by = auth.uid());
|
|
|
|
-- ============================================
|
|
-- Comentários
|
|
-- ============================================
|
|
|
|
COMMENT ON TABLE conversations IS 'Conversas entre médicos e pacientes';
|
|
COMMENT ON TABLE messages IS 'Mensagens trocadas em conversas';
|
|
COMMENT ON TABLE user_presence IS 'Status online/offline de usuários em tempo real';
|
|
COMMENT ON TABLE message_attachments IS 'Anexos compartilhados em mensagens';
|
|
COMMENT ON TABLE message_notifications IS 'Notificações de novas mensagens';
|