Máster Data Science, Big Data & Business Analytics 2024–2025 (Clase 4) — Grupo 4

TFM: Pon aquí el título de tu TFM

Listo

¡Hola! Escribe abajo y te respondo en tiempo real. El contexto del TFM ya está cargado.

CÓDIGO:

import gradio as gr
#import requests
import tempfile
import os
from datetime import datetime, timedelta
import time
import pandas as pd
import re
import json
#from rapidfuzz import process, fuzz
import matplotlib.pyplot as plt
import plotly.express as px
import unicodedata
import difflib
from openai import OpenAI
import webbrowser
import threading
import base64
from pathlib import Path

# Configuración de la conexión a la base de datos:
from sqlalchemy import create_engine, text
from functools import partial

import gradio as gr
#import requests
import tempfile
import os
from datetime import datetime
import time
import pandas as pd
import re
import json
#from rapidfuzz import process, fuzz
import matplotlib.pyplot as plt
import plotly.express as px
import unicodedata
import difflib
from openai import OpenAI
import webbrowser
import threading
import base64
from pathlib import Path

# Configuración de la conexión a la base de datos:
from sqlalchemy import create_engine, text
from functools import partial, lru_cache  # <-- añadido lru_cache

ENGINE = create_engine(
    "mssql+pyodbc://TFM_Sandoz:Zodnas_MFT@tfm-sandoz.database.windows.net/TFM_Sandoz"
    "?driver=ODBC+Driver+17+for+SQL+Server&TrustServerCertificate=yes",
    pool_pre_ping=True, pool_recycle=1800
)
pd.read_sql = partial(pd.read_sql, con=ENGINE)

os.chdir(r"C:\Users\josem\Documents\MASTER\TFM")
ruta_actual = os.getcwd()

# ==================================
# IMPORTS DE LANGCHAIN
# ==================================
#from langchain_openai import OpenAIEmbeddings
#from langchain_community.vectorstores import FAISS (Las dejo comentadas porque no se están usando para nada)
#from langchain.chains import RetrievalQA
#from langchain_openai import ChatOpenAI

# ==================================
# CONFIGURACIÓN OPENAI
# ==================================
os.chdir(r"C:\Users\josem\Documents\MASTER\TFM")
with open("API_KEY.txt") as f:
    OPENAI_API_KEY = f.read().strip()
os.chdir(ruta_actual)

client = OpenAI(api_key=OPENAI_API_KEY)

# ==================================
# FUNCIÓN PARA NORMALIZAR NOMBRES DE CUENTAS
# ==================================
def normalize_account(account):
    """
    Normaliza un nombre de cuenta: lo pasa a minúsculas, elimina espacios extra y remueve diacríticos.
    """
    account = account.lower().strip()
    account = unicodedata.normalize('NFKD', account).encode('ASCII', 'ignore').decode('utf-8')
    return account
    
# ==================================
# [EMC] Normalización de texto para productos 
# ==================================
def _norm_txt(s: str) -> str:
    s = str(s or "").strip().lower()
    try:
        return unicodedata.normalize("NFKD", s).encode("ASCII", "ignore").decode("utf-8")
    except Exception:
        return s

# ==================================
# CAPA DE ACCESO A DATOS (AZURE SQL)
# ==================================
def query_df(sql: str, params: dict | None = None) -> pd.DataFrame:
    return pd.read_sql(text(sql), con=ENGINE, params=params)

def execute_sql(sql: str, params: dict | None = None):
    with ENGINE.begin() as conn:
        return conn.execute(text(sql), params or {})

def get_or_create_cuenta(cuenta: str) -> int:
    row = query_df(
        """
        SELECT TOP 1 id_cuenta
        FROM cuentas
        WHERE LOWER(cuenta) = LOWER(:cuenta)
        """,
        {"cuenta": cuenta}
    )
    if not row.empty:
        return int(row.iloc[0]["id_cuenta"])
    res = execute_sql(
        """
        INSERT INTO cuentas (cuenta)
        OUTPUT inserted.id_cuenta
        VALUES (:cuenta)
        """,
        {"cuenta": cuenta}
    )
    inserted = res.fetchone()
    return int(inserted[0])

def get_usuario_by_credentials(usuario: str, password: str):
    dfu = query_df(
        """
        SELECT TOP 1 id_usuario, usuario, representante
        FROM usuarios
        WHERE usuario = :usuario AND [contraseña] = :password
        """,
        {"usuario": usuario, "password": password}
    )
    if dfu.empty:
        return None
    row = dfu.iloc[0]
    return int(row["id_usuario"]), row["representante"], row["usuario"]

def get_producto_by_nombre(nombre: str) -> int | None:
    dfp = query_df(
        """
        SELECT TOP 1 id_producto
        FROM porfolio
        WHERE LOWER(producto) = LOWER(:nombre)
        """,
        {"nombre": nombre}
    )
    if dfp.empty:
        return None
    return int(dfp.iloc[0]["id_producto"])

# === [EMC] : resolver de id_producto con coincidencia exacta, aproximada y fonética ===
def get_producto_id_resolver(nombre: str) -> int | None:
    """
    Devuelve id_producto para 'nombre' usando la tabla 'porfolio' con varias estrategias:
      1) Coincidencia EXACTA en 'producto' (insensible a mayúsculas).
      2) Coincidencia tipo LIKE (insensible a acentos y mayúsculas) en 'producto'.
      3) Coincidencia FONÉTICA (SQL Server DIFFERENCE) en 'producto'.
      4) (Opcional) Repite 1–3 sobre la columna 'molecula' si existe.
    Retorna None si no encuentra nada.
    """
    # 1) EXACTA por 'producto'
    df = query_df(
        """
        SELECT TOP 1 id_producto
        FROM porfolio
        WHERE LOWER(producto) = LOWER(:nombre)
        """,
        {"nombre": nombre}
    )
    if not df.empty:
        return int(df.iloc[0]["id_producto"])

# 2) LIKE insensible a acentos por 'producto'
    df = query_df(
        """
        SELECT TOP 1 id_producto
        FROM porfolio
        WHERE producto COLLATE Latin1_General_CI_AI LIKE :pat
        ORDER BY LEN(producto) ASC
        """,
        {"pat": f"%{nombre}%"}
    )
    if not df.empty:
        return int(df.iloc[0]["id_producto"])

# 3) FONÉTICA por 'producto' (SOUNDEX/DIFFERENCE)
    df = query_df(
        """
        SELECT TOP 1 id_producto
        FROM porfolio
        WHERE DIFFERENCE(producto, :nombre) >= 3
        ORDER BY DIFFERENCE(producto, :nombre) DESC,
                 ABS(LEN(producto) - LEN(:nombre)) ASC
        """,
        {"nombre": nombre}
    )
    if not df.empty:
        return int(df.iloc[0]["id_producto"])

def _truncate_for_db(value: str, limit: int | None = None) -> str:
    if value is None or limit is None:
        return value
    if len(value) <= limit:
        return value
    return value[: max(0, limit - 1)] + "…"

def insert_visita(
    id_cuenta: int,
    id_usuario: int,
    fecha,            # datetime | date | str
    puntos_clave: str,
    contacto: str,
) -> int:
    # 1) Normaliza fecha a 'date' (la columna es DATE)
    if isinstance(fecha, datetime):
        fecha = fecha.date()
    elif isinstance(fecha, str):
        # Acepta 'YYYY-MM-DD' o 'YYYY-MM-DD HH:MM:SS'
        try:
            fecha = datetime.fromisoformat(fecha.replace("Z","")).date()
        except ValueError:
            # último recurso: solo los 10 primeros caracteres 'YYYY-MM-DD'
            fecha = fecha[:10]

# 2) Ajusta longitudes si tu esquema tiene límites
    puntos_clave = _truncate_for_db(puntos_clave, 1000)   # VARCHAR(1000)
    contacto     = _truncate_for_db(contacto, 150)        # ajusta si procede

params = {
        "id_cuenta": id_cuenta,
        "id_usuario": id_usuario,
        "fecha": fecha,  # -> mejor pasar date que string
        "puntos_clave": puntos_clave,
        "contacto": contacto,
    }

# 3) Inserta y lee el ID en la MISMA ejecución y MISMA conexión
    sql = text("""
        INSERT INTO dbo.visitas_clientes (id_cuenta, id_usuario, fecha, puntos_clave, contacto)
        OUTPUT INSERTED.id_visita
        VALUES (:id_cuenta, :id_usuario, :fecha, :puntos_clave, :contacto)
    """)

# Nota: NO devolvemos el Result, leemos aquí mismo el valor (evita HY010)
    with ENGINE.begin() as conn:
        new_id = conn.execute(sql, params).scalar_one()
        return int(new_id)

def insert_venta_competencia(id_visita: int, id_producto: int | None, empresa: str):
    execute_sql(
        """
        INSERT INTO ventas_competencia (id_visita, id_producto, empresa)
        VALUES (:id_visita, :id_producto, :empresa)
        """,
        {"id_visita": id_visita, "id_producto": id_producto, "empresa": empresa}
    )

# === [EMC] inserción en tabla de ventas ===
def insert_venta(id_visita: int, id_producto: int, unidades: int) -> int:
    if not isinstance(unidades, int) or unidades < 0:
        raise ValueError("unidades debe ser un entero >= 0")
    sql = text("""
        INSERT INTO dbo.ventas (id_visita, id_producto, facturacion)
        OUTPUT INSERTED.id_venta
        VALUES (:id_visita, :id_producto, :unidades)
    """)
    with ENGINE.begin() as conn:
        new_id = conn.execute(sql, {
            "id_visita": id_visita,
            "id_producto": int(id_producto),
            "unidades": int(unidades),
        }).scalar_one()
        return int(new_id)

# === [EMC] extraer ventas del texto y guardarlas en BD ===
def extract_and_insert_sales_for_visit(id_visita: int, source_text: str):
    extraidos = extract_sales_info(source_text) or []
    # normaliza
    pares = []
    for x in extraidos:
        prod = (x.get("producto") or x.get("producto_text") or "").strip()
        uds  = int(x.get("unidades") or 0)
        if prod and uds > 0:
            pares.append((prod, uds))

# resuelve y acumula por id_producto
    acumulado = {}
    no_resueltos = []
    for prod_txt, uds in pares:
        pid = (get_producto_by_nombre(prod_txt) or
               get_producto_id_resolver(prod_txt))
        if pid:
            acumulado[int(pid)] = acumulado.get(int(pid), 0) + int(uds)
        else:
            no_resueltos.append({"producto_text": prod_txt, "unidades": int(uds), "id_producto": None})

# inserta y prepara detalle
    detalle = []
    for pid, uds in acumulado.items():
        insert_venta(id_visita, pid, uds)
        detalle.append({"id_producto": int(pid), "unidades": int(uds)})

# opcional: añadir no resueltos al detalle para que se vean también
    detalle.extend(no_resueltos)

return len(acumulado), detalle

# ==================================
# FUNCIÓN DE DETECCIÓN DE CUENTA
# ==================================
def detect_entities(transcribed_text):
    """
    Detecta el nombre de la cuenta en el texto transcrito y,
    si encuentra algo, lo pone con mayúscula inicial de cada palabra (ej. 'Farmacia San Juan').
    Si no encuentra nada, devuelve 'Cuenta no detectada'.
    """
    prompt = (
        "Eres un analizador de texto que extrae el nombre de la cuenta. "
        "Dado un texto en idioma español, tu tarea es identificar el nombre de la cuenta o lugar (por ejemplo, 'Clínica San Juan', 'Farmacia Central', etc.) que se menciona en el texto. "
        "Si no encuentras nada claro, devuelve 'Cuenta no detectada'.\n\n"
        "Devuelve SOLAMENTE un JSON con la clave:\n"
        "{\n"
        "  \"cuenta_detectada\": \"...\"\n"
        "}\n"
        "No añadas explicación ni texto adicional."
    )
    user_prompt = f"Texto transcrito:\n{transcribed_text}\n"
    messages = [
        {"role": "system", "content": prompt},
        {"role": "user", "content": user_prompt}
    ]
    raw_answer = call_chat_api(messages, model="gpt-4.1-nano", temperature=0)
    try:
        data = json.loads(raw_answer)
        acc = data.get("cuenta_detectada", "Cuenta no detectada").strip()
    except:
        acc = "Cuenta no detectada"
    if not acc:
        acc = "Cuenta no detectada"

# Forzamos la capitalización
    if acc.lower() != "cuenta no detectada":
        acc = " ".join([word.capitalize() for word in acc.split()])

return acc if acc else "Cuenta no detectada"

# ==============================================
# FUNCIÓN DE DETECCIÓN DE NÚMERO DE REGISTROS HISTÓRICOS
# ==============================================
def detect_record_count(transcribed_text):
    """
    Detecta el número de registros mencionado en el texto transcrito.
    Si no encuentra nada, devuelve None (para mantener el valor predeterminado).
    """
    prompt = (
        "Eres un analizador de texto que extrae el número de registros mencionados. "
        "Dado un texto en idioma español, tu tarea es identificar si se menciona un número específico "
        "de registros, historiales o resultados a mostrar o consultar. "
        "Si no encuentras ningún número específico, devuelve null.\n\n"
        "Devuelve SOLAMENTE un JSON con la clave:\n"
        "{\n"
        "  \"numero_registros\": numero o null\n"
        "}\n"
        "No añadas explicación ni texto adicional. El número debe ser un valor numérico, no texto."
    )
    user_prompt = f"Texto transcrito:\n{transcribed_text}\n"
    messages = [
        {"role": "system", "content": prompt},
        {"role": "user", "content": user_prompt}
    ]
    raw_answer = call_chat_api(messages, model="gpt-4.1-nano", temperature=0)
    try:
        data = json.loads(raw_answer)
        count = data.get("numero_registros")
        if count is not None:
            return int(count)
    except:
        pass
    return None

