import os
import pandas as pd
import psycopg2
from sshtunnel import SSHTunnelForwarder
from dotenv import load_dotenv
import dash
from dash import dcc, html, dash_table, Input, Output, State, callback
import dash_bootstrap_components as dbc
import plotly.express as px
from datetime import datetime, timedelta
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from apscheduler.schedulers.background import BackgroundScheduler
import atexit

load_dotenv()

# --- Configuración desde .env ---
SSH_HOST = os.getenv('SSH_HOST')
SSH_PORT = int(os.getenv('SSH_PORT', 22))
SSH_USER = os.getenv('SSH_USER')
SSH_PRIVATE_KEY = os.getenv('SSH_PRIVATE_KEY')
SSH_PASSWORD = os.getenv('SSH_PASSWORD')

DB_HOST = os.getenv('DB_HOST', 'localhost')
DB_PORT = int(os.getenv('DB_PORT', 5432))
DB_NAME = os.getenv('DB_NAME')
DB_USER = os.getenv('DB_USER')
DB_PASSWORD = os.getenv('DB_PASSWORD')

REFRESH_INTERVAL = 7200  # 2 horas

# --- IDs de ingenieros a excluir ---
EXCLUDED_ENGINEER_IDS = [21, 31, 34, 43, 44]

# --- Ponderación ---
PESO_CIERRE = 0.5
PESO_SLA = 0.5

# --- Configuración de correo ---
EMAIL_SENDER = os.getenv('SMTP_USER')
EMAIL_PASSWORD = os.getenv('SMTP_PASSWORD')
EMAIL_RECIPIENT = os.getenv('EMAIL_RECIPIENT')
EMAIL_SMTP_SERVER = os.getenv('SMTP_SERVER', 'mail.appsolec.com')
EMAIL_SMTP_PORT = int(os.getenv('SMTP_PORT', 465))
SMTP_ENCRYPTION = os.getenv('SMTP_ENCRYPTION', 'ssl').lower()


def get_week_range():
    today = datetime.now().date()
    days_since_saturday = (today.weekday() - 5) % 7
    start_date = today - timedelta(days=days_since_saturday)
    end_date = start_date + timedelta(days=6)
    return start_date, end_date


