"""
Check probe data in database
"""
import asyncio
import sys
import os

# Add backend to path
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from sqlalchemy import create_engine, text
from app.config import settings


def check_probes():
    """Check probe data directly with SQL"""
    engine = create_engine(settings.database_url.replace('+asyncpg', ''))

    with engine.connect() as conn:
        # Check all celestial bodies
        result = conn.execute(text("""
            SELECT
                cb.id,
                cb.name,
                cb.name_zh,
                cb.type,
                cb.is_active,
                COUNT(p.id) as position_count
            FROM celestial_bodies cb
            LEFT JOIN positions p ON cb.id = p.body_id
            GROUP BY cb.id, cb.name, cb.name_zh, cb.type, cb.is_active
            ORDER BY cb.type, cb.name
        """))

        print("All Celestial Bodies:")
        print("=" * 100)
        for row in result:
            print(f"ID: {row.id:15s} | Name: {row.name:20s} | Type: {row.type:15s} | Active: {str(row.is_active):5s} | Positions: {row.position_count}")

        print("\n" + "=" * 100)
        print("\nProbes only:")
        print("=" * 100)

        result = conn.execute(text("""
            SELECT
                cb.id,
                cb.name,
                cb.name_zh,
                cb.is_active,
                COUNT(p.id) as position_count
            FROM celestial_bodies cb
            LEFT JOIN positions p ON cb.id = p.body_id
            WHERE cb.type = 'probe'
            GROUP BY cb.id, cb.name, cb.name_zh, cb.is_active
            ORDER BY cb.name
        """))

        for row in result:
            print(f"ID: {row.id:15s} | Name: {row.name:20s} ({row.name_zh}) | Active: {str(row.is_active):5s} | Positions: {row.position_count}")


if __name__ == "__main__":
    check_probes()