# ==================================
# FUNCIÓN DE DETECCIÓN DE CONTACTO
# ==================================
def detect_contact(transcribed_text):
    """
    Utiliza un prompt para extraer el nombre de la persona o departamento de contacto 
    mencionado en el texto. Si no se detecta nada, devuelve 'Contacto no detectado'.
    """
    prompt = (
        "Eres un analizador de texto. Dado un texto en español que describe una visita comercial, "
        "identifica la persona o departamento de contacto al que se hace referencia. "
        "Si no queda claro, devuelve 'Contacto no detectado'.\n\n"
        "Devuelve SOLAMENTE un JSON con la clave:\n"
        "{\n"
        "  \"contacto_detectado\": \"...\"\n"
        "}\n"
        "No añadas explicación ni texto adicional."
    )
    user_prompt = f"Texto transcrito:\n{transcribed_text}\n"
    messages = [
        {"role": "system", "content": prompt},
        {"role": "user", "content": user_prompt}
    ]
    raw_answer = call_chat_api(messages, model="gpt-4.1-nano", temperature=0)
    try:
        data = json.loads(raw_answer)
        contact = data.get("contacto_detectado", "Contacto no detectado").strip()
    except:
        contact = "Contacto no detectado"

# Forzamos la capitalización
    if contact.lower() != "contacto no detectado":
        contact = " ".join(word.capitalize() for word in contact.split())

return contact or "Contacto no detectado"

# ==================================
# FUNCIONES AUXILIARES / AUDIO / TRANSCRIPCIÓN
# ==================================
def call_openai_audio_transcription(audio_path):
    try:
        with open(audio_path, "rb") as audio_file:
            transcription = client.audio.transcriptions.create(
                model="whisper-1",
                file=audio_file
            )
        return transcription.text
    except Exception as e:
        return f"Error: {e}"

def call_chat_api(messages, model="gpt-4.1-nano", temperature=0):
    try:
        response = client.chat.completions.create(
            model=model,
            messages=messages,
            temperature=temperature
        )
        return response.choices[0].message.content
    except Exception as e:
        return f"Error: {e}"

def call_chat_api_simple(prompt, model="gpt-4.1-nano", temperature=0):
    try:
        resp = client.chat.completions.create(
            model=model,
            temperature=temperature,
            messages=[{"role": "user", "content": prompt}]
        )
        return resp.choices[0].message.content
    except Exception as e:
        return f"Error: {e}"

def semantic_filter_transcription(raw_transcription):
    """
    Filtra datos sensibles (nombres de pacientes, direcciones, precios específicos, etc.).
    Conserva: nombre de la cuenta/lugar, contacto y cualquier mención de competencia (empresa y/o productos),
    además del contenido de negocio relevante.
    """
    prompt = f"""
Eres un asistente de procesamiento de transcripciones para un sistema de CRM de ventas. 
Tu tarea es filtrar la siguiente transcripción de una reunión de negocios para eliminar 
información irrelevante y dejar únicamente los datos esenciales para la empresa.

Debes **eliminar**:
- Charlas triviales como saludos, despedidas y comentarios sin valor para el negocio.
- Conversaciones personales sobre vacaciones, familia, deportes, clima o temas no relacionados con la empresa.
- Frases de cortesía o relleno como “qué tal” etc.
- Información repetitiva que no aporte valor a la conversación.

Debes **mantener**:
- Información sobre la cuenta visitada.
- Preguntas y respuestas relacionadas con pedidos, productos, entregas y necesidades del cliente.
- Compromisos de acción, acuerdos o seguimientos.
- Cualquier comentario relevante para la estrategia comercial.
- El nombre de la persona de contacto o departamento mencionado.
- Menciones de competidores (empresa y/o productos) relevantes para la estrategia.

Por favor, devuelve únicamente la transcripción limpia, sin explicaciones adicionales.

Transcripción:
{raw_transcription}
"""
    filtered_text = call_chat_api_simple(prompt)
    return filtered_text

# ==================================
# FUNCIÓN DE GENERAR RESUMEN
# ==================================
def generate_plain_text_summary(filtered_transcription, final_account, visit_date, historical_summary):
    """
    Genera un resumen en texto plano con los apartados:
      - Cuenta
      - Fecha
      - Histórico
      - Resumen de la visita
      - Puntos clave (basados únicamente en la visita actual)
    """
    prompt = f"""
Eres un asistente de CRM para Sandoz. Genera un resumen de la visita en formato de texto plano, 
utilizando saltos de línea para separar claramente cada apartado. El resumen debe contener los siguientes apartados:

Cuenta: {final_account}
Fecha: {visit_date}

Histórico:
{historical_summary}

Resumen de la visita:
[Genera un resumen claro y conciso de la visita basándote en la siguiente transcripción:]
{filtered_transcription}

Puntos clave:
[Extrae y resume exclusivamente los puntos clave de la visita actual basándote en la siguiente transcripción. No incluyas información de visitas anteriores:]
{filtered_transcription}

Por favor, devuelve el resumen estructurado con la siguiente estructura:

Cuenta: <nombre de la cuenta>
Fecha: <fecha y hora>
Histórico:
<resumen histórico en varias líneas>
Resumen de la visita:
<resumen de la visita actual en varias líneas>
Puntos clave:
<puntos clave de la visita actual en varias líneas>
"""
    response = call_chat_api_simple(prompt)
    return response.strip()

def save_text_to_file(text, filename):
    temp_dir = tempfile.gettempdir()
    file_path = os.path.join(temp_dir, filename)
    with open(file_path, "w", encoding="utf-8") as f:
        f.write(text)
    return file_path

def extract_puntos_clave(gpt_output):
    """
    Extrae la sección de 'Puntos clave:' del texto generado por GPT.
    """
    pattern = r"Puntos clave:\s*(.+)$"
    match = re.search(pattern, gpt_output, re.DOTALL | re.IGNORECASE)
    if match:
        return match.group(1).strip()
    else:
        return gpt_output

# ==================================
# DETECCIÓN DE COMPETENCIA EN TRANSCRIPCIONES
# ==================================
def detect_competitor_mentions(transcribed_text: str):
    """
    Devuelve una lista de menciones de competencia con el formato:
    [ {"empresa": "...", "producto": "..." | null } ]
    """
    prompt = (
       "Eres un extractor de inteligencia competitiva de la empresa Sandoz. Dado un texto en español, "
        "identifica menciones de competidores (otros nombres de la empresas) y, si se menciona, el producto.\n\n"
        "Responde SOLO con un JSON de la forma:\n"
        "{\n  \"competencia\": [ {\"empresa\": \"...\", \"producto\": \"...\"}, ... ]\n}\n"
        "Si no hay producto claro, usa null en producto. No añadas nada más."
    )
    messages = [
        {"role": "system", "content": prompt},
        {"role": "user", "content": transcribed_text or ""}
    ]
    raw = call_chat_api(messages, model="gpt-4.1-nano", temperature=0)
    try:
        data = json.loads(raw)
        items = data.get("competencia", []) or []
        out = []
        for it in items:
            empresa = (it.get("empresa") or "").strip()
            producto = it.get("producto")
            producto = (producto or "").strip() or None
            if empresa:
                out.append({"empresa": empresa, "producto": producto})
        return out
    except Exception:
        return []

# === [EMC] Agente para extraer ventas (producto + unidades) desde la transcripción ===
def extract_sales_info(transcribed_text: str) -> list[dict]:
    system_prompt = (
        "Eres un extractor de VENTAS de Sandoz. Lee un diálogo comercial en español y devuelve SOLO un JSON con la forma:\n"
        "{\n"
        "  \"ventas\": [\n"
        "    {\"producto\": \"<nombre o categoría>\", \"unidades\": <entero>}\n"
        "  ]\n"
        "}\n"
        "Reglas:\n"
        "- Extrae únicamente ventas cerradas (lo que el cliente se lleva ahora).\n"
        "- 'unidades' debe ser ENTERO (convierte palabras: 'veinte'->20).\n"
        "- Si no hay ventas claras, devuelve ventas: [].\n"
        "- No añadas texto fuera del JSON."
    )
    messages = [
        {"role": "system", "content": system_prompt},
        {"role": "user", "content": transcribed_text or ""}
    ]
    raw = call_chat_api(messages, model="gpt-4.1-nano", temperature=0)
    out = []
    try:
        data = json.loads(raw)
        for it in data.get("ventas", []) or []:
            prod = str(it.get("producto", "")).strip()
            unidades = it.get("unidades", None)
            try:
                unidades = int(unidades)
            except Exception:
                continue
            if prod and unidades and unidades > 0:
                out.append({"producto": prod, "unidades": int(unidades)})
    except Exception:
        pass
    return out

# ==================================
# (NUEVO) HELPERS PARA AGENTE ESTADÍSTICO
# ==================================
def _fmt_num(x: float) -> str:
    try:
        x = float(x)
    except Exception:
        return "0"
    return f"{x:,.0f}".replace(",", "X").replace(".", ",").replace("X", ".")

def _safe_pct(num: float, den: float) -> float | None:
    if den is None or den <= 0:
        return None
    return (num / den) * 100.0

def _fmt_pct(x: float | None) -> str:
    return "N/D" if x is None else f"{x:.1f}%"

# ==================================
# (NUEVO) FECHAS Y PERIODOS PARA AGENTE ESTADÍSTICO
# ==================================
_MESES_ES = {
    "enero":1,"febrero":2,"marzo":3,"abril":4,"mayo":5,"junio":6,
    "julio":7,"agosto":8,"septiembre":9,"setiembre":9,"octubre":10,"noviembre":11,"diciembre":12
}

def _to_ts(x):
    if pd.isna(x): return pd.NaT
    v = str(x).strip()
    for dayfirst in (True, False):
        out = pd.to_datetime(v, errors="coerce", dayfirst=dayfirst, utc=False)
        if pd.notna(out): return out.normalize()
    vlow = v.lower()
    if re.fullmatch(r"\d{4}", vlow):
        return pd.Timestamp(year=int(vlow), month=1, day=1)
    m = re.match(rf"({'|'.join(_MESES_ES.keys())})\s+(\d{{4}})", vlow)
    if m:
        mes = _MESES_ES[m.group(1)]
        anio = int(m.group(2))
        return pd.Timestamp(year=anio, month=mes, day=1)
    m = re.match(r"q([1-4])\s*(\d{4})", vlow)
    if m:
        q = int(m.group(1)); y = int(m.group(2))
        start_month = {1:1,2:4,3:7,4:10}[q]
        return pd.Timestamp(year=y, month=start_month, day=1)
    return pd.NaT

def _range_month(year:int, month:int):
    start = pd.Timestamp(year=year, month=month, day=1)
    end   = (start + pd.offsets.MonthEnd(0))
    return start.normalize(), end.normalize()

def _parse_free_date_range(texto:str, ref=None):
    """
    Devuelve (start_ts, end_ts, periodo_str) o (None, None, None) si no detecta.
    Soporta:
      - 'YTD', 'QTD', 'TTM', 'LTM'
      - 'este mes', 'último mes', 'último trimestre', 'trimestre pasado', 'acumulado trimestral', 'último año'
      - 'abril 2024', 'Q2 2025', '2024'
      - 'del 01/03/2025 al 30/06/2025'
    """
    ref = pd.Timestamp(ref or datetime.today()).normalize()
    t = (texto or "").lower()

m = re.search(r"del\s+(\d{1,2}[/-]\d{1,2}[/-]\d{2,4})\s+(?:al|a)\s+(\d{1,2}[/-]\d{1,2}[/-]\d{2,4})", t)
    if m:
        s = _to_ts(m.group(1)); e = _to_ts(m.group(2))
        if pd.notna(s) and pd.notna(e) and s <= e:
            return s, e, f"RANGO {s.date().isoformat()}→{e.date().isoformat()}"

if any(k in t for k in ["ytd", "year to date", "año a fecha", "año hasta hoy", "anual acumulado"]):
        return pd.Timestamp(year=ref.year, month=1, day=1), ref, "YTD"
    if any(k in t for k in ["qtd", "quarter to date", "trimestre a fecha", "trimestre hasta hoy", "trimestral acumulado"]):
        q = ref.quarter; sm = {1:1,2:4,3:7,4:10}[q]
        return pd.Timestamp(year=ref.year, month=sm, day=1), ref, "QTD"
    if any(k in t for k in ["ttm","ltm","últimos 12 meses","ultimos 12 meses","rolling 12","último año","ultimo año"]):
        s = (ref - pd.DateOffset(years=1)) + pd.Timedelta(days=1)
        return s, ref, "TTM"

if "este mes" in t:
        s,e = _range_month(ref.year, ref.month); return s,e,"ESTE MES"
    if "último mes" in t or "ultimo mes" in t:
        prev = ref - pd.DateOffset(months=1)
        s,e = _range_month(prev.year, prev.month); return s,e,"ÚLTIMO MES"

