# DatabaseManager Module Overview

`DatabaseManager` is the low-level persistence layer for Bibliotheca. It wraps a
SQLite connection, owns prepared statements, and provides a narrow set of CRUD
methods that the higher-level `BookList` uses to keep the database in sync.

## Responsibilities

- Open / close the SQLite database (`sqlite3_open` / `sqlite3_close`).
- Create the schema on first run (`ensure_schema`).
- Expose prepared insert/update/delete/select helpers for `Book` records.
- Provide a busy handler so concurrent access backs off politely.
- Guard SQLite calls with a mutex so callers can safely use the manager across
  threads.

## Structure

```
DatabaseManager
 ├── std::string db_path_   // path to the .sqlite file
 ├── sqlite3* db_           // raw SQLite handle (owned)
 └── std::mutex mtx_        // serialises public API calls
```

All statements are prepared lazily by `prepare_statements()` and reused for each
operation.

## Schema

`ensure_schema()` creates the following tables:

### books table

```sql
CREATE TABLE IF NOT EXISTS books (
  id TEXT PRIMARY KEY,
  title TEXT,
  author TEXT,
  file_path TEXT,
  cover_path TEXT
);
```

### tags table

```sql
CREATE TABLE IF NOT EXISTS tags (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE
);
```

### book_tags junction table

```sql
CREATE TABLE IF NOT EXISTS book_tags (
  book_id TEXT NOT NULL,
  tag_id INTEGER NOT NULL,
  FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (book_id, tag_id)
);
```

Additional indices or columns should be added here, keeping in mind that the
manager is the canonical place to evolve storage.

## Public API

### Book operations

| Method                | Description                                   |
|-----------------------|-----------------------------------------------|
| `ensure_schema()`     | Creates tables if they do not exist.          |
| `upsert_book(book)`   | INSERT OR REPLACE using the book's id.        |
| `remove_book(id)`     | Deletes the row; returns false if not present.|
| `get_book(id)`        | Loads a single book (optional result).        |
| `load_all_books()`    | Returns a vector of all books with their tags.|

### Tag operations

| Method                              | Description                              |
|-------------------------------------|------------------------------------------|
| `add_tag_to_book(book_id, tag)`     | Associates a tag with a book.            |
| `remove_tag_from_book(book_id, tag)`| Removes a tag association from a book.   |
| `get_tags_for_book(book_id)`        | Returns all tags for a specific book.    |

Internally each method locks `mtx_`, executes the prepared statement, and
translates SQLite rows into `Book` objects. The `add_tag_to_book` method
creates the tag if it doesn't exist (INSERT OR IGNORE).

## Busy handling & thread safety

SQLite is file-based and can momentarily return `SQLITE_BUSY` if another thread
or process holds a lock. The static `busy_handler` callback retries with an
exponential backoff. Because all public methods take the mutex, only one thread
issues SQL commands at a time, simplifying concurrency.

## Extension points

- **Schema migrations**: add ALTER TABLE logic inside `ensure_schema()` and keep
  the `Book` serialization/deserialization in sync.
- **Additional queries**: expose specialised getters (e.g., search by author) if
  higher layers start to need them.
- **Alternative backends**: swapping SQLite for another storage engine would
  mean re-implementing this module while keeping `BookList` untouched.

## Expected usage

`BookList` owns a single `DatabaseManager` instance, calls `ensure_schema()` on
startup, and uses the CRUD helpers whenever a book changes. Other modules should
not talk to SQLite directly; instead they should route all persistence through
this manager to keep logic centralised.