from flask import render_template, redirect, url_for, flash, request, jsonify, send_file
from flask import current_app as app
from flask_login import login_required, current_user
import os
import json
from datetime import datetime
from ...models.rate import Rate
from functools import wraps
import uuid
from ...extensions import csrf
import io
import csv

from app.models.article import Article
from app.models.user import User  # Añadir importación de User
from app.models.invoice_lead import InvoiceLead
from app.models.role import Role
from app.forms.admin_forms import AddUserForm, EditUserForm, EmptyForm, RateLimitSettingsForm
from app.models.setting import Setting
from app.services.rate_limit_service import (
    get_rate_limit,
    enable_strict_mode,
    disable_strict_mode,
    strict_status,
)
from app.extensions import db, bcrypt
from . import admin_bp

# Decorador para restringir acceso solo a Super Administradores
def admin_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if not current_user.is_authenticated: # Ya debería ser manejado por @login_required
            flash('Debes iniciar sesión para acceder a esta página.', 'warning')
            return redirect(url_for('auth_bp.login', next=request.path))
        
        # Asegurar que current_user tiene el método has_role y el rol correcto
        # Verificar tanto 'Super Admin' como 'Super Administrador'
        if not hasattr(current_user, 'has_role') or (not current_user.has_role('Super Admin') and not current_user.has_role('Super Administrador')):
            flash('Acceso restringido. Se requieren permisos de Super Administrador.', 'danger')
            return redirect(url_for('public.home')) # Redirigir a la página principal del sitio
        return f(*args, **kwargs)
    return decorated_function

# Decorador para permitir acceso a Super Admins y Comerciales
def admin_or_comercial_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if not current_user.is_authenticated:
            flash('Debes iniciar sesión para acceder a esta página.', 'warning')
            return redirect(url_for('auth_bp.login', next=request.path))
        
        # Permitir acceso a Super Admin/Administrador y Comercial
        if not hasattr(current_user, 'has_role') or (
            not (current_user.has_role('Super Admin') or current_user.has_role('Super Administrador')) and 
            not current_user.has_role('Comercial')
        ):
            flash('Acceso restringido. No tienes los permisos necesarios.', 'danger')
            return redirect(url_for('public.home'))
        return f(*args, **kwargs)
    return decorated_function

# Decorador para permitir acceso a Super Admin/Administrador y Analista de precios
def admin_or_analyst_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if not current_user.is_authenticated:
            flash('Debes iniciar sesión para acceder a esta página.', 'warning')
            return redirect(url_for('auth_bp.login', next=request.path))

        # Permitir acceso a Super Admin/Administrador y Analista de precios
        if not hasattr(current_user, 'has_role') or (
            not (current_user.has_role('Super Admin') or current_user.has_role('Super Administrador')) and 
            not current_user.has_role('Analista de precios')
        ):
            flash('Acceso restringido. No tienes los permisos necesarios.', 'danger')
            return redirect(url_for('public.home'))
        return f(*args, **kwargs)
    return decorated_function

# Ruta temporal para asignar rol Super Admin al usuario 1
@admin_bp.route('/fix_admin')
@login_required
def fix_admin_access():
    """Ruta temporal para arreglar el acceso del administrador."""
    try:
        user = User.query.get(1)  # Usuario con ID 1
        if not user:
            flash('Usuario administrador no encontrado.', 'danger')
            return redirect(url_for('public.home'))
            
        # Obtener o crear el rol Super Admin
        admin_role = Role.query.filter_by(name='Super Admin').first()
        if not admin_role:
            admin_role = Role(name='Super Admin', description='Acceso completo a todas las funciones.')
            db.session.add(admin_role)
            
        # Verificar si ya tiene el rol
        if admin_role in user.roles.all():
            flash(f'El usuario {user.username} ya tiene rol Super Admin.', 'info')
        else:
            # Asignar el rol
            user.roles.append(admin_role)
            db.session.commit()
            flash(f'¡Rol Super Admin asignado exitosamente a {user.username}!', 'success')
            
        return redirect(url_for('admin.index'))
    except Exception as e:
        flash(f'Error al asignar rol: {str(e)}', 'danger')
        return redirect(url_for('public.home'))

# Ruta para el panel principal de administración
@admin_bp.route('/')
@login_required
@admin_required
def index():
    """Panel de control principal de administración."""
    return render_template('admin/index.html', title="Panel de Administración")

# Ruta para listar leads
@admin_bp.route('/leads')
@login_required
@admin_or_comercial_required
def list_leads():
    """Lista todos los leads con filtros avanzados"""
    try:
        # Parámetros de paginación
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 25, type=int)
        
        # Parámetros de filtro
        filter_id = request.args.get('id')
        filter_name = request.args.get('name')
        filter_email = request.args.get('email')
        filter_phone = request.args.get('phone')
        filter_cups = request.args.get('cups')
        filter_status = request.args.get('status')
        filter_tariff = request.args.get('tariff')
        filter_supplier = request.args.get('supplier')
        filter_channel = request.args.get('channel')
        filter_date_from_s = request.args.get('date_from')
        filter_date_until_s = request.args.get('date_until')
        
        # Construir la consulta base
        query = InvoiceLead.query
        
        # Aplicar filtros
        if filter_id:
            query = query.filter(InvoiceLead.id == filter_id)
        if filter_name:
            query = query.filter(InvoiceLead.name.ilike(f"%{filter_name}%"))
        if filter_email:
            query = query.filter(InvoiceLead.email.ilike(f"%{filter_email}%"))
        if filter_phone:
            query = query.filter(InvoiceLead.phone.ilike(f"%{filter_phone}%"))
        if filter_cups:
            query = query.filter(InvoiceLead.cups.ilike(f"%{filter_cups}%"))
        if filter_status:
            query = query.filter(InvoiceLead.status == filter_status)
        if filter_tariff:
            query = query.filter(InvoiceLead.tariff_type.ilike(f"%{filter_tariff}%"))
        if filter_channel:
            if filter_channel.startswith('commercial_'):
                # Filtrar por comercial específico
                commercial_id = filter_channel.replace('commercial_', '')
                try:
                    commercial_id = int(commercial_id)
                    query = query.filter(
                        InvoiceLead.channel == 'commercial',
                        InvoiceLead.created_by_user_id == commercial_id
                    )
                except ValueError:
                    pass  # ID inválido, ignorar filtro
            else:
                query = query.filter(InvoiceLead.channel == filter_channel)
        if filter_supplier:
            # Filtrar por comercializadora en los detalles JSON
            query = query.filter(InvoiceLead._details.like(f'%"best_rate_supplier":"%{filter_supplier}%"'))
        if filter_date_from_s:
            try:
                df = datetime.strptime(filter_date_from_s, '%Y-%m-%d').date()
                query = query.filter(InvoiceLead.submitted_at >= df)
            except ValueError:
                app.logger.warning(f"Fecha 'date_from' inválida: {filter_date_from_s}")
        if filter_date_until_s:
            try:
                du = datetime.strptime(filter_date_until_s, '%Y-%m-%d').date()
                query = query.filter(InvoiceLead.submitted_at <= du)
            except ValueError:
                app.logger.warning(f"Fecha 'date_until' inválida: {filter_date_until_s}")
        
        # Ordenar por fecha de envío descendente
        query = query.order_by(InvoiceLead.submitted_at.desc())
        
        # Paginar resultados
        pagination = query.paginate(page=page, per_page=per_page, error_out=False)
        leads = pagination.items
    except Exception as e:
        app.logger.error(f"Error al cargar leads: {e}")
        leads = []
        pagination = None
        flash(f'Error al cargar leads: {e}', 'error')
    
    # Pasamos el rol del usuario para ajustar la UI
    is_admin = current_user.has_role('Super Admin') or current_user.has_role('Super Administrador')
    is_comercial = current_user.has_role('Comercial') and not is_admin
    
    return render_template(
        'admin/leads/list.html', 
        title="Gestión de Leads", 
        leads=leads, 
        is_comercial=is_comercial,
        pagination=pagination,
        current_page=page,
        per_page=per_page,
        # Valores de filtros para mantener estado en la UI
        filter_id=filter_id or '',
        filter_name=filter_name,
        filter_email=filter_email,
        filter_phone=filter_phone,
        filter_cups=filter_cups,
        filter_status=filter_status,
        filter_tariff=filter_tariff,
        filter_supplier=filter_supplier,
        filter_channel=filter_channel,
        filter_date_from=filter_date_from_s,
        filter_date_until=filter_date_until_s
    )

