import asyncio
import os
import sys
from dotenv import load_dotenv

load_dotenv()
database_url = os.environ.get("DATABASE_URL", "")
if not database_url:
    from backend.config.settings import settings
    database_url = settings.DATABASE_URL
dsn = database_url.replace("postgresql+asyncpg://", "postgresql://")

async def inspect_and_migrate():
    import asyncpg
    print(f"Connecting to: {dsn.split('@')[-1]}")
    conn = await asyncpg.connect(dsn)
    
    # Check approvals columns
    cols = await conn.fetch("SELECT column_name FROM information_schema.columns WHERE table_name = 'approvals'")
    col_names = [c["column_name"] for c in cols]
    print(f"Approvals columns: {col_names}")
    
    # Check if 'status' vs 'state'
    status_col = "status" if "status" in col_names else "state"
    print(f"Using approvals status column: {status_col}")
    
    # Execute full schema
    from backend.infrastructure.database.postgres_store import init_schema
    await init_schema()
    print("Schema initialization succeeded!")
    
    # List all tables in pabot_db
    tables = await conn.fetch("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name")
    print("\nVerified Tables in pabot_db:")
    for t in tables:
        count = await conn.fetchval(f"SELECT COUNT(*) FROM {t['table_name']}")
        print(f"  • {t['table_name']}: {count} records")
        
    await conn.close()

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