"""
Financial Metrics Calculator Service.

Calculates metrics from GL, HR, and CRM data for alert triggering:
- Budget variance (actual vs budget)
- Cash flow metrics
- Revenue trends
- Expense anomalies
- HR metrics (headcount, payroll)
- CRM metrics (deal velocity, customer churn)
"""

from datetime import datetime, timedelta
from decimal import Decimal
from typing import Dict, List, Optional, Tuple
from sqlalchemy import select, func, and_
from sqlalchemy.ext.asyncio import AsyncSession
from src.models.company import Company
import logging

logger = logging.getLogger(__name__)


class MetricsCalculator:
    """Calculates financial metrics for alert triggering"""
    
    def __init__(self, db: AsyncSession, company_id: int):
        self.db = db
        self.company_id = company_id
    
    async def calculate_budget_variance(self) -> Dict[str, float]:
        """
        Calculate budget variance (actual vs budget).
        
        Returns:
            {
                'total_budget': float,
                'total_actual': float,
                'variance_amount': float,
                'variance_percent': float
            }
        """
        try:
            # Query GL data for actual spend (current month)
            from src.models.application_management import GeneralLedger
            
            today = datetime.now()
            month_start = today.replace(day=1)
            
            result = await self.db.execute(
                select(
                    func.sum(GeneralLedger.debit).label('total_debit'),
                    func.sum(GeneralLedger.credit).label('total_credit'),
                ).where(
                    and_(
                        GeneralLedger.company_id == self.company_id,
                        GeneralLedger.date >= month_start,
                        GeneralLedger.date <= today
                    )
                )
            )
            
            row = result.first()
            total_debit = float(row.total_debit or 0)
            total_credit = float(row.total_credit or 0)
            total_actual = total_debit - total_credit
            
            # Get budget from finance_budget_data for this month/year
            from src.models.application_management import FinanceBudgetData
            
            result = await self.db.execute(
                select(
                    func.sum(FinanceBudgetData.budget_amount).label('total_budget')
                ).where(
                    and_(
                        FinanceBudgetData.company_id == self.company_id,
                        FinanceBudgetData.fiscal_year == today.year,
                        FinanceBudgetData.fiscal_month == today.month
                    )
                )
            )
            
            row = result.first()
            total_budget = float(row.total_budget or 0)
            
            # Calculate variance
            variance_amount = total_actual - total_budget
            variance_percent = (variance_amount / total_budget * 100) if total_budget != 0 else 0
            
            return {
                'total_budget': total_budget,
                'total_actual': total_actual,
                'variance_amount': variance_amount,
                'variance_percent': variance_percent,
                'timestamp': datetime.now().isoformat()
            }
            
        except Exception as e:
            logger.error(f"Error calculating budget variance: {e}")
            return {
                'total_budget': 0.0,
                'total_actual': 0.0,
                'variance_amount': 0.0,
                'variance_percent': 0.0,
                'timestamp': datetime.now().isoformat()
            }
    
    async def calculate_cash_flow_metrics(self) -> Dict[str, float]:
        """
        Calculate cash flow metrics.
        
        Returns:
            {
                'cash_inflow': float,
                'cash_outflow': float,
                'net_cash_flow': float,
                'cash_balance': float
            }
        """
        try:
            from src.models.application_management import GeneralLedger
            
            # Get cash accounts (GL codes for bank/cash)
            result = await self.db.execute(
                select(
                    func.sum(GeneralLedger.credit - GeneralLedger.debit).label('net_cash')
                ).where(
                    and_(
                        GeneralLedger.company_id == self.company_id,
                        GeneralLedger.account_type.in_(['BANK', 'CASH'])
                    )
                )
            )
            
            row = result.first()
            cash_balance = float(row.net_cash or 0)
            
            # Get current month flows
            today = datetime.now()
            month_start = today.replace(day=1)
            
            result = await self.db.execute(
                select(
                    func.sum(GeneralLedger.credit).label('inflow'),
                    func.sum(GeneralLedger.debit).label('outflow')
                ).where(
                    and_(
                        GeneralLedger.company_id == self.company_id,
                        GeneralLedger.date >= month_start,
                        GeneralLedger.date <= today,
                        GeneralLedger.account_type.in_(['BANK', 'CASH'])
                    )
                )
            )
            
            row = result.first()
            cash_inflow = float(row.inflow or 0)
            cash_outflow = float(row.outflow or 0)
            net_cash_flow = cash_inflow - cash_outflow
            
            return {
                'cash_inflow': cash_inflow,
                'cash_outflow': cash_outflow,
                'net_cash_flow': net_cash_flow,
                'cash_balance': cash_balance,
                'timestamp': datetime.now().isoformat()
            }
            
        except Exception as e:
            logger.error(f"Error calculating cash flow: {e}")
            return {
                'cash_inflow': 0.0,
                'cash_outflow': 0.0,
                'net_cash_flow': 0.0,
                'cash_balance': 0.0,
                'timestamp': datetime.now().isoformat()
            }
    
    async def calculate_revenue_trends(self) -> Dict[str, float]:
        """
        Calculate revenue trends (month-over-month growth).
        
        Returns:
            {
                'current_month_revenue': float,
                'previous_month_revenue': float,
                'mom_growth_percent': float,
                'trend': 'UP' | 'DOWN' | 'FLAT'
            }
        """
        try:
            from src.models.application_management import GeneralLedger
            
            today = datetime.now()
            current_month_start = today.replace(day=1)
            previous_month_last = (current_month_start - timedelta(days=1))
            previous_month_start = previous_month_last.replace(day=1)
            
            # Current month revenue (AR accounts)
            result = await self.db.execute(
                select(
                    func.sum(GeneralLedger.credit).label('revenue')
                ).where(
                    and_(
                        GeneralLedger.company_id == self.company_id,
                        GeneralLedger.date >= current_month_start,
                        GeneralLedger.date <= today,
                        GeneralLedger.account_type == 'REVENUE'
                    )
                )
            )
            
            row = result.first()
            current_revenue = float(row.revenue or 0)
            
            # Previous month revenue
            result = await self.db.execute(
                select(
                    func.sum(GeneralLedger.credit).label('revenue')
                ).where(
                    and_(
                        GeneralLedger.company_id == self.company_id,
                        GeneralLedger.date >= previous_month_start,
                        GeneralLedger.date <= previous_month_last,
                        GeneralLedger.account_type == 'REVENUE'
                    )
                )
            )
            
            row = result.first()
            previous_revenue = float(row.revenue or 0)
            
            # Calculate growth
            mom_growth = ((current_revenue - previous_revenue) / previous_revenue * 100) if previous_revenue != 0 else 0
            
            if mom_growth > 5:
                trend = 'UP'
            elif mom_growth < -5:
                trend = 'DOWN'
            else:
                trend = 'FLAT'
            
            return {
                'current_month_revenue': current_revenue,
                'previous_month_revenue': previous_revenue,
                'mom_growth_percent': mom_growth,
                'trend': trend,
                'timestamp': datetime.now().isoformat()
            }
            
        except Exception as e:
            logger.error(f"Error calculating revenue trends: {e}")
            return {
                'current_month_revenue': 0.0,
                'previous_month_revenue': 0.0,
                'mom_growth_percent': 0.0,
                'trend': 'FLAT',
                'timestamp': datetime.now().isoformat()
            }
    
    async def detect_expense_anomalies(self) -> List[Dict]:
        """
        Detect unusual expense patterns (outliers from normal).
        
        Returns:
            [
                {
                    'expense_type': str,
                    'amount': float,
                    'baseline': float,
                    'variance_percent': float,
                    'severity': 'LOW' | 'MEDIUM' | 'HIGH'
                }
            ]
        """
        try:
            from src.models.application_management import GeneralLedger
            
            today = datetime.now()
            
            # Get average expense by category for last 3 months
            three_months_ago = today - timedelta(days=90)
            
            result = await self.db.execute(
                select(
                    GeneralLedger.expense_category,
                    func.avg(GeneralLedger.debit).label('avg_expense'),
                    func.max(GeneralLedger.debit).label('max_expense')
                ).where(
                    and_(
                        GeneralLedger.company_id == self.company_id,
                        GeneralLedger.date >= three_months_ago,
                        GeneralLedger.account_type == 'EXPENSE'
                    )
                ).group_by(GeneralLedger.expense_category)
            )
            
            anomalies = []
            
            for row in result.fetchall():
                category = row.expense_category
                avg_expense = float(row.avg_expense or 0)
                
                # Get current month spending
                month_start = today.replace(day=1)
                
                result2 = await self.db.execute(
                    select(
                        func.sum(GeneralLedger.debit).label('total')
                    ).where(
                        and_(
                            GeneralLedger.company_id == self.company_id,
                            GeneralLedger.expense_category == category,
                            GeneralLedger.date >= month_start,
                            GeneralLedger.date <= today,
                            GeneralLedger.account_type == 'EXPENSE'
                        )
                    )
                )
                
                current = float(result2.first().total or 0)
                
                # Check for anomaly (> 30% variance)
                if avg_expense > 0:
                    variance = ((current - avg_expense) / avg_expense * 100)
                    
                    if abs(variance) > 30:
                        if abs(variance) > 50:
                            severity = 'HIGH'
                        elif abs(variance) > 30:
                            severity = 'MEDIUM'
                        else:
                            severity = 'LOW'
                        
                        anomalies.append({
                            'expense_category': category,
                            'current_amount': current,
                            'baseline': avg_expense,
                            'variance_percent': variance,
                            'severity': severity
                        })
            
            return anomalies
            
        except Exception as e:
            logger.error(f"Error detecting anomalies: {e}")
            return []
    
    async def get_all_metrics(self) -> Dict:
        """Get all metrics at once"""
        return {
            'budget_variance': await self.calculate_budget_variance(),
            'cash_flow': await self.calculate_cash_flow_metrics(),
            'revenue_trends': await self.calculate_revenue_trends(),
            'expense_anomalies': await self.detect_expense_anomalies(),
            'calculated_at': datetime.now().isoformat()
        }
