"""
Age & Gender Analytics Report View

This module provides the view for analyzing loan applications and payment patterns
by demographics (age groups and gender).

Validates: Requirements 2.1, 2.5, 2.6
"""

from django.shortcuts import render
from django.contrib.auth.decorators import login_required
from django.http import HttpResponse
from django.utils import timezone
from decimal import Decimal
from datetime import datetime

from loans.models import Loan
from .demographic_service import DemographicAnalysisService
from users.decorators import staff_required


@login_required
@staff_required
def age_gender_analytics_report(request):
    """
    Display age & gender analytics report with demographic analysis.
    
    Validates: Requirements 2.1, 2.5, 2.6
    
    This view:
    - Calculates statistics by age group: total applications, approved, approval rate, 
      avg amount, avg repayment rate
    - Calculates statistics by gender: total applications, approved, approval rate, 
      avg amount, avg repayment rate
    - Analyzes payment patterns by age group and gender
    
    Query Parameters:
        - export: 'pdf' | 'excel' (optional)
    """
    # Import here to avoid circular import
    from .views import get_filtered_loans_for_user
    
    export_format = request.GET.get('export', '')
    
    # Get all loans (including applications)
    loans = Loan.objects.filter(is_deleted=False).select_related('borrower')
    
    # Apply user's branch and portfolio filters
    selected_branch_id = request.session.get('selected_branch_id')
    loans = get_filtered_loans_for_user(request.user, selected_branch_id, loans)
    
    # Use DemographicAnalysisService to analyze loans
    # Validates: Requirements 2.2, 2.3, 2.4, 2.5, 2.6
    age_group_stats = DemographicAnalysisService.analyze_by_age_group(loans)
    gender_stats = DemographicAnalysisService.analyze_by_gender(loans)
    
    # Calculate overall statistics
    total_applications = loans.count()
    approved_applications = loans.filter(
        status__in=['active', 'paid', 'defaulted', 'rolled_over', 'written_off']
    ).count()
    overall_approval_rate = Decimal('0.00')
    if total_applications > 0:
        overall_approval_rate = round(
            Decimal(approved_applications) / Decimal(total_applications) * Decimal('100'), 2
        )
    
    # Prepare chart data for age groups
    age_group_labels = []
    age_group_applications = []
    age_group_approved = []
    age_group_approval_rates = []
    
    # Order age groups properly
    age_group_order = ['18-25', '26-35', '36-45', '46-55', '56-65', '66+', 'Not Specified']
    for group in age_group_order:
        if group in age_group_stats and age_group_stats[group]['total_applications'] > 0:
            age_group_labels.append(group)
            age_group_applications.append(age_group_stats[group]['total_applications'])
            age_group_approved.append(age_group_stats[group]['approved_applications'])
            age_group_approval_rates.append(float(age_group_stats[group]['approval_rate']))
    
    # Prepare chart data for gender
    gender_labels = []
    gender_applications = []
    gender_approved = []
    gender_approval_rates = []
    
    gender_order = ['Male', 'Female', 'Other', 'Not Specified']
    for category in gender_order:
        if category in gender_stats and gender_stats[category]['total_applications'] > 0:
            gender_labels.append(category)
            gender_applications.append(gender_stats[category]['total_applications'])
            gender_approved.append(gender_stats[category]['approved_applications'])
            gender_approval_rates.append(float(gender_stats[category]['approval_rate']))
    
    # Handle export requests
    if export_format == 'pdf':
        return export_age_gender_analytics_pdf(
            request, age_group_stats, gender_stats, 
            total_applications, approved_applications, overall_approval_rate
        )
    elif export_format == 'excel':
        return export_age_gender_analytics_excel(
            age_group_stats, gender_stats,
            total_applications, approved_applications, overall_approval_rate
        )
    
    context = {
        'age_group_stats': age_group_stats,
        'gender_stats': gender_stats,
        'total_applications': total_applications,
        'approved_applications': approved_applications,
        'overall_approval_rate': overall_approval_rate,
        'age_group_labels': age_group_labels,
        'age_group_applications': age_group_applications,
        'age_group_approved': age_group_approved,
        'age_group_approval_rates': age_group_approval_rates,
        'gender_labels': gender_labels,
        'gender_applications': gender_applications,
        'gender_approved': gender_approved,
        'gender_approval_rates': gender_approval_rates,
        'age_group_order': age_group_order,
        'gender_order': gender_order,
    }
    
    return render(request, 'reports/age_gender_analytics_report.html', context)


