"""Check citation refresh status after background run"""
import asyncio, sys
from sqlalchemy import text
from sqlalchemy.ext.asyncio import create_async_engine

DB = 'postgresql+asyncpg://scilit:scilit_prod_2026@postgres:5432/scilit'

async def main():
    engine = create_async_engine(DB)
    async with engine.connect() as c:
        r = await c.execute(text("SELECT COUNT(*) FROM global_literature WHERE cited_by_count > 0"))
        print(f'Articles with citations > 0: {r.scalar()}')
        r = await c.execute(text("SELECT COUNT(*) FROM global_literature WHERE cited_by_count IS NOT NULL"))
        checked = r.scalar()
        total = (await c.execute(text("SELECT COUNT(*) FROM global_literature"))).scalar()
        print(f'Total checked: {checked}')
        print(f'Total records: {total}')
        print(f'Progress: {checked}/{total} ({(checked/total*100) if total else 0:.1f}%)')
        r = await c.execute(text("SELECT cited_by_count, COUNT(*) FROM global_literature WHERE cited_by_count > 0 GROUP BY cited_by_count ORDER BY cited_by_count DESC LIMIT 5"))
        print('Top 5 citation counts:')
        for row in r:
            print(f'  cited_by_count={row[0]}: {row[1]}')
        r = await c.execute(text("SELECT pmid, cited_by_count FROM global_literature WHERE cited_by_count > 0 ORDER BY cited_by_count DESC LIMIT 10"))
        print('Most cited articles:')
        for row in r:
            print(f'  PMID={row.pmid}: {row.cited_by_count}')
    await engine.dispose()

asyncio.run(main())