def get_helpdesk_metrics(start_date, end_date):
    try:
        tunnel = SSHTunnelForwarder(
            (SSH_HOST, SSH_PORT),
            ssh_username=SSH_USER,
            ssh_password=SSH_PASSWORD if SSH_PASSWORD else None,
            ssh_pkey=SSH_PRIVATE_KEY if SSH_PRIVATE_KEY else None,
            remote_bind_address=(DB_HOST, DB_PORT)
        )
        tunnel.start()
        print(f"Túnel SSH establecido en localhost:{tunnel.local_bind_port}")

        conn = psycopg2.connect(
            host='localhost',
            port=tunnel.local_bind_port,
            database=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD
        )

        exclude_clause = ""
        if EXCLUDED_ENGINEER_IDS:
            exclude_ids = ','.join(map(str, EXCLUDED_ENGINEER_IDS))
            exclude_clause = f"AND t.user_id NOT IN ({exclude_ids})"

        query = f"""
        WITH closed_stages AS (
            SELECT id FROM helpdesk_stage
            WHERE name->>'es_MX' IN ('Resuelto', 'Cerrado')
               OR name->>'en_US' IN ('Solved', 'Canceled')
        ),
        tickets_filtrados AS (
            SELECT
                t.id,
                t.user_id,
                t.sla_deadline,
                t.stage_id,
                t.sla_reached,
                t.close_date,
                t.create_date,
                t.priority
            FROM
                helpdesk_ticket t
            WHERE
                t.create_date >= '{start_date}' AND t.create_date < '{end_date}'
                {exclude_clause}
        ),
        weekly_tickets AS (
            SELECT
                u.id AS engineer_id,
                p.name AS engineer_name,
                COUNT(t.id) AS tickets_asignados,
                COUNT(t.id) FILTER (WHERE t.stage_id IN (SELECT id FROM closed_stages)) AS tickets_cerrados,
                COUNT(t.id) FILTER (
                    WHERE t.sla_deadline < NOW() AND t.close_date IS NULL
                ) AS tickets_vencidos,
                (COUNT(t.id) FILTER (WHERE t.sla_reached = True) * 100.0 / NULLIF(COUNT(t.id), 0)) AS sla_cumplido,
                COUNT(t.id) FILTER (WHERE t.priority::integer = 0) AS "Tickets Baja",
                COUNT(t.id) FILTER (WHERE t.priority::integer = 1) AS "Tickets Media",
                COUNT(t.id) FILTER (WHERE t.priority::integer = 2) AS "Tickets Alta",
                COUNT(t.id) FILTER (WHERE t.priority::integer = 3) AS "Tickets Crítica"
            FROM tickets_filtrados t
            INNER JOIN res_users u ON t.user_id = u.id
            INNER JOIN res_partner p ON u.partner_id = p.id
            GROUP BY u.id, p.name
        ),
        metrics_base AS (
            SELECT
                engineer_name AS ingeniero,
                tickets_asignados AS "Tickets Asignados",
                tickets_cerrados AS "Tickets Cerrados",
                ROUND(CASE WHEN tickets_asignados = 0 THEN 0 ELSE (tickets_cerrados * 100.0 / tickets_asignados) END, 2) AS "Porcentaje Cumplimiento",
                ROUND(COALESCE(sla_cumplido, 0), 2) AS "SLA Cumplido (%)",
                tickets_vencidos AS "Tickets Vencidos",
                "Tickets Baja",
                "Tickets Media",
                "Tickets Alta",
                "Tickets Crítica",
                ROUND(
                    ({PESO_CIERRE} * COALESCE(
                        CASE WHEN tickets_asignados = 0 THEN 0 ELSE (tickets_cerrados * 100.0 / tickets_asignados) END, 0)
                    + {PESO_SLA} * COALESCE(sla_cumplido, 0)
                    ) / ({PESO_CIERRE} + {PESO_SLA}), 2
                ) AS "Puntaje Ponderado",
                CASE
                    WHEN (tickets_cerrados * 100.0 / NULLIF(tickets_asignados, 0)) >= 95 THEN 'Alto'
                    WHEN (tickets_cerrados * 100.0 / NULLIF(tickets_asignados, 0)) >= 80 THEN 'Medio'
                    ELSE 'Bajo'
                END AS comportamiento,
                CASE
                    WHEN COALESCE(sla_cumplido, 0) >= 90 THEN '🟢'
                    WHEN COALESCE(sla_cumplido, 0) >= 75 THEN '🟡'
                    ELSE '🔴'
                END AS estatus
            FROM weekly_tickets
        ),
        metrics_final AS (
            SELECT
                *,
                CASE
                    WHEN estatus = '🟢' AND comportamiento IN ('Alto', 'Medio') THEN '🟢 EXCELENTE'
                    WHEN estatus = '🟢' AND comportamiento = 'Bajo' THEN '🟢 BUENO'
                    WHEN estatus = '🟡' AND comportamiento = 'Alto' THEN '🟢 BUENO'
                    WHEN estatus = '🟡' AND comportamiento = 'Medio' THEN '🟡 REGULAR'
                    WHEN estatus = '🟡' AND comportamiento = 'Bajo' THEN '🟠 MEJORABLE'
                    WHEN estatus = '🔴' AND comportamiento = 'Alto' THEN '🟠 MEJORABLE'
                    WHEN estatus = '🔴' AND comportamiento IN ('Medio', 'Bajo') THEN '🔴 DEFICIENTE'
                END AS resultado,
                CASE
                    WHEN estatus = '🟢' AND comportamiento IN ('Alto', 'Medio') THEN 5
                    WHEN estatus = '🟢' AND comportamiento = 'Bajo' THEN 4
                    WHEN estatus = '🟡' AND comportamiento = 'Alto' THEN 4
                    WHEN estatus = '🟡' AND comportamiento = 'Medio' THEN 3
                    WHEN estatus = '🟡' AND comportamiento = 'Bajo' THEN 2
                    WHEN estatus = '🔴' AND comportamiento = 'Alto' THEN 2
                    WHEN estatus = '🔴' AND comportamiento IN ('Medio', 'Bajo') THEN 1
                END AS resultado_valor
            FROM metrics_base
        )
        SELECT
            ingeniero,
            "Tickets Asignados",
            "Tickets Cerrados",
            "Porcentaje Cumplimiento",
            "SLA Cumplido (%)",
            "Tickets Vencidos",
            "Tickets Baja",
            "Tickets Media",
            "Tickets Alta",
            "Tickets Crítica",
            "Puntaje Ponderado",
            resultado,
            RANK() OVER (ORDER BY "Puntaje Ponderado" DESC, "Tickets Vencidos" ASC) AS ranking
        FROM metrics_final
        ORDER BY ranking;
        """

        df = pd.read_sql(query, conn)
        conn.close()
        tunnel.stop()
        print("Datos obtenidos correctamente.")
        return df

    except Exception as e:
        print(f"Error al obtener datos: {e}")
        return pd.DataFrame()


