#!/usr/bin/env python3
"""
Check what tables exist in the punimtag main database and their record counts.
"""

from __future__ import annotations

import sys
from pathlib import Path

# Add project root to path
project_root = Path(__file__).parent.parent
sys.path.insert(0, str(project_root))

from sqlalchemy import create_engine, inspect, text
from backend.db.session import get_database_url


def check_database_tables() -> None:
    """Check all tables in the database and their record counts."""
    database_url = get_database_url()
    
    print("=" * 80)
    print("PUNIMTAG MAIN DATABASE - TABLE INFORMATION")
    print("=" * 80)
    print(f"\nDatabase URL: {database_url.replace('://', '://****') if '://' in database_url else database_url}\n")
    
    # Create engine
    connect_args = {}
    if database_url.startswith("sqlite"):
        connect_args = {"check_same_thread": False}
    
    engine = create_engine(database_url, connect_args=connect_args)
    
    try:
        # Get inspector to list tables
        inspector = inspect(engine)
        all_tables = inspector.get_table_names()
        
        if not all_tables:
            print("❌ No tables found in database.")
            return
        
        print(f"Found {len(all_tables)} tables:\n")
        
        # Expected tables from models
        expected_tables = {
            "photos",
            "people",
            "faces",
            "person_encodings",
            "tags",
            "phototaglinkage",
            "photo_favorites",
            "users",
            "photo_person_linkage",
            "role_permissions",
        }
        
        # Connect and query each table
        with engine.connect() as conn:
            print(f"{'Table Name':<30} {'Record Count':<15} {'Status'}")
            print("-" * 80)
            
            for table_name in sorted(all_tables):
                # Skip SQLite system tables
                if table_name.startswith("sqlite_"):
                    continue
                
                try:
                    # Get record count
                    if database_url.startswith("sqlite"):
                        result = conn.execute(text(f"SELECT COUNT(*) FROM {table_name}"))
                    else:
                        result = conn.execute(text(f'SELECT COUNT(*) FROM "{table_name}"'))
                    
                    count = result.scalar()
                    
                    # Check if it's an expected table
                    status = "✅ Expected" if table_name in expected_tables else "⚠️  Unexpected"
                    
                    print(f"{table_name:<30} {count:<15} {status}")
                    
                except Exception as e:
                    print(f"{table_name:<30} {'ERROR':<15} ❌ {str(e)[:50]}")
            
            print("-" * 80)
            
            # Summary
            print("\n📊 Summary:")
            with engine.connect() as conn:
                total_records = 0
                tables_with_data = 0
                for table_name in sorted(all_tables):
                    if table_name.startswith("sqlite_"):
                        continue
                    try:
                        if database_url.startswith("sqlite"):
                            result = conn.execute(text(f"SELECT COUNT(*) FROM {table_name}"))
                        else:
                            result = conn.execute(text(f'SELECT COUNT(*) FROM "{table_name}"'))
                        count = result.scalar()
                        total_records += count
                        if count > 0:
                            tables_with_data += 1
                    except:
                        pass
                
                print(f"  Total tables: {len([t for t in all_tables if not t.startswith('sqlite_')])}")
                print(f"  Tables with records: {tables_with_data}")
                print(f"  Total records across all tables: {total_records:,}")
            
            # Check for missing expected tables
            missing_tables = expected_tables - set(all_tables)
            if missing_tables:
                print(f"\n⚠️  Missing expected tables: {', '.join(sorted(missing_tables))}")
            
            # Check for unexpected tables
            unexpected_tables = set(all_tables) - expected_tables - {"alembic_version"}
            unexpected_tables = {t for t in unexpected_tables if not t.startswith("sqlite_")}
            if unexpected_tables:
                print(f"\nℹ️  Additional tables found: {', '.join(sorted(unexpected_tables))}")
    
    except Exception as e:
        print(f"❌ Error connecting to database: {e}")
        import traceback
        traceback.print_exc()
        return
    
    print("\n" + "=" * 80)


if __name__ == "__main__":
    check_database_tables()

