import sys
sys.path.append('Backend')
from app.config.database import engine
from sqlalchemy import text

with engine.connect() as conn:
    res = conn.execute(text("SELECT kode_prodi, COUNT(*) FROM alumni GROUP BY kode_prodi;")).fetchall()
    print("Alumni per kode_prodi:")
    for r in res:
        print(f"  {r[0]}: {r[1]}")
    
    nims = conn.execute(text("SELECT SUBSTRING(nim, 1, 4), COUNT(*) FROM alumni GROUP BY SUBSTRING(nim, 1, 4);")).fetchall()
    print("\nNIM prefix count:")
    for n in nims:
        print(f"  {n[0]}: {n[1]}")