# database.py
import os
import sys
import sqlite3
from PyQt6.QtCore import QSettings

APP_NAME = "SpanishVoiceTrainer"
DEFAULT_DB_FILENAME = "spanish_trainer.db"


def get_default_db_path() -> str:
    """Returns standard macOS Application Support path:
    ~/Library/Application Support/SpanishVoiceTrainer/spanish_trainer.db
    """
    app_support_dir = os.path.expanduser(
        f"~/Library/Application Support/{APP_NAME}"
    )
    os.makedirs(app_support_dir, exist_ok=True)
    return os.path.join(app_support_dir, DEFAULT_DB_FILENAME)


def get_db_path() -> str:
    """Retrieves database path cleanly based on execution environment.

    - In packaged app mode (sys.frozen): strictly isolates data inside
      Application Support unless a valid custom production path is chosen.
      Heals stale settings pointing to local dev source paths.
    - In dev mode: allows fallback to local project directory.
    """
    qs = QSettings(APP_NAME, "Settings")
    custom_path = qs.value("database_path", type=str)

    # 1. Check custom path saved in QSettings
    if custom_path and os.path.exists(custom_path):
        # Safeguard for packaged production app:
        # Ignore custom paths that point back into local development source folders
        if getattr(sys, "frozen", False) and "01_Projects" in custom_path:
            default_path = get_default_db_path()
            qs.setValue("database_path", default_path)  # Repair stale setting
            return default_path
        return custom_path

    # 2. Development mode fallback (uncompiled python runtime)
    if not getattr(sys, "frozen", False):
        local_dev_db = os.path.join(
            os.path.dirname(os.path.abspath(__file__)), DEFAULT_DB_FILENAME
        )
        if os.path.exists(local_dev_db):
            return local_dev_db

    # 3. Default production fallback
    default_path = get_default_db_path()
    qs.setValue("database_path", default_path)
    return default_path


def set_db_path(new_path: str):
    """Updates active database path in user preferences."""
    qs = QSettings(APP_NAME, "Settings")
    qs.setValue("database_path", new_path)


def get_connection():
    """Establishes connection to the active SQLite database."""
    db_path = get_db_path()
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    return conn


def ensure_database_populated():
    """Initializes tables and migrates schemas if running against a new or existing database file."""
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # 1. Unified translations table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS translations (
                translation_id INTEGER PRIMARY KEY AUTOINCREMENT,
                es_text TEXT NOT NULL,
                en_text TEXT NOT NULL,
                source_context TEXT,
                tags TEXT,
                notes TEXT,
                anki_notes TEXT DEFAULT '',
                gender TEXT DEFAULT 'Female',
                sort_order INTEGER DEFAULT 0
            );
        """)

        # Safely migrate existing databases that do not yet have the sort_order column
        cursor.execute("PRAGMA table_info(translations);")
        columns = [row["name"] for row in cursor.fetchall()]
        if "sort_order" not in columns:
            cursor.execute("ALTER TABLE translations ADD COLUMN sort_order INTEGER DEFAULT 0;")

        # 2. Key-value configuration table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS settings (
                key TEXT PRIMARY KEY,
                value TEXT
            );
        """)
        conn.commit()
    finally:
        conn.close()


# ==========================================
# SETTINGS CRUD FUNCTIONS
# ==========================================


def load_all_settings():
    """Fetches all system configuration properties into a flat Python dictionary."""
    conn = get_connection()
    cursor = conn.cursor()
    settings_dict = {}
    try:
        cursor.execute("SELECT settings.key, settings.value FROM settings;")
        for row in cursor.fetchall():
            settings_dict[row["key"]] = row["value"]
    finally:
        conn.close()
    return settings_dict


def save_setting_to_db(key, value):
    """Inserts or replaces an application configuration entry."""
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            """
            INSERT OR REPLACE INTO settings (key, value)
            VALUES (?, ?);
        """,
            (key, value),
        )
        conn.commit()
    finally:
        conn.close()


# ==========================================
# TRANSLATIONS CRUD FUNCTIONS
# ==========================================


def get_all_translations_explicit():
    """Retrieves all records using explicit, table-qualified column declarations sorted by sort_order."""
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT 
                translations.translation_id,
                translations.es_text,
                translations.en_text,
                translations.source_context,
                translations.tags,
                translations.notes,
                translations.anki_notes,
                translations.gender,
                translations.sort_order
            FROM translations
            ORDER BY translations.sort_order ASC, translations.translation_id ASC;
        """)
        return [dict(row) for row in cursor.fetchall()]
    finally:
        conn.close()


def get_translation_by_id(translation_id):
    """Loads a single unified record row for specific inspection or editing."""
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            """
            SELECT 
                translations.translation_id,
                translations.es_text,
                translations.en_text,
                translations.source_context,
                translations.tags,
                translations.notes,
                translations.anki_notes,
                translations.gender,
                translations.sort_order
            FROM translations
            WHERE translations.translation_id = ?;
        """,
            (translation_id,),
        )
        row = cursor.fetchone()
        return dict(row) if row else None
    finally:
        conn.close()


def update_translation_record(
    translation_id,
    es_text,
    en_text,
    source_context,
    tags,
    notes,
    gender,
    anki_notes,
    sort_order=0,
):
    """Saves interface edits directly back into the table using named arguments."""
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            """
            UPDATE translations
            SET 
                es_text = :es,
                en_text = :en,
                source_context = :ctx,
                tags = :tags,
                notes = :notes,
                gender = :gender,
                anki_notes = :anki,
                sort_order = :sort_order
            WHERE translation_id = :id;
        """,
            {
                "id": translation_id,
                "es": es_text,
                "en": en_text,
                "ctx": source_context,
                "tags": tags,
                "notes": notes,
                "gender": gender,
                "anki": anki_notes,
                "sort_order": sort_order,
            },
        )
        conn.commit()
    finally:
        conn.close()


def delete_translation_record(translation_id):
    """Permanently drops a phrase card row from the data index."""
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            """
            DELETE FROM translations
            WHERE translations.translation_id = ?;
        """,
            (translation_id,),
        )
        conn.commit()
    finally:
        conn.close()


def insert_translation_record(
    es_text, en_text, source_context, tags, notes, gender, anki_notes, sort_order=0
):
    """Inserts a new record using named arguments."""
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            """
            INSERT INTO translations (es_text, en_text, source_context, tags, notes, gender, anki_notes, sort_order)
            VALUES (:es, :en, :ctx, :tags, :notes, :gender, :anki, :sort_order);
        """,
            {
                "en": en_text,
                "es": es_text,
                "ctx": source_context,
                "tags": tags,
                "notes": notes,
                "anki": anki_notes,
                "gender": gender,
                "sort_order": sort_order,
            },
        )
        conn.commit()
    finally:
        conn.close()