#!/usr/bin/env python3
"""
Query script to get payment information with member_count breakdown for 2025 and 2026
"""

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

from sqlalchemy import text
from models.database import SessionLocal

def get_payment_stats_by_year(year):
    """Get payment statistics for active primary members by member_count"""
    session = SessionLocal()
    try:
        query = text(f"""
            SELECT COALESCE(m.member_count, 1) as member_count,
                   COUNT(DISTINCT m.id) as num_households,
                   SUM(m.member_count) as total_people,
                   SUM(CASE WHEN p.id IS NOT NULL THEN 1 ELSE 0 END) as households_paid,
                   SUM(CASE WHEN p.id IS NOT NULL THEN m.member_count ELSE 0 END) as people_paid,
                   SUM(COALESCE(p.payment_amount, 0)) as total_revenue
            FROM members m
            LEFT JOIN payments p ON m.id = p.member_id AND EXTRACT(YEAR FROM p.payment_year) = {year}
            WHERE m.member_active = true 
            AND m.member_died IS NULL 
            AND m.member_primary = true
            GROUP BY COALESCE(m.member_count, 1)
            ORDER BY COALESCE(m.member_count, 1) DESC
        """)
        result = session.execute(query).fetchall()
        return result
    finally:
        session.close()

def print_statistics(year, data):
    """Print payment statistics"""
    if not data:
        print(f"No data found for {year}.")
        return
    
    print("\n" + "="*110)
    print(f"PAYMENT STATISTICS FOR ACTIVE PRIMARY MEMBERS - {year}")
    print("="*110)
    print(f"{'Member Cnt':<12} {'Households':<15} {'Total People':<15} {'Paid (HH)':<15} {'Paid (People)':<15} {'Revenue (€)':<15}")
    print("-"*110)
    
    total_households = 0
    total_people = 0
    total_paid_households = 0
    total_paid_people = 0
    total_revenue = 0
    
    for row in data:
        member_count = int(row[0])
        num_households = row[1]
        total_people_count = row[2]
        households_paid = row[3]
        people_paid = row[4]
        revenue = float(row[5]) if row[5] else 0
        
        total_households += num_households
        total_people += total_people_count
        total_paid_households += households_paid
        total_paid_people += people_paid
        total_revenue += revenue
        
        pct_households = (households_paid / num_households * 100) if num_households > 0 else 0
        pct_people = (people_paid / total_people_count * 100) if total_people_count > 0 else 0
        
        print(f"{member_count:<12} {num_households:<15} {total_people_count:<15} {households_paid:<15} {people_paid:<15} {revenue:<15.2f}")
        print(f"{'':12} {f'{pct_households:.1f}%':<15} {f'{pct_people:.1f}%':<15}")
    
    print("-"*110)
    pct_households_total = (total_paid_households / total_households * 100) if total_households > 0 else 0
    pct_people_total = (total_paid_people / total_people * 100) if total_people > 0 else 0
    print(f"{'TOTAL':<12} {total_households:<15} {total_people:<15} {total_paid_households:<15} {total_paid_people:<15} {total_revenue:<15.2f}")
    print(f"{'':12} {f'{pct_households_total:.1f}%':<15} {f'{pct_people_total:.1f}%':<15}")
    print("="*110)

if __name__ == "__main__":
    print("Querying SUST payment statistics by member count...")
    
    # Get 2025 statistics
    print_statistics(2025, get_payment_stats_by_year(2025))
    
    # Get 2026 statistics
    print_statistics(2026, get_payment_stats_by_year(2026))
    
    print("\n")