-- WhatsApp CRM — Schema Completo
CREATE DATABASE IF NOT EXISTS whatsapp_crm CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE whatsapp_crm;

-- Usuários/Agentes
CREATE TABLE IF NOT EXISTS agents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255),
  role ENUM('admin','agent') DEFAULT 'agent',
  status ENUM('online','offline','busy') DEFAULT 'offline',
  department_id INT,
  avatar_url VARCHAR(512),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_status (status)
);

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

-- Contatos/Leads
CREATE TABLE IF NOT EXISTS contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  phone VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(255),
  company VARCHAR(255),
  location VARCHAR(255),
  category VARCHAR(100),
  temperature ENUM('cold','warm','hot') DEFAULT 'cold',
  avatar_url VARCHAR(512),
  lead_score INT DEFAULT 0,
  funnel_stage VARCHAR(100) DEFAULT 'Novo',
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_phone (phone),
  INDEX idx_category (category),
  INDEX idx_updated (updated_at)
);

-- Conversas
CREATE TABLE IF NOT EXISTS conversations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  contact_id INT NOT NULL,
  agent_id INT,
  department_id INT,
  status ENUM('open','pending','resolved','closed') DEFAULT 'open',
  channel VARCHAR(50) DEFAULT 'whatsapp',
  unread_count INT DEFAULT 0,
  last_message TEXT,
  last_message_at DATETIME,
  scheduled_return DATETIME,
  funnel_stage VARCHAR(100),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (contact_id) REFERENCES contacts(id),
  FOREIGN KEY (agent_id) REFERENCES agents(id),
  INDEX idx_status (status),
  INDEX idx_contact (contact_id),
  INDEX idx_agent (agent_id),
  INDEX idx_last_msg (last_message_at)
);

-- Mensagens
CREATE TABLE IF NOT EXISTS messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conversation_id INT NOT NULL,
  contact_id INT,
  agent_id INT,
  direction ENUM('inbound','outbound') NOT NULL,
  type ENUM('text','image','audio','video','document','sticker','location','contacts','reaction','bot','system') DEFAULT 'text',
  content TEXT,
  media_url VARCHAR(512),
  media_type VARCHAR(100),
  whatsapp_message_id VARCHAR(255),
  status ENUM('pending','sent','delivered','read','failed') DEFAULT 'pending',
  is_bot BOOLEAN DEFAULT FALSE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (conversation_id) REFERENCES conversations(id),
  INDEX idx_conversation (conversation_id),
  INDEX idx_created (created_at),
  INDEX idx_wa_id (whatsapp_message_id)
);

-- Configuração WhatsApp
CREATE TABLE IF NOT EXISTS whatsapp_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  phone_number_id VARCHAR(255),
  access_token TEXT,
  webhook_verify_token VARCHAR(255),
  business_account_id VARCHAR(255),
  display_phone VARCHAR(50),
  is_active BOOLEAN DEFAULT FALSE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Configuração do Bot
CREATE TABLE IF NOT EXISTS bot_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  enabled BOOLEAN DEFAULT FALSE,
  business_hours_start TIME DEFAULT '08:00:00',
  business_hours_end TIME DEFAULT '18:00:00',
  business_days VARCHAR(50) DEFAULT '1,2,3,4,5',
  welcome_message TEXT,
  outside_hours_message TEXT,
  transfer_message TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Regras do Bot (keyword → resposta)
CREATE TABLE IF NOT EXISTS bot_rules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  keyword VARCHAR(255) NOT NULL,
  response TEXT NOT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  priority INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Fluxos do Bot
CREATE TABLE IF NOT EXISTS bot_flows (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  trigger_keyword VARCHAR(255),
  steps JSON,
  is_active BOOLEAN DEFAULT TRUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Mensagens Agendadas
CREATE TABLE IF NOT EXISTS scheduled_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conversation_id INT NOT NULL,
  contact_id INT NOT NULL,
  agent_id INT,
  message TEXT NOT NULL,
  scheduled_at DATETIME NOT NULL,
  status ENUM('pending','sent','cancelled','failed') DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (conversation_id) REFERENCES conversations(id)
);

-- Anotações de Contato
CREATE TABLE IF NOT EXISTS contact_notes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  contact_id INT NOT NULL,
  agent_id INT,
  content TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (contact_id) REFERENCES contacts(id)
);

