"""
Vocabulary System Diagnostic Tool

This script performs diagnostics on the vocabulary system, checking:
1. Database connection and table existence
2. Flask session simulation
3. Route functionality simulation
4. Cross-device consistency verification

Run this script to diagnose issues with the vocabulary system.
"""

import os
import sqlite3
import sys
from datetime import datetime
import json

def print_header(title):
    """Print a formatted header"""
    print("\n" + "=" * 60)
    print(f" {title} ")
    print("=" * 60)

def check_database():
    """Check database connectivity and vocabulary table existence"""
    print_header("DATABASE DIAGNOSTICS")
    
    # Find database
    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
        print(f"✓ Database found at {instance_db}")
    elif os.path.exists(root_db):
        db_path = root_db
        print(f"✓ Database found at {root_db}")
    else:
        print(f"✗ ERROR: Database not found in expected locations")
        return None
    
    # Try connecting to database
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        print("✓ Database connection successful")
        
        # Check if vocabulary table exists
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='vocabulary'")
        if cursor.fetchone():
            print("✓ Vocabulary table exists")
            
            # Check table structure
            cursor.execute("PRAGMA table_info(vocabulary)")
            columns = {row[1] for row in cursor.fetchall()}
            expected_columns = {'id', 'student_id', 'word', 'pronunciation', 'definition', 
                               'synonyms', 'antonyms', 'example', 'date_added'}
            
            missing_columns = expected_columns - columns
            if not missing_columns:
                print("✓ Vocabulary table structure is correct")
            else:
                print(f"✗ Vocabulary table is missing columns: {', '.join(missing_columns)}")
            
            # Count entries
            cursor.execute("SELECT COUNT(*) FROM vocabulary")
            count = cursor.fetchone()[0]
            print(f"✓ Vocabulary table has {count} entries")
            
        else:
            print("✗ Vocabulary table does not exist")
        
        return conn
        
    except Exception as e:
        print(f"✗ Database error: {e}")
        return None

def check_session_simulation():
    """Simulate Flask session behavior"""
    print_header("SESSION DIAGNOSTICS (SIMULATION)")
    
    print("In a proper implementation, vocabulary should NOT be stored in the session")
    print("All vocabulary entries should be stored in the database")
    print("Session should only be used for temporary data, like flash messages")
    
    print("\nSimulating session behavior...")
    session = {
        'user_id': 1,
        'vocabulary': [
            {
                'word': 'Temporary',
                'definition': 'This word should not be here - it should be in the DB'
            }
        ]
    }
    
    print("\nSession contents:")
    print(json.dumps(session, indent=2))
    
    print("\n⚠ WARNING: If 'vocabulary' exists in the session, it may cause cross-device inconsistency")
    print("Flask sessions are typically stored in browser cookies or server-side session stores")
    print("This means vocabulary stored in session will not be available across different devices")
    
    print("\nRECOMMENDATION: Ensure all vocabulary is stored in the database, not session")
    
    return session

