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 };
}