"""
Enhanced Excel Generator with Beautiful Formatting and Charts
Professional Excel reports with charts, conditional formatting, and modern styling
"""

import io
import xlsxwriter
from datetime import datetime
from decimal import Decimal
import logging

logger = logging.getLogger(__name__)

class EnhancedExcelGenerator:
    """Generate beautiful, professional Excel reports with charts and formatting"""
    
    def __init__(self):
        self.colors = {
            'primary': '#3B82F6',
            'secondary': '#10B981',
            'accent': '#F59E0B',
            'danger': '#EF4444',
            'dark': '#1F2937',
            'light_gray': '#F3F4F6',
            'medium_gray': '#9CA3AF',
            'white': '#FFFFFF'
        }
    
    def _create_formats(self, workbook):
        """Create custom formats for professional appearance"""
        formats = {}
        
        # Title format
        formats['title'] = workbook.add_format({
            'font_size': 18,
            'bold': True,
            'font_color': self.colors['primary'],
            'align': 'center',
            'valign': 'vcenter',
            'bg_color': self.colors['light_gray'],
            'border': 1,
            'border_color': self.colors['medium_gray']
        })
        
        # Header format
        formats['header'] = workbook.add_format({
            'font_size': 12,
            'bold': True,
            'font_color': 'white',
            'align': 'center',
            'valign': 'vcenter',
            'bg_color': self.colors['primary'],
            'border': 1,
            'border_color': self.colors['primary']
        })
        
        # Subheader format
        formats['subheader'] = workbook.add_format({
            'font_size': 11,
            'bold': True,
            'font_color': self.colors['dark'],
            'align': 'left',
            'valign': 'vcenter',
            'bg_color': self.colors['light_gray'],
            'border': 1,
            'border_color': self.colors['medium_gray']
        })
        
        # Data formats
        formats['data'] = workbook.add_format({
            'font_size': 10,
            'align': 'left',
            'valign': 'vcenter',
            'border': 1,
            'border_color': self.colors['medium_gray']
        })
        
        formats['data_center'] = workbook.add_format({
            'font_size': 10,
            'align': 'center',
            'valign': 'vcenter',
            'border': 1,
            'border_color': self.colors['medium_gray']
        })
        
        formats['data_right'] = workbook.add_format({
            'font_size': 10,
            'align': 'right',
            'valign': 'vcenter',
            'border': 1,
            'border_color': self.colors['medium_gray']
        })
        
        # Currency format
        formats['currency'] = workbook.add_format({
            'font_size': 10,
            'align': 'right',
            'valign': 'vcenter',
            'num_format': 'KES #,##0.00',
            'border': 1,
            'border_color': self.colors['medium_gray']
        })
        
        # Percentage format
        formats['percentage'] = workbook.add_format({
            'font_size': 10,
            'align': 'right',
            'valign': 'vcenter',
            'num_format': '0.0%',
            'border': 1,
            'border_color': self.colors['medium_gray']
        })
        
        # Date format
        formats['date'] = workbook.add_format({
            'font_size': 10,
            'align': 'center',
            'valign': 'vcenter',
            'num_format': 'dd/mm/yyyy',
            'border': 1,
            'border_color': self.colors['medium_gray']
        })
        
        # Status formats
        formats['status_active'] = workbook.add_format({
            'font_size': 10,
            'align': 'center',
            'valign': 'vcenter',
            'bg_color': self.colors['secondary'],
            'font_color': 'white',
            'border': 1,
            'border_color': self.colors['medium_gray']
        })
        
        formats['status_overdue'] = workbook.add_format({
            'font_size': 10,
            'align': 'center',
            'valign': 'vcenter',
            'bg_color': self.colors['danger'],
            'font_color': 'white',
            'border': 1,
            'border_color': self.colors['medium_gray']
        })
        
        formats['status_completed'] = workbook.add_format({
            'font_size': 10,
            'align': 'center',
            'valign': 'vcenter',
            'bg_color': self.colors['primary'],
            'font_color': 'white',
            'border': 1,
            'border_color': self.colors['medium_gray']
        })
        
        # Summary format
        formats['summary'] = workbook.add_format({
            'font_size': 11,
            'bold': True,
            'font_color': self.colors['dark'],
            'align': 'right',
            'valign': 'vcenter',
            'bg_color': self.colors['accent'],
            'border': 2,
            'border_color': self.colors['dark']
        })
        
        return formats
    
    def _add_chart(self, workbook, worksheet, chart_type, data_range, title, position):
        """Add a chart to the worksheet"""
        try:
            chart = workbook.add_chart({'type': chart_type})
            
            if chart_type == 'pie':
                chart.add_series({
                    'categories': data_range['categories'],
                    'values': data_range['values'],
                    'data_labels': {'percentage': True},
                })
            elif chart_type in ['column', 'bar', 'line']:
                chart.add_series({
                    'categories': data_range['categories'],
                    'values': data_range['values'],
                    'name': title,
                })
            
            chart.set_title({'name': title, 'name_font': {'size': 14, 'bold': True}})
            chart.set_style(10)  # Modern style
            chart.set_size({'width': 480, 'height': 288})
            
            worksheet.insert_chart(position, chart)
            
        except Exception as e:
            logger.error(f"Chart creation failed: {e}")
    
    def generate_dashboard_excel(self, data):
        """Generate comprehensive dashboard Excel report"""
        try:
            buffer = io.BytesIO()
            workbook = xlsxwriter.Workbook(buffer, {'in_memory': True})
            formats = self._create_formats(workbook)
            
            # Summary Sheet
            summary_sheet = workbook.add_worksheet('Dashboard Summary')
            self._create_summary_sheet(summary_sheet, data.get('summary', {}), formats)
            
            # Portfolio Sheet
            if 'portfolio' in data:
                portfolio_sheet = workbook.add_worksheet('Portfolio Analysis')
                self._create_portfolio_sheet(portfolio_sheet, data['portfolio'], formats, workbook)
            
            # Loans Sheet
            if 'loans' in data:
                loans_sheet = workbook.add_worksheet('Loan Details')
                self._create_loans_sheet(loans_sheet, data['loans'], formats)
            
            # Risk Analysis Sheet
            if 'risk' in data:
                risk_sheet = workbook.add_worksheet('Risk Analysis')
                self._create_risk_sheet(risk_sheet, data['risk'], formats, workbook)
            
            # Financial Sheet
            if 'financial' in data:
                financial_sheet = workbook.add_worksheet('Financial Summary')
                self._create_financial_sheet(financial_sheet, data['financial'], formats, workbook)
            
            workbook.close()
            buffer.seek(0)
            return buffer
            
        except Exception as e:
            logger.error(f"Dashboard Excel generation failed: {e}")
            return None
    
    def _create_summary_sheet(self, worksheet, data, formats):
        """Create the summary dashboard sheet"""
        # Title
        worksheet.merge_range('A1:F1', 'DASHBOARD SUMMARY REPORT', formats['title'])
        worksheet.merge_range('A2:F2', f'Generated: {datetime.now().strftime("%B %d, %Y at %I:%M %p")}', formats['data_center'])
        
        # Key Metrics
        row = 4
        worksheet.write(row, 0, 'KEY PERFORMANCE INDICATORS', formats['subheader'])
        row += 2
        
        # Create KPI table
        kpi_headers = ['Metric', 'Value', 'Previous Period', 'Change', 'Status']
        for col, header in enumerate(kpi_headers):
            worksheet.write(row, col, header, formats['header'])
        
        row += 1
        
        # Sample KPIs (replace with actual data)
        kpis = [
            ['Total Loans', data.get('total_loans', 0), 0, 0, 'Active'],
            ['Active Loans', data.get('active_loans', 0), 0, 0, 'Active'],
            ['Total Disbursed', data.get('total_disbursed', 0), 0, 0, 'Active'],
            ['Collection Rate', data.get('collection_rate', 0), 0, 0, 'Active'],
            ['Default Rate', data.get('default_rate', 0), 0, 0, 'Active']
        ]
        
        for kpi in kpis:
            worksheet.write(row, 0, kpi[0], formats['data'])
            
            if 'amount' in kpi[0].lower() or 'disbursed' in kpi[0].lower():
                worksheet.write(row, 1, kpi[1], formats['currency'])
            elif 'rate' in kpi[0].lower():
                worksheet.write(row, 1, kpi[1]/100, formats['percentage'])
            else:
                worksheet.write(row, 1, kpi[1], formats['data_right'])
            
            worksheet.write(row, 2, kpi[2], formats['data_right'])
            worksheet.write(row, 3, kpi[3], formats['data_right'])
            worksheet.write(row, 4, kpi[4], formats['status_active'])
            row += 1
        
        # Set column widths
        worksheet.set_column('A:A', 20)
        worksheet.set_column('B:E', 15)
        worksheet.set_column('F:F', 12)
    
    def _create_portfolio_sheet(self, worksheet, data, formats, workbook):
        """Create portfolio analysis sheet with charts"""
        # Title
        worksheet.merge_range('A1:H1', 'PORTFOLIO ANALYSIS', formats['title'])
        worksheet.merge_range('A2:H2', f'Generated: {datetime.now().strftime("%B %d, %Y")}', formats['data_center'])
        
        # Portfolio Summary
        row = 4
        worksheet.write(row, 0, 'PORTFOLIO SUMMARY', formats['subheader'])
        row += 2
        
        # Summary data
        summary_data = data.get('summary', {})
        summary_items = [
            ['Total Portfolio Value', summary_data.get('total_value', 0)],
            ['Outstanding Amount', summary_data.get('outstanding', 0)],
            ['Collection Rate', summary_data.get('collection_rate', 0)],
            ['Portfolio at Risk', summary_data.get('par', 0)]
        ]
        
        for item in summary_items:
            worksheet.write(row, 0, item[0], formats['data'])
            if 'rate' in item[0].lower() or 'risk' in item[0].lower():
                worksheet.write(row, 1, item[1]/100, formats['percentage'])
            else:
                worksheet.write(row, 1, item[1], formats['currency'])
            row += 1
        
        # Add portfolio distribution chart
        if 'distribution' in data:
            chart_data = data['distribution']
            categories_range = f"'Portfolio Analysis'!A{row+3}:A{row+3+len(chart_data)-1}"
            values_range = f"'Portfolio Analysis'!B{row+3}:B{row+3+len(chart_data)-1}"
            
            # Write chart data
            row += 3
            worksheet.write(row-1, 0, 'Portfolio Distribution', formats['subheader'])
            for i, (category, value) in enumerate(chart_data.items()):
                worksheet.write(row+i, 0, category, formats['data'])
                worksheet.write(row+i, 1, value, formats['currency'])
            
            # Add pie chart
            self._add_chart(workbook, worksheet, 'pie', {
                'categories': categories_range,
                'values': values_range
            }, 'Portfolio Distribution', 'D4')
        
        # Set column widths
        worksheet.set_column('A:A', 25)
        worksheet.set_column('B:H', 15)
    
    def _create_loans_sheet(self, worksheet, data, formats):
        """Create detailed loans sheet"""
        # Title
        worksheet.merge_range('A1:J1', 'LOAN DETAILS', formats['title'])
        worksheet.merge_range('A2:J2', f'Generated: {datetime.now().strftime("%B %d, %Y")}', formats['data_center'])
        
        # Headers
        row = 4
        headers = ['Loan #', 'Borrower', 'Amount', 'Disbursed', 'Due Date', 'Status', 'Paid', 'Balance', 'Days Overdue', 'Risk Level']
        
        for col, header in enumerate(headers):
            worksheet.write(row, col, header, formats['header'])
        
        row += 1
        
        # Loan data
        loans = data.get('details', [])
        for loan in loans:
            worksheet.write(row, 0, loan.get('loan_number', ''), formats['data'])
            worksheet.write(row, 1, loan.get('borrower', ''), formats['data'])
            worksheet.write(row, 2, loan.get('amount', 0), formats['currency'])
            worksheet.write(row, 3, loan.get('disbursed_date', ''), formats['date'])
            worksheet.write(row, 4, loan.get('due_date', ''), formats['date'])
            
            # Status with conditional formatting
            status = loan.get('status', '').lower()
            if status == 'active':
                worksheet.write(row, 5, loan.get('status', ''), formats['status_active'])
            elif status == 'overdue':
                worksheet.write(row, 5, loan.get('status', ''), formats['status_overdue'])
            else:
                worksheet.write(row, 5, loan.get('status', ''), formats['status_completed'])
            
            worksheet.write(row, 6, loan.get('amount_paid', 0), formats['currency'])
            worksheet.write(row, 7, loan.get('balance', 0), formats['currency'])
            worksheet.write(row, 8, loan.get('days_overdue', 0), formats['data_center'])
            worksheet.write(row, 9, loan.get('risk_level', ''), formats['data_center'])
            row += 1
        
        # Add totals row
        if loans:
            worksheet.write(row, 0, 'TOTALS', formats['summary'])
            worksheet.write(row, 1, '', formats['summary'])
            worksheet.write(row, 2, f'=SUM(C5:C{row})', formats['summary'])
            worksheet.write(row, 3, '', formats['summary'])
            worksheet.write(row, 4, '', formats['summary'])
            worksheet.write(row, 5, '', formats['summary'])
            worksheet.write(row, 6, f'=SUM(G5:G{row})', formats['summary'])
            worksheet.write(row, 7, f'=SUM(H5:H{row})', formats['summary'])
            worksheet.write(row, 8, '', formats['summary'])
            worksheet.write(row, 9, '', formats['summary'])
        
        # Set column widths
        worksheet.set_column('A:A', 12)
        worksheet.set_column('B:B', 20)
        worksheet.set_column('C:C', 15)
        worksheet.set_column('D:E', 12)
        worksheet.set_column('F:F', 10)
        worksheet.set_column('G:H', 15)
        worksheet.set_column('I:J', 12)
    
    def _create_risk_sheet(self, worksheet, data, formats, workbook):
        """Create risk analysis sheet with charts"""
        # Title
        worksheet.merge_range('A1:G1', 'RISK ANALYSIS', formats['title'])
        worksheet.merge_range('A2:G2', f'Generated: {datetime.now().strftime("%B %d, %Y")}', formats['data_center'])
        
        # Risk Summary
        row = 4
        worksheet.write(row, 0, 'RISK SUMMARY', formats['subheader'])
        row += 2
        
        risk_summary = data.get('summary', {})
        risk_items = [
            ['Total at Risk', risk_summary.get('total_at_risk', 0)],
            ['High Risk Loans', risk_summary.get('high_risk_count', 0)],
            ['Portfolio at Risk %', risk_summary.get('par_percentage', 0)],
            ['Average Days Overdue', risk_summary.get('avg_days_overdue', 0)]
        ]
        
        for item in risk_items:
            worksheet.write(row, 0, item[0], formats['data'])
            if 'percentage' in item[0].lower() or '%' in item[0]:
                worksheet.write(row, 1, item[1]/100, formats['percentage'])
            elif 'amount' in item[0].lower() or 'risk' in item[0].lower():
                worksheet.write(row, 1, item[1], formats['currency'])
            else:
                worksheet.write(row, 1, item[1], formats['data_right'])
            row += 1
        
        # High Risk Loans Table
        row += 2
        worksheet.write(row, 0, 'HIGH RISK LOANS', formats['subheader'])
        row += 2
        
        risk_headers = ['Loan #', 'Borrower', 'Amount', 'Days Overdue', 'Risk Score']
        for col, header in enumerate(risk_headers):
            worksheet.write(row, col, header, formats['header'])
        
        row += 1
        
        high_risk_loans = data.get('high_risk_loans', [])
        for loan in high_risk_loans[:20]:  # Limit to top 20
            worksheet.write(row, 0, loan.get('loan_number', ''), formats['data'])
            worksheet.write(row, 1, loan.get('borrower', ''), formats['data'])
            worksheet.write(row, 2, loan.get('amount', 0), formats['currency'])
            worksheet.write(row, 3, loan.get('days_overdue', 0), formats['data_center'])
            worksheet.write(row, 4, loan.get('risk_score', 0), formats['data_center'])
            row += 1
        
        # Add risk distribution chart
        if 'risk_distribution' in data:
            chart_data = data['risk_distribution']
            categories_range = f"'Risk Analysis'!A{row+3}:A{row+3+len(chart_data)-1}"
            values_range = f"'Risk Analysis'!B{row+3}:B{row+3+len(chart_data)-1}"
            
            # Write chart data
            row += 3
            worksheet.write(row-1, 0, 'Risk Distribution', formats['subheader'])
            for i, (category, value) in enumerate(chart_data.items()):
                worksheet.write(row+i, 0, category, formats['data'])
                worksheet.write(row+i, 1, value, formats['data_right'])
            
            # Add column chart
            self._add_chart(workbook, worksheet, 'column', {
                'categories': categories_range,
                'values': values_range
            }, 'Risk Distribution', 'D4')
        
        # Set column widths
        worksheet.set_column('A:A', 20)
        worksheet.set_column('B:G', 15)
    
    def _create_financial_sheet(self, worksheet, data, formats, workbook):
        """Create financial summary sheet"""
        # Title
        worksheet.merge_range('A1:F1', 'FINANCIAL SUMMARY', formats['title'])
        worksheet.merge_range('A2:F2', f'Generated: {datetime.now().strftime("%B %d, %Y")}', formats['data_center'])
        
        # Revenue Summary
        row = 4
        worksheet.write(row, 0, 'REVENUE SUMMARY', formats['subheader'])
        row += 2
        
        revenue_data = data.get('revenue', {})
        revenue_items = [
            ['Interest Income', revenue_data.get('interest', 0)],
            ['Processing Fees', revenue_data.get('processing_fees', 0)],
            ['Late Fees', revenue_data.get('late_fees', 0)],
            ['Other Income', revenue_data.get('other', 0)],
            ['Total Revenue', revenue_data.get('total', 0)]
        ]
        
        for item in revenue_items:
            worksheet.write(row, 0, item[0], formats['data'])
            worksheet.write(row, 1, item[1], formats['currency'])
            row += 1
        
        # Expense Summary
        row += 2
        worksheet.write(row, 0, 'EXPENSE SUMMARY', formats['subheader'])
        row += 2
        
        expense_data = data.get('expenses', {})
        expense_items = [
            ['Operating Expenses', expense_data.get('operating', 0)],
            ['Staff Costs', expense_data.get('staff', 0)],
            ['Loan Loss Provision', expense_data.get('provisions', 0)],
            ['Other Expenses', expense_data.get('other', 0)],
            ['Total Expenses', expense_data.get('total', 0)]
        ]
        
        for item in expense_items:
            worksheet.write(row, 0, item[0], formats['data'])
            worksheet.write(row, 1, item[1], formats['currency'])
            row += 1
        
        # Profitability
        row += 2
        worksheet.write(row, 0, 'PROFITABILITY', formats['subheader'])
        row += 2
        
        profit_data = data.get('profitability', {})
        profit_items = [
            ['Gross Profit', profit_data.get('gross', 0)],
            ['Net Profit', profit_data.get('net', 0)],
            ['Profit Margin', profit_data.get('margin', 0)]
        ]
        
        for item in profit_items:
            worksheet.write(row, 0, item[0], formats['data'])
            if 'margin' in item[0].lower():
                worksheet.write(row, 1, item[1]/100, formats['percentage'])
            else:
                worksheet.write(row, 1, item[1], formats['currency'])
            row += 1
        
        # Set column widths
        worksheet.set_column('A:A', 25)
        worksheet.set_column('B:F', 15)
    
    def generate_loan_statement_excel(self, loan_data):
        """Generate loan statement Excel file"""
        try:
            buffer = io.BytesIO()
            workbook = xlsxwriter.Workbook(buffer, {'in_memory': True})
            formats = self._create_formats(workbook)
            
            worksheet = workbook.add_worksheet('Loan Statement')
            
            # Title
            worksheet.merge_range('A1:F1', 'LOAN STATEMENT', formats['title'])
            worksheet.merge_range('A2:F2', f'Generated: {datetime.now().strftime("%B %d, %Y")}', formats['data_center'])
            
            # Loan Information
            row = 4
            worksheet.write(row, 0, 'LOAN INFORMATION', formats['subheader'])
            row += 2
            
            loan_info = [
                ['Loan Number', loan_data.get('loan_number', 'N/A')],
                ['Borrower Name', loan_data.get('borrower_name', 'N/A')],
                ['Principal Amount', loan_data.get('principal_amount', 0)],
                ['Disbursement Date', loan_data.get('disbursement_date', 'N/A')],
                ['Due Date', loan_data.get('due_date', 'N/A')],
                ['Status', loan_data.get('status', 'N/A')],
                ['Outstanding Balance', loan_data.get('outstanding_balance', 0)]
            ]
            
            for info in loan_info:
                worksheet.write(row, 0, info[0], formats['data'])
                if 'amount' in info[0].lower() or 'balance' in info[0].lower():
                    worksheet.write(row, 1, info[1], formats['currency'])
                else:
                    worksheet.write(row, 1, info[1], formats['data'])
                row += 1
            
            # Repayment History
            if 'repayments' in loan_data and loan_data['repayments']:
                row += 2
                worksheet.write(row, 0, 'REPAYMENT HISTORY', formats['subheader'])
                row += 2
                
                headers = ['Date', 'Amount', 'Method', 'Reference', 'Balance After']
                for col, header in enumerate(headers):
                    worksheet.write(row, col, header, formats['header'])
                
                row += 1
                
                for repayment in loan_data['repayments']:
                    worksheet.write(row, 0, repayment.get('date', ''), formats['date'])
                    worksheet.write(row, 1, repayment.get('amount', 0), formats['currency'])
                    worksheet.write(row, 2, repayment.get('method', ''), formats['data'])
                    worksheet.write(row, 3, repayment.get('reference', ''), formats['data'])
                    worksheet.write(row, 4, repayment.get('balance_after', 0), formats['currency'])
                    row += 1
            
            # Set column widths
            worksheet.set_column('A:A', 20)
            worksheet.set_column('B:F', 15)
            
            workbook.close()
            buffer.seek(0)
            return buffer
            
        except Exception as e:
            logger.error(f"Loan statement Excel generation failed: {e}")
            return None