-- Tags / Etiquetas
CREATE TABLE IF NOT EXISTS tags (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  color VARCHAR(20) DEFAULT '#25D366',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Tags por Conversa
CREATE TABLE IF NOT EXISTS conversation_tags (
  conversation_id INT NOT NULL,
  tag_id INT NOT NULL,
  PRIMARY KEY (conversation_id, tag_id),
  FOREIGN KEY (conversation_id) REFERENCES conversations(id),
  FOREIGN KEY (tag_id) REFERENCES tags(id)
);

-- Score de Leads
CREATE TABLE IF NOT EXISTS lead_scores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  contact_id INT NOT NULL,
  score INT DEFAULT 0,
  response_time_score INT DEFAULT 0,
  message_count_score INT DEFAULT 0,
  funnel_score INT DEFAULT 0,
  engagement_score INT DEFAULT 0,
  calculated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (contact_id) REFERENCES contacts(id)
);

-- Etapas do Funil de Vendas
CREATE TABLE IF NOT EXISTS funnel_stages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  color VARCHAR(20) DEFAULT '#25D366',
  position INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Custos de Mensagem WhatsApp
CREATE TABLE IF NOT EXISTS message_costs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conversation_id INT,
  conversation_type ENUM('service','utility','authentication','marketing') DEFAULT 'service',
  cost DECIMAL(10,4) DEFAULT 0,
  currency VARCHAR(10) DEFAULT 'BRL',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Configuração do Sistema
CREATE TABLE IF NOT EXISTS system_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  config_key VARCHAR(255) UNIQUE NOT NULL,
  config_value TEXT,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Módulos
CREATE TABLE IF NOT EXISTS module_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  module_key VARCHAR(100) UNIQUE NOT NULL,
  enabled BOOLEAN DEFAULT TRUE,
  config JSON,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Categorias de Leads
CREATE TABLE IF NOT EXISTS lead_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  color VARCHAR(20) DEFAULT '#25D366',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Categorias de Agentes
CREATE TABLE IF NOT EXISTS agent_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  color VARCHAR(20) DEFAULT '#128C7E',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Contas de Marketing
CREATE TABLE IF NOT EXISTS marketing_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  platform ENUM('google_ads','meta','tiktok') NOT NULL,
  account_name VARCHAR(255),
  account_id VARCHAR(255),
  access_token TEXT,
  refresh_token TEXT,
  token_expires_at DATETIME,
  mcc_id VARCHAR(255),
  metadata JSON,
  is_active BOOLEAN DEFAULT TRUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Métricas de Marketing (cache)
CREATE TABLE IF NOT EXISTS marketing_metrics (
  id INT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  period VARCHAR(20),
  metrics JSON,
  campaigns JSON,
  cached_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (account_id) REFERENCES marketing_accounts(id)
);

-- Configuração Asaas (Financeiro)
CREATE TABLE IF NOT EXISTS asaas_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  account_slot TINYINT DEFAULT 1,
  api_key TEXT,
  environment ENUM('sandbox','production') DEFAULT 'sandbox',
  account_name VARCHAR(255),
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Clientes Asaas
CREATE TABLE IF NOT EXISTS asaas_customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  contact_id INT,
  account_slot TINYINT DEFAULT 1,
  asaas_id VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255),
  email VARCHAR(255),
  cpf_cnpj VARCHAR(50),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Pagamentos Asaas
CREATE TABLE IF NOT EXISTS asaas_payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  account_slot TINYINT DEFAULT 1,
  asaas_id VARCHAR(255) UNIQUE,
  billing_type ENUM('PIX','BOLETO','CREDIT_CARD','DEBIT_CARD') DEFAULT 'PIX',
  status VARCHAR(50),
  value DECIMAL(10,2),
  due_date DATE,
  description TEXT,
  invoice_url VARCHAR(512),
  bank_slip_url VARCHAR(512),
  pix_code TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Solicitações de Exclusão de Dados
CREATE TABLE IF NOT EXISTS deletion_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id VARCHAR(255),
  platform VARCHAR(50),
  email VARCHAR(255),
  status ENUM('pending','processing','completed') DEFAULT 'pending',
  confirmation_code VARCHAR(100),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME
);

-- Sessões
CREATE TABLE IF NOT EXISTS sessions (
  id VARCHAR(255) PRIMARY KEY,
  agent_id INT,
  data JSON,
  expires_at DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (agent_id) REFERENCES agents(id)
);