from typing import List, Dict, Optional
from datetime import date, datetime
from django.db.models import Q, Sum, Count, F
from django.utils import timezone
from balancedetail.models import BalanceDetail
from paycode.models import Paycode
from user.models import User


class PaycodeAggregationService:
    """Service for aggregating paycode hours from BalanceDetail"""
    
    @staticmethod
    def aggregate_paycode_hours(
        user_ids: List[str],
        period_start: date,
        period_end: date,
        stamp_ids: Optional[List[str]] = None
    ) -> Dict:
        """
        Aggregate paycode hours for multiple users in a period from BalanceDetail
        
        Args:
            user_ids: List of user UUIDs
            period_start: Start date of period
            period_end: End date of period
            stamp_ids: Optional list of specific stamp IDs (not used for BalanceDetail aggregation)
            
        Returns:
            Dictionary with aggregated data:
            {
                'employees': [
                    {
                        'user_id': str,
                        'user_name': str,
                        'paycodes': [
                            {
                                'paycode': str,
                                'paycode_name': str,
                                'total_hours': float,
                                'stamp_count': int  # Number of days/records
                            }
                        ],
                        'total_hours': float
                    }
                ],
                'summary': {
                    'total_employees': int,
                    'total_hours': float,
                    'paycode_totals': [
                        {
                            'paycode': str,
                            'paycode_name': str,
                            'total_hours': float
                        }
                    ]
                }
            }
        """
        # Query BalanceDetail joined with Paycode
        query = Q(
            user_id__in=user_ids,
            date__gte=period_start,
            date__lte=period_end
        )
        
        # Get BalanceDetail records with paycode information
        balance_details = BalanceDetail.objects.filter(query).select_related(
            'user', 'paycode'
        ).order_by('user', 'paycode', 'date')
        
        # Group by user and paycode
        employee_data = {}
        paycode_totals = {}
        
        for balance_detail in balance_details:
            user_id = str(balance_detail.user.id)
            user_name = f"{balance_detail.user.firstname} {balance_detail.user.lastname}"
            
            # Initialize user data if not exists
            if user_id not in employee_data:
                employee_data[user_id] = {
                    'user_id': user_id,
                    'user_name': user_name,
                    'paycodes': {}
                }
            
            # Get paycode information
            paycode = balance_detail.paycode
            paycode_value = paycode.paycode
            paycode_name = paycode.name
            
            # Initialize paycode for this user if not exists
            if paycode_value not in employee_data[user_id]['paycodes']:
                employee_data[user_id]['paycodes'][paycode_value] = {
                    'paycode': paycode_value,
                    'paycode_name': paycode_name,
                    'total_seconds': 0,
                    'record_count': 0
                }
            
            # Aggregate total_work_seconds (convert to hours later)
            # Using net_work_seconds as it represents actual work time
            work_seconds = balance_detail.net_work_seconds or balance_detail.total_work_seconds or 0
            employee_data[user_id]['paycodes'][paycode_value]['total_seconds'] += work_seconds
            employee_data[user_id]['paycodes'][paycode_value]['record_count'] += 1
            
            # Update paycode totals across all employees
            if paycode_value not in paycode_totals:
                paycode_totals[paycode_value] = {
                    'paycode': paycode_value,
                    'paycode_name': paycode_name,
                    'total_seconds': 0
                }
            paycode_totals[paycode_value]['total_seconds'] += work_seconds
        
        # Convert seconds to hours and format results
        result_employees = []
        total_hours_all = 0.0
        
        for user_id, user_data in employee_data.items():
            user_paycodes = []
            user_total_hours = 0.0
            
            for paycode_value, paycode_data in user_data['paycodes'].items():
                # Convert seconds to hours
                paycode_hours = paycode_data['total_seconds'] / 3600.0
                user_total_hours += paycode_hours
                total_hours_all += paycode_hours
                
                user_paycodes.append({
                    'paycode': paycode_value,
                    'paycode_name': paycode_data['paycode_name'],
                    'total_hours': round(paycode_hours, 2),
                    'stamp_count': paycode_data['record_count']  # Number of BalanceDetail records
                })
            
            result_employees.append({
                'user_id': user_id,
                'user_name': user_data['user_name'],
                'paycodes': sorted(user_paycodes, key=lambda x: x['paycode_name']),
                'total_hours': round(user_total_hours, 2)
            })
        
        # Sort employees by name
        result_employees.sort(key=lambda x: x['user_name'])
        
        # Format paycode totals (convert seconds to hours)
        paycode_totals_list = [
            {
                'paycode': pc['paycode'],
                'paycode_name': pc['paycode_name'],
                'total_hours': round(pc['total_seconds'] / 3600.0, 2)
            }
            for pc in sorted(paycode_totals.values(), key=lambda x: x['paycode_name'])
        ]
        
        return {
            'employees': result_employees,
            'summary': {
                'total_employees': len(result_employees),
                'total_hours': round(total_hours_all, 2),
                'paycode_totals': paycode_totals_list
            }
        }