-- ═══════════════════════════════════════════════════════════════════════════
-- fotos.faciledita.com.br — Schema MySQL
-- ═══════════════════════════════════════════════════════════════════════════

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- ─── Leads ───────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS leads (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  phone           VARCHAR(20) NOT NULL UNIQUE,          -- ex: 5541999887766
  ddd             CHAR(2) NOT NULL,                     -- extraído do phone
  name            VARCHAR(120) DEFAULT NULL,
  whatsapp_name   VARCHAR(120) DEFAULT NULL,            -- nome do WA na entrada
  body_type       ENUM('slim','medium','plus') DEFAULT NULL,
  status          ENUM(
    'new',           -- entrou, ainda não interagiu
    'browsing',      -- vendo o catálogo
    'selected',      -- escolheu os modelos
    'photos_sent',   -- enviou fotos de referência
    'generating',    -- gerando imagens
    'preview_sent',  -- preview enviado, aguardando pagamento
    'awaiting_pay',  -- cobrança gerada
    'paid',          -- pagamento confirmado
    'delivered',     -- fotos em alta entregues
    'abandoned',     -- não respondeu / desistiu
    'cold'           -- sem resposta por 72h+
  ) NOT NULL DEFAULT 'new',
  plan            ENUM('basic','medium','full') DEFAULT NULL,  -- 3/5/10 fotos
  opted_in        TINYINT(1) NOT NULL DEFAULT 0,              -- aceitou receber campanhas
  source          VARCHAR(80) DEFAULT NULL,                   -- utm_source do anúncio
  created_at      DATETIME NOT NULL DEFAULT NOW(),
  updated_at      DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  last_message_at DATETIME DEFAULT NULL,
  recovery_sent_at DATETIME DEFAULT NULL,
  notes           TEXT DEFAULT NULL,
  INDEX idx_status  (status),
  INDEX idx_ddd     (ddd),
  INDEX idx_created (created_at),
  INDEX idx_phone   (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Pedidos ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS orders (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lead_id         INT UNSIGNED NOT NULL,
  plan            ENUM('basic','medium','full') NOT NULL,
  amount          DECIMAL(8,2) NOT NULL,                 -- 9.90 / 15.00 / 25.00
  status          ENUM('pending','paid','cancelled','refunded') NOT NULL DEFAULT 'pending',
  asaas_id        VARCHAR(60) DEFAULT NULL,              -- ID da cobrança no Asaas
  asaas_pix_code  TEXT DEFAULT NULL,                     -- código copia e cola
  asaas_qrcode    TEXT DEFAULT NULL,                     -- base64 do QR
  paid_at         DATETIME DEFAULT NULL,
  created_at      DATETIME NOT NULL DEFAULT NOW(),
  updated_at      DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
  INDEX idx_lead   (lead_id),
  INDEX idx_status (status),
  INDEX idx_asaas  (asaas_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Fotos enviadas pelo lead (referências de rosto) ─────────────────────────
CREATE TABLE IF NOT EXISTS lead_photos (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lead_id     INT UNSIGNED NOT NULL,
  order_id    INT UNSIGNED DEFAULT NULL,
  file_path   VARCHAR(255) NOT NULL,                     -- caminho no servidor
  type        ENUM('reference','preview','delivered') NOT NULL DEFAULT 'reference',
  created_at  DATETIME NOT NULL DEFAULT NOW(),
  FOREIGN KEY (lead_id)  REFERENCES leads(id)  ON DELETE CASCADE,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  INDEX idx_lead (lead_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Modelos do catálogo ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS models (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code        VARCHAR(10) NOT NULL UNIQUE,               -- ex: A01, N12, J03
  name        VARCHAR(120) NOT NULL,
  category    VARCHAR(60) NOT NULL,                      -- aniversário, natal, casual...
  body_type   ENUM('slim','medium','plus','all') NOT NULL DEFAULT 'all',
  image_path  VARCHAR(255) NOT NULL,                     -- imagem do catálogo
  active      TINYINT(1) NOT NULL DEFAULT 1,
  sort_order  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT NOW(),
  INDEX idx_category  (category),
  INDEX idx_body_type (body_type),
  INDEX idx_active    (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Modelos escolhidos por pedido ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS order_models (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id    INT UNSIGNED NOT NULL,
  model_id    INT UNSIGNED NOT NULL,
  position    TINYINT UNSIGNED NOT NULL DEFAULT 1,       -- 1ª, 2ª, 3ª foto...
  gen_status  ENUM('pending','processing','done','error') NOT NULL DEFAULT 'pending',
  gen_preview VARCHAR(255) DEFAULT NULL,                 -- caminho preview baixa-res
  gen_full    VARCHAR(255) DEFAULT NULL,                 -- caminho entrega alta-res
  gen_job_id  VARCHAR(120) DEFAULT NULL,                 -- job ID no fal.ai
  gen_error   TEXT DEFAULT NULL,
  created_at  DATETIME NOT NULL DEFAULT NOW(),
  updated_at  DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (model_id) REFERENCES models(id),
  INDEX idx_order  (order_id),
  INDEX idx_status (gen_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Histórico de mensagens WhatsApp ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS messages (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lead_id     INT UNSIGNED NOT NULL,
  direction   ENUM('in','out') NOT NULL,
  type        ENUM('text','image','audio','document','other') NOT NULL DEFAULT 'text',
  content     TEXT DEFAULT NULL,
  media_path  VARCHAR(255) DEFAULT NULL,
  created_at  DATETIME NOT NULL DEFAULT NOW(),
  FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
  INDEX idx_lead    (lead_id),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Campanhas sazonais ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS campaigns (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(120) NOT NULL,
  message     TEXT NOT NULL,                             -- texto da mensagem
  type        ENUM('national','regional') NOT NULL DEFAULT 'national',
  ddds        VARCHAR(255) DEFAULT NULL,                 -- CSV de DDDs, ex: "41,42,43"
  scheduled_at DATETIME NOT NULL,
  sent_at     DATETIME DEFAULT NULL,
  status      ENUM('draft','scheduled','sending','done','cancelled') NOT NULL DEFAULT 'draft',
  total_sent  INT UNSIGNED NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT NOW(),
  INDEX idx_scheduled (scheduled_at),
  INDEX idx_status    (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Log de envios de campanha por lead ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS campaign_sends (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  campaign_id  INT UNSIGNED NOT NULL,
  lead_id      INT UNSIGNED NOT NULL,
  sent_at      DATETIME DEFAULT NULL,
  status       ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
  FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  FOREIGN KEY (lead_id)     REFERENCES leads(id)     ON DELETE CASCADE,
  UNIQUE KEY uq_campaign_lead (campaign_id, lead_id),
  INDEX idx_campaign (campaign_id),
  INDEX idx_lead     (lead_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Configurações do sistema ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS settings (
  `key`       VARCHAR(80) PRIMARY KEY,
  `value`     TEXT NOT NULL,
  updated_at  DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO settings (`key`, `value`) VALUES
  ('price_basic',   '9.90'),
  ('price_medium',  '15.00'),
  ('price_full',    '25.00'),
  ('photos_basic',  '3'),
  ('photos_medium', '5'),
  ('photos_full',   '10'),
  ('asaas_env',     'sandbox'),       -- sandbox | production
  ('asaas_key',     ''),
  ('falay_key',     ''),
  ('recovery_hours','24'),            -- horas para disparar recuperação
  ('whatsapp_number','');

SET foreign_key_checks = 1;