"""
Скрипт для детальной проверки состояния properties в БД
"""
import psycopg2

DB_CONFIG = {
    'host': 'dpg-d5ht8vi4d50c739akh2g-a.virginia-postgres.render.com',
    'port': 5432,
    'database': 'lead_exchange_bk',
    'user': 'lead_exchange_bk_user',
    'password': '8m2gtTRBW0iAr7nY2Aadzz0VcZBEVKYM'
}

try:
    conn = psycopg2.connect(**DB_CONFIG)
    cursor = conn.cursor()

    print("=" * 70)
    print("DETAILED PROPERTIES CHECK")
    print("=" * 70)

    # Проверяем общее количество
    cursor.execute("SELECT COUNT(*) FROM properties")
    total = cursor.fetchone()[0]
    print(f"\n📊 Total properties: {total}")

    # Проверяем по owner_user_id
    cursor.execute("""
        SELECT owner_user_id, COUNT(*)
        FROM properties
        GROUP BY owner_user_id
        ORDER BY COUNT(*) DESC
    """)
    print(f"\n👥 Properties by owner:")
    for owner_id, count in cursor.fetchall():
        print(f"  {owner_id}: {count} properties")

    # Проверяем, существуют ли эти пользователи
    print(f"\n🔍 Checking if owners exist in users table:")
    cursor.execute("""
        SELECT p.owner_user_id, u.email, u.role, COUNT(p.property_id) as prop_count
        FROM properties p
        LEFT JOIN users u ON p.owner_user_id = u.user_id
        GROUP BY p.owner_user_id, u.email, u.role
    """)
    for owner_id, email, role, count in cursor.fetchall():
        if email:
            print(f"  ✅ {owner_id[:8]}... → {email} ({role}) - {count} props")
        else:
            print(f"  ❌ {owner_id[:8]}... → USER NOT FOUND! - {count} props (will be deleted on cascade)")

    # Проверяем ограничения (constraints)
    print(f"\n🔗 Foreign key constraints on properties:")
    cursor.execute("""
        SELECT
            tc.constraint_name,
            tc.constraint_type,
            kcu.column_name,
            ccu.table_name AS foreign_table_name,
            ccu.column_name AS foreign_column_name,
            rc.delete_rule
        FROM information_schema.table_constraints AS tc
        JOIN information_schema.key_column_usage AS kcu
          ON tc.constraint_name = kcu.constraint_name
        JOIN information_schema.constraint_column_usage AS ccu
          ON ccu.constraint_name = tc.constraint_name
        LEFT JOIN information_schema.referential_constraints AS rc
          ON tc.constraint_name = rc.constraint_name
        WHERE tc.table_name = 'properties' AND tc.constraint_type = 'FOREIGN KEY'
    """)

    for constraint_name, constraint_type, column, foreign_table, foreign_column, delete_rule in cursor.fetchall():
        print(f"  {column} → {foreign_table}.{foreign_column}")
        print(f"    Delete rule: {delete_rule}")

    # Показываем примеры объектов
    print(f"\n📄 Sample properties (first 5):")
    cursor.execute("""
        SELECT property_id, title, owner_user_id, created_at
        FROM properties
        ORDER BY created_at DESC
        LIMIT 5
    """)
    for prop_id, title, owner_id, created in cursor.fetchall():
        print(f"  - {title[:50]}...")
        print(f"    ID: {prop_id}")
        print(f"    Owner: {owner_id}")
        print(f"    Created: {created}")

    # Проверяем пользователя f4e8f58b...
    print(f"\n🔍 Checking specific admin user (f4e8f58b-94f4-4e0f-bd85-1b06b8a3f242):")
    cursor.execute("""
        SELECT user_id, email, role, first_name, last_name
        FROM users
        WHERE user_id = 'f4e8f58b-94f4-4e0f-bd85-1b06b8a3f242'
    """)
    result = cursor.fetchone()
    if result:
        print(f"  ✅ User exists: {result[1]} ({result[2]}) - {result[3]} {result[4]}")
    else:
        print(f"  ❌ User NOT FOUND! This is why properties are being deleted!")
        print(f"  ℹ️  Properties reference a user that doesn't exist")

    print("\n" + "=" * 70)

    cursor.close()
    conn.close()

except Exception as e:
    print(f"❌ Error: {e}")
    import traceback
    traceback.print_exc()