# NUEVOS ALIAS DE TRIMESTRE
    if ("último trimestre" in t or "ultimo trimestre" in t 
        or "trimestre pasado" in t or "acumulado trimestral" in t):
        q = (ref.quarter-1) or 4; y = ref.year if ref.quarter>1 else ref.year-1
        sm = {1:1,2:4,3:7,4:10}[q]
        s = pd.Timestamp(year=y, month=sm, day=1); e = (s + pd.offsets.QuarterEnd(0))
        return s.normalize(), e.normalize(), "ÚLTIMO TRIMESTRE"

if "último año" in t or "ultimo año" in t:
        y = ref.year-1
        s = pd.Timestamp(year=y, month=1, day=1); e = pd.Timestamp(year=y, month=12, day=31)
        return s,e,"AÑO ANTERIOR"

m = re.search(rf"({'|'.join(_MESES_ES.keys())})\s+(\d{{4}})", t)
    if m:
        mes = _MESES_ES[m.group(1)]; anio = int(m.group(2))
        s,e = _range_month(anio, mes); return s,e,f"{m.group(1).title()} {anio}"

m = re.search(r"q([1-4])\s*(\d{4})", t)
    if m:
        q = int(m.group(1)); y = int(m.group(2)); sm = {1:1,2:4,3:7,4:10}[q]
        s = pd.Timestamp(year=y, month=sm, day=1); e = (s + pd.offsets.QuarterEnd(0))
        return s.normalize(), e.normalize(), f"Q{q} {y}"

m = re.search(r"\b(20\d{2}|19\d{2})\b", t)
    if m:
        y = int(m.group(1)); s = pd.Timestamp(year=y, month=1, day=1); e = pd.Timestamp(year=y, month=12, day=31)
        return s,e,str(y)

return None, None, None

def _period_bounds(periodo: str, ref: datetime | None = None) -> tuple[pd.Timestamp, pd.Timestamp]:
    ref = pd.Timestamp(ref or datetime.today()).normalize()
    p = (periodo or "").upper()
    if p == "YTD":
        return pd.Timestamp(year=ref.year, month=1, day=1), ref
    if p == "QTD":
        q = ref.quarter
        sm = {1:1,2:4,3:7,4:10}[q]
        return pd.Timestamp(year=ref.year, month=sm, day=1), ref
    if p == "TTM":
        s = (ref - pd.DateOffset(years=1)) + pd.Timedelta(days=1)
        return s, ref
    return pd.Timestamp(year=ref.year, month=1, day=1), ref

# ==================================
# (NUEVO) CARGA DE DATOS PARA AGENTE ESTADÍSTICO
# ==================================
@lru_cache(maxsize=1)
def _load_sales_data():
    sql = """
        SELECT 
            CAST(vc.fecha AS date) AS _fecha,
            v.facturacion           AS _fact,
            c.cuenta                AS Cuenta,
            p.producto              AS Producto
        FROM ventas v
        JOIN visitas_clientes vc ON vc.id_visita = v.id_visita
        JOIN cuentas c          ON c.id_cuenta = vc.id_cuenta
        JOIN porfolio p         ON p.id_producto = v.id_producto
    """
    df = query_df(sql)
    if df.empty:
        raise ValueError("No hay datos de ventas en BD.")
    df["_fecha"]   = pd.to_datetime(df["_fecha"], errors="coerce").dt.normalize()
    df["_fact"]    = pd.to_numeric(df["_fact"], errors="coerce").fillna(0.0)
    df["Cuenta"]   = df["Cuenta"].astype(str)
    df["Producto"] = df["Producto"].astype(str)
    df["Origen"]   = "Sandoz"
    return df[["_fecha","_fact","Cuenta","Producto","Origen"]].dropna(subset=["_fecha"]).copy()

@lru_cache(maxsize=1)
def _load_competition_data():
    sql = """
        SELECT 
            CAST(v.fecha AS date) AS _fecha,
            c.cuenta              AS Cuenta,
            COALESCE(p.producto,'(sin producto)') AS Producto,
            x.empresa             AS Empresa
        FROM ventas_competencia x
        JOIN visitas_clientes v ON v.id_visita = x.id_visita
        JOIN cuentas c          ON c.id_cuenta = v.id_cuenta
        LEFT JOIN porfolio p    ON p.id_producto = x.id_producto
    """
    df = query_df(sql)
    if df.empty:
        return pd.DataFrame(columns=["_fecha","Cuenta","Producto","Empresa","_menciones","Origen"])
    df["_fecha"]   = pd.to_datetime(df["_fecha"], errors="coerce").dt.normalize()
    df["Cuenta"]   = df["Cuenta"].astype(str)
    df["Producto"] = df["Producto"].astype(str)
    df["Empresa"]  = df["Empresa"].astype(str)
    df = df.dropna(subset=["_fecha"])
    df["_menciones"] = 1.0
    df["Origen"]     = "Competencia"
    return df[["_fecha","Cuenta","Producto","Empresa","_menciones","Origen"]].copy()

# === NUEVO: función para refrescar cachés (soluciona que LY salga 0 teniendo datos 2024/2023)
def _refresh_stats_caches():
    """
    Fuerza recarga de datos desde la BBDD para evitar resultados obsoletos.
    Se llama al inicio de agente_estadistico_responder.
    """
    try:
        _load_sales_data.cache_clear()
    except Exception:
        pass
    try:
        _load_competition_data.cache_clear()
    except Exception:
        pass

# ==================================
# (NUEVO) CÁLCULOS PARA AGENTE ESTADÍSTICO
# ==================================
def _sum_between(df: pd.DataFrame, start, end, value_col="_fact") -> float:
    m = (df["_fecha"] >= start) & (df["_fecha"] <= end)
    return float(df.loc[m, value_col].sum())

def calcular_acumulado_rango(df: pd.DataFrame, start, end, value_col="_fact") -> dict:
    return {
        "inicio": start.date().isoformat(),
        "fin": end.date().isoformat(),
        "acumulado": _sum_between(df, start, end, value_col=value_col)
    }

def calcular_diferencial_vs_ly_rango(df: pd.DataFrame, start, end, value_col="_fact") -> dict:
    acum_actual = _sum_between(df, start, end, value_col=value_col)
    start_ly, end_ly = start - pd.DateOffset(years=1), end - pd.DateOffset(years=1)
    acum_ly = _sum_between(df, start_ly, end_ly, value_col=value_col)
    delta_abs = acum_actual - acum_ly
    delta_pct = None if acum_ly == 0 else (delta_abs / acum_ly) * 100.0
    return {
        "actual": {"inicio": start.date().isoformat(), "fin": end.date().isoformat(), "acumulado": acum_actual},
        "ly":     {"inicio": start_ly.date().isoformat(), "fin": end_ly.date().isoformat(), "acumulado": acum_ly},
        "delta_abs": delta_abs,
        "delta_pct": delta_pct
    }

# ==================================
# (NUEVO) DETECCIÓN DE FILTROS Y PERIODOS + INTENCIÓN
# ==================================
def _detectar_periodo_desde_texto(texto: str, ref: datetime | None = None):
    s,e,label = _parse_free_date_range(texto, ref)
    if s is not None: return (s,e,label)
    ref = pd.Timestamp(ref or datetime.today()).normalize()
    s,e = pd.Timestamp(year=ref.year, month=1, day=1), ref
    return (s,e,"YTD")

def _pide_diferencial(texto: str) -> bool:
    t = (texto or "").lower()
    keys = ["vs el año pasado", "vs año pasado", "comparado con el año pasado",
            "diferencial", "variación", "variacion", "delta", "comparar con ly", "vs ly"]
    return any(k in t for k in keys)

def _detectar_filtro(df_sandoz: pd.DataFrame, df_comp: pd.DataFrame, texto: str) -> dict:
    t = (texto or "").lower()
    out = {"cuenta": None, "producto": None, "empresa": None, "modo_competencia": False}
    if any(k in t for k in ["competencia","competidor","competidores","market","marca rival","empresa rival"]):
        out["modo_competencia"] = True
    for c in sorted(df_sandoz["Cuenta"].dropna().astype(str).unique(), key=lambda x: -len(x)):
        if c.lower() in t: out["cuenta"] = c; break
    for p in sorted(df_sandoz["Producto"].dropna().astype(str).unique(), key=lambda x: -len(x)):
        if p.lower() in t: out["producto"] = p; break
    if not df_comp.empty:
        for e in sorted(df_comp["Empresa"].dropna().astype(str).unique(), key=lambda x: -len(x)):
            if e.lower() in t:
                out["empresa"] = e
                out["modo_competencia"] = True
                break
    return out

def is_stats_intent(text: str) -> bool:
    if not text:
        return False
    t = text.lower()
    keys = [
        "acumulado","ytd","qtd","ttm","ltm","year to date","quarter to date",
        "año a fecha","trimestre a fecha","últimos 12 meses","rolling 12",
        "hasta hoy","delta","diferencial","variación","variacion",
        "comparar con el año pasado","vs ly","vs año pasado",
        "este mes","último mes","ultimo mes","último trimestre","ultimo trimestre","trimestre pasado","acumulado trimestral",
        "del "," al "
    ]
    comp_words = ["competencia","competidor","competidores","empresa","marca rival"]
    return any(k in t for k in keys) or any(k in t for k in comp_words)

# ==================================
# (NUEVO) AGENTE ESTADÍSTICO
# ==================================
def agente_estadistico_responder(peticion: str, fecha_ref: datetime | None = None) -> str:
    # IMPORTANTE: forzar recarga de datos para no usar cachés viejos (arregla LY=0 con datos 2024/2023)
    _refresh_stats_caches()
    try:
        df_s = _load_sales_data()
        df_c = _load_competition_data()
    except Exception as e:
        return f"Error cargando datos de ventas/visitas desde BD: {e}"

start, end, label = _detectar_periodo_desde_texto(peticion, fecha_ref)
    f = _detectar_filtro(df_s, df_c, peticion)
    modo_comp = f["modo_competencia"]

dfs = df_s.copy()
    if f["cuenta"]:   dfs = dfs[dfs["Cuenta"].str.lower()==f["cuenta"].lower()]
    if f["producto"]: dfs = dfs[dfs["Producto"].str.lower()==f["producto"].lower()]

dfc = df_c.copy()
    if f["cuenta"]:   dfc = dfc[dfc["Cuenta"].str.lower()==f["cuenta"].lower()]
    if f["producto"]: dfc = dfc[dfc["Producto"].str.lower()==f["producto"].lower()]
    if f["empresa"]:  dfc = dfc[dfc["Empresa"].str.lower()==f["empresa"].lower()]

pide_delta = _pide_diferencial(peticion)

if modo_comp:
        if dfc.empty:
            contexto = []
            if f["cuenta"]:   contexto.append(f"Cuenta: {f['cuenta']}")
            if f["producto"]: contexto.append(f"Producto: {f['producto']}")
            if f["empresa"]:  contexto.append(f"Empresa: {f['empresa']}")
            ctx = " | ".join(contexto) if contexto else "sin filtros"
            return f"No hay menciones de competencia para el filtro ({ctx})."
        if pide_delta:
            r = calcular_diferencial_vs_ly_rango(dfc, start, end, value_col="_menciones")
            a, ly = r["actual"], r["ly"]
            cab = f"Menciones Competencia {label}"
            if f["cuenta"]:   cab += f" | Cuenta: {f['cuenta']}"
            if f["producto"]: cab += f" | Producto: {f['producto']}"
            if f["empresa"]:  cab += f" | Empresa: {f['empresa']}"
            pct_txt = "N/D" if r["delta_pct"] is None else f"{r['delta_pct']:.1f}%"
            return (
                f"{cab}\n"
                f"- Periodo actual: {a['inicio']} → {a['fin']}: {_fmt_num(a['acumulado'])} menciones\n"
                f"- Mismo periodo LY: {ly['inicio']} → {ly['fin']}: {_fmt_num(ly['acumulado'])} menciones\n\n"
                f"Diferencial absoluto: {_fmt_num(r['delta_abs'])}\n"
                f"Diferencial relativo: {pct_txt}"
            )
        else:
            r = calcular_acumulado_rango(dfc, start, end, value_col="_menciones")
            cab = f"Menciones Competencia {label}"
            if f["cuenta"]:   cab += f" | Cuenta: {f['cuenta']}"
            if f["producto"]: cab += f" | Producto: {f['producto']}"
            if f["empresa"]:  cab += f" | Empresa: {f['empresa']}"
            return (
                f"{cab}\n"
                f"- Desde {r['inicio']} hasta {r['fin']}\n"
                f"- Total menciones: {_fmt_num(r['acumulado'])}"
            )

if dfs.empty:
        contexto = []
        if f["cuenta"]:   contexto.append(f"Cuenta: {f['cuenta']}")
        if f["producto"]: contexto.append(f"Producto: {f['producto']}")
        ctx = " | ".join(contexto) if contexto else "sin filtros"
        return f"No se encontraron ventas para el filtro ({ctx})."

