"""
Fix stale branch sessions — clears all sessions so users log in fresh.
Run on server: DJANGO_SETTINGS_MODULE=branch_system.settings_production python diagnose_production.py
"""
import os, django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
django.setup()

from django.db import connection

print("\n" + "="*60)
print("RAW DATABASE COUNTS (bypasses all Django filters)")
print("="*60)

queries = [
    ("All users",           "SELECT COUNT(*) FROM users"),
    ("Borrowers",           "SELECT COUNT(*) FROM users WHERE role='borrower'"),
    ("Active borrowers",    "SELECT COUNT(*) FROM users WHERE role='borrower' AND status='active'"),
    ("Inactive borrowers",  "SELECT COUNT(*) FROM users WHERE role='borrower' AND status!='active'"),
    ("Loan applications",   "SELECT COUNT(*) FROM loan_applications"),
    ("Loans (all)",         "SELECT COUNT(*) FROM loans"),
    ("Active loans",        "SELECT COUNT(*) FROM loans WHERE status='active'"),
    ("Repayments",          "SELECT COUNT(*) FROM repayments"),
    ("Expenses",            "SELECT COUNT(*) FROM expenses"),
]

with connection.cursor() as cursor:
    for label, sql in queries:
        try:
            cursor.execute(sql)
            count = cursor.fetchone()[0]
            print(f"  {label:<25} {count}")
        except Exception as e:
            print(f"  {label:<25} ERROR: {e}")

print("\n" + "="*60)
print("BORROWER STATUS BREAKDOWN")
print("="*60)
with connection.cursor() as cursor:
    try:
        cursor.execute("SELECT status, is_active, COUNT(*) FROM users WHERE role='borrower' GROUP BY status, is_active")
        rows = cursor.fetchall()
        for row in rows:
            print(f"  status={row[0]!r:<20} is_active={row[1]}  count={row[2]}")
        if not rows:
            print("  No borrowers found at all!")
    except Exception as e:
        print(f"  ERROR: {e}")

print("\n" + "="*60)
print("RECENT DJANGO MIGRATIONS (last 10)")
print("="*60)
with connection.cursor() as cursor:
    try:
        cursor.execute("SELECT app, name, applied FROM django_migrations ORDER BY applied DESC LIMIT 10")
        for row in cursor.fetchall():
            print(f"  {row[0]:<15} {row[1]:<50} {row[2]}")
    except Exception as e:
        print(f"  ERROR: {e}")

print("\n" + "="*60)
print("BRANCH TABLE")
print("="*60)
with connection.cursor() as cursor:
    try:
        cursor.execute("SELECT id, name, is_active, is_main_branch FROM users_branch")
        rows = cursor.fetchall()
        for row in rows:
            print(f"  id={str(row[0])[:8]}... name={row[1]!r} active={row[2]} main={row[3]}")
        if not rows:
            print("  No branches found!")
    except Exception as e:
        print(f"  ERROR: {e}")

print("\n" + "="*60)
print("SESSION TABLE (active sessions count)")
print("="*60)
with connection.cursor() as cursor:
    try:
        cursor.execute("SELECT COUNT(*) FROM django_session WHERE expire_date > NOW()")
        print(f"  Active sessions: {cursor.fetchone()[0]}")
    except Exception as e:
        print(f"  ERROR: {e}")

print("\n" + "="*60)
print("DIAGNOSIS COMPLETE")
print("="*60 + "\n")