# backend/services/analytics_service.py
from datetime import datetime, timedelta
from sqlalchemy import func, and_
from database.models import Attendance, Employee
import pandas as pd

class AnalyticsService:
    def __init__(self, db_session):
        self.db = db_session
        
    def get_personnel_present(self, date=None):
        """Get number of personnel present today"""
        if date is None:
            date = datetime.now().date()
            
        count = self.db.query(Attendance).filter(
            Attendance.date == date,
            Attendance.status.in_(['present', 'early', 'on_time', 'late'])
        ).count()
        return count
        
    def get_shift_coverage(self):
        """Calculate shift coverage percentage"""
        total_employees = self.db.query(Employee).count()
        present_today = self.get_personnel_present()
        
        if total_employees == 0:
            return 0
        return (present_today / total_employees) * 100
        
    def get_staffing_trends(self, days=30):
        """Get staffing trends for last N days"""
        end_date = datetime.now().date()
        start_date = end_date - timedelta(days=days)
        
        results = self.db.query(
            Attendance.date,
            func.count(Attendance.id).label('present_count')
        ).filter(
            Attendance.date.between(start_date, end_date),
            Attendance.status.in_(['present', 'early', 'on_time', 'late'])
        ).group_by(Attendance.date).all()
        
        return pd.DataFrame(results, columns=['date', 'present_count'])
        
    def get_missing_personnel(self):
        """Get list of employees who haven't checked in today"""
        today = datetime.now().date()
        
        # Get all employees
        all_employees = self.db.query(Employee).all()
        all_ids = [e.id for e in all_employees]
        
        # Get present employees today
        present = self.db.query(Attendance.employee_id).filter(
            Attendance.date == today,
            Attendance.status.in_(['present', 'early', 'on_time', 'late'])
        ).distinct().all()
        present_ids = [p[0] for p in present]
        
        # Find missing
        missing_ids = set(all_ids) - set(present_ids)
        
        return self.db.query(Employee).filter(
            Employee.id.in_(missing_ids)
        ).all()