def simulate_route_functionality(conn, session):
    """Simulate route functionality"""
    print_header("ROUTE FUNCTIONALITY SIMULATION")
    
    if not conn:
        print("✗ Cannot simulate routes without database connection")
        return
    
    cursor = conn.cursor()
    student_id = session.get('user_id', 1)
    
    print(f"Simulating operations for user ID: {student_id}")
    
    # Simulate vocabulary retrieval
    print("\n1. Simulating GET /vocabulary route:")
    try:
        # Get user
        cursor.execute("SELECT username FROM users WHERE id = ?", (student_id,))
        user = cursor.fetchone()
        
        if not user:
            print(f"✗ User with ID {student_id} not found")
        else:
            print(f"✓ Found user: {user[0]}")
            
            # Get vocabulary
            cursor.execute("""
                SELECT word, definition FROM vocabulary 
                WHERE student_id = ? 
                ORDER BY date_added DESC
            """, (student_id,))
            
            items = cursor.fetchall()
            print(f"✓ Found {len(items)} vocabulary items in database")
            
            # Show session vocabulary (this should be empty in a proper implementation)
            session_vocab = session.get('vocabulary', [])
            if session_vocab:
                print(f"⚠ WARNING: Found {len(session_vocab)} vocabulary items in session")
                print("    These items will not be available across devices")
            else:
                print("✓ No vocabulary items stored in session (good)")
    except Exception as e:
        print(f"✗ Error simulating vocabulary route: {e}")
    
    # Simulate adding vocabulary
    print("\n2. Simulating POST /add_vocabulary route:")
    try:
        new_word = {
            'word': f"Test_{datetime.now().strftime('%H%M%S')}",
            'pronunciation': '/test/',
            'definition': 'A test word added by the diagnostic tool',
            'synonyms': 'check,verification',
            'antonyms': 'failure,error',
            'example': 'This is a test word.',
        }
        
        print(f"Adding word: {new_word['word']}")
        
        cursor.execute('''
        INSERT INTO vocabulary (
            student_id, word, pronunciation, definition, 
            synonyms, antonyms, example, date_added
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            student_id,
            new_word['word'],
            new_word['pronunciation'],
            new_word['definition'],
            new_word['synonyms'],
            new_word['antonyms'],
            new_word['example'],
            datetime.utcnow().isoformat()
        ))
        
        conn.commit()
        print(f"✓ Successfully added test word to database")
        
        # Verify it was added
        cursor.execute(
            "SELECT id FROM vocabulary WHERE student_id = ? AND word = ?", 
            (student_id, new_word['word'])
        )
        result = cursor.fetchone()
        if result:
            print(f"✓ Verified word was added with ID: {result[0]}")
            
            # Remove the test word to clean up
            cursor.execute("DELETE FROM vocabulary WHERE id = ?", (result[0],))
            conn.commit()
            print(f"✓ Test word removed from database")
        else:
            print(f"✗ Failed to verify word was added to database")
    
    except Exception as e:
        print(f"✗ Error simulating add vocabulary route: {e}")
    
    print("\n3. Cross-device consistency check:")
    print("""
If vocabulary is stored in the database (and NOT in the session):
✓ Changes made on one device will be visible on other devices
✓ Words added on one device will appear on other devices
✓ Words deleted on one device will be removed from other devices

If vocabulary is stored in session (not recommended):
✗ Changes made on one device will NOT be visible on other devices
✗ Each device will have its own separate list of words
✗ Users will be confused why words appear/disappear across devices
    """)

def check_student_py_implementation():
    """Check if the student.py implementation is correct"""
    print_header("CODE IMPLEMENTATION CHECK")
    
    student_py_path = os.path.join(
        os.path.dirname(os.path.abspath(__file__)),
        'app', 'routes', 'student.py'
    )
    
    if not os.path.exists(student_py_path):
        print(f"✗ Could not find student.py at {student_py_path}")
        return
    
    try:
        with open(student_py_path, 'r', encoding='utf-8') as f:
            content = f.read()
        
        print(f"✓ Found student.py ({len(content)} bytes)")
        
        # Check for session usage
        session_vocab_patterns = [
            "session['vocabulary']",
            "session.get('vocabulary'",
            "session.setdefault('vocabulary'"
        ]
        
        found_session_vocab = False
        for pattern in session_vocab_patterns:
            if pattern in content:
                found_session_vocab = True
                print(f"⚠ WARNING: Found possible session vocabulary usage: {pattern}")
        
        if not found_session_vocab:
            print("✓ No obvious session vocabulary usage found (good)")
        
        # Check for database usage
        db_patterns = [
            "Vocabulary.query",
            "db.session.query(Vocabulary)",
            "INSERT INTO vocabulary"
        ]
        
        found_db_usage = False
        for pattern in db_patterns:
            if pattern in content:
                found_db_usage = True
                print(f"✓ Found database vocabulary usage: {pattern}")
        
        if not found_db_usage:
            print("⚠ WARNING: No obvious database vocabulary usage found")
        
    except Exception as e:
        print(f"✗ Error checking student.py: {e}")

def main():
    """Main function"""
    print("\n" + "=" * 60)
    print(" VOCABULARY SYSTEM DIAGNOSTIC TOOL ")
    print("=" * 60)
    print("Running diagnostics on vocabulary system...")
    
    # Run checks
    conn = check_database()
    session = check_session_simulation()
    simulate_route_functionality(conn, session)
    check_student_py_implementation()
    
    if conn:
        conn.close()
    
    # Summary
    print_header("DIAGNOSTIC SUMMARY")
    print("""
To ensure cross-device consistency for vocabulary:

1. ALL vocabulary items MUST be stored in the database
2. NO vocabulary should be stored in the session
3. Routes should ALWAYS read from and write to the database
4. Session should only be used for temporary data like flash messages
5. Any session-based storage should be migrated to the database

If you've fixed all these issues, vocabulary will be consistent across devices.
""")

if __name__ == "__main__":
    main()