"""
Router para manejo de alertas de precio.
"""
from flask import Blueprint, request, jsonify
from flask_jwt_extended import jwt_required
from app.db import get_db_connection
from app.auth import get_current_user
from app.utils.validators import validate_id_format, validate_number, validate_pagination_params
from app.utils.serializers import price_alert_to_dict
from app.utils.logger import get_logger
from datetime import datetime, timezone
import uuid

price_alerts_bp = Blueprint('price_alerts', __name__)
logger = get_logger(__name__)


@price_alerts_bp.route('', methods=['GET'])
@jwt_required()
def get_price_alerts():
    """Get all price alerts for the current user."""
    user = get_current_user()
    if not user:
        return jsonify({'error': 'Authentication required'}), 401
    
    skip = request.args.get('skip', 0, type=int)
    limit = request.args.get('limit', 50, type=int)
    active_only = request.args.get('active_only', 'false').lower() == 'true'
    
    # Validar paginación
    valid, skip_val, limit_val, error_msg = validate_pagination_params(skip, limit, max_limit=100)
    if not valid:
        return jsonify({'error': error_msg}), 400
    
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection error'}), 500
    
    try:
        cursor = conn.cursor()
        user_id = user.get('id')
        
        # Construir query con filtro opcional por activo
        if active_only:
            cursor.execute("""
                SELECT id, usuario_id, llanta_id, negocio_id, precio_maximo, activo, notificado, creado_en, actualizado_en
                FROM alertas_precio
                WHERE usuario_id = %s AND activo = TRUE
                ORDER BY creado_en DESC
                LIMIT %s OFFSET %s
            """, (user_id, limit_val, skip_val))
            
            cursor.execute("""
                SELECT COUNT(*) as total
                FROM alertas_precio
                WHERE usuario_id = %s AND activo = TRUE
            """, (user_id,))
        else:
            cursor.execute("""
                SELECT id, usuario_id, llanta_id, negocio_id, precio_maximo, activo, notificado, creado_en, actualizado_en
                FROM alertas_precio
                WHERE usuario_id = %s
                ORDER BY creado_en DESC
                LIMIT %s OFFSET %s
            """, (user_id, limit_val, skip_val))
            
            cursor.execute("""
                SELECT COUNT(*) as total
                FROM alertas_precio
                WHERE usuario_id = %s
            """, (user_id,))
        
        alerts_data = cursor.fetchall()
        total_result = cursor.fetchone()
        total_count = total_result['total'] if total_result else 0
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'alerts': [price_alert_to_dict(alert) for alert in alerts_data],
            'pagination': {
                'skip': skip_val,
                'limit': limit_val,
                'total': total_count,
                'has_more': (skip_val + limit_val) < total_count
            }
        }), 200
    
    except Exception as e:
        if 'cursor' in locals():
            cursor.close()
        if conn:
            conn.close()
        logger.error(f"Error in get_price_alerts: {str(e)}", exc_info=True, extra={
            'endpoint': 'get_price_alerts',
            'user_id': user.get('id') if user else None
        })
        return jsonify({'error': 'Error retrieving price alerts'}), 500


