Prayer Database Queries

// ── getFirstAsync ──────────────────────────────────────────────────────────
// Returns ONE row, or null if no rows match. Give me exactly one row.
// Use this when you expect at most one result:
// lookup by primary key, checking if a row exists, fetching today’s prayer
// getFirstAsync<T>() returns one object of type T, or null if no row matches.
const prayer = await db.getFirstAsync<Prayer>(
  "SELECT * FROM myprayers WHERE id_myprayer = ?",
  [1]
);
// prayer is Prayer | null

// ── getAllAsync ────────────────────────────────────────────────────────────
// getAllAsync<T>() — “Give me all matching rows.”
// Returns an array of rows (T[]). If nothing matches, you get [], not null.
// Returns ALL matching rows as an array.
// Use for list views (all prayers, all pages for a prayer).
const prayers = await db.getAllAsync<PrayerRow>(
  "SELECT * FROM myprayers ORDER BY date DESC LIMIT ?",
  [50]
);
// prayers is PrayerRow[]  (empty array [] if no rows match — never null)

// ── getEachAsync ───────────────────────────────────────────────────────────
// Returns an async iterator. You process rows as they arrive.
// getEachAsync<T>() — “Stream rows one by one.”
// Use this for very large result sets where loading everything into memory would be wasteful:
// exporting thousands of rows, background processing, analytics or batch jobs.
// We do not use this in the prayer app.
for await (const row of db.getEachAsync<PrayerRow>("SELECT * FROM myprayers")) {
  console.log(row.title);
}

# getFirstAsync equivalent
row = conn.execute("SELECT * FROM myprayers WHERE id = ?", (1,)).fetchone()

# getAllAsync equivalent
rows = conn.execute("SELECT * FROM myprayers ORDER BY date DESC").fetchall()

// ✅ Safe — SQLite binds the value, cannot be injected
await db.getFirstAsync(
  "SELECT * FROM myprayers WHERE date = ?",
  [todayISO()]
);

// ❌ Dangerous — never build SQL strings with user data
const date = userInput;
await db.getFirstAsync(`SELECT * FROM myprayers WHERE date = '${date}'`);

// src/db/prayerQueries.ts
//
// READ QUERIES FOR THE PRAYER DATABASE
// ──────────────────────────────────────
// All functions in this file are "pure database reads" — they SELECT data
// and return it.
//
// PATTERN: every function receives `db` as its first parameter.
// This is dependency injection — the caller provides the database connection.
// The connection comes from useSQLiteContext() in the React component.
//
// RELATIONSHIP BETWEEN TABLES (reminder):
//   myprayers (1) ──── (many) pages
//   One prayer has 0 or more pages.
//   pages.myprayerid → myprayers.id_myprayer  (foreign key)
//   Pages are always ordered by page_number ASC.

import type { SQLiteDatabase } from "expo-sqlite";
import type { Prayer, PrayerSummary, Page } from "./types";
import { todayISO } from "../utils/dateUtils";


// ══════════════════════════════════════════════════════════════════════════════
// INTERNAL ROW TYPES
//
// SQLite returns plain objects.  We define internal types for what the DB
// actually returns (column names, SQLite types) separately from the
// application-level types (Prayer, Page).
//
// This distinction matters because:
//   - SQLite stores NULL as null in JavaScript
//   - SQLite has no boolean — we would use 0/1 integers
//   - Column names must match exactly (id_myprayer, not idMyPrayer)
// ══════════════════════════════════════════════════════════════════════════════

/** Exactly what a SELECT * FROM myprayers row returns */
interface PrayerRow {
  id_myprayer: number;
  title:       string;
  description: string | null;
  date:        string | null;
  image:       string | null;  // base64 string or null
}

/** Exactly what a SELECT * FROM pages row returns */
interface PageRow {
  id:          number;
  myprayerid:  number;
  page_number: number;
  content:     string | null;
  image:       string | null;
}

// ══════════════════════════════════════════════════════════════════════════════
// ROW MAPPERS
//
// Convert raw database rows into clean application objects.
// Equivalent to Python's _row_to_page() and _row_to_prayer() in prayer_db.py.
// ══════════════════════════════════════════════════════════════════════════════

