source ⟩ app ⟩ routes ⟩ planter.js

import { readdirSync, statSync, existsSync, writeFile } from "fs";
import * as path from "path";

import Database from "better-sqlite3";
import express from "express";
import multer from "multer";

import grimm from "../grimm.js";
import { base32, rootFolder } from "../helpers.js";

const router = express.Router();
const upload = multer({ limits: { fieldSize: 10 * 1024 ** 2 } });
const db = new Database("./db/site.db");
db.pragma("journal_mode = DELETE");

const gardenMedia = `${rootFolder}/hypertext/public/garden/media`;

const insertTag = db.transaction(tag => {
	db.prepare(
		`INSERT INTO tags (tagID, defaultName, lang) VALUES (@tagID, @defaultName, @lang)`
	).run(tag);
});

const insertTaggings = db.transaction((pageset, tags) => {
	for (const t of tags) {
		db.prepare(`INSERT INTO taggings (pageset, tag) VALUES (?, ?)`).run(
			pageset,
			t
		);
	}
});

const deleteTaggings = db.transaction((pageset, tags) => {
	for (const t of tags) {
		db.prepare(`DELETE FROM taggings WHERE pageset = ? AND tag = ?`).run(
			pageset,
			t
		);
	}
});

const getTaggings = page =>
	db
		.prepare(
			`SELECT tags.tagID AS tagID, tags.defaultName AS defaultName FROM taggings INNER JOIN tags ON taggings.tag=tags.tagID WHERE taggings.pageset = ?`
		)
		.all(page);

const mergeTaggings = (newTags, page) => {
	if (newTags) {
		for (const tag of newTags) {
			if (
				db
					.prepare("SELECT * FROM tags WHERE tagID = ?")
					.get(tag.tagID) === undefined
			) {
				insertTag(tag);
			}
		}
		newTags = newTags.map(t => t.tagID);
	} else {
		newTags = [];
	}

	const oldTags = getTaggings(page).map(t => t.tagID);

	const tagActions = {
		base: new Set(oldTags.concat(newTags)),
		inserted: [],
		deleted: []
	};

	for (const tag of tagActions.base) {
		if (newTags.includes(tag) && !oldTags.includes(tag)) {
			tagActions.inserted.push(tag);
		}
		if (!newTags.includes(tag) && oldTags.includes(tag)) {
			tagActions.deleted.push(tag);
		}
	}

	insertTaggings(page, tagActions.inserted);
	deleteTaggings(page, tagActions.deleted);
};

const getSlugPlaceholder = () =>
	Math.floor(Math.random() * (16 ** 6 - 1))
		.toString(16)
		.padStart(6, "0");

const failMsg = text => `<span class="failure">${text}</span>`;
const successMsg = text => `<span class="success">${text}</span>`;

const fileTypes = {
	".jpg": "image",
	".jpeg": "image",
	".png": "image",
	".webp": "image",
	".gif": "image",
	".avif": "image",
	".webm": "video",
	".mkv": "video",
	".mov": "video",
	".avi": "video",
	".mp4": "video",
	".alac": "audio",
	".aac": "audio",
	".flac": "audio",
	".ogg": "audio",
	".mp3": "audio",
	".wav": "audio",
	".vtt": "subtitles",
	".srt": "subtitles"
};

const fileTypeIcons = {
	video: "&#xe04b;",
	audio: "&#xe405;",
	subtitles: "&#xe048;",
	other: "&#xe2c8;",
	image: "&#xe3f4;"
};

let media = () =>
	readdirSync(gardenMedia)
		.map(function (v) {
			return {
				name: v,
				time: statSync(path.join(gardenMedia, v)).mtime.getTime()
			};
		})
		.sort(function (a, b) {
			return b.time - a.time;
		})
		.map(function (v) {
			return v.name;
		})
		.slice(0, 50)
		.map(file => ({
			url: file,
			type: fileTypes?.[path.extname(file)] ?? "other",
			icon: fileTypeIcons[fileTypes?.[path.extname(file)] ?? "other"]
		}));

