"""
Simple script to verify vocabulary entries directly from the database
"""
import os
import sqlite3
from datetime import datetime

def list_vocabulary_entries():
    """List all vocabulary entries directly from 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()
        
        # Check if vocabulary table exists
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='vocabulary'")
        if not cursor.fetchone():
            print("ERROR: vocabulary table does not exist in the database.")
            return False
            
        # Get all vocabulary entries
        cursor.execute("""
        SELECT v.id, v.word, v.definition, v.student_id, u.username 
        FROM vocabulary v
        JOIN users u ON v.student_id = u.id
        ORDER BY v.date_added DESC
        """)
        
        entries = cursor.fetchall()
        
        print(f"\nFound {len(entries)} vocabulary entries:")
        print("-" * 70)
        print(f"{'ID':4} | {'Word':<15} | {'Definition':<30} | {'User':<15}")
        print("-" * 70)
        
        for entry in entries:
            entry_id, word, definition, student_id, username = entry
            # Truncate long definition
            if len(definition) > 30:
                definition = definition[:27] + "..."
            print(f"{entry_id:<4} | {word:<15} | {definition:<30} | {username:<15}")
            
        # Close the connection
        conn.close()
        return True
        
    except Exception as e:
        print(f"ERROR: {e}")
        return False

if __name__ == "__main__":
    list_vocabulary_entries()