import sqlite3
import os
from pathlib import Path
from datetime import datetime

def create_vocabulary_table():
    """Create the vocabulary table in the SQLite database"""
    
    # Find the database file
    # Check both common locations
    root_path = Path(os.path.dirname(os.path.abspath(__file__)))
    db_path = root_path / "app.db"
    instance_db_path = root_path / "instance" / "app.db"
    
    if instance_db_path.exists():
        db_file = instance_db_path
    elif db_path.exists():
        db_file = db_path
    else:
        print(f"ERROR: Database file not found in {db_path} or {instance_db_path}")
        return False
    
    try:
        # Connect to the database
        print(f"Connecting to database at {db_file}")
        conn = sqlite3.connect(db_file)
        cursor = conn.cursor()
        
        # Check if vocabulary table already exists
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='vocabulary'")
        if cursor.fetchone():
            print("Vocabulary table already exists.")
            return True
        
        # Create the vocabulary table
        print("Creating vocabulary table...")
        cursor.execute('''
        CREATE TABLE 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,
            FOREIGN KEY (student_id) REFERENCES user (id)
        )
        ''')
        
        # Commit the changes
        conn.commit()
        print("Vocabulary table created successfully!")
        
        # Add a test entry
        print("Adding a test vocabulary entry...")
        now = datetime.utcnow().isoformat()
        cursor.execute('''
        INSERT INTO vocabulary (student_id, word, pronunciation, definition, synonyms, antonyms, example, date_added)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            1,  # Assuming user ID 1 exists
            "Serendipity",
            "/ˌsɛrənˈdɪpɪti/",
            "The occurrence and development of events by chance in a happy or beneficial way",
            "Chance,Luck,Fortune,Happenstance",
            "Plan,Intent,Design,Misfortune",
            "Finding that rare book was pure serendipity.",
            now
        ))
        
        # Commit the changes
        conn.commit()
        print("Test vocabulary entry added!")
        
        # Close the connection
        conn.close()
        return True
    
    except Exception as e:
        print(f"ERROR: {e}")
        return False

if __name__ == "__main__":
    print("=" * 60)
    print(" Vocabulary Database Setup ")
    print("=" * 60)
    print("\nThis script will create the vocabulary table in your SQLite database")
    print("if it doesn't exist and add a sample vocabulary entry.\n")
    
    if create_vocabulary_table():
        print("\nDatabase setup completed successfully!")
        print("\nYou can now use the vocabulary feature in the application.")
        print("Navigate to the student dashboard and click on 'My Vocabulary'")
        print("to view and manage your vocabulary words.")
    else:
        print("\nDatabase setup failed. Please check the error message above.")
        print("\nIf you continue to experience issues, please refer to")
        print("docs/vocabulary_setup.md for alternative setup methods.")