/**
 * Convert a raw pages table row into a Page object.
 *
 * Handles null → default value conversions so the rest of the app never
 * needs to check for null on fields that should always have a value.
 */
function rowToPage(row: PageRow): Page {
  // rowToPage is a data‑cleaning adapter.
  // It takes a raw database row (PageRow) and converts it into the shape your app actually wants (Page).
  // This function is the “border crossing” between the messy outside world (SQLite) and your clean internal model.
  return {
    id:          row.id, // Direct copy. The database always provides an integer primary key.
    myprayerid:  row.myprayerid,
    page_number: row.page_number,
    content:     row.content ?? "",
    // content should always be a string, never null.
    // If row.content is a string → use it. If row.content is null → replace it with ""
    image:       row.image,  // image is allowed to be null (no image uploaded)
  };
}

/**
 * Convert a raw myprayers table row + its pages into a Prayer object.
 *
 * @param row    The myprayers row
 * @param pages  The Page objects already fetched for this prayer
 */
function rowToPrayer(row: PrayerRow, pages: Page[]): Prayer {
  return {
    id_myprayer: row.id_myprayer,
    title:       row.title,
    description: row.description ?? "",
    date:        row.date        ?? "",
    image:       row.image,
    pages,
  };
}

/**
 * Convert a raw myprayers row into a PrayerSummary (no pages).
 * Used by listPrayers() which does not load page content.
 */
function rowToPrayerSummary(row: PrayerRow): PrayerSummary {
  return {
    id_myprayer: row.id_myprayer,
    title:       row.title,
    description: row.description ?? "",
    date:        row.date        ?? "",
    image:       row.image,
  };
}

// ══════════════════════════════════════════════════════════════════════════════
// HELPER: fetch pages for a prayer
// ══════════════════════════════════════════════════════════════════════════════

/**
 * Given a prayer's ID, it fetches all pages that belong to that prayer,
 * sorted from the first page to the last.
 *
 * This is a private helper — callers use getTodayPrayer() etc., not this
 * directly.  It is extracted into its own function because three different
 * query functions all need to fetch pages after getting a prayer row.
 *
 * @param db        Open database connection
 * @param prayerId  id_myprayer of the parent prayer
 * @returns         Array of Page objects, ordered by page_number ASC
 */
// 1. async function — because it uses await inside. It returns a Promise<Page[]>.
async function fetchPages(
  db: SQLiteDatabase,
  prayerId: number
): Promise<Page[]> {
  /* 2. db.getAllAsync<PageRow>(...) — calls the getAllAsync method we learned earlier.
  The <PageRow< generic tells TypeScript that each row coming back will match the
  PageRow interface (columns id, myprayerid, etc.). */
  const rows = await db.getAllAsync<PageRow>(
    /* SQL query: Selects specific columns from pages. We list them explicitly instead of SELECT * because it's clear exactly what data we need.
    * WHERE myprayerid = ? — uses a bound parameter to prevent SQL injection.
    * The ? is replaced by the prayerId value from the array [prayerId].
    * ORDER BY page_number ASC — ascending order. Page 1, then 2, then 3… This matches how the user will read the prayer. */
    `SELECT id, myprayerid, page_number, content, image
     FROM pages
     WHERE myprayerid = ?
     ORDER BY page_number ASC`,
    [prayerId]
  );
  /* rows.map(rowToPage) — once we have the raw database rows (each is a PageRow object),
  we use the rowToPage mapper to convert each one into a clean Page object.
  The mapper handles null to empty string conversions for content. */
  return rows.map(rowToPage);
  // rows.map(rowToPage) takes the array of PageRow objects and transforms it into an array of Page objects.
}

// ══════════════════════════════════════════════════════════════════════════════
// PUBLIC READ QUERIES
// ══════════════════════════════════════════════════════════════════════════════

