CREATE DATABASE IF NOT EXISTS whatsapp_helpdesk
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE whatsapp_helpdesk;

-- Contatos (clientes do WhatsApp)
CREATE TABLE contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  wa_id VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(150) NULL,
  phone VARCHAR(20) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_message_at DATETIME NULL
);

-- Departamentos / Filas
CREATE TABLE departments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO departments (name, description) VALUES
('Suporte', 'Atendimento técnico'),
('Comercial', 'Vendas e propostas'),
('Financeiro', 'Cobrança, pagamentos e NF');

-- Conversas
CREATE TABLE conversations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  contact_id INT NOT NULL,
  department_id INT NULL,
  status ENUM('new','in_progress','waiting_customer','closed') NOT NULL DEFAULT 'new',
  assigned_user_id INT NULL,
  glpi_ticket_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (contact_id) REFERENCES contacts(id),
  FOREIGN KEY (department_id) REFERENCES departments(id),
  FOREIGN KEY (assigned_user_id) REFERENCES users(id)
);

-- Mensagens
CREATE TABLE messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conversation_id INT NOT NULL,
  direction ENUM('in','out') NOT NULL,
  wa_message_id VARCHAR(100) NULL,
  sender_user_id INT NULL,
  body TEXT NOT NULL,
  type VARCHAR(30) NOT NULL DEFAULT 'text',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (conversation_id) REFERENCES conversations(id),
  FOREIGN KEY (sender_user_id) REFERENCES users(id)
);

-- Notas internas
CREATE TABLE conversation_notes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conversation_id INT NOT NULL,
  user_id INT NOT NULL,
  note TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (conversation_id) REFERENCES conversations(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);
