from flask import Blueprint, jsonify, request
from flask_jwt_extended import jwt_required
from sqlalchemy import func, extract, and_, or_
from datetime import datetime, timedelta
from app.database import db
from app.models import Tire, TireBusiness, InventoryItem, User, Review, TipoLlanta
from app.auth import get_current_user

stats_bp = Blueprint('stats', __name__)

@stats_bp.route('/dashboard', methods=['GET'])
@jwt_required()
def get_dashboard_stats():
    """Get dashboard statistics. Requires authentication."""
    user = get_current_user()
    if not user:
        return jsonify({'error': 'Authentication required'}), 401
    
    if user.role.value == 'super-admin':
        # Super admin stats
        total_tires = Tire.query.count()
        total_businesses = TireBusiness.query.count()
        total_inventory_items = InventoryItem.query.count()
        total_inventory_value = db.session.query(func.sum(InventoryItem.precio * InventoryItem.cantidad)).scalar() or 0
        total_users = User.query.count()
        
        return jsonify({
            'total_tires': total_tires,
            'total_businesses': total_businesses,
            'total_inventory_items': total_inventory_items,
            'total_inventory_value': float(total_inventory_value),
            'total_users': total_users,
        }), 200
    
    elif user.role.value == 'business-admin' and user.business_id:
        # Business admin stats
        business_inventory = InventoryItem.query.filter_by(negocio_id=user.business_id).all()
        total_items = len(business_inventory)
        total_quantity = sum(item.cantidad for item in business_inventory)
        total_value = sum(item.precio * item.cantidad for item in business_inventory)
        unique_tires = len(set(item.llanta_id for item in business_inventory))
        low_stock_items = [item for item in business_inventory if item.cantidad < 10]
        
        return jsonify({
            'total_items': total_items,
            'total_quantity': total_quantity,
            'total_value': float(total_value),
            'unique_tires': unique_tires,
            'low_stock_count': len(low_stock_items),
            'low_stock_items': [
                {
                    'id': item.id,
                    'tire_id': item.llanta_id,
                    'quantity': item.cantidad,
                    'price': item.precio
                }
                for item in low_stock_items[:10]  # Limit to 10
            ]
        }), 200
    
    return jsonify({'error': 'No stats available for this user'}), 403

@stats_bp.route('/popular-tires', methods=['GET'])
def get_popular_tires():
    """Get most popular tires (by number of businesses offering them). Public endpoint."""
    # Count how many businesses offer each tire
    tire_counts = db.session.query(
        InventoryItem.llanta_id,
        func.count(func.distinct(InventoryItem.negocio_id)).label('business_count')
    ).group_by(InventoryItem.llanta_id).order_by(func.count(func.distinct(InventoryItem.negocio_id)).desc()).limit(10).all()
    
    popular_tire_ids = [tire_id for tire_id, _ in tire_counts]
    popular_tires = Tire.query.filter(Tire.id.in_(popular_tire_ids)).all()
    
    # Sort by business count
    tire_dict = {tire.id: tire for tire in popular_tires}
    sorted_tires = [tire_dict[tire_id] for tire_id in popular_tire_ids if tire_id in tire_dict]
    
    # Helper function to get tire brand/model/type
    def get_tire_brand(tire):
        if tire.marca_llanta_rel:
            return tire.marca_llanta_rel.nombre
        return tire.marca or ""
    
    def get_tire_model(tire):
        if tire.modelo_llanta_rel:
            return tire.modelo_llanta_rel.nombre
        return tire.modelo or ""
    
    def get_tire_type(tire):
        if tire.tipo_llanta_rel:
            return tire.tipo_llanta_rel.nombre
        if tire.tipo:
            if hasattr(tire.tipo, 'value'):
                return tire.tipo.value
            return str(tire.tipo)
        return ""
    
    return jsonify([{
        'id': tire.id,
        'brand': get_tire_brand(tire),
        'model': get_tire_model(tire),
        'type': get_tire_type(tire),
        'business_count': next((count for tid, count in tire_counts if tid == tire.id), 0)
    } for tire in sorted_tires]), 200

