chatbot for mermaids

chabot.py

import mysql.connector
import anthropic
from datetime import datetime
import json

# Configuración de la conexión a MySQL
db_config = {
    'host': 'localhost',
    'user': '----',
    'password': '-----',
    'database': 'db_mermas_productos',
    'charset': 'utf8mb4',
    'use_unicode': True,
    'autocommit': True
}

# Configurar el cliente de Anthropic
client = anthropic.Anthropic(api_key="-------------")

# Estructura del Data Warehouse de Mermas - Esquema Estrella
ESTRUCTURAS_TABLAS = {
    'dim_tiempo': {
        'descripcion': 'Dimensión temporal para análisis de mermas por fechas',
        'tipo': 'dimension',
        'campos': {
            'fecha_id': 'BIGINT PRIMARY KEY - Identificador único de fecha',
            'fecha': 'DATETIME - Fecha completa',
            'año': 'INT - Año (YYYY)',
            'mes': 'INT - Mes numérico (1-12)',
            'dia': 'INT - Día del mes (1-31)',
            'semestre': 'VARCHAR(20) - Semestre del año',
            'trimestre': 'VARCHAR(20) - Trimestre del año',
            'bimestre': 'VARCHAR(20) - Bimestre del año',
            'nombre_mes': 'VARCHAR(20) - Nombre del mes',
            'semana': 'INT - Número de semana del año',
            'nombre_dia': 'VARCHAR(20) - Nombre del día de la semana',
            'feriado': 'VARCHAR(5) - Indica si es feriado (Sí/No)',
            'temporada': 'VARCHAR(20) - Temporada del año',
            'turno': 'VARCHAR(20) - Turno de trabajo'
        }
    },
    'dim_motivo': {
        'descripcion': 'Dimensión de motivos de mermas y su ubicación',
        'tipo': 'dimension',
        'campos': {
            'motivo_id': 'BIGINT AUTO_INCREMENT PRIMARY KEY - Identificador único del motivo',
            'motivo': 'VARCHAR(50) - Descripción del motivo de merma',
            'ubicacion_motivo': 'VARCHAR(50) - Ubicación donde ocurre el motivo'
        }
    },
    'dim_proveedor': {
        'descripcion': 'Dimensión de proveedores y su información',
        'tipo': 'dimension',
        'campos': {
            'proveedor_id': 'BIGINT AUTO_INCREMENT PRIMARY KEY - Identificador único del proveedor',
            'pais_origen_proveedor': 'VARCHAR(50) - País de origen del proveedor',
            'procedencia_proveedor': 'VARCHAR(50) - Procedencia específica del proveedor',
            'tipo_proveedor': 'VARCHAR(50) - Tipo o categoría del proveedor'
        }
    },
    'dim_producto': {
        'descripcion': 'Dimensión de productos con características comerciales',
        'tipo': 'dimension',
        'campos': {
            'id_producto': 'BIGINT AUTO_INCREMENT PRIMARY KEY - Identificador único del producto',
            'codigo_producto': 'INT - Código interno del producto',
            'descripcion': 'VARCHAR(100) - Descripción del producto',
            'negocio': 'VARCHAR(50) - Línea de negocio del producto',
            'seccion': 'VARCHAR(50) - Sección comercial',
            'linea': 'VARCHAR(50) - Línea de productos',
            'tipo_empaque': 'VARCHAR(50) - Tipo de empaque del producto',
            'categoria': 'VARCHAR(50) - Categoría del producto',
            'origen_producto': 'VARCHAR(50) - Origen del producto',
            'abastecimiento': 'VARCHAR(50) - Tipo de abastecimiento'
        }
    },
    'dim_localizacion': {
        'descripcion': 'Dimensión geográfica y organizacional de ubicaciones',
        'tipo': 'dimension',
        'campos': {
            'localizacion_id': 'BIGINT AUTO_INCREMENT PRIMARY KEY - Identificador único de localización',
            'comuna': 'VARCHAR(50) - Comuna de la ubicación',
            'region': 'VARCHAR(10) - Región geográfica',
            'tienda': 'VARCHAR(50) - Nombre o código de tienda',
            'zonal': 'VARCHAR(50) - Zona comercial',
            'zona': 'VARCHAR(50) - Zona específica'
        }
    },
    'fact_mermas': {
        'descripcion': 'Tabla de hechos principal con métricas de mermas',
        'tipo': 'fact_table',
        'campos': {
            'fact_merma_id': 'BIGINT AUTO_INCREMENT PRIMARY KEY - Identificador único del hecho',
            'fecha_id': 'BIGINT FK - Referencia a dim_tiempo',
            'motivo_id': 'BIGINT FK - Referencia a dim_motivo',
            'proveedor_id': 'BIGINT FK - Referencia a dim_proveedor',
            'id_producto': 'BIGINT FK - Referencia a dim_producto',
            'localizacion_id': 'BIGINT FK - Referencia a dim_localizacion',
            'unidades_merma_p': 'DECIMAL(18,2) - Cantidad en unidades de merma',
            'monto_merma_p': 'DECIMAL(18,2) - Valor monetario de la merma en pesos'
        },
        'relaciones': [
            'INNER JOIN dim_tiempo ON fact_mermas.fecha_id = dim_tiempo.fecha_id',
            'INNER JOIN dim_motivo ON fact_mermas.motivo_id = dim_motivo.motivo_id',
            'INNER JOIN dim_proveedor ON fact_mermas.proveedor_id = dim_proveedor.proveedor_id',
            'INNER JOIN dim_producto ON fact_mermas.id_producto = dim_producto.id_producto',
            'INNER JOIN dim_localizacion ON fact_mermas.localizacion_id = dim_localizacion.localizacion_id'
        ]
    }
}

