123 lines
3.9 KiB
JavaScript
123 lines
3.9 KiB
JavaScript
const fs = require("node:fs");
|
|
const path = require("node:path");
|
|
const { DatabaseSync } = require("node:sqlite");
|
|
|
|
const DATABASE_FILE = process.env.DATABASE_FILE || process.env.DATA_FILE || path.join(__dirname, "data", "invites.db");
|
|
fs.mkdirSync(path.dirname(DATABASE_FILE), { recursive: true });
|
|
|
|
const database = new DatabaseSync(DATABASE_FILE);
|
|
database.exec("PRAGMA journal_mode = WAL");
|
|
database.exec("PRAGMA busy_timeout = 5000");
|
|
database.exec("PRAGMA foreign_keys = ON");
|
|
database.exec(`
|
|
CREATE TABLE IF NOT EXISTS invites (
|
|
token TEXT PRIMARY KEY,
|
|
guest_name TEXT NOT NULL,
|
|
answer TEXT CHECK (answer IN ('not_coming', 'alone', 'with_someone')),
|
|
companion_name TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
first_opened_at TEXT,
|
|
last_opened_at TEXT,
|
|
open_count INTEGER NOT NULL DEFAULT 0,
|
|
responded_at TEXT
|
|
) STRICT
|
|
`);
|
|
|
|
const selectColumns = `
|
|
token,
|
|
guest_name AS guestName,
|
|
answer,
|
|
companion_name AS companionName,
|
|
created_at AS createdAt,
|
|
first_opened_at AS firstOpenedAt,
|
|
last_opened_at AS lastOpenedAt,
|
|
open_count AS openCount,
|
|
responded_at AS respondedAt
|
|
`;
|
|
|
|
const selectOne = database.prepare(`SELECT ${selectColumns} FROM invites WHERE token = ?`);
|
|
const selectAll = database.prepare(`SELECT ${selectColumns} FROM invites ORDER BY created_at ASC`);
|
|
const insertOne = database.prepare(`
|
|
INSERT INTO invites (token, guest_name, created_at)
|
|
VALUES (?, ?, ?)
|
|
`);
|
|
|
|
function migrateLegacyJson() {
|
|
if (process.env.DATABASE_FILE || process.env.DATA_FILE) return;
|
|
const legacyFile = path.join(__dirname, "data", "invites.json");
|
|
const count = database.prepare("SELECT COUNT(*) AS count FROM invites").get().count;
|
|
if (count || !fs.existsSync(legacyFile)) return;
|
|
try {
|
|
const legacy = JSON.parse(fs.readFileSync(legacyFile, "utf8"));
|
|
const insertLegacy = database.prepare(`
|
|
INSERT OR IGNORE INTO invites (
|
|
token, guest_name, answer, companion_name, created_at,
|
|
first_opened_at, last_opened_at, open_count, responded_at
|
|
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
`);
|
|
database.exec("BEGIN IMMEDIATE");
|
|
for (const invite of legacy.invites || []) {
|
|
insertLegacy.run(
|
|
invite.token,
|
|
invite.guestName || "",
|
|
invite.answer || null,
|
|
invite.companionName || "",
|
|
invite.createdAt || new Date().toISOString(),
|
|
invite.firstOpenedAt || null,
|
|
invite.lastOpenedAt || null,
|
|
Number(invite.openCount || 0),
|
|
invite.respondedAt || null
|
|
);
|
|
}
|
|
database.exec("COMMIT");
|
|
} catch (error) {
|
|
try { database.exec("ROLLBACK"); } catch {}
|
|
console.warn("Legacy JSON data could not be imported:", error.message);
|
|
}
|
|
}
|
|
|
|
migrateLegacyJson();
|
|
|
|
function getInvite(token) {
|
|
return selectOne.get(token) || null;
|
|
}
|
|
|
|
function listInvites() {
|
|
return selectAll.all();
|
|
}
|
|
|
|
function recordOpen(token, openedAt) {
|
|
const result = database.prepare(`
|
|
UPDATE invites
|
|
SET first_opened_at = COALESCE(first_opened_at, ?),
|
|
last_opened_at = ?,
|
|
open_count = open_count + 1
|
|
WHERE token = ?
|
|
`).run(openedAt, openedAt, token);
|
|
return result.changes ? getInvite(token) : null;
|
|
}
|
|
|
|
function saveRsvp(token, answer, companionName, respondedAt) {
|
|
const result = database.prepare(`
|
|
UPDATE invites
|
|
SET answer = ?, companion_name = ?, responded_at = ?
|
|
WHERE token = ?
|
|
`).run(answer, companionName, respondedAt, token);
|
|
return result.changes ? getInvite(token) : null;
|
|
}
|
|
|
|
function createInvites(entries) {
|
|
const createdAt = new Date().toISOString();
|
|
database.exec("BEGIN IMMEDIATE");
|
|
try {
|
|
for (const entry of entries) insertOne.run(entry.token, entry.guestName, createdAt);
|
|
database.exec("COMMIT");
|
|
} catch (error) {
|
|
database.exec("ROLLBACK");
|
|
throw error;
|
|
}
|
|
return entries.map(entry => getInvite(entry.token));
|
|
}
|
|
|
|
module.exports = { DATABASE_FILE, createInvites, getInvite, listInvites, recordOpen, saveRsvp };
|