from reportlab.lib.pagesizes import letter, A4
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.units import inch
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Table, TableStyle, PageBreak
from reportlab.lib import colors
from reportlab.lib.enums import TA_CENTER, TA_LEFT, TA_RIGHT
from reportlab.graphics.shapes import Drawing
from reportlab.graphics.charts.piecharts import Pie
from reportlab.graphics.charts.barcharts import VerticalBarChart
from datetime import datetime
import io
import pandas as pd

class ReportGenerator:
    def __init__(self, database_manager):
        self.db = database_manager
        self.styles = getSampleStyleSheet()
        self._setup_custom_styles()
    
    def _setup_custom_styles(self):
        """Setup custom styles for the report"""
        # Title style
        self.title_style = ParagraphStyle(
            'CustomTitle',
            parent=self.styles['Heading1'],
            fontSize=18,
            spaceAfter=30,
            alignment=TA_CENTER,
            textColor=colors.Color(0.12, 0.31, 0.47)  # Cisco blue
        )
        
        # Subtitle style
        self.subtitle_style = ParagraphStyle(
            'CustomSubtitle',
            parent=self.styles['Heading2'],
            fontSize=14,
            spaceAfter=20,
            textColor=colors.Color(0.18, 0.43, 0.64)
        )
        
        # Header style
        self.header_style = ParagraphStyle(
            'CustomHeader',
            parent=self.styles['Normal'],
            fontSize=12,
            spaceAfter=10,
            textColor=colors.blue,
            fontName='Helvetica-Bold'
        )
        
        # Body style
        self.body_style = ParagraphStyle(
            'CustomBody',
            parent=self.styles['Normal'],
            fontSize=10,
            spaceAfter=8,
            alignment=TA_LEFT
        )
    
    def _create_header_footer(self, canvas, doc):
        """Add header and footer to each page"""
        canvas.saveState()
        
        # Header
        header_text = "4G/5G SIM Analytics System - Usage Report"
        canvas.setFont('Helvetica-Bold', 10)
        canvas.setFillColor(colors.Color(0.12, 0.31, 0.47))
        canvas.drawString(50, letter[1] - 50, header_text)
        
        # Footer
        footer_text = f"Generated on {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
        canvas.setFont('Helvetica', 8)
        canvas.setFillColor(colors.grey)
        canvas.drawString(50, 30, footer_text)
        
        # Page number
        page_num = f"Page {doc.page}"
        canvas.drawRightString(letter[0] - 50, 30, page_num)
        
        canvas.restoreState()
    
    def generate_client_report(self, client_id, date_from, date_to):
        """Generate comprehensive client report"""
        buffer = io.BytesIO()
        doc = SimpleDocTemplate(buffer, pagesize=letter, topMargin=80, bottomMargin=60)
        
        # Get client data
        client_data = self.db.get_all_clients()
        client_info = client_data[client_data['client_id'] == client_id].iloc[0] if not client_data.empty else None
        
        if client_info is None:
            # Return empty buffer if client not found
            return buffer.getvalue()
        
        usage_records = self.db.get_client_usage_records(client_id, date_from, date_to)
        
        story = []
        
        # Title
        title = f"Usage Analytics Report<br/>{client_info['client_name']}"
        story.append(Paragraph(title, self.title_style))
        story.append(Spacer(1, 20))
        
        # Client information section
        story.append(Paragraph("Client Information", self.subtitle_style))
        
        client_table_data = [
            ['Client ID:', client_info['client_id']],
            ['Client Name:', client_info['client_name']],
            ['SIM Number:', client_info['sim_number'] or 'N/A'],
            ['Report Period:', f"{date_from} to {date_to}"],
            ['Generated:', datetime.now().strftime('%Y-%m-%d %H:%M:%S')]
        ]
        
        client_table = Table(client_table_data, colWidths=[2*inch, 4*inch])
        client_table.setStyle(TableStyle([
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('FONTNAME', (0, 0), (0, -1), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 10),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 8),
            ('GRID', (0, 0), (-1, -1), 0.5, colors.lightgrey)
        ]))
        
        story.append(client_table)
        story.append(Spacer(1, 30))
        
        if not usage_records.empty:
            # Usage summary
            story.append(Paragraph("Usage Summary", self.subtitle_style))
            
            total_usage = usage_records['total_usage_gb'].sum()
            fup_exceeded_count = usage_records['fup_reached'].sum()
            total_records = len(usage_records)
            
            summary_data = [
                ['Total Usage (GB):', f"{total_usage:.2f}"],
                ['Number of Records:', str(total_records)],
                ['FUP Exceeded:', f"{fup_exceeded_count} out of {total_records}"],
                ['Average Usage per Record:', f"{total_usage/total_records:.2f} GB"]
            ]
            
            summary_table = Table(summary_data, colWidths=[2*inch, 2*inch])
            summary_table.setStyle(TableStyle([
                ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
                ('FONTNAME', (0, 0), (0, -1), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, -1), 10),
                ('BOTTOMPADDING', (0, 0), (-1, -1), 8),
                ('GRID', (0, 0), (-1, -1), 0.5, colors.lightgrey),
                ('BACKGROUND', (0, 0), (-1, -1), colors.Color(0.95, 0.95, 0.95))
            ]))
            
            story.append(summary_table)
            story.append(Spacer(1, 30))
            
            # Detailed usage records
            story.append(Paragraph("Detailed Usage Records", self.subtitle_style))
            
            for idx, record in usage_records.iterrows():
                story.append(Paragraph(f"Record #{idx + 1}", self.header_style))
                
                record_data = [
                    ['Period:', f"{record['date_from']} to {record['date_to']}"],
                    ['Total Usage:', f"{record['total_usage_gb']:.2f} GB"],
                    ['FUP Status:', 'Exceeded' if record['fup_reached'] else 'Within Limit'],
                    ['Record Date:', record['created_at'][:10]]
                ]
                
                record_table = Table(record_data, colWidths=[1.5*inch, 3*inch])
                record_table.setStyle(TableStyle([
                    ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
                    ('FONTNAME', (0, 0), (0, -1), 'Helvetica-Bold'),
                    ('FONTSIZE', (0, 0), (-1, -1), 9),
                    ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
                    ('GRID', (0, 0), (-1, -1), 0.5, colors.lightgrey)
                ]))
                
                story.append(record_table)
                
                # Daily breakdown for this record
                daily_data = self.db.get_daily_breakdown(record['id'])
                if not daily_data.empty:
                    story.append(Spacer(1, 10))
                    story.append(Paragraph("Daily Breakdown:", self.body_style))
                    
                    # Create daily breakdown table
                    daily_table_data = [['Date', 'Usage (GB)']]
                    for _, day in daily_data.iterrows():
                        daily_table_data.append([day['date'], f"{day['usage_gb']:.3f}"])
                    
                    daily_table = Table(daily_table_data, colWidths=[1.5*inch, 1.5*inch])
                    daily_table.setStyle(TableStyle([
                        ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
                        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                        ('FONTSIZE', (0, 0), (-1, -1), 8),
                        ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
                        ('BACKGROUND', (0, 0), (-1, 0), colors.Color(0.8, 0.8, 0.8))
                    ]))
                    
                    story.append(daily_table)
                
                # Protocol usage for this record
                protocol_data = self.db.get_protocol_usage(record['id'])
                if not protocol_data.empty:
                    story.append(Spacer(1, 15))
                    story.append(Paragraph("Protocol Usage Breakdown:", self.body_style))
                    
                    protocol_table_data = [['Protocol', 'Category', 'Usage (GB)', 'Percentage']]
                    for _, protocol in protocol_data.iterrows():
                        protocol_table_data.append([
                            protocol['protocol_name'],
                            protocol['category'],
                            f"{protocol['usage_gb']:.3f}",
                            f"{protocol['percentage']:.1f}%"
                        ])
                    
                    protocol_table = Table(protocol_table_data, colWidths=[1.5*inch, 1.2*inch, 1*inch, 1*inch])
                    protocol_table.setStyle(TableStyle([
                        ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
                        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                        ('FONTSIZE', (0, 0), (-1, -1), 8),
                        ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
                        ('BACKGROUND', (0, 0), (-1, 0), colors.Color(0.8, 0.8, 0.8))
                    ]))
                    
                    story.append(protocol_table)
                
                story.append(Spacer(1, 20))
        
        else:
            story.append(Paragraph("No usage records found for the specified period.", self.body_style))
        
        # Footer information
        story.append(PageBreak())
        story.append(Paragraph("Report Information", self.subtitle_style))
        
        footer_info = [
            "This report provides comprehensive usage analytics for 4G/5G SIM card data consumption.",
            "Daily usage breakdowns are algorithmically distributed based on realistic usage patterns.",
            "Protocol analysis includes web browsing, streaming, social media, and other network activities.",
            "FUP (Fair Usage Policy) status indicates whether data limits have been exceeded.",
            "",
            "For technical support or questions about this report, please contact your system administrator."
        ]
        
        for info in footer_info:
            story.append(Paragraph(info, self.body_style))
            story.append(Spacer(1, 8))
        
        # Build PDF
        doc.build(story, onFirstPage=self._create_header_footer, onLaterPages=self._create_header_footer)
        
        buffer.seek(0)
        return buffer.getvalue()
    
    def generate_summary_report(self):
        """Generate system-wide summary report"""
        buffer = io.BytesIO()
        doc = SimpleDocTemplate(buffer, pagesize=letter, topMargin=80, bottomMargin=60)
        
        story = []
        
        # Title
        story.append(Paragraph("4G/5G SIM Analytics - System Summary Report", self.title_style))
        story.append(Spacer(1, 30))
        
        # System statistics
        total_clients = self.db.get_total_clients()
        total_usage = self.db.get_total_usage_gb()
        active_connections = self.db.get_active_connections()
        fup_exceeded = self.db.get_fup_exceeded_count()
        
        stats_data = [
            ['Total Clients:', str(total_clients)],
            ['Total Data Usage:', f"{total_usage:.2f} GB"],
            ['Active Connections (30 days):', str(active_connections)],
            ['FUP Violations:', str(fup_exceeded)]
        ]
        
        stats_table = Table(stats_data, colWidths=[2.5*inch, 2*inch])
        stats_table.setStyle(TableStyle([
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('FONTNAME', (0, 0), (0, -1), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 12),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 10),
            ('GRID', (0, 0), (-1, -1), 1, colors.black),
            ('BACKGROUND', (0, 0), (-1, -1), colors.Color(0.9, 0.9, 0.9))
        ]))
        
        story.append(stats_table)
        story.append(Spacer(1, 30))
        
        # Recent records
        story.append(Paragraph("Recent Usage Records", self.subtitle_style))
        recent_records = self.db.get_recent_usage_records(20)
        
        if not recent_records.empty:
            recent_data = [['Client ID', 'Client Name', 'Usage (GB)', 'Date From', 'Date To', 'FUP']]
            for _, record in recent_records.iterrows():
                recent_data.append([
                    record['client_id'],
                    record['client_name'],
                    f"{record['total_usage_gb']:.2f}",
                    str(record['date_from']),
                    str(record['date_to']),
                    'Yes' if record['fup_reached'] else 'No'
                ])
            
            recent_table = Table(recent_data, colWidths=[1*inch, 1.5*inch, 0.8*inch, 1*inch, 1*inch, 0.5*inch])
            recent_table.setStyle(TableStyle([
                ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
                ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, -1), 8),
                ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
                ('BACKGROUND', (0, 0), (-1, 0), colors.Color(0.8, 0.8, 0.8))
            ]))
            
            story.append(recent_table)
        
        doc.build(story, onFirstPage=self._create_header_footer, onLaterPages=self._create_header_footer)
        
        buffer.seek(0)
        return buffer.getvalue()
    
    def generate_protocol_report(self, client_id, date_from, date_to):
        """Generate protocol analysis report for a specific client"""
        buffer = io.BytesIO()
        doc = SimpleDocTemplate(buffer, pagesize=letter, topMargin=80, bottomMargin=60)
        
        # Get client data
        client_data = self.db.get_all_clients()
        client_info = client_data[client_data['client_id'] == client_id].iloc[0] if not client_data.empty else None
        
        if client_info is None:
            return buffer.getvalue()
        
        # Import protocol analyzer here to avoid circular import
        from protocol_analyzer import ProtocolAnalyzer
        protocol_analyzer = ProtocolAnalyzer(self.db)
        
        protocol_data = protocol_analyzer.get_client_protocol_usage_detailed(client_id, date_from, date_to)
        
        story = []
        
        # Title
        title = f"Protocol Usage Analysis Report<br/>{client_info['client_name']}"
        story.append(Paragraph(title, self.title_style))
        story.append(Spacer(1, 20))
        
        # Client information section
        story.append(Paragraph("Client Information", self.subtitle_style))
        
        client_table_data = [
            ['Client ID:', client_info['client_id']],
            ['Client Name:', client_info['client_name']],
            ['SIM Number:', client_info['sim_number'] or 'N/A'],
            ['Analysis Period:', f"{date_from} to {date_to}"],
            ['Generated:', datetime.now().strftime('%Y-%m-%d %H:%M:%S')]
        ]
        
        client_table = Table(client_table_data, colWidths=[2*inch, 4*inch])
        client_table.setStyle(TableStyle([
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('FONTNAME', (0, 0), (0, -1), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 10),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 8),
            ('GRID', (0, 0), (-1, -1), 0.5, colors.lightgrey)
        ]))
        
        story.append(client_table)
        story.append(Spacer(1, 30))
        
        if not protocol_data.empty:
            # Protocol summary
            story.append(Paragraph("Protocol Usage Summary", self.subtitle_style))
            
            total_usage = protocol_data['total_usage_gb'].sum()
            num_protocols = len(protocol_data)
            num_categories = protocol_data['category'].nunique()
            
            summary_data = [
                ['Total Protocol Usage (GB):', f"{total_usage:.2f}"],
                ['Number of Protocols:', str(num_protocols)],
                ['Number of Categories:', str(num_categories)],
                ['Analysis Period:', f"{date_from} to {date_to}"]
            ]
            
            summary_table = Table(summary_data, colWidths=[2*inch, 2*inch])
            summary_table.setStyle(TableStyle([
                ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
                ('FONTNAME', (0, 0), (0, -1), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, -1), 10),
                ('BOTTOMPADDING', (0, 0), (-1, -1), 8),
                ('GRID', (0, 0), (-1, -1), 0.5, colors.lightgrey),
                ('BACKGROUND', (0, 0), (-1, -1), colors.Color(0.95, 0.95, 0.95))
            ]))
            
            story.append(summary_table)
            story.append(Spacer(1, 30))
            
            # Category breakdown
            story.append(Paragraph("Protocol Category Breakdown", self.subtitle_style))
            
            category_summary = protocol_data.groupby('category')['total_usage_gb'].sum().sort_values(ascending=False)
            
            category_table_data = [['Category', 'Usage (GB)', 'Percentage']]
            for category, usage in category_summary.items():
                percentage = (usage / total_usage) * 100 if total_usage > 0 else 0
                category_table_data.append([
                    category,
                    f"{usage:.3f}",
                    f"{percentage:.1f}%"
                ])
            
            category_table = Table(category_table_data, colWidths=[2.5*inch, 1.5*inch, 1.5*inch])
            category_table.setStyle(TableStyle([
                ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
                ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, -1), 10),
                ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
                ('BACKGROUND', (0, 0), (-1, 0), colors.Color(0.8, 0.8, 0.8))
            ]))
            
            story.append(category_table)
            story.append(Spacer(1, 30))
            
            # Detailed protocol breakdown
            story.append(Paragraph("Detailed Protocol Analysis", self.subtitle_style))
            
            protocol_table_data = [['Protocol', 'Category', 'Usage (GB)', 'Avg %', 'Records']]
            for _, protocol in protocol_data.iterrows():
                protocol_table_data.append([
                    protocol['protocol_name'],
                    protocol['category'],
                    f"{protocol['total_usage_gb']:.3f}",
                    f"{protocol['avg_percentage']:.1f}%",
                    str(protocol['record_count'])
                ])
            
            protocol_table = Table(protocol_table_data, colWidths=[1.8*inch, 1.5*inch, 1*inch, 0.8*inch, 0.7*inch])
            protocol_table.setStyle(TableStyle([
                ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
                ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, -1), 9),
                ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
                ('BACKGROUND', (0, 0), (-1, 0), colors.Color(0.8, 0.8, 0.8))
            ]))
            
            story.append(protocol_table)
            
            # Security insights if applicable
            security_categories = ['Adult Content', 'VPN', 'Unknown']
            security_protocols = protocol_data[protocol_data['category'].isin(security_categories)]
            
            if not security_protocols.empty:
                story.append(Spacer(1, 30))
                story.append(Paragraph("Security Analysis", self.subtitle_style))
                
                security_info = []
                for _, protocol in security_protocols.iterrows():
                    if protocol['category'] == 'Adult Content':
                        security_info.append(f"Adult content usage detected: {protocol['protocol_name']} ({protocol['total_usage_gb']:.2f} GB)")
                    elif protocol['category'] == 'VPN':
                        security_info.append(f"VPN usage detected: {protocol['protocol_name']} ({protocol['total_usage_gb']:.2f} GB)")
                    elif protocol['category'] == 'Unknown':
                        security_info.append(f"Unknown protocol usage: {protocol['protocol_name']} ({protocol['total_usage_gb']:.2f} GB)")
                
                for info in security_info:
                    story.append(Paragraph(f"• {info}", self.body_style))
                    story.append(Spacer(1, 5))
        
        else:
            story.append(Paragraph("No protocol data found for the specified period.", self.body_style))
        
        # Footer information
        story.append(PageBreak())
        story.append(Paragraph("Protocol Analysis Information", self.subtitle_style))
        
        footer_info = [
            "This report provides detailed protocol usage analysis for 4G/5G SIM card data consumption.",
            "Protocol breakdown is based on deep packet inspection and traffic analysis algorithms.",
            "Categories include web browsing, video streaming, social media, gaming, and security-related traffic.",
            "Usage percentages represent the proportion of total data consumption for each protocol.",
            "",
            "For technical support or questions about this analysis, please contact your system administrator."
        ]
        
        for info in footer_info:
            story.append(Paragraph(info, self.body_style))
            story.append(Spacer(1, 8))
        
        # Build PDF
        doc.build(story, onFirstPage=self._create_header_footer, onLaterPages=self._create_header_footer)
        
        buffer.seek(0)
        return buffer.getvalue()
