#!/usr/bin/env python3
"""
Query script to get payment information for members in 2026 only:
- Payments by active members in 2026
- Payments by members who became inactive in 2025 in 2026
- Payments by members who died in 2025 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_active_members_payments_2026():
    """Get 2026 payment data for active members"""
    session = SessionLocal()
    try:
        query = text("""
            SELECT m.id, m.member_firstname, m.member_surname, m.member_email,
                   COUNT(p.id) as payment_count,
                   SUM(p.payment_amount) as total_paid,
                   MAX(p.payment_date) as last_payment_date
            FROM members m
            LEFT JOIN payments p ON m.id = p.member_id AND EXTRACT(YEAR FROM p.payment_year) = 2026
            WHERE m.member_active = true AND m.member_died IS NULL AND m.member_primary = true
            GROUP BY m.id, m.member_firstname, m.member_surname, m.member_email
            HAVING COUNT(p.id) > 0 OR (COUNT(p.id) = 0 AND m.member_active = true)
            ORDER BY m.member_surname, m.member_firstname
        """)
        result = session.execute(query).fetchall()
        return result
    finally:
        session.close()

def get_inactive_2025_payments_2026():
    """Get 2026 payment data for members who became inactive in 2025"""
    session = SessionLocal()
    try:
        query = text("""
            SELECT DISTINCT m.id, m.member_firstname, m.member_surname, m.member_email,
                   COUNT(p.id) as payment_count,
                   SUM(p.payment_amount) as total_paid,
                   MAX(p.payment_date) as last_payment_date
            FROM members m
            LEFT JOIN payments p ON m.id = p.member_id AND EXTRACT(YEAR FROM p.payment_year) = 2026
            JOIN member_state_log msl ON m.id = msl.member_id
            WHERE msl.state_code = 'I'
            AND EXTRACT(YEAR FROM msl.changed_at) = 2025
            AND m.member_primary = true
            GROUP BY m.id, m.member_firstname, m.member_surname, m.member_email
            ORDER BY m.member_surname, m.member_firstname
        """)
        result = session.execute(query).fetchall()
        return result
    finally:
        session.close()

def get_died_2025_payments_2026():
    """Get 2026 payment data for members who died in 2025"""
    session = SessionLocal()
    try:
        query = text("""
            SELECT m.id, m.member_firstname, m.member_surname, m.member_email, m.member_died,
                   COUNT(p.id) as payment_count,
                   SUM(p.payment_amount) as total_paid,
                   MAX(p.payment_date) as last_payment_date
            FROM members m
            LEFT JOIN payments p ON m.id = p.member_id AND EXTRACT(YEAR FROM p.payment_year) = 2026
            WHERE m.member_died >= '2025-01-01' AND m.member_died < '2026-01-01'
            AND m.member_primary = true
            GROUP BY m.id, m.member_firstname, m.member_surname, m.member_email, m.member_died
            ORDER BY m.member_surname, m.member_firstname
        """)
        result = session.execute(query).fetchall()
        return result
    finally:
        session.close()

def print_active_payments_2026(data):
    """Print 2026 payment data for active members"""
    if not data:
        print("No payment data found for active members in 2026.")
        return
    
    # Filter to only those with payments
    paying_members = [row for row in data if row[4] and row[4] > 0]
    all_members = data
    
    print("\n" + "="*120)
    print(f"2026 PAYMENTS BY ACTIVE MEMBERS")
    print(f"Total Members with payments: {len(paying_members)} | Total Active Members: {len(all_members)}")
    print("="*120)
    print(f"{'#':<4} {'First Name':<20} {'Surname':<25} {'Email':<35} {'Count':<8} {'Total Paid (€)':<15} {'Last Payment':<15}")
    print("-"*120)
    
    total_all = 0
    count_all = 0
    
    for idx, row in enumerate(paying_members, 1):
        payment_count = row[4] or 0
        total_paid = float(row[5]) if row[5] else 0
        last_payment = row[6].strftime("%Y-%m-%d") if row[6] else "N/A"
        
        total_all += total_paid
        count_all += payment_count
        
        print(f"{idx:<4} {(row[1] or ''):<20} {(row[2] or ''):<25} {(row[3] or 'N/A'):<35} {payment_count:<8} {total_paid:<15.2f} {last_payment:<15}")
    
    print("-"*120)
    print(f"{'TOTAL':<79} {count_all:<8} {total_all:<15.2f}")

def print_inactive_2025_payments_2026(data):
    """Print 2026 payment data for members who became inactive in 2025"""
    if not data:
        print("No payment data found for members who became inactive in 2025.")
        return
    
    # Filter to only those with payments
    paying_members = [row for row in data if row[4] and row[4] > 0]
    all_members = data
    
    print("\n" + "="*120)
    print(f"2026 PAYMENTS BY MEMBERS WHO BECAME INACTIVE IN 2025")
    print(f"Total Members with payments: {len(paying_members)} | Total Members Inactive in 2025: {len(all_members)}")
    print("="*120)
    print(f"{'#':<4} {'First Name':<20} {'Surname':<25} {'Email':<35} {'Count':<8} {'Total Paid (€)':<15} {'Last Payment':<15}")
    print("-"*120)
    
    total_all = 0
    count_all = 0
    
    for idx, row in enumerate(paying_members, 1):
        payment_count = row[4] or 0
        total_paid = float(row[5]) if row[5] else 0
        last_payment = row[6].strftime("%Y-%m-%d") if row[6] else "N/A"
        
        total_all += total_paid
        count_all += payment_count
        
        print(f"{idx:<4} {(row[1] or ''):<20} {(row[2] or ''):<25} {(row[3] or 'N/A'):<35} {payment_count:<8} {total_paid:<15.2f} {last_payment:<15}")
    
    if paying_members:
        print("-"*120)
        print(f"{'TOTAL':<79} {count_all:<8} {total_all:<15.2f}")

def print_died_2025_payments_2026(data):
    """Print 2026 payment data for members who died in 2025"""
    if not data:
        print("No payment data found for members who died in 2025.")
        return
    
    # Filter to only those with payments
    paying_members = [row for row in data if row[5] and row[5] > 0]
    all_members = data
    
    print("\n" + "="*120)
    print(f"2026 PAYMENTS BY MEMBERS WHO DIED IN 2025")
    print(f"Total Members with payments: {len(paying_members)} | Total Members Died in 2025: {len(all_members)}")
    print("="*120)
    print(f"{'#':<4} {'First Name':<20} {'Surname':<25} {'Email':<35} {'Count':<8} {'Total Paid (€)':<15} {'Last Payment':<15}")
    print("-"*120)
    
    total_all = 0
    count_all = 0
    
    for idx, row in enumerate(paying_members, 1):
        payment_count = row[5] or 0
        total_paid = float(row[6]) if row[6] else 0
        last_payment = row[7].strftime("%Y-%m-%d") if row[7] else "N/A"
        
        total_all += total_paid
        count_all += payment_count
        
        print(f"{idx:<4} {(row[1] or ''):<20} {(row[2] or ''):<25} {(row[3] or 'N/A'):<35} {payment_count:<8} {total_paid:<15.2f} {last_payment:<15}")
    
    if paying_members:
        print("-"*120)
        print(f"{'TOTAL':<79} {count_all:<8} {total_all:<15.2f}")

if __name__ == "__main__":
    print("Querying SUST member payment data for 2026...")
    
    # Get payment data for active members
    active_payments = get_active_members_payments_2026()
    print_active_payments_2026(active_payments)
    
    # Get payment data for members who became inactive in 2025
    inactive_2025_payments = get_inactive_2025_payments_2026()
    print_inactive_2025_payments_2026(inactive_2025_payments)
    
    # Get payment data for members who died in 2025
    died_2025_payments = get_died_2025_payments_2026()
    print_died_2025_payments_2026(died_2025_payments)
    
    print("\n" + "="*120 + "\n")