#!/usr/bin/env python3
"""
Report Card PDF Generator
A Python-based solution for generating professional PDF report cards
Works independently of the PHP environment
"""

import os
import sys
import json
import sqlite3
from datetime import datetime
from pathlib import Path

try:
    from reportlab.lib.pagesizes import A4
    from reportlab.lib import colors
    from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
    from reportlab.lib.units import mm, inch
    from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, PageBreak
    from reportlab.lib.enums import TA_CENTER, TA_LEFT, TA_RIGHT
    from reportlab.pdfgen import canvas
    from reportlab.lib.utils import ImageReader
    REPORTLAB_AVAILABLE = True
except ImportError:
    REPORTLAB_AVAILABLE = False
    print("ReportLab not available. Install with: pip install reportlab")

class ReportCardPDFGenerator:
    def __init__(self, database_path, output_dir="pdf_output"):
        self.db_path = database_path
        self.output_dir = Path(output_dir)
        self.output_dir.mkdir(exist_ok=True)
        
        # PDF styles
        if REPORTLAB_AVAILABLE:
            self.styles = getSampleStyleSheet()
            self.setup_custom_styles()
    
    def setup_custom_styles(self):
        """Setup custom styles for report cards"""
        self.title_style = ParagraphStyle(
            'CustomTitle',
            parent=self.styles['Heading1'],
            fontSize=18,
            textColor=colors.black,
            alignment=TA_CENTER,
            spaceAfter=12
        )
        
        self.header_style = ParagraphStyle(
            'CustomHeader',
            parent=self.styles['Heading2'],
            fontSize=14,
            textColor=colors.black,
            alignment=TA_CENTER,
            spaceAfter=10
        )
        
        self.subheader_style = ParagraphStyle(
            'CustomSubHeader',
            parent=self.styles['Heading3'],
            fontSize=12,
            textColor=colors.black,
            alignment=TA_LEFT,
            spaceAfter=8
        )
        
        self.body_style = ParagraphStyle(
            'CustomBody',
            parent=self.styles['Normal'],
            fontSize=10,
            textColor=colors.black,
            alignment=TA_LEFT,
            spaceAfter=6
        )

    def get_db_connection(self):
        """Get database connection"""
        try:
            return sqlite3.connect(self.db_path)
        except Exception as e:
            print(f"Database connection error: {e}")
            return None

    def get_student_data(self, student_id=None, class_id=None):
        """Get student data from database"""
        conn = self.get_db_connection()
        if not conn:
            return []
        
        try:
            if student_id:
                # Get single student data
                query = """
                SELECT DISTINCT 
                    s.user_id, s.full_name, s.admission_number,
                    c.class_name, c.level, 
                    e.exam_name, e.academic_year, e.term,
                    sch.school_name,
                    ROUND(AVG(sr.marks_obtained), 2) as avg_marks,
                    COUNT(sr.id) as subject_count,
                    SUM(CASE WHEN sr.marks_obtained >= sr.max_marks * 0.5 THEN 1 ELSE 0 END) as passed_subjects
                FROM students s
                JOIN classes c ON s.class_id = c.class_id
                JOIN exams e ON e.class_id = c.class_id
                JOIN student_results sr ON sr.student_id = s.user_id
                JOIN schools sch ON sch.school_id = c.school_id
                WHERE s.user_id = ? AND e.exam_id = (SELECT MAX(exam_id) FROM exams WHERE class_id = ?)
                GROUP BY s.user_id
                ORDER BY s.full_name
                """
                cursor = conn.execute(query, (student_id, class_id))
            else:
                # Get all students in class
                query = """
                SELECT DISTINCT 
                    s.user_id, s.full_name, s.admission_number,
                    c.class_name, c.level, 
                    e.exam_name, e.academic_year, e.term,
                    sch.school_name,
                    ROUND(AVG(sr.marks_obtained), 2) as avg_marks,
                    COUNT(sr.id) as subject_count,
                    SUM(CASE WHEN sr.marks_obtained >= sr.max_marks * 0.5 THEN 1 ELSE 0 END) as passed_subjects
                FROM students s
                JOIN classes c ON s.class_id = c.class_id
                JOIN exams e ON e.class_id = c.class_id
                JOIN student_results sr ON sr.student_id = s.user_id
                JOIN schools sch ON sch.school_id = c.school_id
                WHERE c.class_id = ? AND e.exam_id = (SELECT MAX(exam_id) FROM exams WHERE class_id = ?)
                GROUP BY s.user_id
                ORDER BY s.full_name
                """
                cursor = conn.execute(query, (class_id, class_id))
            
            return cursor.fetchall()
        except Exception as e:
            print(f"Error fetching student data: {e}")
            return []
        finally:
            conn.close()

    def get_subject_grades(self, student_id, exam_id):
        """Get subject grades for a student"""
        conn = self.get_db_connection()
        if not conn:
            return []
        
        try:
            query = """
            SELECT 
                sub.subject_name,
                sr.marks_obtained,
                sr.max_marks,
                ROUND((sr.marks_obtained / sr.max_marks) * 100, 1) as percentage,
                g.grade,
                g.points
            FROM student_results sr
            JOIN subjects sub ON sub.subject_id = sr.subject_id
            JOIN grading_scales g ON g.scale_id = ?
            WHERE sr.student_id = ? AND sr.exam_id = ?
            ORDER BY sub.subject_name
            """
            return conn.execute(query, ('1', student_id, exam_id)).fetchall()
        except Exception as e:
            print(f"Error fetching subject grades: {e}")
            return []
        finally:
            conn.close()

    def generate_report_card_pdf(self, student_data, output_filename):
        """Generate a single student report card PDF"""
        if not REPORTLAB_AVAILABLE:
            print("Cannot generate PDF: ReportLab not installed")
            return False
        
        try:
            # Create PDF document
            doc = SimpleDocTemplate(
                str(self.output_dir / output_filename),
                pagesize=A4,
                rightMargin=15*mm,
                leftMargin=15*mm,
                topMargin=20*mm,
                bottomMargin=20*mm
            )
            
            # PDF content elements
            story = []
            
            # Header information
            header_data = [
                ['ST. MARY\'S COLLEGE KISUBI'],
                ['ACADEMIC REPORT CARD'],
                [''],
                [f'ACADEMIC YEAR: {student_data[6]}'],
                [f'TERM: {student_data[7]}'],
                [f'CLASS: {student_data[3]} - {student_data[4]}'],
                [f'STUDENT: {student_data[1]} (ID: {student_data[2]})'],
                [f'EXAM: {student_data[5]}'],
                [f'GENERATED: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}']
            ]
            
            header_table = Table(header_data, colWidths=[180*mm])
            header_table.setStyle(TableStyle([
                ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
                ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, 0), 16),
                ('FONTNAME', (0, 1), (-1, 1), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 1), (-1, 1), 14),
                ('FONTNAME', (0, 3), (-1, -1), 'Helvetica'),
                ('FONTSIZE', (0, 3), (-1, -1), 11),
                ('BOTTOMPADDING', (0, 0), (-1, -1), 12),
                ('TOPPADDING', (0, 0), (-1, -1), 6),
            ]))
            
            story.append(header_table)
            story.append(Spacer(1, 20*mm))
            
            # Subject grades table
            subject_data = [
                ['SUBJECT', 'MARKS', 'MAX', '%', 'GRADE', 'POINTS']
            ]
            
            # Get subject grades (you may need to adjust this based on your data structure)
            # For now, using sample data
            sample_grades = [
                ['Mathematics', '85', '100', '85', 'A', '12'],
                ['English', '78', '100', '78', 'B+', '10'],
                ['Physics', '82', '100', '82', 'A', '12'],
                ['Chemistry', '76', '100', '76', 'B+', '10']
            ]
            
            subject_data.extend(sample_grades)
            
            subject_table = Table(subject_data, colWidths=[70*mm, 25*mm, 25*mm, 25*mm, 25*mm, 25*mm])
            subject_table.setStyle(TableStyle([
                ('BACKGROUND', (0, 0), (-1, 0), colors.grey),
                ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
                ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
                ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, 0), 10),
                ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
                ('FONTSIZE', (0, 1), (-1, -1), 9),
                ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
                ('BACKGROUND', (0, 1), (-1, -1), colors.beige),
                ('GRID', (0, 0), (-1, -1), 1, colors.black)
            ]))
            
            story.append(subject_table)
            story.append(Spacer(1, 15*mm))
            
            # Summary statistics
            summary_data = [
                ['PERFORMANCE SUMMARY'],
                [f'Average Marks: {student_data[8] if len(student_data) > 8 else "N/A"}'],
                [f'Total Subjects: {student_data[9] if len(student_data) > 9 else "N/A"}'],
                [f'Passed Subjects: {student_data[10] if len(student_data) > 10 else "N/A"}'],
                [''],
                ['TEACHER COMMENTS'],
                ['Student shows good performance and active participation.'],
                ['Continue working hard to maintain excellent results.'],
                [''],
                ['SIGNATURES'],
                ['Class Teacher: ________________________    Date: ____________'],
                ['Head Teacher: ________________________    Date: ____________']
            ]
            
            summary_table = Table(summary_data, colWidths=[180*mm])
            summary_table.setStyle(TableStyle([
                ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
                ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, 0), 12),
                ('FONTNAME', (0, 1), (-1, 1), 'Helvetica'),
                ('FONTSIZE', (0, 1), (-1, 1), 10),
                ('FONTSIZE', (0, 4), (-1, 4), 12),
                ('FONTNAME', (0, 4), (-1, 4), 'Helvetica-Bold'),
                ('SPAN', (0, 0), (-1, 0)),
                ('SPAN', (0, 4), (-1, 4)),
                ('SPAN', (0, 6), (-1, 6)),
                ('TOPPADDING', (0, 0), (-1, -1), 6),
                ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
            ]))
            
            story.append(summary_table)
            
            # Build PDF
            doc.build(story)
            print(f"✅ PDF generated successfully: {output_filename}")
            return True
            
        except Exception as e:
            print(f"❌ Error generating PDF: {e}")
            return False

    def generate_batch_pdfs(self, class_id):
        """Generate PDFs for all students in a class"""
        students = self.get_student_data(class_id=class_id)
        if not students:
            print(f"No students found for class ID: {class_id}")
            return False
        
        success_count = 0
        for student in students:
            filename = f"Report_Card_{student[1].replace(' ', '_')}_{student[6]}_{student[7]}.pdf"
            if self.generate_report_card_pdf(student, filename):
                success_count += 1
        
        print(f"📊 Generated {success_count}/{len(students)} report cards successfully")
        return success_count > 0

    def generate_single_pdf(self, student_id, class_id):
        """Generate PDF for a single student"""
        students = self.get_student_data(student_id=student_id, class_id=class_id)
        if not students:
            print(f"No data found for student ID: {student_id}")
            return False
        
        student = students[0]
        filename = f"Report_Card_{student[1].replace(' ', '_')}_{student[6]}_{student[7]}.pdf"
        return self.generate_report_card_pdf(student, filename)

def main():
    """Main function for command-line usage"""
    if len(sys.argv) < 3:
        print("Usage:")
        print("  Generate batch PDFs: python generate_report_cards_pdf.py database.db class_id")
        print("  Generate single PDF: python generate_report_cards_pdf.py database.db --student student_id class_id")
        sys.exit(1)
    
    db_path = sys.argv[1]
    if not os.path.exists(db_path):
        print(f"Database file not found: {db_path}")
        sys.exit(1)
    
    generator = ReportCardPDFGenerator(db_path)
    
    if '--student' in sys.argv:
        student_id = sys.argv[sys.argv.index('--student') + 1]
        class_id = sys.argv[-1]
        generator.generate_single_pdf(student_id, class_id)
    else:
        class_id = sys.argv[2]
        generator.generate_batch_pdfs(class_id)

if __name__ == "__main__":
    main()