#!/usr/bin/env python3
"""
Generate a PDF report with payment statistics in German
"""

import sys
from datetime import datetime
from decimal import Decimal

sys.path.insert(0, '/var/git/SUST/sustApp')

try:
    from reportlab.lib.pagesizes import A4, landscape
    from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
    from reportlab.lib.units import cm
    from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, PageBreak
    from reportlab.lib import colors
    from reportlab.lib.enums import TA_CENTER, TA_LEFT
except ImportError:
    print("reportlab not installed. Installing...")
    import subprocess
    subprocess.check_call([sys.executable, "-m", "pip", "install", "reportlab"])
    from reportlab.lib.pagesizes import A4, landscape
    from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
    from reportlab.lib.units import cm
    from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, PageBreak
    from reportlab.lib import colors
    from reportlab.lib.enums import TA_CENTER, TA_LEFT

from sqlalchemy import text
from models.database import SessionLocal

def get_non_paying_members():
    """Get non-paying active primary members"""
    session = SessionLocal()
    try:
        query = text("""
            SELECT m.id, m.member_firstname, m.member_surname, m.member_email,
                   m.member_membership_start, m.member_count,
                   MAX(p.payment_date) as last_payment_date,
                   MAX(EXTRACT(YEAR FROM p.payment_year)) as last_payment_year
            FROM members m
            LEFT JOIN payments p ON m.id = p.member_id
            WHERE m.member_active = true 
            AND m.member_died IS NULL 
            AND m.member_primary = true
            AND m.id NOT IN (
                SELECT DISTINCT member_id 
                FROM payments 
                WHERE EXTRACT(YEAR FROM payment_year) = 2026
            )
            GROUP BY m.id, m.member_firstname, m.member_surname, m.member_email, 
                     m.member_membership_start, m.member_count
            ORDER BY m.member_count DESC, m.member_surname, m.member_firstname
        """)
        result = session.execute(query).fetchall()
        return result
    finally:
        session.close()