@price_alerts_bp.route('', methods=['POST'])
@jwt_required()
def create_price_alert():
    """Create a new price alert."""
    user = get_current_user()
    if not user:
        return jsonify({'error': 'Authentication required'}), 401
    
    data = request.get_json()
    if not data:
        return jsonify({'error': 'Request body is required'}), 400
    
    tire_id = data.get('tire_id')
    business_id = data.get('business_id')
    max_price = data.get('max_price')
    
    # Validaciones
    if not tire_id and not business_id:
        return jsonify({'error': 'tire_id or business_id is required'}), 400
    
    if max_price is not None:
        if not validate_number(max_price, allow_decimal=True):
            return jsonify({'error': 'max_price must be a positive number'}), 400
        try:
            max_price = float(max_price)
            if max_price <= 0:
                return jsonify({'error': 'max_price must be greater than 0'}), 400
        except (ValueError, TypeError):
            return jsonify({'error': 'Invalid max_price format'}), 400
    
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection error'}), 500
    
    try:
        cursor = conn.cursor()
        user_id = user.get('id')
        
        # Verificar que la llanta existe (si se proporciona)
        if tire_id:
            if not validate_id_format(tire_id):
                cursor.close()
                conn.close()
                return jsonify({'error': 'Invalid tire_id format'}), 400
            
            cursor.execute("SELECT id FROM llantas WHERE id = %s", (tire_id,))
            if not cursor.fetchone():
                cursor.close()
                conn.close()
                return jsonify({'error': 'Tire not found'}), 404
        
        # Verificar que el negocio existe (si se proporciona)
        if business_id:
            if not validate_id_format(business_id):
                cursor.close()
                conn.close()
                return jsonify({'error': 'Invalid business_id format'}), 400
            
            cursor.execute("SELECT id FROM negocios_llantas WHERE id = %s", (business_id,))
            if not cursor.fetchone():
                cursor.close()
                conn.close()
                return jsonify({'error': 'Business not found'}), 404
        
        # Verificar si ya existe una alerta similar activa
        if tire_id and business_id:
            cursor.execute("""
                SELECT id FROM alertas_precio
                WHERE usuario_id = %s AND llanta_id = %s AND negocio_id = %s AND activo = TRUE
            """, (user_id, tire_id, business_id))
        elif tire_id:
            cursor.execute("""
                SELECT id FROM alertas_precio
                WHERE usuario_id = %s AND llanta_id = %s AND negocio_id IS NULL AND activo = TRUE
            """, (user_id, tire_id))
        else:
            cursor.execute("""
                SELECT id FROM alertas_precio
                WHERE usuario_id = %s AND llanta_id IS NULL AND negocio_id = %s AND activo = TRUE
            """, (user_id, business_id))
        
        if cursor.fetchone():
            cursor.close()
            conn.close()
            return jsonify({'error': 'Active alert already exists for this tire/business combination'}), 400
        
        # Crear alerta
        alert_id = f"alert-{user_id}-{uuid.uuid4().hex[:12]}"
        cursor.execute("""
            INSERT INTO alertas_precio (id, usuario_id, llanta_id, negocio_id, precio_maximo, activo, notificado, creado_en)
            VALUES (%s, %s, %s, %s, %s, TRUE, FALSE, %s)
        """, (alert_id, user_id, tire_id, business_id, max_price, datetime.now(timezone.utc)))
        
        conn.commit()
        
        # Log change for audit
        from app.governance.audit import log_change
        log_change(
            table='alertas_precio',
            record_id=alert_id,
            action='INSERT',
            user_id=user_id,
            user_email=user.get('correo'),
            new_data={
                'usuario_id': user_id,
                'llanta_id': tire_id,
                'negocio_id': business_id,
                'precio_maximo': max_price
            }
        )
        
        # Obtener alerta creada
        cursor.execute("""
            SELECT id, usuario_id, llanta_id, negocio_id, precio_maximo, activo, notificado, creado_en, actualizado_en
            FROM alertas_precio WHERE id = %s
        """, (alert_id,))
        alert_data = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        return jsonify(price_alert_to_dict(alert_data)), 201
    
    except Exception as e:
        if conn:
            conn.rollback()
            if 'cursor' in locals():
                cursor.close()
            conn.close()
        logger.error(f"Error in create_price_alert: {str(e)}", exc_info=True, extra={
            'endpoint': 'create_price_alert',
            'user_id': user.get('id') if user else None
        })
        return jsonify({'error': 'Error creating price alert'}), 500