def export_age_gender_analytics_pdf(request, age_group_stats, gender_stats, 
                                     total_applications, approved_applications, overall_approval_rate):
    """
    Generate PDF export for age & gender analytics report.
    
    Validates: Requirements 2.8, 7.1, 7.2, 7.4, 7.5, 7.6, 7.7, 7.8
    """
    from reportlab.lib.pagesizes import letter, A4
    from reportlab.lib import colors
    from reportlab.lib.units import inch
    from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, PageBreak
    from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
    from reportlab.lib.enums import TA_CENTER, TA_LEFT, TA_RIGHT
    from io import BytesIO
    
    # Create PDF buffer
    buffer = BytesIO()
    doc = SimpleDocTemplate(buffer, pagesize=A4, topMargin=0.5*inch, bottomMargin=0.5*inch)
    elements = []
    styles = getSampleStyleSheet()
    
    # Title style
    title_style = ParagraphStyle(
        'CustomTitle',
        parent=styles['Heading1'],
        fontSize=18,
        textColor=colors.HexColor('#1e40af'),
        spaceAfter=12,
        alignment=TA_CENTER
    )
    
    # Add title
    elements.append(Paragraph('Age & Gender Analytics Report', title_style))
    elements.append(Spacer(1, 0.2*inch))
    
    # Add metadata
    metadata_style = ParagraphStyle(
        'Metadata',
        parent=styles['Normal'],
        fontSize=9,
        textColor=colors.HexColor('#6b7280'),
        alignment=TA_CENTER
    )
    elements.append(Paragraph(f'Generated: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}', metadata_style))
    elements.append(Spacer(1, 0.3*inch))
    
    # Overall summary
    summary_data = [
        ['Overall Summary', ''],
        ['Total Applications', f'{total_applications:,}'],
        ['Approved Applications', f'{approved_applications:,}'],
        ['Overall Approval Rate', f'{overall_approval_rate}%'],
    ]
    
    summary_table = Table(summary_data, colWidths=[3*inch, 2*inch])
    summary_table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#3b82f6')),
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
        ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
        ('FONTSIZE', (0, 0), (-1, 0), 12),
        ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
        ('GRID', (0, 0), (-1, -1), 1, colors.grey),
    ]))
    elements.append(summary_table)
    elements.append(Spacer(1, 0.4*inch))
    
    # Age Group Analysis
    elements.append(Paragraph('Analysis by Age Group', styles['Heading2']))
    elements.append(Spacer(1, 0.2*inch))
    
    age_headers = ['Age Group', 'Total Apps', 'Approved', 'Approval Rate', 
                   'Avg Loan Amount', 'On-Time', 'Late', 'On-Time Rate']
    age_data = [age_headers]
    
    age_group_order = ['18-25', '26-35', '36-45', '46-55', '56-65', '66+', 'Not Specified']
    for group in age_group_order:
        if group in age_group_stats:
            stats = age_group_stats[group]
            age_data.append([
                group,
                f"{stats['total_applications']:,}",
                f"{stats['approved_applications']:,}",
                f"{stats['approval_rate']}%",
                f"KES {stats['average_loan_amount']:,.2f}",
                f"{stats['on_time_payments']:,}",
                f"{stats['late_payments']:,}",
                f"{stats['on_time_rate']}%",
            ])
    
    age_table = Table(age_data, colWidths=[0.9*inch, 0.8*inch, 0.8*inch, 0.9*inch, 
                                           1.2*inch, 0.7*inch, 0.7*inch, 0.9*inch])
    age_table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1e40af')),
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
        ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
        ('FONTSIZE', (0, 0), (-1, 0), 9),
        ('FONTSIZE', (0, 1), (-1, -1), 8),
        ('BOTTOMPADDING', (0, 0), (-1, 0), 8),
        ('GRID', (0, 0), (-1, -1), 0.5, colors.grey),
        ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f3f4f6')]),
    ]))
    elements.append(age_table)
    elements.append(Spacer(1, 0.4*inch))
    
    # Gender Analysis
    elements.append(Paragraph('Analysis by Gender', styles['Heading2']))
    elements.append(Spacer(1, 0.2*inch))
    
    gender_headers = ['Gender', 'Total Apps', 'Approved', 'Approval Rate', 
                      'Avg Loan Amount', 'On-Time', 'Late', 'On-Time Rate']
    gender_data = [gender_headers]
    
    gender_order = ['Male', 'Female', 'Other', 'Not Specified']
    for category in gender_order:
        if category in gender_stats:
            stats = gender_stats[category]
            gender_data.append([
                category,
                f"{stats['total_applications']:,}",
                f"{stats['approved_applications']:,}",
                f"{stats['approval_rate']}%",
                f"KES {stats['average_loan_amount']:,.2f}",
                f"{stats['on_time_payments']:,}",
                f"{stats['late_payments']:,}",
                f"{stats['on_time_rate']}%",
            ])
    
    gender_table = Table(gender_data, colWidths=[1.2*inch, 0.8*inch, 0.8*inch, 0.9*inch, 
                                                 1.2*inch, 0.7*inch, 0.7*inch, 0.9*inch])
    gender_table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1e40af')),
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
        ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
        ('FONTSIZE', (0, 0), (-1, 0), 9),
        ('FONTSIZE', (0, 1), (-1, -1), 8),
        ('BOTTOMPADDING', (0, 0), (-1, 0), 8),
        ('GRID', (0, 0), (-1, -1), 0.5, colors.grey),
        ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f3f4f6')]),
    ]))
    elements.append(gender_table)
    
    # Build PDF
    doc.build(elements)
    
    # Create response
    buffer.seek(0)
    response = HttpResponse(buffer.getvalue(), content_type='application/pdf')
    response['Content-Disposition'] = f'attachment; filename="age_gender_analytics_{datetime.now().strftime("%Y%m%d_%H%M%S")}.pdf"'
    
    return response



