server/db.js

Repository

wynajem_motorowek

Original path

server/db.js

Role

SOURCE

Size

4965 bytes

Lines

148

SHA-256

78faf60f606dcdc92376d15dd28237427fd117388864c1e9955050055951776b

Displayed range

1–148

const fs = require("fs");
const path = require("path");
const sqlite3 = require("sqlite3");
const { open } = require("sqlite");
const { buildCancelToken, buildOrderNumber } = require("./lib/orders");

let database = null;

function resolveDbPath() {
	const configuredPath = String(process.env.SQLITE_PATH || "./data/booking.sqlite").trim();
	if (path.isAbsolute(configuredPath)) return configuredPath;
	return path.resolve(__dirname, configuredPath);
}

function normalizeSqlPlaceholders(text) {
	return String(text || "").replace(/\$(\d+)/g, "?");
}

async function init() {
	if (database) return database;

	const dbPath = resolveDbPath();
	fs.mkdirSync(path.dirname(dbPath), { recursive: true });

	database = await open({
		filename: dbPath,
		driver: sqlite3.Database,
	});

	await database.exec("PRAGMA foreign_keys = ON;");

	const migrationPath = path.resolve(__dirname, "migrations.sql");
	const migrationSql = fs.readFileSync(migrationPath, "utf8");
	await database.exec(migrationSql);

	const resourceColumns = await database.all("PRAGMA table_info(resources)");
	const hasCapacity = resourceColumns.some((col) => String(col.name || "").toLowerCase() === "capacity");
	if (!hasCapacity) {
		await database.exec("ALTER TABLE resources ADD COLUMN capacity INTEGER NOT NULL DEFAULT 6");
	}
	await database.exec("UPDATE resources SET capacity = 6 WHERE capacity IS NULL OR capacity <= 0");

	const reservationColumns = await database.all("PRAGMA table_info(reservations)");
	const reservationColumnNames = new Set(reservationColumns.map((col) => String(col.name || "").toLowerCase()));
	if (!reservationColumnNames.has("customer_email")) {
		await database.exec("ALTER TABLE reservations ADD COLUMN customer_email TEXT");
	}
	if (!reservationColumnNames.has("payment_method")) {
		await database.exec("ALTER TABLE reservations ADD COLUMN payment_method TEXT NOT NULL DEFAULT 'ONLINE'");
	}
	if (!reservationColumnNames.has("order_number")) {
		await database.exec("ALTER TABLE reservations ADD COLUMN order_number TEXT");
	}
	if (!reservationColumnNames.has("cancel_token")) {
		await database.exec("ALTER TABLE reservations ADD COLUMN cancel_token TEXT");
	}
	await database.exec("UPDATE reservations SET payment_method = 'ONLINE' WHERE payment_method IS NULL OR payment_method = ''");
	await database.exec("CREATE INDEX IF NOT EXISTS idx_reservations_order_number ON reservations(order_number)");

	const missingIdentifiers = await database.all(
		`SELECT id
		 FROM reservations
		 WHERE status IN ('CONFIRMED', 'CANCELLED')
		   AND (order_number IS NULL OR order_number = '' OR cancel_token IS NULL OR cancel_token = '')`
	);
	for (const row of missingIdentifiers) {
		const orderNumber = buildOrderNumber(row.id);
		const cancelToken = buildCancelToken(row.id);
		await database.run(
			`UPDATE reservations
			 SET order_number = COALESCE(NULLIF(order_number, ''), ?),
			     cancel_token = COALESCE(NULLIF(cancel_token, ''), ?)
			 WHERE id = ?`,
			[orderNumber, cancelToken, row.id]
		);
	}

	await database.exec(
		`CREATE TABLE IF NOT EXISTS support_emails (
		  id INTEGER PRIMARY KEY AUTOINCREMENT,
		  email TEXT NOT NULL UNIQUE,
		  active INTEGER NOT NULL DEFAULT 1,
		  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
		)`
	);

	const existingResources = await database.get("SELECT COUNT(*) AS total FROM resources");
	if (Number(existingResources?.total || 0) === 0) {
		const krypyPath = path.resolve(__dirname, "../krypy");
		if (fs.existsSync(krypyPath)) {
			const folders = fs
				.readdirSync(krypyPath, { withFileTypes: true })
				.filter((entry) => entry.isDirectory())
				.map((entry) => entry.name)
				.sort((a, b) => a.localeCompare(b, "pl"));

			for (const name of folders) {
				await database.run(
					`INSERT OR IGNORE INTO resources (name, type, capacity, active)
					 VALUES (?, 'BOAT', 6, 1)`,
					[name]
				);
			}
		}
	}

	const supportFromEnv = String(process.env.SUPPORT_EMAILS || "").trim();
	if (supportFromEnv) {
		const emails = supportFromEnv
			.split(",")
			.map((item) => item.trim().toLowerCase())
			.filter(Boolean);

		for (const email of emails) {
			await database.run(
				`INSERT OR IGNORE INTO support_emails (email, active)
				 VALUES (?, 1)`,
				[email]
			);
		}
	}

	return database;
}

async function query(text, params = []) {
	const db = await init();
	const sql = normalizeSqlPlaceholders(text).trim();
	const leadingWord = sql.split(/\s+/)[0]?.toUpperCase() || "";

	if (["SELECT", "PRAGMA", "WITH"].includes(leadingWord)) {
		const rows = await db.all(sql, params);
		return { rows, rowCount: rows.length };
	}

	const result = await db.run(sql, params);
	return {
		rows: [],
		rowCount: Number(result?.changes || 0),
		lastID: result?.lastID,
	};
}

module.exports = {
	init,
	query,
	resolveDbPath,
};