"""
SQLite database module for user settings persistence.
"""

import sqlite3
import os
from datetime import datetime

# Default settings
DEFAULT_SETTINGS = {
    # Microphone caption settings
    'font_family': 'Arial, sans-serif',
    'font_size': 32,
    'font_weight': 'normal',
    'text_color': '#ffffff',
    'background_color': '#1a1a2e',
    'background_opacity': 0.9,
    'max_words': 30,
    'text_align': 'center',
    'padding': 20,
    'border_radius': 10,
    # Desktop audio caption settings
    'desktop_font_family': 'Arial, sans-serif',
    'desktop_font_size': 28,
    'desktop_font_weight': 'normal',
    'desktop_text_color': '#90EE90',
    'desktop_background_color': '#1a2e1a',
    'desktop_background_opacity': 0.9,
    'desktop_max_words': 30,
    'desktop_text_align': 'center',
    'desktop_padding': 20,
    'desktop_border_radius': 10,
}


def get_db_path():
    """Get database path from environment or use default."""
    return os.environ.get('DATABASE_PATH', 'data/settings.db')


def get_connection():
    """Create a database connection."""
    db_path = get_db_path()

    # Ensure directory exists
    os.makedirs(os.path.dirname(db_path), exist_ok=True)

    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    """Initialize the database with the settings table."""
    conn = get_connection()
    cursor = conn.cursor()

    # Check if table exists
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='user_settings'")
    table_exists = cursor.fetchone() is not None

    if table_exists:
        # Check if we need to migrate from max_lines to max_words
        cursor.execute("PRAGMA table_info(user_settings)")
        columns = [col[1] for col in cursor.fetchall()]

        if 'max_lines' in columns and 'max_words' not in columns:
            # Add max_words column
            cursor.execute('ALTER TABLE user_settings ADD COLUMN max_words INTEGER DEFAULT 30')
            conn.commit()

        # Add desktop audio columns if they don't exist
        if 'desktop_font_family' not in columns:
            desktop_columns = [
                ('desktop_font_family', 'TEXT', "'Arial, sans-serif'"),
                ('desktop_font_size', 'INTEGER', '28'),
                ('desktop_font_weight', 'TEXT', "'normal'"),
                ('desktop_text_color', 'TEXT', "'#90EE90'"),
                ('desktop_background_color', 'TEXT', "'#1a2e1a'"),
                ('desktop_background_opacity', 'REAL', '0.9'),
                ('desktop_max_words', 'INTEGER', '30'),
                ('desktop_text_align', 'TEXT', "'center'"),
                ('desktop_padding', 'INTEGER', '20'),
                ('desktop_border_radius', 'INTEGER', '10'),
            ]
            for col_name, col_type, default in desktop_columns:
                cursor.execute(f'ALTER TABLE user_settings ADD COLUMN {col_name} {col_type} DEFAULT {default}')
            conn.commit()

        # Remove old columns that are no longer needed (fade_delay, max_lines)
        # SQLite doesn't support DROP COLUMN easily, so we just ignore old columns
    else:
        # Create settings table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS user_settings (
                id INTEGER PRIMARY KEY DEFAULT 1,
                font_family TEXT DEFAULT 'Arial, sans-serif',
                font_size INTEGER DEFAULT 32,
                font_weight TEXT DEFAULT 'normal',
                text_color TEXT DEFAULT '#ffffff',
                background_color TEXT DEFAULT '#1a1a2e',
                background_opacity REAL DEFAULT 0.9,
                max_words INTEGER DEFAULT 30,
                text_align TEXT DEFAULT 'center',
                padding INTEGER DEFAULT 20,
                border_radius INTEGER DEFAULT 10,
                desktop_font_family TEXT DEFAULT 'Arial, sans-serif',
                desktop_font_size INTEGER DEFAULT 28,
                desktop_font_weight TEXT DEFAULT 'normal',
                desktop_text_color TEXT DEFAULT '#90EE90',
                desktop_background_color TEXT DEFAULT '#1a2e1a',
                desktop_background_opacity REAL DEFAULT 0.9,
                desktop_max_words INTEGER DEFAULT 30,
                desktop_text_align TEXT DEFAULT 'center',
                desktop_padding INTEGER DEFAULT 20,
                desktop_border_radius INTEGER DEFAULT 10,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

    # Insert default settings if table is empty
    cursor.execute('SELECT COUNT(*) FROM user_settings')
    if cursor.fetchone()[0] == 0:
        columns = ', '.join(DEFAULT_SETTINGS.keys())
        placeholders = ', '.join(['?' for _ in DEFAULT_SETTINGS])
        cursor.execute(
            f'INSERT INTO user_settings ({columns}) VALUES ({placeholders})',
            list(DEFAULT_SETTINGS.values())
        )

    conn.commit()
    conn.close()


def get_settings():
    """Fetch current user settings."""
    conn = get_connection()
    cursor = conn.cursor()

    cursor.execute('SELECT * FROM user_settings WHERE id = 1')
    row = cursor.fetchone()
    conn.close()

    if row:
        # Convert to dict and exclude id and timestamps
        settings = dict(row)
        for key in ['id', 'created_at', 'updated_at', 'max_lines', 'fade_delay']:
            settings.pop(key, None)

        # Ensure max_words exists (for migration)
        if 'max_words' not in settings:
            settings['max_words'] = DEFAULT_SETTINGS['max_words']

        return settings

    return DEFAULT_SETTINGS.copy()


def update_settings(settings_dict):
    """Update user settings with provided values."""
    if not settings_dict:
        return get_settings()

    conn = get_connection()
    cursor = conn.cursor()

    # Build UPDATE query with only valid columns
    valid_columns = set(DEFAULT_SETTINGS.keys())
    updates = []
    values = []

    for key, value in settings_dict.items():
        if key in valid_columns:
            updates.append(f'{key} = ?')
            values.append(value)

    if updates:
        updates.append('updated_at = ?')
        values.append(datetime.now().isoformat())

        query = f'UPDATE user_settings SET {", ".join(updates)} WHERE id = 1'
        cursor.execute(query, values)
        conn.commit()

    conn.close()
    return get_settings()


def reset_settings():
    """Reset all settings to defaults."""
    conn = get_connection()
    cursor = conn.cursor()

    # Delete existing and insert defaults
    cursor.execute('DELETE FROM user_settings')

    columns = ', '.join(DEFAULT_SETTINGS.keys())
    placeholders = ', '.join(['?' for _ in DEFAULT_SETTINGS])
    cursor.execute(
        f'INSERT INTO user_settings ({columns}) VALUES ({placeholders})',
        list(DEFAULT_SETTINGS.values())
    )

    conn.commit()
    conn.close()

    return DEFAULT_SETTINGS.copy()