def validar_contexto_mermas(pregunta):
    """Valida si la pregunta está relacionada con el análisis de mermas"""
    
    # Palabras clave relacionadas con mermas y el contexto del negocio
    palabras_mermas = [
        # Conceptos principales de mermas
        'merma', 'mermas', 'pérdida', 'pérdidas', 'desperdicio', 'desecho',
        'vencimiento', 'caducidad', 'deterioro', 'daño', 'rotura',
        
        # Métricas y análisis
        'monto', 'valor', 'unidades', 'cantidad', 'total', 'suma',
        'promedio', 'máximo', 'mínimo', 'ranking', 'top',
        
        # Dimensiones del negocio
        'producto', 'productos', 'artículo', 'artículos', 'item', 'items',
        'proveedor', 'proveedores', 'supplier',
        'tienda', 'tiendas', 'local', 'locales',
        'región', 'regiones', 'comuna', 'comunas', 'zona', 'zonas',
        'ubicación', 'ubicaciones', 'localización',
        
        # Tiempo y fechas
        'fecha', 'fechas', 'día', 'días', 'mes', 'meses', 'año', 'años',
        'semana', 'semanas', 'trimestre', 'semestre', 'periodo',
        'tendencia', 'temporal', 'histórico',
        
        # Motivos y causas
        'motivo', 'motivos', 'causa', 'causas', 'razón', 'razones',
        'origen', 'tipo', 'categoría', 'clasificación',
        
        # Términos comerciales/retail
        'negocio', 'sección', 'línea', 'categoría', 'empaque',
        'abastecimiento', 'stock', 'inventario',
        
        # Análisis y consultas
        'análisis', 'reporte', 'informe', 'estadística', 'comparar',
        'mostrar', 'listar', 'buscar', 'encontrar', 'cuál', 'cuáles',
        'dónde', 'cuándo', 'cómo', 'por qué', 'qué'
    ]
    
    # Temas claramente fuera de contexto
    temas_fuera_contexto = [
        # Temas personales
        'saludo', 'hola', 'buenos días', 'buenas tardes', 'cómo estás',
        'nombre', 'edad', 'familia', 'personal',
        
        # Temas generales no relacionados
        'clima', 'tiempo atmosférico', 'noticias', 'política', 'deportes',
        'entretenimiento', 'música', 'películas', 'libros',
        'recetas', 'cocina', 'viajes', 'turismo',
        'salud', 'medicina', 'ejercicio',
        'programación general', 'tecnología general',
        'matemáticas general', 'física', 'química',
        
        # Otros sistemas o bases de datos
        'ventas', 'facturación', 'contabilidad', 'recursos humanos',
        'clientes', 'marketing', 'publicidad',
        'compras', 'logística general', 'transporte'
    ]
    
    pregunta_lower = pregunta.lower()
    
    # Verificar si contiene temas claramente fuera de contexto
    for tema in temas_fuera_contexto:
        if tema in pregunta_lower:
            return False
    
    # Verificar si contiene palabras relacionadas con mermas
    palabras_encontradas = 0
    for palabra in palabras_mermas:
        if palabra in pregunta_lower:
            palabras_encontradas += 1
    
    # Si encuentra al menos 1 palabra relacionada con mermas, se considera válida
    # Si no encuentra ninguna, se considera fuera de contexto
    return palabras_encontradas > 0