@stats_bp.route('/reports/most-searched-tires', methods=['GET'])
@jwt_required()
def get_most_searched_tires():
    """Get most searched tires by inventory count. Admin only."""
    user = get_current_user()
    if not user or user.role.value != 'super-admin':
        return jsonify({'error': 'Super admin access required'}), 403
    
    # Get tires with most inventory items (proxy for popularity)
    tire_counts = db.session.query(
        InventoryItem.llanta_id,
        func.count(InventoryItem.id).label('inventory_count'),
        func.sum(InventoryItem.cantidad).label('total_quantity')
    ).group_by(InventoryItem.llanta_id).order_by(func.count(InventoryItem.id).desc()).limit(10).all()
    
    tire_ids = [tire_id for tire_id, _, _ in tire_counts]
    tires = Tire.query.filter(Tire.id.in_(tire_ids)).all()
    tire_dict = {tire.id: tire for tire in tires}
    
    def get_tire_brand(tire):
        if tire.marca_llanta_rel:
            return tire.marca_llanta_rel.nombre
        return tire.marca or ""
    
    def get_tire_model(tire):
        if tire.modelo_llanta_rel:
            return tire.modelo_llanta_rel.nombre
        return tire.modelo or ""
    
    return jsonify([{
        'id': tire_id,
        'brand': get_tire_brand(tire_dict[tire_id]),
        'model': get_tire_model(tire_dict[tire_id]),
        'inventory_count': count,
        'total_quantity': int(total_qty) if total_qty else 0
    } for tire_id, count, total_qty in tire_counts if tire_id in tire_dict]), 200

@stats_bp.route('/reports/most-active-businesses', methods=['GET'])
@jwt_required()
def get_most_active_businesses():
    """Get most active businesses by inventory count. Admin only."""
    user = get_current_user()
    if not user or user.role.value != 'super-admin':
        return jsonify({'error': 'Super admin access required'}), 403
    
    business_counts = db.session.query(
        InventoryItem.negocio_id,
        func.count(InventoryItem.id).label('inventory_count'),
        func.sum(InventoryItem.cantidad).label('total_quantity'),
        func.sum(InventoryItem.precio * InventoryItem.cantidad).label('total_value')
    ).group_by(InventoryItem.negocio_id).order_by(func.count(InventoryItem.id).desc()).limit(10).all()
    
    business_ids = [biz_id for biz_id, _, _, _ in business_counts]
    businesses = TireBusiness.query.filter(TireBusiness.id.in_(business_ids)).all()
    business_dict = {biz.id: biz for biz in businesses}
    
    return jsonify([{
        'id': biz_id,
        'name': business_dict[biz_id].nombre,
        'inventory_count': count,
        'total_quantity': int(total_qty) if total_qty else 0,
        'total_value': float(total_val) if total_val else 0,
        'rating': float(business_dict[biz_id].calificacion)
    } for biz_id, count, total_qty, total_val in business_counts if biz_id in business_dict]), 200

@stats_bp.route('/reports/price-trends', methods=['GET'])
@jwt_required()
def get_price_trends():
    """Get price trends by tire type. Admin only."""
    user = get_current_user()
    if not user or user.role.value != 'super-admin':
        return jsonify({'error': 'Super admin access required'}), 403
    
    # Get average price by tire type
    # Get inventory items with tire type info
    results = db.session.query(
        Tire.tipo_id,
        TipoLlanta.nombre,
        func.avg(InventoryItem.precio).label('avg_price'),
        func.min(InventoryItem.precio).label('min_price'),
        func.max(InventoryItem.precio).label('max_price'),
        func.count(InventoryItem.id).label('count')
    ).join(
        InventoryItem, InventoryItem.llanta_id == Tire.id
    ).join(
        TipoLlanta, TipoLlanta.id == Tire.tipo_id
    ).group_by(Tire.tipo_id, TipoLlanta.nombre).all()
    
    return jsonify([{
        'type': tipo_nombre,
        'avg_price': float(avg_price) if avg_price else 0,
        'min_price': float(min_price) if min_price else 0,
        'max_price': float(max_price) if max_price else 0,
        'count': int(count) if count else 0
    } for _, tipo_nombre, avg_price, min_price, max_price, count in results]), 200

