-- ============================================
-- ImóvelHub Chat — Base de Dados MySQL
-- Correr este ficheiro uma vez para criar tudo
-- ============================================

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

-- Utilizadores
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  avatar VARCHAR(10) DEFAULT NULL,
  color VARCHAR(7) DEFAULT '#1D9E75',
  role ENUM('admin','user') DEFAULT 'user',
  status ENUM('online','away','offline') DEFAULT 'offline',
  last_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
  push_subscription TEXT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Canais e Grupos (type: 'channel' = público, 'group' = privado com múltiplos, 'direct' = 1:1)
CREATE TABLE IF NOT EXISTS rooms (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) DEFAULT NULL,
  description VARCHAR(255) DEFAULT NULL,
  type ENUM('channel','group','direct') NOT NULL,
  icon VARCHAR(10) DEFAULT '#',
  created_by INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
);

-- Membros de cada sala
CREATE TABLE IF NOT EXISTS room_members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  room_id INT NOT NULL,
  user_id INT NOT NULL,
  role ENUM('admin','member') DEFAULT 'member',
  joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_read_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_member (room_id, user_id),
  FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Mensagens
CREATE TABLE IF NOT EXISTS messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  room_id INT NOT NULL,
  user_id INT NOT NULL,
  body TEXT NOT NULL,
  type ENUM('text','file','image','system') DEFAULT 'text',
  file_name VARCHAR(255) DEFAULT NULL,
  file_path VARCHAR(500) DEFAULT NULL,
  file_size INT DEFAULT NULL,
  file_mime VARCHAR(100) DEFAULT NULL,
  reply_to INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (reply_to) REFERENCES messages(id) ON DELETE SET NULL,
  INDEX idx_room_created (room_id, created_at)
);

-- Reações a mensagens
CREATE TABLE IF NOT EXISTS reactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  message_id INT NOT NULL,
  user_id INT NOT NULL,
  emoji VARCHAR(10) NOT NULL,
  UNIQUE KEY unique_reaction (message_id, user_id, emoji),
  FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Push notification subscriptions
CREATE TABLE IF NOT EXISTS push_subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  endpoint TEXT NOT NULL,
  p256dh TEXT NOT NULL,
  auth TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ============================================
-- Dados iniciais de exemplo
-- ============================================

-- Admin padrão (password: admin123)
INSERT INTO users (name, email, password, avatar, role) VALUES
('Administrador', 'admin@imovel.pt', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '👤', 'admin');

-- Canal geral criado automaticamente
INSERT INTO rooms (name, description, type, icon, created_by) VALUES
('geral', 'Canal geral da empresa', 'channel', '#', 1),
('vendas', 'Equipa de vendas', 'channel', '#', 1),
('marketing', 'Marketing e comunicação', 'channel', '#', 1),
('juridico', 'Departamento jurídico', 'channel', '#', 1);

-- Adicionar admin a todos os canais
INSERT INTO room_members (room_id, user_id, role) VALUES
(1, 1, 'admin'), (2, 1, 'admin'), (3, 1, 'admin'), (4, 1, 'admin');