import sys
import os
import time
import logging
from datetime import datetime, timedelta, date
from io import BytesIO

# Importes de terceros
import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment, Font, Protection
from openpyxl.drawing.image import Image as ExcelImg
import matplotlib.pyplot as plt
import matplotlib.dates as mdates

# Importes internos
from .reports_utils import (
    get_styles,
    add_markwater_images,
    logger,
    get_escritorio_path
)

# Configuración Matplotlib no interactiva
plt.switch_backend('Agg')

# Constantes de Diseño
VERTICAL_OFFSET = 17
HORIZONTAL_OFFSET = 2
COLUMNS_PER_SENSOR = 4  
SPACING_BETWEEN_SENSORS = 1 

# Factor de conversión ajustado para márgenes y fuentes
PIXELS_PER_COLUMN_UNIT = 9.5  

# Credenciales y Metadatos
SHEET_PASSWORD = "itgexcel"
WORKBOOK_AUTHOR = "itg"

# Nombres de columnas
FLUX_HEADERS = ["Hora", "Caudal (L/s)", "Acumulado (L)", "Estado Comunicacion"]

def get_flux_data_for_day(db, target_date, centro_id):
    """Obtiene los datos de un día específico."""
    collection_name = f"{centro_id.lower()}_flux_ts"
    
    if collection_name not in db.list_collection_names():
        return []

    collection = db[collection_name]
    start_dt = datetime.combine(target_date, datetime.min.time())
    end_dt = datetime.combine(target_date, datetime.max.time())

    query = {"timestamp": {"$gte": start_dt, "$lte": end_dt}}
    projection = {"_id": 0, "timestamp": 1, "flow_rate": 1, "cumulant": 1, "connected": 1}

    cursor = collection.find(query, projection).sort("timestamp", 1)
    return list(cursor)

def _style_flux_headers(ws, col_start, col_end, font, fill, border, centro_name, row_title, row_header):
    """Estiliza el bloque de encabezados."""
    # Título Fusionado
    cell_title = ws.cell(row=row_title, column=col_start)
    cell_title.value = f"Flujometro: {centro_name}"
    cell_title.font = font
    cell_title.fill = fill
    cell_title.alignment = Alignment(horizontal="center", vertical="center")
    
    ws.merge_cells(start_row=row_title, start_column=col_start, end_row=row_title, end_column=col_end)
    
    for c in range(col_start, col_end + 1):
        ws.cell(row=row_title, column=c).border = border

    # Subtítulos
    for i, header in enumerate(FLUX_HEADERS):
        cell = ws.cell(row=row_header, column=col_start + i)
        cell.value = header
        cell.font = font
        cell.fill = fill
        cell.alignment = Alignment(horizontal="center", vertical="center")
        cell.border = border

def clean_value_for_graph(val):
    """Sanitiza valores para el gráfico."""
    if val is None: return None
    try:
        f_val = float(val)
        if f_val == -1.0: return None
        return f_val
    except (ValueError, TypeError):
        return None

