"""
Script to migrate any session-stored vocabulary to the database.

This script simulates what happens when a user loads the vocabulary page:
1. We load the vocabulary from the database
2. We check for any session-stored vocabulary
3. We migrate those items to the database
"""

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

def migrate_session_vocabulary():
    """Find and migrate any session-stored vocabulary to the database"""
    
    print("\n" + "=" * 60)
    print(" SESSION VOCABULARY MIGRATION ")
    print("=" * 60)
    
    # 1. Find the database file
    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"✓ Using database at {instance_db}")
    elif os.path.exists(root_db):
        db_path = root_db
        print(f"✓ Using database at {root_db}")
    else:
        print(f"✗ ERROR: Database not found in expected locations")
        return False
    
    # Sample session data for testing (in a real app, this would come from Flask's session)
    test_session_data = [
        {
            'word': 'Eloquent',
            'pronunciation': '/ˈɛləkwənt/',
            'definition': 'Fluent or persuasive in speaking or writing',
            'synonyms': 'articulate,expressive,fluent',
            'antonyms': 'inarticulate,unclear,halting',
            'example': 'She was an eloquent speaker who could move any audience.',
            'timestamp': datetime.utcnow().isoformat()
        },
        {
            'word': 'Perseverance',
            'pronunciation': '/ˌpɜrsəˈvɪrəns/',
            'definition': 'Persistence in doing something despite difficulty or delay in achieving success',
            'synonyms': 'persistence,determination,tenacity',
            'antonyms': 'irresolution,laziness,indecision',
            'example': 'His perseverance was rewarded when he finally succeeded.',
            'timestamp': datetime.utcnow().isoformat()
        }
    ]
    
    print(f"\nFound {len(test_session_data)} vocabulary items in simulated session")
    
    # Ask user for the student_id to associate with these words
    student_id = 1  # Default for testing
    print(f"\nUsing student ID: {student_id}")
    
    # Connect to database
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Verify user exists
        cursor.execute("SELECT id, username FROM users WHERE id = ?", (student_id,))
        user = cursor.fetchone()
        if not user:
            print(f"✗ ERROR: User with ID {student_id} not found")
            return False
            
        print(f"✓ Found user: {user[1]} (ID: {user[0]})")
        
        # Check if vocabulary table exists
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='vocabulary'")
        if not cursor.fetchone():
            # Create vocabulary table
            print("Creating vocabulary table...")
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS vocabulary (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                student_id INTEGER NOT NULL,
                word VARCHAR(100) NOT NULL,
                pronunciation VARCHAR(100),
                definition TEXT NOT NULL,
                synonyms TEXT,
                antonyms TEXT,
                example TEXT,
                date_added DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (student_id) REFERENCES users(id)
            )
            ''')
            print("✓ Vocabulary table created")
        
        # Migrate each session item to the database
        print("\nMigrating vocabulary items to database...")
        for i, item in enumerate(test_session_data, 1):
            print(f"  [{i}/{len(test_session_data)}] Migrating '{item['word']}'...")
            
            # Check if this word already exists for this user
            cursor.execute(
                "SELECT id FROM vocabulary WHERE student_id = ? AND word = ?", 
                (student_id, item['word'])
            )
            
            if cursor.fetchone():
                print(f"    - Word already exists in database, skipping")
                continue
                
            # Insert new word
            cursor.execute('''
            INSERT INTO vocabulary (
                student_id, word, pronunciation, definition, 
                synonyms, antonyms, example, date_added
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                student_id,
                item['word'],
                item.get('pronunciation', ''),
                item['definition'],
                item.get('synonyms', ''),
                item.get('antonyms', ''),
                item.get('example', ''),
                item.get('timestamp', datetime.utcnow().isoformat())
            ))
            
            print(f"    ✓ Added to database")
        
        # Commit changes
        conn.commit()
        
        # Verify migration
        cursor.execute("SELECT COUNT(*) FROM vocabulary WHERE student_id = ?", (student_id,))
        count = cursor.fetchone()[0]
        
        print(f"\n✓ Migration complete! User now has {count} vocabulary items in database")
        
        # Display all items
        cursor.execute(
            "SELECT id, word, definition FROM vocabulary WHERE student_id = ? ORDER BY date_added DESC", 
            (student_id,)
        )
        
        items = cursor.fetchall()
        
        print("\nAll vocabulary items for this user:")
        print("-" * 60)
        for item in items:
            item_id, word, definition = item
            if len(definition) > 40:
                definition = definition[:37] + "..."
            print(f"ID {item_id}: {word} - {definition}")
            
        conn.close()
        
        print("\n" + "=" * 60)
        print(" MIGRATION COMPLETED SUCCESSFULLY ")
        print("=" * 60)
        
        return True
        
    except Exception as e:
        print(f"\n✗ ERROR: {e}")
        return False

if __name__ == "__main__":
    migrate_session_vocabulary()