from flask import render_template, flash, redirect, url_for
from flask_login import login_required, current_user
from scripts.models import db, Device, WorkOrder
from collections import defaultdict
from sqlalchemy import func
from utils.db_utils import cache_query, db_operation

def init_dashboard_routes(app):
    @app.route('/dashboard')
    @login_required
    def dashboard():
        allowed_roles = ['装维员', '统计员', '管理员', 'admin']
        if current_user.role not in allowed_roles and current_user.name != 'Admin':
            app.logger.warning(f'未授权访问：用户 {current_user.name} 尝试访问仪表盘页面')
            flash('您没有权限访问此页面')
            return redirect(url_for('login'))
        
        try:
            app.logger.info(f'用户 {current_user.name} 访问仪表盘页面')
            
            # 获取设备在线率数据
            overall_stats = get_overall_device_stats()
            branch_stats = get_branch_device_stats()
            town_stats = get_town_device_stats()
            
            return render_template('dashboard.html',
                                overall_stats=overall_stats,
                                branch_stats=branch_stats,
                                town_stats=town_stats)
        except Exception as e:
            app.logger.error(f"仪表盘页面加载失败: {str(e)}")
            flash('数据加载失败，请稍后重试')
            return redirect(url_for('login'))

    @cache_query(ttl=300)  # 缓存5分钟
    def get_overall_device_stats():
        """获取总体设备在线率统计"""
        stats = {}
        
        # 获取设备类型列表
        device_types = db.session.query(Device.device_type).distinct().all()
        device_types = [d[0] for d in device_types if d[0]]
        
        for device_type in device_types:
            # 获取该类型的设备总数
            total_devices = db.session.query(func.count(Device.device_id))\
                .filter(Device.device_type == device_type)\
                .scalar() or 0
            
            # 获取该类型的派单数
            work_orders = db.session.query(func.count(WorkOrder.order_id))\
                .join(Device, WorkOrder.device_id == Device.device_id)\
                .filter(Device.device_type == device_type)\
                .scalar() or 0
            
            # 计算在线率
            if total_devices > 0:
                online_rate = ((total_devices - work_orders) / total_devices) * 100
            else:
                online_rate = 0
            
            stats[device_type] = {
                'total': total_devices,
                'work_orders': work_orders,
                'online_rate': round(online_rate, 2)
            }
        
        return stats

    @cache_query(ttl=300)  # 缓存5分钟
    def get_branch_device_stats():
        """获取各支局设备在线率统计"""
        stats = {}
        
        # 获取所有支局
        branches = db.session.query(Device.branch).distinct().all()
        branches = [b[0] for b in branches if b[0]]
        
        # 获取设备类型列表
        device_types = db.session.query(Device.device_type).distinct().all()
        device_types = [d[0] for d in device_types if d[0]]
        
        for branch in branches:
            branch_stats = {}
            
            for device_type in device_types:
                # 获取该支局该类型的设备总数
                total_devices = db.session.query(func.count(Device.device_id))\
                    .filter(Device.branch == branch, Device.device_type == device_type)\
                    .scalar() or 0
                
                # 获取该支局该类型的派单数
                work_orders = db.session.query(func.count(WorkOrder.order_id))\
                    .join(Device, WorkOrder.device_id == Device.device_id)\
                    .filter(Device.branch == branch, Device.device_type == device_type)\
                    .scalar() or 0
                
                # 计算在线率
                if total_devices > 0:
                    online_rate = ((total_devices - work_orders) / total_devices) * 100
                else:
                    online_rate = 0
                
                branch_stats[device_type] = {
                    'total': total_devices,
                    'work_orders': work_orders,
                    'online_rate': round(online_rate, 2)
                }
            
            # 计算该支局总体在线率
            total_branch_devices = db.session.query(func.count(Device.device_id))\
                .filter(Device.branch == branch)\
                .scalar() or 0
                
            total_branch_orders = db.session.query(func.count(WorkOrder.order_id))\
                .join(Device, WorkOrder.device_id == Device.device_id)\
                .filter(Device.branch == branch)\
                .scalar() or 0
                
            if total_branch_devices > 0:
                branch_online_rate = ((total_branch_devices - total_branch_orders) / total_branch_devices) * 100
            else:
                branch_online_rate = 0
                
            branch_stats['总计'] = {
                'total': total_branch_devices,
                'work_orders': total_branch_orders,
                'online_rate': round(branch_online_rate, 2)
            }
            
            stats[branch] = branch_stats
        
        return stats

    @cache_query(ttl=300)  # 缓存5分钟
    def get_town_device_stats():
        """获取各乡镇设备在线率统计"""
        stats = {}
        
        # 获取所有乡镇
        towns = db.session.query(Device.town).distinct().all()
        towns = [t[0] for t in towns if t[0]]
        
        # 获取设备类型列表
        device_types = db.session.query(Device.device_type).distinct().all()
        device_types = [d[0] for d in device_types if d[0]]
        
        for town in towns:
            town_stats = {}
            
            for device_type in device_types:
                # 获取该乡镇该类型的设备总数
                total_devices = db.session.query(func.count(Device.device_id))\
                    .filter(Device.town == town, Device.device_type == device_type)\
                    .scalar() or 0
                
                # 获取该乡镇该类型的派单数
                work_orders = db.session.query(func.count(WorkOrder.order_id))\
                    .join(Device, WorkOrder.device_id == Device.device_id)\
                    .filter(Device.town == town, Device.device_type == device_type)\
                    .scalar() or 0
                
                # 计算在线率
                if total_devices > 0:
                    online_rate = ((total_devices - work_orders) / total_devices) * 100
                else:
                    online_rate = 0
                
                town_stats[device_type] = {
                    'total': total_devices,
                    'work_orders': work_orders,
                    'online_rate': round(online_rate, 2)
                }
            
            # 计算该乡镇总体在线率
            total_town_devices = db.session.query(func.count(Device.device_id))\
                .filter(Device.town == town)\
                .scalar() or 0
                
            total_town_orders = db.session.query(func.count(WorkOrder.order_id))\
                .join(Device, WorkOrder.device_id == Device.device_id)\
                .filter(Device.town == town)\
                .scalar() or 0
                
            if total_town_devices > 0:
                town_online_rate = ((total_town_devices - total_town_orders) / total_town_devices) * 100
            else:
                town_online_rate = 0
                
            town_stats['总计'] = {
                'total': total_town_devices,
                'work_orders': total_town_orders,
                'online_rate': round(town_online_rate, 2)
            }
            
            stats[town] = town_stats
        
        return stats