#!/usr/bin/env python3
"""
prayer_db.py — SQLite prayer database core module
──────────────────────────────────────────────────
Manages a structured prayer library with multi-page prayers, inspired by
Sacred Space (sacredspace.com). Each prayer has a title, description, an
optional cover image, and any number of ordered pages.  Each page has its
own content (HTML or plain text) and an optional image.

DATABASE LOCATION
    Default: ~/dotfiles/myArch/config/bible/prayers.db
    Override: pass db_path= to PrayerDB()

SCHEMA
The prayers are multi‑page, where each page can contain HTML or plain text and an optional image.
This models an interactive prayer step‑by‑step experience (like the “Sacred Space” website).

The date field in myprayers is a text ISO‑8601 date, not a native date type.
SQLite has no dedicated date type, so TEXT is used for portability and easy comparison.

    myprayers
        id_myprayer  INTEGER  PRIMARY KEY AUTOINCREMENT
        title        TEXT     NOT NULL          — e.g. "Morning Prayer"
        description  TEXT                       — one-sentence summary
        date         TEXT                       — ISO-8601 "YYYY-MM-DD";
                                                  used by today_prayer() and
                                                  get_date_prayer(date)
        image        BLOB                       — optional cover image bytes

The pages table uses page_number as a sequential order;
a UNIQUE (myprayerid, page_number) constraint prevents duplicate page numbers for the same prayer.

    pages
        id           INTEGER  PRIMARY KEY AUTOINCREMENT
        myprayerid   INTEGER  NOT NULL  REFERENCES myprayers(id_myprayer)
                                        ON DELETE CASCADE
        page_number  INTEGER  NOT NULL  — 1-based ordering within the prayer
        content      TEXT               — HTML or plain text for this page
        image        BLOB               — optional per-page image bytes

    Indexes
        idx_prayers_date     ON myprayers(date)
        idx_pages_prayer     ON pages(myprayerid, page_number)

EXAMPLE USAGE
    from prayer_db import PrayerDB

    db = PrayerDB()

    # Add a prayer with three pages
    pid = db.add_prayer(
        title       = "Morning Prayer",
        description = "Begin the day in God's presence.",
        date        = "2026-05-01",
    )
    db.add_page(pid, 1, "<h2>Presence</h2><p>What I pay attention to…</p>")
    db.add_page(pid, 2, "<h2>Freedom</h2><p>Lord, free me from…</p>")
    db.add_page(pid, 3, "<h2>Gratitude</h2><p>I thank you for…</p>")

    # Retrieve today's prayer
    prayer = db.today_prayer()
    print(prayer["title"])
    for page in prayer["pages"]:
        print(f"Page {page['page_number']}: {page['content'][:40]}")

DEBUGGING THE SQLITE DATABASE
    See the `debug_guide()` function at the bottom of this file, or run:
        python3 prayer_db.py --debug

    Quick commands:
        # Open the database in the SQLite CLI
        sqlite3 ~/dotfiles/myArch/config/bible/prayers.db

        # In the SQLite shell:
        .tables                     -- list tables
        .schema myprayers           -- show table definition
        SELECT * FROM myprayers;    -- all prayers
        SELECT * FROM pages WHERE myprayerid = 1;

        # GUI tools:
        DB Browser for SQLite  (sudo pacman -S sqlitebrowser)
        DBeaver                (paru -S dbeaver)
"""

from __future__ import annotations

import sqlite3 # Built‑in module for SQLite database access.
import datetime # Used for today’s date in methods like today_prayer()
import json
import os
import sys
from contextlib import contextmanager
from pathlib import Path
from typing import Any

# ══════════════════════════════════════════════════════════════════════════════
# CONFIGURATION
# ══════════════════════════════════════════════════════════════════════════════
# The default database location is under ~/dotfiles/myArch/config/bible/.
# This path can be overridden by passing db_path= to PrayerDB().
# Using Path.home() makes it platform‑independent (e.g., /home/user/ on Linux, /Users/user/ on macOS)
DEFAULT_DB_PATH = (
    Path.home() / "dotfiles/myArch/config/bible/prayers.db"
)

# DDL, Database Schema Definition ───────────────────────────────────────────────────────────────
# The DDL is defined as a module‑level constant.
# This makes the schema obvious at a glance and ensures that
# _init_db() runs exactly the same statements every time the database is opened.

_DDL = """
-- SQLite disables foreign‑key constraints by default for backward compatibility.
-- This statement permanently enables enforcement for the current database connection
PRAGMA foreign_keys = ON;

-- ── Table: myprayers ─────────────────────────────────────────────────────────
-- One row per prayer.
-- A prayer whose date matches today is returned by today_prayer().
CREATE TABLE IF NOT EXISTS myprayers (
    id_myprayer  INTEGER  PRIMARY KEY AUTOINCREMENT, -- Surrogate primary key.
    title        TEXT     NOT NULL, --The name of the prayer, e.g., “Morning Prayer”. Cannot be empty.
    description  TEXT, -- A one‑sentence summary or subtitle, optional.
    date         TEXT,     -- ISO-8601 "YYYY-MM-DD" -- The date field links prayers to calendar days.
    --  Not unique. Multiple prayers can share the same date (e.g. Morning + Evening prayer).
    image        BLOB      -- raw bytes; store small images only (< 1 MB)
);

-- ── Table: pages ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS pages (
    id           INTEGER  PRIMARY KEY AUTOINCREMENT,
    myprayerid   INTEGER  NOT NULL
                          REFERENCES myprayers(id_myprayer) ON DELETE CASCADE,
    -- Foreign key to the parent prayer. When a prayer is deleted,
    -- all its pages are automatically removed due to ON DELETE CASCADE.
    page_number  INTEGER  NOT NULL DEFAULT 1,
    -- Sequentially orders the pages within a prayer.
    -- Combined with myprayerid in a UNIQUE constraint to prevent duplicates.
    content      TEXT,    -- The page’s content, typically HTML or plain text.
    image        BLOB,    -- Optional per‑page image.
    UNIQUE (myprayerid, page_number)
    -- This constraint ensures that a prayer cannot have two pages with the same number
);

-- ── Indexes ───────────────────────────────────────────────────────────────────
-- Speeds up queries that filter by date, e.g., SELECT * FROM myprayers WHERE date = '2026-05-09'.
-- Essential for today_prayer().
CREATE INDEX IF NOT EXISTS idx_prayers_date ON myprayers (date);
-- Speeds up retrieval of all pages for a given prayer, which is the most common access pattern
-- (fetch a prayer and its pages).
CREATE INDEX IF NOT EXISTS idx_pages_prayer ON pages (myprayerid, page_number);
"""