/**
 * Return today's prayer (the one whose date matches today's ISO date).
 *
 * If multiple prayers share today's date, returns the one with the
 * lowest id_myprayer (the one inserted first).
 *
 * @param db  Open database connection from useSQLiteContext()
 * @returns   Prayer with pages, or null if no prayer is scheduled today
 *
 * @example
 * const db = useSQLiteContext();
 * const prayer = await getTodayPrayer(db);
 * if (prayer) {
 *   console.log(prayer.title);       // "Ignatian Morning Prayer"
 *   console.log(prayer.pages.length); // 5
 * }
 */
/* The export keyword makes this function usable from other files (like the Prayers tab screen) */
export async function getTodayPrayer(
  db: SQLiteDatabase
): Promise<Prayer | null> {
  /* todayISO() is a utility we imported from ../utils/dateUtils.
  It returns a string like "2026-05-29".
  By passing that to getPrayerByDate, we get whatever prayer is scheduled for today. */
  return getPrayerByDate(db, todayISO());
}

/**
 * Return the prayer for a specific date, or null if none exists.
 *
 *
 * @param db    Open database connection
 * @param date  ISO date string "YYYY-MM-DD"
 * @returns     Prayer with pages, or null
 *
 * @example
 * const prayer = await getPrayerByDate(db, "2026-05-14");
 */
export async function getPrayerByDate(
  db: SQLiteDatabase,
  date: string
): Promise<Prayer | null> {
  // Step 1 — Fetch the prayer row. We use getFirstAsync because we expect at most
  // one prayer for a given date. Even if multiple prayers accidentally share the same date,
  // LIMIT 1 ensures we only get one (the one with the smallest id_myprayer because of ORDER BY id_myprayer ASC).
  // [date] binds the date parameter safely. The query returns either a PrayerRow or null.
  const row = await db.getFirstAsync<PrayerRow>(
    `SELECT id_myprayer, title, description, date, image
     FROM myprayers
     WHERE date = ?
     ORDER BY id_myprayer ASC
     LIMIT 1`,
    [date]
  );

  // Step 2 — Early return if no prayer found
  // if no prayer matches this date, we immediately return null.
  // The function stops here. This is a guard clause.
  if (row === null) return null;

  // Step 3 — Fetch pages. If a prayer exists, we need its pages.
  // We call fetchPages(db, row.id_myprayer) and await the result.
  // fetchPages returns a Promise<Page[]>, so pages will be an array of Page objects.
  const pages = await fetchPages(db, row.id_myprayer);

  // Step 4 — Assemble the full Prayer object.
  // We pass the raw row and the pages array to rowToPrayer.
  // This creates the final Prayer object with all fields (including the pages). Then, we return it.
  return rowToPrayer(row, pages);
}

/**
 * Return a prayer by its primary key, or null if not found.
 *
 * This is almost identical to getPrayerByDate,
 * but it looks up the prayer by its primary key (id_myprayer).
 *
 * @param db        Open database connection
 * @param prayerId  The id_myprayer primary key value
 * @returns         Prayer with pages, or null
 *
 * @example
 * const prayer = await getPrayerById(db, 1);
 */
export async function getPrayerById(
  db: SQLiteDatabase,
  prayerId: number
): Promise<Prayer | null> {
  // A primary key is unique. We know there will be either zero or one row.
  // Using getFirstAsync tells anyone reading the code "I expect at most one result."
  // Because this is a primary key lookup, SQLite can find the row extremely quickly.
  const row = await db.getFirstAsync<PrayerRow>(
    `SELECT id_myprayer, title, description, date, image
     FROM myprayers
     WHERE id_myprayer = ?`,
    [prayerId]
  );

  if (row === null) return null;

  const pages = await fetchPages(db, row.id_myprayer);
  return rowToPrayer(row, pages);
}

