CREATE DATABASE IF NOT EXISTS webthi_chat CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE webthi_chat;

CREATE TABLE IF NOT EXISTS usuarios (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(120) NOT NULL,
  email VARCHAR(150) UNIQUE,
  telefone VARCHAR(30) UNIQUE,
  senha VARCHAR(255) NOT NULL,
  foto VARCHAR(255) DEFAULT NULL,
  status VARCHAR(150) DEFAULT 'Disponível',
  online BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS conversas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tipo ENUM('privada','grupo') DEFAULT 'privada',
  nome_grupo VARCHAR(120) DEFAULT NULL,
  foto_grupo VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS conversa_participantes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conversa_id INT NOT NULL,
  usuario_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (conversa_id) REFERENCES conversas(id) ON DELETE CASCADE,
  FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS mensagens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conversa_id INT NOT NULL,
  remetente_id INT NOT NULL,
  tipo ENUM('texto','imagem','audio','arquivo') DEFAULT 'texto',
  conteudo TEXT,
  arquivo VARCHAR(255) DEFAULT NULL,
  visualizada BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (conversa_id) REFERENCES conversas(id) ON DELETE CASCADE,
  FOREIGN KEY (remetente_id) REFERENCES usuarios(id) ON DELETE CASCADE
);