#!/usr/bin/env python3
"""Supabase database diagnostic script"""
import psycopg2

conn = psycopg2.connect(
    host='aws-1-eu-central-1.pooler.supabase.com',
    port=5432,
    dbname='postgres',
    user='postgres.mitlbxlqjibfcxswgmbq',
    password='Veteroner06.,'
)
cur = conn.cursor()

# 1. Check if user_profiles table exists
cur.execute("""
SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'public' AND table_name = 'user_profiles'
""")
print('=== user_profiles table exists:', bool(cur.fetchone()))

# 2. Check all tables in public schema
cur.execute("""
SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'public' ORDER BY table_name
""")
print('\n=== Public tables:')
for row in cur.fetchall():
    print('  ' + row[0])

# 3. Check RLS policies on user_profiles
cur.execute("""
SELECT policyname, cmd, qual, with_check FROM pg_policies 
WHERE tablename = 'user_profiles'
""")
print('\n=== user_profiles RLS policies:')
for row in cur.fetchall():
    print('  policy=' + str(row[0]) + ' cmd=' + str(row[1]))
    print('    qual=' + str(row[2]))
    print('    with_check=' + str(row[3]))

# 4. Check handle_new_user trigger
cur.execute("""
SELECT trigger_name, event_manipulation, action_statement 
FROM information_schema.triggers 
WHERE event_object_schema = 'auth' AND event_object_table = 'users'
""")
print('\n=== Triggers on auth.users:')
rows = cur.fetchall()
if rows:
    for row in rows:
        print('  ' + str(row[0]) + ' (' + str(row[1]) + '): ' + str(row[2]))
else:
    print('  NO TRIGGERS FOUND!')

# 5. Check app_settings - registration_enabled
try:
    cur.execute("""
    SELECT key, value FROM app_settings WHERE key = 'registration_enabled'
    """)
    result = cur.fetchone()
    print('\n=== registration_enabled: ' + str(result))
except Exception as e:
    print('\n=== app_settings error: ' + str(e))
    conn.rollback()

# 6. Count existing users
cur.execute('SELECT count(*) FROM auth.users')
print('\n=== Total auth.users: ' + str(cur.fetchone()[0]))

# 7. Check user_profiles count
try:
    cur.execute('SELECT count(*) FROM user_profiles')
    print('=== Total user_profiles: ' + str(cur.fetchone()[0]))
except Exception as e:
    print('=== user_profiles count error: ' + str(e))
    conn.rollback()

# 8. Check if handle_new_user function exists
cur.execute("""
SELECT routine_name, routine_type FROM information_schema.routines 
WHERE routine_name = 'handle_new_user' AND routine_schema = 'public'
""")
result = cur.fetchall()
print('\n=== handle_new_user function: ' + str(result))

# 9. Check is_admin function exists
cur.execute("""
SELECT routine_name, routine_type FROM information_schema.routines 
WHERE routine_name = 'is_admin' AND routine_schema = 'public'
""")
result = cur.fetchall()
print('=== is_admin function: ' + str(result))

# 10. Check RLS enabled on user_profiles
cur.execute("""
SELECT relname, relrowsecurity, relforcerowsecurity 
FROM pg_class WHERE relname = 'user_profiles'
""")
row = cur.fetchone()
if row:
    print('\n=== RLS on user_profiles: enabled=' + str(row[1]) + ' forced=' + str(row[2]))

# 11. Check grants on user_profiles
cur.execute("""
SELECT grantee, privilege_type FROM information_schema.table_privileges 
WHERE table_name = 'user_profiles' AND table_schema = 'public'
""")
print('\n=== Grants on user_profiles:')
for row in cur.fetchall():
    print('  ' + str(row[0]) + ': ' + str(row[1]))

# 12. Check auth.users details
cur.execute("""
SELECT id, email, created_at, confirmed_at, email_confirmed_at 
FROM auth.users ORDER BY created_at DESC LIMIT 5
""")
print('\n=== Recent auth.users:')
for row in cur.fetchall():
    print('  id=' + str(row[0])[:8] + '... email=' + str(row[1]) + ' created=' + str(row[2]) + ' confirmed=' + str(row[3]) + ' email_confirmed=' + str(row[4]))

# 13. Check Supabase auth config
cur.execute("""
SELECT * FROM auth.schema_migrations ORDER BY version DESC LIMIT 5
""")
print('\n=== Auth schema migrations (latest 5):')
for row in cur.fetchall():
    print('  ' + str(row[0]))

# 14. Check if email confirmation is required
try:
    cur.execute("""
    SELECT key, value FROM auth.config WHERE key IN ('mailer_autoconfirm', 'enable_signup')
    """)
    print('\n=== Auth config:')
    for row in cur.fetchall():
        print('  ' + str(row[0]) + ' = ' + str(row[1]))
except Exception as e:
    print('\n=== auth.config not accessible: ' + str(e))
    conn.rollback()

conn.close()
print('\n=== Done!')