/**
 * It returns a list of prayers without their pages.
 * The return type is PrayerSummary[] — a lighter object that contains only
 * the fields needed for a list view (title, description, date, image, and the prayer ID).
 *
 * WHY no pages in the list?
 *  Imagine you have 50 or 100 prayers. Loading 50 prayers with all their pages would be slow
 *  — each prayer requires a second query to fetch its pages.
 *  To avoid that, listPrayers skips the pages entirely.
 *  The list screen only shows the title and date.
 *  When the user taps a prayer, we'll call getPrayerById to get the full data, including pages.
 *
 * @param db      Open database connection
 * @param limit   Maximum number of prayers (how many rows) to return (default 100)
 * @param offset  Number of prayers to skip (it tells SQLite to skip a certain number of rows before starting to return results, default 0)
 * @returns       Array of PrayerSummary objects, newest date first
 *
 * @example
 * // First page of 20 prayers → rows 1–20.
 * const prayers = await listPrayers(db, 20, 0);
 *
 * // Second page of 20 prayers → rows 21–40.
 * const page2 = await listPrayers(db, 20, 20);
 * Bound parameters [limit, offset] map to the two ? placeholders in order.
 */
export async function listPrayers(
  db: SQLiteDatabase,
  limit:  number = 100,
  offset: number = 0
): Promise<PrayerSummary[]> {
  const rows = await db.getAllAsync<PrayerRow>(
    `SELECT id_myprayer, title, description, date, image
     FROM myprayers
     ORDER BY date DESC, id_myprayer DESC
     LIMIT ? OFFSET ?`,
    [limit, offset]
  );

  // Array.map() transforms each row (PrayerRow) into a PrayerSummary.
  return rows.map(rowToPrayerSummary);
}

/**
 * Return all prayers whose title or description contains the search query.
 * The match is case‑insensitive for English letters (SQLite's LIKE is case-insensitive for ASCII by default).
 *
 *
 * @param db    Open database connection
 * @param query Search string (partial match, case-insensitive)
 * @returns     Matching PrayerSummary objects
 *
 * @example
 * const results = await searchPrayers(db, "morning");
 * // Returns prayers with "morning" anywhere in title or description
 */
export async function searchPrayers(
  db: SQLiteDatabase,
  query: string
): Promise<PrayerSummary[]> {
  // 1. Building a pattern with % wildcards
  // SQLite's LIKE operator uses % as a wildcard that matches any sequence of characters (including zero).
  // %morning% matches "Morning Prayer", "Good morning", or "morning" anywhere in the text.
  // Without %, LIKE 'morning' would only match exactly "morning".
  // We wrap the user's query in % so it becomes a partial match.
  const pattern = `%${query}%`;

  // 2. Running the query.
  // We use getAllAsync because we expect multiple results.
  const rows = await db.getAllAsync<PrayerRow>(
    `SELECT id_myprayer, title, description, date, image
     FROM myprayers
     WHERE title LIKE ? OR description LIKE ?
     ORDER BY date DESC`,
    [pattern, pattern]
    // Two ? placeholders — the same pattern value is passed twice, once for title LIKE ? and once for description LIKE ?.
    // ORDER BY date DESC puts the most recent prayers first.
  );

  // 3. Returning the result
  // As with listPrayers, we map the rows to PrayerSummary objects.
  // No pages are included — search results are a list, and fetching pages for every result would be quite slow.
  return rows.map(rowToPrayerSummary);
}


/**
 * Return the total number of prayers (rows) in the database (myprayers table).
 * Used by the Settings screen (it will be implemented later on) to show database statistics.
 *
 * @param db  Open database connection
 * @returns   Row count from myprayers
 */
export async function countPrayers(db: SQLiteDatabase): Promise<number> {
  // 1. The SQL query. COUNT(*) is an aggregate function that counts rows.
  // AS total gives the result column a name (total), which we can then access as row.total.
  // 2. Using **getFirstAsync with a custom type**
  // COUNT(*) always returns exactly one row, even if the table is empty (it returns 0).
  // We provide a generic type { total: number } — an object with a single numeric property total.
  // This matches the column alias we gave it in the SQL query.
  const row = await db.getFirstAsync<{ total: number }>(
    "SELECT COUNT(*) AS total FROM myprayers"
  );
  // 3. The null safety dance.
  // Even though SQLite guarantees a row, TypeScript's type system doesn't know that.
  // The return type of getFirstAsync is T | null. So row could be null in the compiler's eyes.
  // row?.total uses optional chaining: if row is null or undefined, the expression short‑circuits and returns undefined.
  // ? 0 is the nullish coalescing operator: if the left side is either null or undefined, it returns the right side (0).
  // So if row were somehow null, we'd still return 0.
  return row?.total ?? 0;
}

