#!/usr/bin/env python3
"""
Query script to get member information:
- Current active members
- Members who became inactive in 2025
- Members who died in 2025
"""

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

from sqlalchemy import text
from models.database import SessionLocal, engine
from models.models import Members, MemberStateLog

def get_active_members():
    """Get all currently active members"""
    session = SessionLocal()
    try:
        query = session.query(Members).filter(
            Members.member_active == True,  # use current active state instead
            Members.member_died == None
        ).order_by(Members.member_surname, Members.member_firstname)
        
        members = query.all()
        return members
    finally:
        session.close()

def get_inactive_2025():
    """Get members who became inactive in 2025"""
    session = SessionLocal()
    try:
        # Query for members who have a state change to inactive in 2025
        query = text("""
            SELECT DISTINCT m.id, m.member_firstname, m.member_surname, m.member_email, 
                   msl.changed_at, msl.reason
            FROM members m
            JOIN member_state_log msl ON m.id = msl.member_id
            WHERE msl.state_code = 'I'
            AND EXTRACT(YEAR FROM msl.changed_at) = 2025
            ORDER BY m.member_surname, m.member_firstname
        """)
        
        result = session.execute(query).fetchall()
        return result
    except Exception as e:
        print(f"Error querying inactive members: {e}")
        # Fallback to alternative query
        return []
    finally:
        session.close()

def get_died_2025():
    """Get members who died in 2025"""
    session = SessionLocal()
    try:
        query = session.query(Members).filter(
            Members.member_died != None,
            Members.member_died >= datetime(2025, 1, 1),
            Members.member_died < datetime(2026, 1, 1)
        ).order_by(Members.member_surname, Members.member_firstname)
        
        members = query.all()
        return members
    finally:
        session.close()

def print_active_members(members):
    """Print active members in formatted table"""
    if not members:
        print("No active members found.")
        return
    
    print("\n" + "="*100)
    print(f"CURRENT ACTIVE MEMBERS ({len(members)} total)")
    print("="*100)
    print(f"{'#':<4} {'First Name':<20} {'Surname':<25} {'Email':<35} {'Member Since':<12}")
    print("-"*100)
    
    for idx, member in enumerate(members, 1):
        email = member.member_email or "N/A"
        member_since = member.member_membership_start.strftime("%Y-%m-%d") if member.member_membership_start else "N/A"
        print(f"{idx:<4} {(member.member_firstname or ''):<20} {(member.member_surname or ''):<25} {email:<35} {member_since:<12}")

def print_inactive_2025(members):
    """Print members who became inactive in 2025"""
    if not members:
        print("No members became inactive in 2025.")
        return
    
    print("\n" + "="*100)
    print(f"MEMBERS WHO BECAME INACTIVE IN 2025 ({len(members)} total)")
    print("="*100)
    print(f"{'#':<4} {'First Name':<20} {'Surname':<25} {'Email':<35} {'Inactive Date':<15} {'Reason':<20}")
    print("-"*100)
    
    for idx, row in enumerate(members, 1):
        name = row[1] or ""
        surname = row[2] or ""
        email = row[3] or "N/A"
        changed_at = row[4].strftime("%Y-%m-%d") if row[4] else "N/A"
        reason = row[5] or "N/A"
        print(f"{idx:<4} {name:<20} {surname:<25} {email:<35} {changed_at:<15} {reason:<20}")

def print_died_2025(members):
    """Print members who died in 2025"""
    if not members:
        print("No members died in 2025.")
        return
    
    print("\n" + "="*100)
    print(f"MEMBERS WHO DIED IN 2025 ({len(members)} total)")
    print("="*100)
    print(f"{'#':<4} {'First Name':<20} {'Surname':<25} {'Email':<35} {'Date of Death':<15}")
    print("-"*100)
    
    for idx, member in enumerate(members, 1):
        email = member.member_email or "N/A"
        died_date = member.member_died.strftime("%Y-%m-%d") if member.member_died else "N/A"
        print(f"{idx:<4} {(member.member_firstname or ''):<20} {(member.member_surname or ''):<25} {email:<35} {died_date:<15}")

if __name__ == "__main__":
    print("Querying SUST member database...")
    
    # Get active members
    active_members = get_active_members()
    print_active_members(active_members)
    
    # Get members who became inactive in 2025
    inactive_2025 = get_inactive_2025()
    print_inactive_2025(inactive_2025)
    
    # Get members who died in 2025
    died_2025 = get_died_2025()
    print_died_2025(died_2025)
    
    print("\n" + "="*100 + "\n")