"""
Enhanced models for the comprehensive Reports & Statements Dashboard
"""
from django.db import models
from django.contrib.auth import get_user_model
from django.utils import timezone
from django.db.models import Q, Count, Sum, Avg
from decimal import Decimal
import uuid

User = get_user_model()


class CustomerRequestManager(models.Manager):
    """Manager for CustomerRequest model with common queries"""
    
    def pending(self):
        """Get all pending requests"""
        return self.filter(status='pending')
    
    def in_progress(self):
        """Get all in-progress requests"""
        return self.filter(status='in_progress')
    
    def resolved(self):
        """Get all resolved requests"""
        return self.filter(status='resolved')
    
    def high_priority(self):
        """Get high priority requests"""
        return self.filter(priority__in=['high', 'urgent'])
    
    def by_type(self, request_type):
        """Get requests by type"""
        return self.filter(request_type=request_type)
    
    def assigned_to_user(self, user):
        """Get requests assigned to a specific user"""
        return self.filter(assigned_to=user)
    
    def unassigned(self):
        """Get unassigned requests"""
        return self.filter(assigned_to__isnull=True)
    
    def overdue(self, hours=24):
        """Get requests that are overdue (older than specified hours)"""
        from django.utils import timezone
        cutoff_time = timezone.now() - timezone.timedelta(hours=hours)
        return self.filter(
            created_at__lt=cutoff_time,
            status__in=['pending', 'in_progress']
        )
    
    def get_analytics(self, start_date=None, end_date=None):
        """Get request analytics for a date range"""
        queryset = self.all()
        if start_date:
            queryset = queryset.filter(created_at__gte=start_date)
        if end_date:
            queryset = queryset.filter(created_at__lte=end_date)
        
        return {
            'total_requests': queryset.count(),
            'pending_requests': queryset.filter(status='pending').count(),
            'in_progress_requests': queryset.filter(status='in_progress').count(),
            'resolved_requests': queryset.filter(status='resolved').count(),
            'high_priority_requests': queryset.filter(priority__in=['high', 'urgent']).count(),
            'avg_resolution_time': queryset.filter(
                resolved_at__isnull=False
            ).aggregate(
                avg_time=Avg('resolved_at') - Avg('created_at')
            )['avg_time'],
            'requests_by_type': queryset.values('request_type').annotate(
                count=Count('id')
            ).order_by('-count'),
        }


class RegistrationFeeManager(models.Manager):
    """Manager for RegistrationFee model with common queries"""
    
    def active(self):
        """Get active registration fees"""
        return self.filter(is_active=True)
    
    def by_product_type(self, product_type):
        """Get fees by product type"""
        return self.filter(product_type=product_type)
    
    def effective_now(self):
        """Get fees that are currently effective"""
        now = timezone.now()
        return self.filter(
            is_active=True,
            effective_from__lte=now
        ).filter(
            Q(effective_to__isnull=True) | Q(effective_to__gte=now)
        )


class RegistrationFeePaymentManager(models.Manager):
    """Manager for RegistrationFeePayment model with common queries"""
    
    def by_payment_method(self, method):
        """Get payments by payment method"""
        return self.filter(payment_method=method)
    
    def by_date_range(self, start_date, end_date):
        """Get payments within date range"""
        return self.filter(payment_date__range=[start_date, end_date])
    
    def by_customer(self, customer):
        """Get payments by customer"""
        return self.filter(customer=customer)
    
    def get_revenue_analytics(self, start_date=None, end_date=None):
        """Get revenue analytics for registration fees"""
        queryset = self.all()
        if start_date:
            queryset = queryset.filter(payment_date__gte=start_date)
        if end_date:
            queryset = queryset.filter(payment_date__lte=end_date)
        
        return {
            'total_revenue': queryset.aggregate(total=Sum('amount_paid'))['total'] or Decimal('0'),
            'total_payments': queryset.count(),
            'avg_payment': queryset.aggregate(avg=Avg('amount_paid'))['avg'] or Decimal('0'),
            'revenue_by_product': queryset.values(
                'registration_fee__product_type'
            ).annotate(
                total=Sum('amount_paid'),
                count=Count('id')
            ).order_by('-total'),
            'revenue_by_method': queryset.values('payment_method').annotate(
                total=Sum('amount_paid'),
                count=Count('id')
            ).order_by('-total'),
        }