// ══════════════════════════════════════════════════════════════════════════════
// SEED FUNCTION — inserting test data
//
// If the myprayers table is empty, it inserts one complete prayer with five pages.
// This gives us instant test data so we can verify that getTodayPrayer() and the other read queries work right away.
// ══════════════════════════════════════════════════════════════════════════════

/**
 * Seed the database with one sample prayer if it is empty.
 * Only inserts if COUNT(*) FROM myprayers is 0.
 *
 * @param db  Open database connection
 * @returns   true if seed was inserted, false if data already existed
 */
export async function seedIfEmpty(db: SQLiteDatabase): Promise<boolean> {
  // 1. Check if seeding is needed. We reuse our countPrayers function.
  // If the table already has data, we log a message and return false — no seed performed.
  const count = await countPrayers(db);
  if (count > 0) {
    console.log(`[DB] Seed skipped — ${count} prayer(s) already exist.`);
    return false;
  }

  console.log("[DB] Seeding sample prayer…");

  // 2. Insert the prayer row with runAsync
  // runAsync is the method used for statements that change data (INSERT, UPDATE, DELETE).
  // It does not return rows; instead it returns a SQLiteRunResult object with two useful properties:
  // lastInsertRowId: the primary key of the newly inserted row.
  // changes: the number of rows affected (1 for a single insert).
  // We store the returned object in result and immediately extract lastInsertRowId
  // This gives us the id_myprayer of the prayer we just created — we'll need it to link the pages.
  const result = await db.runAsync(
    `INSERT INTO myprayers (title, description, date, image)
     VALUES (?, ?, ?, ?)`,
    [
      "Ignatian Morning Prayer",
      "A five-step reflection to begin the day in God's presence.",
      todayISO(),  // set to today so getTodayPrayer() finds it
      null,        // no cover image for now
    ]
  );

  // result.lastInsertRowId is the id_myprayer of the row we just inserted -
  // we'll need it to link the pages.
  const prayerId = result.lastInsertRowId;
  console.log(`[DB] Prayer inserted with id=${prayerId}`);

  // 3. Insert the five pages
  // We have an array of page objects, each with a number and content.
  const pages = [
    {
      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>",
    },
    {
      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>",
    },
    {
      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>",
    },
    {
      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>",
    },
    {
      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>",
    },
  ];

  // We iterate with a for...of loop and call runAsync for each page:
  // myprayerid is set to the prayerId we just obtained. This is the foreign key linking each page to the prayer.
  // page_number ensures the pages are ordered correctly.
  // content contains the HTML for that page.
  // image is null for now — no cover images in the seed (test) data.
  for (const page of pages) {
    // Why await inside the loop?
    // Each INSERT is an asynchronous database call. We await each one so they run sequentially.
    await db.runAsync(
      `INSERT INTO pages (myprayerid, page_number, content, image)
       VALUES (?, ?, ?, ?)`,
      [prayerId, page.number, page.content, null]
    );
  }

  // 4. Logging and returning. After all inserts succeed, we log a success message and return true.
  // The Metro log output helps you verify that the seed ran.
  console.log(`[DB] Seeded ${pages.length} pages for prayer id=${prayerId}`);
  return true;
}

// React calls this function to know what to draw on the screen.
export default function PrayersScreen() {
  return <Text>Hello</Text>;
}

// You create state with:
const [value, setValue] = useState(initialValue);

// For example:
const [loading, setLoading] = useState(true);

useEffect(() => {
  run();
}, [db]);

<SQLiteProvider>

Component mounts
   ↓
Initial render happens
   ↓
loading === true → UI shows spinner
   ↓
useEffect runs (after the render)
   ↓
run() executes:
   - seedIfEmpty()
   - getTodayPrayer()
   - listPrayers()
   - countPrayers()
   ↓
