source ⟩ app ⟩ routes ⟩ garden.js

import Database from "better-sqlite3";
import express from "express";
import SunCalc from "suncalc";

import grimm from "../grimm.js";
import {
	getRubricFilters,
	comparePages,
	getComments,
	prettyRender,
	rubric,
	auth
} from "../helpers.js";

const router = express.Router();
const db = new Database("./db/site.db");
db.pragma("journal_mode = DELETE");

const limit = 15;

rubric.langs.html.parseMediaSource = src =>
	src.match(/^\/|^\.|^[a-z]+:\/\//) ? src : `/garden/media/${src}`;

const filters = getRubricFilters(rubric);

const massageData = data => {
	data.yearSlug = data.qualifiedSlug;
	data.fullSlug = "garden/" + data.qualifiedSlug;

	data.moonPhase = SunCalc.getMoonIllumination(
		new Date(data.pageCreated),
		54.9738,
		-1.6132
	).phase;

	data.commentsCount = +db
		.prepare("Select COUNT(*) From comments Where page_id = ?")
		.get(data.fullSlug)["COUNT(*)"];

	const translations = db
		.prepare("Select * From garden Where public = 1 And translates = ?")
		.all(data.translates)
		.sort(comparePages);

	data.translations = translations.length > 1 ? translations : null;

	data.tags =
		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(data.fullSlug) || [];

	return data;
};

const recentComments = (placeholder = "") =>
	db
		.prepare(
			`Select comments.name, comments.website, comments.page_id, garden.title
			From comments
			Inner Join garden
				On comments.page_id = 'garden/' || garden.qualifiedSlug
			Where comments.page_id Like 'garden/%'
				And comments.planet = 'earth'
				And comments.shown = 1
				And garden.public = 1
			Order By comments.submit_date Desc
			Limit 5`
		)
		.all()
		.map(cmt => [
			cmt.website
				? `<a href="${rubric.langs.html.escapeQuotes(
						cmt.website
				  )}">${rubric.langs.html.escape(cmt.name)}</a>`
				: rubric.langs.html.escape(cmt.name),
			`<a href="/${rubric.langs.html.escapeQuotes(
				cmt.page_id
			)}">${filters["rubric-inline"](cmt.title || placeholder)}</a>`
		]);

const archives = () => {
	const isUnique = (val, idx, array) => array.indexOf(val) === idx;

	const rawPosts = db
		.prepare(
			`Select qualifiedSlug, title, pageCreated, lang
			From garden
			Where public = 1 And postFormat = 'article'
			Order By pageCreated Desc`
		)
		.all();

	const months = rawPosts
		.map(x => x.pageCreated.slice(0, 7))
		.filter(isUnique);

	const years = months.map(x => x.slice(0, 4)).filter(isUnique);

	const posts = years.map(y => ({
		year: y,
		months: months
			.filter(m => m.startsWith(y))
			.map(m => ({
				month: m,
				posts: rawPosts.filter(post => post.pageCreated.startsWith(m))
			}))
	}));

	return posts;
};

const displaySingle = (req, res, next) => {
	let page = db
		.prepare(`Select * From garden Where slug = ? And public = 1 Limit 1`)
		.get(req.params.slug);

	if (page === undefined) {
		return next();
	}

	if (req.params.year != page.qualifiedSlug.slice(0, 4)) {
		return res.redirect(`/garden/${page.qualifiedSlug}`);
	}

	const lastPage = db
		.prepare(
			`Select qualifiedSlug, title
			From garden
			Where pageCreated < @pageCreated
				And lang = @lang
				And public = 1
			Order By pageCreated Desc
			Limit 1`
		)
		.get(page);

	const nextPage = db
		.prepare(
			`Select qualifiedSlug, title
			From garden
			Where pageCreated > @pageCreated
				And lang = @lang
				And public = 1
			Order By pageCreated Asc
			Limit 1`
		)
		.get(page);

	page = massageData(page);

	const toSendOver = {
		post: page,
		previous: lastPage,
		next: nextPage,
		title: page.title
			? page.title.replace(/&shy;/g, "").replace(/::/g, "")
			: grimm.translate(page.lang, "garden.post.untitled"),
		lang: page.lang,
		collation: {
			type: "single",
			title: page.title,
			richTitle: null
		},
		pagePath: page.fullSlug,
		baseDomain: "satyrs.eu",
		comments: getComments(page.fullSlug),
		grimm: grimm,
		tr: grimm.translator(page.lang),
		compileDebug: true,
		filters: filters,
		recentComments: recentComments,
		archives: archives
	};

	return prettyRender(res, next, "../views/garden/single", toSendOver);
};

const displayChron = (req, res, next) => {
	req.params.offset = +(req.params?.offset ?? 1);

	if (req.params.month > 12) {
		return next();
	}

	let offset = (req.params.offset - 1) * limit;
	if (isNaN(offset)) {
		return next();
	}

	let posts = db
		.prepare(
			`Select * From garden
			Where public = 1
				And pageCreated Like @time
			Order By pageCreated Desc
			Limit ${limit}
			Offset ${offset}`
		)
		.all({
			time: req.params.month
				? `${req.params.year}-${req.params.month}%`
				: `${req.params.year}%`
		})
		.map(massageData);

	if (posts.length == 0) return next();

	const hasOlderPage = !!db
		.prepare(
			`Select * From garden
		Where public = 1
			And pageCreated Like @time
		Order By pageCreated Desc
		Limit 1
		Offset ${offset + limit}`
		)
		.get({
			time: req.params.month
				? `${req.params.year}-${req.params.month}%`
				: `${req.params.year}%`
		});

	const tr = grimm.translator("en");
	const toSendOver = {
		posts: posts,
		collation: {
			type: "chron",
			hasOlderPage: hasOlderPage,
			year: req.params.year,
			month: req.params.month,
			offset: req.params.offset,
			title: req.params.month
				? tr("garden.collation.chronMonth")(
						req.params.year + "-" + req.params.month
				  )
				: tr("garden.collation.chronYear")(req.params.year),
			richTitle: req.params.month
				? tr("garden.collation.chronMonth")(
						req.params.year + "-" + req.params.month
				  )
				: tr("garden.collation.chronYear")(req.params.year)
		},
		lang: "en",
		pagePath: `garden/${req.params.year}${
			req.params.month ? "/" + req.params.month : ""
		}${req.params.offset > 1 ? `/page/${req.params.offset}` : ""}`,
		baseDomain: "satyrs.eu",
		grimm: grimm,
		tr: tr,
		compileDebug: true,
		filters: filters,
		recentComments: recentComments,
		archives: archives
	};

	return prettyRender(res, next, "../views/garden/several", toSendOver);
};

const displayTagged = (req, res, next) => {
	req.params.offset = +(req.params?.offset ?? 1);

	const tag = db
		.prepare("Select * From tags Where tagID = ?")
		.get(req.params.tag);

	if (tag === undefined) {
		return next();
	}

	let offset = (req.params.offset - 1) * limit;
	if (isNaN(offset)) {
		return next();
	}

	let posts = db
		.prepare(
			`Select garden.*
			From garden
			Inner Join taggings
				On taggings.pageset = 'garden/' || garden.qualifiedSlug
			Where garden.public = 1
				And taggings.tag = @tag
			Order By garden.pageCreated Desc
			Limit ${limit}
			Offset ${offset}`
		)
		.all({
			tag: req.params.tag
		})
		.map(massageData);

	if (posts.length == 0) return next();

	const hasOlderPage = !!db
		.prepare(
			`Select garden.*
			From garden
			Inner Join taggings
				On taggings.pageset = 'garden/' || garden.qualifiedSlug
			Where garden.public = 1
				And taggings.tag = @tag
			Order By garden.pageCreated Desc
			Limit 1
			Offset ${offset + limit}`
		)
		.get({
			tag: req.params.tag
		});

	const tr = grimm.translator(tag.lang || "en");

	const toSendOver = {
		posts: posts,
		collation: {
			type: "tagged",
			hasOlderPage: hasOlderPage,
			tag: tag,
			title: tr("garden.collation.tagged")(tag.defaultName),
			richTitle: tr("garden.collation.tagged")(
				filters["rubric-inline"](tag.defaultName)
			),
			offset: req.params.offset
		},
		lang: tag.lang || "en",
		pagePath: `garden/tagged/${req.params.tag}${
			req.params.offset > 1 ? `/page/${req.params.offset}` : ""
		}`,
		baseDomain: "satyrs.eu",
		grimm: grimm,
		tr: tr,
		compileDebug: true,
		filters: filters,
		recentComments: recentComments,
		archives: archives
	};

	return prettyRender(res, next, "../views/garden/several", toSendOver);
};

const displayByLanguage = (req, res, next) => {
	req.params.offset = +(req.params?.offset ?? 1);

	if (!grimm.dict[req.params.lang]) {
		return next();
	}

	let offset = (req.params.offset - 1) * limit;
	if (isNaN(offset)) {
		return next();
	}

	let posts = db
		.prepare(
			`Select * From garden
			Where public = 1
				And lang = @lang
			Order By pageCreated Desc
			Limit ${limit}
			Offset ${offset}`
		)
		.all({
			lang: req.params.lang
		})
		.map(massageData);

	if (posts.length == 0) return next();

	const hasOlderPage = !!db
		.prepare(
			`Select * From garden
			Where public = 1
				And lang = @lang
			Order By pageCreated Desc
			Limit 1
			Offset ${offset + limit}`
		)
		.get({
			lang: req.params.lang
		});

	const tr = grimm.translator(req.params.lang);

	const toSendOver = {
		posts: posts,
		collation: {
			type: "language",
			hasOlderPage: hasOlderPage,
			title: tr("garden.collation.language"),
			richTitle: tr("garden.collation.language"),
			offset: req.params.offset
		},
		lang: req.params.lang,
		pagePath: `garden/lang/${req.params.lang}${
			req.params.offset > 1 ? `/page/${req.params.offset}` : ""
		}`,
		baseDomain: "satyrs.eu",
		grimm: grimm,
		tr: tr,
		compileDebug: true,
		filters: filters,
		recentComments: recentComments,
		archives: archives
	};

	return prettyRender(res, next, "../views/garden/several", toSendOver);
};

const displayAll = (req, res, next) => {
	req.params.offset = +(req.params?.offset ?? 1);

	let offset = (req.params.offset - 1) * limit;
	if (isNaN(offset)) {
		return next();
	}

	let posts = db
		.prepare(
			`Select * From garden
			Where public = 1
			Order By pageCreated Desc
			Limit ${limit}
			Offset ${offset}`
		)
		.all()
		.map(massageData);

	if (posts.length == 0) return next();

	const hasOlderPage = !!db
		.prepare(
			`Select * From garden
			Where public = 1
			Order By pageCreated Desc
			Limit 1
			Offset ${offset + limit}`
		)
		.get();

	const tr = grimm.translator("en");

	const toSendOver = {
		posts: posts,
		collation: {
			type: "all",
			hasOlderPage: hasOlderPage,
			title: tr("garden.siteName"),
			richTitle: null,
			offset: req.params.offset
		},
		lang: "en",
		pagePath: `garden${
			req.params.offset > 1 ? `/page/${req.params.offset}` : ""
		}`,
		baseDomain: "satyrs.eu",
		grimm: grimm,
		tr: tr,
		compileDebug: true,
		filters: filters,
		recentComments: recentComments,
		archives: archives
	};

	return prettyRender(res, next, "../views/garden/several", toSendOver);
};

const displayPreview = (req, res, next) => {
	let page = db
		.prepare(`Select * From garden Where slug = ? Limit 1`)
		.get(req.params.slug);

	if (page === undefined) {
		return next();
	}

	const lastPage = db
		.prepare(
			`Select qualifiedSlug, title
			From garden
			Where pageCreated < @pageCreated
				And lang = @lang
				And public = 1
			Order By pageCreated Desc
			Limit 1`
		)
		.get(page);

	const nextPage = db
		.prepare(
			`Select qualifiedSlug, title
			From garden
			Where pageCreated > @pageCreated
				And lang = @lang
				And public = 1
			Order By pageCreated Asc
			Limit 1`
		)
		.get(page);

	page = massageData(page);

	const toSendOver = {
		post: page,
		previous: lastPage,
		next: nextPage,
		title: page.title
			? page.title.replace(/&shy;/g, "").replace(/::/g, "")
			: grimm.translate(page.lang, "garden.post.untitled"),
		lang: page.lang,
		collation: {
			type: "single",
			title: page.title,
			richTitle: null
		},
		pagePath: page.fullSlug,
		baseDomain: "satyrs.eu",
		comments: getComments(page.fullSlug),
		grimm: grimm,
		tr: grimm.translator(page.lang),
		compileDebug: true,
		filters: filters,
		recentComments: recentComments,
		archives: archives
	};

	return prettyRender(res, next, "../views/garden/single", toSendOver);
};

const displayFeed = (req, res, next) => {
	const posts = db
		.prepare(
			`Select * From garden Where public = 1 Order By pageCreated Desc`
		)
		.all();
	const lastUpdated = db.prepare(`Select MAX(pageUpdated) From garden`).get()[
		"MAX(pageUpdated)"
	];

	for (const post of posts) {
		post.title = post.title
			? post.title.replace(/&shy;/g, "").replace(/::/g, "")
			: "Untitled";
	}

	const toSendOver = {
		posts: posts,
		lastUpdated: lastUpdated,
		baseDomain: "satyrs.eu",
		compileDebug: true,
		filters: filters
	};

	return res
		.set("Content-Type", "application/atom+xml")
		.render("../views/garden/feed", toSendOver);
};

router.get("/:year(\\d{4,})", displayChron);
router.get("/:year(\\d{4,})/page/:offset", displayChron);
router.get("/:year(\\d{4,})/:month(\\d{2})", displayChron);
router.get("/:year(\\d{4,})/:month(\\d{2})/page/:offset", displayChron);

router.get("/tagged/:tag", displayTagged);
router.get("/tagged/:tag/page/:offset", displayTagged);

router.get("/lang/:lang", displayByLanguage);
router.get("/lang/:lang/page/:offset", displayByLanguage);

router.get("/:year(\\d{4,})/:slug", displaySingle);
router.get("/:year(\\d{4,})/:slug/page/:offset", displaySingle);

router.get("/", displayAll);
router.get("/page/:offset", displayAll);

router.get("/feed", displayFeed);

router.get("/preview/:slug", auth("admin"), displayPreview);

router.get("/:slug", displaySingle);

export default router;