CREATE DATABASE IF NOT EXISTS asisma_crm CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE asisma_crm;

-- 1. config_sistema
CREATE TABLE IF NOT EXISTS config_sistema (
    id INT AUTO_INCREMENT PRIMARY KEY,
    clave VARCHAR(100) UNIQUE NOT NULL,
    valor TEXT,
    descripcion VARCHAR(255),
    grupo VARCHAR(50) DEFAULT 'general',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. regiones
CREATE TABLE IF NOT EXISTS regiones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    codigo VARCHAR(10),
    activa BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. verticales
CREATE TABLE IF NOT EXISTS verticales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    descripcion TEXT,
    ce_family_id INT, -- ID de familia en Control Empresas
    activa BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. marcas
CREATE TABLE IF NOT EXISTS marcas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vertical_id INT NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    contacto_nombre VARCHAR(255),
    contacto_email VARCHAR(255),
    contacto_telefono VARCHAR(50),
    ce_subfamily_id INT, -- ID de subfamilia en Control Empresas
    activa BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vertical_id) REFERENCES verticales(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. productos
CREATE TABLE IF NOT EXISTS productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vertical_id INT NOT NULL,
    marca_id INT NOT NULL,
    nombre VARCHAR(255) NOT NULL,
    descripcion TEXT,
    precio_minimo DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    precio_sugerido DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    ce_articulo_id INT, -- ID de artículo en Control Empresas
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vertical_id) REFERENCES verticales(id) ON DELETE CASCADE,
    FOREIGN KEY (marca_id) REFERENCES marcas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. empresas_franquiciados
