#!/usr/bin/env python3
"""
Inspect current database state to understand the duplicate key issue
"""
import os
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from dotenv import load_dotenv

load_dotenv()

# Use dev server database
DATABASE_URL = "postgresql://postgres:RchhcpaUKZuZuMOvB5kwCP1weLBnAG6tNMXE5FHdk8AwCvolBMALYFVYRM7WCl9x@10.9.23.11:5001/membership_demo"

engine = create_engine(DATABASE_URL)
Session = sessionmaker(bind=engine)
db = Session()

print("=" * 80)
print("DATABASE STATE INSPECTION")
print("=" * 80)

# Check current counts
result = db.execute(text("SELECT COUNT(*) FROM permissions"))
perm_count = result.scalar()
print(f"\nTotal permissions: {perm_count}")

result = db.execute(text("SELECT COUNT(*) FROM role_permissions"))
rp_count = result.scalar()
print(f"Total role_permissions: {rp_count}")

if rp_count > 0:
    print("\n" + "=" * 80)
    print("CURRENT ROLE-PERMISSION MAPPINGS (grouped by role)")
    print("=" * 80)

    result = db.execute(text("""
        SELECT
            rp.role,
            r.name as role_name,
            COUNT(*) as permission_count
        FROM role_permissions rp
        LEFT JOIN roles r ON rp.role_id = r.id
        GROUP BY rp.role, r.name
        ORDER BY rp.role
    """))

    for row in result.fetchall():
        print(f"  {row[0]:15} ({row[1]:20}): {row[2]} permissions")

    print("\n" + "=" * 80)
    print("CHECKING FOR DUPLICATES")
    print("=" * 80)

    # Check if there are actual duplicates
    result = db.execute(text("""
        SELECT role, permission_id, COUNT(*) as count
        FROM role_permissions
        GROUP BY role, permission_id
        HAVING COUNT(*) > 1
    """))

    duplicates = result.fetchall()
    if duplicates:
        print(f"\n⚠️  Found {len(duplicates)} duplicate (role, permission_id) pairs:")
        for dup in duplicates[:10]:  # Show first 10
            print(f"  role={dup[0]}, permission_id={dup[1]}, count={dup[2]}")
    else:
        print("\n✓ No duplicate (role, permission_id) pairs found")

    print("\n" + "=" * 80)
    print("CHECKING SPECIFIC ADMIN PERMISSIONS")
    print("=" * 80)

    # Check how many admin permissions exist
    result = db.execute(text("""
        SELECT COUNT(*)
        FROM role_permissions
        WHERE role = 'admin'
    """))
    admin_count = result.scalar()
    print(f"\nAdmin has {admin_count} permission assignments")

    # Check if the specific permission mentioned in error exists
    result = db.execute(text("""
        SELECT rp.id, rp.role, p.code, p.name
        FROM role_permissions rp
        JOIN permissions p ON rp.permission_id = p.id
        WHERE rp.role = 'admin'
        ORDER BY p.code
        LIMIT 20
    """))

    print("\nFirst 20 admin permissions:")
    for row in result.fetchall():
        print(f"  {row[1]:10} -> {row[2]:30} ({row[3]})")

db.close()