def export_age_gender_analytics_excel(age_group_stats, gender_stats,
                                       total_applications, approved_applications, overall_approval_rate):
    """
    Generate Excel export for age & gender analytics report.
    
    Validates: Requirements 2.9, 7.3, 7.10, 7.11
    """
    from openpyxl import Workbook
    from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
    from openpyxl.utils import get_column_letter
    
    # Create workbook with multiple sheets
    wb = Workbook()
    
    # Sheet 1: Summary
    ws_summary = wb.active
    ws_summary.title = 'Summary'
    
    # Title
    ws_summary.merge_cells('A1:D1')
    ws_summary['A1'] = 'Age & Gender Analytics Report'
    ws_summary['A1'].font = Font(size=16, bold=True, color='1e40af')
    ws_summary['A1'].alignment = Alignment(horizontal='center')
    
    # Metadata
    ws_summary.merge_cells('A2:D2')
    ws_summary['A2'] = f"Generated: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
    ws_summary['A2'].font = Font(size=10, color='6b7280')
    ws_summary['A2'].alignment = Alignment(horizontal='center')
    
    # Overall summary
    ws_summary['A4'] = 'Overall Summary'
    ws_summary['A4'].font = Font(size=12, bold=True)
    
    summary_data = [
        ['Metric', 'Value'],
        ['Total Applications', total_applications],
        ['Approved Applications', approved_applications],
        ['Overall Approval Rate', f'{overall_approval_rate}%'],
    ]
    
    for row_idx, (label, value) in enumerate(summary_data, start=5):
        ws_summary.cell(row=row_idx, column=1, value=label)
        ws_summary.cell(row=row_idx, column=2, value=value)
        if row_idx == 5:  # Header row
            ws_summary.cell(row=row_idx, column=1).font = Font(bold=True, color='ffffff')
            ws_summary.cell(row=row_idx, column=2).font = Font(bold=True, color='ffffff')
            ws_summary.cell(row=row_idx, column=1).fill = PatternFill(start_color='3b82f6', end_color='3b82f6', fill_type='solid')
            ws_summary.cell(row=row_idx, column=2).fill = PatternFill(start_color='3b82f6', end_color='3b82f6', fill_type='solid')
    
    ws_summary.column_dimensions['A'].width = 25
    ws_summary.column_dimensions['B'].width = 20
    
    # Sheet 2: Age Group Analysis
    ws_age = wb.create_sheet('Age Group Analysis')
    
    # Title
    ws_age.merge_cells('A1:H1')
    ws_age['A1'] = 'Analysis by Age Group'
    ws_age['A1'].font = Font(size=14, bold=True, color='1e40af')
    ws_age['A1'].alignment = Alignment(horizontal='center')
    
    # Headers
    age_headers = ['Age Group', 'Total Applications', 'Approved Applications', 'Approval Rate (%)', 
                   'Avg Loan Amount', 'On-Time Payments', 'Late Payments', 'On-Time Rate (%)']
    
    for col, header in enumerate(age_headers, start=1):
        cell = ws_age.cell(row=3, column=col)
        cell.value = header
        cell.font = Font(bold=True, color='ffffff')
        cell.fill = PatternFill(start_color='1e40af', end_color='1e40af', fill_type='solid')
        cell.alignment = Alignment(horizontal='center', wrap_text=True)
    
    # Data rows
    age_group_order = ['18-25', '26-35', '36-45', '46-55', '56-65', '66+', 'Not Specified']
    row_idx = 4
    for group in age_group_order:
        if group in age_group_stats:
            stats = age_group_stats[group]
            ws_age.cell(row=row_idx, column=1, value=group)
            ws_age.cell(row=row_idx, column=2, value=stats['total_applications'])
            ws_age.cell(row=row_idx, column=3, value=stats['approved_applications'])
            ws_age.cell(row=row_idx, column=4, value=float(stats['approval_rate']))
            ws_age.cell(row=row_idx, column=5, value=float(stats['average_loan_amount'])).number_format = '#,##0.00'
            ws_age.cell(row=row_idx, column=6, value=stats['on_time_payments'])
            ws_age.cell(row=row_idx, column=7, value=stats['late_payments'])
            ws_age.cell(row=row_idx, column=8, value=float(stats['on_time_rate']))
            row_idx += 1
    
    # Adjust column widths
    for col in range(1, 9):
        ws_age.column_dimensions[get_column_letter(col)].width = 18
    
    # Sheet 3: Gender Analysis
    ws_gender = wb.create_sheet('Gender Analysis')
    
    # Title
    ws_gender.merge_cells('A1:H1')
    ws_gender['A1'] = 'Analysis by Gender'
    ws_gender['A1'].font = Font(size=14, bold=True, color='1e40af')
    ws_gender['A1'].alignment = Alignment(horizontal='center')
    
    # Headers
    gender_headers = ['Gender', 'Total Applications', 'Approved Applications', 'Approval Rate (%)', 
                      'Avg Loan Amount', 'On-Time Payments', 'Late Payments', 'On-Time Rate (%)']
    
    for col, header in enumerate(gender_headers, start=1):
        cell = ws_gender.cell(row=3, column=col)
        cell.value = header
        cell.font = Font(bold=True, color='ffffff')
        cell.fill = PatternFill(start_color='1e40af', end_color='1e40af', fill_type='solid')
        cell.alignment = Alignment(horizontal='center', wrap_text=True)
    
    # Data rows
    gender_order = ['Male', 'Female', 'Other', 'Not Specified']
    row_idx = 4
    for category in gender_order:
        if category in gender_stats:
            stats = gender_stats[category]
            ws_gender.cell(row=row_idx, column=1, value=category)
            ws_gender.cell(row=row_idx, column=2, value=stats['total_applications'])
            ws_gender.cell(row=row_idx, column=3, value=stats['approved_applications'])
            ws_gender.cell(row=row_idx, column=4, value=float(stats['approval_rate']))
            ws_gender.cell(row=row_idx, column=5, value=float(stats['average_loan_amount'])).number_format = '#,##0.00'
            ws_gender.cell(row=row_idx, column=6, value=stats['on_time_payments'])
            ws_gender.cell(row=row_idx, column=7, value=stats['late_payments'])
            ws_gender.cell(row=row_idx, column=8, value=float(stats['on_time_rate']))
            row_idx += 1
    
    # Adjust column widths
    for col in range(1, 9):
        ws_gender.column_dimensions[get_column_letter(col)].width = 18
    
    # Create response
    response = HttpResponse(
        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )
    response['Content-Disposition'] = f'attachment; filename="age_gender_analytics_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx"'
    
    wb.save(response)
    return response