-- Estrutura do banco de dados para Chat WebCis

CREATE TABLE `sites` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `domain` VARCHAR(255) NOT NULL,
  `nome` VARCHAR(100) DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE `agents` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `nome` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NOT NULL UNIQUE,
  `senha` VARCHAR(255) NOT NULL,
  `status` ENUM('online','offline') DEFAULT 'offline',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE `chats` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `site_id` INT NOT NULL,
  `user_nome` VARCHAR(100) NOT NULL,
  `user_email` VARCHAR(100) NOT NULL,
  `user_telefone` VARCHAR(30) DEFAULT NULL,
  `user_empresa` VARCHAR(100) DEFAULT NULL,
  `user_ip` VARCHAR(45) DEFAULT NULL,
  `user_browser` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('pendente','resolvido') DEFAULT 'pendente',
  `assigned_agent` INT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `ended_at` DATETIME DEFAULT NULL,
  FOREIGN KEY (`site_id`) REFERENCES `sites`(`id`),
  FOREIGN KEY (`assigned_agent`) REFERENCES `agents`(`id`)
);

CREATE TABLE `messages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `chat_id` INT NOT NULL,
  `sender` ENUM('user','agent') NOT NULL,
  `agent_id` INT DEFAULT NULL,
  `mensagem` TEXT NOT NULL,
  `is_read` TINYINT(1) DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`chat_id`) REFERENCES `chats`(`id`),
  FOREIGN KEY (`agent_id`) REFERENCES `agents`(`id`)
);

CREATE TABLE `quick_replies` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `agent_id` INT DEFAULT NULL,
  `titulo` VARCHAR(100) NOT NULL,
  `mensagem` TEXT NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`agent_id`) REFERENCES `agents`(`id`)
);

-- Índices para performance
CREATE INDEX idx_chats_email ON chats(user_email);
CREATE INDEX idx_chats_nome ON chats(user_nome);
CREATE INDEX idx_chats_status ON chats(status);
CREATE INDEX idx_messages_chat_id ON messages(chat_id);
CREATE INDEX idx_messages_created_at ON messages(created_at);