# ══════════════════════════════════════════════════════════════════════════════
# RETURN TYPE HELPERS
# ══════════════════════════════════════════════════════════════════════════════

# All public methods return plain dicts so the GUI layer never imports
# from this module beyond PrayerDB itself.

# Shape of a page dict:
#   { id, myprayerid, page_number, content, image }
#
# Shape of a prayer dict (from _row_to_prayer):
#   { id_myprayer, title, description, date, image, pages: [page_dict, ...] }
PageDict   = dict[str, Any]
PrayerDict = dict[str, Any]
# Note: dict[str, Any] means “a dictionary whose keys are strings, and whose values can be anything.”
# These are type aliases that document the shape of the dictionaries returned by the module’s public methods.
# They use dict[str, Any] because the actual keys are known (e.g., id, title, pages)
# but the values have mixed types (int, str, bytes, list of dicts).

def _row_to_page(row: sqlite3.Row) -> PageDict:
    """Convert a pages table row to a plain dict.
    Takes a sqlite3.Row object (from a SELECT on pages) and converts it to a plain dict.

    content: If row["content"] is None (e.g., if the column was never set),
    it returns an empty string. That normalises the output so consumers don’t need to check for None.
    """
    return {
        "id":          row["id"],
        "myprayerid":  row["myprayerid"],
        "page_number": row["page_number"],
        "content":     row["content"] or "",
        "image":       row["image"],
        # image is left as the raw bytes or None from the database.
        # BLOBs are returned as bytes by the sqlite3 module; None is returned if the column is NULL.
    }


def _row_to_prayer(row: sqlite3.Row, pages: list[PageDict]) -> PrayerDict:
    """Convert a myprayers table row + its pages into a plain dict.

    Similar to _row_to_page, but also receives a list of already‑converted page dicts.
    The pages key embeds the child pages directly in the prayer dict,
    completing the one‑to‑many relationship.
    """
    return {
        "id_myprayer": row["id_myprayer"],
        "title":       row["title"] or "",
        # Again, textual fields that might be NULL are coerced to empty strings.
        "description": row["description"] or "",
        "date":        row["date"] or "",
        "image":       row["image"],   # The image field remains raw bytes or None.
        "pages":       pages,
    }


# ══════════════════════════════════════════════════════════════════════════════
# DATABASE CLASS
# ══════════════════════════════════════════════════════════════════════════════