class ReportScheduleManager(models.Manager):
    """Manager for ReportSchedule model with common queries"""
    
    def active(self):
        """Get active report schedules"""
        return self.filter(is_active=True)
    
    def due_for_execution(self):
        """Get schedules that are due for execution"""
        return self.filter(
            is_active=True,
            next_run__lte=timezone.now()
        )
    
    def by_frequency(self, frequency):
        """Get schedules by frequency"""
        return self.filter(frequency=frequency)
    
    def by_report_type(self, report_type):
        """Get schedules by report type"""
        return self.filter(report_type=report_type)


class CustomerRequest(models.Model):
    """
    Customer service requests and inquiries
    """
    REQUEST_TYPES = [
        ('loan_inquiry', 'Loan Inquiry'),
        ('payment_issue', 'Payment Issue'),
        ('account_issue', 'Account Issue'),
        ('document_issue', 'Document Issue'),
        ('rollover_request', 'Rollover Request'),
        ('complaint', 'Complaint'),
        ('technical_support', 'Technical Support'),
        ('general_inquiry', 'General Inquiry'),
        ('loan_extension', 'Loan Extension'),
        ('payment_plan', 'Payment Plan Request'),
        ('account_closure', 'Account Closure'),
        ('statement_request', 'Statement Request'),
        ('receipt_request', 'Receipt Request'),
        ('other', 'Other'),
    ]
    
    STATUS_CHOICES = [
        ('pending', 'Pending'),
        ('in_progress', 'In Progress'),
        ('resolved', 'Resolved'),
        ('closed', 'Closed'),
        ('escalated', 'Escalated'),
        ('cancelled', 'Cancelled'),
    ]
    
    PRIORITY_LEVELS = [
        ('low', 'Low'),
        ('medium', 'Medium'),
        ('high', 'High'),
        ('urgent', 'Urgent'),
    ]
    
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    request_number = models.CharField(max_length=20, unique=True)
    
    # Request details
    customer = models.ForeignKey(User, on_delete=models.CASCADE, related_name='customer_requests')
    request_type = models.CharField(max_length=30, choices=REQUEST_TYPES)
    subject = models.CharField(max_length=200)
    description = models.TextField()
    priority = models.CharField(max_length=20, choices=PRIORITY_LEVELS, default='medium')
    
    # Status and assignment
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='pending')
    assigned_to = models.ForeignKey(
        User, 
        on_delete=models.SET_NULL, 
        null=True, 
        blank=True, 
        related_name='assigned_requests'
    )
    
    # Related objects
    related_loan = models.ForeignKey(
        'loans.Loan', 
        on_delete=models.SET_NULL, 
        null=True, 
        blank=True
    )
    related_application = models.ForeignKey(
        'loans.LoanApplication', 
        on_delete=models.SET_NULL, 
        null=True, 
        blank=True
    )
    
    # Resolution
    resolution_notes = models.TextField(blank=True, null=True)
    resolved_by = models.ForeignKey(
        User, 
        on_delete=models.SET_NULL, 
        null=True, 
        blank=True, 
        related_name='resolved_requests'
    )
    resolved_at = models.DateTimeField(blank=True, null=True)
    
    # Timestamps
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    # Custom manager
    objects = CustomerRequestManager()
    
    class Meta:
        db_table = 'customer_requests'
        ordering = ['-created_at']
        indexes = [
            models.Index(fields=['status', 'priority']),
            models.Index(fields=['request_type', 'created_at']),
            models.Index(fields=['assigned_to', 'status']),
            models.Index(fields=['customer', 'status']),
            models.Index(fields=['created_at', 'status']),
            models.Index(fields=['priority', 'created_at']),
        ]
    
    def __str__(self):
        return f"Request {self.request_number} - {self.customer.get_full_name()}"
    
    def save(self, *args, **kwargs):
        if not self.request_number:
            # Generate request number
            last_request = CustomerRequest.objects.order_by('-created_at').first()
            if last_request and last_request.request_number:
                try:
                    last_num = int(last_request.request_number.split('-')[1])
                    self.request_number = f"REQ-{last_num + 1:06d}"
                except (IndexError, ValueError):
                    self.request_number = f"REQ-{int(timezone.now().timestamp()):06d}"
            else:
                self.request_number = "REQ-000001"
        
        super().save(*args, **kwargs)
    
    @property
    def resolution_time(self):
        """Calculate resolution time in hours"""
        if self.resolved_at:
            return (self.resolved_at - self.created_at).total_seconds() / 3600
        return None
    
    @property
    def age_hours(self):
        """Calculate age in hours"""
        return (timezone.now() - self.created_at).total_seconds() / 3600