router.get("/posts", (req, res, next) => {
	const uiLang = req.user.prefs.uiLang || "en";

	const posts = db
		.prepare(
			"SELECT * FROM garden WHERE public = 1 ORDER BY pageCreated DESC"
		)
		.all();

	const drafts = db
		.prepare(
			"SELECT * FROM garden WHERE public = 0 ORDER BY pageUpdated DESC"
		)
		.all();

	posts.forEach(x => {
		x.tags = getTaggings("garden/" + x.qualifiedSlug);
	});
	drafts.forEach(x => {
		x.tags = getTaggings("garden/" + x.qualifiedSlug);
	});

	return res.render("../admin/planter/posts", {
		grimm: grimm,
		lang: uiLang,
		tr: grimm.translator(uiLang),
		posts: posts,
		drafts: drafts
	});
});

router.get("/comments", (req, res, next) => {
	const uiLang = req.user.prefs.uiLang || "en";

	const comments = db
		.prepare("SELECT * FROM comments WHERE planet = 'earth'")
		.all()
		.reverse();

	for (const comment of comments) {
		if (comment.hashcode) {
			comment.tripcode = base32(
				parseInt(comment.hashcode.slice(0, 10), 16)
			);
		}
	}

	return res.render("../admin/planter/comments", {
		grimm: grimm,
		lang: uiLang,
		tr: grimm.translator(uiLang),
		comments: comments
	});
});

router.get("/tags", (req, res, next) => {
	const uiLang = req.user.prefs.uiLang || "en";

	const tags = db.prepare("SELECT * FROM tags ORDER BY tagID asc").all();

	for (const tag of tags) {
		tag.count = db
			.prepare("SELECT COUNT(*) FROM taggings WHERE tag = ?")
			.get(tag.tagID)["COUNT(*)"];
	}

	return res.render("../admin/planter/tags", {
		grimm: grimm,
		lang: uiLang,
		tr: grimm.translator(uiLang),
		tags: tags
	});
});

router.get("/prefs", (req, res, next) => {
	const uiLang = req.user.prefs.uiLang || "en";

	return res.render("../admin/planter/prefs", {
		grimm: grimm,
		lang: uiLang,
		tr: grimm.translator(uiLang),
		user: req.user
	});
});

router.get("/new", (req, res, next) => {
	const uiLang = req.user.prefs.uiLang || "en";
	const slugPlaceholder = getSlugPlaceholder();
	return res.render("../admin/planter/editor", {
		grimm: grimm,
		lang: uiLang,
		tr: grimm.translator(uiLang),
		mode: "creating",
		media: media(),
		post: {},
		slugPlaceholder: slugPlaceholder,
		newPost: true,
		dongle: `?uiLang=${uiLang}&slugPlaceholder=${slugPlaceholder}`
	});
});

router.get("/translate/:postSlug", (req, res, next) => {
	const uiLang = req.user.prefs.uiLang || "en";

	const editee = db
		.prepare("SELECT * FROM garden WHERE slug = ?")
		.get(req.params.postSlug);

	editee.tags = getTaggings("garden/" + editee.qualifiedSlug)
		.map(t => t.tagID)
		.join(", ");

	editee.slug = undefined;
	editee.qualifiedSlug = undefined;
	editee.pageCreated = undefined;

	const slugPlaceholder = getSlugPlaceholder();

	return res.render("../admin/planter/editor", {
		grimm: grimm,
		lang: uiLang,
		tr: grimm.translator(uiLang),
		media: media(),
		mode: "translating",
		post: editee,
		slugPlaceholder: slugPlaceholder,
		newPost: true,
		dongle: `?uiLang=${uiLang}&slugPlaceholder=${slugPlaceholder}`
	});
});