if pide_delta:
        r = calcular_diferencial_vs_ly_rango(dfs, start, end, value_col="_fact")
        a, ly = r["actual"], r["ly"]
        cab = f"Acumulado Sandoz {label}"
        if f["cuenta"]:   cab += f" | Cuenta: {f['cuenta']}"
        if f["producto"]: cab += f" | Producto: {f['producto']}"
        pct_txt = "N/D" if r["delta_pct"] is None else f"{r['delta_pct']:.1f}%"
        return (
            f"{cab}\n"
            f"- Periodo actual: {a['inicio']} → {a['fin']}: {_fmt_num(a['acumulado'])}\n"
            f"- Mismo periodo LY: {ly['inicio']} → {ly['fin']}: {_fmt_num(ly['acumulado'])}\n\n"
            f"Diferencial absoluto: {_fmt_num(r['delta_abs'])}\n"
            f"Diferencial relativo: {pct_txt}"
        )
    else:
        r = calcular_acumulado_rango(dfs, start, end, value_col="_fact")
        cab = f"Acumulado Sandoz {label}"
        if f["cuenta"]:   cab += f" | Cuenta: {f['cuenta']}"
        if f["producto"]: cab += f" | Producto: {f['producto']}"
        return (
            f"{cab}\n"
            f"- Desde {r['inicio']} hasta {r['fin']}\n"
            f"- Total facturación: {_fmt_num(r['acumulado'])}"
        )

# ==================================
# FUNCIÓN PARA AÑADIR VISITA A LA BBDD
# ==================================
def add_visit_to_db(account_name: str, id_usuario: int, visit_date: str, puntos_clave: str, contacto: str,
                    competitor_mentions: list[dict]):
    """
    Inserta la visita en BD y registra menciones de competencia en ventas_competencia.
    """
    id_cuenta = get_or_create_cuenta(account_name)
    id_visita = insert_visita(id_cuenta, id_usuario, visit_date, puntos_clave, contacto)
    for item in competitor_mentions or []:
        empresa = item.get("empresa")
        producto = item.get("producto")
        id_producto = None
        if producto:
            id_producto = get_producto_by_nombre(producto)
        insert_venta_competencia(id_visita, id_producto, empresa)
    return id_visita

# ==================================
# FUNCIÓN DE VERIFICAR PUNTOS FALTANTES
# ==================================
def check_missing_points(transcribed_text):
    """
    Revisa si en la transcripción se cumplen algunos puntos mínimos, y devuelve un JSON
    con estado 'presente' / 'faltante' por cada punto.
    """
    prompt = f"""
Eres un verificador de integridad para reportes de visita de Sandoz.
Puntos mínimos:
1. Identificación de la cuenta
2. Persona de contacto o departamento
3. Productos o líneas discutidas
4. Feedback y acciones futuras

Compara la transcripción:
{transcribed_text}

Devuelve un JSON con:
{{
  "reporte_integridad": [
    {{
      "punto": "<nombre>",
      "estado": "presente" o "faltante",
      "comentario": "..."
    }},
    ...
  ],
  "recomendacion_global": "..."
}}
"""
    ans = call_chat_api_simple(prompt)
    try:
        data = json.loads(ans)
        return data
    except:
        return {
            "reporte_integridad": [],
            "recomendacion_global": "No se pudo parsear la respuesta del modelo."
        }

# ==========================================
# FUNCIÓN TEXT-TO-SPEECH
# ==========================================
def text_to_speech(text: str) -> str | None:
    """Convierte texto a audio usando OpenAI TTS y devuelve la ruta del archivo."""
    if not text or not text.strip():
        return None
    try:
        # Usamos un archivo temporal para guardar el audio
        with tempfile.NamedTemporaryFile(suffix=".mp3", delete=False) as tmp_file:
            speech_file_path = tmp_file.name

response = client.audio.speech.create(
            #Cambiado el modelo a tts-1-hd para mayor calidad y la voz a "nova" para un acento potencialmente más neutro.
            model="tts-1-hd",
            voice="nova", # tipo de voz
            input=text
        )
        response.stream_to_file(speech_file_path)
        return speech_file_path
    except Exception as e:
        print(f"Error en la API de Text-to-Speech: {e}")
        return None
    
# ==================================
# FUNCIONES PARA RAG / HISTORIAL
# ==================================
def retrieve_account_records(account_name, limit):
    dfv = query_df(
        """
        SELECT
            vc.fecha,
            vc.puntos_clave
        FROM visitas_clientes vc
        JOIN cuentas c ON c.id_cuenta = vc.id_cuenta
        WHERE LOWER(c.cuenta) = LOWER(:cuenta)
        ORDER BY vc.fecha DESC
        OFFSET 0 ROWS FETCH NEXT :limit ROWS ONLY
        """,
        {"cuenta": account_name, "limit": int(limit) if str(limit).isdigit() else 5}
    )
    if dfv.empty:
        return "No se encontraron registros para la cuenta ingresada."
    lines = []
    for _, row in dfv.iterrows():
        fecha_str = str(row["fecha"]) if pd.notnull(row["fecha"]) else "Sin fecha"
        lines.append(f"{fecha_str}\n{row['puntos_clave']}")
    return "\n\n".join(lines)

def retrieve_sales_summary(account_name):
    dfs = query_df(
        """
        SELECT p.producto, SUM(v.facturacion) AS facturacion
        FROM ventas v
        JOIN visitas_clientes vc ON vc.id_visita = v.id_visita
        JOIN cuentas c ON c.id_cuenta = vc.id_cuenta
        JOIN porfolio p ON p.id_producto = v.id_producto
        WHERE LOWER(c.cuenta) = LOWER(:cuenta)
        GROUP BY p.producto
        ORDER BY SUM(v.facturacion) DESC
        """,
        {"cuenta": account_name}
    )
    if dfs.empty:
        return "No se encontraron registros de ventas para la cuenta ingresada."
    output = ""
    for _, row in dfs.iterrows():
        output += f"{row['producto']}: {row['facturacion']}\n"
    return output

def retrieve_competition_summary(account_name):
    dfc = query_df(
        """
        SELECT vc.empresa, p.producto, COUNT(*) AS menciones
        FROM ventas_competencia vc
        JOIN visitas_clientes v ON v.id_visita = vc.id_visita
        JOIN cuentas c ON c.id_cuenta = v.id_cuenta
        LEFT JOIN porfolio p ON p.id_producto = vc.id_producto
        WHERE LOWER(c.cuenta) = LOWER(:cuenta)
        GROUP BY vc.empresa, p.producto
        ORDER BY COUNT(*) DESC
        """,
        {"cuenta": account_name}
    )
    if dfc.empty:
        return "No hay menciones de competencia para esta cuenta."
    lines = []
    for _, r in dfc.iterrows():
        prod = r["producto"] if pd.notnull(r["producto"]) else "(sin producto)"
        lines.append(f"{r['empresa']} - {prod}: {int(r['menciones'])} menciones")
    return "\n".join(lines)

def retrieve_account_history(account_name, limit):
    visits_history = retrieve_account_records(account_name, limit)
    sales_history = retrieve_sales_summary(account_name)
    competition_history = retrieve_competition_summary(account_name)
    combined = (
        "Historial de visitas:\n" + visits_history +
        "\n\nHistorial de ventas:\n" + sales_history +
        "\n\nCompetencia (menciones):\n" + competition_history
    )
    return combined

def answer_commercial_query_local(query, account_name):
    if account_name.strip() == "":
        detected_account = detect_entities(query)
        if detected_account in ["Cuenta no detectada", "Sin cuenta detectada"]:
            detected_account = "Cuenta desconocida"
    else:
        detected_account = account_name
    combined_context = retrieve_account_history(detected_account, 5)
    prompt = f"""
Eres un asistente comercial experto en Sandoz. Usa el contexto histórico de la cuenta {detected_account}
(visitas, ventas y menciones de competencia) para responder.
----------------------------------------------------
{combined_context}
----------------------------------------------------
Consulta:
"{query}"

Requisitos de la respuesta:
- Sé conciso y profesional.
- Incluye recomendaciones accionables (qué productos priorizar, próximos pasos).
- Ten en cuenta la competencia (empresa y/o productos mencionados) para detectar oportunidades (huecos, fortalezas propias, contraofertas, bundling, etc.).
"""
    response = call_chat_api_simple(prompt)
    return response

def dashboard_overview():
    # Gráfico 1: Número de visitas por comercial
    try:
        df_visits = query_df(
            """
            SELECT u.representante, COUNT(*) AS cantidad
            FROM visitas_clientes vc
            JOIN usuarios u ON u.id_usuario = vc.id_usuario
            GROUP BY u.representante
            ORDER BY COUNT(*) DESC
            """
        )
        if df_visits.empty:
            fig1 = plt.figure(); plt.text(0.5, 0.5, "No hay datos de visitas", ha="center", va="center")
        else:
            fig1, ax1 = plt.subplots()
            ax1.bar(df_visits["representante"], df_visits["cantidad"], color="skyblue")
            ax1.set_title("Número de visitas por comercial")
            ax1.set_xlabel("Comercial"); ax1.set_ylabel("Visitas")
            for t in ax1.get_xticklabels(): t.set_rotation(45)
            plt.tight_layout()
    except Exception as e:
        fig1 = plt.figure(); plt.text(0.5, 0.5, "Error en dashboard (visitas):\n" + str(e), ha="center", va="center")

# Gráfico 2: Facturación por cuenta
    try:
        df_by_cuenta = query_df(
            """
            SELECT c.cuenta, SUM(v.facturacion) AS facturacion
            FROM ventas v
            JOIN visitas_clientes vc ON vc.id_visita = v.id_visita
            JOIN cuentas c ON c.id_cuenta = vc.id_cuenta
            GROUP BY c.cuenta
            ORDER BY SUM(v.facturacion) DESC
            """
        )
        if df_by_cuenta.empty:
            fig2 = plt.figure(); plt.text(0.5, 0.5, "No hay datos de ventas", ha="center", va="center")
        else:
            fig2, ax2 = plt.subplots()
            ax2.bar(df_by_cuenta["cuenta"], df_by_cuenta["facturacion"], color="salmon")
            ax2.set_title("Facturación por cuenta")
            ax2.set_xlabel("Cuenta"); ax2.set_ylabel("Facturación")
            for t in ax2.get_xticklabels(): t.set_rotation(45)
            plt.tight_layout()
    except Exception as e:
        fig2 = plt.figure(); plt.text(0.5, 0.5, "Error en dashboard (ventas por cuenta):\n" + str(e), ha="center", va="center")

# Gráfico 3: Facturación por producto
    try:
        df_by_prod = query_df(
            """
            SELECT p.producto, SUM(v.facturacion) AS facturacion
            FROM ventas v
            JOIN porfolio p ON p.id_producto = v.id_producto
            GROUP BY p.producto
            ORDER BY SUM(v.facturacion) DESC
            """
        )
        if df_by_prod.empty:
            fig3 = plt.figure(); plt.text(0.5, 0.5, "No hay datos de ventas", ha="center", va="center")
        else:
            fig3, ax3 = plt.subplots()
            ax3.bar(df_by_prod["producto"], df_by_prod["facturacion"], color="lightgreen")
            ax3.set_title("Facturación por producto")
            ax3.set_xlabel("Producto"); ax3.set_ylabel("Facturación")
            for t in ax3.get_xticklabels(): t.set_rotation(45)
            plt.tight_layout()
    except Exception as e:
        fig3 = plt.figure(); plt.text(0.5, 0.5, "Error en dashboard (ventas por producto):\n" + str(e), ha="center", va="center")

# Gráfico 4 (plotly) Interactivo por cuenta y producto
    try:
        df_sales3 = query_df(
            """
            SELECT c.cuenta, p.producto, SUM(v.facturacion) AS facturacion
            FROM ventas v
            JOIN visitas_clientes vc ON vc.id_visita = v.id_visita
            JOIN cuentas c ON c.id_cuenta = vc.id_cuenta
            JOIN porfolio p ON p.id_producto = v.id_producto
            GROUP BY c.cuenta, p.producto
            """
        )
        if not df_sales3.empty:
            fig4 = px.bar(
                df_sales3, x="cuenta", y="facturacion", color="producto",
                barmode="group", title="Facturación por cuenta y producto"
            )
        else:
            fig4 = px.bar(title="No hay datos de ventas para mostrar")
    except Exception as e:
        fig4 = px.bar(title="Error en dashboard (interactivo): " + str(e))

return fig1, fig2, fig3, fig4

# ====================================================
# CÓDIGO DE LOGIN (BD)
# ====================================================
def verify_login(username, password):
    """Verifica credenciales en BD y devuelve (ok, representante, id_usuario)."""
    try:
        user = get_usuario_by_credentials(username, str(password))
        if not user:
            return False, None, None
        id_usuario, representante, _ = user
        return True, representante, id_usuario
    except Exception:
        return False, None, None

# ====================================================
# INTERFAZ GRADIO
# ====================================================
with gr.Blocks() as demo:
    gr.HTML('''
    <style>
    /* Forzamos fondo blanco en .white-container */
    .white-container {
        background-color: #ffffff !important;
        padding: 20px !important;
        margin-top: 20px !important;
        border-radius: 5px !important;
    }
    .white-container * {
        background-color: #ffffff !important;
        color: #000000 !important;
    }
    .dark .white-container * {
        background-color: #ffffff !important;
        color: #000000 !important;
    }

.btn-spacing {
        margin-bottom: 20px !important;
    }

.step-heading-1-5 {
        background-color: transparent !important;
        color: #333 !important;
        display: inline-block !important;
        padding: 2px !important;
        margin: 0 !important;
        font-weight: 600 !important;
        font-size: 0.85rem !important;
        border: none !important;
        box-shadow: none !important;
    }
    </style>
    ''')