def mostrar_tablas_disponibles():
    """Muestra las tablas disponibles organizadas por tipo"""
    print("\n" + "="*60)
    print("📊 DATA WAREHOUSE DE MERMAS - TABLAS DISPONIBLES")
    print("="*60)
    
    # Mostrar dimensiones
    print("\n🔹 TABLAS DIMENSIÓN:")
    for tabla, info in ESTRUCTURAS_TABLAS.items():
        if info['tipo'] == 'dimension':
            print(f"   • {tabla}: {info['descripcion']}")
    
    # Mostrar tabla de hechos
    print("\n⭐ TABLA DE HECHOS:")
    for tabla, info in ESTRUCTURAS_TABLAS.items():
        if info['tipo'] == 'fact_table':
            print(f"   • {tabla}: {info['descripcion']}")
    
    print("\n💡 Tip: Usa 'estructura [nombre_tabla]' para ver detalles específicos")
    print("="*60)

def mostrar_estructura_tabla(nombre_tabla):
    """Muestra la estructura detallada de una tabla específica"""
    if nombre_tabla in ESTRUCTURAS_TABLAS:
        tabla_info = ESTRUCTURAS_TABLAS[nombre_tabla]
        tipo_icono = "🔹" if tabla_info['tipo'] == 'dimension' else "⭐"
        
        print(f"\n{tipo_icono} ESTRUCTURA DE {nombre_tabla.upper()}")
        print("="*50)
        print(f"Tipo: {tabla_info['tipo'].upper()}")
        print(f"Descripción: {tabla_info['descripcion']}")
        print("\nCampos:")
        
        for campo, descripcion in tabla_info['campos'].items():
            tipo_campo = descripcion.split(' - ')[0]
            desc_campo = descripcion.split(' - ', 1)[1] if ' - ' in descripcion else ''
            print(f"  📋 {campo}: {tipo_campo}")
            if desc_campo:
                print(f"      └─ {desc_campo}")
        
        # Mostrar relaciones si es tabla de hechos
        if 'relaciones' in tabla_info:
            print("\n🔗 Relaciones (JOINs típicos):")
            for relacion in tabla_info['relaciones']:
                print(f"   {relacion}")
        
        print("="*50)
    else:
        print(f"❌ Tabla '{nombre_tabla}' no encontrada.")

def generar_contexto_datawarehouse():
    """Genera el contexto específico del data warehouse de mermas"""
    contexto = """CONTEXTO: DATA WAREHOUSE DE MERMAS - ESQUEMA ESTRELLA

ESTRUCTURA GENERAL:
- Tabla de hechos central: fact_mermas (contiene métricas de mermas)
- 5 Dimensiones conectadas: tiempo, motivo, proveedor, producto, localización

TABLAS Y RELACIONES:

"""
    
    for tabla, info in ESTRUCTURAS_TABLAS.items():
        contexto += f"TABLA: {tabla} ({info['tipo'].upper()})\n"
        contexto += f"Descripción: {info['descripcion']}\n"
        contexto += "Campos:\n"
        for campo, descripcion in info['campos'].items():
            tipo = descripcion.split(' - ')[0]
            contexto += f"  {campo} {tipo}\n"
        contexto += "\n"
    
    contexto += """MÉTRICAS PRINCIPALES:
- unidades_merma_p: Cantidad física de productos perdidos
- monto_merma_p: Valor monetario de las mermas en pesos chilenos

ANÁLISIS TÍPICOS:
- Mermas por tiempo (tendencias temporales)
- Mermas por motivo (causas principales)
- Mermas por producto (productos más afectados)
- Mermas por ubicación (análisis geográfico)
- Mermas por proveedor (análisis de proveedores)
"""
    
    return contexto