CREATE TABLE IF NOT EXISTS empresas_franquiciados (
    id INT AUTO_INCREMENT PRIMARY KEY,
    razon_social VARCHAR(255) NOT NULL,
    nombre_fantasia VARCHAR(255) NOT NULL,
    rut VARCHAR(50) NOT NULL,
    email VARCHAR(255),
    telefono VARCHAR(50),
    direccion TEXT,
    api_facturacion_url VARCHAR(500),
    api_key_facturacion VARCHAR(255),
    ce_empresa_rut VARCHAR(50), -- RUT en Control Empresas para sincronización
    config_json LONGTEXT DEFAULT NULL,
    activa BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. franquiciados
CREATE TABLE IF NOT EXISTS franquiciados (
    id INT AUTO_INCREMENT PRIMARY KEY,
    empresa_madre_id INT NOT NULL,
    region_id INT NOT NULL,
    vertical_id INT NOT NULL,
    estado ENUM('activo','suspendido','inactivo') DEFAULT 'activo',
    tiempo_toma_lead INT DEFAULT 300, -- en segundos (5 min default)
    prioridad INT DEFAULT 0,
    max_leads_activos INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (empresa_madre_id) REFERENCES empresas_franquiciados(id) ON DELETE CASCADE,
    FOREIGN KEY (region_id) REFERENCES regiones(id),
    FOREIGN KEY (vertical_id) REFERENCES verticales(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. usuarios
CREATE TABLE IF NOT EXISTS usuarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    empresa_id INT NOT NULL,
    franquiciado_id INT DEFAULT NULL,
    rol ENUM('admin','franquiciado','vendedor','marca') NOT NULL,
    nombre VARCHAR(255) NOT NULL,
    email VARCHAR(191) UNIQUE NOT NULL, -- Reducido de 255 a 191 para compatibilidad de índice utf8mb4 en MySQL 5.6
    password_hash VARCHAR(255) NOT NULL,
    telefono VARCHAR(50),
    permisos LONGTEXT DEFAULT NULL,
    activo BOOLEAN DEFAULT TRUE,
    ultimo_login DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (empresa_id) REFERENCES empresas_franquiciados(id) ON DELETE CASCADE,
    FOREIGN KEY (franquiciado_id) REFERENCES franquiciados(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. empresas (clientes de los leads)
CREATE TABLE IF NOT EXISTS empresas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rut VARCHAR(50) NOT NULL UNIQUE,
    razon_social VARCHAR(255) NOT NULL,
    nombre_fantasia VARCHAR(255),
    giro VARCHAR(255),
    direccion TEXT,
    region_id INT DEFAULT NULL,
    telefono VARCHAR(50),
    email VARCHAR(255),
    sitio_web VARCHAR(255),
    activa TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (region_id) REFERENCES regiones(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. contactos
CREATE TABLE IF NOT EXISTS contactos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    empresa_id INT DEFAULT NULL,
    nombre VARCHAR(255) NOT NULL,
    cargo VARCHAR(100),
    telefono VARCHAR(50),
    correo VARCHAR(255),
    es_principal BOOLEAN DEFAULT FALSE,
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. leads
CREATE TABLE IF NOT EXISTS leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    empresa_id INT DEFAULT NULL,
    contacto_id INT NOT NULL,
    franquiciado_id INT DEFAULT NULL,
    usuario_asignado_id INT DEFAULT NULL,
    origen VARCHAR(50) NOT NULL,
    region_id INT DEFAULT NULL,
    vertical_interes INT DEFAULT NULL,
    marca_interes INT DEFAULT NULL,
    producto_interes INT DEFAULT NULL,
    estado_embudo ENUM('nuevo','asignado','contactado','cotizado','negociando','firma','ganado','perdido','invalido') DEFAULT 'nuevo',
    motivo_perdida VARCHAR(100) DEFAULT NULL,
    nota_perdida TEXT DEFAULT NULL,
    telefono_enmascarado VARCHAR(50) DEFAULT NULL,
    telefono_real VARCHAR(50) NOT NULL,
    correo_enmascarado VARCHAR(255) DEFAULT NULL,
    correo_real VARCHAR(255) NOT NULL,
    datos_liberados BOOLEAN DEFAULT FALSE,
    datos_liberados_at DATETIME DEFAULT NULL,
    ai_activo BOOLEAN DEFAULT TRUE,
    seguimiento_activo BOOLEAN DEFAULT TRUE,
    last_read_at DATETIME DEFAULT NULL,
    wa_instance_id INT DEFAULT NULL,
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (empresa_id) REFERENCES empresas(id),
    FOREIGN KEY (contacto_id) REFERENCES contactos(id),
    FOREIGN KEY (franquiciado_id) REFERENCES franquiciados(id) ON DELETE SET NULL,
    FOREIGN KEY (usuario_asignado_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    FOREIGN KEY (region_id) REFERENCES regiones(id),
    FOREIGN KEY (vertical_interes) REFERENCES verticales(id),
    FOREIGN KEY (marca_interes) REFERENCES marcas(id) ON DELETE SET NULL,
    FOREIGN KEY (producto_interes) REFERENCES productos(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. actividades_crm
CREATE TABLE IF NOT EXISTS actividades_crm (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id INT NOT NULL,
    usuario_id INT DEFAULT NULL,
    tipo ENUM('whatsapp','llamada','correo','reunion','nota','sistema') NOT NULL,
    direccion ENUM('entrante','saliente','interna') DEFAULT 'interna',
    asunto VARCHAR(255) NOT NULL,
    detalles TEXT,
    metadata_json LONGTEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. cotizaciones
CREATE TABLE IF NOT EXISTS cotizaciones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id INT NOT NULL,
    franquiciado_id INT NOT NULL,
    usuario_id INT NOT NULL,
    hash VARCHAR(64) UNIQUE NOT NULL,
    productos_json LONGTEXT NOT NULL, -- Lista de productos con precios, cantidades, subtotal
    subtotal DECIMAL(12,2) NOT NULL,
    descuento_valor DECIMAL(12,2) DEFAULT 0.00,
    descuento_tipo ENUM('fijo','porcentaje') DEFAULT 'fijo',
    impuesto_pct DECIMAL(5,2) DEFAULT 19.00,
    total DECIMAL(12,2) NOT NULL,
    moneda VARCHAR(10) DEFAULT 'CLP',
    notas TEXT,
    validez_horas INT DEFAULT 48,
    vencimiento_at DATETIME NOT NULL,
    estado ENUM('borrador','enviada','vista','aceptada','vencida','rechazada') DEFAULT 'borrador',
    vista_at DATETIME DEFAULT NULL,
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (franquiciado_id) REFERENCES franquiciados(id),
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. operaciones_marca (Implementación post-venta)
CREATE TABLE IF NOT EXISTS operaciones_marca (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id INT NOT NULL,
    marca_id INT NOT NULL,
    franquiciado_id INT NOT NULL,
    estado_implementacion ENUM('crear_cliente','implementacion','capacitacion','operativo','cerrado') DEFAULT 'crear_cliente',
    tipo_venta ENUM('unica','recurrente','con_inventario') DEFAULT 'unica',
    facturacion_habilitada BOOLEAN DEFAULT FALSE,
    notas TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (marca_id) REFERENCES marcas(id),
    FOREIGN KEY (franquiciado_id) REFERENCES franquiciados(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15. cola_asignacion
CREATE TABLE IF NOT EXISTS cola_asignacion (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franquiciado_id INT NOT NULL,
    usuario_id INT NOT NULL,
    orden INT NOT NULL,
    activo BOOLEAN DEFAULT TRUE,
    UNIQUE KEY uq_franquiciado_usuario (franquiciado_id, usuario_id),
    FOREIGN KEY (franquiciado_id) REFERENCES franquiciados(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15b. franquiciado_marcas: marcas que la matriz autoriza a vender a cada
-- franquiciado (dentro de su vertical). Restringe creación de leads,
-- catálogo visible, cotizaciones y la rotación en cascada.
CREATE TABLE IF NOT EXISTS franquiciado_marcas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franquiciado_id INT NOT NULL,
    marca_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_franq_marca (franquiciado_id, marca_id),
    FOREIGN KEY (franquiciado_id) REFERENCES franquiciados(id) ON DELETE CASCADE,
    FOREIGN KEY (marca_id) REFERENCES marcas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 16. asignaciones_lead
CREATE TABLE IF NOT EXISTS asignaciones_lead (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id INT NOT NULL,
    franquiciado_id INT NOT NULL,
    usuario_id INT NOT NULL,
    estado ENUM('pendiente','aceptado','timeout','rechazado') DEFAULT 'pendiente',
    asignado_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    respondido_at TIMESTAMP NULL DEFAULT NULL,
    timeout_segundos INT DEFAULT 300,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (franquiciado_id) REFERENCES franquiciados(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 17. cobros_leads
CREATE TABLE IF NOT EXISTS cobros_leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franquiciado_id INT NOT NULL,
    lead_id INT DEFAULT NULL,
    tipo ENUM('por_asignacion','por_cierre','mensual','paquete') DEFAULT 'por_asignacion',
    monto DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    estado ENUM('pendiente','cobrado','anulado') DEFAULT 'pendiente',
    periodo VARCHAR(7) NOT NULL, -- formato YYYY-MM
    factura_referencia VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (franquiciado_id) REFERENCES franquiciados(id) ON DELETE CASCADE,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 18. whatsapp_instancias
CREATE TABLE IF NOT EXISTS whatsapp_instancias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franquiciado_id INT DEFAULT NULL, -- Servidor Evolution de la franquicia (NULL = matriz)
    usuario_id INT DEFAULT NULL,      -- Vendedor dueño de la línea de WhatsApp
    nombre_instancia VARCHAR(100) NOT NULL,
    api_url VARCHAR(500) NOT NULL,
    api_token VARCHAR(255) NOT NULL,
    webhook_token VARCHAR(64) UNIQUE, -- Identifica la instancia en el webhook (?t=...)
    telefono VARCHAR(50),
    estado ENUM('conectada','desconectada','error') DEFAULT 'desconectada',
    config_json LONGTEXT DEFAULT NULL,
    activa BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (franquiciado_id) REFERENCES franquiciados(id) ON DELETE SET NULL,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 19. whatsapp_mensajes
CREATE TABLE IF NOT EXISTS whatsapp_mensajes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id INT NOT NULL,
    instancia_id INT NOT NULL,
    message_id VARCHAR(255) NOT NULL,
    direccion ENUM('entrante','saliente') NOT NULL,
    tipo ENUM('text','image','audio','video','document','sticker','reaction') DEFAULT 'text',
    contenido TEXT,
    media_url VARCHAR(500) DEFAULT NULL,
    media_path VARCHAR(500) DEFAULT NULL,
    remitente VARCHAR(50) NOT NULL,
    leido BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (instancia_id) REFERENCES whatsapp_instancias(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 20. audit_log
CREATE TABLE IF NOT EXISTS audit_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT DEFAULT NULL,
    franquiciado_id INT DEFAULT NULL,
    accion VARCHAR(100) NOT NULL,
    entidad VARCHAR(50) NOT NULL,
    entidad_id INT DEFAULT NULL,
    datos_antes LONGTEXT DEFAULT NULL,
    datos_despues LONGTEXT DEFAULT NULL,
    ip VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Índices recomendados para optimización
CREATE INDEX idx_leads_estado ON leads(estado_embudo);
CREATE INDEX idx_leads_franquiciado ON leads(franquiciado_id);
CREATE INDEX idx_leads_usuario ON leads(usuario_asignado_id);
CREATE INDEX idx_leads_region ON leads(region_id);
CREATE INDEX idx_leads_vertical ON leads(vertical_interes);
CREATE INDEX idx_actividades_lead ON actividades_crm(lead_id);
CREATE INDEX idx_cotizaciones_lead ON cotizaciones(lead_id);
CREATE INDEX idx_asignaciones_lead_rel ON asignaciones_lead(lead_id);
CREATE INDEX idx_asignaciones_lead_estado ON asignaciones_lead(estado);
CREATE INDEX idx_whatsapp_mensajes_lead ON whatsapp_mensajes(lead_id);
CREATE INDEX idx_audit_entidad ON audit_log(entidad, entidad_id);
CREATE INDEX idx_audit_usuario ON audit_log(usuario_id);