def generate_report_html(df, start_date, end_date):
    if df.empty:
        return "<h3>No hay datos para el período seleccionado.</h3>"

    df_cierre = df[['ingeniero', 'Tickets Asignados', 'Tickets Cerrados', 'Porcentaje Cumplimiento', 'Tickets Vencidos',
                    'Tickets Baja', 'Tickets Media', 'Tickets Alta', 'Tickets Crítica']].copy()
    df_cierre = df_cierre.sort_values('Porcentaje Cumplimiento', ascending=False)

    df_sla = df[['ingeniero', 'SLA Cumplido (%)']].copy()
    df_sla = df_sla.sort_values('SLA Cumplido (%)', ascending=False)

    df_ranking = df[['ingeniero', 'Puntaje Ponderado', 'resultado', 'ranking']].copy()
    df_ranking = df_ranking.sort_values('ranking', ascending=True).rename(columns={'resultado': 'Resultado'})

    # Función para convertir DataFrame a HTML con estilos inline
    def df_to_inline_html(df, table_class=''):
        if df.empty:
            return "<p>Sin datos</p>"
        # Obtener nombres de columnas
        cols = df.columns.tolist()
        # Generar encabezados
        header_html = ''.join([f'<th style="border:1px solid #ddd;padding:8px;background-color:#f2f2f2;text-align:center;font-size:14px;">{col}</th>' for col in cols])
        # Generar filas
        rows_html = ''
        for _, row in df.iterrows():
            cells_html = ''
            for col in cols:
                val = row[col]
                # Aplicar color especial a la columna 'Resultado' si existe
                if col == 'Resultado':
                    # Mapeo de colores según categoría
                    color_map = {
                        'EXCELENTE': '#d4edda',
                        'BUENO': '#c3e6cb',
                        'REGULAR': '#fff3cd',
                        'MEJORABLE': '#ffe5b4',
                        'DEFICIENTE': '#f8d7da'
                    }
                    bg = color_map.get(val.split()[-1] if ' ' in val else val, 'transparent')
                    cells_html += f'<td style="border:1px solid #ddd;padding:8px;text-align:center;background-color:{bg};font-size:13px;">{val}</td>'
                else:
                    cells_html += f'<td style="border:1px solid #ddd;padding:8px;text-align:center;font-size:13px;">{val}</td>'
            rows_html += f'<tr>{cells_html}</tr>'
        return f'<table style="border-collapse:collapse;width:100%;margin-bottom:20px;font-family:Arial,sans-serif;">{header_html}{rows_html}</table>'

    # Generar HTML de cada tabla
    html_cierre = df_to_inline_html(df_cierre)
    html_sla = df_to_inline_html(df_sla)
    html_ranking = df_to_inline_html(df_ranking)

    html_content = f"""
    <html>
    <head>
        <meta charset="UTF-8">
    </head>
    <body style="font-family:Arial,sans-serif;margin:20px;color:#333;">
        <h2 style="color:#333;">🤖 Dashboard Ingenieros de Soporte</h2>
        <p><strong>Período:</strong> {start_date} - {end_date}</p>
        <h3 style="color:#333;">📋 Cierre de Tickets</h3>
        {html_cierre}
        <h3 style="color:#333;">📋 Cumplimiento de SLA</h3>
        {html_sla}
        <h3 style="color:#333;">🏆 Ranking Ponderado</h3>
        {html_ranking}
        <p style="font-size:12px;color:#999;"><em>Reporte generado automáticamente el {datetime.now().strftime('%d/%m/%Y %H:%M')}</em></p>
    </body>
    </html>
    """
    return html_content