def insert_flux_graph(ws, data_rows, anchor_cell, centro_name, custom_width_pixels):
    """
    Genera e inserta el gráfico con un ancho dinámico calculado.
    Incluye franjas rojas verticales cuando hay desconexión (-1).
    """
    if not data_rows: return

    # 1. Preparar datos
    ts = []
    flow = []
    cumulant = []
    bad_data_mask = [] # Máscara booleana para las franjas rojas
    
    for d in data_rows:
        ts.append(d['timestamp'])
        
        # Obtenemos valores crudos para la lógica de la franja roja
        r_flow = d.get('flow_rate')
        r_cum = d.get('cumulant')
        
        # Limpiamos valores para la línea (convertir -1 a None)
        flow.append(clean_value_for_graph(r_flow))
        cumulant.append(clean_value_for_graph(r_cum))

        # Lógica de Franja Roja: Si cualquiera es -1, marcamos True
        # Nota: comparamos r_flow == -1 directamente (entero o float)
        is_bad = False
        try:
            if r_flow is not None and float(r_flow) == -1.0: is_bad = True
            if r_cum is not None and float(r_cum) == -1.0: is_bad = True
        except:
            pass # Si no se puede convertir a float, asumimos que no es -1 (o es data corrupta ignorada)
        
        bad_data_mask.append(is_bad)

    # 2. Configuración Gráfico
    plt.rcParams.update({'font.size': 10})
    
    fig, ax1 = plt.subplots(figsize=(10, 5), dpi=100) 

    # --- PINTAR FRANJAS ROJAS DE ERROR ---
    # Usamos fill_between con transform=ax1.get_xaxis_transform()
    # Esto significa: X usa coordenadas de datos (fechas), Y usa coordenadas de ejes (0=abajo, 1=arriba)
    if any(bad_data_mask):
        ax1.fill_between(ts, 0, 1, where=bad_data_mask, 
                         transform=ax1.get_xaxis_transform(), 
                         color='red', alpha=0.2, zorder=1, label='Sin Comunicación')

    # Eje 1: Caudal
    color_flow = '#1e4882' 
    ax1.set_xlabel('Hora')
    label_y1 = FLUX_HEADERS[1] if len(FLUX_HEADERS) > 1 else 'Caudal'
    ax1.set_ylabel(label_y1, color=color_flow)
    
    ax1.plot(ts, flow, color=color_flow, linewidth=1.5, label='Caudal', zorder=2)
    ax1.tick_params(axis='y', labelcolor=color_flow)
    
    valid_flow = [x for x in flow if x is not None]
    if valid_flow: ax1.set_ylim(bottom=0)

    # Eje 2: Acumulado
    ax2 = ax1.twinx()
    color_cum = '#2ca02c' 
    label_y2 = FLUX_HEADERS[2] if len(FLUX_HEADERS) > 2 else 'Acumulado'
    ax2.set_ylabel(label_y2, color=color_cum)
    
    ax2.plot(ts, cumulant, color=color_cum, linestyle='--', linewidth=1.5, label='Acumulado', zorder=2)
    ax2.tick_params(axis='y', labelcolor=color_cum)

    ax1.xaxis.set_major_formatter(mdates.DateFormatter('%H:%M'))
    fig.autofmt_xdate()
    ax1.set_title(f"Flujometro - {centro_name}")
    ax1.grid(True, alpha=0.3)

    # 3. Guardar imagen sin bordes blancos
    img_data = BytesIO()
    plt.savefig(img_data, format='png', bbox_inches='tight', pad_inches=0.1)
    plt.close(fig)
    img_data.seek(0)

    # Insertar en Excel
    img = ExcelImg(img_data)
    img.width = int(custom_width_pixels)
    img.height = 300 
    
    ws.add_image(img, anchor_cell)

