CREATE DATABASE IF NOT EXISTS claro_hogar_smart CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE claro_hogar_smart;
CREATE TABLE leads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  phone VARCHAR(40) NOT NULL,
  city VARCHAR(120) NOT NULL,
  neighborhood VARCHAR(120),
  address VARCHAR(255) NOT NULL,
  service VARCHAR(120),
  plan VARCHAR(120),
  notes TEXT,
  status ENUM('nuevo','contactado','interesado','venta_concretada','enviado_plataforma','aprobado','rechazado','instalado') DEFAULT 'nuevo',
  created_at DATETIME NOT NULL
);
CREATE TABLE clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lead_id INT NULL,
  name VARCHAR(160) NOT NULL,
  phone VARCHAR(40) NOT NULL,
  document_id VARCHAR(60),
  city VARCHAR(120),
  address VARCHAR(255),
  plan VARCHAR(120),
  invoice_cycle ENUM('1-10','10-20','20-30') DEFAULT '1-10',
  next_invoice_date DATE,
  status ENUM('activo','por_vencer','vencido','pagado','suspendido') DEFAULT 'activo',
  created_at DATETIME NOT NULL,
  FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE SET NULL
);
CREATE TABLE invoice_alerts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  alert_type VARCHAR(40) NOT NULL,
  alert_date DATE NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY unique_alert (client_id, alert_type, alert_date),
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);
CREATE TABLE customer_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NULL,
  lead_id INT NULL,
  channel VARCHAR(40) DEFAULT 'whatsapp',
  message TEXT NOT NULL,
  status VARCHAR(40) DEFAULT 'pendiente',
  created_at DATETIME NOT NULL
);