def identificar_contexto_consulta(pregunta):
    """Identifica el tipo de análisis requerido según la pregunta"""
    pregunta_lower = pregunta.lower()
    
    contextos = {
        'temporal': ['año', 'mes', 'día', 'fecha', 'semana', 'trimestre', 'semestre', 'tiempo', 'periodo', 'temporal'],
        'motivo': ['motivo', 'causa', 'razón', 'por qué', 'debido', 'origen'],
        'producto': ['producto', 'artículo', 'item', 'código', 'descripción', 'categoría', 'línea', 'negocio'],
        'geografico': ['región', 'comuna', 'tienda', 'zona', 'ubicación', 'donde', 'localización'],
        'proveedor': ['proveedor', 'supplier', 'abastecedor', 'país', 'procedencia'],
        'financiero': ['monto', 'valor', 'dinero', 'pesos', 'costo', 'pérdida', 'monetario'],
        'volumetrico': ['unidades', 'cantidad', 'volumen', 'cuánto', 'total']
    }
    
    contextos_detectados = []
    for contexto, palabras in contextos.items():
        if any(palabra in pregunta_lower for palabra in palabras):
            contextos_detectados.append(contexto)
    
    return contextos_detectados if contextos_detectados else ['general']

def obtener_consulta_sql(pregunta):
    """Genera la consulta SQL optimizada para el data warehouse de mermas"""
    contextos = identificar_contexto_consulta(pregunta)
    contexto_dw = generar_contexto_datawarehouse()
    
    prompt = f"""{contexto_dw}

PREGUNTA DEL USUARIO: "{pregunta}"
CONTEXTOS DETECTADOS: {', '.join(contextos)}

Genera una consulta SQL para MySQL que responda la pregunta sobre el data warehouse de mermas.

REGLAS PARA CONSULTAS DE DATA WAREHOUSE:

ESTRUCTURA Y JOINS:
1. La tabla principal es fact_mermas (tabla de hechos)
2. Une las dimensiones necesarias usando INNER JOIN
3. Usa los campos de relación correctos (fecha_id, motivo_id, etc.)
4. Para análisis completos, incluye campos descriptivos de las dimensiones

MÉTRICAS Y AGREGACIONES:
5. Para totales de mermas: SUM(monto_merma_p) AS total_merma_pesos
6. Para cantidades: SUM(unidades_merma_p) AS total_unidades_merma
7. Para promedios: AVG(monto_merma_p), AVG(unidades_merma_p)
8. Para conteos: COUNT(*) AS cantidad_registros
9. Usa GROUP BY para análisis por dimensiones

FILTROS Y BÚSQUEDAS:
10. Para fechas: WHERE dim_tiempo.año = XXXX, dim_tiempo.mes = XX
11. Para texto: WHERE LOWER(campo) LIKE LOWER('%texto%')
12. Para rangos: WHERE monto_merma_p BETWEEN valor1 AND valor2

ORDENAMIENTO Y LIMITACIÓN:
13. ORDER BY para resultados ordenados (típicamente por monto DESC)
14. LIMIT 100 para limitar resultados
15. Para TOP N: ORDER BY SUM(monto_merma_p) DESC LIMIT N

FUNCIONES MYSQL ESPECÍFICAS:
16. Para fechas: DATE_FORMAT(fecha, '%Y-%m'), YEAR(fecha), MONTH(fecha)
17. Para números: FORMAT(monto_merma_p, 0) para formato con comas
18. Para concatenar: CONCAT(campo1, ' - ', campo2)

ANÁLISIS TÍPICOS:
19. Ranking de productos con más mermas
20. Tendencias temporales de mermas
21. Análisis por motivos de merma
22. Comparativas por región/tienda
23. Análisis de proveedores con mayores mermas

Solo responde con la consulta SQL optimizada, sin explicaciones adicionales."""

    message = client.messages.create(
        model="claude-3-haiku-20240307",
        max_tokens=2000,
        temperature=0,
        messages=[
            {
                "role": "user",
                "content": prompt
            }
        ]
    )

    return message.content[0].text.strip()