@stats_bp.route('/reports/inventory-by-type', methods=['GET'])
@jwt_required()
def get_inventory_by_type():
    """Get inventory distribution by tire type. Admin only."""
    user = get_current_user()
    if not user or user.role.value != 'super-admin':
        return jsonify({'error': 'Super admin access required'}), 403
    
    results = db.session.query(
        Tire.tipo_id,
        TipoLlanta.nombre,
        func.sum(InventoryItem.cantidad).label('total_quantity'),
        func.count(InventoryItem.id).label('item_count')
    ).join(
        InventoryItem, InventoryItem.llanta_id == Tire.id
    ).join(
        TipoLlanta, TipoLlanta.id == Tire.tipo_id
    ).group_by(Tire.tipo_id, TipoLlanta.nombre).all()
    
    return jsonify([{
        'type': tipo_nombre,
        'total_quantity': int(total_qty) if total_qty else 0,
        'item_count': int(item_count) if item_count else 0
    } for _, tipo_nombre, total_qty, item_count in results]), 200

@stats_bp.route('/reports/business-stats', methods=['GET'])
@jwt_required()
def get_business_stats():
    """Get business statistics. Business admin or super admin."""
    user = get_current_user()
    if not user:
        return jsonify({'error': 'Authentication required'}), 401
    
    business_id = request.args.get('business_id')
    
    # Business admin can only see their own stats
    if user.role.value == 'business-admin':
        if not user.business_id:
            return jsonify({'error': 'No business associated'}), 403
        business_id = user.business_id
    elif user.role.value != 'super-admin':
        return jsonify({'error': 'Access denied'}), 403
    
    if not business_id:
        return jsonify({'error': 'business_id required'}), 400
    
    business = TireBusiness.query.filter_by(id=business_id).first()
    if not business:
        return jsonify({'error': 'Business not found'}), 404
    
    inventory_items = InventoryItem.query.filter_by(negocio_id=business_id).all()
    
    total_items = len(inventory_items)
    total_quantity = sum(item.cantidad for item in inventory_items)
    total_value = sum(item.precio * item.cantidad for item in inventory_items)
    unique_tires = len(set(item.llanta_id for item in inventory_items))
    avg_price = total_value / total_quantity if total_quantity > 0 else 0
    
    # Get reviews
    reviews = Review.query.filter_by(negocio_id=business_id).all()
    review_count = len(reviews)
    avg_rating = sum(r.calificacion for r in reviews) / review_count if review_count > 0 else 0
    
    return jsonify({
        'business_id': business_id,
        'business_name': business.nombre,
        'total_items': total_items,
        'total_quantity': total_quantity,
        'total_value': float(total_value),
        'unique_tires': unique_tires,
        'avg_price': float(avg_price),
        'review_count': review_count,
        'avg_rating': float(avg_rating),
        'rating': float(business.calificacion)
    }), 200

@stats_bp.route('/reports/inventory-over-time', methods=['GET'])
@jwt_required()
def get_inventory_over_time():
    """Get inventory changes over time. Admin only."""
    user = get_current_user()
    if not user or user.role.value != 'super-admin':
        return jsonify({'error': 'Super admin access required'}), 403
    
    # Get inventory items grouped by month
    results = db.session.query(
        extract('year', InventoryItem.creado_en).label('year'),
        extract('month', InventoryItem.creado_en).label('month'),
        func.count(InventoryItem.id).label('item_count'),
        func.sum(InventoryItem.cantidad).label('total_quantity')
    ).group_by(
        extract('year', InventoryItem.creado_en),
        extract('month', InventoryItem.creado_en)
    ).order_by(
        extract('year', InventoryItem.creado_en),
        extract('month', InventoryItem.creado_en)
    ).all()
    
    return jsonify([{
        'year': int(year),
        'month': int(month),
        'item_count': int(item_count) if item_count else 0,
        'total_quantity': int(total_qty) if total_qty else 0,
        'label': f"{int(month):02d}/{int(year)}"
    } for year, month, item_count, total_qty in results]), 200