"""
Database Inspection Script
Connect to PostgreSQL and examine existing structure
"""

import asyncio
import asyncpg


async def inspect_database():
    """Connect to PostgreSQL and inspect tables"""
    # Connection string
    conn_string = "postgres://avnadmin:AVNS_-lwx_03K2q6KDrMmSoX@stagingmirchi1-stagingmirchi.f.aivencloud.com:14387/defaultdb?sslmode=require"
    
    try:
        conn = await asyncpg.connect(conn_string)
        print("✅ Connected to PostgreSQL database\n")
        
        # Get all tables
        tables = await conn.fetch("""
            SELECT table_name 
            FROM information_schema.tables 
            WHERE table_schema = 'public' 
            ORDER BY table_name
        """)
        
        print(f"📋 Found {len(tables)} tables:\n")
        
        for table in tables:
            table_name = table['table_name']
            print(f"\n{'='*60}")
            print(f"TABLE: {table_name}")
            print('='*60)
            
            # Get columns
            columns = await conn.fetch("""
                SELECT column_name, data_type, character_maximum_length, is_nullable, column_default
                FROM information_schema.columns
                WHERE table_schema = 'public' AND table_name = $1
                ORDER BY ordinal_position
            """, table_name)
            
            for col in columns:
                dtype = col['data_type']
                if col['character_maximum_length']:
                    dtype += f"({col['character_maximum_length']})"
                nullable = "NULL" if col['is_nullable'] == 'YES' else "NOT NULL"
                default = f"DEFAULT {col['column_default']}" if col['column_default'] else ""
                print(f"  {col['column_name']}: {dtype} {nullable} {default}")
            
            # Get row count
            try:
                count = await conn.fetchval(f'SELECT COUNT(*) FROM "{table_name}"')
                print(f"\n  📊 Row count: {count}")
            except:
                pass
        
        await conn.close()
        print("\n\n✅ Inspection complete!")
        
    except Exception as e:
        print(f"❌ Error: {e}")


if __name__ == "__main__":
    asyncio.run(inspect_database())