#!/usr/bin/env python3
"""
Count how many admin users exist in the database.
Admins = users linked to ROLE_ADMINISTRATOR via alf_authority_alias.

Usage (from project root):
  python scripts/count_admins.py
"""

import sys

# Use backend config and DB connection
try:
    from backend.app.config import get_db_config
except ImportError:
    print("Run from project root: python scripts/count_admins.py", file=sys.stderr)
    sys.exit(1)


def main():
    config = get_db_config()
    if config.get("type") != "mysql":
        print("This script supports MySQL only. DB_TYPE is:", config.get("type"))
        sys.exit(1)
    try:
        import pymysql
    except ImportError:
        print("Install pymysql: pip install pymysql", file=sys.stderr)
        sys.exit(1)
    conn = pymysql.connect(
        host=config.get("host", "localhost"),
        port=config.get("port", 3306),
        user=config.get("username", "root"),
        password=config.get("password", ""),
        database=config.get("database", "LRS43"),
        charset=config.get("charset", "utf8mb4"),
    )
    cursor = conn.cursor()
    # ROLE_ADMINISTRATOR id in alf_authority (usually 2)
    cursor.execute("SELECT id FROM alf_authority WHERE authority = %s", ("ROLE_ADMINISTRATOR",))
    row = cursor.fetchone()
    if not row:
        print("ROLE_ADMINISTRATOR not found in alf_authority. No admins defined.")
        cursor.close()
        conn.close()
        return
    role_admin_id = row[0]
    # Count users linked to this role
    cursor.execute(
        """
        SELECT COUNT(*) FROM alf_authority_alias a
        INNER JOIN alf_authority u ON u.id = a.auth_id
        WHERE a.alias_id = %s
          AND u.authority NOT LIKE 'ROLE_%%'
          AND u.authority NOT LIKE 'GROUP_%%'
        """,
        (role_admin_id,),
    )
    count = cursor.fetchone()[0]
    # List them
    cursor.execute(
        """
        SELECT u.id, u.authority
        FROM alf_authority_alias a
        INNER JOIN alf_authority u ON u.id = a.auth_id
        WHERE a.alias_id = %s
          AND u.authority NOT LIKE 'ROLE_%%'
          AND u.authority NOT LIKE 'GROUP_%%'
        ORDER BY u.authority
        """,
        (role_admin_id,),
    )
    admins = cursor.fetchall()
    cursor.close()
    conn.close()
    print(f"Admins in DB: {count}")
    for uid, username in admins:
        print(f"  - {username} (id={uid})")


if __name__ == "__main__":
    main()