def send_email_report(start_date=None, end_date=None):
    """Envía el reporte por correo usando las fechas proporcionadas o la semana actual."""
    print("Iniciando envío de reporte por correo...")
    
    # Si no se proporcionan fechas, usar la semana actual
    if start_date is None or end_date is None:
        start, end = get_week_range()
    else:
        # Convertir strings a date si es necesario
        if isinstance(start_date, str):
            start = datetime.strptime(start_date, '%Y-%m-%d').date()
        else:
            start = start_date
        if isinstance(end_date, str):
            end = datetime.strptime(end_date, '%Y-%m-%d').date()
        else:
            end = end_date

    start_str = start.strftime('%Y-%m-%d')
    end_str = (end + timedelta(days=1)).strftime('%Y-%m-%d')

    df = get_helpdesk_metrics(start_str, end_str)
    if df.empty:
        return "No hay datos para el período seleccionado."

    html_content = generate_report_html(df, start.strftime('%d/%m/%Y'), end.strftime('%d/%m/%Y'))

    msg = MIMEMultipart('alternative')
    msg['Subject'] = f"🤖 Reporte de Soporte {start.strftime('%d/%m')} - {end.strftime('%d/%m')}"
    msg['From'] = EMAIL_SENDER
    msg['To'] = EMAIL_RECIPIENT

    part = MIMEText(html_content, 'html')
    msg.attach(part)

    try:
        if SMTP_ENCRYPTION == 'ssl':
            with smtplib.SMTP_SSL(EMAIL_SMTP_SERVER, EMAIL_SMTP_PORT) as server:
                server.login(EMAIL_SENDER, EMAIL_PASSWORD)
                server.send_message(msg)
        else:
            with smtplib.SMTP(EMAIL_SMTP_SERVER, EMAIL_SMTP_PORT) as server:
                server.starttls()
                server.login(EMAIL_SENDER, EMAIL_PASSWORD)
                server.send_message(msg)
        return f"Correo enviado correctamente a {EMAIL_RECIPIENT}"
    except Exception as e:
        return f"Error al enviar correo: {str(e)}"


# --- Estilos para la tabla de ranking ---
STYLE_CONDITIONAL = [
    {'if': {'filter_query': '{resultado} contains "EXCELENTE"'}, 'backgroundColor': '#d4edda', 'color': '#155724'},
    {'if': {'filter_query': '{resultado} contains "BUENO"'}, 'backgroundColor': '#c3e6cb', 'color': '#155724'},
    {'if': {'filter_query': '{resultado} contains "REGULAR"'}, 'backgroundColor': '#fff3cd', 'color': '#856404'},
    {'if': {'filter_query': '{resultado} contains "MEJORABLE"'}, 'backgroundColor': '#ffe5b4', 'color': '#856404'},
    {'if': {'filter_query': '{resultado} contains "DEFICIENTE"'}, 'backgroundColor': '#f8d7da', 'color': '#721c24'},
    {'if': {'state': 'selected'}, 'backgroundColor': '#0D6580', 'color': 'white'},
    {'if': {'state': 'active'}, 'backgroundColor': '#0D6580', 'color': 'white'}
]