gr.HTML('''
    <script>
      const logoutChannel = new BroadcastChannel("logout_channel");

logoutChannel.onmessage = (event) => {
        if (event.data === "logout") {
          localStorage.clear();
          sessionStorage.clear();
          window.location.reload(); 
        }
      };

function broadcastLogout() {
        logoutChannel.postMessage("logout");
      }
    </script>
    ''')

# Logo superior
   
    def load_image_base64(path):
        with open(path, "rb") as f:
            img_data = f.read()
        return f"data:image/png;base64,{base64.b64encode(img_data).decode()}"

logo_base64 = load_image_base64("Logo_Sandoz.png")

gr.HTML(f'''
    <div style="padding: 0; margin: 0;">
    <img src="{logo_base64}" style="height: 60px; border: none; box-shadow: none; background: transparent; pointer-events: none; cursor: default;">
    </div>
    ''')

# Estado de sesión: representante y id_usuario
    user_session = gr.State(value=None)
    user_id_state = gr.State(value=None)
    
    # ===================================================
    # PESTAÑA "LOGIN"
    # ===================================================
    with gr.Tab("Login") as tab_login:
        gr.Markdown("## Asistente CRM AI Sandoz - Login")
        username_input = gr.Textbox(label="Usuario")
        password_input = gr.Textbox(label="Contraseña", type="password")
        login_button = gr.Button("Iniciar sesión")
        login_message = gr.Markdown()
        
        def login(username, password):
            ok, representative, id_usuario = verify_login(username, password)
            if ok:
                return f"✅ Bienvenido {representative}", representative, id_usuario
            return "❌ Usuario o contraseña incorrectos.", None, None
        
        login_button.click(
            fn=login,
            inputs=[username_input, password_input],
            outputs=[login_message, user_session, user_id_state]
        )
    
    # ===================================================
    # PESTAÑA "REGISTRAR VISITA"
    # ===================================================
    with gr.Tab("Subir audio", visible=False) as tab_audio:
        gr.Markdown("## Registro de visita - transcripción de audios y resumen")
        
        # Estados para almacenar transcripciones y validaciones
        main_transcript_state = gr.State()
        combined_transcript_state = gr.State()
        filtered_transcription_state = gr.State()
        integrity_data_state = gr.State()

contact_state = gr.State()

# (MODIFICADO) Nuevo estado para controlar si ya se registró la visita
        visit_already_registered = gr.State(False)  # <-- Nuevo

# ========== PASO 1 ==========
        with gr.Group(visible=True) as step1_container:
            gr.Markdown("### Paso 1: Subir audio y verificar contenido", elem_classes="step-heading-1-5")
            with gr.Group(elem_classes="white-container"):
                audio_input_main = gr.Audio(type="filepath", label="Audio Principal")
                btn_process_main = gr.Button("Procesar audio y verificar contenido", elem_classes="btn-spacing")
                main_audio_msg = gr.Markdown()
                verify_msg = gr.Markdown()
                account_input = gr.Textbox(label="Nombre de la cuenta (detectada o manual)")

# ========== PASO 1.1 (Opcional) ==========
        with gr.Group(visible=False) as step3_container:
            gr.Markdown("### Pendiente de completar: Subir audio complementario y verificar contenido", elem_classes="step-heading-1-5")
            with gr.Group(elem_classes="white-container"):
                audio_input_second = gr.Audio(type="filepath", label="Audio complementario")
                btn_process_second = gr.Button("Procesar audio complementario y verificar contenido", elem_classes="btn-spacing")
                second_audio_msg = gr.Markdown()
                recheck_msg = gr.Markdown()

# ========== PASO 2 ==========
        with gr.Group(visible=False) as step4_container:
            gr.Markdown("### Paso 2: Generar transcripción", elem_classes="step-heading-1-5")
            with gr.Group(elem_classes="white-container"):
                final_transcript_box = gr.Textbox(label="Transcripción final", lines=5)
                btn_filter_final = gr.Button("Obtener transcripción", elem_classes="btn-spacing")

# ========== PASO 3 ==========
        with gr.Group(visible=False) as step5_container:
            gr.Markdown("### Paso 3: Generar ficha resumen de la visita", elem_classes="step-heading-1-5")
            with gr.Group(elem_classes="white-container"):
                summary_textbox = gr.Textbox(label="Resumen estructurado", lines=10)
                summary_file_out = gr.File(label="Descargar resumen de la visita")
                gen_summary_msg = gr.Markdown()
                btn_generate_summary = gr.Button("Generar resumen", elem_classes="btn-spacing")

# ========== PASO 4 ==========
        with gr.Group(visible=False) as step6_container:
            gr.Markdown("### Paso 4: Registrar visita en el histórico", elem_classes="step-heading-1-5")
            with gr.Group(elem_classes="white-container"):
                register_msg = gr.Markdown()
                btn_register = gr.Button("Registrar visita")

# ----------------------------------------
        # FUNCIONES / CALLBACKS
        # ----------------------------------------
        def check_integrity_callback(transcript):
            if not transcript:
                return (
                    "No hay transcripción para verificar.",
                    {"all_ok": False},
                    gr.update(visible=False),
                    gr.update(visible=False)
                )

detected_acc = detect_entities(transcript)
            cuenta_no_detectada = detected_acc.lower() in ["cuenta no detectada", "ana", "luis", "maría"]
            detected_acc_msg = (
                "⚠️ Cuenta no detectada. Por favor, ingrésala manualmente."
                if cuenta_no_detectada
                else f"La cuenta es identificada como '{detected_acc}'."
            )

data = check_missing_points(transcript)
            repint = data.get("reporte_integridad", [])
            recomend = data.get("recomendacion_global", "")
            msg = "### Resultado de la verificación\n"

faltan_puntos = {
                "cuenta": False,
                "contacto": False,
                "productos": False,
                "feedback": False
            }

for item in repint:
                punto = item["punto"].lower()
                estado = item["estado"].lower()
                comentario = item["comentario"]
                icon = "✅" if estado == "presente" else "❌"

msg += f"- **{punto}**: {estado.capitalize()} {icon}\n"
                if comentario:
                    msg += f"  - {comentario}\n"

if estado == "faltante":
                    if "cuenta" in punto:
                        faltan_puntos["cuenta"] = True
                    elif "contacto" in punto or "departamento" in punto:
                        faltan_puntos["contacto"] = True
                    elif "productos" in punto or "líneas discutidas" in punto:
                        faltan_puntos["productos"] = True
                    elif "feedback" in punto or "acciones futuras" in punto:
                        faltan_puntos["feedback"] = True

msg += f"\n**Recomendación global:** {recomend}\n\n{detected_acc_msg}"
            data["all_ok"] = not any(faltan_puntos.values())

# Caso 1: Falta solo la cuenta
            if faltan_puntos["cuenta"] and not (faltan_puntos["contacto"] or faltan_puntos["productos"] or faltan_puntos["feedback"]):
                return msg, data, gr.update(visible=False), gr.update(visible=True)

# Caso 2: Falta algún otro punto
            if faltan_puntos["contacto"] or faltan_puntos["productos"] or faltan_puntos["feedback"]:
                return msg, data, gr.update(visible=True), gr.update(visible=False)

# Caso 3: Todo OK
            return msg, data, gr.update(visible=False), gr.update(visible=True)

def recheck_integrity_callback(transcript):
            if not transcript:
                return "No hay transcripción para verificar.", {"all_ok": False}, gr.update(visible=False)
            data = check_missing_points(transcript)
            repint = data.get("reporte_integridad", [])
            recomend = data.get("recomendacion_global", "")
            msg = "### Resultado de la verificación\n"

all_ok = True
            for item in repint:
                punto = item.get("punto", "")
                estado = item.get("estado", "desconocido")
                comentario = item.get("comentario", "")
                icon = "✅" if estado.lower() == "presente" else "❌"
                msg += f"- **{punto}**: {estado.capitalize()} {icon}\n"
                if comentario:
                    msg += f"  - {comentario}\n"
                if estado.lower() == "faltante":
                    all_ok = False

msg += f"\n**Recomendación global:** {recomend}\n"
            data["all_ok"] = all_ok

if not all_ok:
                return msg, data, gr.update(visible=False)
            return msg, data, gr.update(visible=True)

# ----------------------------------------
        # PROCESAR AUDIO PRINCIPAL
        # ----------------------------------------
        def process_main_audio_callback(audio, representative):
            progress_msg = ""
            if not representative:
                progress_msg = "⚠️ Debes iniciar sesión primero."
                yield progress_msg, "", None, None, "", {"all_ok": False}, gr.update(visible=False), gr.update(visible=False), ""
                return
            if not audio:
                progress_msg = "No has subido ningún audio principal."
                yield progress_msg, "", None, None, "", {"all_ok": False}, gr.update(visible=False), gr.update(visible=False), ""
                return

progress_msg += "Procesando audio...\n"
            yield progress_msg, "", None, None, "", {"all_ok": False}, gr.update(visible=False), gr.update(visible=False), ""
            time.sleep(1)

progress_msg += "Transcribiendo audio...\n"
            yield progress_msg, "", None, None, "", {"all_ok": False}, gr.update(visible=False), gr.update(visible=False), ""
            raw_transcription = call_openai_audio_transcription(audio)

detected_acc = detect_entities(raw_transcription)
            if detected_acc.lower() == "cuenta no detectada":
                detected_acc = "Sin cuenta detectada"

detected_contact = detect_contact(raw_transcription)

main_transcript = raw_transcription
            combined = raw_transcription
            time.sleep(1)

progress_msg += "Verificando contenido...\n"
            yield progress_msg, "", None, None, "", {"all_ok": False}, gr.update(visible=False), gr.update(visible=False), ""
            verify_m, integrity_d, step3_vis, step4_vis = check_integrity_callback(main_transcript)
            time.sleep(1)

progress_msg += "✅ Proceso completado.\n"
            yield (
                progress_msg,           
                verify_m,               
                main_transcript,        
                combined,               
                detected_acc,           
                integrity_d,            
                step3_vis,              
                step4_vis,              
                detected_contact        
            )

btn_process_main.click(
            fn=process_main_audio_callback,
            inputs=[audio_input_main, user_session],
            outputs=[
                main_audio_msg,          
                verify_msg,              
                main_transcript_state,   
                combined_transcript_state,
                account_input,           
                integrity_data_state,    
                step3_container,         
                step4_container,         
                contact_state            
            ],
            queue=True
        )

# ----------------------------------------
        # PROCESAR AUDIO SECUNDARIO
        # ----------------------------------------
        def process_second_audio_callback(audio_second, representative, combined):
            progress_msg = ""
            if not representative:
                progress_msg = "⚠️ Debes iniciar sesión primero."
                yield progress_msg, "", combined, {"all_ok":False}, gr.update(visible=False)
                return
            if not audio_second:
                progress_msg = "No has subido ningún audio secundario."
                yield progress_msg, "", combined, {"all_ok":False}, gr.update(visible=False)
                return

progress_msg += "Procesando audio secundario...\n"
            yield progress_msg, "", combined, {"all_ok":False}, gr.update(visible=False)
            time.sleep(1)

progress_msg += "Transcribiendo audio...\n"
            yield progress_msg, "", combined, {"all_ok":False}, gr.update(visible=False)
            raw_transcription_2 = call_openai_audio_transcription(audio_second)
            new_combined = (combined or "") + "\n" + raw_transcription_2
            time.sleep(1)

progress_msg += "Verificando contenido...\n"
            yield progress_msg, "", new_combined, {"all_ok":False}, gr.update(visible=False)
            recheck_m, integrity_d, step4_vis = recheck_integrity_callback(new_combined)
            time.sleep(1)

progress_msg += "✅ Proceso completado.\n"
            yield progress_msg, recheck_m, new_combined, integrity_d, step4_vis

btn_process_second.click(
            fn=process_second_audio_callback,
            inputs=[audio_input_second, user_session, combined_transcript_state],
            outputs=[
                second_audio_msg,
                recheck_msg,
                combined_transcript_state,
                integrity_data_state,
                step4_container
            ],
            queue=True
        )

# ----------------------------------------
        # OBTENER TRANSCRIPCIÓN FILTRADA
        # ----------------------------------------
        def filter_final_callback(combined, representative):
            if not representative:
                return "⚠️ Debes iniciar sesión primero."
            if not combined:
                return "No hay transcripción combinada."
            filtered = semantic_filter_transcription(combined)
            return filtered

btn_filter_final.click(
            fn=filter_final_callback,
            inputs=[combined_transcript_state, user_session],
            outputs=[final_transcript_box]
        )

def store_filtered_and_show_step5(x):
            return x, gr.update(visible=True)

final_transcript_box.change(
            fn=store_filtered_and_show_step5,
            inputs=[final_transcript_box],
            outputs=[filtered_transcription_state, step5_container]
        )