def create_pdf():
    """Create PDF report"""
    
    # Create PDF document
    pdf_file = "/var/git/SUST/Zahlungsstatistik_2025_2026.pdf"
    doc = SimpleDocTemplate(pdf_file, pagesize=landscape(A4), topMargin=0.8*cm, bottomMargin=0.8*cm)
    
    # Style sheets
    styles = getSampleStyleSheet()
    title_style = ParagraphStyle(
        'CustomTitle',
        parent=styles['Heading1'],
        fontSize=18,
        textColor=colors.HexColor('#1a1a1a'),
        spaceAfter=12,
        alignment=TA_CENTER,
        fontName='Helvetica-Bold'
    )
    
    heading_style = ParagraphStyle(
        'CustomHeading',
        parent=styles['Heading2'],
        fontSize=12,
        textColor=colors.HexColor('#333333'),
        spaceAfter=8,
        spaceBefore=8,
        fontName='Helvetica-Bold'
    )
    
    normal_style = ParagraphStyle(
        'CustomNormal',
        parent=styles['Normal'],
        fontSize=9,
        leading=11
    )
    
    # Build content
    story = []
    
    # Title
    story.append(Paragraph("SUST Zahlungsstatistik 2025-2026", title_style))
    story.append(Spacer(1, 0.3*cm))
    story.append(Paragraph(f"Stand: {datetime.now().strftime('%d.%m.%Y')}", normal_style))
    story.append(Spacer(1, 0.5*cm))
    
    # Section 1: 2025 Statistics
    story.append(Paragraph("Zahlungsstatistiken 2025 - Nach Haushaltsgröße", heading_style))
    
    table_data_2025 = [
        ["Mitglieder", "Haushalte", "Gesamtpersonen", "Zahlende HH", "Zahlende Personen", "Einnahmen", "Quote"],
        ["2 (Paare)", "33", "66", "33", "66", "€900,00", "100,0%"],
        ["1 (Einzelpersonen)", "109", "109", "102", "102", "€1.575,00", "93,6%"],
        ["GESAMT", "142", "175", "135", "168", "€2.475,00", "95,1%"]
    ]
    
    table = Table(table_data_2025, colWidths=[2.5*cm, 2*cm, 2.2*cm, 2*cm, 2.2*cm, 2*cm, 1.8*cm])
    table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#4472C4')),
        ('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), 9),
        ('BOTTOMPADDING', (0, 0), (-1, 0), 8),
        ('BACKGROUND', (0, -1), (-1, -1), colors.HexColor('#E7E6E6')),
        ('FONTNAME', (0, -1), (-1, -1), 'Helvetica-Bold'),
        ('GRID', (0, 0), (-1, -1), 1, colors.black),
        ('FONTSIZE', (0, 1), (-1, -1), 8),
        ('ROWBACKGROUNDS', (0, 1), (-1, -2), [colors.white, colors.HexColor('#F2F2F2')])
    ]))
    story.append(table)
    story.append(Spacer(1, 0.5*cm))
    
    # Section 2: 2026 Statistics
    story.append(Paragraph("Zahlungsstatistiken 2026 - Nach Haushaltsgröße", heading_style))
    
    table_data_2026 = [
        ["Mitglieder", "Haushalte", "Gesamtpersonen", "Zahlende HH", "Zahlende Personen", "Einnahmen", "Quote"],
        ["2 (Paare)", "33", "66", "32", "64", "€1.095,00", "97,0%"],
        ["1 (Einzelpersonen)", "109", "109", "97", "97", "€1.778,00", "89,0%"],
        ["GESAMT", "142", "175", "129", "161", "€2.873,00", "90,8%"]
    ]
    
    table = Table(table_data_2026, colWidths=[2.5*cm, 2*cm, 2.2*cm, 2*cm, 2.2*cm, 2*cm, 1.8*cm])
    table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#4472C4')),
        ('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), 9),
        ('BOTTOMPADDING', (0, 0), (-1, 0), 8),
        ('BACKGROUND', (0, -1), (-1, -1), colors.HexColor('#E7E6E6')),
        ('FONTNAME', (0, -1), (-1, -1), 'Helvetica-Bold'),
        ('GRID', (0, 0), (-1, -1), 1, colors.black),
        ('FONTSIZE', (0, 1), (-1, -1), 8),
        ('ROWBACKGROUNDS', (0, 1), (-1, -2), [colors.white, colors.HexColor('#F2F2F2')])
    ]))
    story.append(table)
    story.append(Spacer(1, 0.5*cm))
    
    # Section 3: Key insights
    story.append(Paragraph("Wichtigste Erkenntnisse", heading_style))
    
    insights_text = """
    <br/><b>1. Tatsächliche Mitgliedschaft:</b> 175 Personen in 142 primären Haushaltskonten (66 Personen in Paaren / 37,7%, 109 Einzelpersonen / 62,3%)
    <br/><b>2. 2025 Leistung:</b> Paare 100% Quote, Einzelpersonen 93,6%, personenbasierte Quote 96,0%
    <br/><b>3. 2026 Leistung:</b> Paare 97,0% Quote, Einzelpersonen 89,0%, personenbasierte Quote 92,0% (4% Rückgang)
    <br/><b>4. Umsatzwachstum:</b> €2.475 → €2.873 (+16%) trotz Rückgang der Zahlungsquoten
    """
    
    story.append(Paragraph(insights_text, normal_style))
    story.append(Spacer(1, 0.5*cm))
    
    # Page break
    story.append(PageBreak())
    
    # Section 4: Non-paying members
    story.append(Paragraph("Nicht zahlende Mitglieder 2026 (Aktiv und Primär)", heading_style))
    story.append(Spacer(1, 0.3*cm))
    
    non_paying = get_non_paying_members()
    
    table_data = [
        ["#", "HH-Sz.", "Vorname", "Nachname", "E-Mail", "Mitglied seit", "Letzte Zahlung", "Jahr"]
    ]
    
    total_people = 0
    for idx, row in enumerate(non_paying, 1):
        firstname = row[1] or ""
        surname = row[2] or ""
        email = row[3] or "N/A"
        member_since = row[4].strftime("%Y-%m-%d") if row[4] else "N/A"
        member_count = row[5] or 1
        last_payment = row[6].strftime("%Y-%m-%d") if row[6] else "Nie"
        last_year = int(row[7]) if row[7] else 0
        
        total_people += member_count
        
        table_data.append([
            str(idx),
            str(member_count),
            firstname,
            surname,
            email,
            member_since,
            last_payment,
            str(last_year) if last_year > 0 else "-"
        ])
    
    # Add summary
    table_data.append([
        "",
        "13",
        "",
        "",
        "",
        f"Insgesamt: {len(non_paying)} Haushalte / {total_people} Personen",
        "",
        ""
    ])
    
    table = Table(table_data, colWidths=[0.6*cm, 1*cm, 1.8*cm, 2*cm, 3.5*cm, 2*cm, 2*cm, 1*cm])
    table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#4472C4')),
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
        ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
        ('ALIGN', (0, 0), (1, -1), 'CENTER'),
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
        ('FONTSIZE', (0, 0), (-1, 0), 8),
        ('BOTTOMPADDING', (0, 0), (-1, 0), 6),
        ('BACKGROUND', (0, -1), (-1, -1), colors.HexColor('#E7E6E6')),
        ('FONTNAME', (0, -1), (-1, -1), 'Helvetica-Bold'),
        ('GRID', (0, 0), (-1, -1), 1, colors.black),
        ('FONTSIZE', (0, 1), (-1, -1), 7),
        ('ROWBACKGROUNDS', (0, 1), (-1, -2), [colors.white, colors.HexColor('#F2F2F2')])
    ]))
    story.append(table)
    
    # Build PDF
    doc.build(story)
    print(f"PDF-Report erstellt: {pdf_file}")

if __name__ == "__main__":
    create_pdf()