CREATE TABLE IF NOT EXISTS settings (
  `key` varchar(100) NOT NULL PRIMARY KEY,
  `value` text NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS whatsapp_instances (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(120) NOT NULL DEFAULT 'SUPERFAX',
  phone varchar(30) NULL,
  status varchar(30) NOT NULL DEFAULT 'desconectado',
  api_url varchar(255) NULL,
  api_key text NULL,
  webhook_url varchar(255) NULL,
  last_sync datetime NULL,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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 settings (`key`, `value`) VALUES
('whatsapp_api_url','http://localhost:8080'),
('whatsapp_api_key',''),
('whatsapp_instance','SUPERFAX'),
('whatsapp_enabled','0'),
('whatsapp_ai_enabled','0'),
('whatsapp_ai_business_hours','08:00-18:00'),
('whatsapp_auto_ticket','1'),
('whatsapp_human_after','3'),
('whatsapp_webhook_secret','superfax-webhook'),
('whatsapp_default_technician',''),
('whatsapp_company_name','SUPERFAX SOLUÇÕES')
ON DUPLICATE KEY UPDATE `key`=`key`;

INSERT INTO whatsapp_instances (name, status, api_url)
SELECT 'SUPERFAX', 'desconectado', 'http://localhost:8080'
WHERE NOT EXISTS (SELECT 1 FROM whatsapp_instances WHERE name='SUPERFAX');

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');