# Eliminar todos los leads
@admin_bp.route('/leads/delete', methods=['POST'])
@login_required
@admin_required
def delete_leads():
    """Elimina los leads seleccionados o todos si no se selecciona ninguno."""
    ids = request.form.getlist('lead_ids')
    if ids:
        InvoiceLead.query.filter(InvoiceLead.id.in_(ids)).delete(synchronize_session=False)
        flash(f'Se eliminaron {len(ids)} leads seleccionados.', 'success')
    else:
        InvoiceLead.query.delete()
        flash('Todos los leads fueron eliminados.', 'success')
    db.session.commit()
    return redirect(url_for('admin.list_leads'))

# Endpoint JSON para obtener detalle de un lead
@admin_bp.route('/leads/detalle/<int:lead_id>', methods=['GET'])
@login_required
@admin_or_comercial_required
def lead_detail(lead_id):
    try:
        lead = InvoiceLead.query.get_or_404(lead_id)
        lead_data = {
            'id': lead.id,
            'name': lead.name,
            'email': lead.email,
            'phone': lead.phone,
            'cups': lead.cups,
            'status': lead.status,
            'tariff_type': lead.tariff_type,
            'message': lead.message,
            'channel': lead.channel,
            'submitted_at': lead.submitted_at.isoformat() if lead.submitted_at else None,
            'details': lead.details,
            'commercial_comments': lead.commercial_comments,
            'selected_plan': lead.selected_plan,
            'recommended_supplier': lead.recommended_supplier,
            'created_by': {
                'name': lead.created_by.name if lead.created_by else None,
                'username': lead.created_by.username if lead.created_by else None
            } if lead.created_by else None
        }
        return jsonify({'success': True, 'lead': lead_data})
    except Exception as e:
        app.logger.error(f"Error al obtener detalle de lead {lead_id}: {e}")
        return jsonify({'success': False, 'message': str(e)}), 500

# Eliminar un lead individual
@admin_bp.route('/leads/eliminar/<int:lead_id>', methods=['POST'])
@login_required
@admin_required
def delete_lead(lead_id):
    """Elimina un lead individual."""
    try:
        lead = InvoiceLead.query.get_or_404(lead_id)
        app.logger.info(f"Eliminando lead {lead_id}: {lead.name}")
        
        # Eliminar archivo físico si existe
        if lead.file_path and os.path.exists(lead.file_path):
            try:
                os.remove(lead.file_path)
                app.logger.info(f"Archivo eliminado: {lead.file_path}")
            except Exception as file_error:
                app.logger.warning(f"No se pudo eliminar el archivo {lead.file_path}: {file_error}")
        
        db.session.delete(lead)
        db.session.commit()
        app.logger.info(f"Lead {lead_id} eliminado exitosamente")
        
        if request.is_json:
            return jsonify({'success': True, 'message': 'Lead eliminado con éxito'})
        else:
            flash('Lead eliminado con éxito', 'success')
            
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error al eliminar lead {lead_id}: {e}")
        
        if request.is_json:
            return jsonify({'success': False, 'message': f'Error al eliminar el lead: {str(e)}'}), 500
        else:
            flash(f'Error al eliminar el lead: {e}', 'error')
            
    return redirect(url_for('admin.list_leads'))

# Endpoints para filtros dinámicos de leads
@admin_bp.route('/api/leads/comercializadoras', methods=['GET'])
@login_required
@admin_or_comercial_required
def get_lead_suppliers():
    """Devuelve la lista de comercializadoras disponibles en leads"""
    try:
        # Obtener comercializadoras desde los detalles JSON de leads
        suppliers = db.session.query(
            db.func.json_unquote(db.func.json_extract(InvoiceLead._details, '$.best_rate_supplier'))
        ).filter(
            InvoiceLead._details.isnot(None),
            db.func.json_extract(InvoiceLead._details, '$.best_rate_supplier').isnot(None)
        ).distinct().all()
        
        supplier_list = [s[0] for s in suppliers if s[0] and s[0] != 'null']
        supplier_list.sort()
        
        return jsonify({'success': True, 'suppliers': supplier_list})
    except Exception as e:
        app.logger.error(f"Error obteniendo comercializadoras: {e}")
        return jsonify({'success': False, 'message': str(e)}), 500

@admin_bp.route('/api/leads/comerciales', methods=['GET'])
@login_required
@admin_or_comercial_required
def get_lead_commercials():
    """Devuelve la lista de comerciales disponibles"""
    try:
        # Obtener usuarios con rol Comercial o Agent
        commercials = User.query.join(User.roles).filter(
            Role.name.in_(['Comercial', 'Agent'])
        ).all()
        
        app.logger.info(f"Comerciales encontrados: {len(commercials)}")
        
        commercial_list = [{
            'id': c.id,
            'name': c.name or c.username,
            'username': c.username,
            'email': c.email
        } for c in commercials]
        
        app.logger.info(f"Lista de comerciales: {commercial_list}")
        
        return jsonify({'success': True, 'commercials': commercial_list})
    except Exception as e:
        app.logger.error(f"Error obteniendo comerciales: {e}")
        return jsonify({'success': False, 'message': str(e)}), 500

@admin_bp.route('/api/leads/tipos-tarifa', methods=['GET'])
@login_required
@admin_or_comercial_required
def get_lead_tariff_types():
    """Devuelve la lista de tipos de tarifas disponibles en leads"""
    try:
        # Obtener tipos de tarifa distintos de leads
        tariff_types = InvoiceLead.query.with_entities(
            InvoiceLead.tariff_type
        ).filter(
            InvoiceLead.tariff_type.isnot(None)
        ).distinct().all()
        
        tariff_list = [t[0] for t in tariff_types if t[0]]
        tariff_list.sort()
        
        return jsonify({'success': True, 'tariff_types': tariff_list})
    except Exception as e:
        app.logger.error(f"Error obteniendo tipos de tarifa: {e}")
        return jsonify({'success': False, 'message': str(e)}), 500

# Servir archivo adjunto de un lead
@admin_bp.route('/leads/<int:lead_id>/file')
@login_required
@admin_or_comercial_required  # Permitir a comerciales ver PDFs
def get_lead_file(lead_id):
    lead = InvoiceLead.query.get_or_404(lead_id)
    if not lead.file_path or not os.path.exists(lead.file_path):
        flash('Archivo de factura no encontrado.', 'error')
        return redirect(url_for('admin.list_leads'))
    # send_file maneja correctamente PDFs e imágenes
    return send_file(lead.file_path, as_attachment=False)

