"""
Analytics Service for RateRight Platform
Provides comprehensive analytics for workers and clients
"""

from datetime import datetime, timedelta
from sqlalchemy import func, case, and_, text
from app.extensions import db
from app.models.contract import Contract
from app.models.job import Job
from app.models.user import User
from app.models.rating import Rating
from app.models.enums import ContractStatus, ApplicationStatus


class AnalyticsService:
    """Service for generating analytics data"""
    
    @staticmethod
    def get_worker_analytics(worker_id):
        """Get comprehensive analytics for a worker"""
        return {
            'earnings': AnalyticsService.get_earnings_over_time(worker_id),
            'completion_rate': AnalyticsService.get_job_completion_rate(worker_id),
            'response_time': AnalyticsService.get_response_time_metrics(worker_id),
            'popular_services': AnalyticsService.get_popular_services(worker_id),
            'peak_hours': AnalyticsService.get_peak_hours_analysis(worker_id),
            'customer_retention': AnalyticsService.get_customer_retention(worker_id),
            'summary': AnalyticsService.get_worker_summary(worker_id)
        }
    
    @staticmethod
    def get_client_analytics(client_id):
        """Get comprehensive analytics for a client"""
        return {
            'spending_patterns': AnalyticsService.get_spending_patterns(client_id),
            'favorite_workers': AnalyticsService.get_favorite_workers(client_id),
            'project_success': AnalyticsService.get_project_success_rate(client_id),
            'summary': AnalyticsService.get_client_summary(client_id)
        }
    
    # Worker Analytics Methods
    
    @staticmethod
    def get_earnings_over_time(worker_id, days=30):
        """Get earnings trend over specified days"""
        end_date = datetime.utcnow()
        start_date = end_date - timedelta(days=days)
        
        query = db.session.query(
            func.date(Contract.completed_at).label('date'),
            func.sum(Contract.amount).label('daily_earnings'),
            func.count(Contract.id).label('jobs_count')
        ).filter(
            Contract.worker_id == worker_id,
            Contract.status == ContractStatus.COMPLETED,
            Contract.completed_at >= start_date
        ).group_by(
            func.date(Contract.completed_at)
        ).order_by(
            func.date(Contract.completed_at)
        )
        
        results = query.all()
        
        # Fill in missing dates with zero earnings
        earnings_data = []
        date_dict = {r.date: {'earnings': float(r.daily_earnings), 'jobs': r.jobs_count} 
                     for r in results}
        
        current_date = start_date.date()
        cumulative = 0
        while current_date <= end_date.date():
            if current_date in date_dict:
                daily_amount = date_dict[current_date]['earnings']
                jobs = date_dict[current_date]['jobs']
            else:
                daily_amount = 0
                jobs = 0
            
            cumulative += daily_amount
            earnings_data.append({
                'date': current_date.isoformat(),
                'earnings': daily_amount,
                'cumulative': cumulative,
                'jobs': jobs
            })
            current_date += timedelta(days=1)
        
        return earnings_data
    
    @staticmethod
    def get_job_completion_rate(worker_id):
        """Get job completion statistics"""
        query = db.session.query(
            func.count(Contract.id).label('total'),
            func.sum(case([(Contract.status == ContractStatus.COMPLETED, 1)], else_=0)).label('completed'),
            func.sum(case([(Contract.status == ContractStatus.CANCELLED, 1)], else_=0)).label('cancelled'),
            func.sum(case([(Contract.status == ContractStatus.ACTIVE, 1)], else_=0)).label('active')
        ).filter(
            Contract.worker_id == worker_id
        )
        
        result = query.first()
        
        if result and result.total > 0:
            completion_rate = (result.completed / result.total * 100) if result.completed else 0
            return {
                'total_jobs': result.total,
                'completed': result.completed or 0,
                'cancelled': result.cancelled or 0,
                'active': result.active or 0,
                'completion_rate': round(completion_rate, 2)
            }
        
        return {
            'total_jobs': 0,
            'completed': 0,
            'cancelled': 0,
            'active': 0,
            'completion_rate': 0
        }
    
    @staticmethod
    def get_response_time_metrics(worker_id):
        """Get response time metrics by job category"""
        # Note: This would require an Application model which may not exist
        # Using a simplified version with contracts
        query = db.session.query(
            Job.category,
            func.count(Contract.id).label('job_count'),
            func.avg(
                func.timestampdiff(text('HOUR'), Job.created_at, Contract.created_at)
            ).label('avg_response_hours')
        ).join(
            Contract, Contract.job_id == Job.id
        ).filter(
            Contract.worker_id == worker_id
        ).group_by(
            Job.category
        )
        
        results = query.all()
        
        return [{
            'category': r.category,
            'job_count': r.job_count,
            'avg_response_hours': round(float(r.avg_response_hours or 0), 1)
        } for r in results]
    
    @staticmethod
    def get_popular_services(worker_id):
        """Get most profitable service categories"""
        query = db.session.query(
            Job.category.label('service'),
            func.count(Contract.id).label('job_count'),
            func.sum(Contract.amount).label('total_revenue'),
            func.avg(Contract.amount).label('avg_job_value'),
            func.avg(Rating.overall_score).label('avg_rating')
        ).join(
            Contract, Contract.job_id == Job.id
        ).outerjoin(
            Rating, Rating.contract_id == Contract.id
        ).filter(
            Contract.worker_id == worker_id,
            Contract.status == ContractStatus.COMPLETED
        ).group_by(
            Job.category
        ).order_by(
            func.sum(Contract.amount).desc()
        ).limit(5)
        
        results = query.all()
        
        return [{
            'service': r.service,
            'job_count': r.job_count,
            'total_revenue': float(r.total_revenue or 0),
            'avg_job_value': round(float(r.avg_job_value or 0), 2),
            'avg_rating': round(float(r.avg_rating or 0), 2) if r.avg_rating else 0
        } for r in results]
    
    @staticmethod
    def get_peak_hours_analysis(worker_id):
        """Get activity analysis by hour of day"""
        query = db.session.query(
            func.hour(Contract.created_at).label('hour'),
            func.count(Contract.id).label('contracts_created')
        ).filter(
            Contract.worker_id == worker_id
        ).group_by(
            func.hour(Contract.created_at)
        ).order_by(
            func.hour(Contract.created_at)
        )
        
        results = query.all()
        hour_dict = {r.hour: r.contracts_created for r in results}
        
        # Fill all 24 hours
        peak_hours = []
        for hour in range(24):
            peak_hours.append({
                'hour': hour,
                'activity': hour_dict.get(hour, 0)
            })
        
        return peak_hours
    
    @staticmethod
    def get_customer_retention(worker_id):
        """Get customer retention metrics"""
        # Get all unique clients
        client_query = db.session.query(
            Contract.contractor_id,
            func.count(Contract.id).label('job_count')
        ).filter(
            Contract.worker_id == worker_id,
            Contract.status == ContractStatus.COMPLETED
        ).group_by(
            Contract.contractor_id
        )
        
        results = client_query.all()
        
        if not results:
            return {
                'unique_clients': 0,
                'repeat_clients': 0,
                'retention_rate': 0,
                'avg_jobs_per_client': 0
            }
        
        unique_clients = len(results)
        repeat_clients = sum(1 for r in results if r.job_count > 1)
        total_jobs = sum(r.job_count for r in results)
        
        return {
            'unique_clients': unique_clients,
            'repeat_clients': repeat_clients,
            'retention_rate': round((repeat_clients / unique_clients * 100) if unique_clients > 0 else 0, 2),
            'avg_jobs_per_client': round(total_jobs / unique_clients if unique_clients > 0 else 0, 2)
        }
    
    @staticmethod
    def get_worker_summary(worker_id):
        """Get worker summary statistics"""
        # Total earnings
        earnings_query = db.session.query(
            func.sum(Contract.amount).label('total_earnings'),
            func.count(Contract.id).label('total_jobs')
        ).filter(
            Contract.worker_id == worker_id,
            Contract.status == ContractStatus.COMPLETED
        ).first()
        
        # Average rating
        rating_query = db.session.query(
            func.avg(Rating.overall_score).label('avg_rating'),
            func.count(Rating.id).label('total_ratings')
        ).filter(
            Rating.rated_id == worker_id
        ).first()
        
        # This month's earnings
        start_of_month = datetime.utcnow().replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        monthly_query = db.session.query(
            func.sum(Contract.amount).label('monthly_earnings')
        ).filter(
            Contract.worker_id == worker_id,
            Contract.status == ContractStatus.COMPLETED,
            Contract.completed_at >= start_of_month
        ).first()
        
        return {
            'total_earnings': float(earnings_query.total_earnings or 0) if earnings_query else 0,
            'total_jobs': earnings_query.total_jobs or 0 if earnings_query else 0,
            'avg_rating': round(float(rating_query.avg_rating or 0), 2) if rating_query else 0,
            'total_ratings': rating_query.total_ratings or 0 if rating_query else 0,
            'monthly_earnings': float(monthly_query.monthly_earnings or 0) if monthly_query else 0
        }
    
    # Client Analytics Methods
    
    @staticmethod
    def get_spending_patterns(client_id, months=12):
        """Get monthly spending patterns"""
        end_date = datetime.utcnow()
        start_date = end_date - timedelta(days=months*30)
        
        query = db.session.query(
            func.date_format(Contract.created_at, '%Y-%m').label('month'),
            func.count(Contract.id).label('projects'),
            func.sum(Contract.amount).label('total_spent'),
            func.avg(Contract.amount).label('avg_cost')
        ).filter(
            Contract.contractor_id == client_id,
            Contract.created_at >= start_date
        ).group_by(
            func.date_format(Contract.created_at, '%Y-%m')
        ).order_by(
            func.date_format(Contract.created_at, '%Y-%m').desc()
        )
        
        results = query.all()
        
        return [{
            'month': r.month,
            'projects': r.projects,
            'total_spent': float(r.total_spent or 0),
            'avg_cost': round(float(r.avg_cost or 0), 2)
        } for r in results]
    
    @staticmethod
    def get_favorite_workers(client_id):
        """Get top workers by engagement"""
        query = db.session.query(
            User.id,
            User.first_name,
            User.last_name,
            func.count(Contract.id).label('projects_together'),
            func.sum(Contract.amount).label('total_paid'),
            func.avg(Rating.overall_score).label('avg_rating')
        ).join(
            Contract, Contract.worker_id == User.id
        ).outerjoin(
            Rating, and_(
                Rating.contract_id == Contract.id,
                Rating.rater_id == client_id
            )
        ).filter(
            Contract.contractor_id == client_id
        ).group_by(
            User.id, User.first_name, User.last_name
        ).order_by(
            func.count(Contract.id).desc()
        ).limit(5)
        
        results = query.all()
        
        return [{
            'worker_id': r.id,
            'name': f"{r.first_name} {r.last_name}",
            'projects_together': r.projects_together,
            'total_paid': float(r.total_paid or 0),
            'avg_rating': round(float(r.avg_rating or 0), 2) if r.avg_rating else 0
        } for r in results]
    
    @staticmethod
    def get_project_success_rate(client_id):
        """Get project success metrics by category"""
        query = db.session.query(
            Job.category,
            func.count(Contract.id).label('total_projects'),
            func.avg(case([(Contract.status == ContractStatus.COMPLETED, 1)], else_=0)).label('success_rate'),
            func.avg(Rating.overall_score).label('avg_satisfaction')
        ).join(
            Contract, Contract.job_id == Job.id
        ).outerjoin(
            Rating, Rating.contract_id == Contract.id
        ).filter(
            Contract.contractor_id == client_id
        ).group_by(
            Job.category
        )
        
        results = query.all()
        
        return [{
            'category': r.category,
            'total_projects': r.total_projects,
            'success_rate': round(float(r.success_rate or 0) * 100, 2),
            'avg_satisfaction': round(float(r.avg_satisfaction or 0), 2) if r.avg_satisfaction else 0
        } for r in results]
    
    @staticmethod
    def get_client_summary(client_id):
        """Get client summary statistics"""
        # Total spending
        spending_query = db.session.query(
            func.sum(Contract.amount).label('total_spent'),
            func.count(Contract.id).label('total_projects')
        ).filter(
            Contract.contractor_id == client_id
        ).first()
        
        # Active projects
        active_query = db.session.query(
            func.count(Contract.id).label('active_projects')
        ).filter(
            Contract.contractor_id == client_id,
            Contract.status == ContractStatus.ACTIVE
        ).first()
        
        # This month's spending
        start_of_month = datetime.utcnow().replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        monthly_query = db.session.query(
            func.sum(Contract.amount).label('monthly_spending')
        ).filter(
            Contract.contractor_id == client_id,
            Contract.created_at >= start_of_month
        ).first()
        
        return {
            'total_spent': float(spending_query.total_spent or 0) if spending_query else 0,
            'total_projects': spending_query.total_projects or 0 if spending_query else 0,
            'active_projects': active_query.active_projects or 0 if active_query else 0,
            'monthly_spending': float(monthly_query.monthly_spending or 0) if monthly_query else 0
        }