class RegistrationFee(models.Model):
    """
    Registration fees for different products and services
    """
    PRODUCT_TYPES = [
        ('boost', 'Boost'),
        ('boost_plus', 'Boost Plus'),
        ('mwamba', 'Mwamba'),
        ('imara', 'Imara'),
        ('account_opening', 'Account Opening'),
        ('document_processing', 'Document Processing'),
        ('statement_request', 'Statement Request'),
        ('certificate', 'Certificate'),
        ('other', 'Other'),
    ]
    
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    product_type = models.CharField(max_length=30, choices=PRODUCT_TYPES)
    fee_name = models.CharField(max_length=200)
    amount = models.DecimalField(max_digits=10, decimal_places=2)
    description = models.TextField(blank=True, null=True)
    
    # Status
    is_active = models.BooleanField(default=True)
    effective_from = models.DateTimeField(default=timezone.now)
    effective_to = models.DateTimeField(blank=True, null=True)
    
    # Timestamps
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    created_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
    
    # Custom manager
    objects = RegistrationFeeManager()
    
    class Meta:
        db_table = 'registration_fees'
        ordering = ['product_type', 'fee_name']
        indexes = [
            models.Index(fields=['product_type', 'is_active']),
            models.Index(fields=['is_active', 'effective_from']),
            models.Index(fields=['effective_from', 'effective_to']),
        ]
    
    def __str__(self):
        return f"{self.fee_name} - KES {self.amount}"


class RegistrationFeePayment(models.Model):
    """
    Track registration fee payments
    """
    PAYMENT_METHODS = [
        ('mpesa', 'M-Pesa'),
        ('bank', 'Bank Transfer'),
        ('cash', 'Cash'),
        ('cheque', 'Cheque'),
        ('card', 'Card Payment'),
    ]
    
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    receipt_number = models.CharField(max_length=20, unique=True)
    
    # Payment details
    customer = models.ForeignKey(User, on_delete=models.CASCADE)
    registration_fee = models.ForeignKey(RegistrationFee, on_delete=models.CASCADE)
    amount_paid = models.DecimalField(max_digits=10, decimal_places=2)
    payment_method = models.CharField(max_length=20, choices=PAYMENT_METHODS)
    payment_date = models.DateTimeField(default=timezone.now)
    
    # Transaction details
    transaction_reference = models.CharField(max_length=100, blank=True, null=True)
    payment_notes = models.TextField(blank=True, null=True)
    
    # Related objects
    related_loan = models.ForeignKey(
        'loans.Loan', 
        on_delete=models.SET_NULL, 
        null=True, 
        blank=True
    )
    related_application = models.ForeignKey(
        'loans.LoanApplication', 
        on_delete=models.SET_NULL, 
        null=True, 
        blank=True
    )
    
    # Processing
    processed_by = models.ForeignKey(
        User, 
        on_delete=models.SET_NULL, 
        null=True, 
        related_name='processed_reg_fees'
    )
    
    # Timestamps
    created_at = models.DateTimeField(auto_now_add=True)
    
    # Custom manager
    objects = RegistrationFeePaymentManager()
    
    class Meta:
        db_table = 'registration_fee_payments'
        ordering = ['-payment_date']
        indexes = [
            models.Index(fields=['customer', 'payment_date']),
            models.Index(fields=['payment_method', 'payment_date']),
            models.Index(fields=['registration_fee', 'payment_date']),
            models.Index(fields=['payment_date', 'amount_paid']),
            models.Index(fields=['transaction_reference']),
        ]
    
    def __str__(self):
        return f"Receipt {self.receipt_number} - {self.customer.get_full_name()}"
    
    def save(self, *args, **kwargs):
        if not self.receipt_number:
            # Generate receipt number
            last_receipt = RegistrationFeePayment.objects.order_by('-created_at').first()
            if last_receipt and last_receipt.receipt_number:
                try:
                    last_num = int(last_receipt.receipt_number.split('-')[1])
                    self.receipt_number = f"REG-{last_num + 1:06d}"
                except (IndexError, ValueError):
                    self.receipt_number = f"REG-{int(timezone.now().timestamp()):06d}"
            else:
                self.receipt_number = "REG-000001"
        
        super().save(*args, **kwargs)