class PrayerDB:
    """
    SQLite prayer database.

    All public methods open a connection, do their work, commit (or
    rollback on error), and close.

    Self‑contained calls: Each method acquires its own connection, performs the operation,
    and releases the connection. This avoids the complexity of a singleton connection
    and makes multi‑threading safe

    WAL mode: Write‑Ahead Logging allows concurrent reading and writing.

    Args:
        db_path: Can be a string or Path to the .db file.
        The constructor creates any necessary parent directories.

    Typical lifecycle:
        db = PrayerDB()                   # opens / creates the database
        pid = db.add_prayer(...)          # returns the new prayer's PK
        db.add_page(pid, 1, "<p>…</p>")   # add pages
        prayer = db.today_prayer()        # retrieve today's prayer
    """

    def __init__(self, db_path: Path | str = DEFAULT_DB_PATH) -> None:
        # Converts to a Path object regardless of input type, giving consistent path handling.
        self.db_path = Path(db_path)
        # Creates the directory structure if it doesn’t exist (e.g., ~/dotfiles/myArch/config/bible/).
        # parents=True creates all intermediate directories (like mkdir -p) and
        # exist_ok=True prevents an exception if the directory already exists.
        self.db_path.parent.mkdir(parents=True, exist_ok=True)
        # It calls the private method that runs the DDL to create tables/indexes if needed.
        self._init_db()

    # ── Internal helpers ──────────────────────────────────────────────────

    @contextmanager
    def _connect(self):
        """
        Context manager that yields an open sqlite3.Connection.

        This is the single mechanism used by all public methods to obtain a database connection.
        It ensures every connection is properly configured, committed/rolled back, and closed.
        """
        # Opens (or creates) the SQLite database file.
        conn = sqlite3.connect(self.db_path)
        # Changes the default row factory from tuple to sqlite3.Row.
        # This allows accessing columns by name (row["title"]) rather than  index (row[1]).
        # Makes code more readable and robust to schema changes.
        conn.row_factory = sqlite3.Row
        # Enables foreign‑key constraint enforcement for this connection only.
        # SQLite’s default is OFF. Without this, REFERENCES and ON DELETE CASCADE are parsed but ignored,
        # which could lead to orphaned pages.
        # SQLite resets PRAGMA settings per connection. The pragma must be set on every new connection
        conn.execute("PRAGMA foreign_keys = ON")
        # Sets the journal mode to Write‑Ahead Logging.
        #  Write-Ahead Logging allows concurrent reads while a write is in progress.
        conn.execute("PRAGMA journal_mode = WAL")
        try:
            yield conn # it hands the connection to the with block
            conn.commit() #  If the block completes without exception, the transaction is committed.
            # This saves all changes made by the caller.
        except Exception:
            # If any exception occurs, the transaction is rolled back, leaving the database unchanged.
            conn.rollback()
            # The exception is re‑raised so the caller knows the operation failed.
            raise
        finally:
            # It always closes the connection, freeing database locks and system resources.
            # This is essential to avoid leaks.
            conn.close()

    def _init_db(self) -> None:
        """Create tables and indexes if they do not exist yet."""
        # It uses the _connect context manager, so the DDL runs inside a single transaction.
        with self._connect() as conn:
            # It executes the entire _DDL string as a series of SQL statements separated by semicolons.
            # Because all CREATE TABLE statements use IF NOT EXISTS, the script is idempotent:
            # it can be run every time the application starts without harm.
            conn.executescript(_DDL)

    def _fetch_pages(
        self, conn: sqlite3.Connection, prayer_id: int
    ) -> list[PageDict]:
        """
        Given a database connection and a prayer’s primary key (id_myprayer), this method fetches all associated pages,
        ordered by page_number ascending. It is used internally by the “read” methods to build a complete PrayerDict that includes the pages list.

        Args:
            conn: sqlite3.Connection – an open SQLite connection (provided by the _connect() context manager).
            The caller must already have a connection; this method does not manage its own transaction.

            prayer_id: int – the myprayerid of the parent prayer.

        Returns:
            list[PageDict] – a list of dictionaries, each created by _row_to_page()
            (keys: id, myprayerid, page_number, content, image).
            The list is ordered by page_number ascending, matching the ORDER BY clause.
        """
        # SELECT * FROM pages WHERE myprayerid = ? ORDER BY page_number
        # Uses a parameterised query (?) to prevent SQL injection and benefit from the index idx_pages_prayer
        # (on myprayerid, page_number). The index ensures the result set is already sorted and retrieval is efficient.
        # .fetchall() – Retrieves all matching rows into a list of sqlite3.Row objects.
        rows = conn.execute(
            "SELECT * FROM pages WHERE myprayerid = ? ORDER BY page_number",
            (prayer_id,),
        ).fetchall()
        # Converts each row to a PageDict via _row_to_page().
        # If a prayer has no pages, an empty list is returned (a valid result).
        return [_row_to_page(r) for r in rows]

    # ── READ ──────────────────────────────────────────────────────────────

    def today_prayer(self) -> PrayerDict | None:
        """
        Provides the “prayer of the day” – the prayer whose date column matches the current calendar day
        or None. It’s the primary entry point for a daily reading experience.
        If multiple prayers share today's date the one with the lowest
        id_myprayer (oldest inserted) is returned.

        Returns:
            A PrayerDict with keys:
                id_myprayer, title, description, date, image, pages
            or None if no prayer is scheduled for today.

        Example:
            prayer = db.today_prayer()
            if prayer:
                print(prayer["title"])
                for page in prayer["pages"]:
                    print(page["content"])
        """
        # datetime.date.today().isoformat() yields a string like "2026-05-11".
        # This matches the date column format.
        today = datetime.date.today().isoformat()
        # The call is forwarded to get_date_prayer(today), which contains the actual database logic.
        return self.get_date_prayer(today)

    def get_date_prayer(self, date: str) -> PrayerDict | None:
        """
        Return the prayer for the given date string (YYYY-MM-DD), or None.

        Args:
            date: ISO-8601 date string, e.g. "2026-05-01".

        Returns:
            PrayerDict or None.

        Example:
            prayer = db.get_date_prayer("2026-05-01")
        """
        # 1. Acquire a connection via the _connect context manager.
        # This ensures foreign keys, WAL mode, and proper transaction handling.
        with self._connect() as conn:
            # 2. Query the myprayers table:
            # WHERE date = ? – parameterised, so user input (even if malicious) is safe.
            # ORDER BY id_myprayer LIMIT 1 – when multiple prayers share the same date,
            # the one inserted first is returned (deterministic).
            # .fetchone() returns a single sqlite3.Row or None.
            row = conn.execute(
                "SELECT * FROM myprayers WHERE date = ? ORDER BY id_myprayer LIMIT 1",
                (date,),
            ).fetchone()
            if row is None:
                # 3. If no row, return None immediately.
                return None
            # 4. If a row is found, fetch all its pages using _fetch_pages(conn, row["id_myprayer"])...
            pages = self._fetch_pages(conn, row["id_myprayer"])
            # and construct the full PrayerDict with _row_to_prayer(row, pages).
            return _row_to_prayer(row, pages)
            # 5. The connection is automatically committed (no writes took place, but conn.commit() is harmless)
            # and closed after the with block.

    def get_prayer_by_id(self, prayer_id: int) -> PrayerDict | None:
        """
        Return a single prayer by its primary key, or None.

        Unlike get_date_prayer, there’s no ordering ambiguity
        – id_myprayer is the primary key, so at most one row exists.

        Args:
            prayer_id: The id_myprayer value.
        """
        # Follows the same pattern: acquire a connection, query/fetch the prayer row,
        # then its pages, and combine into a PrayerDict.
        with self._connect() as conn:
            # Uses parameterised WHERE id_myprayer = ?
            # – direct index lookup on the primary key (fastest possible access).
            row = conn.execute(
                "SELECT * FROM myprayers WHERE id_myprayer = ?",
                (prayer_id,),
            ).fetchone()
            if row is None:
                # Returns None if the prayer_id doesn’t exist.
                return None
            pages = self._fetch_pages(conn, prayer_id)
            return _row_to_prayer(row, pages)

    def list_prayers(
        self,
        limit: int = 100,
        offset: int = 0,
        order_by: str = "date DESC",
    ) -> list[PrayerDict]:
        """
        Return a list of prayers (without page content) for browsing.

        Pages are NOT loaded here — call get_prayer_by_id() when the user
        opens a specific prayer.  This keeps list queries fast.

        Args:
            limit:    Maximum number of rows to return.
            offset:   Skip this many rows (for pagination).
            order_by: SQL ORDER BY clause.  Allowed values:
                      "date DESC" | "date ASC" | "title ASC" | "id_myprayer DESC"

        Returns:
            List of dicts; each dict has the same shape as a full PrayerDict, i.e., id_myprayer, title, description,
            date, image (bytes|None) but pages = [] (empty list).
        """
        safe_orders = {
            "date DESC", "date ASC", "title ASC", "title DESC",
            "id_myprayer DESC", "id_myprayer ASC",
        }
        # Because the order_by value is interpolated directly into the SQL string (not parameterised),
        # a whitelist is essential to prevent SQL injection.
        # The following check rejects any unexpected input.
        if order_by not in safe_orders:
            raise ValueError(
                f"order_by must be one of {safe_orders}, got {order_by!r}"
            )
        with self._connect() as conn:
            rows = conn.execute(
                f"SELECT * FROM myprayers ORDER BY {order_by} LIMIT ? OFFSET ?",
                (limit, offset),
            ).fetchall()
            # Each dict has the same shape as a full PrayerDict, i.e., id_myprayer, title, description,
            # date, image (bytes|None) but pages = [] (empty list).
            return [_row_to_prayer(r, []) for r in rows]

    def search_prayers(self, query: str) -> list[PrayerDict]:
        """
        Full-text search over title and description.

        Uses LIKE with wildcards — good enough for a personal library.
        For larger datasets, consider enabling FTS5.

        Args:
            query: Search string.  Case-insensitive.

        Returns:
            List of matching PrayerDicts (pages not loaded).
        """
        # f"%{query}%" adds SQL LIKE wildcards around the user’s query.
        # For example, "peace" becomes "%peace%", which matches any title/description containing “peace” anywhere.
        # SQLite’s LIKE is case‑insensitive for ASCII characters by default,
        # which is sufficient for most prayer titles (English)
        pattern = f"%{query}%"
        with self._connect() as conn:
            # The pattern is passed as a parameter, so even if the user types % or _ (which have special meaning
            # in LIKE), they are not interpreted as wildcards – they are treated as literal characters because
            # they are inside the bound parameter string.
            # ORDER BY date DESC – puts prayers scheduled for recent dates first.
            rows = conn.execute(
                """SELECT * FROM myprayers
                   WHERE title LIKE ? OR description LIKE ?
                   ORDER BY date DESC""",
                (pattern, pattern),
            ).fetchall()
            # Returns – a list of prayer dicts with empty pages (same as list_prayers).
            return [_row_to_prayer(r, []) for r in rows]

    # ── WRITE ─────────────────────────────────────────────────────────────

    def add_prayer(
        self,
        title:       str,
        description: str  = "",
        date:        str  = "",
        image:       bytes | None = None,
    ) -> int:
        """
        Insert a new prayer into the ``myprayers`` table and return its
        primary key.

        Args:
            title:       The prayer’s name (e.g. "Morning Prayer").  It will
                         be stripped of leading/trailing whitespace.
            description: Optional one‑sentence summary that appears on
                         prayer cards or lists.  Whitespace is stripped.
            date:        ISO‑8601 date string (``"YYYY-MM-DD"``). An empty
                         string (``""``) or a string consisting only of
                         whitespace is stored as ``NULL`` – use this for
                         prayers that are not tied to a calendar day.
            image:       Raw bytes of a cover image (JPEG, PNG, etc.), or
                         ``None`` for no image.

        Returns:
            The new prayer's id_myprayer (integer primary key).

        Raises:
            ValueError: If *title* is empty or contains only whitespace.

        Example:
            >>> pid = db.add_prayer(
            ...     title="Examen",
            ...     description="Ignatian examination of conscience.",
            ...     date="2026-05-01",
            ... )
            >>> print(pid)
            12
        """
        # Guard against missing title – the UI always requires a name.
        if not title or not title.strip():
            raise ValueError("Prayer title cannot be empty.")

        # Open a connection (context manager automatically commits/rolls back).
        with self._connect() as conn:
            # strip() cleans stray whitespace; date=None converts empty strings
            # to SQL NULL so that the UI treats them as “no date”.
            cursor = conn.execute(
                "INSERT INTO myprayers (title, description, date, image) "
                "VALUES (?, ?, ?, ?)",
                (title.strip(), description.strip(), date.strip() or None, image),
            )
            # lastrowid is guaranteed by Python’s sqlite3 module to return the
            # rowid of the most recent INSERT on this cursor.
            return cursor.lastrowid

    def update_prayer(
        self,
        prayer_id:   int,
        title:       str | None = None,
        description: str | None = None,
        date:        str | None = None,
        image:       bytes | None = ...,   # type: ignore[assignment]
    ) -> bool:
        """
        Change one or more fields of an existing prayer.

        Only fields for which a value is explicitly provided are updated. The
        special sentinel ``...`` (Ellipsis) is used to distinguish “leave
        unchanged” from “set to ``NULL``” for the *image* column.

        Args:
            prayer_id:   The ``id_myprayer`` of the row to modify.
            title:       New title (whitespace will be stripped); ``None``
                         means do not change.
            description: New description (whitespace stripped); ``None``
                         leaves the current value untouched.
            date:        New date string (``"YYYY-MM-DD"``, whitespace
                         stripped, empty string stored as ``NULL``); ``None``
                         means no change.
            image:       New image bytes, ``None`` to remove any existing
                         image, or ``...`` to leave the column as it is.

        Returns:
            ``True`` when exactly one row was modified, ``False`` if no row
            with *prayer_id* exists.

        Example:
            >>> db.update_prayer(1, title="Evening Examen", date="2026-05-02")
            True
        """
        # Ellipsis is the sentinel: it cannot be an actual image value.
        _SENTINEL = ...   # distinguishes "not passed" from None

        # Build the SET clause and corresponding parameter list dynamically.
        sets:  list[str] = []
        params: list[Any] = []

        # Each block appends a column = ? literal and the processed value.
        if title is not None:
            sets.append("title = ?")
            params.append(title.strip())
        if description is not None:
            sets.append("description = ?")
            params.append(description.strip())
        if date is not None:
            # Same strip() + None conversion as in add_prayer.
            sets.append("date = ?")
            params.append(date.strip() or None)
        if image is not _SENTINEL:
            # When image is None (explicitly passed), clear the column.
            sets.append("image = ?")
            params.append(image)

        # If the caller provided nothing to update, exit early without a query.
        if not sets:
            return False   # nothing to update

        # The WHERE clause needs the prayer_id as the last parameter.
        params.append(prayer_id)
        with self._connect() as conn:
            cursor = conn.execute(
                f"UPDATE myprayers SET {', '.join(sets)} WHERE id_myprayer = ?",
                params,
            )
            # rowcount == 1 means success; 0 means prayer_id was not found.
            return cursor.rowcount > 0

    def remove_prayer(self, prayer_id: int) -> bool:
        """
        Delete a prayer and all its associated pages.

        The ``pages`` table is defined with ``ON DELETE CASCADE``, so
        removing the parent row automatically removes every page that
        belongs to that prayer.

        Args:
            prayer_id: The ``id_myprayer`` of the prayer to delete.

        Returns:
            ``True`` if a prayer with that ID existed and was removed,
            ``False`` otherwise (the prayer might already have been deleted).
        """
        with self._connect() as conn:
            # The foreign key constraint handles cascading deletions; nothing
            # else needs to be done here.
            # sqlite3.execute() expects the second argument to be a sequence of parameters, even if there is only one parameter.
            # If we wrote: (prayer_id) Python would pass just an integer, and SQLite would complain.
            # We pass a tuple like (prayer_id,)
            cursor = conn.execute(
                "DELETE FROM myprayers WHERE id_myprayer = ?",
                (prayer_id,),
            )
            return cursor.rowcount > 0

    def add_page(
        self,
        prayer_id:   int,
        page_number: int,
        content:     str  = "",
        image:       bytes | None = None,
    ) -> int:
        """
        Insert (or replace) a single page inside a prayer.

        If a page with the same ``(myprayerid, page_number)`` already
        exists, its content and image are overwritten.  This behaviour is
        intentional – it allows a caller to “re‑upload” a prayer’s steps
        without worrying about duplicates.

        Args:
            prayer_id:   The parent prayer’s ``id_myprayer``.  It must already exist.
            page_number: 1‑based position within the prayer.  Must be unique
                         per prayer.
            content:     HTML or plain‑text content describing this step.
            image:       Optional cover or illustration bytes for the
                         individual page.

        Returns:
            The ``id`` of the new or updated page row.

        Raises:
            ValueError: If *prayer_id* does not exist in ``myprayers``.

        Example:

            >>> pid = db.add_prayer("Daily Examen")
            >>> db.add_page(pid, 1, "<h2>Presence</h2><p>I reflect on God's presence…</p>")
            101
            >>> db.add_page(pid, 2, "<h2>Freedom</h2><p>Lord, free me from…</p>")
            102
    """

        with self._connect() as conn:
            # Verify the parent prayer exists
            # SELECT 1 FROM myprayers WHERE id_myprayer = ? means:
            # Is there any row in myprayers whose primary key equals prayer_id?
            # We don’t need the whole row — just to know whether it exists. So we select the constant 1,
            # which is a common SQL idiom for existence checks.
            # fetchone(). Fetch one row. If the prayer exists → returns a tuple like (1,).
            # If it does NOT exist → returns None
            exists = conn.execute(
                "SELECT 1 FROM myprayers WHERE id_myprayer = ?", (prayer_id,)
            ).fetchone()
            if exists is None:
                # This is the correct way to enforce referential integrity at the application level.
                raise ValueError(
                    f"Prayer with id_myprayer={prayer_id} does not exist."
                )

            # Each prayer has multiple pages. So the table has a unique constraint: UNIQUE (myprayerid, page_number)
            # Meaning: A prayer cannot have two pages with the same page number.
            # ON CONFLICT (myprayerid, page_number). If the insert violates the UNIQUE constraint on (myprayerid, page_number)
            # i.e., if the page already exists, DO UPDATE SET …
            # keep the existing row and update its content and image fields.
            # The keyword excluded refers to the row we are trying to insert.
            # content = excluded.content and image   = excluded.image means:
            # “Replace the old content/image with the new ones we have just passed.”
            cursor = conn.execute(
                """INSERT INTO pages (myprayerid, page_number, content, image)
                   VALUES (?, ?, ?, ?)
                   ON CONFLICT (myprayerid, page_number)
                   DO UPDATE SET content = excluded.content,
                                 image   = excluded.image""",
                (prayer_id, page_number, content, image),
            )
            # lastrowid returns the id of the inserted/replaced row.
            return cursor.lastrowid

    def add_content_prayer(
        self,
        prayer_id: int,
        pages:     list[dict],
    ) -> list[int]:
        """
        Bulk‑add a sequence of pages to a prayer.

        This is the primary method for populating a newly created prayer’s
        content.  It calls `add_page` for each dictionary in *pages*,
        preserving input order.

        Args:
            prayer_id: The target prayer’s ``id_myprayer``.
            pages:     A list of dicts, each having:
                           * ``page_number`` **(int)** – required.
                           * ``content`` **(str)** – HTML or plain text
                             (defaults to ``""`` if missing).
                           * ``image`` **(bytes | None)** – optional, passed
                             through unchanged.

        Returns:
            A list of page IDs in the same order as the input list.  Each
            ID is the primary key of the page inserted/replaced.

        Example:

            >>> db.add_content_prayer(pid, [
                {"page_number": 1, "content": "<h2>Presence</h2><p>…</p>"},"
                {"page_number": 2, "content": "<h2>Freedom</h2><p>…</p>"}"},
                {"page_number": 3, "content": "<h2>Gratitude</h2><p>…</p>"}"},
                [...]
            ])
            [201, 202, 203, 204, 205]
        """

        ids = []
        # Process pages sequentially so that errors on one page are
        # immediately raised.  The caller can wrap the whole operation in a
        # transaction if atomicity is required.
        for p in pages:
            page_id = self.add_page(
                prayer_id   = prayer_id,
                page_number = int(p["page_number"]),
                content     = str(p.get("content", "")),
                image       = p.get("image"),
            )
            ids.append(page_id)
        return ids

    def update_page(
        self,
        page_id:  int,
        content:  str | None = None,
        image:    bytes | None = ...,   # type: ignore[assignment]
    ) -> bool:
        """
        Modify a page’s content and/or image.

        Uses the same sentinel pattern as `update_prayer` to
        distinguish “no change” from “clear the image”.

        Args:
            page_id: The ``pages.id`` of the row to update.
            content: New content text; ``None`` leaves the current value.
            image:   New image bytes, ``None`` to remove the image, or
                     ``...`` to leave it untouched.

        Returns:
            ``True`` if the page was found and updated, ``False`` otherwise.
        """
        _SENTINEL = ...
        sets:   list[str] = []
        params: list[Any] = []

        if content is not None:
            sets.append("content = ?")
            params.append(content)
        if image is not _SENTINEL:
            sets.append("image = ?")
            params.append(image)

        if not sets:
            return False

        params.append(page_id)
        with self._connect() as conn:
            cursor = conn.execute(
                f"UPDATE pages SET {', '.join(sets)} WHERE id = ?",
                params,
            )
            return cursor.rowcount > 0

    def remove_page(self, page_id: int) -> bool:
        """
        Delete a single page from the ``pages`` table.

        This removes only the one row identified by *page_id*.  It does not
        affect the parent prayer or any other pages.  If the prayer
        subsequently has gaps in its ``page_number`` sequence, you can
        renumber them with `reorder_pages`.

        Args:
            page_id: The primary key (``pages.id``) of the page to remove.

        Returns:
            ``True`` if a page with the given id existed and was deleted,
            ``False`` if no such page was found (it might already have been
            removed).

        Example:
            >>> db.remove_page(104)
            True
            >>> db.remove_page(999)   # It does not exist
            False
        """
        # A simple DELETE by primary key; rowcount tells us if it succeeded.
        with self._connect() as conn:
            cursor = conn.execute(
                "DELETE FROM pages WHERE id = ?", (page_id,)
            )
            return cursor.rowcount > 0

    def reorder_pages(self, prayer_id: int, new_order: list[int]) -> None:
        """
        Reassign sequential ``page_number`` values to a prayer's pages.

        Passing a list of page IDs determines the order in which the pages
        will appear.  The first ID in the list gets ``page_number=1``, the
        second gets ``2``, and so on.  Any page of the prayer that is **not**
        included in *new_order* retains its current ``page_number`` (which may
        cause duplicates or gaps – callers should include every page or
        manually remove leftovers).

        The update only touches rows where ``myprayerid`` matches
        *prayer_id*, so accidentally passing IDs from another prayer has no
        effect.

        Args:
            prayer_id: The parent prayer's ``id_myprayer``.  Only pages
                       belonging to this prayer will be altered.
            new_order: List of page IDs in the desired display order.  Each
                       ID must correspond to an existing row in the ``pages``
                       table.

        Returns:
            ``None``.  Changes are committed immediately.

        Example:
            Suppose prayer 5 has pages with ids 10, 11, 12, currently ordered
            by their ``page_number`` as 1, 2, 3.  To reverse them::

                db.reorder_pages(5, [12, 11, 10])

            After this call, page 12 has ``page_number=1``, 11 has 2, and 10
            has 3.

        Note:
            This method does **not** return any value, and it does not verify
            that *new_order* contains exactly the set of pages belonging to
            the prayer.  It is the caller's responsibility to provide a
            correct list.
        """
        with self._connect() as conn:
            # enumerate(start=1) produces 1-based page numbers in order,
            # that is, 1 12; 2 11; 3 10
            for new_num, page_id in enumerate(new_order, start=1):
                # The first ID in new_order (page_id = 12) gets page_number 1 (new_num = 1)
                # The second ID in new_order (page_id = 11) gets page_number 2 (new_num = 2), the third gets 3.
                # The WHERE clause restricts the update to pages that actually
                # belong to the prayer, preventing cross-prayer mishaps.
                conn.execute(
                    "UPDATE pages SET page_number = ? WHERE id = ? AND myprayerid = ?",
                    (new_num, page_id, prayer_id),
                )

    # ── UTILITY ───────────────────────────────────────────────────────────

    def stats(self) -> dict:
        """
        Gather summary statistics about the underlying SQLite database file.
        This is useful for displaying a status bar or a "About" screen in the UI.

        Returns:
            A dictionary with the following keys:

            * ``prayer_count`` (int) – total number of rows in ``myprayers``.
            * ``page_count``   (int) – total number of rows in ``pages``.
            * ``db_size_kb``   (float) – size of the database file on disk,
                               rounded to one decimal place.
            * ``db_path``      (str) – absolute path to the database file
                               (useful for diagnostics).

        Example:
            >>> db.stats()
            {'prayer_count': 12, 'page_count': 57, 'db_size_kb': 45.3,
             'db_path': '/home/user/.config/myapp/myprayers.db'}
        """
        with self._connect() as conn:
            # COUNT(*) is fast even on large tables; SQLite doesn't maintain
            # a separate counter, so this is a full scan, but negligible.
            n_prayers = conn.execute(
                "SELECT COUNT(*) FROM myprayers"
            ).fetchone()[0]
            n_pages = conn.execute(
                "SELECT COUNT(*) FROM pages"
            ).fetchone()[0]

        # os.stat() provides file size; if the file doesn't exist (impossible
        # in normal operation after initialisation) we return 0.
        size_kb = self.db_path.stat().st_size / 1024 if self.db_path.exists() else 0

        return {
            "prayer_count": n_prayers,
            "page_count":   n_pages,
            "db_size_kb":   round(size_kb, 1),
            "db_path":      str(self.db_path),
        }

    def export_json(self, path: Path | str | None = None) -> str:
        """
        Serialise all prayers and their pages to a JSON string.

        Designed for backups or transferring data between installations.
        Since JSON cannot natively store binary data, any ``image`` columns
        are base64‑encoded before serialisation.

        Args:
            path: Optional file path (``Path`` or string) to write the JSON.
                  If provided, the file is overwritten with UTF‑8 text.  If
                  omitted or ``None``, only the string is returned.

        Returns:
            A JSON string containing an object with a single key ``"prayers"``
            whose value is a list of prayer objects.  Each prayer object
            contains all its columns plus a ``"pages"`` list.  Image fields
            that were originally ``bytes`` are represented as base64 strings.

        Example:
            Save a backup file::

                db.export_json(Path.home() / "backups" / "prayers_2026-05-13.json")

            Obtain the JSON string without writing to disk::

                json_str = db.export_json()
                with open("backup.json", "w") as f:
                    f.write(json_str)
        """
        import base64
        prayers = []
        with self._connect() as conn:
            # Fetch all prayers ordered by primary key for deterministic output.
            rows = conn.execute(
                "SELECT * FROM myprayers ORDER BY id_myprayer"
            ).fetchall()

            for row in rows:
                # _fetch_pages is a private helper that retrieves all pages for
                # a given prayer, already ordered by page_number.
                pages = self._fetch_pages(conn, row["id_myprayer"])
                # _row_to_prayer builds a dict from the row and attaches the
                # pages list.  The helper returns image columns as raw bytes.
                d = _row_to_prayer(row, pages)

                # Convert binary image fields to base64 strings for JSON serialisation
                if d["image"]:
                    d["image"] = base64.b64encode(d["image"]).decode()
                for page in d["pages"]:
                    if page["image"]:
                        page["image"] = base64.b64encode(page["image"]).decode()
                prayers.append(d)

        # json.dumps with ensure_ascii=False preserves non‑ASCII characters.
        text = json.dumps({"prayers": prayers}, ensure_ascii=False, indent=2)

        # Optionally write to disk; Path.write_text handles encoding.
        if path:
            Path(path).write_text(text, encoding="utf-8")
        return text

