#!/usr/bin/env python # -*- coding: utf-8 -*- """ Script para generar reporte HTML con graficas usando Chart.js """ import sqlite3 import os import json from datetime import datetime script_dir = os.path.dirname(os.path.abspath(__file__)) project_root = os.path.join(script_dir, '..') os.chdir(project_root) def get_color(valor): if valor > 70: return '#e74c3c' elif valor > 50: return '#f39c12' else: return '#27ae60' def generar_reporte(): conn = sqlite3.connect('data/sentiment_analysis.db') conn.row_factory = sqlite3.Row cursor = conn.cursor() cursor.execute('SELECT * FROM customer_profiles ORDER BY churn_risk DESC') clientes = cursor.fetchall() cursor.execute('SELECT COUNT(*) as count FROM conversations') total_analyses = cursor.fetchone()[0] cursor.execute('SELECT AVG(sentiment_score) as avg FROM conversations') avg_sentiment = cursor.fetchone()[0] or 0 cursor.execute('SELECT COUNT(*) as count FROM risk_alerts WHERE resolved = 0') active_alerts = cursor.fetchone()[0] nombres_clientes = [c['customer_id'] for c in clientes] riesgos = [c['churn_risk'] * 100 for c in clientes] sentimientos = [c['lifetime_sentiment'] for c in clientes] colores = [get_color(r) for r in riesgos] tabla_clientes = "" for c in clientes: if c['churn_risk'] > 0.7: clase = "riesgo-rojo" elif c['churn_risk'] > 0.5: clase = "riesgo-naranja" else: clase = "riesgo-verde" tabla_clientes += f""" {c['customer_id']} {c['lifetime_sentiment']:.1f}/100 {c['churn_risk']:.1%} {c['total_interactions']} {c['last_contact']} """ colores_json = json.dumps(colores) nombres_json = json.dumps(nombres_clientes) riesgos_json = json.dumps(riesgos) sentimientos_json = json.dumps(sentimientos) html = f""" Sentiment Evolution Tracker - Reporte

Sentiment Evolution Tracker - Dashboard

Reporte generado: {datetime.now().strftime('%d/%m/%Y %H:%M:%S')}

{len(clientes)}

Clientes

{total_analyses}

Analisis

{avg_sentiment:.0f}

Sentimiento Promedio

{active_alerts}

Alertas Activas

Graficas de Analisis

Riesgo de Churn por Cliente

Sentimiento Promedio por Cliente

Clientes Registrados

{tabla_clientes}

Cliente ID	Sentimiento	Riesgo Churn	Interacciones	Ultimo Contacto

""" conn.close() with open('data/reporte_clientes.html', 'w', encoding='utf-8') as f: f.write(html) print("✅ Reporte con graficas generado!") print("📊 Ubicacion: data/reporte_clientes.html") print("🌐 Abre el archivo en tu navegador para ver las graficas") if __name__ == "__main__": generar_reporte()