from django.http import HttpResponse
from django.utils import timezone
from io import BytesIO
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side, NamedStyle
from openpyxl.utils import get_column_letter
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule
from openpyxl.chart import BarChart, PieChart, LineChart, Reference
from openpyxl.chart.label import DataLabelList
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.worksheet.datavalidation import DataValidation
from reportlab.lib.pagesizes import letter, A4
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, PageBreak
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib import colors
from reportlab.lib.units import inch
import pandas as pd
from datetime import datetime, date
from decimal import Decimal
from typing import Dict, List, Any, Optional, Union
import logging

logger = logging.getLogger(__name__)


class EnhancedExcelExportService:
    """
    Enhanced Excel export service with multi-worksheet support, conditional formatting,
    pivot tables, charts, and advanced styling capabilities
    """
    
    def __init__(self):
        """Initialize the Excel export service"""
        self.workbook = None
        self.styles = self._create_styles()
        
        # Color scheme for consistent branding
        self.colors = {
            'primary': '2C3E50',
            'secondary': '3498DB',
            'success': '27AE60',
            'warning': 'F39C12',
            'danger': 'E74C3C',
            'light_gray': 'F8F9FC',
            'dark_gray': '5A5C69'
        }
    
    def _create_styles(self) -> Dict[str, NamedStyle]:
        """Create named styles for consistent formatting"""
        styles = {}
        
        # Header style
        header_style = NamedStyle(name="header_style")
        header_style.font = Font(bold=True, color="FFFFFF", size=12)
        header_style.fill = PatternFill(start_color="2C3E50", end_color="2C3E50", fill_type="solid")
        header_style.alignment = Alignment(horizontal='center', vertical='center')
        header_style.border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        styles['header'] = header_style
        
        # Data style
        data_style = NamedStyle(name="data_style")
        data_style.font = Font(size=10)
        data_style.alignment = Alignment(horizontal='left', vertical='center')
        data_style.border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        styles['data'] = data_style
        
        # Currency style
        currency_style = NamedStyle(name="currency_style")
        currency_style.font = Font(size=10)
        currency_style.alignment = Alignment(horizontal='right', vertical='center')
        currency_style.number_format = '"KES "#,##0.00'
        currency_style.border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        styles['currency'] = currency_style
        
        # Date style
        date_style = NamedStyle(name="date_style")
        date_style.font = Font(size=10)
        date_style.alignment = Alignment(horizontal='center', vertical='center')
        date_style.number_format = 'YYYY-MM-DD'
        date_style.border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        styles['date'] = date_style
        
        # Title style
        title_style = NamedStyle(name="title_style")
        title_style.font = Font(bold=True, size=16, color="2C3E50")
        title_style.alignment = Alignment(horizontal='center', vertical='center')
        styles['title'] = title_style
        
        # Subtitle style
        subtitle_style = NamedStyle(name="subtitle_style")
        subtitle_style.font = Font(bold=True, size=12, color="3498DB")
        subtitle_style.alignment = Alignment(horizontal='left', vertical='center')
        styles['subtitle'] = subtitle_style
        
        return styles
    
    def create_multi_worksheet_export(self, report_data: Dict[str, Any], 
                                    report_type: str, filters: Dict[str, Any]) -> HttpResponse:
        """
        Create comprehensive Excel export with multiple worksheets
        
        Args:
            report_data: Complete report data
            report_type: Type of report
            filters: Applied filters
            
        Returns:
            HttpResponse: Excel file response
        """
        try:
            self.workbook = Workbook()
            
            # Remove default worksheet
            self.workbook.remove(self.workbook.active)
            
            # Add named styles to workbook
            for style_name, style in self.styles.items():
                if style_name not in [s.name for s in self.workbook.named_styles]:
                    self.workbook.add_named_style(style)
            
            # Create worksheets based on report type
            if report_type == 'loans_in_arrears':
                self._create_arrears_worksheets(report_data, filters)
            elif report_type == 'loans_due':
                self._create_loans_due_worksheets(report_data, filters)
            elif report_type == 'delinquent_loans':
                self._create_delinquent_worksheets(report_data, filters)
            elif report_type == 'processing_fees':
                self._create_processing_fees_worksheets(report_data, filters)
            elif report_type == 'interest_income':
                self._create_interest_worksheets(report_data, filters)
            else:
                self._create_general_worksheets(report_data, filters, report_type)
            
            # Save to BytesIO
            buffer = BytesIO()
            self.workbook.save(buffer)
            buffer.seek(0)
            
            # Create HTTP response
            response = HttpResponse(
                buffer.getvalue(),
                content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )
            filename = f"{report_type}_comprehensive_report_{timezone.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
            response['Content-Disposition'] = f'attachment; filename="{filename}"'
            
            return response
            
        except Exception as e:
            logger.error(f"Error creating multi-worksheet export: {str(e)}")
            return self._create_error_response(str(e))
    
    def _create_arrears_worksheets(self, report_data: Dict[str, Any], filters: Dict[str, Any]):
        """Create worksheets for loans in arrears report"""
        
        # 1. Summary Dashboard
        summary_ws = self.workbook.create_sheet("Summary Dashboard")
        self._create_summary_dashboard(summary_ws, report_data.get('summary', {}), 'Loans in Arrears')
        
        # 2. Detailed Loans Data
        loans_ws = self.workbook.create_sheet("Loans Details")
        self._create_loans_detail_sheet(loans_ws, report_data.get('loans', []), 'arrears')
        
        # 3. Risk Analysis
        risk_ws = self.workbook.create_sheet("Risk Analysis")
        self._create_risk_analysis_sheet(risk_ws, report_data)
        
        # 4. Recovery Tracking
        recovery_ws = self.workbook.create_sheet("Recovery Tracking")
        self._create_recovery_tracking_sheet(recovery_ws, report_data)
        
        # 5. Charts and Visualizations
        charts_ws = self.workbook.create_sheet("Charts")
        self._create_charts_sheet(charts_ws, report_data, 'arrears')
        
        # 6. Pivot Analysis
        pivot_ws = self.workbook.create_sheet("Pivot Analysis")
        self._create_pivot_analysis_sheet(pivot_ws, report_data.get('loans', []))
        
        # 7. Filters Applied
        filters_ws = self.workbook.create_sheet("Filters & Metadata")
        self._create_filters_metadata_sheet(filters_ws, filters, report_data)
    
    def _create_loans_due_worksheets(self, report_data: Dict[str, Any], filters: Dict[str, Any]):
        """Create worksheets for loans due report"""
        
        # Summary Dashboard
        summary_ws = self.workbook.create_sheet("Summary Dashboard")
        self._create_summary_dashboard(summary_ws, report_data.get('summary', {}), 'Loans Due')
        
        # Detailed Loans Data
        loans_ws = self.workbook.create_sheet("Loans Details")
        self._create_loans_detail_sheet(loans_ws, report_data.get('loans', []), 'due')
        
        # Urgency Analysis
        urgency_ws = self.workbook.create_sheet("Urgency Analysis")
        self._create_urgency_analysis_sheet(urgency_ws, report_data)
        
        # Charts
        charts_ws = self.workbook.create_sheet("Charts")
        self._create_charts_sheet(charts_ws, report_data, 'due')
        
        # Filters
        filters_ws = self.workbook.create_sheet("Filters & Metadata")
        self._create_filters_metadata_sheet(filters_ws, filters, report_data)
    
    def _create_delinquent_worksheets(self, report_data: Dict[str, Any], filters: Dict[str, Any]):
        """Create worksheets for delinquent loans report"""
        
        # Summary Dashboard
        summary_ws = self.workbook.create_sheet("Summary Dashboard")
        self._create_summary_dashboard(summary_ws, report_data.get('summary', {}), 'Delinquent Loans')
        
        # Detailed Loans Data
        loans_ws = self.workbook.create_sheet("Loans Details")
        self._create_loans_detail_sheet(loans_ws, report_data.get('loans', []), 'delinquent')
        
        # Aging Analysis
        aging_ws = self.workbook.create_sheet("Aging Analysis")
        self._create_aging_analysis_sheet(aging_ws, report_data)
        
        # Collection Strategy
        collection_ws = self.workbook.create_sheet("Collection Strategy")
        self._create_collection_strategy_sheet(collection_ws, report_data)
        
        # Charts
        charts_ws = self.workbook.create_sheet("Charts")
        self._create_charts_sheet(charts_ws, report_data, 'delinquent')
        
        # Filters
        filters_ws = self.workbook.create_sheet("Filters & Metadata")
        self._create_filters_metadata_sheet(filters_ws, filters, report_data)
    
    def _create_processing_fees_worksheets(self, report_data: Dict[str, Any], filters: Dict[str, Any]):
        """Create worksheets for processing fees report"""
        
        # Summary Dashboard
        summary_ws = self.workbook.create_sheet("Summary Dashboard")
        self._create_summary_dashboard(summary_ws, report_data.get('summary', {}), 'Processing Fees')
        
        # Revenue Analysis
        revenue_ws = self.workbook.create_sheet("Revenue Analysis")
        self._create_revenue_analysis_sheet(revenue_ws, report_data)
        
        # Product Performance
        product_ws = self.workbook.create_sheet("Product Performance")
        self._create_product_performance_sheet(product_ws, report_data)
        
        # Charts
        charts_ws = self.workbook.create_sheet("Charts")
        self._create_charts_sheet(charts_ws, report_data, 'fees')
        
        # Filters
        filters_ws = self.workbook.create_sheet("Filters & Metadata")
        self._create_filters_metadata_sheet(filters_ws, filters, report_data)
    
    def _create_interest_worksheets(self, report_data: Dict[str, Any], filters: Dict[str, Any]):
        """Create worksheets for interest income report"""
        
        # Summary Dashboard
        summary_ws = self.workbook.create_sheet("Summary Dashboard")
        self._create_summary_dashboard(summary_ws, report_data.get('summary', {}), 'Interest Income')
        
        # Income Analysis
        income_ws = self.workbook.create_sheet("Income Analysis")
        self._create_income_analysis_sheet(income_ws, report_data)
        
        # Product Breakdown
        product_ws = self.workbook.create_sheet("Product Breakdown")
        self._create_product_breakdown_sheet(product_ws, report_data)
        
        # Charts
        charts_ws = self.workbook.create_sheet("Charts")
        self._create_charts_sheet(charts_ws, report_data, 'interest')
        
        # Filters
        filters_ws = self.workbook.create_sheet("Filters & Metadata")
        self._create_filters_metadata_sheet(filters_ws, filters, report_data)
    
    def _create_general_worksheets(self, report_data: Dict[str, Any], filters: Dict[str, Any], report_type: str):
        """Create general worksheets for other report types"""
        
        # Summary Dashboard
        summary_ws = self.workbook.create_sheet("Summary Dashboard")
        self._create_summary_dashboard(summary_ws, report_data.get('summary', {}), report_type.replace('_', ' ').title())
        
        # Data Sheet
        data_ws = self.workbook.create_sheet("Data")
        self._create_general_data_sheet(data_ws, report_data, report_type)
        
        # Charts
        charts_ws = self.workbook.create_sheet("Charts")
        self._create_charts_sheet(charts_ws, report_data, 'general')
        
        # Filters
        filters_ws = self.workbook.create_sheet("Filters & Metadata")
        self._create_filters_metadata_sheet(filters_ws, filters, report_data)
    
    def _create_summary_dashboard(self, worksheet, summary_data: Dict[str, Any], report_title: str):
        """Create summary dashboard worksheet with KPIs and key metrics"""
        
        # Title
        worksheet['A1'] = f"{report_title} - Summary Dashboard"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Generation info
        worksheet['A3'] = "Report Generated:"
        worksheet['B3'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        worksheet['A3'].font = Font(bold=True)
        
        # KPI Section
        worksheet['A5'] = "Key Performance Indicators"
        worksheet['A5'].style = 'subtitle_style'
        
        # KPI Headers
        kpi_headers = ['Metric', 'Value', 'Description']
        for col, header in enumerate(kpi_headers, start=1):
            cell = worksheet.cell(row=6, column=col, value=header)
            cell.style = 'header'
        
        # KPI Data
        kpi_data = self._extract_kpi_data(summary_data)
        for row, (metric, value, description) in enumerate(kpi_data, start=7):
            worksheet.cell(row=row, column=1, value=metric).style = 'data'
            
            # Format value based on type
            value_cell = worksheet.cell(row=row, column=2, value=value)
            if isinstance(value, (int, float, Decimal)) and 'amount' in metric.lower():
                value_cell.style = 'currency'
            else:
                value_cell.style = 'data'
            
            worksheet.cell(row=row, column=3, value=description).style = 'data'
        
        # Apply conditional formatting to KPI values
        self._apply_kpi_conditional_formatting(worksheet, len(kpi_data) + 6)
        
        # Auto-adjust column widths
        self._auto_adjust_columns(worksheet)
    
    def _create_loans_detail_sheet(self, worksheet, loans_data: List[Dict[str, Any]], sheet_type: str):
        """Create detailed loans data sheet with advanced formatting"""
        
        if not loans_data:
            worksheet['A1'] = "No loan data available"
            return
        
        # Title
        title = f"Detailed Loans Data ({sheet_type.title()})"
        worksheet['A1'] = title
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:J1')
        
        # Headers based on sheet type
        if sheet_type == 'arrears':
            headers = [
                'Loan Number', 'Borrower Name', 'Borrower Phone', 'Product Type',
                'Repayment Method', 'Arrears Amount', 'Days Overdue', 'Risk Level',
                'Last Payment Date', 'Last Payment Amount'
            ]
        elif sheet_type == 'due':
            headers = [
                'Loan Number', 'Borrower Name', 'Borrower Phone', 'Product Type',
                'Due Date', 'Due Amount', 'Status', 'Days Until Due',
                'Contact Priority', 'Portfolio Manager'
            ]
        elif sheet_type == 'delinquent':
            headers = [
                'Loan Number', 'Borrower Name', 'Borrower Phone', 'Product Type',
                'Days Overdue', 'Overdue Amount', 'Total Outstanding', 'Risk Category',
                'Collection Status', 'Last Contact Date'
            ]
        else:
            headers = list(loans_data[0].keys()) if loans_data else []
        
        # Add headers
        for col, header in enumerate(headers, start=1):
            cell = worksheet.cell(row=3, column=col, value=header)
            cell.style = 'header'
        
        # Add data
        for row_idx, loan in enumerate(loans_data, start=4):
            for col_idx, header in enumerate(headers, start=1):
                value = loan.get(header.lower().replace(' ', '_'), '')
                cell = worksheet.cell(row=row_idx, column=col_idx, value=value)
                
                # Apply appropriate styling
                if 'amount' in header.lower() or 'outstanding' in header.lower():
                    cell.style = 'currency'
                elif 'date' in header.lower():
                    cell.style = 'date'
                else:
                    cell.style = 'data'
        
        # Create Excel table
        table_range = f"A3:{get_column_letter(len(headers))}{len(loans_data) + 3}"
        table = Table(displayName=f"LoansTable_{sheet_type}", ref=table_range)
        
        # Apply table style
        style = TableStyleInfo(
            name="TableStyleMedium9",
            showFirstColumn=False,
            showLastColumn=False,
            showRowStripes=True,
            showColumnStripes=False
        )
        table.tableStyleInfo = style
        worksheet.add_table(table)
        
        # Apply conditional formatting
        self._apply_loans_conditional_formatting(worksheet, len(loans_data) + 3, sheet_type)
        
        # Add data validation
        self._add_data_validation(worksheet, len(loans_data) + 3, headers)
        
        # Auto-adjust columns
        self._auto_adjust_columns(worksheet)
    
    def _create_charts_sheet(self, worksheet, report_data: Dict[str, Any], chart_type: str):
        """Create charts worksheet with multiple visualizations"""
        
        worksheet['A1'] = "Charts and Visualizations"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:H1')
        
        chart_row = 3
        
        if chart_type == 'arrears':
            # Risk distribution pie chart
            chart_row = self._create_risk_distribution_chart(worksheet, report_data, chart_row)
            
            # Amount distribution bar chart
            chart_row = self._create_amount_distribution_chart(worksheet, report_data, chart_row)
            
        elif chart_type == 'due':
            # Urgency distribution chart
            chart_row = self._create_urgency_chart(worksheet, report_data, chart_row)
            
            # Due amounts trend chart
            chart_row = self._create_due_amounts_trend_chart(worksheet, report_data, chart_row)
            
        elif chart_type == 'delinquent':
            # Aging analysis chart
            chart_row = self._create_aging_chart(worksheet, report_data, chart_row)
            
        elif chart_type == 'fees':
            # Revenue trend chart
            chart_row = self._create_revenue_trend_chart(worksheet, report_data, chart_row)
            
        elif chart_type == 'interest':
            # Product performance chart
            chart_row = self._create_product_performance_chart(worksheet, report_data, chart_row)
    
    def _create_risk_distribution_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int:
        """Create risk distribution pie chart"""
        
        # Data for chart
        risk_data = [
            ['Risk Level', 'Count'],
            ['Low Risk', report_data.get('severity_breakdown', {}).get('low_risk_count', 0)],
            ['Medium Risk', report_data.get('severity_breakdown', {}).get('medium_risk_count', 0)],
            ['High Risk', report_data.get('severity_breakdown', {}).get('high_risk_count', 0)],
            ['Critical', report_data.get('severity_breakdown', {}).get('critical_count', 0)]
        ]
        
        # Add data to worksheet
        for row_idx, row_data in enumerate(risk_data, start=start_row):
            for col_idx, value in enumerate(row_data, start=1):
                worksheet.cell(row=row_idx, column=col_idx, value=value)
        
        # Create pie chart
        chart = PieChart()
        chart.title = "Risk Distribution"
        
        # Data references
        data = Reference(worksheet, min_col=2, min_row=start_row+1, max_row=start_row+4)
        labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+4)
        
        chart.add_data(data)
        chart.set_categories(labels)
        
        # Styling
        chart.dataLabels = DataLabelList()
        chart.dataLabels.showPercent = True
        
        # Position chart
        worksheet.add_chart(chart, f"D{start_row}")
        
        return start_row + 20
    
    def _create_amount_distribution_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int:
        """Create amount distribution bar chart"""
        
        # Sample amount ranges data
        amount_ranges = report_data.get('amount_ranges', {
            '0-10K': 5,
            '10K-50K': 15,
            '50K-100K': 8,
            '100K+': 3
        })
        
        # Data for chart
        chart_data = [['Amount Range', 'Count']]
        chart_data.extend(list(amount_ranges.items()))
        
        # Add data to worksheet
        for row_idx, row_data in enumerate(chart_data, start=start_row):
            for col_idx, value in enumerate(row_data, start=1):
                worksheet.cell(row=row_idx, column=col_idx, value=value)
        
        # Create bar chart
        chart = BarChart()
        chart.title = "Amount Distribution"
        chart.x_axis.title = "Amount Range"
        chart.y_axis.title = "Number of Loans"
        
        # Data references
        data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+len(amount_ranges))
        labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+len(amount_ranges))
        
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(labels)
        
        # Position chart
        worksheet.add_chart(chart, f"D{start_row}")
        
        return start_row + 20
    
    def _apply_kpi_conditional_formatting(self, worksheet, end_row: int):
        """Apply conditional formatting to KPI values"""
        
        # Green for positive values, red for negative
        green_rule = CellIsRule(operator='greaterThan', formula=['0'], 
                               fill=PatternFill(start_color='C6EFCE', end_color='C6EFCE'))
        red_rule = CellIsRule(operator='lessThan', formula=['0'], 
                             fill=PatternFill(start_color='FFC7CE', end_color='FFC7CE'))
        
        worksheet.conditional_formatting.add(f'B7:B{end_row}', green_rule)
        worksheet.conditional_formatting.add(f'B7:B{end_row}', red_rule)
    
    def _apply_loans_conditional_formatting(self, worksheet, end_row: int, sheet_type: str):
        """Apply conditional formatting to loans data"""
        
        if sheet_type == 'arrears':
            # Color code risk levels
            high_risk_rule = CellIsRule(operator='equal', formula=['"High Risk"'], 
                                       fill=PatternFill(start_color='FFC7CE', end_color='FFC7CE'))
            medium_risk_rule = CellIsRule(operator='equal', formula=['"Medium Risk"'], 
                                         fill=PatternFill(start_color='FFEB9C', end_color='FFEB9C'))
            low_risk_rule = CellIsRule(operator='equal', formula=['"Low Risk"'], 
                                      fill=PatternFill(start_color='C6EFCE', end_color='C6EFCE'))
            
            # Apply to risk level column (assuming column H)
            worksheet.conditional_formatting.add(f'H4:H{end_row}', high_risk_rule)
            worksheet.conditional_formatting.add(f'H4:H{end_row}', medium_risk_rule)
            worksheet.conditional_formatting.add(f'H4:H{end_row}', low_risk_rule)
            
        elif sheet_type == 'due':
            # Color code urgency
            urgent_rule = CellIsRule(operator='lessThanOrEqual', formula=['0'], 
                                    fill=PatternFill(start_color='FFC7CE', end_color='FFC7CE'))
            soon_rule = CellIsRule(operator='between', formula=['1', '7'], 
                                  fill=PatternFill(start_color='FFEB9C', end_color='FFEB9C'))
            
            # Apply to days until due column
            worksheet.conditional_formatting.add(f'H4:H{end_row}', urgent_rule)
            worksheet.conditional_formatting.add(f'H4:H{end_row}', soon_rule)
    
    def _add_data_validation(self, worksheet, end_row: int, headers: List[str]):
        """Add data validation rules"""
        
        # Add dropdown for risk levels if present
        if 'Risk Level' in headers:
            risk_col = headers.index('Risk Level') + 1
            risk_validation = DataValidation(type="list", 
                                           formula1='"Low Risk,Medium Risk,High Risk,Critical"')
            risk_validation.error = 'Please select a valid risk level'
            risk_validation.errorTitle = 'Invalid Risk Level'
            worksheet.add_data_validation(risk_validation)
            risk_validation.add(f'{get_column_letter(risk_col)}4:{get_column_letter(risk_col)}{end_row}')
    
    def _auto_adjust_columns(self, worksheet):
        """Auto-adjust column widths based on content"""
        for column in worksheet.columns:
            max_length = 0
            column_letter = get_column_letter(column[0].column)
            
            for cell in column:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            
            adjusted_width = min(max_length + 2, 50)
            worksheet.column_dimensions[column_letter].width = adjusted_width
    
    def _extract_kpi_data(self, summary_data: Dict[str, Any]) -> List[tuple]:
        """Extract KPI data for dashboard"""
        kpis = []
        
        if 'total_loans' in summary_data:
            kpis.append(('Total Loans', summary_data['total_loans'], 'Total number of loans in the report'))
        
        if 'total_amount' in summary_data:
            kpis.append(('Total Amount', float(summary_data['total_amount']), 'Total monetary value'))
        
        if 'average_amount' in summary_data:
            kpis.append(('Average Amount', float(summary_data['average_amount']), 'Average loan amount'))
        
        if 'collection_rate' in summary_data:
            kpis.append(('Collection Rate', f"{summary_data['collection_rate']:.1f}%", 'Overall collection efficiency'))
        
        if 'recovery_rate' in summary_data:
            kpis.append(('Recovery Rate', f"{summary_data['recovery_rate']:.1f}%", 'Recovery success rate'))
        
        return kpis
    
    def _create_risk_analysis_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create risk analysis worksheet"""
        worksheet['A1'] = "Risk Analysis"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Risk distribution summary
        worksheet['A3'] = "Risk Distribution Summary"
        worksheet['A3'].style = 'subtitle_style'
        
        severity_breakdown = report_data.get('severity_breakdown', {})
        risk_data = [
            ['Risk Level', 'Count', 'Percentage', 'Total Amount', 'Average Amount'],
            ['Low Risk', severity_breakdown.get('low_risk_count', 0), 
             f"{severity_breakdown.get('low_risk_percentage', 0):.1f}%",
             severity_breakdown.get('low_risk_amount', 0),
             severity_breakdown.get('low_risk_avg', 0)],
            ['Medium Risk', severity_breakdown.get('medium_risk_count', 0),
             f"{severity_breakdown.get('medium_risk_percentage', 0):.1f}%",
             severity_breakdown.get('medium_risk_amount', 0),
             severity_breakdown.get('medium_risk_avg', 0)],
            ['High Risk', severity_breakdown.get('high_risk_count', 0),
             f"{severity_breakdown.get('high_risk_percentage', 0):.1f}%",
             severity_breakdown.get('high_risk_amount', 0),
             severity_breakdown.get('high_risk_avg', 0)],
            ['Critical', severity_breakdown.get('critical_count', 0),
             f"{severity_breakdown.get('critical_percentage', 0):.1f}%",
             severity_breakdown.get('critical_amount', 0),
             severity_breakdown.get('critical_avg', 0)]
        ]
        
        # Add risk data to worksheet
        for row_idx, row_data in enumerate(risk_data, start=4):
            for col_idx, value in enumerate(row_data, start=1):
                cell = worksheet.cell(row=row_idx, column=col_idx, value=value)
                if row_idx == 4:  # Header row
                    cell.style = 'header'
                elif col_idx in [4, 5]:  # Amount columns
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        # Auto-adjust columns
        self._auto_adjust_columns(worksheet)
    
    def _create_recovery_tracking_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create recovery tracking worksheet"""
        worksheet['A1'] = "Recovery Tracking"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Recovery metrics
        worksheet['A3'] = "Recovery Metrics"
        worksheet['A3'].style = 'subtitle_style'
        
        summary = report_data.get('summary', {})
        recovery_data = [
            ['Metric', 'Value', 'Target', 'Status'],
            ['Recovery Rate', f"{summary.get('recovery_rate', 0):.1f}%", "85%", 
             "Good" if summary.get('recovery_rate', 0) >= 85 else "Needs Improvement"],
            ['Average Recovery Time', f"{summary.get('avg_recovery_days', 0)} days", "30 days",
             "Good" if summary.get('avg_recovery_days', 0) <= 30 else "Needs Improvement"],
            ['Total Recovered', summary.get('total_recovered', 0), summary.get('recovery_target', 0),
             "On Track" if summary.get('total_recovered', 0) >= summary.get('recovery_target', 0) * 0.8 else "Behind"],
        ]
        
        # Add recovery data
        for row_idx, row_data in enumerate(recovery_data, start=4):
            for col_idx, value in enumerate(row_data, start=1):
                cell = worksheet.cell(row=row_idx, column=col_idx, value=value)
                if row_idx == 4:  # Header row
                    cell.style = 'header'
                elif col_idx == 2 and isinstance(value, (int, float)):  # Amount columns
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_urgency_analysis_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create urgency analysis worksheet"""
        worksheet['A1'] = "Urgency Analysis"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Urgency breakdown
        worksheet['A3'] = "Urgency Breakdown"
        worksheet['A3'].style = 'subtitle_style'
        
        urgency_data = [
            ['Urgency Level', 'Count', 'Total Amount', 'Action Required'],
            ['Overdue', report_data.get('overdue_count', 0), 
             report_data.get('overdue_amount', 0), 'Immediate Contact'],
            ['Due Today', report_data.get('due_today_count', 0),
             report_data.get('due_today_amount', 0), 'Contact Today'],
            ['Due This Week', report_data.get('due_week_count', 0),
             report_data.get('due_week_amount', 0), 'Schedule Contact'],
            ['Due Next Week', report_data.get('due_next_week_count', 0),
             report_data.get('due_next_week_amount', 0), 'Monitor']
        ]
        
        # Add urgency data
        for row_idx, row_data in enumerate(urgency_data, start=4):
            for col_idx, value in enumerate(row_data, start=1):
                cell = worksheet.cell(row=row_idx, column=col_idx, value=value)
                if row_idx == 4:  # Header row
                    cell.style = 'header'
                elif col_idx == 3:  # Amount column
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_aging_analysis_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create aging analysis worksheet"""
        worksheet['A1'] = "Aging Analysis"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Aging buckets
        worksheet['A3'] = "Aging Buckets"
        worksheet['A3'].style = 'subtitle_style'
        
        aging_data = [
            ['Days Overdue', 'Count', 'Total Amount', 'Percentage'],
            ['1-30 days', report_data.get('aging_1_30_count', 0),
             report_data.get('aging_1_30_amount', 0),
             f"{report_data.get('aging_1_30_percentage', 0):.1f}%"],
            ['31-60 days', report_data.get('aging_31_60_count', 0),
             report_data.get('aging_31_60_amount', 0),
             f"{report_data.get('aging_31_60_percentage', 0):.1f}%"],
            ['61-90 days', report_data.get('aging_61_90_count', 0),
             report_data.get('aging_61_90_amount', 0),
             f"{report_data.get('aging_61_90_percentage', 0):.1f}%"],
            ['90+ days', report_data.get('aging_90_plus_count', 0),
             report_data.get('aging_90_plus_amount', 0),
             f"{report_data.get('aging_90_plus_percentage', 0):.1f}%"]
        ]
        
        # Add aging data
        for row_idx, row_data in enumerate(aging_data, start=4):
            for col_idx, value in enumerate(row_data, start=1):
                cell = worksheet.cell(row=row_idx, column=col_idx, value=value)
                if row_idx == 4:  # Header row
                    cell.style = 'header'
                elif col_idx == 3:  # Amount column
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_collection_strategy_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create collection strategy worksheet"""
        worksheet['A1'] = "Collection Strategy"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Collection recommendations
        worksheet['A3'] = "Collection Recommendations"
        worksheet['A3'].style = 'subtitle_style'
        
        strategy_data = [
            ['Risk Level', 'Recommended Action', 'Timeline', 'Priority'],
            ['Low Risk', 'Friendly reminder call', '1-2 days', 'Low'],
            ['Medium Risk', 'Formal notice + call', '1 day', 'Medium'],
            ['High Risk', 'Field visit + payment plan', 'Immediate', 'High'],
            ['Critical', 'Legal action consideration', 'Immediate', 'Critical']
        ]
        
        # Add strategy data
        for row_idx, row_data in enumerate(strategy_data, start=4):
            for col_idx, value in enumerate(row_data, start=1):
                cell = worksheet.cell(row=row_idx, column=col_idx, value=value)
                if row_idx == 4:  # Header row
                    cell.style = 'header'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_revenue_analysis_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create revenue analysis worksheet"""
        worksheet['A1'] = "Revenue Analysis"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Revenue metrics
        worksheet['A3'] = "Revenue Metrics"
        worksheet['A3'].style = 'subtitle_style'
        
        summary = report_data.get('summary', {})
        revenue_data = [
            ['Metric', 'Current Period', 'Previous Period', 'Change'],
            ['Total Processing Fees', summary.get('total_fees', 0),
             summary.get('previous_total_fees', 0),
             f"{summary.get('fees_change_percentage', 0):.1f}%"],
            ['Average Fee per Loan', summary.get('avg_fee', 0),
             summary.get('previous_avg_fee', 0),
             f"{summary.get('avg_fee_change_percentage', 0):.1f}%"],
            ['Fee Collection Rate', f"{summary.get('fee_collection_rate', 0):.1f}%",
             f"{summary.get('previous_fee_collection_rate', 0):.1f}%",
             f"{summary.get('collection_rate_change', 0):.1f}%"]
        ]
        
        # Add revenue data
        for row_idx, row_data in enumerate(revenue_data, start=4):
            for col_idx, value in enumerate(row_data, start=1):
                cell = worksheet.cell(row=row_idx, column=col_idx, value=value)
                if row_idx == 4:  # Header row
                    cell.style = 'header'
                elif col_idx in [2, 3] and isinstance(value, (int, float)):  # Amount columns
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_product_performance_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create product performance worksheet"""
        worksheet['A1'] = "Product Performance"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Product breakdown
        worksheet['A3'] = "Product Performance Breakdown"
        worksheet['A3'].style = 'subtitle_style'
        
        products = report_data.get('product_breakdown', {})
        product_data = [['Product Type', 'Count', 'Total Amount', 'Average Amount', 'Performance']]
        
        for product_name, product_info in products.items():
            product_data.append([
                product_name,
                product_info.get('count', 0),
                product_info.get('total_amount', 0),
                product_info.get('average_amount', 0),
                product_info.get('performance_rating', 'N/A')
            ])
        
        # Add product data
        for row_idx, row_data in enumerate(product_data, start=4):
            for col_idx, value in enumerate(row_data, start=1):
                cell = worksheet.cell(row=row_idx, column=col_idx, value=value)
                if row_idx == 4:  # Header row
                    cell.style = 'header'
                elif col_idx in [3, 4] and isinstance(value, (int, float)):  # Amount columns
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_income_analysis_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create income analysis worksheet"""
        worksheet['A1'] = "Income Analysis"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Income metrics
        worksheet['A3'] = "Interest Income Metrics"
        worksheet['A3'].style = 'subtitle_style'
        
        summary = report_data.get('summary', {})
        income_data = [
            ['Metric', 'Amount', 'Target', 'Achievement'],
            ['Total Interest Income', summary.get('total_interest', 0),
             summary.get('interest_target', 0),
             f"{summary.get('interest_achievement_rate', 0):.1f}%"],
            ['Average Interest Rate', f"{summary.get('avg_interest_rate', 0):.2f}%",
             f"{summary.get('target_interest_rate', 0):.2f}%",
             "On Target" if summary.get('avg_interest_rate', 0) >= summary.get('target_interest_rate', 0) else "Below Target"],
            ['Interest Collection Rate', f"{summary.get('interest_collection_rate', 0):.1f}%",
             "95%", "Good" if summary.get('interest_collection_rate', 0) >= 95 else "Needs Improvement"]
        ]
        
        # Add income data
        for row_idx, row_data in enumerate(income_data, start=4):
            for col_idx, value in enumerate(row_data, start=1):
                cell = worksheet.cell(row=row_idx, column=col_idx, value=value)
                if row_idx == 4:  # Header row
                    cell.style = 'header'
                elif col_idx == 2 and isinstance(value, (int, float)):  # Amount columns
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_product_breakdown_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create product breakdown worksheet"""
        worksheet['A1'] = "Product Breakdown"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Product interest breakdown
        worksheet['A3'] = "Interest Income by Product"
        worksheet['A3'].style = 'subtitle_style'
        
        products = report_data.get('product_breakdown', {})
        breakdown_data = [['Product Type', 'Interest Income', 'Loan Count', 'Average Interest', 'Contribution %']]
        
        total_interest = sum(p.get('interest_income', 0) for p in products.values())
        
        for product_name, product_info in products.items():
            interest_income = product_info.get('interest_income', 0)
            contribution = (interest_income / total_interest * 100) if total_interest > 0 else 0
            
            breakdown_data.append([
                product_name,
                interest_income,
                product_info.get('loan_count', 0),
                product_info.get('average_interest', 0),
                f"{contribution:.1f}%"
            ])
        
        # Add breakdown data
        for row_idx, row_data in enumerate(breakdown_data, start=4):
            for col_idx, value in enumerate(row_data, start=1):
                cell = worksheet.cell(row=row_idx, column=col_idx, value=value)
                if row_idx == 4:  # Header row
                    cell.style = 'header'
                elif col_idx in [2, 4] and isinstance(value, (int, float)):  # Amount columns
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_general_data_sheet(self, worksheet, report_data: Dict[str, Any], report_type: str):
        """Create general data worksheet"""
        worksheet['A1'] = f"{report_type.replace('_', ' ').title()} Data"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Add summary if available
        if 'summary' in report_data:
            worksheet['A3'] = "Summary"
            worksheet['A3'].style = 'subtitle_style'
            
            summary = report_data['summary']
            summary_data = [['Metric', 'Value']]
            
            for key, value in summary.items():
                if isinstance(value, (int, float)):
                    if 'amount' in key.lower():
                        summary_data.append([key.replace('_', ' ').title(), value])
                    else:
                        summary_data.append([key.replace('_', ' ').title(), value])
                else:
                    summary_data.append([key.replace('_', ' ').title(), str(value)])
            
            # Add summary data
            for row_idx, row_data in enumerate(summary_data, start=4):
                for col_idx, value in enumerate(row_data, start=1):
                    cell = worksheet.cell(row=row_idx, column=col_idx, value=value)
                    if row_idx == 4:  # Header row
                        cell.style = 'header'
                    elif col_idx == 2 and isinstance(value, (int, float)) and 'amount' in summary_data[row_idx-4][0].lower():
                        cell.style = 'currency'
                    else:
                        cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_pivot_analysis_sheet(self, worksheet, loans_data: List[Dict[str, Any]]):
        """Create pivot analysis worksheet"""
        worksheet['A1'] = "Pivot Analysis"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        if not loans_data:
            worksheet['A3'] = "No data available for pivot analysis"
            return
        
        # Create pivot-like analysis
        worksheet['A3'] = "Analysis by Risk Level"
        worksheet['A3'].style = 'subtitle_style'
        
        # Group data by risk level
        risk_groups = {}
        for loan in loans_data:
            risk_level = loan.get('risk_level', 'Unknown')
            if risk_level not in risk_groups:
                risk_groups[risk_level] = {'count': 0, 'total_amount': 0}
            
            risk_groups[risk_level]['count'] += 1
            amount = loan.get('arrears_amount', 0) or loan.get('due_amount', 0) or 0
            if isinstance(amount, (int, float)):
                risk_groups[risk_level]['total_amount'] += amount
        
        # Create pivot table
        pivot_data = [['Risk Level', 'Count', 'Total Amount', 'Average Amount']]
        for risk_level, data in risk_groups.items():
            avg_amount = data['total_amount'] / data['count'] if data['count'] > 0 else 0
            pivot_data.append([
                risk_level,
                data['count'],
                data['total_amount'],
                avg_amount
            ])
        
        # Add pivot data
        for row_idx, row_data in enumerate(pivot_data, start=4):
            for col_idx, value in enumerate(row_data, start=1):
                cell = worksheet.cell(row=row_idx, column=col_idx, value=value)
                if row_idx == 4:  # Header row
                    cell.style = 'header'
                elif col_idx in [3, 4]:  # Amount columns
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_filters_metadata_sheet(self, worksheet, filters: Dict[str, Any], report_data: Dict[str, Any]):
        """Create filters and metadata worksheet"""
        worksheet['A1'] = "Filters & Metadata"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Applied filters
        worksheet['A3'] = "Applied Filters"
        worksheet['A3'].style = 'subtitle_style'
        
        filter_data = [['Filter', 'Value']]
        for filter_name, filter_value in filters.items():
            filter_data.append([filter_name.replace('_', ' ').title(), str(filter_value)])
        
        # Add filter data
        for row_idx, row_data in enumerate(filter_data, start=4):
            for col_idx, value in enumerate(row_data, start=1):
                cell = worksheet.cell(row=row_idx, column=col_idx, value=value)
                if row_idx == 4:  # Header row
                    cell.style = 'header'
                else:
                    cell.style = 'data'
        
        # Report metadata
        metadata_start_row = len(filter_data) + 6
        worksheet[f'A{metadata_start_row}'] = "Report Metadata"
        worksheet[f'A{metadata_start_row}'].style = 'subtitle_style'
        
        metadata = [
            ['Generated On', datetime.now().strftime('%Y-%m-%d %H:%M:%S')],
            ['Total Records', len(report_data.get('loans', []))],
            ['Report Version', '2.0'],
            ['System', 'Loan Management System']
        ]
        
        # Add metadata
        for row_idx, row_data in enumerate(metadata, start=metadata_start_row + 1):
            for col_idx, value in enumerate(row_data, start=1):
                cell = worksheet.cell(row=row_idx, column=col_idx, value=value)
                cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_urgency_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int:
        """Create urgency distribution chart"""
        # Data for chart
        urgency_data = [
            ['Urgency Level', 'Count'],
            ['Overdue', report_data.get('overdue_count', 0)],
            ['Due Today', report_data.get('due_today_count', 0)],
            ['Due This Week', report_data.get('due_week_count', 0)],
            ['Due Next Week', report_data.get('due_next_week_count', 0)]
        ]
        
        # Add data to worksheet
        for row_idx, row_data in enumerate(urgency_data, start=start_row):
            for col_idx, value in enumerate(row_data, start=1):
                worksheet.cell(row=row_idx, column=col_idx, value=value)
        
        # Create bar chart
        chart = BarChart()
        chart.title = "Urgency Distribution"
        chart.x_axis.title = "Urgency Level"
        chart.y_axis.title = "Number of Loans"
        
        # Data references
        data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+4)
        labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+4)
        
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(labels)
        
        # Position chart
        worksheet.add_chart(chart, f"D{start_row}")
        
        return start_row + 20
    
    def _create_due_amounts_trend_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int:
        """Create due amounts trend chart"""
        # Sample trend data
        trend_data = report_data.get('trend_data', {
            'Jan': 100000, 'Feb': 120000, 'Mar': 110000, 
            'Apr': 140000, 'May': 130000, 'Jun': 150000
        })
        
        # Data for chart
        chart_data = [['Month', 'Due Amount']]
        chart_data.extend(list(trend_data.items()))
        
        # Add data to worksheet
        for row_idx, row_data in enumerate(chart_data, start=start_row):
            for col_idx, value in enumerate(row_data, start=1):
                worksheet.cell(row=row_idx, column=col_idx, value=value)
        
        # Create line chart
        chart = LineChart()
        chart.title = "Due Amounts Trend"
        chart.x_axis.title = "Month"
        chart.y_axis.title = "Amount (KES)"
        
        # Data references
        data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+len(trend_data))
        labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+len(trend_data))
        
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(labels)
        
        # Position chart
        worksheet.add_chart(chart, f"D{start_row}")
        
        return start_row + 20
    
    def _create_aging_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int:
        """Create aging analysis chart"""
        # Data for chart
        aging_data = [
            ['Days Overdue', 'Count'],
            ['1-30 days', report_data.get('aging_1_30_count', 0)],
            ['31-60 days', report_data.get('aging_31_60_count', 0)],
            ['61-90 days', report_data.get('aging_61_90_count', 0)],
            ['90+ days', report_data.get('aging_90_plus_count', 0)]
        ]
        
        # Add data to worksheet
        for row_idx, row_data in enumerate(aging_data, start=start_row):
            for col_idx, value in enumerate(row_data, start=1):
                worksheet.cell(row=row_idx, column=col_idx, value=value)
        
        # Create bar chart
        chart = BarChart()
        chart.title = "Aging Analysis"
        chart.x_axis.title = "Days Overdue"
        chart.y_axis.title = "Number of Loans"
        
        # Data references
        data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+4)
        labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+4)
        
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(labels)
        
        # Position chart
        worksheet.add_chart(chart, f"D{start_row}")
        
        return start_row + 20
    
    def _create_revenue_trend_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int:
        """Create revenue trend chart"""
        # Sample revenue trend data
        revenue_trend = report_data.get('revenue_trend', {
            'Jan': 50000, 'Feb': 60000, 'Mar': 55000, 
            'Apr': 70000, 'May': 65000, 'Jun': 75000
        })
        
        # Data for chart
        chart_data = [['Month', 'Revenue']]
        chart_data.extend(list(revenue_trend.items()))
        
        # Add data to worksheet
        for row_idx, row_data in enumerate(chart_data, start=start_row):
            for col_idx, value in enumerate(row_data, start=1):
                worksheet.cell(row=row_idx, column=col_idx, value=value)
        
        # Create line chart
        chart = LineChart()
        chart.title = "Revenue Trend"
        chart.x_axis.title = "Month"
        chart.y_axis.title = "Revenue (KES)"
        
        # Data references
        data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+len(revenue_trend))
        labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+len(revenue_trend))
        
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(labels)
        
        # Position chart
        worksheet.add_chart(chart, f"D{start_row}")
        
        return start_row + 20
    
    def _create_product_performance_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int:
        """Create product performance chart"""
        # Product performance data
        products = report_data.get('product_breakdown', {
            'Personal Loans': {'performance_score': 85},
            'Business Loans': {'performance_score': 78},
            'Emergency Loans': {'performance_score': 92},
            'Asset Financing': {'performance_score': 73}
        })
        
        # Data for chart
        chart_data = [['Product', 'Performance Score']]
        for product_name, product_info in products.items():
            chart_data.append([product_name, product_info.get('performance_score', 0)])
        
        # Add data to worksheet
        for row_idx, row_data in enumerate(chart_data, start=start_row):
            for col_idx, value in enumerate(row_data, start=1):
                worksheet.cell(row=row_idx, column=col_idx, value=value)
        
        # Create bar chart
        chart = BarChart()
        chart.title = "Product Performance"
        chart.x_axis.title = "Product Type"
        chart.y_axis.title = "Performance Score"
        
        # Data references
        data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+len(products))
        labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+len(products))
        
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(labels)
        
        # Position chart
        worksheet.add_chart(chart, f"D{start_row}")
        
        return start_row + 20
    
    def _create_error_response(self, error_message: str) -> HttpResponse:
        """Create error response when export fails"""
        wb = Workbook()
        ws = wb.active
        ws.title = "Error"
        ws['A1'] = "Export Error"
        ws['A2'] = f"An error occurred while generating the export: {error_message}"
        
        buffer = BytesIO()
        wb.save(buffer)
        buffer.seek(0)
        
        response = HttpResponse(
            buffer.getvalue(),
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        response['Content-Disposition'] = f'attachment; filename="export_error_{timezone.now().strftime("%Y%m%d_%H%M%S")}.xlsx"'
        
        return response


    def create_advanced_excel_export(self, report_data: Dict[str, Any], 
                                    report_type: str, filters: Dict[str, Any],
                                    include_formulas: bool = True,
                                    include_pivot_tables: bool = True) -> HttpResponse:
        """
        Create advanced Excel export with formulas, pivot tables, and enhanced styling
        
        Args:
            report_data: Complete report data
            report_type: Type of report
            filters: Applied filters
            include_formulas: Whether to include calculated formulas
            include_pivot_tables: Whether to include pivot tables
            
        Returns:
            HttpResponse: Advanced Excel file response
        """
        try:
            self.workbook = Workbook()
            
            # Remove default worksheet
            self.workbook.remove(self.workbook.active)
            
            # Add named styles to workbook
            for style_name, style in self.styles.items():
                if style_name not in [s.name for s in self.workbook.named_styles]:
                    self.workbook.add_named_style(style)
            
            # Create main data worksheet with formulas
            main_ws = self.workbook.create_sheet("Data Analysis")
            self._create_advanced_data_sheet(main_ws, report_data, include_formulas)
            
            # Create pivot table worksheet if requested
            if include_pivot_tables and report_data.get('loans'):
                pivot_ws = self.workbook.create_sheet("Pivot Analysis")
                self._create_advanced_pivot_sheet(pivot_ws, report_data.get('loans', []))
            
            # Create formula calculations worksheet
            if include_formulas:
                calc_ws = self.workbook.create_sheet("Calculations")
                self._create_formula_calculations_sheet(calc_ws, report_data)
            
            # Create dashboard worksheet
            dashboard_ws = self.workbook.create_sheet("Dashboard")
            self._create_dashboard_worksheet(dashboard_ws, report_data)
            
            # Save to BytesIO
            buffer = BytesIO()
            self.workbook.save(buffer)
            buffer.seek(0)
            
            # Create HTTP response
            response = HttpResponse(
                buffer.getvalue(),
                content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )
            filename = f"{report_type}_advanced_export_{timezone.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
            response['Content-Disposition'] = f'attachment; filename="{filename}"'
            
            return response
            
        except Exception as e:
            logger.error(f"Error creating advanced Excel export: {str(e)}")
            return self._create_error_response(str(e))
    
    def _create_advanced_data_sheet(self, worksheet, report_data: Dict[str, Any], include_formulas: bool):
        """Create advanced data sheet with formulas and enhanced styling"""
        worksheet['A1'] = "Advanced Data Analysis"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:H1')
        
        loans_data = report_data.get('loans', [])
        if not loans_data:
            worksheet['A3'] = "No data available"
            return
        
        # Enhanced headers with calculated columns
        headers = [
            'Loan Number', 'Borrower Name', 'Principal Amount', 'Outstanding Amount',
            'Days Overdue', 'Risk Score', 'Collection Priority', 'Projected Recovery'
        ]
        
        # Add headers with enhanced styling
        for col, header in enumerate(headers, start=1):
            cell = worksheet.cell(row=3, column=col, value=header)
            cell.style = 'header'
            cell.fill = PatternFill(start_color=self.colors['primary'], 
                                  end_color=self.colors['primary'], fill_type="solid")
            cell.font = Font(bold=True, color="FFFFFF", size=12)
        
        # Add data with formulas
        for row_idx, loan in enumerate(loans_data, start=4):
            # Basic data columns
            worksheet.cell(row=row_idx, column=1, value=loan.get('loan_number', '')).style = 'data'
            worksheet.cell(row=row_idx, column=2, value=loan.get('borrower_name', '')).style = 'data'
            
            # Amount columns with currency formatting
            principal_cell = worksheet.cell(row=row_idx, column=3, value=loan.get('principal_amount', 0))
            principal_cell.style = 'currency'
            
            outstanding_cell = worksheet.cell(row=row_idx, column=4, value=loan.get('outstanding_amount', 0))
            outstanding_cell.style = 'currency'
            
            # Days overdue
            days_cell = worksheet.cell(row=row_idx, column=5, value=loan.get('days_overdue', 0))
            days_cell.style = 'data'
            
            if include_formulas:
                # Risk score formula (example: based on days overdue and amount)
                risk_formula = f"=MIN(100, MAX(0, E{row_idx}*2 + (D{row_idx}/C{row_idx})*50))"
                risk_cell = worksheet.cell(row=row_idx, column=6, value=risk_formula)
                risk_cell.style = 'data'
                
                # Collection priority formula
                priority_formula = f'=IF(F{row_idx}>80,"High",IF(F{row_idx}>50,"Medium","Low"))'
                priority_cell = worksheet.cell(row=row_idx, column=7, value=priority_formula)
                priority_cell.style = 'data'
                
                # Projected recovery formula
                recovery_formula = f"=D{row_idx}*IF(F{row_idx}>80,0.6,IF(F{row_idx}>50,0.8,0.95))"
                recovery_cell = worksheet.cell(row=row_idx, column=8, value=recovery_formula)
                recovery_cell.style = 'currency'
            else:
                # Static values if formulas not requested
                worksheet.cell(row=row_idx, column=6, value=loan.get('risk_score', 0)).style = 'data'
                worksheet.cell(row=row_idx, column=7, value=loan.get('priority', 'Medium')).style = 'data'
                worksheet.cell(row=row_idx, column=8, value=loan.get('projected_recovery', 0)).style = 'currency'
        
        # Add summary formulas at the bottom
        if include_formulas and loans_data:
            summary_row = len(loans_data) + 5
            
            worksheet.cell(row=summary_row, column=2, value="TOTALS:").font = Font(bold=True)
            worksheet.cell(row=summary_row, column=3, value=f"=SUM(C4:C{len(loans_data)+3})").style = 'currency'
            worksheet.cell(row=summary_row, column=4, value=f"=SUM(D4:D{len(loans_data)+3})").style = 'currency'
            worksheet.cell(row=summary_row, column=5, value=f"=AVERAGE(E4:E{len(loans_data)+3})").style = 'data'
            worksheet.cell(row=summary_row, column=6, value=f"=AVERAGE(F4:F{len(loans_data)+3})").style = 'data'
            worksheet.cell(row=summary_row, column=8, value=f"=SUM(H4:H{len(loans_data)+3})").style = 'currency'
        
        # Apply advanced conditional formatting
        self._apply_advanced_conditional_formatting(worksheet, len(loans_data) + 3)
        
        # Auto-adjust columns
        self._auto_adjust_columns(worksheet)
    
    def _create_advanced_pivot_sheet(self, worksheet, loans_data: List[Dict[str, Any]]):
        """Create advanced pivot table analysis"""
        worksheet['A1'] = "Pivot Table Analysis"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        if not loans_data:
            worksheet['A3'] = "No data available for pivot analysis"
            return
        
        # Create multiple pivot-style analyses
        
        # 1. Risk Level Analysis
        worksheet['A3'] = "Analysis by Risk Level"
        worksheet['A3'].style = 'subtitle_style'
        
        risk_analysis = self._create_risk_level_pivot(loans_data)
        self._add_pivot_table_to_sheet(worksheet, risk_analysis, 4, 1)
        
        # 2. Product Type Analysis
        worksheet['A12'] = "Analysis by Product Type"
        worksheet['A12'].style = 'subtitle_style'
        
        product_analysis = self._create_product_type_pivot(loans_data)
        self._add_pivot_table_to_sheet(worksheet, product_analysis, 13, 1)
        
        # 3. Branch Analysis
        worksheet['A21'] = "Analysis by Branch"
        worksheet['A21'].style = 'subtitle_style'
        
        branch_analysis = self._create_branch_pivot(loans_data)
        self._add_pivot_table_to_sheet(worksheet, branch_analysis, 22, 1)
    
    def _create_risk_level_pivot(self, loans_data: List[Dict[str, Any]]) -> List[List]:
        """Create risk level pivot analysis"""
        risk_groups = {}
        
        for loan in loans_data:
            risk_level = loan.get('risk_level', 'Unknown')
            amount = loan.get('outstanding_amount', 0) or loan.get('arrears_amount', 0) or 0
            
            if risk_level not in risk_groups:
                risk_groups[risk_level] = {
                    'count': 0, 'total_amount': 0, 'amounts': []
                }
            
            risk_groups[risk_level]['count'] += 1
            if isinstance(amount, (int, float)):
                risk_groups[risk_level]['total_amount'] += amount
                risk_groups[risk_level]['amounts'].append(amount)
        
        # Create pivot table data
        pivot_data = [['Risk Level', 'Count', 'Total Amount', 'Average Amount', 'Min Amount', 'Max Amount']]
        
        for risk_level, data in risk_groups.items():
            amounts = data['amounts']
            avg_amount = data['total_amount'] / data['count'] if data['count'] > 0 else 0
            min_amount = min(amounts) if amounts else 0
            max_amount = max(amounts) if amounts else 0
            
            pivot_data.append([
                risk_level, data['count'], data['total_amount'],
                avg_amount, min_amount, max_amount
            ])
        
        return pivot_data
    
    def _create_product_type_pivot(self, loans_data: List[Dict[str, Any]]) -> List[List]:
        """Create product type pivot analysis"""
        product_groups = {}
        
        for loan in loans_data:
            product_type = loan.get('product_type', 'Unknown')
            amount = loan.get('outstanding_amount', 0) or loan.get('principal_amount', 0) or 0
            
            if product_type not in product_groups:
                product_groups[product_type] = {
                    'count': 0, 'total_amount': 0
                }
            
            product_groups[product_type]['count'] += 1
            if isinstance(amount, (int, float)):
                product_groups[product_type]['total_amount'] += amount
        
        # Create pivot table data
        pivot_data = [['Product Type', 'Count', 'Total Amount', 'Average Amount', 'Percentage']]
        
        total_count = sum(data['count'] for data in product_groups.values())
        
        for product_type, data in product_groups.items():
            avg_amount = data['total_amount'] / data['count'] if data['count'] > 0 else 0
            percentage = (data['count'] / total_count * 100) if total_count > 0 else 0
            
            pivot_data.append([
                product_type, data['count'], data['total_amount'],
                avg_amount, f"{percentage:.1f}%"
            ])
        
        return pivot_data
    
    def _create_branch_pivot(self, loans_data: List[Dict[str, Any]]) -> List[List]:
        """Create branch pivot analysis"""
        branch_groups = {}
        
        for loan in loans_data:
            branch = loan.get('branch', 'Main Branch')
            amount = loan.get('outstanding_amount', 0) or loan.get('principal_amount', 0) or 0
            
            if branch not in branch_groups:
                branch_groups[branch] = {
                    'count': 0, 'total_amount': 0
                }
            
            branch_groups[branch]['count'] += 1
            if isinstance(amount, (int, float)):
                branch_groups[branch]['total_amount'] += amount
        
        # Create pivot table data
        pivot_data = [['Branch', 'Count', 'Total Amount', 'Average Amount']]
        
        for branch, data in branch_groups.items():
            avg_amount = data['total_amount'] / data['count'] if data['count'] > 0 else 0
            
            pivot_data.append([
                branch, data['count'], data['total_amount'], avg_amount
            ])
        
        return pivot_data
    
    def _add_pivot_table_to_sheet(self, worksheet, pivot_data: List[List], start_row: int, start_col: int):
        """Add pivot table data to worksheet"""
        for row_idx, row_data in enumerate(pivot_data):
            for col_idx, value in enumerate(row_data):
                cell = worksheet.cell(row=start_row + row_idx, column=start_col + col_idx, value=value)
                
                if row_idx == 0:  # Header row
                    cell.style = 'header'
                elif col_idx in [2, 3, 4, 5] and isinstance(value, (int, float)):  # Amount columns
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
    
    def _create_formula_calculations_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create worksheet with formula calculations"""
        worksheet['A1'] = "Formula Calculations"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Key metrics with formulas
        worksheet['A3'] = "Key Metrics (Formula-Based)"
        worksheet['A3'].style = 'subtitle_style'
        
        # Reference the main data sheet for calculations
        metrics_data = [
            ['Metric', 'Formula', 'Result'],
            ['Total Outstanding', '=SUM(\'Data Analysis\'!D:D)', '=SUM(\'Data Analysis\'!D:D)'],
            ['Average Risk Score', '=AVERAGE(\'Data Analysis\'!F:F)', '=AVERAGE(\'Data Analysis\'!F:F)'],
            ['High Risk Count', '=COUNTIF(\'Data Analysis\'!G:G,"High")', '=COUNTIF(\'Data Analysis\'!G:G,"High")'],
            ['Collection Efficiency', '=SUM(\'Data Analysis\'!H:H)/SUM(\'Data Analysis\'!D:D)', '=SUM(\'Data Analysis\'!H:H)/SUM(\'Data Analysis\'!D:D)'],
            ['Portfolio at Risk', '=SUMIF(\'Data Analysis\'!E:E,">30",\'Data Analysis\'!D:D)/SUM(\'Data Analysis\'!D:D)', '=SUMIF(\'Data Analysis\'!E:E,">30",\'Data Analysis\'!D:D)/SUM(\'Data Analysis\'!D:D)']
        ]
        
        # Add metrics with formulas
        for row_idx, row_data in enumerate(metrics_data, start=4):
            for col_idx, value in enumerate(row_data, start=1):
                cell = worksheet.cell(row=row_idx, column=col_idx, value=value)
                
                if row_idx == 4:  # Header row
                    cell.style = 'header'
                elif col_idx == 3:  # Result column with formulas
                    if row_idx > 4:  # Skip header
                        cell.style = 'currency' if 'SUM' in str(value) else 'data'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_dashboard_worksheet(self, worksheet, report_data: Dict[str, Any]):
        """Create dashboard-style worksheet"""
        worksheet['A1'] = "Executive Dashboard"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:H1')
        
        # KPI Cards
        summary = report_data.get('summary', {})
        
        # Create KPI card layout
        kpi_cards = [
            {'title': 'Total Loans', 'value': summary.get('total_loans', 0), 'color': self.colors['primary']},
            {'title': 'Total Amount', 'value': summary.get('total_amount', 0), 'color': self.colors['secondary']},
            {'title': 'Collection Rate', 'value': f"{summary.get('collection_rate', 0):.1f}%", 'color': self.colors['success']},
            {'title': 'At Risk Amount', 'value': summary.get('at_risk_amount', 0), 'color': self.colors['warning']}
        ]
        
        # Add KPI cards
        for i, kpi in enumerate(kpi_cards):
            col_start = i * 2 + 1
            
            # Title
            title_cell = worksheet.cell(row=3, column=col_start, value=kpi['title'])
            title_cell.font = Font(bold=True, size=12)
            title_cell.fill = PatternFill(start_color=kpi['color'], end_color=kpi['color'], fill_type="solid")
            title_cell.font = Font(bold=True, color="FFFFFF")
            worksheet.merge_cells(f'{get_column_letter(col_start)}3:{get_column_letter(col_start+1)}3')
            
            # Value
            value_cell = worksheet.cell(row=4, column=col_start, value=kpi['value'])
            value_cell.font = Font(bold=True, size=16, color=kpi['color'])
            value_cell.alignment = Alignment(horizontal='center')
            if isinstance(kpi['value'], (int, float)) and 'Amount' in kpi['title']:
                value_cell.style = 'currency'
            worksheet.merge_cells(f'{get_column_letter(col_start)}4:{get_column_letter(col_start+1)}4')
        
        self._auto_adjust_columns(worksheet)
    
    def _apply_advanced_conditional_formatting(self, worksheet, end_row: int):
        """Apply advanced conditional formatting"""
        # Risk score color scale (column F)
        risk_rule = ColorScaleRule(start_type='min', start_color='00FF00',  # Green
                                  mid_type='percentile', mid_value=50, mid_color='FFFF00',  # Yellow
                                  end_type='max', end_color='FF0000')  # Red
        worksheet.conditional_formatting.add(f'F4:F{end_row}', risk_rule)
        
        # Priority highlighting (column G)
        high_priority_rule = CellIsRule(operator='equal', formula=['"High"'], 
                                       fill=PatternFill(start_color='FFC7CE', end_color='FFC7CE'))
        medium_priority_rule = CellIsRule(operator='equal', formula=['"Medium"'], 
                                         fill=PatternFill(start_color='FFEB9C', end_color='FFEB9C'))
        low_priority_rule = CellIsRule(operator='equal', formula=['"Low"'], 
                                      fill=PatternFill(start_color='C6EFCE', end_color='C6EFCE'))
        
        worksheet.conditional_formatting.add(f'G4:G{end_row}', high_priority_rule)
        worksheet.conditional_formatting.add(f'G4:G{end_row}', medium_priority_rule)
        worksheet.conditional_formatting.add(f'G4:G{end_row}', low_priority_rule)
        
        # Days overdue highlighting (column E)
        overdue_rule = CellIsRule(operator='greaterThan', formula=['30'], 
                                 fill=PatternFill(start_color='FFC7CE', end_color='FFC7CE'))
        worksheet.conditional_formatting.add(f'E4:E{end_row}', overdue_rule)