# ══════════════════════════════════════════════════════════════════════════════
# DEBUG GUIDE
# ══════════════════════════════════════════════════════════════════════════════

def debug_guide() -> None:
    """
    Print a comprehensive SQLite debugging guide for this database.

    This function is intended to be run from the command line with
    ``python3 prayer_db.py --debug``.  It creates a temporary class `PrayerDB`
    instance only to retrieve the database path and current counts, then prints
    a multi‑section reference covering:

    1. How to open the database with the ``sqlite3`` CLI and execute useful
       dot‑commands and queries.
    2. GUI tools (DB Browser for SQLite, DBeaver) and how to install them on
       Arch Linux.
    3. Python REPL snippets that demonstrate the most common read operations
       and data export.
    4. How to check foreign‑key integrity with ``PRAGMA foreign_key_check``.
    5. How to reset the database (delete the file) to start fresh.

    Raises:
        FileNotFoundError: If the database file has not been created yet
            (e.g., the application has never been launched).  In normal usage
            `PrayerDB` initialises the file automatically.

    Note:
        The guide hard‑codes ``sqlite3`` paths; users on other platforms may
        need to adjust the commands (e.g., ``sqlite3.exe`` on Windows).
    """
    # Instantiate the database to get its path and current statistics.
    db = PrayerDB()
    s  = db.stats()

    # Print a formatted multi‑line string using an f‑string.
    # The curly braces inside the printed guide are escaped by doubling them,
    # while Python expressions are enclosed in single braces.
    print(f"""
╔══════════════════════════════════════════════════════════════════════════╗
║           PRAYER DATABASE — DEBUG GUIDE                                  ║
╠══════════════════════════════════════════════════════════════════════════╣
║  Database : {s['db_path']:<60}║
║  Prayers  : {s['prayer_count']:<60}║
║  Pages    : {s['page_count']:<60}║
║  Size     : {str(s['db_size_kb']) + ' KB':<60}║
╚══════════════════════════════════════════════════════════════════════════╝

── 1. SQLITE CLI ─────────────────────────────────────────────────────────────

  # Open the database
  sqlite3 "{s['db_path']}"

  # Useful shell commands (prefix with dot):
  .tables                           list all tables
  .schema myprayers                 show CREATE TABLE statement
  .schema pages
  .mode column                      aligned column output
  .headers on                       show column names
  .width 5 20 15 10                 set column widths

  # Queries
  SELECT id_myprayer, title, date FROM myprayers;
  SELECT * FROM myprayers WHERE date = date('now');
  SELECT p.title, pg.page_number, substr(pg.content, 1, 60)
    FROM myprayers p JOIN pages pg ON p.id_myprayer = pg.myprayerid
    ORDER BY p.id_myprayer, pg.page_number;

  # Count pages per prayer
  SELECT p.title, COUNT(pg.id) AS page_count
    FROM myprayers p LEFT JOIN pages pg ON p.id_myprayer = pg.myprayerid
    GROUP BY p.id_myprayer;

  # Exit the CLI
  .quit

── 2. GUI TOOLS ──────────────────────────────────────────────────────────────

  sudo pacman -S sqlitebrowser     # DB Browser for SQLite (free, excellent)
  paru -S dbeaver                  # DBeaver (heavier but very powerful)

  DB Browser: File → Open Database → {s['db_path']}
  Then: Browse Data tab → select table → edit cells directly.

── 3. PYTHON REPL ─────────────────────────────────────────────────────────────

  from prayer_db import PrayerDB
  db = PrayerDB()

  db.stats()                        # quick summary
  db.list_prayers()                 # all prayers (no page content)
  db.today_prayer()                 # today's prayer with pages
  db.get_date_prayer("2026-05-01")  # specific date
  db.get_prayer_by_id(1)            # by primary key

  # Pretty-print
  import json
  print(json.dumps(db.today_prayer(), indent=2, default=str))

  # Export all data
  db.export_json("/tmp/prayers_backup.json")

── 4. CHECK FOREIGN KEY INTEGRITY ───────────────────────────────────────────

  sqlite3 "{s['db_path']}" "PRAGMA foreign_key_check;"
  # Returns rows for any orphaned pages (myprayerid points to deleted prayer)

── 5. RESET / START OVER ────────────────────────────────────────────────────

  # Delete the database file:
  rm "{s['db_path']}"
  # The next PrayerDB() call recreates it with empty tables.
""")