STYLE_CELL_CONDITIONAL = [
    {'if': {'column_id': 'ingeniero'}, 'textAlign': 'left'},
    {'if': {'column_id': 'resultado'}, 'textAlign': 'left'}
]


# --- Inicializar la app con Bootstrap ---
app = dash.Dash(__name__, external_stylesheets=[dbc.themes.BOOTSTRAP])
app.title = "Dashboard Ingenieros de Soporte"

start_default, end_default = get_week_range()

# --- Layout ---
app.layout = html.Div([
    # Almacén para controlar la visibilidad del toast
    dcc.Store(id='toast-store', data={'is_open': False, 'message': '', 'color': 'success'}),

    # Contenedor de toasts (flotante en la esquina superior derecha)
    html.Div(
        id='toast-container',
        style={
            'position': 'fixed',
            'top': '20px',
            'right': '20px',
            'zIndex': 1050,
            'maxWidth': '400px'
        }
    ),

    dbc.Container([
        dbc.Row([
            dbc.Col(html.H2("🤖 Dashboard Ingenieros de Soporte", className="text-center mt-2"), width=12)
        ]),

        dbc.Row([
            dbc.Col([
                html.Label("Periodo:"),
                dcc.DatePickerRange(
                    id='date-range',
                    start_date=start_default,
                    end_date=end_default,
                    display_format='DD/MM/YYYY',
                    style={'marginLeft': '10px'}
                )
            ], width=5, className="d-flex align-items-center"),
            dbc.Col(html.Div(id='selected-range', className="text-center font-weight-bold"), width=4),
            dbc.Col(
                dbc.Button(
                    "Send 🛸",
                    id="btn-enviar-correo",
                    color="primary",
                    size="sm",
                    className="float-end"
                ),
                width=3
            ),
        ], className="mb-2"),

        # Tarjetas de KPI
        dbc.Row([
            dbc.Col(dbc.Card(dbc.CardBody([
                html.H6("Ingenieros", className="card-title", style={'fontSize': '14px'}),
                html.H2(id="kpi-ingenieros", children="0", className="card-text text-primary", style={'fontSize': '20px'})
            ]), color="light", style={'padding': '2px'}), width=3),
            dbc.Col(dbc.Card(dbc.CardBody([
                html.H6("Tickets Totales", className="card-title", style={'fontSize': '14px'}),
                html.H2(id="kpi-tickets", children="0", className="card-text text-primary", style={'fontSize': '20px'})
            ]), color="light", style={'padding': '2px'}), width=3),
            dbc.Col(dbc.Card(dbc.CardBody([
                html.H6("Prom. Cumplimiento", className="card-title", style={'fontSize': '14px'}),
                html.H2(id="kpi-cumplimiento", children="0%", className="card-text text-success", style={'fontSize': '20px'})
            ]), color="light", style={'padding': '2px'}), width=3),
            dbc.Col(dbc.Card(dbc.CardBody([
                html.H6("Prom. SLA", className="card-title", style={'fontSize': '14px'}),
                html.H2(id="kpi-sla", children="0%", className="card-text text-success", style={'fontSize': '20px'})
            ]), color="light", style={'padding': '2px'}), width=3),
        ], className="mb-2"),

        # Gráficas
        dbc.Row([
            dbc.Col([
                html.H5("% Cumplimiento por Ingeniero", className="text-center", style={'fontSize': '14px', 'marginBottom': '2px'}),
                dcc.Graph(id='chart-cierre', config={'displayModeBar': False}, style={'height': '200px'})
            ], width=6),
            dbc.Col([
                html.H5("% SLA Cumplido por Ingeniero", className="text-center", style={'fontSize': '14px', 'marginBottom': '2px'}),
                dcc.Graph(id='chart-sla', config={'displayModeBar': False}, style={'height': '200px'})
            ], width=6),
        ], className="mb-2"),

        # Tablas
        dbc.Row([
            dbc.Col([
                html.H5("Cierre de Tickets", className="text-center", style={'fontSize': '14px', 'marginBottom': '2px'}),
                dash_table.DataTable(
                    id='table-cierre',
                    columns=[
                        {'name': 'Ingeniero', 'id': 'ingeniero'},
                        {'name': 'Asig.', 'id': 'Tickets Asignados'},
                        {'name': 'Cerrados', 'id': 'Tickets Cerrados'},
                        {'name': '% Cumpl.', 'id': 'Porcentaje Cumplimiento', 'format': {'specifier': '.1f'}},
                        {'name': 'Venc.', 'id': 'Tickets Vencidos'},
                        {'name': 'B', 'id': 'Tickets Baja'},
                        {'name': 'M', 'id': 'Tickets Media'},
                        {'name': 'A', 'id': 'Tickets Alta'},
                        {'name': 'C', 'id': 'Tickets Crítica'}
                    ],
                    style_table={'overflowX': 'auto', 'height': '180px', 'overflowY': 'auto'},
                    style_cell={'textAlign': 'center', 'padding': '2px', 'fontSize': '11px'},
                    style_header={'backgroundColor': '#f5f5f5', 'fontWeight': 'bold', 'fontSize': '11px'}
                )
            ], width=6),
            dbc.Col([
                html.H5("Cumplimiento de SLA", className="text-center", style={'fontSize': '14px', 'marginBottom': '2px'}),
                dash_table.DataTable(
                    id='table-sla',
                    columns=[
                        {'name': 'Ingeniero', 'id': 'ingeniero'},
                        {'name': '% SLA Cumplido', 'id': 'SLA Cumplido (%)', 'format': {'specifier': '.1f'}}
                    ],
                    style_table={'overflowX': 'auto', 'height': '180px', 'overflowY': 'auto'},
                    style_cell={'textAlign': 'center', 'padding': '2px', 'fontSize': '11px'},
                    style_header={'backgroundColor': '#f5f5f5', 'fontWeight': 'bold', 'fontSize': '11px'}
                )
            ], width=6),
        ], className="mb-2"),

        # Ranking y prioridades
        dbc.Row([
            dbc.Col([
                html.H5("🏆 Ranking Ponderado", className="text-center", style={'fontSize': '14px', 'marginBottom': '2px'}),
                dash_table.DataTable(
                    id='table-ranking',
                    columns=[
                        {'name': 'Ingeniero', 'id': 'ingeniero'},
                        {'name': 'Puntaje', 'id': 'Puntaje Ponderado', 'format': {'specifier': '.2f'}},
                        {'name': 'Resultado', 'id': 'resultado'},
                        {'name': '#', 'id': 'ranking'}
                    ],
                    style_table={'overflowX': 'auto', 'height': '180px', 'overflowY': 'auto'},
                    style_cell={'textAlign': 'center', 'padding': '2px', 'fontSize': '11px'},
                    style_cell_conditional=STYLE_CELL_CONDITIONAL,
                    style_header={'backgroundColor': '#f5f5f5', 'fontWeight': 'bold', 'fontSize': '11px'},
                    style_data_conditional=STYLE_CONDITIONAL
                )
            ], width=4),
            dbc.Col([
                html.H5("Distribución de Prioridades", className="text-center", style={'fontSize': '14px', 'marginBottom': '2px'}),
                dcc.Graph(id='priority-chart', config={'displayModeBar': False}, style={'height': '200px'})
            ], width=8),
        ], className="mb-2"),

        dcc.Interval(id='interval-component', interval=REFRESH_INTERVAL * 1000, n_intervals=0)
    ], fluid=True, style={'padding': '8px'}),
])


