db_implementations.ts

import { connect, Client } from "ts-postgres";

import { COOKIE_TIMEOUT } from "./controller";

const SQL_CONNECTION = connect({
	user: "Matixannder",
	host: "localhost",
	port: 5432,
	database: "TODOAppDB",
});

type UIID = string;

type TaskInfo = {
	title: string;
	description: string;
}

export interface Tasks {
	[key: UIID]: TaskInfo;
}


export async function createTable(): Promise<void> {
	const psqlConnection = await SQL_CONNECTION;
	const createUserTable =
		`CREATE TABLE users (
			id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
			cookie_id VARCHAR(15),
			cookie_expiration_date_utc TIMESTAMP
	);`; 

	const createTasksTable = 
		`CREATE TABLE tasks (
			id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
			user_id BIGINT REFERENCES users (id),
			title VARCHAR(255),
			description TEXT
	);`;


	try {
		await psqlConnection.query(createUserTable);
	} catch (e) {
		console.log("User's table already exists");
	}

	try {
		await psqlConnection.query(createTasksTable);
	}  catch (e) {
		console.log("Tasks table already exists");
	}

}

// Why I don't just, like put the cookie time span as autogenerated by the DB?
function cookieTimeStamp(): string {
	let date = new Date().getTime();
	date += COOKIE_TIMEOUT * 1000;

	let newDate = new Date(date);
	return newDate.toISOString().replace("T", " ").replace(/\..*$/, ""); 
}

// Query to delete users when their cookie expires:
// delete from users where cookie_expiration_date_utc < now() at time zone 'utc';
//
// Because now every user id is related to the tasks table, the query 
// must delete the tasks first, or it'll throw an error

export async function createUser(cookie_id: string): Promise<void> {
	let psqlConnection = await SQL_CONNECTION;
	let raw_sessionId = cookie_id.replace(/sessionId=/g, "");

	try {
		await psqlConnection.query(`SELECT * FROM users WHERE cookie_id='${raw_sessionId}'`);
	} catch (e) {
		console.log("User already exists");
		console.log(e);
	}

	let createUserQuery = 
		`INSERT INTO users (cookie_id, cookie_expiration_date_utc)
		VALUES (${raw_sessionId}, '${cookieTimeStamp()}');`;

	await psqlConnection.query(createUserQuery);
}

export async function getUserTasks(userCookie: string): Promise<Tasks | void> {
	const raw_sessionId = userCookie.replace(/sessionId=/g, "");

	const psqlConnection = await SQL_CONNECTION;
	const getTasksQuery = 
		`SELECT task_uuid, title, description FROM tasks
		WHERE user_id=
		(SELECT id FROM users WHERE cookie_id='${raw_sessionId}')`;

	const getTasks = await psqlConnection.query(getTasksQuery);

	if (getTasks.status !== "SELECT 0") {
		const user_tasks: Tasks = {};
		const UUID = 0, TITLE = 1, DESCRIPTION = 2;

		for (let row of getTasks.rows) {
			user_tasks[row[UUID]] = {
				title: row[TITLE],
				description: row[DESCRIPTION]
			}
		}
		return user_tasks;
	}
	else {
		return;
	}
}

export async function createTask(userCookie: string, task: TaskInfo): Promise<void> {
	let psqlConnection  = await SQL_CONNECTION;
	const cookie = userCookie.replace(/sessionId=/g, "");
	const lookForUser = `SELECT id FROM users WHERE cookie_id='${cookie}'`
	const createNewTaskQuery = 
		`INSERT INTO tasks (user_id, title, description)
		 VALUES ((${lookForUser}), ${task.title}, ${task.description})`;
	
	// Why the hell this is "any"?
	let result: any;

	try {
		result = await psqlConnection.query(createNewTaskQuery);
	} catch (e) {
		console.log("An error ocurred");
		console.log(cookie);
		console.log(task.title);
		console.log(task.description);
		console.log(e);
	}
	console.log(result);
	return;
}

export async function getTask(task_uuid: String): Promise<TaskInfo>
{
	let psqlConnection  = await SQL_CONNECTION;

	const query = await psqlConnection.query(
		`SELECT title, description FROM tasks
		 WHERE task_uuid='${task_uuid}'`);

	const info = query.rows[0]
	const task: TaskInfo = {
		title: info[0],
		description: info[1]
	}

	return task;
}