# Ruta para listar tarifas
@admin_bp.route('/tarifas')
@login_required
@admin_or_comercial_required
def list_rates():
    """Lista todas las tarifas disponibles."""
    try:
        # Parámetros de paginación
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 10, type=int)  # Default 10 por página

        # Parámetros de filtro
        filter_id = request.args.get('id')
        filter_supplier = request.args.get('supplier')
        filter_name = request.args.get('name')
        filter_tariff_type = request.args.get('tariff_type')
        filter_valid_from_s = request.args.get('valid_from')
        filter_valid_until_s = request.args.get('valid_until')

        # Construir la consulta base
        query = Rate.query

        # Aplicar filtros
        if filter_id:
            query = query.filter(Rate.id == filter_id)
        if filter_supplier:
            query = query.filter(Rate.supplier.ilike(f"%{filter_supplier}%"))
        if filter_name:
            query = query.filter(Rate.name.ilike(f"%{filter_name}%"))
        if filter_tariff_type:
            query = query.filter(Rate.tariff_type.ilike(f"%{filter_tariff_type}%"))
        if filter_valid_from_s:
            try:
                vf = datetime.strptime(filter_valid_from_s, '%Y-%m-%d').date()
                query = query.filter(Rate.valid_from >= vf)
            except ValueError:
                app.logger.warning(f"Fecha 'valid_from' inválida: {filter_valid_from_s}")
        if filter_valid_until_s:
            try:
                vu = datetime.strptime(filter_valid_until_s, '%Y-%m-%d').date()
                query = query.filter(Rate.valid_until <= vu)
            except ValueError:
                app.logger.warning(f"Fecha 'valid_until' inválida: {filter_valid_until_s}")

        # Ordenar para consistencia
        query = query.order_by(Rate.name.asc())

        # Paginar resultados
        pagination = query.paginate(page=page, per_page=per_page, error_out=False)
        rates = pagination.items
    except Exception as e:
        app.logger.error(f"Error al cargar tarifas: {e}")
        rates = []
        pagination = None  # Asegurarse de que pagination tenga un valor
        flash(f'Error al cargar tarifas: {e}', 'error')
    
    # Pasamos el rol del usuario para ajustar la UI
    is_admin = current_user.has_role('Super Admin') or current_user.has_role('Super Administrador')
    is_comercial = current_user.has_role('Comercial') and not is_admin
    # Formulario vacío para token CSRF en acciones POST (eliminación)
    csrf_form = EmptyForm()
    return render_template(
        'admin/rates/list.html',
        title="Gestión de Tarifas",
        rates=rates,
        is_comercial=is_comercial,
        pagination=pagination,
        current_page=page,
        per_page=per_page,
        # Valores de filtros para mantener estado en la UI y en la paginación
        filter_id=filter_id or '',
        filter_supplier=filter_supplier,
        filter_name=filter_name,
        filter_tariff_type=filter_tariff_type,
        filter_valid_from=filter_valid_from_s,
        filter_valid_until=filter_valid_until_s,
        csrf_form=csrf_form
    )

# Ruta para crear una nueva tarifa
@admin_bp.route('/tarifas/nuevo', methods=['GET', 'POST'])
@login_required
@admin_required
def create_rate():
    """Formulario para crear una nueva tarifa."""
    if request.method == 'POST':
        try:
            # Crear nueva tarifa basada en form data
            energy_prices = {}
            power_prices = {}
            
            # Procesar precios de energía
            for period in ['P1', 'P2', 'P3', 'P4', 'P5', 'P6']:
                energy_price = request.form.get(f'energy_{period}')
                if energy_price:
                    try:
                        energy_prices[period] = float(energy_price)
                    except ValueError:
                        pass
            
            # Procesar precios de potencia
            for period in ['P1', 'P2', 'P3', 'P4', 'P5', 'P6']:
                power_price = request.form.get(f'power_{period}')
                if power_price:
                    try:
                        power_prices[period] = float(power_price)
                    except ValueError:
                        pass
            
            # Convertir fechas de string a objeto Date
            valid_from = datetime.strptime(request.form.get('valid_from'), '%Y-%m-%d').date() if request.form.get('valid_from') else None
            valid_until = datetime.strptime(request.form.get('valid_until'), '%Y-%m-%d').date() if request.form.get('valid_until') else None
            
            # Crear objeto Rate
            new_rate = Rate(
                name=request.form.get('name'),
                supplier=request.form.get('supplier'),
                tariff_type=request.form.get('tariff_type'),
                energy_prices=energy_prices,
                power_prices=power_prices,
                valid_from=valid_from,
                valid_until=valid_until,
                notes=request.form.get('notes', '')
            )
            
            # Guardar en la base de datos
            db.session.add(new_rate)
            db.session.commit()
            
            flash('Tarifa creada con éxito', 'success')
            return redirect(url_for('admin.list_rates'))
        except Exception as e:
            db.session.rollback()
            app.logger.error(f"Error al crear tarifa: {e}")
            flash(f'Error al crear la tarifa: {e}', 'error')
    return render_template('admin/rates/form.html', title="Nueva Tarifa", rate=None)