# --- Callback para el envío de correo manual ---
@app.callback(
    [Output('toast-store', 'data'),
     Output('toast-container', 'children')],
    [Input('btn-enviar-correo', 'n_clicks')],
    [State('date-range', 'start_date'),
     State('date-range', 'end_date')],
    prevent_initial_call=True
)
def enviar_correo_manual(n_clicks, start_date, end_date):
    if n_clicks is None or n_clicks == 0:
        return {'is_open': False, 'message': '', 'color': 'success'}, []

    # Validar fechas
    if not start_date or not end_date:
        return (
            {'is_open': True, 'message': '⚠️ Selecciona un rango de fechas válido.', 'color': 'warning'},
            dbc.Toast(
                "⚠️ Selecciona un rango de fechas válido.",
                id="toast-message",
                header="Atención",
                icon="warning",
                dismissable=True,
                is_open=True,
                duration=4000,
                style={'position': 'fixed', 'top': '20px', 'right': '20px', 'width': '350px'}
            )
        )

    # Convertir fechas a string YYYY-MM-DD si son objetos date
    if hasattr(start_date, 'strftime'):
        start_date = start_date.strftime('%Y-%m-%d')
    if hasattr(end_date, 'strftime'):
        end_date = end_date.strftime('%Y-%m-%d')

    # Enviar correo
    resultado = send_email_report(start_date, end_date)

    if "Error" in resultado:
        return (
            {'is_open': True, 'message': resultado, 'color': 'danger'},
            dbc.Toast(
                f"❌ {resultado}",
                id="toast-message",
                header="Error",
                icon="danger",
                dismissable=True,
                is_open=True,
                duration=5000,
                style={'position': 'fixed', 'top': '20px', 'right': '20px', 'width': '350px'}
            )
        )
    else:
        return (
            {'is_open': True, 'message': resultado, 'color': 'success'},
            dbc.Toast(
                f"✅ {resultado}",
                id="toast-message",
                header="Éxito",
                icon="success",
                dismissable=True,
                is_open=True,
                duration=4000,
                style={'position': 'fixed', 'top': '20px', 'right': '20px', 'width': '350px'}
            )
        )


