"""Database connection utilities — shared across models, migrations, and services."""

import os
import sqlite3
import threading

DB_PATH = os.environ.get("RELAY_DB_PATH", "/app/data/relay.db")

# Write lock for schema migrations — prevents concurrent ALTER/CREATE from
# gunicorn workers + RQ worker. WAL handles concurrent reads fine, but
# schema changes must be serialized.
_db_write_lock = threading.Lock()


def get_db():
    """Return a new SQLite connection with row_factory and WAL mode."""
    conn = sqlite3.connect(DB_PATH, timeout=30, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys = ON")
    conn.execute("PRAGMA busy_timeout = 5000")  # Wait up to 5s instead of failing on lock
    return conn


def write_lock():
    """Context manager for serializing write operations across threads.

    Use for schema migrations (ALTER TABLE, CREATE TABLE) to prevent
    concurrent schema changes from gunicorn workers + RQ worker.

    Example:
        with write_lock():
            conn.execute("ALTER TABLE sites ADD COLUMN foo TEXT")
    """
    return _db_write_lock


def _table_exists(conn, name):
    """Check if a table exists in the database."""
    row = conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
        (name,),
    ).fetchone()
    return row is not None