def ejecutar_sql(sql):
    """Ejecuta la consulta SQL y retorna los resultados"""
    try:
        conn = mysql.connector.connect(**db_config)
        cur = conn.cursor(dictionary=True)
        cur.execute(sql)
        results = cur.fetchall()
        cur.close()
        conn.close()
        return results
    except mysql.connector.Error as err:
        raise Exception(f"Error MySQL: {err}")
    except Exception as e:
        raise Exception(f"Error al ejecutar consulta: {e}")

def generar_respuesta_final(resultado_sql, pregunta):
    """Genera la respuesta final optimizada para análisis de mermas"""
    contextos = identificar_contexto_consulta(pregunta)
    
    prompt = f"""PREGUNTA: "{pregunta}"
CONTEXTO DE ANÁLISIS: {', '.join(contextos)}

RESULTADOS DEL DATA WAREHOUSE DE MERMAS:
{json.dumps(resultado_sql, ensure_ascii=False, indent=2, default=str)}

Genera una respuesta profesional para análisis de mermas siguiendo estas reglas:

FORMATO DE RESPUESTA:
1. Respuesta directa y clara, sin términos técnicos
2. Enfoque en insights de negocio sobre mermas
3. Usa terminología del retail/comercial apropiada

FORMATO DE NÚMEROS:
4. Montos en pesos chilenos: $1.500.000 (punto separador de miles)
5. Unidades con separador de miles: 1.500 unidades
6. Porcentajes con 1 decimal: 15,5%
7. Fechas en formato dd/mm/yyyy cuando corresponda

ESTRUCTURA DE DATOS:
8. Para rankings: presenta del mayor al menor
9. Para tendencias temporales: orden cronológico
10. Para comparativas: resalta diferencias significativas
11. Si hay muchos resultados, agrupa o muestra los más relevantes

CONTEXTO DE NEGOCIO:
12. Interpreta las mermas como pérdidas comerciales
13. Relaciona motivos con procesos operacionales
14. Conecta ubicaciones con performance de tiendas/zonas
15. Vincula productos con categorías comerciales

LIMITACIONES:
16. Solo entrega información explícita en los resultados
17. Sin especulaciones o recomendaciones no solicitadas
18. Si no hay datos, indica disponibilidad limitada
19. Mantén objetividad en el análisis
20. Si una pregunta no puede responderse, indica que no hay datos suficientes

La respuesta debe ser útil para la toma de decisiones comerciales sobre control de mermas."""

    message = client.messages.create(
        model="claude-3-haiku-20240307",
        max_tokens=1500,
        temperature=0,
        messages=[
            {
                "role": "user",
                "content": prompt
            }
        ]
    )

    return message.content[0].text.strip()

def mostrar_menu():
    """Muestra el menú principal del sistema"""
    print("\n" + "="*65)
    print("📊 SISTEMA DE ANÁLISIS DE MERMAS - DATA WAREHOUSE")
    print("="*65)
    print("🔍 COMANDOS DISPONIBLES:")
    print("   • 'tablas' - Ver estructura del data warehouse")
    print("   • 'estructura [tabla]' - Detalles de una tabla específica")
    print("   • 'ejemplos' - Ver ejemplos de consultas típicas")
    print("   • 'salir' - Finalizar el sistema")
    print("   • O escribe tu pregunta sobre mermas")
    print("-"*65)
    print("💡 EJEMPLOS DE CONSULTAS:")
    print("   - '¿Cuáles son los productos con más mermas este año?'")
    print("   - 'Mermas por motivo en la región metropolitana'")
    print("   - 'Tendencia mensual de mermas en 2024'")
    print("="*65)