# Ruta para editar una tarifa existente
@admin_bp.route('/tarifas/editar/<int:rate_id>', methods=['GET', 'POST'])
@login_required
@admin_required
def edit_rate(rate_id):
    """Formulario para editar una tarifa existente."""
    try:
        # Buscar la tarifa por ID en la base de datos
        rate = Rate.query.get(rate_id)        
        if not rate:
            flash('Tarifa no encontrada', 'error')
            return redirect(url_for('admin.list_rates'))
        if request.method == 'POST':
            app.logger.info(f"Procesando formulario para tarifa ID {rate_id}")
            app.logger.debug(f"Datos recibidos: {request.form}")
            
            # Actualizar datos de la tarifa
            rate.name = request.form.get('name')
            rate.supplier = request.form.get('supplier')
            rate.tariff_type = request.form.get('tariff_type')
            rate.notes = request.form.get('notes', '')
            
            app.logger.debug(f"Datos básicos actualizados: nombre={rate.name}, proveedor={rate.supplier}, tipo={rate.tariff_type}")
            
            # Convertir fechas de string a objeto Date
            if request.form.get('valid_from'):
                rate.valid_from = datetime.strptime(request.form.get('valid_from'), '%Y-%m-%d').date()
                app.logger.debug(f"Fecha válido desde: {rate.valid_from}")
            else:
                # Si no se proporciona fecha, usar la fecha actual
                rate.valid_from = datetime.now().date()
                app.logger.debug(f"Usando fecha actual para valid_from: {rate.valid_from}")
                
            if request.form.get('valid_until'):
                rate.valid_until = datetime.strptime(request.form.get('valid_until'), '%Y-%m-%d').date()
                app.logger.debug(f"Fecha válido hasta: {rate.valid_until}")
            
            # Inicializar diccionarios si son None
            if rate.energy_prices is None:
                rate.energy_prices = {}
            if rate.power_prices is None:
                rate.power_prices = {}
                
            # Actualizar precios de energía
            energy_prices = {}
            for period in ['P1', 'P2', 'P3', 'P4', 'P5', 'P6']:
                energy_price = request.form.get(f'energy_{period}')
                app.logger.debug(f"Procesando energy_{period}: {energy_price}")
                if energy_price and energy_price.strip():
                    energy_prices[period] = float(energy_price)
                    app.logger.debug(f"Precio energía {period} actualizado a {energy_prices[period]}")
            rate.energy_prices = energy_prices
            app.logger.debug(f"Precios de energía actualizados: {rate.energy_prices}")
            
            # Actualizar precios de potencia
            power_prices = {}
            for period in ['P1', 'P2', 'P3', 'P4', 'P5', 'P6']:
                power_price = request.form.get(f'power_{period}')
                app.logger.debug(f"Procesando power_{period}: {power_price}")
                if power_price and power_price.strip():
                    power_prices[period] = float(power_price)
                    app.logger.debug(f"Precio potencia {period} actualizado a {power_prices[period]}")

            rate.power_prices = power_prices
            app.logger.debug(f"Precios de potencia actualizados: {rate.power_prices}")
            
            # Forzar la actualización de la fecha de modificación
            rate.updated_at = datetime.utcnow()
            
            # Guardar cambios en la base de datos
            # Verificar el estado del objeto antes de guardarlo
            app.logger.debug(f"Estado del objeto Rate antes de guardar: {rate.__dict__}")
            app.logger.debug(f"Tipo de energy_prices: {type(rate.energy_prices)}, Contenido: {rate.energy_prices}")
            app.logger.debug(f"Tipo de power_prices: {type(rate.power_prices)}, Contenido: {rate.power_prices}")
            
            # Forzar el commit para asegurar que los cambios se guarden
            db.session.add(rate)
            db.session.commit()
            
            # Verificar que los cambios se guardaron correctamente
            updated_rate = Rate.query.get(rate_id)
            app.logger.debug(f"Estado del objeto Rate después de guardar: {updated_rate.__dict__}")
            app.logger.debug(f"Precios de energía guardados: {updated_rate.energy_prices}")
            app.logger.debug(f"Precios de potencia guardados: {updated_rate.power_prices}")
            
            app.logger.info(f"Tarifa ID {rate_id} actualizada correctamente")
            flash('Tarifa actualizada con éxito', 'success')
            return redirect(url_for('admin.list_rates'))
        
        # Para GET, convertir objeto Rate a diccionario para mantener compatibilidad con la plantilla
        rate_dict = rate.to_dict()
        
    except Exception as e:
        db.session.rollback()
        # Logging detallado para depuración
        app.logger.error(f"Error al editar tarifa ID {rate_id}: {e}")
        app.logger.error(f"Datos del formulario: {request.form}")
        import traceback
        app.logger.error(f"Traceback: {traceback.format_exc()}")
        flash(f'Error al editar la tarifa: {str(e)}', 'error')
        return redirect(url_for('admin.list_rates'))
    
    return render_template('admin/rates/form.html', title="Editar Tarifa", rate=rate_dict)

# Ruta para eliminar una tarifa
@admin_bp.route('/tarifas/eliminar/<int:rate_id>', methods=['POST'])
@login_required
@admin_required
def delete_rate(rate_id):
    """Elimina una tarifa existente."""
    app.logger.info(f"Intentando eliminar tarifa con ID: {rate_id}")
    
    try:
        # Buscar la tarifa por ID en la base de datos
        rate = Rate.query.get(rate_id)
        
        if not rate:
            flash('Tarifa no encontrada', 'error')
            return redirect(url_for('admin.list_rates'))
        
        # Eliminar la tarifa de la base de datos
        db.session.delete(rate)
        db.session.commit()
        
        flash('Tarifa eliminada con éxito', 'success')
        
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error al eliminar tarifa: {e}")
        flash(f'Error al eliminar la tarifa: {e}', 'error')
    
    return redirect(url_for('admin.list_rates'))



# Endpoint JSON para obtener detalle de una tarifa
@admin_bp.route('/tarifas/detalle/<int:rate_id>', methods=['GET'])
@login_required
@admin_or_comercial_required
def rate_detail(rate_id):
    try:
        rate = Rate.query.get_or_404(rate_id)
        return jsonify({'success': True, 'rate': rate.to_dict()})
    except Exception as e:
        app.logger.error(f"Error al obtener detalle de tarifa {rate_id}: {e}")
        return jsonify({'success': False, 'message': str(e)}), 500

# =====================
# Importación/Exportación CSV de Tarifas
# =====================

@admin_bp.route('/tarifas/csv/plantilla', methods=['GET'])
@login_required
@admin_or_analyst_required
def rates_csv_template():
    """Descarga una plantilla CSV con el formato esperado para importar tarifas."""
    try:
        header = [
            'id', 'supplier', 'name', 'tariff_type', 'valid_from', 'valid_until', 'notes',
            'energy_P1', 'energy_P2', 'energy_P3', 'energy_P4', 'energy_P5', 'energy_P6',
            'power_P1', 'power_P2', 'power_P3', 'power_P4', 'power_P5', 'power_P6'
        ]

        si = io.StringIO()
        writer = csv.writer(si)
        writer.writerow(header)
        # Fila de ejemplo (opcional): dejar vacía para evitar confusión

        data = si.getvalue().encode('utf-8-sig')  # BOM para Excel/Windows
        filename = f"plantilla_tarifas.csv"
        return send_file(io.BytesIO(data), mimetype='text/csv', as_attachment=True, download_name=filename)
    except Exception as e:
        app.logger.error(f"Error al generar plantilla CSV: {e}")
        flash(f'Error al generar la plantilla CSV: {e}', 'danger')
        return redirect(url_for('admin.list_rates'))


@admin_bp.route('/tarifas/csv/export', methods=['GET'])
@login_required
@admin_or_comercial_required
def export_rates_csv():
    """Exporta tarifas a CSV aplicando los mismos filtros del listado."""
    try:
        # Filtros (mismos que list_rates)
        filter_id = request.args.get('id')
        filter_supplier = request.args.get('supplier')
        filter_name = request.args.get('name')
        filter_tariff_type = request.args.get('tariff_type')
        filter_valid_from_s = request.args.get('valid_from')
        filter_valid_until_s = request.args.get('valid_until')

        query = Rate.query
        if filter_id:
            query = query.filter(Rate.id == filter_id)
        if filter_supplier:
            query = query.filter(Rate.supplier.ilike(f"%{filter_supplier}%"))
        if filter_name:
            query = query.filter(Rate.name.ilike(f"%{filter_name}%"))
        if filter_tariff_type:
            query = query.filter(Rate.tariff_type.ilike(f"%{filter_tariff_type}%"))
        if filter_valid_from_s:
            try:
                vf = datetime.strptime(filter_valid_from_s, '%Y-%m-%d').date()
                query = query.filter(Rate.valid_from >= vf)
            except ValueError:
                app.logger.warning(f"Fecha 'valid_from' inválida: {filter_valid_from_s}")
        if filter_valid_until_s:
            try:
                vu = datetime.strptime(filter_valid_until_s, '%Y-%m-%d').date()
                query = query.filter(Rate.valid_until <= vu)
            except ValueError:
                app.logger.warning(f"Fecha 'valid_until' inválida: {filter_valid_until_s}")

        rates = query.order_by(Rate.name.asc()).all()

        header = [
            'id', 'supplier', 'name', 'tariff_type', 'valid_from', 'valid_until', 'notes',
            'energy_P1', 'energy_P2', 'energy_P3', 'energy_P4', 'energy_P5', 'energy_P6',
            'power_P1', 'power_P2', 'power_P3', 'power_P4', 'power_P5', 'power_P6'
        ]

        si = io.StringIO()
        writer = csv.writer(si)
        writer.writerow(header)

        def price_or_empty(prices, key):
            try:
                return ('' if prices is None else prices.get(key) if isinstance(prices, dict) else '')
            except Exception:
                return ''

        for r in rates:
            energy = r.energy_prices or {}
            power = r.power_prices or {}
            writer.writerow([
                r.id,
                r.supplier or '',
                r.name or '',
                r.tariff_type or '',
                r.valid_from.isoformat() if r.valid_from else '',
                r.valid_until.isoformat() if r.valid_until else '',
                (r.notes or '').replace('\n', ' ').strip(),
                price_or_empty(energy, 'P1'), price_or_empty(energy, 'P2'), price_or_empty(energy, 'P3'),
                price_or_empty(energy, 'P4'), price_or_empty(energy, 'P5'), price_or_empty(energy, 'P6'),
                price_or_empty(power, 'P1'), price_or_empty(power, 'P2'), price_or_empty(power, 'P3'),
                price_or_empty(power, 'P4'), price_or_empty(power, 'P5'), price_or_empty(power, 'P6'),
            ])

        data = si.getvalue().encode('utf-8-sig')
        filename = f"tarifas_export_{datetime.utcnow().strftime('%Y%m%d_%H%M%S')}.csv"
        return send_file(io.BytesIO(data), mimetype='text/csv', as_attachment=True, download_name=filename)
    except Exception as e:
        app.logger.error(f"Error al exportar CSV: {e}")
        flash(f'Error al exportar tarifas a CSV: {e}', 'danger')
        return redirect(url_for('admin.list_rates'))