@price_alerts_bp.route('/<alert_id>', methods=['PUT'])
@jwt_required()
def update_price_alert(alert_id):
    """Update a price alert (activate/deactivate or change max_price)."""
    user = get_current_user()
    if not user:
        return jsonify({'error': 'Authentication required'}), 401
    
    data = request.get_json()
    if not data:
        return jsonify({'error': 'Request body is required'}), 400
    
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection error'}), 500
    
    try:
        cursor = conn.cursor()
        user_id = user.get('id')
        
        # Verificar que la alerta existe y pertenece al usuario
        cursor.execute("""
            SELECT id, precio_maximo, activo FROM alertas_precio
            WHERE id = %s AND usuario_id = %s
        """, (alert_id, user_id))
        alert_data = cursor.fetchone()
        
        if not alert_data:
            cursor.close()
            conn.close()
            return jsonify({'error': 'Alert not found'}), 404
        
        updates = []
        params = []
        
        # Actualizar precio máximo si se proporciona
        if 'max_price' in data:
            max_price = data['max_price']
            if max_price is not None:
                if not validate_number(max_price, allow_decimal=True):
                    cursor.close()
                    conn.close()
                    return jsonify({'error': 'max_price must be a positive number'}), 400
                try:
                    max_price = float(max_price)
                    if max_price <= 0:
                        cursor.close()
                        conn.close()
                        return jsonify({'error': 'max_price must be greater than 0'}), 400
                except (ValueError, TypeError):
                    cursor.close()
                    conn.close()
                    return jsonify({'error': 'Invalid max_price format'}), 400
            updates.append("precio_maximo = %s")
            params.append(max_price)
        
        # Actualizar estado activo si se proporciona
        if 'active' in data:
            active = bool(data['active'])
            updates.append("activo = %s")
            params.append(active)
            # Si se desactiva, también resetear notificado
            if not active:
                updates.append("notificado = FALSE")
        
        if updates:
            params.append(alert_id)
            cursor.execute(f"""
                UPDATE alertas_precio
                SET {', '.join(updates)}, actualizado_en = %s
                WHERE id = %s
            """, params + [datetime.now(timezone.utc), alert_id])
            conn.commit()
        
        # Obtener alerta actualizada
        cursor.execute("""
            SELECT id, usuario_id, llanta_id, negocio_id, precio_maximo, activo, notificado, creado_en, actualizado_en
            FROM alertas_precio WHERE id = %s
        """, (alert_id,))
        updated_alert = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        return jsonify(price_alert_to_dict(updated_alert)), 200
    
    except Exception as e:
        if conn:
            conn.rollback()
            if 'cursor' in locals():
                cursor.close()
            conn.close()
        logger.error(f"Error in update_price_alert: {str(e)}", exc_info=True, extra={
            'endpoint': 'update_price_alert',
            'user_id': user.get('id') if user else None,
            'alert_id': alert_id
        })
        return jsonify({'error': 'Error updating price alert'}), 500


@price_alerts_bp.route('/<alert_id>', methods=['DELETE'])
@jwt_required()
def delete_price_alert(alert_id):
    """Delete a price alert."""
    user = get_current_user()
    if not user:
        return jsonify({'error': 'Authentication required'}), 401
    
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection error'}), 500
    
    try:
        cursor = conn.cursor()
        user_id = user.get('id')
        
        # Verificar que la alerta existe y pertenece al usuario
        cursor.execute("""
            SELECT id FROM alertas_precio
            WHERE id = %s AND usuario_id = %s
        """, (alert_id, user_id))
        
        if not cursor.fetchone():
            cursor.close()
            conn.close()
            return jsonify({'error': 'Alert not found'}), 404
        
        # Eliminar alerta
        cursor.execute("DELETE FROM alertas_precio WHERE id = %s", (alert_id,))
        conn.commit()
        
        # Log change for audit
        from app.governance.audit import log_change
        log_change(
            table='alertas_precio',
            record_id=alert_id,
            action='DELETE',
            user_id=user_id,
            user_email=user.get('correo')
        )
        
        cursor.close()
        conn.close()
        
        return '', 204
    
    except Exception as e:
        if conn:
            conn.rollback()
            if 'cursor' in locals():
                cursor.close()
            conn.close()
        logger.error(f"Error in delete_price_alert: {str(e)}", exc_info=True, extra={
            'endpoint': 'delete_price_alert',
            'user_id': user.get('id') if user else None,
            'alert_id': alert_id
        })
        return jsonify({'error': 'Error deleting price alert'}), 500