# ══════════════════════════════════════════════════════════════════════════════
# DEMO / QUICK TEST
# ══════════════════════════════════════════════════════════════════════════════

def _demo(db: PrayerDB) -> None:
    """
    Insert a sample Ignatian morning prayer and verify it can be read back.

    This function is intended for development and quick smoke tests, meaning preliminary,
    high-level software testing type that verifies critical functionalities of a new build are working,
    ensuring stability before deeper testing. It:

    1. Obtains today’s ISO date.
    2. Creates a new prayer record with a five‑step structure.
    3. Populates the five pages using `add_content_prayer`.
    4. Retrieves the prayer via `today_prayer` and ensures it is not ``None``.
    5. Prints a brief summary of the inserted prayer, its pages, and the
       current database statistics.

    Args:
        db: An already‑opened `PrayerDB` instance.

    Raises:
        AssertionError: If ``today_prayer()`` returns ``None``, indicating
            the demo data was not inserted correctly (this would abort the
            script).

    Note:
        The function assumes no existing prayer for today; if one already
        exists it will result in duplicate rows (the date field is not unique),
        but the demo will still succeed.
    """
    # Obtain today’s date as "YYYY-MM-DD".
    today = datetime.date.today().isoformat()
    print(f"  Adding sample prayer for {today}…")

    # Create the prayer header record.
    pid = db.add_prayer(
        title       = "Ignatian Morning Prayer",
        description = "A five-step reflection to begin the day in God's presence.",
        date        = today,
    )

    # Bulk-add the pages, each with a heading and a short meditation.
    db.add_content_prayer(pid, [
        {
            "page_number": 1,
            "content": (
                "<h2>Presence</h2>"
                "<p>What I pay attention to has a hold on my becoming./<p>"
                "<p>I reflect on the presence of God in this moment, "
                "in my breath, in the light around me./<p>"
            ),
        },
        {
            "page_number": 2,
            "content": (
                "<h2>Freedom/<h2>"
                "<p>Lord, free me from whatever holds me back from loving well today./<p>"
                "<p>I notice the fears, distractions or resentments in my heart, "
                "and I hand them to you./<p>"
            ),
        },
        {
            "page_number": 3,
            "content": (
                "<h2>Gratitude/<h2>"
                "<p>I pause to notice the gifts of this new day./<p>"
                "<p>I thank you for rest, for another morning, for the people "
                "I will meet and the work I am given to do./<p>"
            ),
        },
        {
            "page_number": 4,
            "content": (
                "<h2>Petition/<h2>"
                "<p>Ask for what you need./<p>"
                "<p>I bring my needs and the needs of those I love "
                "into your presence, trusting that you hear me./<p>"
            ),
        },
        {
            "page_number": 5,
            "content": (
                "<h2>Review/<h2>"
                "<p>At the close of the day, look back with gratitude./<p>"
                "<p>Where did I sense God's presence today? "
                "Where did I turn away? I rest in that awareness./<p>>"
            ),
        },
    ])

    # Fetch the prayer we just inserted (should be today’s).
    prayer = db.today_prayer()
    # It checks if the variable prayer contains a value. If prayer holds a valid object,
    # the program continues running safely. If prayer is None, the program stops immediately and
    # raises an AssertionError displaying the custom message: "today_prayer() returned None after insert".
    assert prayer is not None, "today_prayer() returned None after insert"

    # Print a summary header.
    print(f"  Prayer inserted: id={prayer['id_myprayer']}, title={prayer['title']!r}")
    print(f"  Pages: {len(prayer['pages'])}")

    # For each page, strip H2 tags and show a short snippet.
    for page in prayer["pages"]:
        # Show first 60 chars of stripped content
        snippet = page["content"].replace("<h2>Presence</h2>", "").replace("/<h2>Presence</h2>", " — ")
        snippet = snippet[:60].strip()
        print(f"    Page {page['page_number']}: {snippet}…")

    print()
    # Show database statistics after the insertion.
    s = db.stats()
    print(f"  Stats: {s['prayer_count']} prayer(s), {s['page_count']} page(s), "
          f"{s['db_size_kb']} KB at {s['db_path']}")