@admin_bp.route('/tarifas/csv/import', methods=['POST'])
@login_required
@admin_or_analyst_required
def import_rates_csv():
    """Importa tarifas desde un archivo CSV. Actualiza si viene 'id'; crea si no."""
    try:
        file = request.files.get('file')
        if not file or file.filename == '':
            flash('No se seleccionó ningún archivo CSV.', 'warning')
            return redirect(url_for('admin.list_rates'))

        # Envolver en TextIO para csv.reader con UTF-8 y tolerancia a BOM
        stream = io.TextIOWrapper(file.stream, encoding='utf-8-sig', newline='')
        reader = csv.DictReader(stream)

        required_basic = ['name', 'tariff_type', 'valid_from']
        processed = 0
        created = 0
        updated = 0
        errors = []

        def parse_float(val):
            if val is None:
                return None
            s = str(val).strip()
            if not s:
                return None
            s = s.replace(',', '.')
            try:
                return float(s)
            except ValueError:
                return None

        for idx, row in enumerate(reader, start=2):  # start=2 por el header en línea 1
            try:
                processed += 1
                # Validación básica
                for field in required_basic:
                    if not (row.get(field) and str(row.get(field)).strip()):
                        raise ValueError(f"Falta el campo obligatorio '{field}'")

                # Preparar datos
                rid = row.get('id')
                rid = int(rid) if rid and str(rid).strip().isdigit() else None
                name = (row.get('name') or '').strip()
                supplier = (row.get('supplier') or '').strip() or None
                tariff_type = (row.get('tariff_type') or '').strip()
                notes = (row.get('notes') or '').strip() or None

                # Fechas
                try:
                    valid_from = datetime.strptime((row.get('valid_from') or '').strip(), '%Y-%m-%d').date()
                except ValueError:
                    raise ValueError("'valid_from' debe tener formato YYYY-MM-DD")
                valid_until = None
                vu_raw = (row.get('valid_until') or '').strip()
                if vu_raw:
                    try:
                        valid_until = datetime.strptime(vu_raw, '%Y-%m-%d').date()
                    except ValueError:
                        raise ValueError("'valid_until' debe tener formato YYYY-MM-DD")

                # Precios energía y potencia P1..P6
                energy = {}
                power = {}
                for p in ['P1','P2','P3','P4','P5','P6']:
                    ev = parse_float(row.get(f'energy_{p}'))
                    if ev is not None:
                        energy[p] = ev
                    pv = parse_float(row.get(f'power_{p}'))
                    if pv is not None:
                        power[p] = pv

                # Crear o actualizar
                if rid:
                    rate = Rate.query.get(rid)
                    if not rate:
                        raise ValueError(f"No existe una tarifa con id={rid}")
                    rate.name = name
                    rate.supplier = supplier
                    rate.tariff_type = tariff_type
                    rate.valid_from = valid_from
                    rate.valid_until = valid_until
                    rate.notes = notes
                    rate.energy_prices = energy
                    rate.power_prices = power
                    rate.updated_at = datetime.utcnow()
                    db.session.add(rate)
                    db.session.commit()
                    updated += 1
                else:
                    new_rate = Rate(
                        name=name,
                        supplier=supplier,
                        tariff_type=tariff_type,
                        valid_from=valid_from,
                        valid_until=valid_until,
                        notes=notes,
                        energy_prices=energy or {},
                        power_prices=power or {}
                    )
                    db.session.add(new_rate)
                    db.session.commit()
                    created += 1

            except Exception as e:
                db.session.rollback()
                errors.append(f"Línea {idx}: {str(e)}")

        # Resumen al usuario
        if errors:
            flash(f"Importación finalizada con errores. Procesadas: {processed}, Creadas: {created}, Actualizadas: {updated}. Errores: {len(errors)}", 'warning')
            # Guardar detalle en logs para consulta
            for msg in errors[:10]:  # Mostrar algunas en logs
                app.logger.warning(f"Import CSV - {msg}")
        else:
            flash(f"Importación realizada con éxito. Procesadas: {processed}, Creadas: {created}, Actualizadas: {updated}.", 'success')

        return redirect(url_for('admin.list_rates'))
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error general en importación CSV: {e}")
        flash(f'Error en importación CSV: {e}', 'danger')
        return redirect(url_for('admin.list_rates'))

# =====================
# Seguridad / Rate Limiter
# =====================
@admin_bp.route('/security/rate-limiter', methods=['GET', 'POST'])
@login_required
@admin_required
def rate_limiter_settings():
    """Gestión de límites de tasa, whitelist y modo estricto."""
    form = RateLimitSettingsForm()

    # Manejo de activación/desactivación del modo estricto SIN requerir los campos obligatorios
    # del formulario principal de límites. CSRF sigue protegido globalmente por Flask-WTF.
    if request.method == 'POST':
        try:
            if form.activate_strict.data:
                minutes = form.strict_minutes.data or 60
                enable_strict_mode(minutes)
                flash(f'Modo estricto activado por {minutes} minutos.', 'success')
                return redirect(url_for('admin.rate_limiter_settings'))
            elif form.deactivate_strict.data:
                disable_strict_mode()
                flash('Modo estricto desactivado.', 'info')
                return redirect(url_for('admin.rate_limiter_settings'))
        except Exception as e:
            db.session.rollback()
            app.logger.error(f"Error al actualizar configuración de rate limiting (estricto): {e}")
            flash(f'Error al actualizar configuración: {e}', 'danger')

    # Guardar configuración de límites (requiere validar campos obligatorios)
    if form.validate_on_submit() and form.submit.data:
        try:
            Setting.set_value('rate.auth.login', form.login_limit.data.strip())
            Setting.set_value('rate.auth.register', form.register_limit.data.strip())
            Setting.set_value('rate.auth.reset_request', form.reset_request_limit.data.strip())
            Setting.set_value('rate.auth.reset', form.reset_limit.data.strip())
            # Whitelist: una por línea
            whitelist = []
            if form.whitelist.data:
                whitelist = [line.strip() for line in form.whitelist.data.splitlines() if line.strip()]
            Setting.set_json('ratelimit.whitelist_ips', whitelist)
            db.session.commit()
            flash('Configuración de rate limiting actualizada correctamente.', 'success')
            return redirect(url_for('admin.rate_limiter_settings'))
        except Exception as e:
            db.session.rollback()
            app.logger.error(f"Error al actualizar configuración de rate limiting: {e}")
            flash(f'Error al actualizar configuración: {e}', 'danger')

    # Precargar valores actuales
    if request.method == 'GET':
        form.login_limit.data = Setting.get_value('rate.auth.login', '10/minute; 50/hour')
        form.register_limit.data = Setting.get_value('rate.auth.register', '5/minute; 20/hour')
        form.reset_request_limit.data = Setting.get_value('rate.auth.reset_request', '5/minute; 20/hour')
        form.reset_limit.data = Setting.get_value('rate.auth.reset', '10/minute; 50/hour')
        current_whitelist = Setting.get_json('ratelimit.whitelist_ips', []) or []
        form.whitelist.data = "\n".join(current_whitelist)

    status = strict_status()
    return render_template('admin/security/rate_limiter.html', title='Rate Limiting', form=form, status=status)


