"""SQLite WAL helper. Path is ~/.recoil/v2_workspace_state.db (OUTSIDE Dropbox).

Dropbox sync mid-write corrupts SQLite WAL. Per ADR-0004, the database lives
under ~/.recoil/ which is local-only. The directory is created on first use.
"""
from __future__ import annotations

import sqlite3
from contextlib import contextmanager
from pathlib import Path
from typing import Iterator

DB_PATH = Path.home() / ".recoil" / "v2_workspace_state.db"


def init_db() -> None:
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    with sqlite3.connect(DB_PATH) as conn:
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("PRAGMA synchronous=NORMAL;")
        conn.execute("PRAGMA foreign_keys=ON;")
        conn.execute(
            """
            CREATE TABLE IF NOT EXISTS workspace_state (
                workspace_id TEXT PRIMARY KEY,
                schema_version INTEGER NOT NULL,
                payload_json TEXT NOT NULL,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
            """
        )


@contextmanager
def get_conn() -> Iterator[sqlite3.Connection]:
    conn = sqlite3.connect(DB_PATH)
    conn.execute("PRAGMA foreign_keys=ON;")
    try:
        yield conn
    finally:
        conn.close()


# Idempotent init on import. Acceptable here because the cost is one CREATE TABLE
# IF NOT EXISTS round-trip — well below the per-request cost — and it avoids the
# "did anyone remember to call init_db()?" footgun.
init_db()