def mostrar_ejemplos():
    """Muestra ejemplos de consultas típicas"""
    ejemplos = [
        "¿Cuáles son los 10 productos con mayores mermas en monto?",
        "Muestra las mermas totales por mes en 2024",
        "¿Qué motivos generan más mermas en valor?",
        "Mermas por región ordenadas de mayor a menor",
        "¿Cuáles proveedores tienen productos con más mermas?",
        "Tendencia de mermas por trimestre",
        "Top 5 tiendas con mayores pérdidas por mermas",
        "Mermas en productos de la categoría 'lácteos'",
        "¿En qué días de la semana hay más mermas?",
        "Comparar mermas entre temporadas del año"
    ]
    
    print("\n📋 EJEMPLOS DE CONSULTAS TÍPICAS:")
    print("="*50)
    for i, ejemplo in enumerate(ejemplos, 1):
        print(f"{i:2d}. {ejemplo}")
    print("="*50)

def mostrar_mensaje_fuera_contexto():
    """Muestra mensaje cuando la pregunta está fuera de contexto"""
    print("\n⚠️  CONSULTA FUERA DE CONTEXTO")
    print("="*50)
    print("❌ Esta pregunta no puede responderse porque está fuera del")
    print("   contexto del sistema de análisis de mermas.")
    print()
    print("🔍 ESTE SISTEMA ESTÁ DISEÑADO PARA ANALIZAR:")
    print("   • Mermas y pérdidas de productos")
    print("   • Tendencias temporales de mermas")
    print("   • Análisis por motivos de merma")
    print("   • Comparativas por ubicación/tienda")
    print("   • Análisis de productos y proveedores")
    print("   • Métricas de unidades y montos perdidos")
    print()
    print("💡 Prueba con consultas como:")
    print("   - '¿Cuáles productos tienen más mermas?'")
    print("   - 'Mermas por región este año'")
    print("   - 'Tendencia mensual de pérdidas'")
    print("="*50)

def main():
    """Función principal del sistema de análisis de mermas"""
    mostrar_menu()
    
    while True:
        try:
            pregunta = input("\n🔍 Ingresa tu consulta: ").strip()
            
            # Comandos especiales
            if pregunta.lower() == 'salir':
                print("👋 Sistema finalizado. ¡Hasta luego!")
                break
            elif pregunta.lower() == 'tablas':
                mostrar_tablas_disponibles()
                continue
            elif pregunta.lower() == 'ejemplos':
                mostrar_ejemplos()
                continue
            elif pregunta.lower().startswith('estructura '):
                nombre_tabla = pregunta[11:].strip()
                mostrar_estructura_tabla(nombre_tabla)
                continue
            elif not pregunta:
                print("⚠️  Por favor ingresa una consulta válida.")
                continue
            
            # VALIDACIÓN CRUCIAL: Verificar si la pregunta está en contexto
            if not validar_contexto_mermas(pregunta):
                mostrar_mensaje_fuera_contexto()
                continue
            
            # Procesar consulta de mermas (solo si pasó la validación)
            print("⏳ Analizando datos de mermas...")
            
            # Generar y mostrar SQL
            sql_query = obtener_consulta_sql(pregunta)
            print(f"📋 Consulta SQL: {sql_query}")
            
            # Ejecutar consulta
            sql_resultados = ejecutar_sql(sql_query)
            print(f"📊 Registros procesados: {len(sql_resultados)}")
            
            # Generar respuesta de análisis
            respuesta_final = generar_respuesta_final(sql_resultados, pregunta)
            print(f"\n✅ ANÁLISIS DE MERMAS:")
            print("-" * 50)
            print(respuesta_final)
            print("-" * 50)
            
        except KeyboardInterrupt:
            print("\n\n👋 Sistema interrumpido. ¡Hasta luego!")
            break
        except Exception as e:
            print(f"❌ Error en el análisis: {e}")
            print("💡 Verifica tu consulta o la conexión a la base de datos.")

if __name__ == "__main__":
    main()