# Rutas para gestionar artículos del blog
@admin_bp.route('/articulos')
@login_required
@admin_required
def manage_articles():
    """Lista todos los artículos del blog para administración."""
    try:
        # Obtener todos los artículos de la base de datos
        # En la administración queremos ver TODOS los artículos, incluso los no publicados
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 10, type=int)

        query = Article.query.order_by(Article.id.desc())
        articles_pagination = query.paginate(page=page, per_page=per_page, error_out=False)
        articles = articles_pagination.items
        
        # Usar el método to_dict() del modelo para garantizar consistencia
        articles_data = [article.to_dict() for article in articles]
    except Exception as e:
        flash(f"Error al cargar artículos: {str(e)}", 'danger')
        articles_data = []  # Lista vacía si hay error
    # Formulario vacío para token CSRF en acciones POST (eliminación)
    csrf_form = EmptyForm()

    return render_template(
        'admin/blog/list.html',
        title="Gestión de Artículos",
        articles=articles_data,
        pagination=articles_pagination,
        current_page=page,
        per_page=per_page,
        form=csrf_form
    )

# Rutas para gestionar usuarios
@admin_bp.route('/users')
@login_required
@admin_required # Solo Super Administradores pueden gestionar usuarios
def list_users():
    """Lista todos los usuarios del sistema."""
    try:
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 15, type=int)

        # Filtros
        filter_id = request.args.get('id', type=int)
        filter_username = request.args.get('username')
        filter_email = request.args.get('email')
        filter_role = request.args.get('role')
        filter_created_from_s = request.args.get('created_from')
        filter_created_until_s = request.args.get('created_until')
        filter_is_active = request.args.get('is_active')

        # Construir consulta
        query = User.query
        if filter_id:
            query = query.filter(User.id == filter_id)
        if filter_username:
            query = query.filter(User.username.ilike(f"%{filter_username}%"))
        if filter_email:
            query = query.filter(User.email.ilike(f"%{filter_email}%"))
        if filter_role:
            # Usar any() para evitar duplicados por joins y simplificar la condición
            query = query.filter(User.roles.any(Role.name.ilike(f"%{filter_role}%")))
        if filter_is_active is not None and filter_is_active != '':
            active_bool = str(filter_is_active).lower() in ('1', 'true', 't', 'yes')
            query = query.filter(User.is_active.is_(active_bool))
        if filter_created_from_s:
            try:
                start = datetime.strptime(filter_created_from_s, '%Y-%m-%d')
                query = query.filter(User.created_at >= start)
            except ValueError:
                app.logger.warning(f"Fecha 'created_from' inválida: {filter_created_from_s}")
        if filter_created_until_s:
            try:
                end = datetime.strptime(filter_created_until_s, '%Y-%m-%d').replace(hour=23, minute=59, second=59, microsecond=999999)
                query = query.filter(User.created_at <= end)
            except ValueError:
                app.logger.warning(f"Fecha 'created_until' inválida: {filter_created_until_s}")

        # Ordenar por nombre de usuario para consistencia
        query = query.order_by(User.username)

        # Paginación
        users_pagination = query.paginate(page=page, per_page=per_page, error_out=False)
        users = users_pagination.items
        
        # Crear un formulario CSRF para cada usuario y uno global para AJAX
        csrf_forms = {}
        for user in users:
            csrf_forms[user.id] = EmptyForm()
        global_csrf_form = EmptyForm()
    except Exception as e:
        app.logger.error(f"Error al cargar usuarios: {str(e)}")
        flash(f"Error al cargar usuarios: {str(e)}", 'danger')
        users = []
        csrf_forms = {}
        users_pagination = None
        global_csrf_form = EmptyForm()
        # Valores por defecto de filtros para evitar NameError al renderizar tras excepción
        per_page = 15
        filter_id = None
        filter_username = None
        filter_email = None
        filter_role = None
        filter_created_from_s = None
        filter_created_until_s = None
        filter_is_active = None
    
    return render_template('admin/users/list.html', 
                           title="Gestión de Usuarios", 
                           users=users, 
                           csrf_forms=csrf_forms,
                           pagination=users_pagination,
                           per_page=per_page,
                           filter_id=filter_id or '',
                           filter_username=filter_username,
                           filter_email=filter_email,
                           filter_role=filter_role,
                           filter_created_from=filter_created_from_s,
                           filter_created_until=filter_created_until_s,
                           filter_is_active=filter_is_active,
                           global_csrf_form=global_csrf_form)


@admin_bp.route('/users/add', methods=['GET', 'POST'])
@login_required
@admin_required
def add_user():
    """Crea un nuevo usuario desde el panel de administración."""
    form = AddUserForm()
    # Populate roles choices dynamically
    form.roles.choices = [(r.id, r.name) for r in Role.query.order_by('name')]
    
    if form.validate_on_submit():
        # No necesitamos verificar duplicados aquí ya que WTForms lo hace por nosotros
        try:
            # Crear usuario con los datos básicos
            user = User(username=form.username.data, email=form.email.data)
            # Usar el método set_password para establecer la contraseña correctamente
            user.set_password(form.password.data)
            
            # Asignar roles
            selected_roles = Role.query.filter(Role.id.in_(form.roles.data)).all()
            user.roles = selected_roles
            
            db.session.add(user)
            db.session.commit()
            flash('El usuario ha sido creado con éxito.', 'success')
            return redirect(url_for('admin.list_users'))
        except Exception as e:
            db.session.rollback()
            app.logger.error(f"Error al crear usuario: {str(e)}")
            flash(f'Hubo un error al crear el usuario: {str(e)}', 'danger')
            # En caso de error, también redirigimos al listado de usuarios
            return redirect(url_for('admin.list_users'))

    return render_template('admin/users/form.html', title='Crear Nuevo Usuario', form=form, legend='Crear Nuevo Usuario')