State variables updated (setSeeded, setPrayer, setList, setTotal, setLoading)
   ↓
React re-renders the component
   ↓
loading === false → UI shows real data

// app/(tabs)/prayers.tsx
//
// PRAYERS TAB — Test screen
//
// Tests:
//   1. seedIfEmpty() inserts a sample prayer on first launch
//   2. getTodayPrayer() returns the prayer
//   3. listPrayers() returns a list of summaries
//   4. getPrayerById() fetches a specific prayer by id

// React & React Native: basic UI components.
import React, { useEffect, useState } from "react";
import {
  View,
  Text,
  ScrollView,
  StyleSheet,
  ActivityIndicator,
  TouchableOpacity,
} from "react-native";
/* useSQLiteContext: a hook from expo-sqlite that gives the component access to the open database connection.
It’s provided by a context higher in the tree (usually a SQLiteProvider in the root layout). */
import { useSQLiteContext } from "expo-sqlite";
/* Query functions: these are imported from ../../src/db/prayerQueries. They encapsulate the SQL logic: */
import {
  // Inserts a single sample prayer (with pages) if the database is empty.
  // Returns true if seeding happened, false if data already existed.
  seedIfEmpty,
  // Returns the prayer assigned to today’s date, or null.
  getTodayPrayer,
  // Returns an array of PrayerSummary objects (id, title, date) for a paginated list.
  listPrayers,
  // Teturns the total number of prayers in the database.
  countPrayers,
} from "../../src/db/prayerQueries";
// Types: Prayer (full prayer with pages) and PrayerSummary (lightweight list item).
import type { Prayer, PrayerSummary } from "../../src/db/types";