def process_flux_day(db, workbook, date_obj, centro_ids):
    """Procesa una hoja completa para un día específico."""
    sheet_name = date_obj.strftime("%d-%m-%Y")
    ws = workbook.create_sheet(title=sheet_name)

    # Metadatos de Usuario
    workbook.properties.creator = WORKBOOK_AUTHOR
    workbook.properties.lastModifiedBy = WORKBOOK_AUTHOR

    font, fill, fill_iny, fill_iny_man, fill_vdf, border, fill_alarm = get_styles()
    red_font = Font(color="FF0000")
    
    # Marca de Agua
    img_path = os.path.join(os.path.dirname(__file__), "images", "Logo_Reportes.png")
    if os.path.exists(img_path):
        add_markwater_images(ws, img_path, start_column="C", start_row=VERTICAL_OFFSET + 1, 
                             repeats=len(centro_ids), step=COLUMNS_PER_SENSOR + SPACING_BETWEEN_SENSORS + 1)

    # ws.freeze_panes = ws.cell(row=VERTICAL_OFFSET + 3, column=1)

    row_title_merged = VERTICAL_OFFSET + 1
    row_headers = VERTICAL_OFFSET + 2
    row_data_start = VERTICAL_OFFSET + 3

    # Freeze Panes (Congelar desde los datos hacia arriba)
    ws.freeze_panes = ws.cell(row=row_data_start, column=1)

    has_data = False

    for idx, centro_id in enumerate(centro_ids):
        # 1. Definir Coordenadas
        col_start_idx = HORIZONTAL_OFFSET + 1 + (idx * (COLUMNS_PER_SENSOR + SPACING_BETWEEN_SENSORS))
        col_end_idx = col_start_idx + COLUMNS_PER_SENSOR - 1
        col_start_letter = get_column_letter(col_start_idx)
        
        data = get_flux_data_for_day(db, date_obj, centro_id)
        
        # 2. Estilos de Encabezado
        _style_flux_headers(ws, col_start_idx, col_end_idx, font, fill, border, 
                            centro_id.capitalize(), row_title_merged, row_headers)

        if not data: continue
        has_data = True

        # 3. Pre-procesamiento para determinar ancho de columnas
        column_widths = {i: len(str(h)) for i, h in enumerate(FLUX_HEADERS)}

        for row_data in data:
            raw_flow = row_data.get('flow_rate')
            raw_cum = row_data.get('cumulant')
            is_conn = row_data.get('connected')
            
            val_ts = row_data['timestamp'].strftime("%H:%M:%S")
            
            # Conversión temporal solo para medir largo del texto
            if raw_flow is not None and raw_flow != -1:
                v_flow_str = str(round(float(raw_flow), 2))
            else:
                v_flow_str = ""

            if raw_cum is not None and raw_cum != -1:
                v_cum_str = str(round(float(raw_cum), 2))
            else:
                v_cum_str = ""
            
            val_state = "Desc." if (raw_flow == -1 or is_conn is False) else "Conectado"
            
            row_vals = [val_ts, v_flow_str, v_cum_str, val_state]
            
            for i, txt in enumerate(row_vals):
                if len(txt) > column_widths[i]:
                    column_widths[i] = len(txt)

        # 4. Asignar Anchos a Excel y Calcular Total Pixeles
        total_width_excel_units = 0
        
        for i in range(COLUMNS_PER_SENSOR):
            calc_width = column_widths[i] + 3 
            final_width = max(12, min(calc_width, 50))
            
            col_letter = get_column_letter(col_start_idx + i)
            ws.column_dimensions[col_letter].width = final_width
            total_width_excel_units += final_width

        total_pixels_graph = (total_width_excel_units * PIXELS_PER_COLUMN_UNIT)

        # 5. Generar Gráfico
        graph_anchor = f"{col_start_letter}2"
        insert_flux_graph(ws, data, graph_anchor, centro_id, total_pixels_graph)

        # 6. Escribir Datos
        for r_idx, row_data in enumerate(data):
            current_row = row_data_start + r_idx
            
            raw_flow = row_data.get('flow_rate')
            raw_cum = row_data.get('cumulant')
            is_connected = row_data.get('connected')

            # --- LÓGICA DE VALORES Y REDONDEO ---
            # Flow
            val_flow = None
            if raw_flow is not None and raw_flow != -1:
                try: val_flow = round(float(raw_flow), 2)
                except: val_flow = None

            # Cumulant
            val_cum = None
            if raw_cum is not None and raw_cum != -1:
                try: val_cum = round(float(raw_cum), 2)
                except: val_cum = None
            
            # Conexión
            val_conn = "Desconectado" if (raw_flow == -1 or is_connected is False) else "Conectado"

            vals = [
                row_data['timestamp'].strftime("%H:%M:%S"),
                val_flow,
                val_cum,
                val_conn
            ]

            for i, val in enumerate(vals):
                cell = ws.cell(row=current_row, column=col_start_idx + i, value=val)
                cell.border = border
                cell.alignment = Alignment(horizontal='center', vertical='center')

                # Bloquear celda explícitamente (es el default, pero aseguramos)
                cell.protection = Protection(locked=True)
                
                # Formato Numérico
                if i in [1, 2] and val is not None:
                    cell.number_format = '0.00'

                if i == 3 and val == "Desc.": 
                    cell.font = red_font

    # --- PROTECCIÓN DE HOJA ---
    ws.protection.sheet     = True
    ws.protection.objects   = True # Protege gráficos
    ws.protection.scenarios = True
    ws.protection.password  = SHEET_PASSWORD

    return has_data