supabase-schema.sql

-- ============================================
-- new-mIRC Database Schema
-- Execute este script no Supabase SQL Editor
-- ============================================

-- Criar tabela de usuários
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  username TEXT UNIQUE NOT NULL,
  display_name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  avatar_url TEXT,
  status TEXT DEFAULT 'offline' CHECK (status IN ('online', 'away', 'busy', 'offline')),
  status_message TEXT,
  public_key TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  last_seen TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Criar tabela de servidores
CREATE TABLE IF NOT EXISTS servers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  description TEXT,
  owner_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  icon_url TEXT,
  is_public BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Criar tabela de canais
CREATE TABLE IF NOT EXISTS channels (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  server_id UUID REFERENCES servers(id) ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  topic TEXT,
  type TEXT DEFAULT 'text' CHECK (type IN ('text', 'voice')),
  is_private BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Criar tabela de membros do servidor
CREATE TABLE IF NOT EXISTS server_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  server_id UUID REFERENCES servers(id) ON DELETE CASCADE NOT NULL,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  role TEXT DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'moderator', 'member')),
  joined_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(server_id, user_id)
);

-- Criar tabela de mensagens
CREATE TABLE IF NOT EXISTS messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  channel_id UUID REFERENCES channels(id) ON DELETE CASCADE NOT NULL,
  user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  content TEXT NOT NULL,
  encrypted_keys JSONB,
  type TEXT DEFAULT 'text' CHECK (type IN ('text', 'image', 'video', 'file', 'system')),
  media_url TEXT,
  reply_to UUID REFERENCES messages(id) ON DELETE SET NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  edited_at TIMESTAMP WITH TIME ZONE
);

-- Criar tabela de mensagens diretas
CREATE TABLE IF NOT EXISTS direct_messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sender_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  recipient_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  content TEXT NOT NULL,
  encrypted_key TEXT,
  type TEXT DEFAULT 'text' CHECK (type IN ('text', 'image', 'video', 'file')),
  media_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  read_at TIMESTAMP WITH TIME ZONE
);

-- Criar tabela de chaves de criptografia
CREATE TABLE IF NOT EXISTS user_keys (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  device_id TEXT NOT NULL,
  identity_key TEXT NOT NULL,
  signed_pre_key TEXT NOT NULL,
  one_time_keys JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id, device_id)
);

-- ============================================
-- Índices para melhor performance
-- ============================================

