"""
app/db.py
=========
Lightweight SQLite-backed key/value settings store.

The database file is created automatically on first use beside the
application package, or at the path set by the DB_PATH environment
variable (useful for Docker volume persistence).

Public API
----------
  init_db()              — create the table if it doesn't exist (call at startup)
  get_setting(key)       — return the stored string value, or None
  save_setting(key, val) — upsert a key/value pair
  get_all_settings()     — return all rows as {key: value}
  delete_setting(key)    — remove a key (used to clear optional fields)
"""

import os
import sqlite3
import threading
from pathlib import Path

from .config import BASE_DIR

# ---------------------------------------------------------------------------
# Database location
# ---------------------------------------------------------------------------

# Default: videocompressor/videopress.db  — sits beside the app/ package.
# Override with the DB_PATH env var (e.g. to a Docker-mounted volume path).
DB_PATH = Path(os.environ.get('DB_PATH', str(BASE_DIR / 'videopress.db')))

# SQLite connections are not thread-safe across threads; use a per-thread
# connection via threading.local() so each worker greenlet/thread gets its own.
_local = threading.local()

_INIT_LOCK = threading.Lock()
_initialised = False


def _connect() -> sqlite3.Connection:
    """Return (and cache) a per-thread SQLite connection."""
    if not hasattr(_local, 'conn') or _local.conn is None:
        _local.conn = sqlite3.connect(str(DB_PATH), check_same_thread=False)
        _local.conn.row_factory = sqlite3.Row
        # WAL mode allows concurrent reads alongside a single writer
        _local.conn.execute('PRAGMA journal_mode=WAL')
        _local.conn.execute('PRAGMA foreign_keys=ON')
    return _local.conn


# ---------------------------------------------------------------------------
# Schema
# ---------------------------------------------------------------------------

def init_db() -> None:
    """
    Create the settings table if it does not already exist.
    Also creates the parent directory of DB_PATH if needed.
    Safe to call multiple times — idempotent.
    """
    global _initialised
    with _INIT_LOCK:
        if _initialised:
            return

        # Ensure the directory exists before SQLite tries to create the file.
        # This handles the case where the Docker volume mount creates ./data
        # as root before the container user can write to it.
        db_dir = DB_PATH.parent
        try:
            db_dir.mkdir(parents=True, exist_ok=True)
        except PermissionError:
            raise PermissionError(
                f"Cannot create database directory '{db_dir}'. "
                f"If running in Docker, create the directory on the host first "
                f"and ensure it is writable by UID 1000:\n"
                f"  mkdir -p {db_dir} && chown 1000:1000 {db_dir}"
            )

        # Test that we can actually write to the directory before SQLite tries
        test_file = db_dir / '.write_test'
        try:
            test_file.touch()
            test_file.unlink()
        except PermissionError:
            raise PermissionError(
                f"Database directory '{db_dir}' is not writable by the current user. "
                f"If running in Docker, fix permissions on the host:\n"
                f"  chown 1000:1000 {db_dir}"
            )

        conn = _connect()
        conn.execute("""
            CREATE TABLE IF NOT EXISTS settings (
                key   TEXT PRIMARY KEY,
                value TEXT NOT NULL
            )
        """)
        conn.commit()
        _initialised = True


# ---------------------------------------------------------------------------
# CRUD helpers
# ---------------------------------------------------------------------------

def get_setting(key: str) -> str | None:
    """Return the stored value for *key*, or None if not set."""
    init_db()
    row = _connect().execute(
        'SELECT value FROM settings WHERE key = ?', (key,)
    ).fetchone()
    return row['value'] if row else None


def save_setting(key: str, value: str) -> None:
    """Insert or update *key* with *value*."""
    init_db()
    conn = _connect()
    conn.execute(
        'INSERT INTO settings (key, value) VALUES (?, ?)'
        '  ON CONFLICT(key) DO UPDATE SET value = excluded.value',
        (key, value),
    )
    conn.commit()


def delete_setting(key: str) -> None:
    """Remove *key* from the store (silently succeeds if absent)."""
    init_db()
    conn = _connect()
    conn.execute('DELETE FROM settings WHERE key = ?', (key,))
    conn.commit()


def get_all_settings() -> dict[str, str]:
    """Return all stored settings as a plain dict."""
    init_db()
    rows = _connect().execute('SELECT key, value FROM settings').fetchall()
    return {row['key']: row['value'] for row in rows}