@admin_bp.route('/users/edit/<int:user_id>', methods=['GET', 'POST'])
@login_required
@admin_required
def edit_user(user_id):
    """Edita un usuario existente."""
    user = User.query.get_or_404(user_id)
    form = EditUserForm(obj=user)
    
    # Populate roles choices dynamically
    form.roles.choices = [(r.id, r.name) for r in Role.query.order_by('name')]
    
    if form.validate_on_submit():
        try:
            user.username = form.username.data
            user.email = form.email.data
            
            # Si se proporcionó una nueva contraseña, actualizarla usando el método set_password
            if form.password.data and form.password.data.strip():
                user.set_password(form.password.data)
            
            # Actualizar roles
            selected_roles = Role.query.filter(Role.id.in_(form.roles.data)).all()
            user.roles = selected_roles
            
            # Actualizar estado
            user.is_active = form.is_active.data
            
            db.session.commit()
            flash('El usuario ha sido actualizado con éxito.', 'success')
            return redirect(url_for('admin.list_users'))
        except Exception as e:
            db.session.rollback()
            app.logger.error(f"Error al actualizar usuario: {str(e)}")
            flash(f'Hubo un error al actualizar el usuario: {str(e)}', 'danger')
    
    # Prepopulate roles field
    if request.method == 'GET':
        form.roles.data = [role.id for role in user.roles]
        form.is_active.data = user.is_active
    
    return render_template('admin/users/form.html', title='Editar Usuario', 
                          form=form, legend=f'Editar Usuario: {user.username}', user=user)

@admin_bp.route('/users/delete/<int:user_id>', methods=['POST'])
@login_required
@admin_required
def delete_user(user_id):
    """Elimina un usuario."""
    user = User.query.get_or_404(user_id)
    
    # No permitir que un usuario se elimine a sí mismo
    if user.id == current_user.id:
        flash('No puede eliminar su propia cuenta de usuario.', 'danger')
        return redirect(url_for('admin.list_users'))
    
    try:
        db.session.delete(user)
        db.session.commit()
        flash('El usuario ha sido eliminado con éxito.', 'success')
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error al eliminar usuario: {str(e)}")
        flash(f'Hubo un error al eliminar el usuario: {str(e)}', 'danger')
    
    return redirect(url_for('admin.list_users'))

@admin_bp.route('/users/toggle/<int:user_id>', methods=['POST'])
@login_required
@admin_required
def toggle_user_status(user_id):
    """Activa o desactiva un usuario."""
    user = User.query.get_or_404(user_id)
    
    # No permitir desactivar al propio usuario
    if user.id == current_user.id:
        return jsonify({'success': False, 'message': 'No puede desactivar su propia cuenta'}), 400
    
    try:
        # Cambiar el estado actual al opuesto
        user.is_active = not user.is_active
        status_text = 'activado' if user.is_active else 'desactivado'
        
        db.session.commit()
        return jsonify({
            'success': True, 
            'is_active': user.is_active, 
            'message': f'Usuario {status_text} correctamente'
        })
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error al cambiar estado de usuario: {str(e)}")
        return jsonify({'success': False, 'message': f'Error: {str(e)}'}), 500

# =====================
# Endpoints para estudios de ahorro y funcionalidades de leads
# =====================

@admin_bp.route('/leads/save-savings', methods=['POST'])
@login_required
@admin_or_comercial_required
def save_lead_savings():
    """Guarda comentarios, plan y comercializadora recomendada para un lead"""
    try:
        data = request.get_json()
        lead_id = data.get('lead_id')
        comments = data.get('comments', '')
        selected_plan = data.get('selected_plan', '')
        recommended_supplier = data.get('recommended_supplier', '')
        
        lead = InvoiceLead.query.get_or_404(lead_id)
        
        # Guardar datos del comercial
        lead.commercial_comments = comments
        lead.selected_plan = selected_plan
        lead.recommended_supplier = recommended_supplier
        
        # Agregar al histórico si hay datos de ahorro
        if lead.details:
            study_data = {
                'comments': comments,
                'selected_plan': selected_plan,
                'recommended_supplier': recommended_supplier,
                'savings_data': lead.details,
                'created_by': current_user.name or current_user.username
            }
            lead.add_savings_study(study_data)
        
        db.session.commit()
        
        return jsonify({
            'success': True, 
            'message': 'Datos guardados correctamente'
        })
        
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error al guardar datos de ahorro: {e}")
        return jsonify({'success': False, 'message': str(e)}), 500

@admin_bp.route('/leads/history/<int:lead_id>', methods=['GET'])
@login_required
@admin_or_comercial_required
def get_lead_history(lead_id):
    """Obtiene el histórico de estudios de ahorro de un lead"""
    try:
        lead = InvoiceLead.query.get_or_404(lead_id)
        history = lead.savings_history_data
        
        return jsonify({
            'success': True,
            'history': history,
            'current_data': {
                'comments': lead.commercial_comments,
                'selected_plan': lead.selected_plan,
                'recommended_supplier': lead.recommended_supplier
            }
        })
        
    except Exception as e:
        app.logger.error(f"Error al obtener histórico: {e}")
        return jsonify({'success': False, 'message': str(e)}), 500
@admin_bp.route('/leads/recalculate/<int:lead_id>', methods=['POST'])
@login_required
@admin_or_comercial_required
def recalculate_lead_savings(lead_id):
    """Recalcula el ahorro de un lead usando los datos existentes y redirigiendo a la plantilla de extracción"""
    try:
        from datetime import datetime
        from flask import session
        
        lead = InvoiceLead.query.get_or_404(lead_id)
        
        # Verificar que el lead tenga datos existentes
        if not lead.details:
            return jsonify({
                'success': False,
                'message': 'No hay datos existentes para este lead'
            }), 400
        
        app.logger.info(f"Iniciando recálculo para lead {lead_id} usando datos existentes")
        
        # Usar los datos existentes del lead
        existing_data = lead.details
        
        # Verificar que los datos existentes sean válidos
        energy_consumption = existing_data.get('energy_consumption')
        power_contracted = existing_data.get('power_periods') or existing_data.get('power_contracted')
        
        if not energy_consumption or not power_contracted:
            return jsonify({
                'success': False,
                'message': 'Los datos existentes no contienen información de consumo o potencia'
            }), 400
        
        # Calcular ahorro con los datos existentes
        from app.services.tariff_comparison import TariffComparison
        
        tariff_comparison = TariffComparison()
        tariff_type = existing_data.get('detected_tariff', lead.tariff_type or '2.0TD')
        matching_rate = tariff_comparison.find_matching_rate(tariff_type)
        
        if not matching_rate:
            return jsonify({
                'success': False,
                'message': f'No se encontró tarifa de referencia para {tariff_type}'
            }), 400
        
        # Preparar datos del cliente para el cálculo
        client_data = {
            'consumption': energy_consumption,
            'power': power_contracted,
            'current_energy_prices': existing_data.get('energy_prices', {}),
            'current_power_prices': existing_data.get('power_prices', {}),
            'billing_period_days': existing_data.get('billing_days', 30),
            'reactive_energy': existing_data.get('reactive_energy', {}),
            'reactive_energy_prices': existing_data.get('reactive_energy_prices', {}),
            'reactive_energy_total_cost': existing_data.get('reactive_energy_total_cost'),
            'discounts': existing_data.get('discounts', 0),
            'current_supplier': existing_data.get('provider_name', 'Desconocida'),
            'cups': lead.cups or existing_data.get('cups', ''),
            'client_name': lead.name
        }
        
        # Calcular ahorro con precios actualizados
        savings_data = tariff_comparison.calculate_savings(client_data, matching_rate)
        
        if not savings_data:
            return jsonify({
                'success': False,
                'message': 'Error al calcular el ahorro con precios actualizados'
            }), 400
        
        # Preparar datos para la sesión
        session_data = {
            'client_name': existing_data.get('client_name', lead.name),
            'cups': existing_data.get('cups', lead.cups),
            'supply_address': existing_data.get('supply_address', ''),
            'provider_name': existing_data.get('provider_name', 'Desconocida'),
            'detected_tariff': tariff_type,
            'billing_start_date': existing_data.get('billing_start', ''),
            'billing_end_date': existing_data.get('billing_end', ''),
            'billing_days': existing_data.get('billing_days', 30),
            'energy_consumption': energy_consumption,
            'power_periods': power_contracted,
            'energy_prices': existing_data.get('energy_prices', {}),
            'power_prices': existing_data.get('power_prices', {}),
            'reactive_energy': existing_data.get('reactive_energy', {}),
            'reactive_energy_prices': existing_data.get('reactive_energy_prices', {}),
            'reactive_energy_total_cost': existing_data.get('reactive_energy_total_cost'),
            'discounts': existing_data.get('discounts', 0),
            'total_amount': existing_data.get('total_amount', 0),
            'recalculation_mode': True,
            'original_lead_id': lead_id
        }
        
        # Guardar en sesión incluyendo los resultados de ahorro
        for key, value in session_data.items():
            session[key] = value
        
        # Datos necesarios para la plantilla de resultados
        session['savings_results'] = savings_data
        session['confirmed_invoice_data'] = session_data
        session['lead_id'] = lead_id
        
        # Registrar el recálculo completado en el histórico
        study_entry = {
            'timestamp': datetime.now().isoformat(),
            'user_id': current_user.id,
            'user_name': current_user.name if hasattr(current_user, 'name') and current_user.name else current_user.username,
            'action': 'recalculo_con_precios_actualizados',
            'tariff_type': tariff_type,
            'estimated_savings': savings_data.get('yearly_savings', 0),
            'best_plan': savings_data.get('best_plan', {}).get('name', 'No disponible'),
            'current_supplier': client_data['current_supplier'],
            'consumption_data': client_data['consumption'],
            'power_data': client_data['power'],
            'data_source': 'datos_existentes'
        }
        
        lead.add_savings_study(study_entry)
        db.session.commit()
        
        app.logger.info(f"Recálculo completado para lead {lead_id}. Ahorro estimado: {savings_data.get('yearly_savings', 0)}€")
        
        # Redirigir a la plantilla de resultados con los datos calculados
        return jsonify({
            'success': True,
            'message': f'Recálculo completado. Nuevo ahorro estimado: {savings_data.get("yearly_savings", 0):.2f}€ anuales',
            'redirect_url': url_for('invoice.saving_result')
        })
        
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error al preparar recálculo para lead {lead_id}: {e}")
        return jsonify({'success': False, 'message': f'Error interno: {str(e)}'}), 500