CREATE INDEX IF NOT EXISTS idx_messages_channel_id ON messages(channel_id);
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_direct_messages_sender ON direct_messages(sender_id);
CREATE INDEX IF NOT EXISTS idx_direct_messages_recipient ON direct_messages(recipient_id);
CREATE INDEX IF NOT EXISTS idx_direct_messages_created_at ON direct_messages(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_server_members_server_id ON server_members(server_id);
CREATE INDEX IF NOT EXISTS idx_server_members_user_id ON server_members(user_id);
CREATE INDEX IF NOT EXISTS idx_channels_server_id ON channels(server_id);

-- ============================================
-- Row Level Security (RLS)
-- ============================================

ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE servers ENABLE ROW LEVEL SECURITY;
ALTER TABLE channels ENABLE ROW LEVEL SECURITY;
ALTER TABLE server_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE direct_messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_keys ENABLE ROW LEVEL SECURITY;

-- ============================================
-- Políticas de Segurança - Users
-- ============================================

-- Todos podem ver todos os usuários (para lista de usuários)
CREATE POLICY "Users can view all users" ON users
  FOR SELECT USING (true);

-- Usuários podem atualizar apenas seu próprio perfil
CREATE POLICY "Users can update own profile" ON users
  FOR UPDATE USING (auth.uid() = id);

-- Usuários podem inserir seu próprio perfil (durante registro)
CREATE POLICY "Users can insert own profile" ON users
  FOR INSERT WITH CHECK (auth.uid() = id);

-- ============================================
-- Políticas de Segurança - Servers
-- ============================================

-- Qualquer um pode ver servidores públicos
CREATE POLICY "Anyone can view public servers" ON servers
  FOR SELECT USING (is_public = true);

-- Membros podem ver servidores privados dos quais fazem parte
CREATE POLICY "Members can view their private servers" ON servers
  FOR SELECT USING (
    id IN (SELECT server_id FROM server_members WHERE user_id = auth.uid())
  );

-- Usuários autenticados podem criar servidores
CREATE POLICY "Authenticated users can create servers" ON servers
  FOR INSERT WITH CHECK (auth.uid() = owner_id);

-- Donos podem atualizar seus servidores
CREATE POLICY "Owners can update their servers" ON servers
  FOR UPDATE USING (auth.uid() = owner_id);

-- Donos podem deletar seus servidores
CREATE POLICY "Owners can delete their servers" ON servers
  FOR DELETE USING (auth.uid() = owner_id);

-- ============================================
-- Políticas de Segurança - Channels
-- ============================================

-- Membros do servidor podem ver canais
CREATE POLICY "Server members can view channels" ON channels
  FOR SELECT USING (
    server_id IN (SELECT server_id FROM server_members WHERE user_id = auth.uid())
  );

-- Admins e donos podem criar canais
CREATE POLICY "Admins can create channels" ON channels
  FOR INSERT WITH CHECK (
    server_id IN (
      SELECT server_id FROM server_members 
      WHERE user_id = auth.uid() 
      AND role IN ('owner', 'admin')
    )
  );

-- Admins e donos podem atualizar canais
CREATE POLICY "Admins can update channels" ON channels
  FOR UPDATE USING (
    server_id IN (
      SELECT server_id FROM server_members 
      WHERE user_id = auth.uid() 
      AND role IN ('owner', 'admin')
    )
  );

-- Admins e donos podem deletar canais
CREATE POLICY "Admins can delete channels" ON channels
  FOR DELETE USING (
    server_id IN (
      SELECT server_id FROM server_members 
      WHERE user_id = auth.uid() 
      AND role IN ('owner', 'admin')
    )
  );

-- ============================================
-- Políticas de Segurança - Server Members
-- ============================================

-- Membros podem ver outros membros do mesmo servidor
CREATE POLICY "Members can view server members" ON server_members
  FOR SELECT USING (
    server_id IN (SELECT server_id FROM server_members WHERE user_id = auth.uid())
  );

-- Usuários podem entrar em servidores públicos
CREATE POLICY "Users can join public servers" ON server_members
  FOR INSERT WITH CHECK (
    auth.uid() = user_id AND
    server_id IN (SELECT id FROM servers WHERE is_public = true)
  );

-- Admins podem adicionar membros
CREATE POLICY "Admins can add members" ON server_members
  FOR INSERT WITH CHECK (
    server_id IN (
      SELECT server_id FROM server_members 
      WHERE user_id = auth.uid() 
      AND role IN ('owner', 'admin')
    )
  );

-- Usuários podem sair de servidores
CREATE POLICY "Users can leave servers" ON server_members
  FOR DELETE USING (auth.uid() = user_id);

-- Admins podem remover membros
CREATE POLICY "Admins can remove members" ON server_members
  FOR DELETE USING (
    server_id IN (
      SELECT server_id FROM server_members 
      WHERE user_id = auth.uid() 
      AND role IN ('owner', 'admin')
    )
  );

-- ============================================
-- Políticas de Segurança - Messages
-- ============================================

-- Membros do servidor podem ver mensagens dos canais
CREATE POLICY "Server members can view messages" ON messages
  FOR SELECT USING (
    channel_id IN (
      SELECT c.id FROM channels c
      INNER JOIN server_members sm ON c.server_id = sm.server_id
      WHERE sm.user_id = auth.uid()
    )
  );

-- Membros podem enviar mensagens
CREATE POLICY "Members can send messages" ON messages
  FOR INSERT WITH CHECK (
    auth.uid() = user_id AND
    channel_id IN (
      SELECT c.id FROM channels c
      INNER JOIN server_members sm ON c.server_id = sm.server_id
      WHERE sm.user_id = auth.uid()
    )
  );

-- Usuários podem editar suas próprias mensagens
CREATE POLICY "Users can update own messages" ON messages
  FOR UPDATE USING (auth.uid() = user_id);

-- Usuários podem deletar suas próprias mensagens
CREATE POLICY "Users can delete own messages" ON messages
  FOR DELETE USING (auth.uid() = user_id);

-- Moderadores podem deletar mensagens
CREATE POLICY "Moderators can delete messages" ON messages
  FOR DELETE USING (
    channel_id IN (
      SELECT c.id FROM channels c
      INNER JOIN server_members sm ON c.server_id = sm.server_id
      WHERE sm.user_id = auth.uid() 
      AND sm.role IN ('owner', 'admin', 'moderator')
    )
  );

-- ============================================
-- Políticas de Segurança - Direct Messages
-- ============================================

-- Usuários podem ver mensagens diretas enviadas ou recebidas
CREATE POLICY "Users can view their DMs" ON direct_messages
  FOR SELECT USING (
    auth.uid() = sender_id OR auth.uid() = recipient_id
  );

-- Usuários podem enviar mensagens diretas
CREATE POLICY "Users can send DMs" ON direct_messages
  FOR INSERT WITH CHECK (auth.uid() = sender_id);

-- Usuários podem atualizar mensagens que enviaram (para marcar como lida)
CREATE POLICY "Users can update DMs" ON direct_messages
  FOR UPDATE USING (
    auth.uid() = sender_id OR auth.uid() = recipient_id
  );

-- Usuários podem deletar mensagens que enviaram
CREATE POLICY "Users can delete own DMs" ON direct_messages
  FOR DELETE USING (auth.uid() = sender_id);

-- ============================================
-- Políticas de Segurança - User Keys
-- ============================================

-- Todos podem ver chaves públicas (necessário para E2E)
CREATE POLICY "Anyone can view public keys" ON user_keys
  FOR SELECT USING (true);

-- Usuários podem inserir suas próprias chaves
CREATE POLICY "Users can insert own keys" ON user_keys
  FOR INSERT WITH CHECK (auth.uid() = user_id);

-- Usuários podem atualizar suas próprias chaves
CREATE POLICY "Users can update own keys" ON user_keys
  FOR UPDATE USING (auth.uid() = user_id);

-- Usuários podem deletar suas próprias chaves
CREATE POLICY "Users can delete own keys" ON user_keys
  FOR DELETE USING (auth.uid() = user_id);

-- ============================================
-- Funções e Triggers
-- ============================================

-- Função para atualizar last_seen automaticamente
CREATE OR REPLACE FUNCTION update_last_seen()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE users 
  SET last_seen = NOW() 
  WHERE id = NEW.user_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger para atualizar last_seen ao enviar mensagem
CREATE TRIGGER update_user_last_seen_on_message
AFTER INSERT ON messages
FOR EACH ROW
EXECUTE FUNCTION update_last_seen();

-- Trigger para atualizar last_seen ao enviar DM
CREATE TRIGGER update_user_last_seen_on_dm
AFTER INSERT ON direct_messages
FOR EACH ROW
EXECUTE FUNCTION update_last_seen();

-- ============================================
-- Realtime
-- ============================================

-- Habilitar Realtime para as tabelas necessárias
ALTER PUBLICATION supabase_realtime ADD TABLE messages;
ALTER PUBLICATION supabase_realtime ADD TABLE direct_messages;
ALTER PUBLICATION supabase_realtime ADD TABLE users;
ALTER PUBLICATION supabase_realtime ADD TABLE server_members;

-- ============================================
-- Dados de exemplo (opcional - remover em produção)
-- ============================================

-- Você pode adicionar dados de exemplo aqui se desejar
-- Por exemplo, um servidor público padrão

-- ============================================
-- Fim do script
-- ============================================