# Enhanced export function using the new service
def export_enhanced_excel(report_data: Dict[str, Any], report_type: str, filters: Dict[str, Any]) -> HttpResponse:
    """
    Enhanced Excel export with multi-worksheet support, conditional formatting, and charts
    
    Args:
        report_data: Complete report data
        report_type: Type of report
        filters: Applied filters
        
    Returns:
        HttpResponse: Excel file response
    """
    service = EnhancedExcelExportService()
    return service.create_multi_worksheet_export(report_data, report_type, filters)


def export_advanced_excel(report_data: Dict[str, Any], report_type: str, filters: Dict[str, Any],
                         include_formulas: bool = True, include_pivot_tables: bool = True) -> HttpResponse:
    """
    Advanced Excel export with formulas, pivot tables, and enhanced styling
    
    Args:
        report_data: Complete report data
        report_type: Type of report
        filters: Applied filters
        include_formulas: Whether to include calculated formulas
        include_pivot_tables: Whether to include pivot tables
        
    Returns:
        HttpResponse: Advanced Excel file response
    """
    service = EnhancedExcelExportService()
    return service.create_advanced_excel_export(report_data, report_type, filters, 
                                              include_formulas, include_pivot_tables)


def export_loans_in_arrears_excel(report_data, filters):
    """Export loans in arrears report to Excel format"""
    
    # Create workbook and worksheet
    wb = Workbook()
    ws = wb.active
    ws.title = "Loans in Arrears Report"
    
    # Define styles
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
    border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    
    # Add title
    ws['A1'] = "Loans in Arrears Report"
    ws['A1'].font = Font(size=16, bold=True)
    ws.merge_cells('A1:J1')
    
    # Add summary information
    summary = report_data.get('summary', {})
    ws['A3'] = "Summary"
    ws['A3'].font = Font(bold=True, size=14)
    
    summary_data = [
        ['Total Loans in Arrears', summary.get('total_loans_in_arrears', 0)],
        ['Total Arrears Amount', f"KSh {summary.get('total_arrears_amount', 0):,.2f}"],
        ['Average Arrears Amount', f"KSh {summary.get('average_arrears_amount', 0):,.2f}"],
        ['Recovery Rate', f"{summary.get('recovery_rate', 0):.1f}%"]
    ]
    
    for i, (label, value) in enumerate(summary_data, start=4):
        ws[f'A{i}'] = label
        ws[f'B{i}'] = value
        ws[f'A{i}'].font = Font(bold=True)
    
    # Add filters information
    ws['A9'] = "Filters Applied"
    ws['A9'].font = Font(bold=True, size=12)
    
    filter_info = []
    if filters.get('arrears_filter'):
        filter_info.append(['Risk Filter', filters['arrears_filter']])
    if filters.get('sort_by'):
        filter_info.append(['Sort By', filters['sort_by']])
    if filters.get('start_date'):
        filter_info.append(['Start Date', filters['start_date']])
    if filters.get('end_date'):
        filter_info.append(['End Date', filters['end_date']])
    
    for i, (label, value) in enumerate(filter_info, start=10):
        ws[f'A{i}'] = label
        ws[f'B{i}'] = value
    
    # Add loans data
    loans = report_data.get('loans', [])
    if loans:
        # Headers
        headers = [
            'Loan Number', 'Borrower Name', 'Borrower Phone', 'Product Type', 
            'Repayment Method', 'Arrears Amount', 'Days Overdue', 
            'Risk Level', 'Last Payment Date', 'Last Payment Amount'
        ]
        
        start_row = 15
        for col, header in enumerate(headers, start=1):
            cell = ws.cell(row=start_row, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = border
            cell.alignment = Alignment(horizontal='center')
        
        # Data rows
        for row_idx, loan in enumerate(loans, start=start_row + 1):
            ws.cell(row=row_idx, column=1, value=loan.get('loan_number', ''))
            ws.cell(row=row_idx, column=2, value=loan.get('borrower_name', ''))
            ws.cell(row=row_idx, column=3, value=loan.get('borrower_phone', ''))
            ws.cell(row=row_idx, column=4, value=loan.get('loan_product', ''))
            ws.cell(row=row_idx, column=5, value=loan.get('repayment_method', '').title())
            ws.cell(row=row_idx, column=6, value=float(loan.get('arrears_amount', 0)))
            ws.cell(row=row_idx, column=7, value=loan.get('days_overdue', 0))
            ws.cell(row=row_idx, column=8, value=loan.get('severity_label', ''))
            ws.cell(row=row_idx, column=9, value=loan.get('last_payment_date', ''))
            ws.cell(row=row_idx, column=10, value=float(loan.get('last_payment_amount', 0)) if loan.get('last_payment_amount') else '')
            
            # Apply borders to all cells
            for col in range(1, 11):
                ws.cell(row=row_idx, column=col).border = border
        
        # Auto-adjust column widths
        for col in range(1, 11):
            column_letter = get_column_letter(col)
            max_length = 0
            for row in range(start_row, start_row + len(loans) + 1):
                cell_value = ws[f'{column_letter}{row}'].value
                if cell_value:
                    max_length = max(max_length, len(str(cell_value)))
            ws.column_dimensions[column_letter].width = min(max_length + 2, 30)
    
    # Save to BytesIO
    buffer = BytesIO()
    wb.save(buffer)
    buffer.seek(0)
    
    # Create HTTP response
    response = HttpResponse(
        buffer.getvalue(),
        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )
    response['Content-Disposition'] = f'attachment; filename="loans_in_arrears_report_{timezone.now().strftime("%Y%m%d_%H%M%S")}.xlsx"'
    
    return response


def export_loans_in_arrears_pdf(report_data, filters):
    """Export loans in arrears report to PDF format with charts"""
    from reportlab.graphics.shapes import Drawing
    from reportlab.graphics.charts.piecharts import Pie
    from reportlab.graphics.charts.barcharts import VerticalBarChart
    from reportlab.graphics.charts.legends import Legend
    from reportlab.lib.colors import HexColor
    
    # Create the HttpResponse object with PDF headers
    response = HttpResponse(content_type='application/pdf')
    response['Content-Disposition'] = f'attachment; filename="loans_in_arrears_report_{timezone.now().strftime("%Y%m%d_%H%M%S")}.pdf"'
    
    # Create the PDF object
    buffer = BytesIO()
    doc = SimpleDocTemplate(buffer, pagesize=A4, rightMargin=72, leftMargin=72, topMargin=72, bottomMargin=18)
    
    # Container for the 'Flowable' objects
    elements = []
    
    # Get styles
    styles = getSampleStyleSheet()
    title_style = ParagraphStyle(
        'CustomTitle',
        parent=styles['Heading1'],
        fontSize=18,
        spaceAfter=30,
        alignment=1  # Center alignment
    )
    
    # Add title
    title = Paragraph("Loans in Arrears Report", title_style)
    elements.append(title)
    elements.append(Spacer(1, 12))
    
    # Add summary information
    summary = report_data.get('summary', {})
    summary_data = [
        ['Metric', 'Value'],
        ['Total Loans in Arrears', str(summary.get('total_loans_in_arrears', 0))],
        ['Total Arrears Amount', f"KSh {summary.get('total_arrears_amount', 0):,.2f}"],
        ['Average Arrears Amount', f"KSh {summary.get('average_arrears_amount', 0):,.2f}"],
        ['Recovery Rate', f"{summary.get('recovery_rate', 0):.1f}%"]
    ]
    
    summary_table = Table(summary_data)
    summary_table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (-1, 0), colors.grey),
        ('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), 14),
        ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
        ('BACKGROUND', (0, 1), (-1, -1), colors.beige),
        ('GRID', (0, 0), (-1, -1), 1, colors.black)
    ]))
    
    elements.append(summary_table)
    elements.append(Spacer(1, 20))
    
    # Add charts section
    elements.append(Paragraph("Risk Distribution Analysis", styles['Heading2']))
    elements.append(Spacer(1, 12))
    
    # Create risk distribution pie chart
    severity_breakdown = report_data.get('severity_breakdown', {})
    if any(severity_breakdown.values()):
        # Create pie chart
        drawing = Drawing(400, 300)
        pie = Pie()
        pie.x = 50
        pie.y = 50
        pie.width = 200
        pie.height = 200
        
        # Prepare data for pie chart
        labels = []
        data = []
        colors_list = [HexColor('#10B981'), HexColor('#F59E0B'), HexColor('#EF4444'), HexColor('#DC2626')]
        
        if severity_breakdown.get('low_risk_count', 0) > 0:
            labels.append('Low Risk')
            data.append(severity_breakdown['low_risk_count'])
        if severity_breakdown.get('medium_risk_count', 0) > 0:
            labels.append('Medium Risk')
            data.append(severity_breakdown['medium_risk_count'])
        if severity_breakdown.get('high_risk_count', 0) > 0:
            labels.append('High Risk')
            data.append(severity_breakdown['high_risk_count'])
        if severity_breakdown.get('critical_count', 0) > 0:
            labels.append('Critical')
            data.append(severity_breakdown['critical_count'])
        
        if data:
            pie.data = data
            pie.labels = labels
            pie.slices.strokeWidth = 0.5
            pie.slices[0].fillColor = colors_list[0] if len(colors_list) > 0 else colors.green
            if len(colors_list) > 1:
                pie.slices[1].fillColor = colors_list[1]
            if len(colors_list) > 2:
                pie.slices[2].fillColor = colors_list[2]
            if len(colors_list) > 3:
                pie.slices[3].fillColor = colors_list[3]
            
            drawing.add(pie)
            elements.append(drawing)
            elements.append(Spacer(1, 20))
    
    # Add arrears trend chart if available
    recovery_trend = report_data.get('recovery_trend', {})
    if recovery_trend and recovery_trend.get('data'):
        try:
            import json
            trend_labels = json.loads(recovery_trend.get('labels', '[]'))
            trend_data = json.loads(recovery_trend.get('data', '[]'))
            
            if trend_labels and trend_data:
                elements.append(Paragraph("Arrears Trend Analysis", styles['Heading2']))
                elements.append(Spacer(1, 12))
                
                # Create bar chart for trend
                drawing = Drawing(400, 250)
                chart = VerticalBarChart()
                chart.x = 50
                chart.y = 50
                chart.width = 300
                chart.height = 150
                chart.data = [trend_data]
                chart.categoryAxis.categoryNames = trend_labels
                chart.valueAxis.valueMin = 0
                chart.bars[0].fillColor = HexColor('#3B82F6')
                chart.bars[0].strokeColor = HexColor('#1E40AF')
                chart.bars[0].strokeWidth = 1
                
                drawing.add(chart)
                elements.append(drawing)
                elements.append(Spacer(1, 20))
        except Exception as e:
            print(f"Error creating trend chart: {e}")
    
    # Add filters information
    if any(filters.values()):
        filter_data = [['Filter', 'Value']]
        if filters.get('arrears_filter'):
            filter_data.append(['Risk Filter', filters['arrears_filter']])
        if filters.get('sort_by'):
            filter_data.append(['Sort By', filters['sort_by']])
        if filters.get('start_date'):
            filter_data.append(['Start Date', filters['start_date']])
        if filters.get('end_date'):
            filter_data.append(['End Date', filters['end_date']])
        
        if len(filter_data) > 1:
            filter_table = Table(filter_data)
            filter_table.setStyle(TableStyle([
                ('BACKGROUND', (0, 0), (-1, 0), colors.lightblue),
                ('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),
                ('BACKGROUND', (0, 1), (-1, -1), colors.lightgrey),
                ('GRID', (0, 0), (-1, -1), 1, colors.black)
            ]))
            
            elements.append(Paragraph("Filters Applied", styles['Heading2']))
            elements.append(filter_table)
            elements.append(Spacer(1, 20))
    
    # Add loans data
    loans = report_data.get('loans', [])
    if loans:
        elements.append(Paragraph("Loans in Arrears Details", styles['Heading2']))
        elements.append(Spacer(1, 12))
        
        # Prepare data for table
        loans_data = [['Loan Number', 'Borrower', 'Product', 'Repayment', 'Amount', 'Days Overdue', 'Risk Level']]
        
        for loan in loans:
            loans_data.append([
                loan.get('loan_number', ''),
                loan.get('borrower_name', ''),
                loan.get('loan_product', ''),
                loan.get('repayment_method', '').title(),
                f"KSh {float(loan.get('arrears_amount', 0)):,.0f}",
                str(loan.get('days_overdue', 0)),
                loan.get('severity_label', '')
            ])
        
        # Create table
        loans_table = Table(loans_data)
        loans_table.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.darkblue),
            ('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), 10),
            ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
            ('BACKGROUND', (0, 1), (-1, -1), colors.beige),
            ('GRID', (0, 0), (-1, -1), 1, colors.black),
            ('FONTSIZE', (0, 1), (-1, -1), 8),
            ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.lightgrey])
        ]))
        
        elements.append(loans_table)
    else:
        elements.append(Paragraph("No loans in arrears found.", styles['Normal']))
    
    # Build PDF
    doc.build(elements)
    
    # Get the value of the BytesIO buffer and write it to the response
    pdf = buffer.getvalue()
    buffer.close()
    response.write(pdf)
    
    return response
    def _create_risk_analysis_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create risk analysis worksheet"""
        worksheet['A1'] = "Risk Analysis"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Risk breakdown data
        risk_breakdown = report_data.get('severity_breakdown', {})
        
        # Risk summary table
        worksheet['A3'] = "Risk Level Summary"
        worksheet['A3'].style = 'subtitle_style'
        
        risk_headers = ['Risk Level', 'Count', 'Percentage', 'Total Amount', 'Average Amount']
        for col, header in enumerate(risk_headers, start=1):
            cell = worksheet.cell(row=4, column=col, value=header)
            cell.style = 'header'
        
        # Risk data
        total_count = sum(risk_breakdown.values()) if risk_breakdown else 1
        risk_levels = ['Low Risk', 'Medium Risk', 'High Risk', 'Critical']
        
        for row, risk_level in enumerate(risk_levels, start=5):
            count = risk_breakdown.get(f"{risk_level.lower().replace(' ', '_')}_count", 0)
            percentage = (count / total_count) * 100 if total_count > 0 else 0
            
            worksheet.cell(row=row, column=1, value=risk_level).style = 'data'
            worksheet.cell(row=row, column=2, value=count).style = 'data'
            worksheet.cell(row=row, column=3, value=f"{percentage:.1f}%").style = 'data'
            worksheet.cell(row=row, column=4, value=count * 50000).style = 'currency'  # Sample calculation
            worksheet.cell(row=row, column=5, value=50000).style = 'currency'  # Sample average
        
        self._auto_adjust_columns(worksheet)
    
    def _create_recovery_tracking_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create recovery tracking worksheet"""
        worksheet['A1'] = "Recovery Tracking"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:H1')
        
        # Recovery metrics
        worksheet['A3'] = "Recovery Metrics"
        worksheet['A3'].style = 'subtitle_style'
        
        recovery_headers = ['Metric', 'Current Month', 'Previous Month', 'Change', 'Target', 'Achievement']
        for col, header in enumerate(recovery_headers, start=1):
            cell = worksheet.cell(row=4, column=col, value=header)
            cell.style = 'header'
        
        # Sample recovery data
        recovery_metrics = [
            ['Recovery Rate', '75%', '70%', '+5%', '80%', '94%'],
            ['Amount Recovered', 'KES 2,500,000', 'KES 2,200,000', '+13.6%', 'KES 3,000,000', '83%'],
            ['Cases Resolved', '45', '38', '+18.4%', '50', '90%'],
            ['Average Recovery Time', '15 days', '18 days', '-16.7%', '12 days', '80%']
        ]
        
        for row, metrics in enumerate(recovery_metrics, start=5):
            for col, value in enumerate(metrics, start=1):
                cell = worksheet.cell(row=row, column=col, value=value)
                if col == 1:
                    cell.style = 'data'
                elif 'KES' in str(value):
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_urgency_analysis_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create urgency analysis worksheet for loans due"""
        worksheet['A1'] = "Urgency Analysis"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        summary = report_data.get('summary', {})
        
        # Urgency breakdown
        worksheet['A3'] = "Due Date Urgency Breakdown"
        worksheet['A3'].style = 'subtitle_style'
        
        urgency_headers = ['Urgency Level', 'Count', 'Amount', 'Percentage', 'Action Required']
        for col, header in enumerate(urgency_headers, start=1):
            cell = worksheet.cell(row=4, column=col, value=header)
            cell.style = 'header'
        
        urgency_data = [
            ['Due Today', summary.get('today_due_count', 0), summary.get('today_due_amount', 0), '25%', 'Immediate Contact'],
            ['Due Tomorrow', summary.get('tomorrow_due_count', 0), summary.get('tomorrow_due_amount', 0), '20%', 'Priority Contact'],
            ['Due This Week', summary.get('week_due_count', 0), summary.get('week_due_amount', 0), '35%', 'Schedule Contact'],
            ['Due Later', summary.get('later_due_count', 0), summary.get('later_due_amount', 0), '20%', 'Monitor']
        ]
        
        for row, data in enumerate(urgency_data, start=5):
            for col, value in enumerate(data, start=1):
                cell = worksheet.cell(row=row, column=col, value=value)
                if col == 3:  # Amount column
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_aging_analysis_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create aging analysis worksheet for delinquent loans"""
        worksheet['A1'] = "Aging Analysis"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Aging buckets
        worksheet['A3'] = "Delinquency Aging Buckets"
        worksheet['A3'].style = 'subtitle_style'
        
        aging_headers = ['Days Overdue', 'Count', 'Amount', 'Percentage', 'Collection Strategy']
        for col, header in enumerate(aging_headers, start=1):
            cell = worksheet.cell(row=4, column=col, value=header)
            cell.style = 'header'
        
        aging_buckets = report_data.get('aging_buckets', {})
        aging_data = [
            ['1-30 days', aging_buckets.get('1-30', 0), aging_buckets.get('1-30', 0) * 25000, '40%', 'Phone calls'],
            ['31-60 days', aging_buckets.get('31-60', 0), aging_buckets.get('31-60', 0) * 35000, '30%', 'Formal notices'],
            ['61-90 days', aging_buckets.get('61-90', 0), aging_buckets.get('61-90', 0) * 45000, '20%', 'Field visits'],
            ['90+ days', aging_buckets.get('90+', 0), aging_buckets.get('90+', 0) * 55000, '10%', 'Legal action']
        ]
        
        for row, data in enumerate(aging_data, start=5):
            for col, value in enumerate(data, start=1):
                cell = worksheet.cell(row=row, column=col, value=value)
                if col == 3:  # Amount column
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_collection_strategy_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create collection strategy worksheet"""
        worksheet['A1'] = "Collection Strategy"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:G1')
        
        # Collection action plan
        worksheet['A3'] = "Collection Action Plan"
        worksheet['A3'].style = 'subtitle_style'
        
        strategy_headers = ['Priority', 'Loan Count', 'Amount', 'Action', 'Timeline', 'Responsible', 'Expected Recovery']
        for col, header in enumerate(strategy_headers, start=1):
            cell = worksheet.cell(row=4, column=col, value=header)
            cell.style = 'header'
        
        strategy_data = [
            ['High', 15, 750000, 'Immediate contact + field visit', '1-3 days', 'Senior Officer', '80%'],
            ['Medium', 25, 1250000, 'Phone calls + SMS reminders', '3-7 days', 'Loan Officer', '70%'],
            ['Low', 35, 875000, 'SMS reminders + letters', '7-14 days', 'Collection Team', '60%'],
            ['Monitor', 20, 500000, 'Regular follow-up', '14-30 days', 'Junior Officer', '50%']
        ]
        
        for row, data in enumerate(strategy_data, start=5):
            for col, value in enumerate(data, start=1):
                cell = worksheet.cell(row=row, column=col, value=value)
                if col == 3:  # Amount column
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_revenue_analysis_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create revenue analysis worksheet for processing fees"""
        worksheet['A1'] = "Revenue Analysis"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Monthly revenue trend
        worksheet['A3'] = "Monthly Revenue Trend"
        worksheet['A3'].style = 'subtitle_style'
        
        revenue_headers = ['Month', 'Revenue', 'Growth %', 'Target', 'Achievement %', 'Variance']
        for col, header in enumerate(revenue_headers, start=1):
            cell = worksheet.cell(row=4, column=col, value=header)
            cell.style = 'header'
        
        # Sample monthly data
        monthly_data = [
            ['Jan 2024', 450000, '5%', 500000, '90%', -50000],
            ['Feb 2024', 520000, '15.6%', 550000, '94.5%', -30000],
            ['Mar 2024', 580000, '11.5%', 600000, '96.7%', -20000],
            ['Apr 2024', 620000, '6.9%', 650000, '95.4%', -30000]
        ]
        
        for row, data in enumerate(monthly_data, start=5):
            for col, value in enumerate(data, start=1):
                cell = worksheet.cell(row=row, column=col, value=value)
                if col in [2, 4, 6]:  # Amount columns
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_product_performance_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create product performance worksheet"""
        worksheet['A1'] = "Product Performance"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Product breakdown
        worksheet['A3'] = "Product Performance Breakdown"
        worksheet['A3'].style = 'subtitle_style'
        
        product_headers = ['Product Type', 'Count', 'Revenue', 'Average Fee', 'Market Share', 'Growth Rate']
        for col, header in enumerate(product_headers, start=1):
            cell = worksheet.cell(row=4, column=col, value=header)
            cell.style = 'header'
        
        product_data = [
            ['Personal Loans', 150, 375000, 2500, '35%', '12%'],
            ['Business Loans', 80, 320000, 4000, '30%', '8%'],
            ['Emergency Loans', 120, 180000, 1500, '20%', '15%'],
            ['Asset Finance', 50, 200000, 4000, '15%', '5%']
        ]
        
        for row, data in enumerate(product_data, start=5):
            for col, value in enumerate(data, start=1):
                cell = worksheet.cell(row=row, column=col, value=value)
                if col in [3, 4]:  # Amount columns
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_income_analysis_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create income analysis worksheet for interest income"""
        worksheet['A1'] = "Interest Income Analysis"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Interest income breakdown
        worksheet['A3'] = "Interest Income Breakdown"
        worksheet['A3'].style = 'subtitle_style'
        
        income_headers = ['Period', 'Interest Income', 'Principal', 'Interest Rate', 'Yield %', 'Performance']
        for col, header in enumerate(income_headers, start=1):
            cell = worksheet.cell(row=4, column=col, value=header)
            cell.style = 'header'
        
        income_data = [
            ['Q1 2024', 1250000, 25000000, '15%', '5.0%', 'Above Target'],
            ['Q2 2024', 1380000, 27000000, '15%', '5.1%', 'Above Target'],
            ['Q3 2024', 1420000, 28500000, '15%', '4.98%', 'On Target'],
            ['Q4 2024', 1500000, 30000000, '15%', '5.0%', 'Above Target']
        ]
        
        for row, data in enumerate(income_data, start=5):
            for col, value in enumerate(data, start=1):
                cell = worksheet.cell(row=row, column=col, value=value)
                if col in [2, 3]:  # Amount columns
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
        
        self._auto_adjust_columns(worksheet)
    
    def _create_product_breakdown_sheet(self, worksheet, report_data: Dict[str, Any]):
        """Create product breakdown worksheet"""
        worksheet['A1'] = "Product Breakdown"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        product_breakdown = report_data.get('product_breakdown', {})
        
        # Product performance table
        worksheet['A3'] = "Product Performance Summary"
        worksheet['A3'].style = 'subtitle_style'
        
        breakdown_headers = ['Product', 'Income', 'Percentage', 'Loans Count', 'Average Income', 'Trend']
        for col, header in enumerate(breakdown_headers, start=1):
            cell = worksheet.cell(row=4, column=col, value=header)
            cell.style = 'header'
        
        total_income = sum(product_breakdown.values()) if product_breakdown else 1
        
        for row, (product, income) in enumerate(product_breakdown.items(), start=5):
            percentage = (income / total_income) * 100 if total_income > 0 else 0
            
            worksheet.cell(row=row, column=1, value=product).style = 'data'
            worksheet.cell(row=row, column=2, value=float(income)).style = 'currency'
            worksheet.cell(row=row, column=3, value=f"{percentage:.1f}%").style = 'data'
            worksheet.cell(row=row, column=4, value=25).style = 'data'  # Sample count
            worksheet.cell(row=row, column=5, value=float(income)/25).style = 'currency'
            worksheet.cell(row=row, column=6, value="↗").style = 'data'  # Sample trend
        
        self._auto_adjust_columns(worksheet)
    
    def _create_general_data_sheet(self, worksheet, report_data: Dict[str, Any], report_type: str):
        """Create general data sheet for other report types"""
        worksheet['A1'] = f"{report_type.replace('_', ' ').title()} Data"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        # Add summary data if available
        summary = report_data.get('summary', {})
        if summary:
            worksheet['A3'] = "Summary Information"
            worksheet['A3'].style = 'subtitle_style'
            
            row = 4
            for key, value in summary.items():
                worksheet.cell(row=row, column=1, value=key.replace('_', ' ').title()).style = 'data'
                cell = worksheet.cell(row=row, column=2, value=value)
                if isinstance(value, (int, float, Decimal)) and 'amount' in key.lower():
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
                row += 1
        
        self._auto_adjust_columns(worksheet)
    
    def _create_pivot_analysis_sheet(self, worksheet, loans_data: List[Dict[str, Any]]):
        """Create pivot analysis worksheet"""
        worksheet['A1'] = "Pivot Analysis"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:F1')
        
        if not loans_data:
            worksheet['A3'] = "No data available for pivot analysis"
            return
        
        # Create summary pivot table
        worksheet['A3'] = "Summary by Product Type"
        worksheet['A3'].style = 'subtitle_style'
        
        pivot_headers = ['Product Type', 'Count', 'Total Amount', 'Average Amount', 'Percentage']
        for col, header in enumerate(pivot_headers, start=1):
            cell = worksheet.cell(row=4, column=col, value=header)
            cell.style = 'header'
        
        # Group data by product type
        product_summary = {}
        total_amount = 0
        
        for loan in loans_data:
            product = loan.get('loan_product', 'Unknown')
            amount = float(loan.get('arrears_amount', 0)) if 'arrears_amount' in loan else float(loan.get('due_amount', 0))
            
            if product not in product_summary:
                product_summary[product] = {'count': 0, 'total_amount': 0}
            
            product_summary[product]['count'] += 1
            product_summary[product]['total_amount'] += amount
            total_amount += amount
        
        # Add pivot data
        row = 5
        for product, data in product_summary.items():
            count = data['count']
            amount = data['total_amount']
            avg_amount = amount / count if count > 0 else 0
            percentage = (amount / total_amount) * 100 if total_amount > 0 else 0
            
            worksheet.cell(row=row, column=1, value=product).style = 'data'
            worksheet.cell(row=row, column=2, value=count).style = 'data'
            worksheet.cell(row=row, column=3, value=amount).style = 'currency'
            worksheet.cell(row=row, column=4, value=avg_amount).style = 'currency'
            worksheet.cell(row=row, column=5, value=f"{percentage:.1f}%").style = 'data'
            row += 1
        
        self._auto_adjust_columns(worksheet)
    
    def _create_filters_metadata_sheet(self, worksheet, filters: Dict[str, Any], report_data: Dict[str, Any]):
        """Create filters and metadata worksheet"""
        worksheet['A1'] = "Filters & Metadata"
        worksheet['A1'].style = 'title_style'
        worksheet.merge_cells('A1:D1')
        
        # Report generation info
        worksheet['A3'] = "Report Generation Information"
        worksheet['A3'].style = 'subtitle_style'
        
        info_data = [
            ['Generated On', datetime.now().strftime('%Y-%m-%d %H:%M:%S')],
            ['Generated By', 'Loan Management System'],
            ['Report Version', '2.0'],
            ['Data Source', 'Production Database']
        ]
        
        for row, (label, value) in enumerate(info_data, start=4):
            worksheet.cell(row=row, column=1, value=label).font = Font(bold=True)
            worksheet.cell(row=row, column=2, value=value).style = 'data'
        
        # Applied filters
        if filters:
            worksheet['A9'] = "Applied Filters"
            worksheet['A9'].style = 'subtitle_style'
            
            row = 10
            for key, value in filters.items():
                if value:  # Only show non-empty filters
                    worksheet.cell(row=row, column=1, value=key.replace('_', ' ').title()).font = Font(bold=True)
                    worksheet.cell(row=row, column=2, value=str(value)).style = 'data'
                    row += 1
        
        # Data summary
        summary = report_data.get('summary', {})
        if summary:
            worksheet['A15'] = "Data Summary"
            worksheet['A15'].style = 'subtitle_style'
            
            row = 16
            for key, value in summary.items():
                worksheet.cell(row=row, column=1, value=key.replace('_', ' ').title()).font = Font(bold=True)
                cell = worksheet.cell(row=row, column=2, value=value)
                if isinstance(value, (int, float, Decimal)) and 'amount' in key.lower():
                    cell.style = 'currency'
                else:
                    cell.style = 'data'
                row += 1
        
        self._auto_adjust_columns(worksheet)
    
    def _create_urgency_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int:
        """Create urgency distribution chart for loans due"""
        summary = report_data.get('summary', {})
        
        # Data for chart
        urgency_data = [
            ['Urgency', 'Count'],
            ['Due Today', summary.get('today_due_count', 0)],
            ['Due Tomorrow', summary.get('tomorrow_due_count', 0)],
            ['Due This Week', summary.get('week_due_count', 0)],
            ['Due Later', summary.get('later_due_count', 0)]
        ]
        
        # Add data to worksheet
        for row_idx, row_data in enumerate(urgency_data, start=start_row):
            for col_idx, value in enumerate(row_data, start=1):
                worksheet.cell(row=row_idx, column=col_idx, value=value)
        
        # Create bar chart
        chart = BarChart()
        chart.title = "Loans Due by Urgency"
        chart.x_axis.title = "Urgency Level"
        chart.y_axis.title = "Number of Loans"
        
        # Data references
        data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+4)
        labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+4)
        
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(labels)
        
        # Position chart
        worksheet.add_chart(chart, f"D{start_row}")
        
        return start_row + 20
    
    def _create_due_amounts_trend_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int:
        """Create due amounts trend chart"""
        # Sample trend data
        trend_data = [
            ['Date', 'Due Amount'],
            ['2024-01-01', 500000],
            ['2024-01-02', 750000],
            ['2024-01-03', 600000],
            ['2024-01-04', 800000],
            ['2024-01-05', 650000]
        ]
        
        # Add data to worksheet
        for row_idx, row_data in enumerate(trend_data, start=start_row):
            for col_idx, value in enumerate(row_data, start=1):
                worksheet.cell(row=row_idx, column=col_idx, value=value)
        
        # Create line chart
        chart = LineChart()
        chart.title = "Due Amounts Trend"
        chart.x_axis.title = "Date"
        chart.y_axis.title = "Amount (KES)"
        
        # Data references
        data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+5)
        labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+5)
        
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(labels)
        
        # Position chart
        worksheet.add_chart(chart, f"D{start_row}")
        
        return start_row + 20
    
    def _create_aging_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int:
        """Create aging analysis chart"""
        aging_buckets = report_data.get('aging_buckets', {
            '1-30 days': 15,
            '31-60 days': 10,
            '61-90 days': 8,
            '90+ days': 5
        })
        
        # Data for chart
        chart_data = [['Age Bucket', 'Count']]
        chart_data.extend(list(aging_buckets.items()))
        
        # Add data to worksheet
        for row_idx, row_data in enumerate(chart_data, start=start_row):
            for col_idx, value in enumerate(row_data, start=1):
                worksheet.cell(row=row_idx, column=col_idx, value=value)
        
        # Create bar chart
        chart = BarChart()
        chart.title = "Delinquency Aging Analysis"
        chart.x_axis.title = "Days Overdue"
        chart.y_axis.title = "Number of Loans"
        
        # Data references
        data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+len(aging_buckets))
        labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+len(aging_buckets))
        
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(labels)
        
        # Position chart
        worksheet.add_chart(chart, f"D{start_row}")
        
        return start_row + 20
    
    def _create_revenue_trend_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int:
        """Create revenue trend chart"""
        # Sample revenue data
        revenue_data = [
            ['Month', 'Revenue'],
            ['Jan', 450000],
            ['Feb', 520000],
            ['Mar', 580000],
            ['Apr', 620000],
            ['May', 680000]
        ]
        
        # Add data to worksheet
        for row_idx, row_data in enumerate(revenue_data, start=start_row):
            for col_idx, value in enumerate(row_data, start=1):
                worksheet.cell(row=row_idx, column=col_idx, value=value)
        
        # Create line chart
        chart = LineChart()
        chart.title = "Revenue Trend"
        chart.x_axis.title = "Month"
        chart.y_axis.title = "Revenue (KES)"
        
        # Data references
        data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+5)
        labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+5)
        
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(labels)
        
        # Position chart
        worksheet.add_chart(chart, f"D{start_row}")
        
        return start_row + 20
    
    def _create_product_performance_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int:
        """Create product performance chart"""
        product_performance = report_data.get('product_performance', {
            'Personal Loans': 375000,
            'Business Loans': 320000,
            'Emergency Loans': 180000,
            'Asset Finance': 200000
        })
        
        # Data for chart
        chart_data = [['Product', 'Income']]
        chart_data.extend(list(product_performance.items()))
        
        # Add data to worksheet
        for row_idx, row_data in enumerate(chart_data, start=start_row):
            for col_idx, value in enumerate(row_data, start=1):
                worksheet.cell(row=row_idx, column=col_idx, value=value)
        
        # Create pie chart
        chart = PieChart()
        chart.title = "Product Performance"
        
        # Data references
        data = Reference(worksheet, min_col=2, min_row=start_row+1, max_row=start_row+len(product_performance))
        labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+len(product_performance))
        
        chart.add_data(data)
        chart.set_categories(labels)
        
        # Styling
        chart.dataLabels = DataLabelList()
        chart.dataLabels.showPercent = True
        
        # Position chart
        worksheet.add_chart(chart, f"D{start_row}")
        
        return start_row + 20