router.get("/edit/:postSlug(*)", (req, res, next) => {
	const uiLang = req.user.prefs.uiLang || "en";

	const editee = db
		.prepare("SELECT * FROM garden WHERE slug = ?")
		.get(req.params.postSlug);

	editee.tags = getTaggings("garden/" + editee.qualifiedSlug)
		.map(t => t.tagID)
		.join(", ");

	return res.render("../admin/planter/editor", {
		grimm: grimm,
		lang: uiLang,
		tr: grimm.translator(uiLang),
		media: media(),
		mode: "editing",
		post: editee,
		slugPlaceholder: false,
		newPost: false,
		dongle: `?uiLang=${uiLang}`
	});
});

router.get("/", (req, res, next) => {
	return res.redirect("/x/planter/posts");
});

/* “Massaging” post metadata to make it easier for me to deal with */
const massage = data => {
	data.slug = data.slug || data.slugPlaceholder;

	data.pageCreated =
		!data.pageCreated && data.verb == "publish"
			? new Date().toISOString()
			: data.pageCreated
			? data.pageCreated.replace(" ", "T") + "Z"
			: null;
	data.pageUpdated = new Date().toISOString();

	data.qualifiedSlug = data.pageCreated
		? `${data.pageCreated.slice(0, 4)}/${data.slug}`
		: `xxxx/${data.slug}`;

	data.slugChanged = data.qualifiedSlug != data.originalQualifiedSlug;

	data.translates = data.translates || data.slug;

	data.thumbnail = data.thumbnail || null;
	data.thumbnailAlt = data.thumbnailAlt || null;

	data.commentStatus = +!!data?.commentStatus;
	data.public =
		data.verb == "publish" ? 1 : data.verb == "retract" ? 0 : data.public;

	data.tags = data.tags.trim()
		? data.tags
				.split(",")
				.map(tag => tag.trim())
				.map(tag =>
					tag.match(/\[[0-9a-zA-Z-]+\]$/)
						? {
								tagID: tag.match(
									/(.*?) *\[([0-9a-zA-Z-]+)\]$/
								)[2],
								defaultName: tag.match(
									/(.*?) *\[([0-9a-zA-Z-]+)\]$/
								)[1],
								lang: data.lang
						  }
						: {
								tagID: grimm.slugify(tag),
								defaultName: tag,
								lang: data.lang
						  }
				)
		: null;

	return data;
};