// Component State
export default function PrayersScreen() {
  const db = useSQLiteContext();

  // It shows a spinner while queries run.
  const [loading,  setLoading]  = useState(true);
  // The result of getTodayPrayer(), either a full prayer or null if none found.
  const [prayer,   setPrayer]   = useState<Prayer | null>(null);
  // The paginated list of prayer summaries.
  const [list,     setList]     = useState<PrayerSummary[]>([]);
  // The total number of prayers in the DB (for display).
  const [total,    setTotal]    = useState(0);
  // It shows whether seedIfEmpty() actually inserted a row (used for the badge message).
  const [seeded,   setSeeded]   = useState(false);
  // It catches any thrown exception and displays it.
  const [error,    setError]    = useState<string | null>(null);

  // Running the Queries
  // The effect runs once when the component mounts (and again if the db reference ever changes). It calls run().
  useEffect(() => {
    run();
  }, [db]);

  async function run() {
    try {
      setLoading(true);

      // 1. seedIfEmpty checks if any prayers exist.
      // If not, it inserts one sample prayer with five pages and returns true.
      // If there’s already data, it returns false (only runs on first launch).
      const didSeed = await seedIfEmpty(db);
      setSeeded(didSeed);

      // 2. Today’s prayer – getTodayPrayer looks for a prayer whose date column equals today’s UTC date string (YYYY-MM-DD).
      // If no prayer matches, it returns null.
      const todayPrayer = await getTodayPrayer(db);
      setPrayer(todayPrayer);

      // 3. Load prayer list
      // It fetches the first 10 prayer summaries ordered by date descending.
      const prayers = await listPrayers(db, 10, 0);
      setList(prayers);

      // 4. Total count – gets the total number of prayers.
      const count = await countPrayers(db);
      setTotal(count);

    } catch (e) {
      setError(String(e));
    } finally {
      setLoading(false);
    }
  }

  /* While loading is true, a centered spinner with gold color (#c9a84c) and a muted message appears. */
  if (loading) {
    return (
      <View style={s.center}>
        <ActivityIndicator size="large" color="#c9a84c" />
        <Text style={s.muted}>Running queries…</Text>
      </View>
    );
  }

  /* If a query throws, the error message is displayed in red on a dark background. */
  if (error) {
    return (
      <View style={s.center}>
        <Text style={s.errorText}>❌ {error}</Text>
      </View>
    );
  }

  /* Main UI. The screen is a ScrollView with padding. */
  return (
    <ScrollView style={s.scroll} contentContainerStyle={s.content}>

      {/* Seed status */}
      <View style={s.badge}>
        <Text style={s.badgeText}>
          /* If seeded is true, it confirms a fresh seed. Otherwise, it says how many prayers exist already. */
          {seeded
            ? "✅ Sample prayer seeded (first launch)"
            : `ℹ️  ${total} prayer(s) already in database`}
        </Text>
      </View>

      {/* Today's prayer card, getTodayPrayer() is NOT a function call.
      It is just a piece of text printed on the screen */}
      <Text style={s.sectionTitle}>getTodayPrayer()</Text>
      /* After we run: const todayPrayer = await getTodayPrayer(db); and setPrayer(todayPrayer);
      The state now contains something like:
      prayer = {
        id: 1,
        title: "Morning Prayer",
        date: "2025-05-31",
        description: "A peaceful morning reflection.",
        pages: [
          { id: 10, page_number: 1, content: "<p>Text Page Number 1</p>" },
          { id: 11, page_number: 2, content: "<p>Text Page Number 2</p>" },
          ...
        ]
      } */
      /* Shows the prayer’s title, date, and description. */
      {prayer ? (
        <View style={s.card}>
          <Text style={s.prayerTitle}>{prayer.title}</Text>
          <Text style={s.prayerDate}>{prayer.date}</Text>
          <Text style={s.prayerDesc}>{prayer.description}</Text>
          /* Below a divider, it displays the number of pages and a preview of each page’s HTML content
          (stripped of tags, trimmed, and preview). */
          <View style={s.divider} />
          <Text style={s.meta}>
            {prayer.pages.length} page{prayer.pages.length !== 1 ? "s" : ""}
          </Text>
          /* .map() is a JavaScript array method. It takes an array and transforms each element into something else.
          In React, you use .map() to turn an array into UI elements.
          For each page in the array, create a <View> that displays it. */
          {prayer.pages.map((page) => (
            /* React requires a unique key for each item in a list. */
            <View key={page.id} style={s.pageRow}>
              <Text style={s.pageNum}>Page {page.page_number}</Text>
              {/* Show a preview (the first 80 characters) of the HTML content */}
              <Text style={s.pagePreview}>
                {page.content
                  .replace(/<[^>]+>/g, " ")  /* strip or remove HTML tags */
                  .trim() /* trim whitespace */
                  .slice(0, 80)} /* take the first 80 characters */
                {page.content.length > 80 ? "…" : ""} /* adds an ellipsis if the content is long. */
              </Text>
            </View>
          ))}
        </View>
      ) : (
        /* If no prayer exists for today, it prints a helpful message with today’s date.
        This is a standard React pattern: {prayer ? ShowPrayer  : ShowEmptyMessage }
        */
        <View style={s.card}>
          <Text style={s.muted}>
            No prayer found for today ({new Date().toISOString().slice(0, 10)}).
          </Text>
        </View>
      )}

      {/* Prayer list */}
      <Text style={s.sectionTitle}>
        /* The total count is shown in the section title. */
        listPrayers()  ·  {total} total
      </Text>
      /* list is the paginated list of prayer summaries. Shows the paginated list of prayer summaries.
      Each row is a horizontal flex container with the title on the left and the date on the right. */
      {list.map((p) => (
        <View key={p.id_myprayer} style={s.listRow}>
          <Text style={s.listTitle}>{p.title}</Text>
          <Text style={s.listDate}>{p.date || "no date"}</Text>
        </View>
      ))}

      {/* Hint */}
      <View style={s.hint}>
        <Text style={s.hintText}>
          This post is complete — read queries working.{"\n"}
          Next post adds INSERT / UPDATE / DELETE queries.{"\n"}
        </Text>
      </View>

      {/* Refresh button */}
      /* Tapping it re-runs all queries. This is useful to see the badge change after the first seed
      (from “seeded” to “N prayer(s) already in database”). */
      <TouchableOpacity style={s.btn} onPress={run}>
        <Text style={s.btnText}>↺  Refresh</Text>
      </TouchableOpacity>

    </ScrollView>
  );
}

