From b0db0e55c0c400bc948a4934b0e4fddfbfdcca51 Mon Sep 17 00:00:00 2001
From: Jan Tuomi <jan@jantuomi.fi>
Date: Tue, 7 Oct 2025 13:04:03 +0300
Subject: Rewrite to run as a traditional script, update deps

---
 src/config.ts     |  18 ++-----
 src/db.ts         | 143 +++++++++++++++++++++++++++++++++++++++++-------------
 src/index.ts      | 109 +++++++++++++++++------------------------
 src/middleware.ts |  67 ++++++++++++++-----------
 src/sheets.ts     |  36 ++++++++++----
 5 files changed, 222 insertions(+), 151 deletions(-)

(limited to 'src')

diff --git a/src/config.ts b/src/config.ts
index 4e034fa..d8e3ca0 100644
--- a/src/config.ts
+++ b/src/config.ts
@@ -14,13 +14,6 @@ class ConfigError extends Error {
 }
 
 const nodeEnv = process.env.NODE_ENV || "development";
-const port = process.env.PORT ? Number(process.env.PORT) : 3000;
-const authToken = process.env.AUTH_TOKEN || "";
-
-const tgWebhookUrl = process.env.TELEGRAM_WEBHOOK_URL || null;
-if (!tgWebhookUrl) {
-  throw new ConfigError("Missing env var TELEGRAM_WEBHOOK_URL");
-}
 
 const tgBotToken = process.env.TELEGRAM_BOT_TOKEN || null;
 if (!tgBotToken) {
@@ -37,10 +30,8 @@ if (!sheetsRange) {
   throw new ConfigError("Missing env var SHEETS_RANGE");
 }
 
-const pgConnectionUri = process.env.PG_CONNECTION_URI || null;
-if (!pgConnectionUri) {
-  throw new ConfigError("Missing env var PG_CONNECTION_URI");
-}
+const sqlitePath =
+  process.env.SQLITE_PATH || path.join(process.cwd(), "data", "hommabot.db");
 
 const googleSaJsonB64 = process.env.G_SA_JSON_B64 || null;
 if (!googleSaJsonB64) {
@@ -55,13 +46,10 @@ fs.writeFileSync(googleSaJsonPath, googleSaJsonUtf8);
 
 const config = {
   nodeEnv,
-  port,
-  authToken,
-  tgWebhookUrl,
   tgBotToken,
   sheetsSpreadsheetId,
   sheetsRange,
-  pgConnectionUri,
+  sqlitePath,
   googleSaJson,
   googleSaJsonPath,
 };
diff --git a/src/db.ts b/src/db.ts
index f649d2d..511b519 100644
--- a/src/db.ts
+++ b/src/db.ts
@@ -1,9 +1,23 @@
-import {
-  createPool,
-  QueryResultType,
-  sql,
-} from "slonik";
-import config from "./config";
+/* SQLite3 persistence layer replacing previous PG / Slonik implementation.
+ *
+ * Features:
+ * - Uses better-sqlite3 (synchronous, high-performance, safe for WAL mode)
+ * - Separate "write" connection (single) and a small pool of readonly connections for parallel reads
+ * - Applies requested PRAGMAs on every connection
+ * - Uses BEGIN IMMEDIATE transactions for write operations
+ * - STRICT tables (requires SQLite 3.37+)
+ *
+ * Environment:
+ *   SQLITE_PATH (optional) - path to DB file. Defaults to ./data/hommabot.db
+ *
+ * Schema (mirrors previous PG schema):
+ *   active_chats(id INTEGER PRIMARY KEY)
+ *   permitted_users(id INTEGER PRIMARY KEY)
+ */
+
+import fs from "fs";
+import path from "path";
+import Database, { Database as BetterSqliteDb } from "better-sqlite3";
 
 interface ActiveChat {
   id: number;
@@ -13,30 +27,93 @@ interface PermittedUser {
   id: number;
 }
 
-const connection = createPool(config.pgConnectionUri);
-
-export const getActiveChats = async (): Promise<readonly ActiveChat[]> =>
-  connection.any(sql`
-    SELECT *
-    FROM active_chats
-  `);
-
-export const addActiveChat = async (id: number): Promise<QueryResultType<void>> =>
-  connection.query<void>(sql`
-    INSERT INTO active_chats
-    (id) VALUES
-    (${id})
-    ON CONFLICT DO NOTHING
-  `);
-
-export const removeActiveChat = async (id: number): Promise<QueryResultType<void>> =>
-  connection.query<void>(sql`
-    DELETE FROM active_chats
-    WHERE id = ${id}
-  `);
-
-export const getPermittedUsers = async (): Promise<readonly PermittedUser[]> =>
-  connection.any(sql`
-    SELECT *
-    FROM permitted_users
-  `);
+const DB_FILE =
+  process.env.SQLITE_PATH || path.join(process.cwd(), "data", "hommabot.db");
+
+ensurePath();
+const db = new Database(DB_FILE, {
+  fileMustExist: false,
+  readonly: false,
+});
+db.pragma("journal_mode = WAL");
+db.pragma("busy_timeout = 5000");
+db.pragma("synchronous = NORMAL");
+db.pragma("cache_size = 1000000000");
+db.pragma("foreign_keys = ON");
+db.pragma("temp_store = MEMORY");
+
+const ddl = `
+  BEGIN IMMEDIATE;
+  CREATE TABLE IF NOT EXISTS active_chats (
+    id INTEGER PRIMARY KEY
+  ) STRICT;
+
+  CREATE TABLE IF NOT EXISTS permitted_users (
+    id INTEGER PRIMARY KEY
+  ) STRICT;
+  COMMIT;
+`;
+db.exec(ddl);
+
+/**
+ * Initialize directory for DB file if required.
+ */
+function ensurePath() {
+  const dir = path.dirname(DB_FILE);
+  if (!fs.existsSync(dir)) {
+    fs.mkdirSync(dir, { recursive: true });
+  }
+}
+
+/**
+ * Wrap a write operation in a BEGIN IMMEDIATE transaction.
+ */
+function withWriteTx<T>(fn: (db: BetterSqliteDb) => T): T {
+  const begin = db.prepare("BEGIN IMMEDIATE");
+  const commit = db.prepare("COMMIT");
+  const rollback = db.prepare("ROLLBACK");
+
+  begin.run();
+  try {
+    const result = fn(db);
+    commit.run();
+    return result;
+  } catch (err) {
+    try {
+      rollback.run();
+    } catch {
+      /* ignore */
+    }
+    throw err;
+  }
+}
+
+/* Public API (mirrors old Slonik-based version) */
+
+export const getActiveChats = async (): Promise<readonly ActiveChat[]> => {
+  const stmt = db.prepare("SELECT id FROM active_chats");
+  return stmt.all() as ActiveChat[];
+};
+
+export const addActiveChat = async (id: number): Promise<void> => {
+  withWriteTx((db) => {
+    const stmt = db.prepare(
+      "INSERT OR IGNORE INTO active_chats (id) VALUES (?)",
+    );
+    stmt.run(id);
+  });
+};
+
+export const removeActiveChat = async (id: number): Promise<void> => {
+  withWriteTx((db) => {
+    const stmt = db.prepare("DELETE FROM active_chats WHERE id = ?");
+    stmt.run(id);
+  });
+};
+
+export const getPermittedUsers = async (): Promise<
+  readonly PermittedUser[]
+> => {
+  const stmt = db.prepare("SELECT id FROM permitted_users");
+  return stmt.all() as PermittedUser[];
+};
diff --git a/src/index.ts b/src/index.ts
index b675584..c6d92eb 100644
--- a/src/index.ts
+++ b/src/index.ts
@@ -1,70 +1,51 @@
-import { App } from "@tinyhttp/app";
-import { logger } from "@tinyhttp/logger";
-import bodyParser from "body-parser";
-import config from "./config";
 import { buildSheetsClient } from "./sheets";
-import { bot, broadcastMessage } from "./tg";
-import { httpHeaderAuth } from "./middleware";
+import { broadcastMessage } from "./tg";
 
-const main = async () => {
-  const sheets = await buildSheetsClient();
-
-  const app = new App({
-    onError: (err, _req, res) => {
-      console.log(err);
-      res.status(500).send("Something bad happened");
-    },
-  });
-
-  app
-    .use(logger({
-      timestamp: {
-        format: "YYYY-MM-DDTHH:mm:ssZ[Z]",
-      },
-    }))
-    .use(bot.webhookCallback("/webhook/telegram"))
-    .use(bodyParser.json())
-    .get("/", async (_, res) => {
-      res.send({ service: "hommabot2 API" });
-    })
-    .post("/scheduler/trigger", httpHeaderAuth, async (_req, res) => {
-      try {
-        const sheetData = await sheets.fetchSheetData();
-        const entries = sheets.processRows(sheetData);
-        const thisWeeksEntries = sheets.filterOnlyThisWeek(entries);
-
-        if (thisWeeksEntries.length > 0) {
-          const tasks = thisWeeksEntries.map(e => `${e.name} (${e.interval})`);
-          const taskText = tasks.join("\n");
-          const msg = `Tällä viikolla tehtävät hommat:\n${taskText}`;
-          await broadcastMessage(msg);
-          console.log("Broadcasted message:", msg);
-        } else {
-          const msg = "Tällä viikolla ei toistuvia hommia! 🎉";
-          await broadcastMessage(msg);
-          console.log("Broadcasted message:", msg);
-        }
-
-        const newDateStamps = sheets.updatedLastDoneDateStamps(entries);
-        await sheets.updateSheetLastDoneColumn(newDateStamps);
+const main = async (): Promise<void> => {
+  const startTime = Date.now();
+  console.log("[run] Starting recurring tasks notification script");
 
-        res.sendStatus(200);
-      } catch (err) {
-        console.error(err);
-        res.sendStatus(500);
-      }
-    });
-
-  console.log(`Setting up webhook on ${config.tgWebhookUrl}`);
-  bot.telegram.setWebhook(config.tgWebhookUrl);
-
-  console.log(`Serving on http://localhost:${config.port}`);
-  app.listen(config.port);
+  const sheets = await buildSheetsClient();
 
+  // 1. Fetch raw sheet data
+  const sheetData = await sheets.fetchSheetData();
+  console.log(`[run] Retrieved ${sheetData.length} raw rows from Sheets`);
+
+  // 2. Process rows (compute next/last dates)
+  const entries = sheets.processRows(sheetData);
+  console.log(`[run] Processed ${entries.length} entries`);
+
+  // 3. Filter only tasks due this week
+  const thisWeeksEntries = sheets.filterOnlyThisWeek(entries);
+  console.log(`[run] Found ${thisWeeksEntries.length} entries due this week`);
+
+  // 4. Compose & broadcast Telegram message
+  if (thisWeeksEntries.length > 0) {
+    const tasks = thisWeeksEntries.map((e) => `${e.name} (${e.interval})`);
+    const taskText = tasks.join("\n");
+    const msg = `Tällä viikolla tehtävät hommat:\n${taskText}`;
+    await broadcastMessage(msg);
+    console.log("[run] Broadcasted weekly task message");
+  } else {
+    const msg = "Tällä viikolla ei toistuvia hommia! 🎉";
+    await broadcastMessage(msg);
+    console.log("[run] Broadcasted empty-week message");
+  }
+
+  // 5. Update "last done" column with new date stamps (only tasks done this week are advanced)
+  const newDateStamps = sheets.updatedLastDoneDateStamps(entries);
+  await sheets.updateSheetLastDoneColumn(newDateStamps);
+  console.log("[run] Updated last done column in sheet");
+
+  const durationMs = Date.now() - startTime;
+  console.log(`[run] Completed successfully in ${durationMs}ms`);
 };
 
-main();
-
-// Enable graceful stop
-process.once("SIGINT", () => bot.stop("SIGINT"));
-process.once("SIGTERM", () => bot.stop("SIGTERM"));
+main()
+  .then(() => {
+    process.exit(0);
+  })
+  .catch((err) => {
+    console.error("[run] Fatal error:", err);
+    process.exit(1);
+  });
diff --git a/src/middleware.ts b/src/middleware.ts
index 98d5688..589022f 100644
--- a/src/middleware.ts
+++ b/src/middleware.ts
@@ -1,39 +1,48 @@
-import { Request, Response } from "@tinyhttp/app";
-import { Context } from "telegraf";
-import config from "./config";
+/**
+ * Telegram-only middleware utilities.
+ *
+ * HTTP server functionality has been removed, so any HTTP-specific middleware
+ * (e.g. header-based auth) has been deleted.
+ *
+ * This module currently exposes:
+ *   tgAuth - Ensures that only permitted Telegram user IDs can invoke bot commands.
+ */
 
+import { Context } from "telegraf";
 import { getPermittedUsers } from "./db";
 
-const tgAuth = async (ctx: Context, next: () => Promise<void>): Promise<void> => {
+/**
+ * Telegram authorization middleware.
+ *
+ * Logic:
+ * 1. Extract the Telegram user ID from the incoming context.
+ * 2. Load the list of permitted user IDs from the SQLite database.
+ * 3. If the user ID is not present, reply with an authorization failure message
+ *    and do NOT call next().
+ * 4. Otherwise, continue to the next middleware/handler.
+ */
+export const tgAuth = async (
+  ctx: Context,
+  next: () => Promise<void>,
+): Promise<void> => {
   const userId = ctx.message?.from.id;
-
-  const tgUsers = await getPermittedUsers();
-  const tgUserIds = tgUsers.map(u => u.id);
-
-  if (!userId || !tgUserIds.includes(userId)) {
-    ctx.reply("Käyttäjälläsi ei ole oikeuksia startata HommaBottia.");
-  } else {
-    await next();
-  }
-};
-
-const httpHeaderAuth = async (req: Request, res: Response, next: () => void): Promise<void> => {
-  const authHeader = req.headers.authorization || null;
-  if (!authHeader) {
-    res.sendStatus(401);
+  if (!userId) {
+    await ctx.reply("Käyttäjätunnusta ei voitu lukea (user id puuttuu).");
     return;
   }
 
-  const token = authHeader.split(" ")[1];
-  if (!token || token !== config.authToken) {
-    res.sendStatus(401);
-    return;
-  }
+  try {
+    const permitted = await getPermittedUsers();
+    const permittedIds = new Set(permitted.map((u) => u.id));
 
-  next();
-}
+    if (!permittedIds.has(userId)) {
+      await ctx.reply("Käyttäjälläsi ei ole oikeuksia käyttää HommaBottia.");
+      return;
+    }
 
-export {
-  tgAuth,
-  httpHeaderAuth,
+    await next();
+  } catch (err) {
+    console.error("[tgAuth] Authorization check failed:", err);
+    await ctx.reply("Odottamaton virhe valtuutuksessa.");
+  }
 };
diff --git a/src/sheets.ts b/src/sheets.ts
index d3d97e5..3578198 100644
--- a/src/sheets.ts
+++ b/src/sheets.ts
@@ -1,5 +1,14 @@
 import { google } from "googleapis";
-import { add, set, Duration, parse, format, getDay, subDays, addDays } from "date-fns";
+import {
+  add,
+  set,
+  Duration,
+  parse,
+  format,
+  getDay,
+  subDays,
+  addDays,
+} from "date-fns";
 
 import config from "./config";
 
@@ -89,7 +98,12 @@ const processRow = (row: SheetsRawEntry) => {
 const isThisWeek = (entry: SheetsProcessedEntry): boolean => {
   const nextDate = entry.nextDate;
   const currentDateTime = new Date();
-  const today = set(currentDateTime, { hours: 0, minutes: 0, seconds: 0, milliseconds: 0 });
+  const today = set(currentDateTime, {
+    hours: 0,
+    minutes: 0,
+    seconds: 0,
+    milliseconds: 0,
+  });
   const dayOfWeek = (getDay(today) + 7 - 1) % 7; // getDay returns sunday = 0
   const currentWeekStart = subDays(today, dayOfWeek);
   const currentWeekEnd = addDays(currentWeekStart, 7);
@@ -108,9 +122,7 @@ export const buildSheetsClient = async () => {
     keyFile: config.googleSaJsonPath,
   });
 
-  // Acquire an auth client, and bind it to all future calls
-  const authClient = await auth.getClient();
-  google.options({ auth: authClient });
+  google.options({ auth });
   const sheets = google.sheets({ version: "v4" });
 
   const fetchSheetData = async (): Promise<SheetsRawEntry[]> => {
@@ -121,7 +133,7 @@ export const buildSheetsClient = async () => {
       });
 
       const entriesAsLists = res.data.values || [];
-      const rawEntries: SheetsRawEntry[] = entriesAsLists.map(row => ({
+      const rawEntries: SheetsRawEntry[] = entriesAsLists.map((row) => ({
         intervalStr: row[0],
         name: row[1],
         lastDoneDateStr: row[2],
@@ -158,7 +170,7 @@ export const buildSheetsClient = async () => {
     const lastDoneColumnRange = getRightmostColumnInRange(config.sheetsRange);
 
     const body = {
-      values: newDateStamps.map(nds => [nds]),
+      values: newDateStamps.map((nds) => [nds]),
     };
 
     await sheets.spreadsheets.values.update({
@@ -171,10 +183,14 @@ export const buildSheetsClient = async () => {
 
   const processRows = (rows: SheetsRawEntry[]) => rows.map(processRow);
 
-  const filterOnlyThisWeek = (entries: SheetsProcessedEntry[]): SheetsProcessedEntry[] => entries.filter(isThisWeek);
+  const filterOnlyThisWeek = (
+    entries: SheetsProcessedEntry[],
+  ): SheetsProcessedEntry[] => entries.filter(isThisWeek);
 
-  const updatedLastDoneDateStamps = (entries: SheetsProcessedEntry[]): string[] =>
-    entries.map(e => isThisWeek(e) ? e.nextDateStamp : e.lastDateStamp);
+  const updatedLastDoneDateStamps = (
+    entries: SheetsProcessedEntry[],
+  ): string[] =>
+    entries.map((e) => (isThisWeek(e) ? e.nextDateStamp : e.lastDateStamp));
 
   return {
     fetchSheetData,
-- 
cgit v1.3