#!/usr/bin/env python3
"""
Query script to get active primary members who did NOT pay in 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_non_paying_active_primary_2026():
    """Get active primary members who did not pay in 2026"""
    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 print_non_paying_members(data):
    """Print active primary members who did not pay in 2026"""
    if not data:
        print("No non-paying members found.")
        return
    
    print("\n" + "="*150)
    print(f"ACTIVE PRIMARY MEMBERS WHO DID NOT PAY IN 2026")
    print(f"Total: {len(data)} members")
    print("="*150)
    print(f"{'#':<4} {'Member Cnt':<12} {'First Name':<20} {'Surname':<25} {'Email':<40} {'Member Since':<15} {'Last Payment':<15} {'Last Yr':<10}")
    print("-"*150)
    
    total_people = 0
    count_by_member_count = {}
    
    for idx, row in enumerate(data, 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 "Never"
        last_year = int(row[7]) if row[7] else 0
        
        total_people += member_count
        count_by_member_count[member_count] = count_by_member_count.get(member_count, 0) + 1
        
        print(f"{idx:<4} {member_count:<12} {firstname:<20} {surname:<25} {email:<40} {member_since:<15} {last_payment:<15} {last_year:<10}")
    
    print("-"*150)
    print(f"Total non-paying active primary members: {len(data)}")
    print(f"Total people represented: {total_people}")
    print(f"\nBreakdown by member count:")
    for count in sorted(count_by_member_count.keys(), reverse=True):
        num_households = count_by_member_count[count]
        num_people = count * num_households
        print(f"  - Member count {count}: {num_households} household(s) representing {num_people} people")

if __name__ == "__main__":
    print("Querying SUST database for active primary members who did not pay in 2026...")
    
    non_paying = get_non_paying_active_primary_2026()
    print_non_paying_members(non_paying)
    
    print("\n" + "="*140 + "\n")