CREATE TABLE conversations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  jid VARCHAR(120) NOT NULL,
  display_name VARCHAR(120) NULL,
  phone VARCHAR(30) NULL,
  status ENUM('open','pending','closed') NOT NULL DEFAULT 'open',
  queue ENUM('comercial','agendamento','financeiro') NOT NULL DEFAULT 'comercial',
  labels JSON NULL,
  last_message_preview VARCHAR(255) NULL,
  last_message_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_jid (jid),
  INDEX idx_status (status),
  INDEX idx_queue (queue),
  INDEX idx_lastmsg (last_message_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  conversation_id BIGINT UNSIGNED NOT NULL,
  evo_message_id VARCHAR(120) NULL,
  direction ENUM('in','out') NOT NULL,
  msg_type ENUM('text','image','audio','video','document','sticker','unknown') NOT NULL DEFAULT 'text',
  content TEXT NULL,
  media_url TEXT NULL,
  raw JSON NULL,
  sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_conv (conversation_id),
  INDEX idx_sent (sent_at),
  UNIQUE KEY uq_evo_msg (evo_message_id),
  CONSTRAINT fk_msg_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','agent') NOT NULL DEFAULT 'agent',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;