@admin_bp.route('/leads/edit/<int:lead_id>', methods=['GET', 'POST'])
@login_required
@admin_or_comercial_required
def edit_lead(lead_id):
    """Edita un lead existente"""
    try:
        lead = InvoiceLead.query.get_or_404(lead_id)
        
        if request.method == 'POST':
            data = request.get_json() if request.is_json else request.form
            app.logger.info(f"Editando lead {lead_id} con datos: {data}")
            
            # Actualizar campos básicos
            lead.name = data.get('name', lead.name)
            lead.email = data.get('email', lead.email)
            lead.phone = data.get('phone', lead.phone)
            lead.cups = data.get('cups', lead.cups)
            lead.status = data.get('status', lead.status)
            lead.message = data.get('message', lead.message)
            
            # Actualizar campos de comercial si están presentes
            if 'commercial_comments' in data:
                lead.commercial_comments = data.get('commercial_comments')
                app.logger.info(f"Actualizando commercial_comments: {lead.commercial_comments}")
            if 'selected_plan' in data:
                lead.selected_plan = data.get('selected_plan')
                app.logger.info(f"Actualizando selected_plan: {lead.selected_plan}")
            if 'recommended_supplier' in data:
                lead.recommended_supplier = data.get('recommended_supplier')
                app.logger.info(f"Actualizando recommended_supplier: {lead.recommended_supplier}")
            
            # Si se actualizaron campos comerciales, agregar al histórico
            if any(field in data for field in ['commercial_comments', 'selected_plan', 'recommended_supplier']):
                from datetime import datetime
                study_entry = {
                    'timestamp': datetime.now().isoformat(),
                    'user_id': current_user.id,
                    'user_name': current_user.name if hasattr(current_user, 'name') else current_user.email,
                    'action': 'edicion_comentarios',
                    'commercial_comments': lead.commercial_comments,
                    'selected_plan': lead.selected_plan,
                    'recommended_supplier': lead.recommended_supplier
                }
                lead.add_savings_study(study_entry)
                app.logger.info(f"Agregado al histórico: {study_entry}")
            
            db.session.commit()
            app.logger.info(f"Lead {lead_id} actualizado exitosamente")
            
            if request.is_json:
                return jsonify({'success': True, 'message': 'Lead actualizado correctamente'})
            else:
                flash('Lead actualizado correctamente', 'success')
                return redirect(url_for('admin.list_leads'))
        
        # GET request - mostrar formulario
        return render_template('admin/leads/edit.html', title="Editar Lead", lead=lead)
        
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error al editar lead: {e}")
        if request.is_json:
            return jsonify({'success': False, 'message': str(e)}), 500
        else:
            flash(f'Error al editar lead: {e}', 'error')
            return redirect(url_for('admin.list_leads'))

@admin_bp.route('/leads/comments/<int:lead_id>', methods=['GET'])
@login_required
@admin_or_comercial_required
def get_lead_comments(lead_id):
    """Obtiene los comentarios CRM de un lead"""
    try:
        lead = InvoiceLead.query.get_or_404(lead_id)
        
        # Filtrar solo comentarios del histórico
        comments = []
        if lead.savings_history_data:
            for entry in lead.savings_history_data:
                if entry.get('action') == 'comentario_añadido':
                    comments.append({
                        'timestamp': entry.get('timestamp'),
                        'user_name': entry.get('user_name', 'Usuario'),
                        'comment': entry.get('comment', '')
                    })
        
        # Ordenar por timestamp (más recientes primero)
        comments.sort(key=lambda x: x['timestamp'], reverse=False)
        
        return jsonify({
            'success': True,
            'comments': comments
        })
        
    except Exception as e:
        app.logger.error(f"Error al obtener comentarios: {e}")
        return jsonify({'success': False, 'message': str(e)}), 500

@admin_bp.route('/leads/comments/<int:lead_id>', methods=['POST'])
@login_required
@admin_or_comercial_required
def add_lead_comment(lead_id):
    """Añade un comentario CRM a un lead"""
    try:
        lead = InvoiceLead.query.get_or_404(lead_id)
        data = request.get_json()
        
        comment_text = data.get('comment', '').strip()
        if not comment_text:
            return jsonify({'success': False, 'message': 'El comentario no puede estar vacío'}), 400
        
        from datetime import datetime
        comment_entry = {
            'timestamp': datetime.now().isoformat(),
            'user_id': current_user.id,
            'user_name': current_user.name if hasattr(current_user, 'name') and current_user.name else current_user.username,
            'action': 'comentario_añadido',
            'comment': comment_text
        }
        
        lead.add_savings_study(comment_entry)
        db.session.commit()
        
        app.logger.info(f"Comentario añadido al lead {lead_id} por {current_user.username}")
        
        return jsonify({
            'success': True,
            'message': 'Comentario añadido correctamente'
        })
        
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error al añadir comentario: {e}")
        return jsonify({'success': False, 'message': str(e)}), 500