# ----------------------------------------
        # GENERAR RESUMEN
        # ----------------------------------------
        def generate_summary_callback(filtered_transcription, user_account, representative):
            if not representative:
                return "", None, "⚠️ Debes iniciar sesión primero.", gr.update(visible=True), gr.update(visible=False)
            if not filtered_transcription:
                return "", None, "No hay transcripción final filtrada.", gr.update(visible=True), gr.update(visible=False)

final_account = user_account.strip()
            if not final_account:
                final_account = "Cuenta no detectada"

visit_date = datetime.today().strftime('%Y-%m-%d %H:%M:%S')
            try:
                hist_df = query_df(
                    """
                    SELECT vc.puntos_clave
                    FROM visitas_clientes vc
                    JOIN cuentas c ON c.id_cuenta = vc.id_cuenta
                    WHERE LOWER(c.cuenta) = LOWER(:cuenta)
                    ORDER BY vc.fecha DESC
                    """,
                    {"cuenta": final_account}
                )
                if hist_df.empty:
                    historical_summary = "No hay registros previos disponibles."
                else:
                    historical_summary = " ".join([str(x) for x in hist_df["puntos_clave"].dropna().tolist()])
            except Exception as e:
                historical_summary = f"Error al obtener histórico: {e}"

summary_text = generate_plain_text_summary(
                filtered_transcription,
                final_account,
                visit_date,
                historical_summary
            )
            summary_file = save_text_to_file(summary_text, "resumen_visita.txt")
            if not os.path.exists(summary_file):
                return "", None, "⚠️ Error al generar el archivo de resumen.", gr.update(visible=False), gr.update(visible=False)
            summary_file_out = gr.File(value=summary_file, label="Descargar resumen de la visita")
            return summary_text, summary_file, "✅ Resumen generado con éxito.", gr.update(visible=True), gr.update(visible=True)

btn_generate_summary.click(
            fn=generate_summary_callback,
            inputs=[filtered_transcription_state, account_input, user_session],
            outputs=[summary_textbox, summary_file_out, gen_summary_msg, step5_container, step6_container]
        )

# ----------------------------------------
        # (MODIFICADO) REGISTRAR VISITA
        # ----------------------------------------
        def register_in_history_callback(final_account, representative, id_usuario, corrected_text, combined_transcript, integrity_data, contact_value, already_registered):
            # Si ya se ha registrado, no repetimos
            if already_registered:  # <-- Uso del nuevo estado
                return "¡La visita ya está registrada y no se puede registrar dos veces!", True, gr.update(interactive=False)

if not representative:
                return "⚠️ Debes iniciar sesión primero.", False, gr.update(interactive=True)
            if not id_usuario:
                return "⚠️ No se pudo identificar el usuario en BD.", False, gr.update(interactive=True)
            if not final_account.strip():
                return "No se ha indicado la cuenta final para registrar.", False, gr.update(interactive=True)
            if not integrity_data or not integrity_data.get("all_ok"):
                return "❌ No se puede registrar: faltan puntos mínimos de la visita.", False, gr.update(interactive=True)

visit_date = datetime.today().strftime('%Y-%m-%d %H:%M:%S')
            puntos_clave = extract_puntos_clave(corrected_text)
            # Detectar competencia en la transcripción combinada
            competitor_mentions = detect_competitor_mentions(combined_transcript or corrected_text)
            # Registrar en BD
            try:
                id_visita = add_visit_to_db(final_account, int(id_usuario), visit_date, puntos_clave, contact_value, competitor_mentions)
            except Exception as e:
                return f"⚠️ Error al registrar la visita en BD: {e}", False, gr.update(interactive=True)
            # === [EMC]: extraer e insertar ventas en tabla 'ventas' ===
            extra_msg = ""
            try:
                # Elegimos como fuente la transcripción combinada si existe; si no, el texto corregido
                fuente_texto = (combined_transcript if "combined_transcript" in locals() else None) or corrected_text or ""
            except Exception:
                fuente_texto = corrected_text if "corrected_text" in locals() else ""
            try:
                n_ins, _det = extract_and_insert_sales_for_visit(id_visita=id_visita, source_text=fuente_texto)
                if n_ins > 0:
                    extra_msg = f" Se registraron {n_ins} línea(s) en la tabla de ventas."
                else:
                    extra_msg = " No se detectaron ventas claras en la transcripción."
            except Exception as _e_sales:
                extra_msg = f" ⚠️ Ventas no registradas por error: {_e_sales}"

# Marcamos el estado como True (para indicar que ya se registró)
            return "¡Visita registrada con éxito!" + extra_msg, True, gr.update(interactive=False)

# (MODIFICADO) btn_register con 3 salidas: mensaje, estado, propiedades del botón
        btn_register.click(
            fn=register_in_history_callback,
            inputs=[account_input, user_session, user_id_state, summary_textbox, combined_transcript_state, integrity_data_state, contact_state, visit_already_registered],
            outputs=[register_msg, visit_already_registered, btn_register]
        )

# ==========================================
    # PESTAÑA: "Asistente unificado" (fusiona RAG + Histórico + Agente estadístico)
    # ==========================================
    with gr.Tab("Asistente", visible=False) as tab_unificado:
        gr.Markdown("### Un único chat para consultas al asistente o al histórico")
        chat = gr.Chatbot(label="Asistente Sandoz", type="messages")
        last_account_state = gr.State(value="")  # recuerda la última cuenta detectada usada

with gr.Row():
            msg_in = gr.Textbox(
                label="Mensaje",
                placeholder="Ej.: Acumulado YTD de Farmacia Central / Menciones de competencia Q2 2024 en Paracetamol / Muéstrame los 3 últimos registros de Y",
                lines=2,
            )
        with gr.Row():
            audio_in = gr.Audio(type="filepath", label="🎤 Preguntar por voz (opcional)")
        with gr.Row():
            send_btn = gr.Button("Enviar", variant="primary")
            voice_btn = gr.Button("Procesar voz")
        
        # ## AÑADIDO ##: Componente de audio para la respuesta (oculto por defecto)
        with gr.Row():
            audio_out = gr.Audio(label="Respuesta de voz", autoplay=True, visible=False)

voice_status_unificado = gr.Markdown()

def is_history_intent(text: str) -> bool:
            """Heurística sencilla para detectar intención de consultar histórico."""
            if not text:
                return False
            t = text.lower()
            keywords = [
                "histori", "históric", "registro", "registros", "recientes",
                "últimos", "ultimos", "consultar", "mostrar", "ver"
            ]
            return any(k in t for k in keywords)

def _route_and_answer(user_text: str, chat_history: list, last_account: str):
            """
            Orden de enrutado:
            1) Agente estadístico (YTD/QTD/TTM, rangos, vs LY, competencia)
            2) Histórico (RAG)
            3) Consulta comercial generativa (RAG + LLM)
            """
            if not user_text or not user_text.strip():
                chat_history = chat_history + [
                    {"role": "user", "content": user_text or ""},
                    {"role": "assistant", "content": "Indica una pregunta o petición."},
                ]
                return chat_history, last_account, ""

user_text = user_text.strip()

# 1) Agente estadístico (trigger por palabras clave)
            if is_stats_intent(user_text):
                respuesta = agente_estadistico_responder(user_text, datetime.today())
                chat_history = chat_history + [
                    {"role": "user", "content": user_text},
                    {"role": "assistant", "content": respuesta},
                ]
                return chat_history, last_account, ""

# 2) Histórico / RAG comercial
            detected_account = detect_entities(user_text)
            record_count = detect_record_count(user_text)

use_account = ""
            if detected_account not in ["Cuenta no detectada", "Sin cuenta detectada", None, ""]:
                use_account = detected_account
            elif last_account:
                use_account = last_account

if is_history_intent(user_text):
                if not use_account:
                    assistant_text = "No detecté la cuenta para consultar el histórico. Especifica el nombre de la cuenta."
                    chat_history = chat_history + [
                        {"role": "user", "content": user_text},
                        {"role": "assistant", "content": assistant_text},
                    ]
                    return chat_history, last_account, ""
                limit = record_count if (isinstance(record_count, int) and record_count > 0) else 5
                result = retrieve_account_history(use_account, limit)
                chat_history = chat_history + [
                    {"role": "user", "content": user_text},
                    {"role": "assistant", "content": result},
                ]
                return chat_history, use_account, ""
            else:
                resp = answer_commercial_query_local(user_text, use_account or "")
                chat_history = chat_history + [
                    {"role": "user", "content": user_text},
                    {"role": "assistant", "content": resp},
                ]
                return chat_history, (use_account or last_account), ""

# ## MODIFICADO ##
        def handle_text_message(user_text, chat_history, last_account):
            chat_history, last_account, _ = _route_and_answer(user_text, chat_history, last_account)
            # Limpiamos el input de texto y nos aseguramos de que el audio out esté oculto
            return chat_history, last_account, gr.update(value=""), gr.update(value=None, visible=False)

# ## MODIFICADO ##
        def handle_voice_message(audio_path, chat_history, last_account):
            if not audio_path:
                msg = "❌ No se ha grabado ningún audio."
                return chat_history + [
                    {"role": "assistant", "content": msg}
                ], last_account, msg, gr.update(value=None, visible=False)

transcription = call_openai_audio_transcription(audio_path)
            if not transcription or "Error" in transcription:
                msg = f"❌ Error en la transcripción: {transcription}"
                return chat_history + [{"role": "assistant", "content": msg}], last_account, msg, gr.update(value=None, visible=False)

chat_history, last_account, _ = _route_and_answer(transcription, chat_history, last_account)
            
            # Extraemos la última respuesta del asistente para convertirla a voz
            assistant_response_text = ""
            if chat_history and chat_history[-1]['role'] == 'assistant':
                assistant_response_text = chat_history[-1]['content']
            
            audio_file_path = text_to_speech(assistant_response_text)

status = "✅ Voz procesada"
            
            # Devolvemos la actualización del chat, el estado, el mensaje de estado y el audio para reproducir
            return chat_history, last_account, status, gr.update(value=audio_file_path, visible=True)

# ## MODIFICADO ##: Eventos
        send_btn.click(
            fn=handle_text_message,
            inputs=[msg_in, chat, last_account_state],
            outputs=[chat, last_account_state, msg_in, audio_out],
        )
        msg_in.submit(
            fn=handle_text_message,
            inputs=[msg_in, chat, last_account_state],
            outputs=[chat, last_account_state, msg_in, audio_out],
        )
        voice_btn.click(
            fn=handle_voice_message,
            inputs=[audio_in, chat, last_account_state],
            outputs=[chat, last_account_state, voice_status_unificado, audio_out],
        )

# ==========================================
    # PESTAÑA: "Dashboard"
    # ==========================================
    with gr.Tab("Dashboard", visible=False) as tab_dashboard:
        gr.Markdown("### Dashboard: Gráficos")
        with gr.Row():
            dashboard_plot1 = gr.Plot(label="Número de visitas por comercial", show_label=False)
            dashboard_plot2 = gr.Plot(label="Facturación por cuenta", show_label=False)
            dashboard_plot3 = gr.Plot(label="Facturación por producto", show_label=False)
        with gr.Row():
            dashboard_plot4 = gr.Plot(label="Gráfico Interactivo", show_label=False)
        
        dashboard_btn = gr.Button("Actualizar Dashboard")
        dashboard_btn.click(
            fn=dashboard_overview,
            inputs=[],
            outputs=[dashboard_plot1, dashboard_plot2, dashboard_plot3, dashboard_plot4]
        )

# ==========================================
    # PESTAÑA: "Cerrar sesión"
    # ==========================================
    with gr.Tab("Cerrar sesión", visible=False) as tab_logout:
        gr.Markdown("## Cerrar sesión")
        
        logout_button = gr.Button("Cerrar sesión", elem_id="logout_button")
        logout_message = gr.Markdown()
        
        def logout_fn():
            return (
                None,                          # user_session -> None
                None,                          # user_id_state -> None
                "✅ Sesión cerrada con éxito.", # logout_message
                "",                            # main_transcript_state
                "",                            # combined_transcript_state
                "",                            # filtered_transcription_state
                "",                            # integrity_data_state
                "",                            # account_input
                ""                             # contact_state
            )

logout_button.click(
            fn=logout_fn,
            inputs=[],
            outputs=[
                user_session, user_id_state, logout_message,
                main_transcript_state, combined_transcript_state,
                filtered_transcription_state, integrity_data_state,
                account_input,
                contact_state
            ]
        )

gr.HTML('''
        <script>
          const logoutChannel = new BroadcastChannel("logout_channel");
          logoutChannel.onmessage = (event) => {
            if (event.data === "logout") {
              localStorage.clear();
              sessionStorage.clear();
              window.location.reload();
            }
          };
          function broadcastLogout() {
            logoutChannel.postMessage("logout");
          }
        </script>
        ''')

# ------------------------------------------
    # Control de visibilidad de pestañas (login)
    # ------------------------------------------
    def update_visibility(representative):
        is_logged = representative is not None
        return (
            gr.update(visible=is_logged),  # tab_audio
            gr.update(visible=is_logged),  # tab_unificado (nuevo)
            gr.update(visible=is_logged),  # tab_dashboard
            gr.update(visible=is_logged)   # tab_logout
        )
    
    user_session.change(
        update_visibility,
        inputs=[user_session],
        outputs=[tab_audio, tab_unificado, tab_dashboard, tab_logout]
    )

#Lanzar la app
if __name__ == "__main__":
    port = int(os.environ.get("PORT", 7860))