class ReportSchedule(models.Model):
    """
    Scheduled report generation
    """
    REPORT_TYPES = [
        ('loans_due', 'Loans Due Report'),
        ('delinquent_loans', 'Delinquent Loans Report'),
        ('loans_in_arrears', 'Loans in Arrears Report'),
        ('processing_fees', 'Processing Fees Report'),
        ('interest_income', 'Interest Income Report'),
        ('registration_fees', 'Registration Fees Report'),
        ('customer_requests', 'Customer Requests Report'),
        ('portfolio_summary', 'Portfolio Summary Report'),
        ('collection_report', 'Collection Report'),
        ('risk_analysis', 'Risk Analysis Report'),
    ]
    
    FREQUENCY_CHOICES = [
        ('daily', 'Daily'),
        ('weekly', 'Weekly'),
        ('monthly', 'Monthly'),
        ('quarterly', 'Quarterly'),
        ('yearly', 'Yearly'),
    ]
    
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=200)
    report_type = models.CharField(max_length=30, choices=REPORT_TYPES)
    frequency = models.CharField(max_length=20, choices=FREQUENCY_CHOICES)
    
    # Recipients
    recipients = models.ManyToManyField(User, related_name='scheduled_reports')
    
    # Schedule settings
    is_active = models.BooleanField(default=True)
    next_run = models.DateTimeField()
    last_run = models.DateTimeField(blank=True, null=True)
    
    # Report parameters
    parameters = models.JSONField(default=dict, blank=True)
    
    # Timestamps
    created_at = models.DateTimeField(auto_now_add=True)
    created_by = models.ForeignKey(User, on_delete=models.CASCADE)
    
    # Custom manager
    objects = ReportScheduleManager()
    
    class Meta:
        db_table = 'report_schedules'
        ordering = ['name']
        indexes = [
            models.Index(fields=['is_active', 'next_run']),
            models.Index(fields=['report_type', 'frequency']),
            models.Index(fields=['frequency', 'is_active']),
        ]
    
    def __str__(self):
        return f"{self.name} ({self.get_frequency_display()})"


class ReportExecution(models.Model):
    """
    Track report execution history
    """
    STATUS_CHOICES = [
        ('pending', 'Pending'),
        ('running', 'Running'),
        ('completed', 'Completed'),
        ('failed', 'Failed'),
    ]
    
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    schedule = models.ForeignKey(ReportSchedule, on_delete=models.CASCADE, null=True, blank=True)
    report_type = models.CharField(max_length=30)
    
    # Execution details
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='pending')
    started_at = models.DateTimeField(auto_now_add=True)
    completed_at = models.DateTimeField(blank=True, null=True)
    error_message = models.TextField(blank=True, null=True)
    
    # Generated files
    pdf_file = models.FileField(upload_to='reports/generated/', blank=True, null=True)
    excel_file = models.FileField(upload_to='reports/generated/', blank=True, null=True)
    
    # Parameters used
    parameters = models.JSONField(default=dict, blank=True)
    
    # Execution stats
    records_processed = models.PositiveIntegerField(default=0)
    execution_time = models.FloatField(blank=True, null=True)  # in seconds
    
    class Meta:
        db_table = 'report_executions'
        ordering = ['-started_at']
        indexes = [
            models.Index(fields=['status', 'started_at']),
            models.Index(fields=['report_type', 'started_at']),
            models.Index(fields=['schedule', 'status']),
        ]
    
    def __str__(self):
        return f"{self.report_type} - {self.started_at.strftime('%Y-%m-%d %H:%M')}"