/* Stylesheet. All styles use a dark theme (#1a1f2e background, blueish-gray cards, gold accents)
consistent with a prayer app. */

const s = StyleSheet.create({
  scroll:       { flex: 1, backgroundColor: "#1a1f2e" },
  content:      { padding: 20, paddingBottom: 48 },
  /* full‑screen centering for loading/error states */
  center:       { flex: 1, backgroundColor: "#1a1f2e",
                  alignItems: "center", justifyContent: "center", gap: 12 },
  /* green‑tinted container with a light green border, used for status */
  badge:        { backgroundColor: "#1e2d1e", borderRadius: 8, padding: 12,
                  marginBottom: 20, borderWidth: 1, borderColor: "#2d4a2d" },
  badgeText:    { color: "#86efac", fontSize: 13, textAlign: "center" },
  sectionTitle: { fontSize: 13, color: "#6b7280", fontFamily: "monospace",
                  marginBottom: 8, marginTop: 4 },
  /* dark card with a subtle border, used for prayer details */
  card:         { backgroundColor: "#222840", borderRadius: 10, padding: 16,
                  marginBottom: 20, borderWidth: 1, borderColor: "#2d3550" },
  prayerTitle:  { fontSize: 18, fontWeight: "bold", color: "#c9a84c",
                  marginBottom: 2 },
  prayerDate:   { fontSize: 12, color: "#6b7280", marginBottom: 6 },
  prayerDesc:   { fontSize: 13, color: "#9ca3af", lineHeight: 20,
                  marginBottom: 12 },
  divider:      { height: 1, backgroundColor: "#2d3550", marginBottom: 12 },
  meta:         { fontSize: 12, color: "#5b8fd4", fontWeight: "600",
                  marginBottom: 8 },
  pageRow:      { flexDirection: "row", gap: 10, marginBottom: 6,
                  alignItems: "flex-start" },
  pageNum:      { fontSize: 12, color: "#5b8fd4", fontFamily: "monospace",
                  width: 50, flexShrink: 0 },
  pagePreview:  { fontSize: 12, color: "#9ca3af", flex: 1, lineHeight: 18 },
  listRow:      { flexDirection: "row", justifyContent: "space-between",
                  alignItems: "center", backgroundColor: "#222840",
                  borderRadius: 8, padding: 12, marginBottom: 8,
                  borderWidth: 1, borderColor: "#2d3550" },
  listTitle:    { fontSize: 14, color: "#dde4f0", flex: 1 },
  listDate:     { fontSize: 12, color: "#6b7280" },
  hint:         { backgroundColor: "#1a1f2e", borderRadius: 8, padding: 14,
                  borderWidth: 1, borderColor: "#2d3550", marginBottom: 16 },
  hintText:     { fontSize: 11, color: "#4b5563", fontFamily: "monospace",
                  lineHeight: 18 },
  btn:          { backgroundColor: "#2d3550", borderRadius: 8, padding: 14,
                  alignItems: "center" },
  btnText:      { color: "#9ca3af", fontSize: 14 },
  muted:        { color: "#6b7280", fontSize: 13, textAlign: "center" },
  errorText:    { color: "#f87171", fontSize: 13, fontFamily: "monospace",
                  padding: 16, textAlign: "center" },
});

# This starts the Expo dev server with cleared caches
npx expo start --clear

# Expected first launch output
✅ Sample prayer seeded (first launch)

getTodayPrayer()
┌────────────────────────────────────────────┐
│ Ignatian Morning Prayer                    │
│ 2026-05-14                                 │
│ A five-step reflection to begin the day…  │
│ ─────────────────────────────────────────  │
│ 5 pages                                    │
│ Page 1  Presence What I pay attention to…  │
│ Page 2  Freedom Lord, free me from…        │
│ Page 3  Gratitude I pause to notice…       │
│ Page 4  Petition Ask for what you need…    │
│ Page 5  Review At the close of the day…   │
└────────────────────────────────────────────┘

listPrayers()  ·  1 total
  Ignatian Morning Prayer        2026-05-14