# --- Callback principal para actualizar el dashboard ---
@app.callback(
    [Output('table-cierre', 'data'),
     Output('chart-cierre', 'figure'),
     Output('table-sla', 'data'),
     Output('chart-sla', 'figure'),
     Output('table-ranking', 'data'),
     Output('priority-chart', 'figure'),
     Output('selected-range', 'children'),
     Output('kpi-ingenieros', 'children'),
     Output('kpi-tickets', 'children'),
     Output('kpi-cumplimiento', 'children'),
     Output('kpi-sla', 'children')],
    [Input('date-range', 'start_date'),
     Input('date-range', 'end_date'),
     Input('interval-component', 'n_intervals')]
)
def update_dashboard(start_date, end_date, n_intervals):
    if isinstance(start_date, str):
        start_date = datetime.strptime(start_date, '%Y-%m-%d').date()
    if isinstance(end_date, str):
        end_date = datetime.strptime(end_date, '%Y-%m-%d').date()

    if not start_date:
        start_date, _ = get_week_range()
    if not end_date:
        _, end_date = get_week_range()

    if start_date > end_date:
        start_date, end_date = end_date, start_date

    start_str = start_date.strftime('%Y-%m-%d')
    end_str = (end_date + timedelta(days=1)).strftime('%Y-%m-%d')

    df = get_helpdesk_metrics(start_str, end_str)

    range_text = f"Rango: {start_date.strftime('%d/%m/%Y')} - {end_date.strftime('%d/%m/%Y')}"

    if df.empty:
        empty_fig = px.bar(title="Sin datos disponibles")
        return ([], empty_fig, [], empty_fig, [], empty_fig, range_text,
                "0", "0", "0%", "0%")

    # --- KPI ---
    kpi_ingenieros = len(df)
    kpi_tickets = df['Tickets Asignados'].sum()
    kpi_cumplimiento = round(df['Porcentaje Cumplimiento'].mean(), 1)
    kpi_sla = round(df['SLA Cumplido (%)'].mean(), 1)

    # --- Tablas ---
    df_cierre = df[['ingeniero', 'Tickets Asignados', 'Tickets Cerrados', 'Porcentaje Cumplimiento',
                    'Tickets Vencidos', 'Tickets Baja', 'Tickets Media', 'Tickets Alta', 'Tickets Crítica']].copy()
    df_cierre = df_cierre.sort_values('Porcentaje Cumplimiento', ascending=False)
    data_cierre = df_cierre.to_dict('records')

    df_sla = df[['ingeniero', 'SLA Cumplido (%)']].copy()
    df_sla = df_sla.sort_values('SLA Cumplido (%)', ascending=False)
    data_sla = df_sla.to_dict('records')

    df_ranking = df[['ingeniero', 'Puntaje Ponderado', 'resultado', 'ranking']].copy()
    df_ranking = df_ranking.sort_values('ranking', ascending=True)
    data_ranking = df_ranking.to_dict('records')

    # --- Gráficas ---
    fig_cierre = px.bar(
        df_cierre,
        x='Porcentaje Cumplimiento',
        y='ingeniero',
        orientation='h',
        text='Porcentaje Cumplimiento',
        labels={'ingeniero': '', 'Porcentaje Cumplimiento': '% Cumpl.'}
    )
    fig_cierre.update_traces(texttemplate='%{text:.1f}%', textposition='outside')
    fig_cierre.update_layout(height=190, margin=dict(l=80, r=30, t=10, b=10), yaxis={'categoryorder': 'total ascending'})

    fig_sla = px.bar(
        df_sla,
        x='SLA Cumplido (%)',
        y='ingeniero',
        orientation='h',
        text='SLA Cumplido (%)',
        labels={'ingeniero': '', 'SLA Cumplido (%)': '% SLA'}
    )
    fig_sla.update_traces(texttemplate='%{text:.1f}%', textposition='outside')
    fig_sla.update_layout(height=190, margin=dict(l=80, r=30, t=10, b=10), yaxis={'categoryorder': 'total ascending'})

    # Prioridades
    priority_cols = ['Tickets Baja', 'Tickets Media', 'Tickets Alta', 'Tickets Crítica']
    df_priority = df[['ingeniero'] + priority_cols].copy()
    df_priority_melt = df_priority.melt(id_vars=['ingeniero'], var_name='Prioridad', value_name='Cantidad')
    fig_priority = px.bar(
        df_priority_melt,
        x='ingeniero',
        y='Cantidad',
        color='Prioridad',
        barmode='stack',
        color_discrete_map={
            'Tickets Baja': '#6c757d',
            'Tickets Media': '#ffc107',
            'Tickets Alta': '#fd7e14',
            'Tickets Crítica': '#dc3545'
        },
        labels={'ingeniero': '', 'Cantidad': '', 'Prioridad': ''}
    )
    fig_priority.update_layout(height=190, margin=dict(l=30, r=30, t=10, b=10), xaxis_tickangle=-45)

    return (data_cierre, fig_cierre,
            data_sla, fig_sla,
            data_ranking, fig_priority,
            range_text,
            str(kpi_ingenieros), str(kpi_tickets),
            f"{kpi_cumplimiento}%", f"{kpi_sla}%")

# --- Programar envío automático de correo ---
scheduler = BackgroundScheduler()
scheduler.add_job(lambda: send_email_report(), 'cron', day_of_week='fri', hour=16, minute=25)
scheduler.start()
atexit.register(lambda: scheduler.shutdown())

if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=8050)