#!/usr/bin/env python3
"""Show all tables and their structures in the database."""

import sys
import os

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

from sqlalchemy import inspect, text
from backend.db.session import engine, get_database_url
from backend.db.models import Base


def show_table_structure(table_name: str, inspector):
    """Show the structure of a table."""
    print(f"\n{'='*80}")
    print(f"Table: {table_name}")
    print(f"{'='*80}")
    
    # Get columns
    columns = inspector.get_columns(table_name)
    print("\nColumns:")
    print(f"{'Name':<30} {'Type':<25} {'Nullable':<10} {'Primary Key':<12} {'Default'}")
    print("-" * 100)
    
    for col in columns:
        col_type = str(col['type'])
        nullable = "Yes" if col['nullable'] else "No"
        primary_key = "Yes" if col.get('primary_key', False) else "No"
        default = str(col.get('default', ''))[:30] if col.get('default') else ''
        print(f"{col['name']:<30} {col_type:<25} {nullable:<10} {primary_key:<12} {default}")
    
    # Get indexes
    indexes = inspector.get_indexes(table_name)
    if indexes:
        print("\nIndexes:")
        for idx in indexes:
            unique = "UNIQUE" if idx.get('unique', False) else ""
            columns_str = ", ".join(idx['column_names'])
            print(f"  {idx['name']}: {columns_str} {unique}")
    
    # Get foreign keys
    foreign_keys = inspector.get_foreign_keys(table_name)
    if foreign_keys:
        print("\nForeign Keys:")
        for fk in foreign_keys:
            constrained_cols = ", ".join(fk['constrained_columns'])
            referred_table = fk['referred_table']
            referred_cols = ", ".join(fk['referred_columns'])
            print(f"  {constrained_cols} -> {referred_table}({referred_cols})")


def show_all_tables():
    """Show all tables and their structures."""
    db_url = get_database_url()
    print(f"Database: {db_url}")
    print(f"\n{'='*80}")
    
    # Create inspector
    inspector = inspect(engine)
    
    # Get all table names
    table_names = inspector.get_table_names()
    
    if not table_names:
        print("No tables found in database.")
        print("\nTables should be created on web app startup.")
        print("\nHere are the table structures from models:")
        
        # Show from models instead
        from backend.db.models import Photo, Person, Face, PersonEmbedding, Tag, PhotoTag
        
        models = [
            ("photos", Photo),
            ("people", Person),
            ("faces", Face),
            ("person_embeddings", PersonEmbedding),
            ("tags", Tag),
            ("photo_tags", PhotoTag),
        ]
        
        for table_name, model in models:
            print(f"\n{'='*80}")
            print(f"Table: {table_name}")
            print(f"{'='*80}")
            print("\nColumns:")
            for col in model.__table__.columns:
                nullable = "Yes" if col.nullable else "No"
                primary_key = "Yes" if col.primary_key else "No"
                default = str(col.default) if col.default else ''
                print(f"  {col.name:<30} {col.type!s:<25} Nullable: {nullable:<10} PK: {primary_key:<12} Default: {default}")
            
            # Show indexes
            indexes = model.__table__.indexes
            if indexes:
                print("\nIndexes:")
                for idx in indexes:
                    unique = "UNIQUE" if idx.unique else ""
                    cols = ", ".join([c.name for c in idx.columns])
                    print(f"  {idx.name}: {cols} {unique}")
            
            # Show foreign keys
            fks = [fk for fk in model.__table__.foreign_keys]
            if fks:
                print("\nForeign Keys:")
                for fk in fks:
                    print(f"  {fk.parent.name} -> {fk.column.table.name}({fk.column.name})")
        
        return
    
    print(f"\nFound {len(table_names)} table(s):")
    for table_name in sorted(table_names):
        print(f"  - {table_name}")
    
    # Show structure for each table
    for table_name in sorted(table_names):
        show_table_structure(table_name, inspector)


if __name__ == "__main__":
    try:
        show_all_tables()
    except Exception as e:
        print(f"❌ Error showing tables: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)

