1const db = getDatabase("questions.sqlite");
2
3/** how each type is stored */
4export const types = { normal: 0, reject: 1, pending: 3 } as const;
5export type Type = keyof typeof types;
6const dbQuestionType = sqliteNumericEnum(types);
7
8const dbQuestions = sqliteTable("questions", {
9 /**
10 * questions are identified by a 'minute identifier'
11 * which is time / 60000 (minutes since epoch)
12 */
13 qmid: integer().primaryKey(),
14 type: dbQuestionType().notNull(),
15 /** for type="pending" questions, this is a JSON payload */
16 text: text().notNull(),
17});
18db.migrate(dbQuestions, 1);
19
20export type Question = ReturnType<typeof toQuestion>;
21
22/** the payload of a question that has not been answered */
23export type PendingData = {
24 date: string;
25 prompt: string;
26 sourceName: string | "unknown";
27 sourceLocation: string | "unknown";
28 sourceVPN: string | null;
29};
30
31function toQuestion(q: typeof dbQuestions.$inferSelect) {
32 const date = new Date(q.qmid * 1000 * 60);
33 return {
34 /** "https://paperclover.net/q+a/${id}" - #YYMMDDHHMM */
35 id: formatQuestionId(date),
36 ...q,
37 date,
38 };
39}
40
41const toQmid = (date: Date) => Math.floor(date.getTime() / 1000 / 60);
42
43export function getAll() {
44 return db.select().from(dbQuestions)
45 .where(eq(dbQuestions.type, "normal"))
46 .orderBy(desc(dbQuestions.qmid))
47 .all().map(toQuestion);
48}
49
50/** what is in the inbox, with each question's payload beside it */
51export function getAllPending() {
52 return db.select().from(dbQuestions)
53 .where(eq(dbQuestions.type, "pending"))
54 .orderBy(desc(dbQuestions.qmid))
55 .all().map((q) => ({ ...JSON.parse(q.text) as PendingData, ...toQuestion(q) }));
56}
57
58export function getByDate(timestamp: Date) {
59 // Older questions are inserted with second precision,
60 // but that data should always be floored.
61 const q = db.select().from(dbQuestions).where(eq(dbQuestions.qmid, toQmid(timestamp))).get();
62 return q ? toQuestion(q) : null;
63}
64
65/** Create a new Question, rounding the date to the nearest available slot */
66export function create(type: Type, text: string, date = new Date()) {
67 const { qmid } = db.get<{ qmid: number }>(sql`
68 with recursive timestamp(value) as (
69 select ${toQmid(date)}
70 union all
71 select value + 1
72 from timestamp
73 where exists (select 1 from questions where qmid = value)
74 )
75 insert into questions (type, text, qmid)
76 values (${types[type]}, ${text}, (
77 select value from timestamp
78 where not exists (select 1 from questions where qmid = value)
79 limit 1
80 ))
81 returning qmid
82 `);
83 return new Date(qmid * 1000 * 60);
84}
85
86export function deleteByQmid(qmid: number) {
87 db.delete(dbQuestions).where(eq(dbQuestions.qmid, qmid)).run();
88}
89
90export function rejectByQmid(qmid: number) {
91 db.update(dbQuestions).set({ type: "reject" }).where(eq(dbQuestions.qmid, qmid)).run();
92}
93
94export function updateByQmid(qmid: number, text: string, type: Type) {
95 db.update(dbQuestions).set({ text, type }).where(eq(dbQuestions.qmid, qmid)).run();
96}
97
98/** answered questions that link to `ref`, as `@ref` or by url */
99export function search(ref: string) {
100 const links = or(
101 like(dbQuestions.text, `%@${ref}%`),
102 like(dbQuestions.text, `%https://paperclover.net/${ref}%`),
103 );
104 return db.select().from(dbQuestions)
105 .where(and(eq(dbQuestions.type, "normal"), links))
106 .all().map(toQuestion);
107}
108
109import { getDatabase, sqliteNumericEnum } from "#sitegen/drizzle";
110import { and, desc, eq, like, or, sql } from "drizzle-orm";
111import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";
112import { formatQuestionId } from "../format.ts";