1 files changed, 84 insertions, 0 deletions
diff --git a/app/src/lib/server/assignments.ts b/app/src/lib/server/assignments.ts
new file mode 100644
index 0000000..7622408
--- /dev/null
+++ b/app/src/lib/server/assignments.ts
@@ -0,0 +1,84 @@
+import type pg from "pg";
+
+/** A row from the `cemetary_plot` table. */
+export interface CemetaryPlot {
+	id: number;
+	address: string;
+	ownerId: number;
+	//assignmentInterval: Date;
+}
+
+/** A row from the `assignments` table. */
+export interface Assignment {
+	id: number;
+	gardenerId: number;
+	cemetaryPlotId: number;
+	date: Date;
+}
+
+/**
+ * Retrieves all assignments for the given user.
+ *
+ * @param dbConn Connection to database.
+ * @param userId ID used to identify user.
+ */
+export async function getAssignments(dbConn: pg.ClientBase, userId: number): Promise<Assignment[]> {
+	const result = await dbConn.query(
+		"SELECT * FROM assignments WHERE gardener_id = $1 ORDER BY date",
+		[userId],
+	);
+	return result.rows.map(
+		(r) =>
+			({
+				id: r.id,
+				gardenerId: r.gardener_id,
+				cemetaryPlotId: r.cemetary_plot_id,
+				date: r.date,
+			}) satisfies Assignment,
+	);
+}
+
+type GetAssignmentResult =
+	| { assignment: Assignment; cemetaryPlot: CemetaryPlot }
+	| { assignment: null; cemetaryPlot: null };
+
+/**
+ * Retrieves a specfic assignment, along with relevant cemetary plot.
+ */
+export async function getAssignmentAndCemetaryById(
+	dbConn: pg.ClientBase,
+	assignmentId: number,
+): Promise<GetAssignmentResult> {
+	const queryText = `SELECT
+		a.id,
+		a.gardener_id,
+		a.cemetary_plot_id,
+		a.date,
+		c.id,
+		c.address,
+		c.owner_id,
+		c.assignment_interval
+	FROM
+		assignments AS a
+	INNER JOIN
+		cemetary_plots AS C ON c.id = a.cemetary_plot_id
+	WHERE c.id = $1`;
+	const result = await dbConn.query({ rowMode: "array", text: queryText }, [assignmentId]);
+	if (result.rowCount == 0) {
+		return { assignment: null, cemetaryPlot: null };
+	}
+
+	const assignment: Assignment = {
+		id: result.rows[0][0],
+		gardenerId: result.rows[0][1],
+		cemetaryPlotId: result.rows[0][2],
+		date: result.rows[0][3],
+	};
+	const cemetaryPlot: CemetaryPlot = {
+		id: result.rows[0][4],
+		address: result.rows[0][5],
+		ownerId: result.rows[0][6],
+		//assignmentInterval: result.rows[0][7],
+	};
+	return { assignment, cemetaryPlot };
+}