router.post("/post-save", (req, res, next) => {
	const query = Object.assign(req.query, req.body);
	const uiLang = query?.uiLang || "en";
	const tr = grimm.translator(uiLang);

	massage(query);

	/* ERRORS */
	if (query.newPost) {
		if (db.prepare("SELECT * FROM garden WHERE slug = ?").get(query.slug)) {
			return res
				.status(422)
				.send(failMsg(tr("planter.message.slugTaken", uiLang)));
		}
		if (
			db
				.prepare(
					"SELECT * FROM garden WHERE translates = ? AND lang = ?"
				)
				.get(query.translates, query.lang)
		) {
			return res
				.status(422)
				.send(
					failMsg(
						tr("planter.message.alreadyTranslated", uiLang)(
							query.translates,
							query.lang
						)
					)
				);
		}
	}
	if (query.thumbnail && !query.thumbnailAlt) {
		return res
			.status(422)
			.send(
				failMsg(tr("planter.message.missingThumbnailAltText", uiLang))
			);
	}

	/* Replace tags */

	if (!query.newPost && query.slugChanged) {
		db.transaction((oldSlug, newSlug) => {
			db.prepare(`UPDATE taggings SET pageset = ? WHERE pageset = ?`).run(
				newSlug,
				oldSlug
			);
		})(
			"garden/" + query.originalQualifiedSlug,
			"garden/" + query.qualifiedSlug
		);
	}

	mergeTaggings(query.tags, "garden/" + query.qualifiedSlug);

	/* Update database entry */

	db.transaction(q => {
		const columns = [
			"slug",
			"qualifiedSlug",
			"public",
			"title",
			"contents",
			"pageCreated",
			"pageUpdated",
			"lang",
			"translates",
			"postFormat",
			"commentStatus",
			"markup",
			"thumbnail",
			"thumbnailAlt"
		].filter(c => q[c] !== undefined);
		const atColumns = columns.map(c => `@${c}`);
		if (q.newPost) {
			db.prepare(
				`INSERT INTO garden (${columns.join(
					", "
				)}) VALUES (${atColumns.join(", ")})`
			).run(q);
		} else {
			db.prepare(
				`UPDATE garden SET ${columns
					.map(c => `${c} = @${c}`)
					.join(", ")} WHERE slug = ?`
			).run(q.originalQualifiedSlug.replace(/^(xxxx|[0-9]+)\//, ""), q);
		}
	})(query);

	/* SUCCESS */

	let message = {
		save: successMsg(tr("planter.message.postSaved")(query.pageUpdated)),
		publish: successMsg(
			tr("planter.message.postPublished")(
				"/garden/" + query.qualifiedSlug
			)
		),
		retract: ""
	}[query.verb];

	message += `<span hx-swap-oob="innerHTML:title">${tr(
		"planter.editor.editing"
	)(query.title)}</span>`;

	if (query.newPost) {
		message += `<input id="editor-new-post" name="newPost" type="checkbox" hx-swap-oob="true" hidden>`;
	}

	if (query.verb == "publish") {
		message += `<input id="editor-page-created" name="pageCreated" type="datetime-local" value="${query.pageCreated
			.replace("T", " ")
			.replace("Z", "")}" hx-swap-oob="true">`;
	}

	if (query.slugChanged) {
		message += `<input id="editor-original-qualified-slug" name="originalQualifiedSlug" value="${query.qualifiedSlug}" hx-swap-oob="true" hidden>`;

		return res
			.set("HX-Push", `/x/planter/edit/${query.slug}`)
			.send(message);
	} else {
		return res.send(message);
	}
});

router.post("/post-media", upload.single("file"), (req, res, next) => {
	const uiLang = req.user.prefs.uiLang || "en";

	console.log(req.file);

	const mediaType =
		fileTypes?.[path.extname(req.file.originalname)] ?? "other";
	const mediaIcon = fileTypeIcons[mediaType];

	let mediaName = req.file.originalname;

	while (existsSync(path.join(gardenMedia, mediaName))) {
		const parsedName = path.parse(req.file.originalname);
		delete parsedName.base;
		parsedName.name +=
			"-" +
			Math.floor(Math.random() * (16 ** 5 - 1))
				.toString(16)
				.padStart(5, "0");
		mediaName = path.format(parsedName);
	}

	const mediaButton =
		mediaType == "image"
			? `<figure><button type="button" style="background-image: url('/garden/media/${mediaName}')" data-url="${mediaName}"></button><figcaption>${mediaName}</figcaption></figure>`
			: `<figure class="misc-media"><button type="button" data-url="${mediaName}"><span class="icon">${mediaIcon}</span></button><figcaption>${mediaName}</figcaption></figure>`;

	return writeFile(path.join(gardenMedia, mediaName), req.file.buffer, () =>
		res.send(mediaButton)
	);
});

router.post("/post-prefs", (req, res, next) => {
	const query = Object.assign(
		{ userID: req.user.userID },
		req.query,
		req.body
	);
	const uiLang = query.uiLang || req.user.prefs.uiLang || "en";
	const tr = grimm.translator(uiLang);

	db.transaction(() => {
		db.prepare(
			`UPDATE user_preferences SET uiLang = @uiLang WHERE userID = @userID`
		).run(query);
	})();

	return res.send(
		`<span class="success" lang="${uiLang}">${tr(
			"planter.message.savedPreferences"
		)}</span>`
	);
});

router.post("/post-show-comment", (req, res, next) => {
	if (req.query.id) {
		const toggled = +!db
			.prepare("SELECT shown FROM comments WHERE id = ?")
			.get(req.query.id).shown;

		db.transaction(() => {
			db.prepare("UPDATE comments SET shown = ? WHERE id = ?").run(
				toggled,
				req.query.id
			);
		})();

		return res.status(200);
	} else {
		return res.status(422);
	}
});

export default router;