"""
Export service for generating PDF and Excel reports.
"""
from io import BytesIO
from datetime import datetime
from decimal import Decimal
from typing import Dict, Optional

from reportlab.lib import colors
from reportlab.lib.pagesizes import A4, landscape
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.units import inch
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, PageBreak
from reportlab.lib.enums import TA_CENTER, TA_RIGHT, TA_LEFT

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

from django.conf import settings


class ExportService:
    """Service for exporting financial reports to PDF and Excel formats."""
    
    def __init__(self):
        self.institution_name = getattr(settings, 'INSTITUTION_NAME', 'Microfinance Institution')
    
    def _format_currency(self, amount: Decimal) -> str:
        """Format decimal amount as currency with thousands separator."""
        if amount is None:
            return "0.00"
        # Handle nested dictionaries or lists by extracting the total
        if isinstance(amount, dict):
            amount = amount.get('total', 0)
        elif isinstance(amount, (list, tuple)):
            # If it's a list, sum all values
            amount = sum(float(x) if not isinstance(x, dict) else float(x.get('total', 0)) for x in amount)
        # Convert to float for formatting
        try:
            return "{:,.2f}".format(float(amount))
        except (ValueError, TypeError):
            return "0.00"
    
    def _create_pdf_header(self, canvas, doc, title: str, date_range: str = None):
        """Create PDF header with institution name and report title."""
        canvas.saveState()
        
        # Institution name
        canvas.setFont('Helvetica-Bold', 14)
        canvas.drawCentredString(doc.width / 2 + doc.leftMargin, 
                                doc.height + doc.topMargin + 30, 
                                self.institution_name)
        
        # Report title
        canvas.setFont('Helvetica-Bold', 12)
        canvas.drawCentredString(doc.width / 2 + doc.leftMargin,
                                doc.height + doc.topMargin + 15,
                                title)
        
        # Date range if provided
        if date_range:
            canvas.setFont('Helvetica', 10)
            canvas.drawCentredString(doc.width / 2 + doc.leftMargin,
                                    doc.height + doc.topMargin,
                                    date_range)
        
        canvas.restoreState()
    
    def _create_pdf_footer(self, canvas, doc, generated_by: str, page_num: int, total_pages: int):
        """Create PDF footer with generation info and page numbers."""
        canvas.saveState()
        
        # Generated by info
        canvas.setFont('Helvetica', 8)
        canvas.drawString(doc.leftMargin, 0.5 * inch,
                         f"Generated by {generated_by} on {datetime.now().strftime('%Y-%m-%d %H:%M')}")
        
        # Page number
        canvas.drawRightString(doc.width + doc.leftMargin, 0.5 * inch,
                              f"Page {page_num} of {total_pages}")
        
        canvas.restoreState()
    
    def export_trial_balance_pdf(self, report_data: Dict, generated_by: str) -> BytesIO:
        """
        Export trial balance report to PDF.
        
        Args:
            report_data: Dictionary with report_date, branch, accounts, totals, by_type
            generated_by: Username of person generating report
            
        Returns:
            BytesIO object containing PDF data
        """
        buffer = BytesIO()
        
        # Create PDF document
        doc = SimpleDocTemplate(buffer, pagesize=A4,
                               topMargin=1.5*inch, bottomMargin=1*inch)
        
        story = []
        styles = getSampleStyleSheet()
        
        # Title and date info
        title = "Trial Balance"
        date_str = f"As of {report_data['report_date']}"
        branch_str = f"Branch: {report_data.get('branch', 'All Branches')}"
        
        # Table data
        data = [['Account Code', 'Account Name', 'Debit Balance', 'Credit Balance']]
        
        # Group accounts by type
        current_type = None
        for account in report_data['accounts']:
            # Add type header
            if account['type'] != current_type:
                current_type = account['type']
                data.append([current_type.upper(), '', '', ''])
            
            # Add account row
            data.append([
                account['code'],
                account['name'],
                self._format_currency(account.get('debit_balance', 0)),
                self._format_currency(account.get('credit_balance', 0))
            ])
        
        # Add totals
        data.append(['', 'TOTAL', 
                    self._format_currency(report_data['totals']['total_debits']),
                    self._format_currency(report_data['totals']['total_credits'])])
        
        # Create table
        table = Table(data, colWidths=[1.5*inch, 3*inch, 1.5*inch, 1.5*inch])
        table.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.grey),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('ALIGN', (2, 0), (-1, -1), 'RIGHT'),
            ('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),
            ('FONTNAME', (0, -1), (-1, -1), 'Helvetica-Bold'),
            ('GRID', (0, 0), (-1, -1), 1, colors.black),
        ]))
        
        story.append(Paragraph(branch_str, styles['Normal']))
        story.append(Spacer(1, 12))
        story.append(table)
        
        # Build PDF with header/footer
        def add_page_elements(canvas, doc):
            self._create_pdf_header(canvas, doc, title, date_str)
            self._create_pdf_footer(canvas, doc, generated_by, canvas.getPageNumber(), doc.page)
        
        doc.build(story, onFirstPage=add_page_elements, onLaterPages=add_page_elements)
        
        buffer.seek(0)
        return buffer

    def export_income_statement_pdf(self, report_data: Dict, generated_by: str) -> BytesIO:
        """
        Export income statement to PDF.
        
        Args:
            report_data: Dictionary with period, branch, income, expenses, totals
            generated_by: Username of person generating report
            
        Returns:
            BytesIO object containing PDF data
        """
        buffer = BytesIO()
        doc = SimpleDocTemplate(buffer, pagesize=A4,
                               topMargin=1.5*inch, bottomMargin=1*inch)
        
        story = []
        styles = getSampleStyleSheet()
        
        title = "Income Statement"
        date_str = f"Period: {report_data['period']['start']} to {report_data['period']['end']}"
        branch_str = f"Branch: {report_data.get('branch', 'All Branches')}"
        
        story.append(Paragraph(branch_str, styles['Normal']))
        story.append(Spacer(1, 12))
        
        # Income section
        data = [['INCOME', '']]
        
        for category, amount in report_data.get('income', {}).items():
            # Handle nested dict structure (when groups are used)
            if isinstance(amount, dict):
                amount_value = amount.get('total', 0)
            else:
                amount_value = amount
            data.append([f"  {category}", self._format_currency(amount_value)])
        
        gross_income = report_data.get('totals', {}).get('gross_income', report_data.get('gross_income', 0))
        data.append(['Gross Income', self._format_currency(gross_income)])
        data.append(['', ''])
        
        # Expenses section
        data.append(['EXPENSES', ''])
        
        for category, amount in report_data.get('expenses', {}).items():
            # Handle nested dict structure (when groups are used)
            if isinstance(amount, dict):
                amount_value = amount.get('total', 0)
            else:
                amount_value = amount
            data.append([f"  {category}", self._format_currency(amount_value)])
        
        total_expenses = report_data.get('totals', {}).get('total_expenses', report_data.get('total_expenses', 0))
        data.append(['Total Expenses', self._format_currency(total_expenses)])
        data.append(['', ''])
        
        # Net income
        net_income = report_data.get('totals', {}).get('net_income', report_data.get('net_income', 0))
        data.append(['NET INCOME', self._format_currency(net_income)])
        
        table = Table(data, colWidths=[4*inch, 2*inch])
        table.setStyle(TableStyle([
            ('ALIGN', (0, 0), (0, -1), 'LEFT'),
            ('ALIGN', (1, 0), (1, -1), 'RIGHT'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTNAME', (0, -1), (-1, -1), 'Helvetica-Bold'),
            ('LINEABOVE', (0, -1), (-1, -1), 2, colors.black),
            ('GRID', (0, 0), (-1, -1), 0.5, colors.grey),
        ]))
        
        story.append(table)
        
        def add_page_elements(canvas, doc):
            self._create_pdf_header(canvas, doc, title, date_str)
            self._create_pdf_footer(canvas, doc, generated_by, canvas.getPageNumber(), doc.page)
        
        doc.build(story, onFirstPage=add_page_elements, onLaterPages=add_page_elements)
        
        buffer.seek(0)
        return buffer
    
    def export_balance_sheet_pdf(self, report_data: Dict, generated_by: str) -> BytesIO:
        """
        Export balance sheet to PDF.
        
        Args:
            report_data: Dictionary with as_of_date, branch, assets, liabilities, equity, totals
            generated_by: Username of person generating report
            
        Returns:
            BytesIO object containing PDF data
        """
        buffer = BytesIO()
        doc = SimpleDocTemplate(buffer, pagesize=A4,
                               topMargin=1.5*inch, bottomMargin=1*inch)
        
        story = []
        styles = getSampleStyleSheet()
        
        title = "Balance Sheet"
        date_str = f"As of {report_data['as_of_date']}"
        branch_str = f"Branch: {report_data.get('branch', 'All Branches')}"
        
        story.append(Paragraph(branch_str, styles['Normal']))
        story.append(Spacer(1, 12))
        
        # Assets section
        data = [['ASSETS', '']]
        
        for category, amount in report_data.get('assets', {}).items():
            # Handle nested dict structure (when groups are used)
            if isinstance(amount, dict):
                amount_value = amount.get('total', 0)
            else:
                amount_value = amount
            data.append([f"  {category}", self._format_currency(amount_value)])
        
        total_assets = report_data.get('totals', {}).get('total_assets', report_data.get('total_assets', 0))
        data.append(['Total Assets', self._format_currency(total_assets)])
        data.append(['', ''])
        
        # Liabilities section
        data.append(['LIABILITIES', ''])
        
        for category, amount in report_data.get('liabilities', {}).items():
            # Handle nested dict structure (when groups are used)
            if isinstance(amount, dict):
                amount_value = amount.get('total', 0)
            else:
                amount_value = amount
            data.append([f"  {category}", self._format_currency(amount_value)])
        
        total_liabilities = report_data.get('totals', {}).get('total_liabilities', report_data.get('total_liabilities', 0))
        data.append(['Total Liabilities', self._format_currency(total_liabilities)])
        data.append(['', ''])
        
        # Equity section
        data.append(['EQUITY', ''])
        
        for category, amount in report_data.get('equity', {}).items():
            # Handle nested dict structure (when groups are used)
            if isinstance(amount, dict):
                amount_value = amount.get('total', 0)
            else:
                amount_value = amount
            data.append([f"  {category}", self._format_currency(amount_value)])
        
        total_equity = report_data.get('totals', {}).get('total_equity', report_data.get('total_equity', 0))
        data.append(['Total Equity', self._format_currency(total_equity)])
        data.append(['', ''])
        
        # Total liabilities and equity
        total_liab_equity = total_liabilities + total_equity
        data.append(['TOTAL LIABILITIES & EQUITY', self._format_currency(total_liab_equity)])
        
        table = Table(data, colWidths=[4*inch, 2*inch])
        table.setStyle(TableStyle([
            ('ALIGN', (0, 0), (0, -1), 'LEFT'),
            ('ALIGN', (1, 0), (1, -1), 'RIGHT'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTNAME', (0, -1), (-1, -1), 'Helvetica-Bold'),
            ('LINEABOVE', (0, -1), (-1, -1), 2, colors.black),
            ('GRID', (0, 0), (-1, -1), 0.5, colors.grey),
        ]))
        
        story.append(table)
        
        def add_page_elements(canvas, doc):
            self._create_pdf_header(canvas, doc, title, date_str)
            self._create_pdf_footer(canvas, doc, generated_by, canvas.getPageNumber(), doc.page)
        
        doc.build(story, onFirstPage=add_page_elements, onLaterPages=add_page_elements)
        
        buffer.seek(0)
        return buffer
    
    def export_cash_flow_pdf(self, report_data: Dict, generated_by: str) -> BytesIO:
        """
        Export cash flow statement to PDF.
        
        Args:
            report_data: Dictionary with period, branch, operating, investing, financing activities
            generated_by: Username of person generating report
            
        Returns:
            BytesIO object containing PDF data
        """
        buffer = BytesIO()
        doc = SimpleDocTemplate(buffer, pagesize=A4,
                               topMargin=1.5*inch, bottomMargin=1*inch)
        
        story = []
        styles = getSampleStyleSheet()
        
        title = "Cash Flow Statement"
        date_str = f"Period: {report_data['period']['start']} to {report_data['period']['end']}"
        branch_str = f"Branch: {report_data.get('branch', 'All Branches')}"
        
        story.append(Paragraph(branch_str, styles['Normal']))
        story.append(Spacer(1, 12))
        
        data = []
        
        # Operating activities
        data.append(['CASH FLOWS FROM OPERATING ACTIVITIES', ''])
        data.append(['  Net Income', self._format_currency(report_data.get('net_income', 0))])
        
        for item, amount in report_data.get('operating_adjustments', {}).items():
            data.append([f"    {item}", self._format_currency(amount)])
        
        data.append(['Net Cash from Operating Activities', 
                    self._format_currency(report_data.get('net_operating', 0))])
        data.append(['', ''])
        
        # Investing activities
        data.append(['CASH FLOWS FROM INVESTING ACTIVITIES', ''])
        
        for item, amount in report_data.get('investing', {}).items():
            data.append([f"  {item}", self._format_currency(amount)])
        
        data.append(['Net Cash from Investing Activities', 
                    self._format_currency(report_data.get('net_investing', 0))])
        data.append(['', ''])
        
        # Financing activities
        data.append(['CASH FLOWS FROM FINANCING ACTIVITIES', ''])
        
        for item, amount in report_data.get('financing', {}).items():
            data.append([f"  {item}", self._format_currency(amount)])
        
        data.append(['Net Cash from Financing Activities', 
                    self._format_currency(report_data.get('net_financing', 0))])
        data.append(['', ''])
        
        # Net change
        data.append(['NET CHANGE IN CASH', self._format_currency(report_data.get('net_change', 0))])
        data.append(['Beginning Cash Balance', self._format_currency(report_data.get('beginning_cash', 0))])
        data.append(['Ending Cash Balance', self._format_currency(report_data.get('ending_cash', 0))])
        
        table = Table(data, colWidths=[4*inch, 2*inch])
        table.setStyle(TableStyle([
            ('ALIGN', (0, 0), (0, -1), 'LEFT'),
            ('ALIGN', (1, 0), (1, -1), 'RIGHT'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTNAME', (0, -1), (-1, -1), 'Helvetica-Bold'),
            ('LINEABOVE', (0, -1), (-1, -1), 2, colors.black),
            ('GRID', (0, 0), (-1, -1), 0.5, colors.grey),
        ]))
        
        story.append(table)
        
        def add_page_elements(canvas, doc):
            self._create_pdf_header(canvas, doc, title, date_str)
            self._create_pdf_footer(canvas, doc, generated_by, canvas.getPageNumber(), doc.page)
        
        doc.build(story, onFirstPage=add_page_elements, onLaterPages=add_page_elements)
        
        buffer.seek(0)
        return buffer

    def export_account_analysis_pdf(self, report_data: Dict, generated_by: str) -> BytesIO:
        """
        Export account analysis report to PDF.
        
        Args:
            report_data: Dictionary with account info, opening_balance, transactions, closing_balance
            generated_by: Username of person generating report
            
        Returns:
            BytesIO object containing PDF data
        """
        buffer = BytesIO()
        doc = SimpleDocTemplate(buffer, pagesize=landscape(A4),
                               topMargin=1.5*inch, bottomMargin=1*inch)
        
        story = []
        styles = getSampleStyleSheet()
        
        account_info = report_data.get('account', {})
        title = f"Account Analysis: {account_info.get('code', '')} - {account_info.get('name', '')}"
        date_str = f"Period: {report_data['period']['start']} to {report_data['period']['end']}"
        
        story.append(Spacer(1, 12))
        
        # Opening balance
        story.append(Paragraph(f"Opening Balance: {self._format_currency(report_data.get('opening_balance', 0))}", 
                              styles['Normal']))
        story.append(Spacer(1, 12))
        
        # Transactions table
        data = [['Date', 'Description', 'Reference', 'Debit', 'Credit', 'Balance']]
        
        for txn in report_data.get('transactions', []):
            data.append([
                str(txn.get('date', '')),
                txn.get('description', '')[:40],  # Truncate long descriptions
                txn.get('reference', ''),
                self._format_currency(txn.get('debit', 0)),
                self._format_currency(txn.get('credit', 0)),
                self._format_currency(txn.get('balance', 0))
            ])
        
        # Totals row
        data.append(['', 'TOTALS', '',
                    self._format_currency(report_data.get('total_debits', 0)),
                    self._format_currency(report_data.get('total_credits', 0)),
                    ''])
        
        table = Table(data, colWidths=[1*inch, 3*inch, 1.5*inch, 1.2*inch, 1.2*inch, 1.2*inch])
        table.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.grey),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('ALIGN', (3, 0), (-1, -1), 'RIGHT'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, 0), 9),
            ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
            ('BACKGROUND', (0, -1), (-1, -1), colors.beige),
            ('FONTNAME', (0, -1), (-1, -1), 'Helvetica-Bold'),
            ('GRID', (0, 0), (-1, -1), 1, colors.black),
        ]))
        
        story.append(table)
        story.append(Spacer(1, 12))
        
        # Closing balance
        story.append(Paragraph(f"Closing Balance: {self._format_currency(report_data.get('closing_balance', 0))}", 
                              styles['Heading3']))
        
        def add_page_elements(canvas, doc):
            self._create_pdf_header(canvas, doc, title, date_str)
            self._create_pdf_footer(canvas, doc, generated_by, canvas.getPageNumber(), doc.page)
        
        doc.build(story, onFirstPage=add_page_elements, onLaterPages=add_page_elements)
        
        buffer.seek(0)
        return buffer
    
    # Excel export methods
    
    def _apply_excel_header_style(self, ws, row):
        """Apply header styling to Excel worksheet row."""
        for cell in ws[row]:
            cell.font = Font(bold=True, color="FFFFFF")
            cell.fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
            cell.alignment = Alignment(horizontal="center", vertical="center")
            cell.border = Border(
                left=Side(style='thin'),
                right=Side(style='thin'),
                top=Side(style='thin'),
                bottom=Side(style='thin')
            )
    
    def _format_excel_currency(self, ws, start_row, end_row, columns):
        """Apply currency formatting to specified columns in Excel."""
        for row in range(start_row, end_row + 1):
            for col in columns:
                cell = ws.cell(row=row, column=col)
                cell.number_format = '#,##0.00'
                cell.alignment = Alignment(horizontal="right")
    
    def export_trial_balance_excel(self, report_data: Dict) -> BytesIO:
        """
        Export trial balance report to Excel.
        
        Args:
            report_data: Dictionary with report_date, branch, accounts, totals, by_type
            
        Returns:
            BytesIO object containing Excel data
        """
        wb = Workbook()
        ws = wb.active
        ws.title = "Trial Balance"
        
        # Header
        ws['A1'] = self.institution_name
        ws['A1'].font = Font(bold=True, size=14)
        ws['A2'] = "Trial Balance"
        ws['A2'].font = Font(bold=True, size=12)
        ws['A3'] = f"As of {report_data['report_date']}"
        ws['A4'] = f"Branch: {report_data.get('branch', 'All Branches')}"
        
        # Column headers
        row = 6
        ws.cell(row=row, column=1, value="Account Code")
        ws.cell(row=row, column=2, value="Account Name")
        ws.cell(row=row, column=3, value="Debit Balance")
        ws.cell(row=row, column=4, value="Credit Balance")
        self._apply_excel_header_style(ws, row)
        
        # Data rows
        row += 1
        current_type = None
        
        for account in report_data['accounts']:
            # Add type header
            if account['type'] != current_type:
                current_type = account['type']
                ws.cell(row=row, column=1, value=current_type.upper())
                ws.cell(row=row, column=1).font = Font(bold=True)
                row += 1
            
            ws.cell(row=row, column=1, value=account['code'])
            ws.cell(row=row, column=2, value=account['name'])
            ws.cell(row=row, column=3, value=float(account.get('debit_balance', 0)))
            ws.cell(row=row, column=4, value=float(account.get('credit_balance', 0)))
            row += 1
        
        # Totals row
        ws.cell(row=row, column=2, value="TOTAL")
        ws.cell(row=row, column=2).font = Font(bold=True)
        ws.cell(row=row, column=3, value=float(report_data['totals']['total_debits']))
        ws.cell(row=row, column=4, value=float(report_data['totals']['total_credits']))
        
        for col in range(1, 5):
            ws.cell(row=row, column=col).font = Font(bold=True)
            ws.cell(row=row, column=col).fill = PatternFill(start_color="F0F0F0", end_color="F0F0F0", fill_type="solid")
        
        # Format currency columns
        self._format_excel_currency(ws, 7, row, [3, 4])
        
        # Adjust column widths
        ws.column_dimensions['A'].width = 15
        ws.column_dimensions['B'].width = 35
        ws.column_dimensions['C'].width = 18
        ws.column_dimensions['D'].width = 18
        
        buffer = BytesIO()
        wb.save(buffer)
        buffer.seek(0)
        return buffer
    
    def export_income_statement_excel(self, report_data: Dict) -> BytesIO:
        """
        Export income statement to Excel.
        
        Args:
            report_data: Dictionary with period, branch, income, expenses, totals
            
        Returns:
            BytesIO object containing Excel data
        """
        wb = Workbook()
        ws = wb.active
        ws.title = "Income Statement"
        
        # Header
        ws['A1'] = self.institution_name
        ws['A1'].font = Font(bold=True, size=14)
        ws['A2'] = "Income Statement"
        ws['A2'].font = Font(bold=True, size=12)
        ws['A3'] = f"Period: {report_data['period']['start']} to {report_data['period']['end']}"
        ws['A4'] = f"Branch: {report_data.get('branch', 'All Branches')}"
        
        row = 6
        
        # Income section
        ws.cell(row=row, column=1, value="INCOME")
        ws.cell(row=row, column=1).font = Font(bold=True)
        row += 1
        
        for category, amount in report_data.get('income', {}).items():
            # Handle nested dict structure (when groups are used)
            if isinstance(amount, dict):
                amount_value = float(amount.get('total', 0))
            else:
                amount_value = float(amount)
            ws.cell(row=row, column=1, value=f"  {category}")
            ws.cell(row=row, column=2, value=amount_value)
            row += 1
        
        gross_income = report_data.get('totals', {}).get('gross_income', report_data.get('gross_income', 0))
        ws.cell(row=row, column=1, value="Gross Income")
        ws.cell(row=row, column=1).font = Font(bold=True)
        ws.cell(row=row, column=2, value=float(gross_income))
        ws.cell(row=row, column=2).font = Font(bold=True)
        row += 2
        
        # Expenses section
        ws.cell(row=row, column=1, value="EXPENSES")
        ws.cell(row=row, column=1).font = Font(bold=True)
        row += 1
        
        for category, amount in report_data.get('expenses', {}).items():
            # Handle nested dict structure (when groups are used)
            if isinstance(amount, dict):
                amount_value = float(amount.get('total', 0))
            else:
                amount_value = float(amount)
            ws.cell(row=row, column=1, value=f"  {category}")
            ws.cell(row=row, column=2, value=amount_value)
            row += 1
        
        total_expenses = report_data.get('totals', {}).get('total_expenses', report_data.get('total_expenses', 0))
        ws.cell(row=row, column=1, value="Total Expenses")
        ws.cell(row=row, column=1).font = Font(bold=True)
        ws.cell(row=row, column=2, value=float(total_expenses))
        ws.cell(row=row, column=2).font = Font(bold=True)
        row += 2
        
        # Net income
        net_income = report_data.get('totals', {}).get('net_income', report_data.get('net_income', 0))
        ws.cell(row=row, column=1, value="NET INCOME")
        ws.cell(row=row, column=1).font = Font(bold=True)
        ws.cell(row=row, column=2, value=float(net_income))
        ws.cell(row=row, column=2).font = Font(bold=True)
        
        # Format currency column
        self._format_excel_currency(ws, 6, row, [2])
        
        # Adjust column widths
        ws.column_dimensions['A'].width = 35
        ws.column_dimensions['B'].width = 18
        
        buffer = BytesIO()
        wb.save(buffer)
        buffer.seek(0)
        return buffer
    
    def export_balance_sheet_excel(self, report_data: Dict) -> BytesIO:
        """
        Export balance sheet to Excel.
        
        Args:
            report_data: Dictionary with as_of_date, branch, assets, liabilities, equity, totals
            
        Returns:
            BytesIO object containing Excel data
        """
        wb = Workbook()
        ws = wb.active
        ws.title = "Balance Sheet"
        
        # Header
        ws['A1'] = self.institution_name
        ws['A1'].font = Font(bold=True, size=14)
        ws['A2'] = "Balance Sheet"
        ws['A2'].font = Font(bold=True, size=12)
        ws['A3'] = f"As of {report_data['as_of_date']}"
        ws['A4'] = f"Branch: {report_data.get('branch', 'All Branches')}"
        
        row = 6
        
        # Assets section
        ws.cell(row=row, column=1, value="ASSETS")
        ws.cell(row=row, column=1).font = Font(bold=True)
        row += 1
        
        for category, amount in report_data.get('assets', {}).items():
            # Handle nested dict structure (when groups are used)
            if isinstance(amount, dict):
                amount_value = float(amount.get('total', 0))
            else:
                amount_value = float(amount)
            ws.cell(row=row, column=1, value=f"  {category}")
            ws.cell(row=row, column=2, value=amount_value)
            row += 1
        
        total_assets = report_data.get('totals', {}).get('total_assets', report_data.get('total_assets', 0))
        ws.cell(row=row, column=1, value="Total Assets")
        ws.cell(row=row, column=1).font = Font(bold=True)
        ws.cell(row=row, column=2, value=float(total_assets))
        ws.cell(row=row, column=2).font = Font(bold=True)
        row += 2
        
        # Liabilities section
        ws.cell(row=row, column=1, value="LIABILITIES")
        ws.cell(row=row, column=1).font = Font(bold=True)
        row += 1
        
        for category, amount in report_data.get('liabilities', {}).items():
            # Handle nested dict structure (when groups are used)
            if isinstance(amount, dict):
                amount_value = float(amount.get('total', 0))
            else:
                amount_value = float(amount)
            ws.cell(row=row, column=1, value=f"  {category}")
            ws.cell(row=row, column=2, value=amount_value)
            row += 1
        
        total_liabilities = report_data.get('totals', {}).get('total_liabilities', report_data.get('total_liabilities', 0))
        ws.cell(row=row, column=1, value="Total Liabilities")
        ws.cell(row=row, column=1).font = Font(bold=True)
        ws.cell(row=row, column=2, value=float(total_liabilities))
        ws.cell(row=row, column=2).font = Font(bold=True)
        row += 2
        
        # Equity section
        ws.cell(row=row, column=1, value="EQUITY")
        ws.cell(row=row, column=1).font = Font(bold=True)
        row += 1
        
        for category, amount in report_data.get('equity', {}).items():
            # Handle nested dict structure (when groups are used)
            if isinstance(amount, dict):
                amount_value = float(amount.get('total', 0))
            else:
                amount_value = float(amount)
            ws.cell(row=row, column=1, value=f"  {category}")
            ws.cell(row=row, column=2, value=amount_value)
            row += 1
        
        total_equity = report_data.get('totals', {}).get('total_equity', report_data.get('total_equity', 0))
        ws.cell(row=row, column=1, value="Total Equity")
        ws.cell(row=row, column=1).font = Font(bold=True)
        ws.cell(row=row, column=2, value=float(total_equity))
        ws.cell(row=row, column=2).font = Font(bold=True)
        row += 2
        
        # Total liabilities and equity
        total_liab_equity = float(total_liabilities) + float(total_equity)
        ws.cell(row=row, column=1, value="TOTAL LIABILITIES & EQUITY")
        ws.cell(row=row, column=1).font = Font(bold=True)
        ws.cell(row=row, column=2, value=total_liab_equity)
        ws.cell(row=row, column=2).font = Font(bold=True)
        
        # Format currency column
        self._format_excel_currency(ws, 6, row, [2])
        
        # Adjust column widths
        ws.column_dimensions['A'].width = 35
        ws.column_dimensions['B'].width = 18
        
        buffer = BytesIO()
        wb.save(buffer)
        buffer.seek(0)
        return buffer
    
    def export_cash_flow_excel(self, report_data: Dict) -> BytesIO:
        """
        Export cash flow statement to Excel.
        
        Args:
            report_data: Dictionary with period, branch, operating, investing, financing activities
            
        Returns:
            BytesIO object containing Excel data
        """
        wb = Workbook()
        ws = wb.active
        ws.title = "Cash Flow Statement"
        
        # Header
        ws['A1'] = self.institution_name
        ws['A1'].font = Font(bold=True, size=14)
        ws['A2'] = "Cash Flow Statement"
        ws['A2'].font = Font(bold=True, size=12)
        ws['A3'] = f"Period: {report_data['period']['start']} to {report_data['period']['end']}"
        ws['A4'] = f"Branch: {report_data.get('branch', 'All Branches')}"
        
        row = 6
        
        # Operating activities
        ws.cell(row=row, column=1, value="CASH FLOWS FROM OPERATING ACTIVITIES")
        ws.cell(row=row, column=1).font = Font(bold=True)
        row += 1
        
        ws.cell(row=row, column=1, value="  Net Income")
        ws.cell(row=row, column=2, value=float(report_data.get('net_income', 0)))
        row += 1
        
        for item, amount in report_data.get('operating_adjustments', {}).items():
            ws.cell(row=row, column=1, value=f"    {item}")
            ws.cell(row=row, column=2, value=float(amount))
            row += 1
        
        ws.cell(row=row, column=1, value="Net Cash from Operating Activities")
        ws.cell(row=row, column=1).font = Font(bold=True)
        ws.cell(row=row, column=2, value=float(report_data.get('net_operating', 0)))
        ws.cell(row=row, column=2).font = Font(bold=True)
        row += 2
        
        # Investing activities
        ws.cell(row=row, column=1, value="CASH FLOWS FROM INVESTING ACTIVITIES")
        ws.cell(row=row, column=1).font = Font(bold=True)
        row += 1
        
        for item, amount in report_data.get('investing', {}).items():
            ws.cell(row=row, column=1, value=f"  {item}")
            ws.cell(row=row, column=2, value=float(amount))
            row += 1
        
        ws.cell(row=row, column=1, value="Net Cash from Investing Activities")
        ws.cell(row=row, column=1).font = Font(bold=True)
        ws.cell(row=row, column=2, value=float(report_data.get('net_investing', 0)))
        ws.cell(row=row, column=2).font = Font(bold=True)
        row += 2
        
        # Financing activities
        ws.cell(row=row, column=1, value="CASH FLOWS FROM FINANCING ACTIVITIES")
        ws.cell(row=row, column=1).font = Font(bold=True)
        row += 1
        
        for item, amount in report_data.get('financing', {}).items():
            ws.cell(row=row, column=1, value=f"  {item}")
            ws.cell(row=row, column=2, value=float(amount))
            row += 1
        
        ws.cell(row=row, column=1, value="Net Cash from Financing Activities")
        ws.cell(row=row, column=1).font = Font(bold=True)
        ws.cell(row=row, column=2, value=float(report_data.get('net_financing', 0)))
        ws.cell(row=row, column=2).font = Font(bold=True)
        row += 2
        
        # Net change
        ws.cell(row=row, column=1, value="NET CHANGE IN CASH")
        ws.cell(row=row, column=1).font = Font(bold=True)
        ws.cell(row=row, column=2, value=float(report_data.get('net_change', 0)))
        ws.cell(row=row, column=2).font = Font(bold=True)
        row += 1
        
        ws.cell(row=row, column=1, value="Beginning Cash Balance")
        ws.cell(row=row, column=2, value=float(report_data.get('beginning_cash', 0)))
        row += 1
        
        ws.cell(row=row, column=1, value="Ending Cash Balance")
        ws.cell(row=row, column=1).font = Font(bold=True)
        ws.cell(row=row, column=2, value=float(report_data.get('ending_cash', 0)))
        ws.cell(row=row, column=2).font = Font(bold=True)
        
        # Format currency column
        self._format_excel_currency(ws, 6, row, [2])
        
        # Adjust column widths
        ws.column_dimensions['A'].width = 40
        ws.column_dimensions['B'].width = 18
        
        buffer = BytesIO()
        wb.save(buffer)
        buffer.seek(0)
        return buffer
    
    def export_account_analysis_excel(self, report_data: Dict) -> BytesIO:
        """
        Export account analysis report to Excel.
        
        Args:
            report_data: Dictionary with account info, opening_balance, transactions, closing_balance
            
        Returns:
            BytesIO object containing Excel data
        """
        wb = Workbook()
        ws = wb.active
        ws.title = "Account Analysis"
        
        account_info = report_data.get('account', {})
        
        # Header
        ws['A1'] = self.institution_name
        ws['A1'].font = Font(bold=True, size=14)
        ws['A2'] = f"Account Analysis: {account_info.get('code', '')} - {account_info.get('name', '')}"
        ws['A2'].font = Font(bold=True, size=12)
        ws['A3'] = f"Period: {report_data['period']['start']} to {report_data['period']['end']}"
        ws['A4'] = f"Opening Balance: {self._format_currency(report_data.get('opening_balance', 0))}"
        
        # Column headers
        row = 6
        ws.cell(row=row, column=1, value="Date")
        ws.cell(row=row, column=2, value="Description")
        ws.cell(row=row, column=3, value="Reference")
        ws.cell(row=row, column=4, value="Debit")
        ws.cell(row=row, column=5, value="Credit")
        ws.cell(row=row, column=6, value="Balance")
        self._apply_excel_header_style(ws, row)
        
        # Data rows
        row += 1
        
        for txn in report_data.get('transactions', []):
            ws.cell(row=row, column=1, value=str(txn.get('date', '')))
            ws.cell(row=row, column=2, value=txn.get('description', ''))
            ws.cell(row=row, column=3, value=txn.get('reference', ''))
            ws.cell(row=row, column=4, value=float(txn.get('debit', 0)))
            ws.cell(row=row, column=5, value=float(txn.get('credit', 0)))
            ws.cell(row=row, column=6, value=float(txn.get('balance', 0)))
            row += 1
        
        # Totals row
        ws.cell(row=row, column=2, value="TOTALS")
        ws.cell(row=row, column=2).font = Font(bold=True)
        ws.cell(row=row, column=4, value=float(report_data.get('total_debits', 0)))
        ws.cell(row=row, column=5, value=float(report_data.get('total_credits', 0)))
        
        for col in range(1, 7):
            ws.cell(row=row, column=col).font = Font(bold=True)
            ws.cell(row=row, column=col).fill = PatternFill(start_color="F0F0F0", end_color="F0F0F0", fill_type="solid")
        
        row += 2
        ws.cell(row=row, column=1, value="Closing Balance:")
        ws.cell(row=row, column=1).font = Font(bold=True)
        ws.cell(row=row, column=2, value=self._format_currency(report_data.get('closing_balance', 0)))
        ws.cell(row=row, column=2).font = Font(bold=True)
        
        # Format currency columns
        self._format_excel_currency(ws, 7, row - 2, [4, 5, 6])
        
        # Adjust column widths
        ws.column_dimensions['A'].width = 12
        ws.column_dimensions['B'].width = 35
        ws.column_dimensions['C'].width = 18
        ws.column_dimensions['D'].width = 15
        ws.column_dimensions['E'].width = 15
        ws.column_dimensions['F'].width = 15
        
        buffer = BytesIO()
        wb.save(buffer)
        buffer.seek(0)
        return buffer