# Abre navegador con el parámetro en un hilo separado
    def open_browser():
        webbrowser.open(f"http://localhost:{port}/?__theme=light")

threading.Timer(1.5, open_browser).start()  # Retraso para dar tiempo al server

# Lanza Gradio normalmente
    demo.launch(server_port=port, share=False)

Memoria del trabajo:
UPGRADE DE SISTEMA CRM INTELIGENTE PARA REPRESENTANTES DE VENTAS  
GRUPO 4
Jose Manuel Hernando Valle
Ignacio Cosidó López
Marta De La Fuente Diez
Ainhoa Sánchez Segura
José Huerta Alberiche
Elena Mihaela Cozma Melinte

Índice:
1. Introducción	3
2. Estado del arte y motivación	4
3. Metodología	6
4. Desarrollo e implementación	8
4.1. ETL: de audio a datos estructurados	8
4.2. Recomendador y consultas	9
4.3. Funciones implementadas	9
4.4. Interfaz y usabilidad	10
4.5. Resultado final	12
5. Resultados y validación	12
6. Escalabilidad y recomendaciones para futuros desarrollos	14
6.1 Modo conversacional en tiempo real	14
6.2 Base de Datos NoSQL	15
6.3 Interfaz web en HTML	16
6.4 Modelos LLM abiertos en servidores propios	17
7. Conclusiones	17
7.1 Cumplimiento de objetivos.	17
7.2 Contribución del trabajo (tanto técnica como empresarial).	18
8. Bibliografía	19
9. Anexo e información de interés	19
1. Introducción
El presente trabajo se centra en el desarrollo de un aplicativo CRM (Customer Relationship Management) para la farmacéutica Sandoz con el objetivo de facilitar y agilizar el trabajo diario de sus representantes de ventas. 
Se parte del contexto de que dichos representantes realizan visitas periódicas a las diferentes farmacias a través de las cuales Sandoz comercializa sus productos. Tras estas visitas, los representantes deben realizar una serie de tareas de carácter administrativo consistentes es el registro por escrito de la visita, los asuntos tratados o las ventas cerradas durante la visita en cuestión. Esta tarea, realizada de forma manual, supone una pérdida de eficiencia e información ya que por una parte se incurre en un gasto innecesario del tiempo de sus trabajadores; y por otra parte, existe el riesgo de no poder captar adecuadamente toda la información debido a errores humanos. Las consecuencias de una mala praxis en la realización de esta tarea puede llegar a conducir en última instancia a una pérdida de poder competitivo para la empresa si los datos registrados de forma incorrecta, incompleta o sesgada son empleados para la toma de decisiones estratégicas.
En este contexto, el empleo de herramientas como la desarrollada durante el presente trabajo permite capturar el conocimiento compartido durante la visita de forma ágil, estandarizada y trazable y convertirlo en business intelligence de manera fiable en cuanto a la calidad del dato y el cumplimiento regulatorio.
El CRM desarrollado aborda este reto con un flujo voice-first: el representante registra un audio de la visita y el sistema lo transcribe automáticamente con modelos de speech-to-text (OpenAI whisper-1) para convertirlo en texto fiable y utilizable que se almacena en bases de datos en AzureSQL.
A continuación, aplica un filtro semántico que elimina contenido irrelevante y potencialmente sensible (como pueden ser datos de pacientes), conservando lo estrictamente útil para negocio (cuenta, contacto, compromisos y menciones de competidores), lo que facilita el cumplimiento de privacidad en un entorno altamente regulado.
Sobre ese texto, el asistente extrae entidades clave (cuenta y contacto) y valida la integridad mínima del reporte, guiando al usuario cuando falta información crítica. 
También se identifican menciones a las empresas competidoras y detecta ventas cerradas con cantidades, elevando la calidad del insight competitivo que pueden llegar a aportar los datos captados de cara a futuras estrategias.
A partir de ahí, el CRM ofrece tres capacidades diferenciadoras: 
(1) un asistente conversacional que responde consultas comerciales con contexto histórico de la cuenta (RAG local); 
(2) un agente estadístico que entiende rangos naturales (YTD, QTD, TTM) y devuelve acumulados y deltas tanto de ventas propias como de menciones de competencia
 (3) un dashboard de seguimiento de visitas y facturación por cuenta y producto. 
En conjunto, esta arquitectura reduce tiempos de reporte, estandariza la captura de información crítica, eleva la calidad del dato y habilita el análisis en lenguaje natural para la toma de decisiones en tiempo casi real. Como valor añadido de adopción, el CRM incluye síntesis por voz de las respuestas (TTS) para consultas manos libres durante la planificación o inmediatamente tras la visita.

2. Estado del arte y motivación
En los últimos años, la aplicación de la Inteligencia Artificial y de las técnicas de análisis de datos en los sistemas de gestión de clientes (CRM) ha evolucionado de manera notable. Mientras que en un comienzo los desarrollos se centran en la transcripción y resumen automático de interacciones comerciales, las tendencias actuales apuntan hacia la creación de asistentes inteligentes integrados que son capaces de transformar la conversación entre un representante y su cliente en información estructurada, medible y accionable. En este sentido, este trabajo se enmarca en esta línea de innovación mediante un CRM que combina captura automática de datos, procesamiento semántico y analítica avanzada en lenguaje natural.
El presente proyecto parte de una versión inicial (v7) que permitió materializar un prototipo funcional de CRM asistido por Inteligencia Artificial, capaz de transcribir visitas comerciales y de resumir la información recogida mediante modelos de lenguaje. Este primer desarrollo mostró de manera clara la viabilidad de incorporar IA a la labor del representante de ventas. 
No obstante, al someterlo a un análisis más detenido y a las necesidades reales de la empresa, se evidenciaron varias limitaciones estructurales y funcionales que restringen su utilidad práctica en un entorno corporativo como puede ser el farmacéutico.
En el plano de la gestión de datos, la versión 7 se apoya en un esquema muy básico: la información se almacena en archivos CSV separados (por ejemplo, Registro_Sandoz.csv y Ventas.csv). Esta solución facilita la fase de test inicial, pero plantea problemas significativos de consistencia, escalabilidad y explotación analítica. De hecho, el fichero de ventas no se alimentaba automáticamente de las transcripciones, sino que era rellenado manualmente para elaborar dashboards sencillos. En la práctica, esto impedía vincular directamente lo comentado durante una visita con, por ejemplo, los indicadores de ventas, reduciendo así la capacidad de la aplicación de generar valor.
Otro aspecto crítico era el uso de un repositorio semántico de textos (vectorstore) para almacenar el histórico de visitas. Aunque innovador, este enfoque dependía en exceso del lenguaje libre de los representantes, lo que dificulta la obtención de datos comparables y homogéneos. La falta de estructura impedía extraer indicadores fiables o realizar análisis sistemáticos, reduciendo el valor estratégico de la información.
La inteligencia accionable sobre las transcripciones también resultaba limitada en la versión 7. Si bien ya existían mecanismos valiosos como la detección de cuenta y contacto o la aplicación de un filtro semántico para proteger datos sensibles, este último eliminaba información potencialmente útil, como las menciones a competidores. El resultado era un CRM que, aunque protegía la confidencialidad, desaprovechaba fuentes de inteligencia competitiva clave para el sector farmacéutico. Asimismo, la herramienta no era capaz de identificar automáticamente ventas concretas ni de registrarlas de manera estructurada en su base de datos.
Desde la perspectiva del usuario, la experiencia también planteaba desafíos. La versión 7 ofrecía únicamente una modalidad de interacción, lo cual colisiona con uno de los requisitos de la empresa: que el sistema respondiera en texto cuando la consulta se formule en texto y en voz cuando la interacción se realice de manera oral. Esta carencia limita tanto la accesibilidad como la versatilidad del asistente en escenarios reales de uso.
En cuanto al apartado de seguridad y control de acceso, la versión 7 ya incorporaba un sistema de login, lo que constituyó una base importante para la funcionalidad del CRM.
Sin embargo, este componente estaba vinculado a archivos locales, lo que dificulta la integración con esquemas corporativos más avanzados y quedaba vulnerable a errores humanos.
En conjunto, la versión 7 supuso un avance valioso como una versión funcional (aunque primitiva), ya que incorporaba elementos clave como la detección de cuentas y contactos y un primer filtro semántico de información sensible. No obstante, sus carencias (como pueden ser la dependencia de ficheros CSV manuales, pérdida de información relevante en el filtrado, ausencia de extracción automática de ventas, falta de diferenciación en los modos de interacción y una arquitectura de datos poco escalable) evidenciaron la necesidad de una evolución.
El trabajo realizado hasta alcanzar la versión 11 trata de paliar todas estas limitaciones a la par que busca mantener una estructura de desarrollo sencilla para facilitar la implementación en Sandoz e incorpora mejoras sustanciales en la captura, el tratamiento y el análisis de los datos. De esta manera, el proyecto no solo corrige los puntos débiles de la versión inicial, sino que también se alinea con las exigencias de una organización competidora en un sector en el que la calidad del dato, la trazabilidad de la información y la inteligencia en el dato son esenciales para la toma de decisiones estratégicas.

3. Metodología
El punto de partida del proyecto se encuentra en la colaboración con la empresa farmacéutica Sandoz, que planteó un problema de gestión de información desestructurada procedente de las visitas comerciales. En el sistema previo, los comerciales grababan un audio tras cada visita a la farmacia, y a partir de él se extraía información básica como el nombre de la farmacia, la persona de contacto y algunos puntos clave de la reunión. Este proceso, aunque útil, resultaba insuficiente para los objetivos de la empresa, que deseaba enriquecer la información y facilitar su integración en su CRM corporativo.
La necesidad principal consistía en extender ese flujo inicial para incluir datos de ventas, resúmenes más detallados y aspectos estratégicos como el seguimiento de la competencia, la evolución de las ofertas y el análisis del portfolio de productos en cada farmacia. 
De este modo, la empresa no solo podría almacenar datos básicos, sino también generar conocimiento estratégico útil para la labor comercial.
Desde el inicio, se planteó una metodología que mantuviera la estructura ya existente de agentes, a fin de facilitar la integración y reducir riesgos de incompatibilidad. Para ello, se establecieron dos grandes líneas de trabajo:
1.	Extracción, Transformación y Carga de datos (ETL): un proceso que, partiendo del audio grabado por el comercial, permitiera transcribirlo, identificar la información relevante y almacenarla de manera estructurada en una base de datos.

2.	Sistema de recomendación: un módulo capaz de aprovechar esos datos para ofrecer soporte a los comerciales, respondiendo preguntas, mostrando resúmenes y ofreciendo recomendaciones tanto en texto como en audio.

Una decisión clave fue la elección de la infraestructura tecnológica. Dado que la empresa trabajaba con el ecosistema de Microsoft, se optó por implementar la base de datos en Azure SQL Server, lo que facilitó la integración con el CRM ya existente y garantizó la escalabilidad y seguridad necesarias.
Metodológicamente, este enfoque persigue dos objetivos complementarios:
•	Reducir la carga administrativa de los comerciales, automatizando la transcripción y estructuración de los datos.

•	Aportar valor añadido mediante un sistema inteligente de consulta y recomendación que apoyara la toma de decisiones en el punto de venta.

4. Desarrollo e implementación
El desarrollo del proyecto se materializó en dos bloques principales: la construcción del flujo ETL y la implementación del recomendador integrado en una interfaz accesible para los usuarios.

4.1. ETL: de audio a datos estructurados
La primera parte del desarrollo se centró en diseñar un pipeline que transformara audios desestructurados en información lista para explotar en el CRM.
•	El audio era transcrito mediante Whisper, modelo de reconocimiento automático de voz.

•	El texto obtenido era procesado por agentes basados en modelos de lenguaje (LLM) para identificar entidades clave: nombre de la farmacia, persona de contacto, ventas realizadas, productos de la competencia y observaciones relevantes.

•	La información extraída pasaba por validaciones y filtros semánticos para garantizar su integridad y la eliminación de datos sensibles.

•	Finalmente, los datos eran insertados automáticamente en la base de datos en la nube de Azure SQL Server, quedando disponibles para el resto de sistemas corporativos.
Este proceso no solo replicaba el flujo ya existente, sino que lo enriquece con nuevas categorías de información y con medidas de control adicionales para asegurar la trazabilidad de posibles errores.

4.2. Recomendador y consultas
La segunda parte del proyecto consistió en la creación de un sistema de recomendación interactivo para los comerciales. Este módulo, integrado en una interfaz basada en Gradio, permitía realizar consultas en lenguaje natural, ya fuera en texto o en audio.
•	Cuando el usuario introducía una pregunta, el sistema recuperaba los datos relevantes desde la base de datos SQL y los combinaba con un modelo de lenguaje (GPT-4 mini o nano, según necesidades de rendimiento).

•	El resultado se presentaba en texto o, en caso de petición en audio, se sintetizaba con un modelo de conversión texto a voz (TTS1 HD, voz Nova).

•	De este modo, se habilitaba un canal flexible y adaptado a distintos escenarios de uso, reforzando la utilidad de la herramienta en el día a día de los comerciales.

4.3. Funciones implementadas
A nivel técnico, se desarrollaron o adaptaron diversas funciones, entre las que destacan:
•	Funciones de detección de cuentas y contactos: asociadas a cada visita comercial.

