"""
Export Service for PDF and Excel Generation

This service provides centralized export functionality for all report pages,
generating PDF and Excel files with consistent formatting and filter metadata.
"""

from django.http import HttpResponse
from django.utils import timezone
from io import BytesIO
from decimal import Decimal
from datetime import date, datetime
from typing import Dict, List, Any, Optional
import logging

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

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

logger = logging.getLogger(__name__)


class ReportExportService:
    """
    Service class for exporting reports to PDF and Excel formats.
    
    Provides consistent formatting, filter metadata inclusion, and handles
    large datasets with appropriate pagination or limits.
    """
    
    # Maximum rows per export to prevent memory issues
    MAX_EXPORT_ROWS = 10000
    
    def __init__(self):
        """Initialize the export service"""
        self.pdf_styles = getSampleStyleSheet()
        self._setup_custom_styles()
    
    def _setup_custom_styles(self):
        """Set up custom paragraph styles for PDF generation"""
        # Title style
        self.pdf_styles.add(ParagraphStyle(
            name='ReportTitle',
            parent=self.pdf_styles['Heading1'],
            fontSize=18,
            textColor=colors.HexColor('#2c3e50'),
            spaceAfter=20,
            alignment=TA_CENTER,
            fontName='Helvetica-Bold'
        ))
        
        # Subtitle style
        self.pdf_styles.add(ParagraphStyle(
            name='ReportSubtitle',
            parent=self.pdf_styles['Heading2'],
            fontSize=14,
            textColor=colors.HexColor('#3498db'),
            spaceAfter=12,
            alignment=TA_LEFT,
            fontName='Helvetica-Bold'
        ))
        
        # Filter info style
        self.pdf_styles.add(ParagraphStyle(
            name='FilterInfo',
            parent=self.pdf_styles['Normal'],
            fontSize=10,
            textColor=colors.HexColor('#5a5c69'),
            spaceAfter=10,
            alignment=TA_LEFT,
            fontName='Helvetica'
        ))
    
    def export_to_pdf(self, report_data: Dict[str, Any], report_type: str, 
                     filters: Dict[str, Any]) -> HttpResponse:
        """
        Export report data to PDF format with comprehensive error handling.
        
        Args:
            report_data: Dictionary containing report data (must include 'loans' key)
            report_type: Type of report (e.g., 'loans_due', 'processing_fees')
            filters: Dictionary of applied filters
            
        Returns:
            HttpResponse with PDF content and appropriate headers, or error response
        """
        try:
            # Validate input data
            if not report_data:
                logger.warning("Empty report_data provided for PDF export")
                report_data = {'loans': []}
            
            if 'loans' not in report_data:
                logger.warning("Missing 'loans' key in report_data")
                report_data['loans'] = []
            # Create PDF buffer
            buffer = BytesIO()
            
            # Create PDF document
            doc = SimpleDocTemplate(
                buffer,
                pagesize=A4,
                rightMargin=0.75*inch,
                leftMargin=0.75*inch,
                topMargin=1*inch,
                bottomMargin=0.75*inch
            )
            
            # Build PDF content
            story = []
            
            # Add title
            title = self._format_report_title(report_type)
            story.append(Paragraph(title, self.pdf_styles['ReportTitle']))
            story.append(Spacer(1, 0.3*inch))
            
            # Add generation info
            generation_info = f"Generated on: {self.format_date(datetime.now())}"
            story.append(Paragraph(generation_info, self.pdf_styles['FilterInfo']))
            story.append(Spacer(1, 0.2*inch))
            
            # Add filter metadata
            if filters:
                story.extend(self._create_filter_metadata_section(filters))
                story.append(Spacer(1, 0.3*inch))
            
            # Add data table
            loans_data = report_data.get('loans', [])
            
            if loans_data:
                # Limit data if too large
                if len(loans_data) > self.MAX_EXPORT_ROWS:
                    loans_data = loans_data[:self.MAX_EXPORT_ROWS]
                    warning = f"Note: Export limited to {self.MAX_EXPORT_ROWS} records"
                    story.append(Paragraph(warning, self.pdf_styles['FilterInfo']))
                    story.append(Spacer(1, 0.1*inch))
                
                data_table = self._create_pdf_data_table(loans_data, report_type)
                story.append(data_table)
            else:
                story.append(Paragraph("No data available for export.", self.pdf_styles['Normal']))
            
            # Build PDF
            doc.build(story)
            
            # Get PDF content
            buffer.seek(0)
            pdf_content = buffer.getvalue()
            buffer.close()
            
            # Create HTTP response
            response = HttpResponse(pdf_content, content_type='application/pdf')
            filename = f"{report_type}_report_{timezone.now().strftime('%Y%m%d_%H%M%S')}.pdf"
            response['Content-Disposition'] = f'attachment; filename="{filename}"'
            
            return response
            
        except Exception as e:
            logger.error(f"Error generating PDF export: {str(e)}")
            return self._create_error_response('PDF', str(e))
    
    def export_to_excel(self, report_data: Dict[str, Any], report_type: str,
                       filters: Dict[str, Any]) -> HttpResponse:
        """
        Export report data to Excel format with comprehensive error handling.
        
        Args:
            report_data: Dictionary containing report data (must include 'loans' key)
            report_type: Type of report (e.g., 'loans_due', 'processing_fees')
            filters: Dictionary of applied filters
            
        Returns:
            HttpResponse with Excel content and appropriate headers, or error response
        """
        try:
            # Validate input data
            if not report_data:
                logger.warning("Empty report_data provided for Excel export")
                report_data = {'loans': []}
            
            if 'loans' not in report_data:
                logger.warning("Missing 'loans' key in report_data")
                report_data['loans'] = []
            # Create workbook
            workbook = Workbook()
            sheet = workbook.active
            sheet.title = "Report Data"
            
            # Add title
            title = self._format_report_title(report_type)
            sheet['A1'] = title
            sheet['A1'].font = Font(size=16, bold=True, color='2c3e50')
            sheet['A1'].alignment = Alignment(horizontal='center')
            sheet.merge_cells('A1:F1')
            
            # Add generation info
            sheet['A2'] = f"Generated on: {self.format_date(datetime.now())}"
            sheet['A2'].font = Font(size=10, color='5a5c69')
            
            current_row = 4
            
            # Add filter metadata
            if filters:
                current_row = self._add_excel_filter_metadata(sheet, filters, current_row)
                current_row += 1
            
            # Add data
            loans_data = report_data.get('loans', [])
            
            if loans_data:
                # Limit data if too large
                if len(loans_data) > self.MAX_EXPORT_ROWS:
                    loans_data = loans_data[:self.MAX_EXPORT_ROWS]
                    sheet[f'A{current_row}'] = f"Note: Export limited to {self.MAX_EXPORT_ROWS} records"
                    sheet[f'A{current_row}'].font = Font(italic=True, color='e74c3c')
                    current_row += 2
                
                current_row = self._add_excel_data_table(sheet, loans_data, report_type, current_row)
            else:
                sheet[f'A{current_row}'] = "No data available for export."
                sheet[f'A{current_row}'].font = Font(italic=True)
            
            # Auto-adjust column widths
            self._auto_adjust_excel_columns(sheet)
            
            # Save to buffer
            buffer = BytesIO()
            workbook.save(buffer)
            buffer.seek(0)
            excel_content = buffer.getvalue()
            buffer.close()
            
            # Create HTTP response
            response = HttpResponse(
                excel_content,
                content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )
            filename = f"{report_type}_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 generating Excel export: {str(e)}")
            return self._create_error_response('Excel', str(e))
    
    def format_currency(self, amount: Any) -> str:
        """
        Format currency values with two decimal places.
        
        Args:
            amount: Numeric value to format
            
        Returns:
            Formatted currency string (e.g., "KES 10,000.00")
        """
        try:
            if amount is None:
                return "KES 0.00"
            
            # Convert to Decimal for precision
            if isinstance(amount, str):
                amount = Decimal(amount)
            elif not isinstance(amount, Decimal):
                amount = Decimal(str(amount))
            
            # Format with two decimal places and thousands separator
            formatted = f"KES {amount:,.2f}"
            return formatted
            
        except (ValueError, TypeError, Exception) as e:
            logger.warning(f"Error formatting currency {amount}: {str(e)}")
            return "KES 0.00"
    
    def format_date(self, date_value: Any) -> str:
        """
        Format date values consistently.
        
        Args:
            date_value: Date or datetime object to format
            
        Returns:
            Formatted date string (e.g., "2024-01-15")
        """
        try:
            if date_value is None:
                return "N/A"
            
            # Handle datetime objects
            if isinstance(date_value, datetime):
                return date_value.strftime('%Y-%m-%d %H:%M:%S')
            
            # Handle date objects
            if isinstance(date_value, date):
                return date_value.strftime('%Y-%m-%d')
            
            # Handle string dates
            if isinstance(date_value, str):
                return date_value
            
            return str(date_value)
            
        except Exception as e:
            logger.warning(f"Error formatting date {date_value}: {str(e)}")
            return "N/A"
    
    def _format_report_title(self, report_type: str) -> str:
        """Format report type into readable title"""
        return report_type.replace('_', ' ').title() + " Report"
    
    def _create_filter_metadata_section(self, filters: Dict[str, Any]) -> List:
        """Create filter metadata section for PDF"""
        elements = []
        
        elements.append(Paragraph("Applied Filters:", self.pdf_styles['ReportSubtitle']))
        
        filter_lines = []
        
        if filters.get('start_date') and filters.get('end_date'):
            filter_lines.append(f"Date Range: {self.format_date(filters['start_date'])} to {self.format_date(filters['end_date'])}")
        
        if filters.get('product_id'):
            filter_lines.append(f"Loan Product: {filters['product_id']}")
        
        if filters.get('branch_id'):
            filter_lines.append(f"Branch: {filters['branch_id']}")
        
        if filters.get('gender'):
            filter_lines.append(f"Gender: {filters['gender']}")
        
        if filters.get('period'):
            filter_lines.append(f"Period: {filters['period']}")
        
        if not filter_lines:
            filter_lines.append("No filters applied")
        
        for line in filter_lines:
            elements.append(Paragraph(f"• {line}", self.pdf_styles['FilterInfo']))
        
        return elements
    
    def _create_pdf_data_table(self, loans_data: List[Dict], report_type: str) -> Table:
        """Create data table for PDF"""
        # Determine columns based on report type
        if report_type == 'loans_due':
            headers = ['Loan Number', 'Borrower', 'Amount', 'Due Date', 'Outstanding']
            keys = ['loan_number', 'borrower_name', 'principal_amount', 'due_date', 'outstanding_amount']
        elif report_type == 'processing_fees':
            headers = ['Loan Number', 'Borrower', 'Processing Fee', 'Date']
            keys = ['loan_number', 'borrower_name', 'processing_fee', 'disbursement_date']
        elif report_type == 'interest_income':
            headers = ['Loan Number', 'Borrower', 'Interest Amount', 'Date']
            keys = ['loan_number', 'borrower_name', 'interest_amount', 'disbursement_date']
        else:
            # Generic columns
            if loans_data:
                headers = list(loans_data[0].keys())
                keys = headers
            else:
                headers = ['No Data']
                keys = []
        
        # Build table data
        table_data = [headers]
        
        for loan in loans_data:
            row = []
            for key in keys:
                value = loan.get(key, '')
                
                # Format based on key type
                if 'amount' in key.lower() or 'fee' in key.lower():
                    row.append(self.format_currency(value))
                elif 'date' in key.lower():
                    row.append(self.format_date(value))
                else:
                    row.append(str(value) if value is not None else '')
            
            table_data.append(row)
        
        # Create table
        table = Table(table_data)
        
        # Style table
        table.setStyle(TableStyle([
            # Header styling
            ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#2c3e50')),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
            ('ALIGN', (0, 0), (-1, 0), 'CENTER'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, 0), 11),
            ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
            
            # Data styling
            ('BACKGROUND', (0, 1), (-1, -1), colors.beige),
            ('TEXTCOLOR', (0, 1), (-1, -1), colors.black),
            ('ALIGN', (0, 1), (-1, -1), 'LEFT'),
            ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
            ('FONTSIZE', (0, 1), (-1, -1), 9),
            ('GRID', (0, 0), (-1, -1), 1, colors.black),
            ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
        ]))
        
        return table
    
    def _add_excel_filter_metadata(self, sheet, filters: Dict[str, Any], start_row: int) -> int:
        """Add filter metadata to Excel sheet"""
        sheet[f'A{start_row}'] = "Applied Filters:"
        sheet[f'A{start_row}'].font = Font(bold=True, size=12, color='3498db')
        start_row += 1
        
        if filters.get('start_date') and filters.get('end_date'):
            sheet[f'A{start_row}'] = f"Date Range: {self.format_date(filters['start_date'])} to {self.format_date(filters['end_date'])}"
            start_row += 1
        
        if filters.get('product_id'):
            sheet[f'A{start_row}'] = f"Loan Product: {filters['product_id']}"
            start_row += 1
        
        if filters.get('branch_id'):
            sheet[f'A{start_row}'] = f"Branch: {filters['branch_id']}"
            start_row += 1
        
        if filters.get('gender'):
            sheet[f'A{start_row}'] = f"Gender: {filters['gender']}"
            start_row += 1
        
        if filters.get('period'):
            sheet[f'A{start_row}'] = f"Period: {filters['period']}"
            start_row += 1
        
        return start_row
    
    def _add_excel_data_table(self, sheet, loans_data: List[Dict], 
                             report_type: str, start_row: int) -> int:
        """Add data table to Excel sheet"""
        # Determine columns based on report type
        if report_type == 'loans_due':
            headers = ['Loan Number', 'Borrower', 'Amount', 'Due Date', 'Outstanding']
            keys = ['loan_number', 'borrower_name', 'principal_amount', 'due_date', 'outstanding_amount']
        elif report_type == 'processing_fees':
            headers = ['Loan Number', 'Borrower', 'Processing Fee', 'Date']
            keys = ['loan_number', 'borrower_name', 'processing_fee', 'disbursement_date']
        elif report_type == 'interest_income':
            headers = ['Loan Number', 'Borrower', 'Interest Amount', 'Date']
            keys = ['loan_number', 'borrower_name', 'interest_amount', 'disbursement_date']
        else:
            # Generic columns
            if loans_data:
                headers = list(loans_data[0].keys())
                keys = headers
            else:
                return start_row
        
        # Add headers
        for col_idx, header in enumerate(headers, start=1):
            cell = sheet.cell(row=start_row, column=col_idx, value=header)
            cell.font = Font(bold=True, color='FFFFFF')
            cell.fill = PatternFill(start_color='2c3e50', end_color='2c3e50', 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')
            )
        
        start_row += 1
        
        # Add data rows
        for loan in loans_data:
            for col_idx, key in enumerate(keys, start=1):
                value = loan.get(key, '')
                
                # Format based on key type
                if 'amount' in key.lower() or 'fee' in key.lower():
                    formatted_value = self.format_currency(value)
                elif 'date' in key.lower():
                    formatted_value = self.format_date(value)
                else:
                    formatted_value = str(value) if value is not None else ''
                
                cell = sheet.cell(row=start_row, column=col_idx, value=formatted_value)
                cell.alignment = Alignment(horizontal='left', vertical='center')
                cell.border = Border(
                    left=Side(style='thin'),
                    right=Side(style='thin'),
                    top=Side(style='thin'),
                    bottom=Side(style='thin')
                )
            
            start_row += 1
        
        return start_row
    
    def _auto_adjust_excel_columns(self, sheet):
        """Auto-adjust column widths based on content"""
        for column in sheet.columns:
            max_length = 0
            column_letter = get_column_letter(column[0].column)
            
            for cell in column:
                try:
                    if cell.value:
                        max_length = max(max_length, len(str(cell.value)))
                except:
                    pass
            
            adjusted_width = min(max_length + 2, 50)
            sheet.column_dimensions[column_letter].width = adjusted_width
    
    def _create_error_response(self, export_type: str, error_message: str) -> HttpResponse:
        """Create error response for failed exports"""
        response = HttpResponse(
            f"Error generating {export_type} export: {error_message}",
            content_type='text/plain',
            status=500
        )
        return response
