"""
List all tables in the SQLite database
"""
import os
import sqlite3

def list_database_tables():
    """List all tables in the database"""
    
    # Determine the database path
    root_dir = os.path.dirname(os.path.abspath(__file__))
    instance_db = os.path.join(root_dir, 'instance', 'app.db')
    root_db = os.path.join(root_dir, 'app.db')
    
    if os.path.exists(instance_db):
        db_path = instance_db
    elif os.path.exists(root_db):
        db_path = root_db
    else:
        print(f"ERROR: Database not found at {instance_db} or {root_db}")
        return False
    
    print(f"Using database at: {db_path}")
    
    try:
        # Connect to the database
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Get list of all tables
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = cursor.fetchall()
        
        print(f"Found {len(tables)} tables:")
        for i, table in enumerate(tables, 1):
            print(f"{i}. {table[0]}")
        
        # For each table, show column structure
        for table_name in [table[0] for table in tables]:
            print(f"\n{'-' * 40}")
            print(f"Table: {table_name}")
            print(f"{'-' * 40}")
            
            cursor.execute(f"PRAGMA table_info({table_name})")
            columns = cursor.fetchall()
            
            for col in columns:
                col_id, col_name, col_type, not_null, default_val, is_pk = col
                print(f"- {col_name} ({col_type}){' PRIMARY KEY' if is_pk else ''}{' NOT NULL' if not_null else ''}")
            
            # Show sample row if available
            cursor.execute(f"SELECT * FROM {table_name} LIMIT 1")
            sample = cursor.fetchone()
            if sample:
                print("\nSample data:")
                for i, col in enumerate(columns):
                    print(f"  {col[1]}: {sample[i]}")
            else:
                print("\nNo data in this table.")
        
        conn.close()
        return True
        
    except Exception as e:
        print(f"ERROR: {e}")
        return False

if __name__ == "__main__":
    list_database_tables()