•	Módulos de transcripción y extracción de información (ventas, competencia, resúmenes, métricas estadísticas).

•	Filtros semánticos de datos sensibles, heredados y ajustados del sistema previo.

•	Carga en base de datos con validaciones que garantizan que no falte ningún dato crítico.

•	Funciones de Text-to-Speech (TTS) para generar respuestas en audio.

•	Gestión de histórico y consultas unificadas con la base de datos en la nube.

4.4. Interfaz y usabilidad
La solución final se presenta al usuario mediante una interfaz construida en Gradio. Se mejoró notablemente respecto a la versión original, pasando de un modo oscuro a un modo claro y logo estático incrustado, haciéndola más acorde a la imagen de Sandoz:
Por otro lado, se fusionaron las pestañas de preguntar al asistente y consultar el histórico en una sola, de tal forma que ya solo con una pestaña el agente es capaz de detectar la clase de consulta que hace el usuario y arrojar la respuesta apropiada. Por otro lado, en esta misma pestaña, se cambió a un estilo tipo conversación de WhatsApp.
Todos estos cambios estéticos más la adición de poder realizar la consulta por voz y recibir la respuesta también por voz mejoran sustancialmente la experiencia de usuario, ofreciendo una solución más profesional y atractiva para el asistente de ventas.
En cuanto al script, la filosofía de diseño se basó en mantener el código lo más simple y autoexplicativo posible, de forma que futuras modificaciones e integraciones pudieran realizarse con facilidad. En el anexo, puede encontrarse una comparativa de la versión original y la final.
4.5. Resultado final
El sistema fue probado con usuarios y demostró un funcionamiento sólido tanto en la transcripción y estructuración de datos como en la capacidad de responder consultas en tiempo real. En esencia, el trabajo desarrollado puede resumirse en dos grandes logros:
1.	Automatizar el paso de información desestructurada (audio) a datos estructurados en SQL, reduciendo la carga administrativa de los comerciales.

2.	Aportar un recomendador inteligente que añade valor estratégico a la labor de ventas mediante la explotación de esos datos en un entorno interactivo y accesible.

5. Resultados y validación
La empresa nos pidió que la solución funcionará en dos escenarios concretos:

1.	ETL de carga  de información desde un audio a la base de datos. En este caso hemos seguido los ejemplos que nos pasaron, son audios de una duración de entre 20 segundos y minuto y medio en el que se da toda la información. En nuestra solución asumimos que la información que se debe subir a través de esta herramienta sería la información de la visita, las ventas generadas y la información de la competencia, ya que otra información, como sería las ofertas, el portfolio o las nuevas altas de farmacias se harían desde el CRM directamente. Esto hace que en un audio de pocos segundos se transcriba y obtener la información crítica en pocos segundos y de forma cómoda.

2.	Recomendador (RAG). Para esto nos planteó la empresa que se pudiera responder a las siguientes preguntas. 
a.	Ver en qué productos/moléculas reduce la frecuencia en un intervalo de año vista (year to day) o trimestre vista (quarter to day).
b.	Ver la frecuencia en la que pide la farmacia y según esta frecuencia recomendar ir o no. 
c.	Ver los descuentos por productos/moléculas predefinidos y la oferta que tiene para comprobar si están cumpliendo o no. 
d.	Sabiendo momentos de roturas de stock de la competencia, identificar esos momentos y atacar sobre esos productos. 
e.	Desglosar todo el catálogo que tenemos de productos que vendemos a estas farmacias y mostrando tanto lo que nos compran como lo que no.

Para esta información, tras la generación de las tablas en la base de datos que guardará información necesaria, pudimos alimentar el RAG con información externa a la del entrenamiento.

Esto le permitiría al comercial de manera más sencilla hacer preguntas en lenguaje natural sin tener que estar interactuando directamente con CRM o las bases de datos y permitiendo añadir valor a la estrategia con la información que aporte el modelo de lenguaje.

Hemos estado probando ambas soluciones y dan respuesta de manera casi inmediata. Si que es verdad que nos hemos ceñido a una solución en que el coste de los modelos sea el óptimo, es decir, que haga la tarea de la manera más eficiente, en un tiempo esperable y que el coste no sea alto, aunque siempre se pueden usar modelos de OpenAI más potentes aunque eso supone un mayor consumo.

6. Escalabilidad y recomendaciones para futuros desarrollos
A lo largo del desarrollo de este trabajo fuimos encontrando distintos puntos en los que la herramienta podría evolucionar para ganar en utilidad y robustez en un entorno real. Con el avance del proyecto fuimos identificando una serie de líneas claras de evolución futura, entre las que destacan la incorporación de un modo conversacional en tiempo real que permita una interacción mucho más natural mediante voz; el uso de modelos LLM de código abierto y pesos disponibles públicamente (como Mistral), que podrían desplegarse en servidores propios para dar mayor control sobre los datos y reducir la dependencia de proveedores externos; la adopción de una persistencia NoSQL que haga más sencillo el almacenamiento de los historiales conversacionales y permita adaptar la estructura de datos a medida que evoluciona la herramienta y las necesidades de la empresa, aportando sobre todo una mayor flexibilidad en la estructura de los datos a futuro; y el desarrollo de una interfaz web en formato HTML, apoyada en tecnologías habituales de la web (HTML, CSS y JavaScript), que ofrezca una experiencia más accesible, eficaz y agradable para los usuarios finales respecto a la solución actual basada en Gradio.

6.1 Modo conversacional en tiempo real
Uno de los avances más prometedores que hemos identificado es la posibilidad de incorporar un modo conversacional en tiempo real, donde la interacción con la herramienta se produzca directamente por voz, con una latencia muy baja y respuestas inmediatas. Justo al final del desarrollo de este trabajo apareció la versión oficial del Realtime API de OpenAI, que permite crear experiencias de voz a voz de manera estable y lista para producción. Llegamos a probarlo e incluso a incorporarlo de forma preliminar dentro del desarrollo, y la naturalidad en la conversación nos sorprendió muy positivamente. Sin embargo, al encontrarnos ya en una fase muy avanzada, vimos que su integración completa implicaba rehacer gran parte del trabajo realizado, por lo que finalmente decidimos no mantenerlo en esta versión.
Aun así, estamos convencidos de que este es un paso natural en la evolución de la herramienta. Un modo conversacional en tiempo real permitiría que los representantes pudieran dictar sus interacciones de manera fluida y recibir respuestas inmediatas, sin necesidad de pasar por transcripciones intermedias o interfaces de texto. Esta capacidad no solo reduce al mínimo la fricción en el uso, sino que convierte la interacción en algo tan sencillo como hablar y escuchar.
De cara al futuro, vemos este modo como una de las prioridades para próximos desarrollos, no solo por la mejora en la experiencia de usuario, sino también porque abre la puerta a integrar funciones más avanzadas en tiempo real, como la consulta inmediata de información del histórico, la generación de recordatorios o la detección temprana de oportunidades durante la propia conversación.

6.2 Base de Datos NoSQL
futuro, vemos este modo como una de las prioridades para próximos desarrollos, no solo por la mejora en la experiencia de usuario, sino también porque abre la puerta a integrar funciones más avanzadas en tiempo real, como la consulta inmediata de información del histórico, la gene
Otro de los aspectos en los que vimos margen de evolución es en la forma de almacenar los datos generados por la herramienta. Hasta ahora hemos trabajado con una base de datos relacional, que ofrece ventajas claras a la hora de mantener consistencia y fiabilidad en la información. Sin embargo, conforme la herramienta vaya creciendo y se vayan registrando más interacciones, observamos que este enfoque puede volverse rígido y más difícil de mantener.
En este sentido, consideramos que el uso de una base de datos NoSQL, como por ejemplo MongoDB, aportaría ventajas importantes. Su modelo documental permite guardar de manera más directa los historiales conversacionales completos, sin necesidad de fragmentarlos en múltiples tablas, y sobre todo facilita que la estructura de los datos pueda ir adaptándose conforme evoluciona la herramienta y cambian las necesidades de la empresa. Esto hace posible añadir nuevos campos o modificar la forma en que se almacenan las conversaciones sin tener que rediseñar esquemas completos, algo especialmente útil en un contexto donde la extracción de información y las funcionalidades de la herramienta se encuentran en continua mejora. 
Además, las bases de datos NoSQL están pensadas para ofrecer escalabilidad horizontal, lo que significa que, si el volumen de datos crece de forma significativa, es posible repartir la carga entre diferentes servidores de una manera relativamente sencilla. Esto garantiza que el sistema pueda seguir funcionando con fluidez incluso cuando el número de usuarios y de conversaciones procesadas aumente considerablemente. 
Por estas razones creemos que la incorporación de una base de datos NoSQL es un paso natural que facilita tanto el mantenimiento como la evolución de la herramienta en escenarios de uso más intensivo.

6.3 Interfaz web en HTML
Durante el proyecto utilizamos Gradio para iterar rápido y validar ideas. De cara a un uso extendido en la empresa, proponemos evolucionar hacia una interfaz web en HTML que ofrezca una experiencia más accesible, eficaz y agradable, con transiciones más fluidas y una presentación adaptada a los flujos reales de trabajo.
La construiríamos con HTML, CSS y JavaScript, lo que nos da mayor control sobre el diseño y la interacción: componentes reutilizables (formularios, tablas, buscadores, gráficos), navegación clara y una identidad visual alineada con la empresa. Este enfoque facilita además la autenticación corporativa (SSO), la gestión de permisos por rol y la integración con APIs internas.
En operación, un front web propio permite versionado y despliegues controlados, telemetría de uso para mejorar la UX y pruebas A/B cuando sea necesario. Aunque la solución actual ya es accesible desde navegador, este paso nos da más control evolutivo y sienta una base sólida para incorporar nuevas funciones con rapidez.

6.4 Modelos LLM abiertos en servidores propios
También contemplamos una línea de evolución basada en modelos LLM abiertos que pueden descargarse y ejecutarse internamente (por ejemplo, de la familia Mistral AI). Esta opción nos permitiría reducir la dependencia de proveedores externos, ajustar mejor el coste a medida que crece el uso y, sobre todo, mantener el procesamiento de la información dentro del perímetro de la empresa, con mayor control sobre privacidad, auditoría y trazabilidad.
Ahora bien, al operar el modelo en nuestros servidores asumimos más responsabilidades: dimensionar y escalar la capacidad (CPU/GPU), monitorizar rendimiento y errores, mantener y actualizar el servicio, y aplicar medidas de seguridad y cumplimiento. En un servicio gestionado por un proveedor externo como OpenAI, buena parte de esa carga recae en el proveedor; en el enfoque autoalojado la responsabilidad operativa pasa a ser nuestra.
Conviene señalar que esta vía puede tensionarse con el modo Realtime descrito en el 6.1: hoy existen menos alternativas abiertas y suficientemente maduras para ofrecer voz a voz en tiempo real con la misma fiabilidad y latencia que las soluciones comerciales. En la práctica, proponemos evaluar ambos caminos en paralelo y decidir según el caso de uso: priorizar soberanía del dato y control de costes con un modelo abierto ejecutado en servidores propios, o priorizar experiencia conversacional inmediata con un servicio Realtime gestionado.

7. Conclusiones
7.1 Cumplimiento de objetivos.
Hemos conseguido llegar a los objetivos marcados, hemos desarrollado una solución que permite tanto el tratamiento de la información como su explotación para el equipo comercial. Es una solución con potencial de escalabilidad, ya que está desarrollado para funcionar el un entorno de Azure y poder explotar tanto el almacenamiento, como hemos hecho nosotros con Azure SQL, como para usar los modelos de OpenAI disponibles en Azure OpenAI. La aplicación guarda la información básica de forma eficiente, sin complicar mucho el trabajo de los comerciales. Además incluyen un método de chat con el que se puede preparar una estrategia de ventas con un gran nivel de detalle.

7.2 Contribución del trabajo (tanto técnica como empresarial).
La aplicación va a poder dar muchísima información a los comerciales en el momento exacto. Todo lo que hemos preparado está en una versión Alfa, está probada por el equipo de desarrollo, por lo que todavía queda trabajo. Nuestras reuniones, sobre todo con el equipo de Data Science de Sandoz, han ayudado a que desarrolláramos esta versión, pero ahora faltaría preparar una versión Beta junto con el equipo de ventas, adaptándola más a sus sistemas de información y dispositivos de trabajo, y pudiendo testear la aplicación en un sistema real, que nos permita adaptarla al día a día.

8. Bibliografía

•	OpenAI. (2025). GPT-5 prompting guide. OpenAI Cookbook. https://cookbook.openai.com/examples/gpt-5/gpt-5_prompting_guide
•	OpenAI. (2024). GPT-4.1: The most advanced AI model. OpenAI. https://openai.com/index/gpt-4-1/
•	 OpenAI. (2025, 28 agosto). Introducing gpt-realtime and Realtime API updates for production voice agents. OpenAI. https://openai.com/index/introducing-gpt-realtime/
•	Mistral AI. (2025). mistralai. Hugging Face. https://huggingface.co/mistralai

9. Anexo e información de interés
•	Video explicativo de funcionalidades: Click aquí
Adjuntamos en la entrega zip lo siguiente:
•	Código
•	Esquema de la base de datos relacional