

CREATE TABLE IF NOT EXISTS whatsapp_contacts (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id int NULL,
  name varchar(160) NULL,
  phone varchar(30) NOT NULL,
  company varchar(160) NULL,
  email varchar(160) NULL,
  tags varchar(255) NULL,
  last_message_at datetime NULL,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_whatsapp_contacts_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS whatsapp_messages (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  contact_id int NULL,
  ticket_id int NULL,
  direction enum('in','out') NOT NULL DEFAULT 'in',
  phone varchar(30) NOT NULL,
  sender_name varchar(160) NULL,
  message_type varchar(30) NOT NULL DEFAULT 'text',
  message text NULL,
  status varchar(30) NOT NULL DEFAULT 'received',
  evolution_message_id varchar(120) NULL,
  payload longtext NULL,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_whatsapp_messages_phone (phone),
  INDEX idx_whatsapp_messages_ticket (ticket_id),
  INDEX idx_whatsapp_messages_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS whatsapp_templates (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(120) NOT NULL,
  category varchar(80) NULL,
  body text NOT NULL,
  active tinyint(1) NOT NULL DEFAULT 1,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS whatsapp_logs (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  level varchar(20) NOT NULL DEFAULT 'info',
  action varchar(120) NULL,
  message text NULL,
  payload longtext NULL,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_whatsapp_logs_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS whatsapp_queue (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  phone varchar(30) NOT NULL,
  message text NOT NULL,
  status varchar(30) NOT NULL DEFAULT 'pendente',
  attempts int NOT NULL DEFAULT 0,
  last_error text NULL,
  scheduled_at datetime NULL,
  sent_at datetime NULL,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO whatsapp_templates (name, category, body)
SELECT 'Protocolo criado', 'Chamados', 'Olá, {cliente}. Seu chamado {protocolo} foi aberto para o equipamento {equipamento}. Em breve um técnico dará continuidade.'
WHERE NOT EXISTS (SELECT 1 FROM whatsapp_templates WHERE name='Protocolo criado');

INSERT INTO whatsapp_templates (name, category, body)
SELECT 'Solicitação de toner', 'Toner', 'Recebemos sua solicitação de toner. Informe o patrimônio ou modelo do equipamento para localizarmos o contrato.'
WHERE NOT EXISTS (SELECT 1 FROM whatsapp_templates WHERE name='Solicitação de toner');
