import os
import sqlite3
from pprint import pprint

# Check both possible database locations
db_paths = [
    'app.db',                  # Root directory db
    os.path.join('instance', 'app.db')  # Instance directory db
]

for db_path in db_paths:
    if os.path.exists(db_path):
        print(f"\nExamining database at: {db_path}")
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Check if users table exists
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='users';")
        if cursor.fetchone():
            # Get all users
            cursor.execute("SELECT id, username, email, role, is_approved, password_hash FROM users;")
            users = cursor.fetchall()
            
            print(f"Found {len(users)} users:")
            for user in users:
                print(f"ID: {user[0]}, Username: {user[1]}, Email: {user[2]}, Role: {user[3]}, Approved: {user[4]}")
                print(f"Password hash: {user[5][:20]}...")  # Show just the beginning of the hash
                print("-" * 50)
        else:
            print("No 'users' table found in this database.")
        
        # List all tables
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = cursor.fetchall()
        print(f"Tables in database: {', '.join([t[0] for t in tables])}")
        
        conn.close()
    else:
        print(f"\nNo database found at: {db_path}")