# database/connection.py
import sqlite3
import os

def get_connection():
    # Force the path to be absolute relative to the project folder
    db_path = os.path.abspath("spanish_trainer.db")
    return sqlite3.connect(db_path)

def init_db():
    print("🛠️ Constructing relational database schema...")
    conn = get_connection()
    cursor = conn.cursor()
    
    # Enable foreign keys explicitly for this connection instance
    cursor.execute("PRAGMA foreign_keys = ON;")
    
    # 1. Phrases Table (Holds individual localized text strings)
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS phrases (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            text TEXT NOT NULL,
            language TEXT NOT NULL,
            textbook TEXT,
            unit INTEGER,
            source_context TEXT,
            word_type TEXT,
            grammar_note TEXT,
            voice_gender TEXT DEFAULT 'female',
            base_speed REAL DEFAULT 1.0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
    """)
    
    # 2. Translations Table (The relational tie binding English and Spanish IDs together)
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS translations (
            translation_id INTEGER PRIMARY KEY AUTOINCREMENT,
            source_phrase_id INTEGER,
            target_phrase_id INTEGER,
            deck_name TEXT DEFAULT 'General',
            FOREIGN KEY (source_phrase_id) REFERENCES phrases(id) ON DELETE CASCADE,
            FOREIGN KEY (target_phrase_id) REFERENCES phrases(id) ON DELETE CASCADE
        );
    """)
    
    # 3. Audio Tracks Table (Links phrase items to local disk storage clips)
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS audio_tracks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            phrase_id INTEGER,
            file_path TEXT NOT NULL,
            FOREIGN KEY (phrase_id) REFERENCES phrases(id) ON DELETE CASCADE
        );
    """)
    
    # CRITICAL: Force SQLite to physically commit the table architectures to disk
    conn.commit()
    
    # Verification Sweep: Double-check that tables actually exist before we hand over control
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = [row[0] for row in cursor.fetchall()]
    conn.close()
    
    print(f"✅ Database tables physically confirmed on disk: {tables}")