# ══════════════════════════════════════════════════════════════════════════════
# CLI
# ══════════════════════════════════════════════════════════════════════════════

if __name__ == "__main__":
    # The script supports three mutually‑exclusive flags: --debug, --demo, --stats, and --export.
    # Check for --debug first and run the guide, then exit.
    if "--debug" in sys.argv:
        debug_guide()
        sys.exit(0)

    # --demo inserts sample data and prints a summary.
    if "--demo" in sys.argv:
        print("Running demo…")
        _db = PrayerDB()
        _demo(_db)
        print("Done. Run --debug for SQLite debugging instructions.")
        sys.exit(0)

    # --stats outputs a JSON dictionary with prayer/page counts and file size.
    if "--stats" in sys.argv:
        import json as _json
        _db = PrayerDB()
        print(_json.dumps(_db.stats(), indent=2))
        sys.exit(0)

    # --export writes all prayers to a JSON file.  If no path is given after
    # the flag, defaults to "/tmp/prayers.json".
    if "--export" in sys.argv:
        import sys as _sys # local alias to avoid shadowing the outer sys
        # Find the index of --export and take the next argument as the path,
        # if present. Otherwise, defaults to "/tmp/prayers.json".
        out = sys.argv[sys.argv.index("--export") + 1] if len(sys.argv) > sys.argv.index("--export") + 1 else "/tmp/prayers.json"
        PrayerDB().export_json(out)
        print(f"Exported to {out}")
        sys.exit(0)

    # If no recognised flag is used, print the module docstring and a short
    # usage line.
    print(__doc__)
    print("\nUsage:  python3 prayer_db.py [--demo | --debug | --stats | --export FILE]")