done lol

2 files changed, 117 insertions, 17 deletions

diff --git a/src/database/postgres.ts b/src/database/postgres.ts
index 2ea19bd..677a106 100644
--- a/src/database/postgres.ts
+++ b/src/database/postgres.ts
@@ -1,10 +1,16 @@
-import { Client } from 'pg';
+import type { Thread, Comment }  from '../models/Thread';
+import { Client, types } from 'pg';
+
 var connectionString = "postgres://scale:secret@db/scale?sslmode=disable";
 let client: Client;
 
 export function InitPostgres() {
     client = new Client(connectionString);
     client.connect();
+
+    types.setTypeParser(types.builtins.INT8, (val) => {
+        return Number(val)
+    });
 }
 
 export function ClosePostgres() {
@@ -31,9 +37,9 @@ where nspname != 'pg_toast' and nspname != 'pg_catalog'
 and nspname != 'public' and nspname != 'information_schema';`; // to remove the default schemas
 
     client.query(query, (err, res) => {
-        let r: { status: number, data: any } = { status: 500, data: undefined };
+        let r: { status: number, data: any } = { status: 404, data: undefined };
         if (err) {
-            r.status = 404; r.data = err.message;
+            r.data = err.message;
         } else {
             r.status = 200; r.data = [];
             for(let board of res.rows)
@@ -46,20 +52,24 @@ and nspname != 'public' and nspname != 'information_schema';`; // to remove the
 export function CreateBoard(name: string, callback: (r: { status: number, data: any }) => void) {
     let query = `
 create schema ${name};
-create table ${name}.threads(
+create table ${name}.thread(
   id serial primary key,
   timestamp bigint,
-  title text,
-  content text,
+  title text NOT NULL,
+  content text NOT NULL,
   image text,
   imagetype text
 );
-create table ${name}.comments(
+create table ${name}.comment(
   id serial primary key,
+  tid int,
   timestamp bigint,
-  content text,
+  content text NOT NULL,
   image text,
-  imagetype text
+  imagetype text,
+  constraints fk_tid
+    foreign key(tid)
+      references ${name}.thread(id)
 );`;
 
     client.query(query, (err, res) => {
@@ -72,3 +82,88 @@ create table ${name}.comments(
         callback(r);
     });
 }
+
+export function GetBoard(board: string, callback: (r: { status: number, data: any }) => void) {
+    let query = `
+select * from ${board}.thread
+order by timestamp desc;`;
+
+    client.query(query, (err, res) => {
+        let r: { status: number, data: any } = { status: 404, data: undefined };
+        if (err) {
+            r.data = err.message;
+        } else {
+            r.status = 200; r.data = res.rows;
+        }
+        callback(r);
+    });
+}
+
+
+export function CreateThread(board: string, t: Thread, callback: (r: { status: number, data: any }) => void) {
+    let query = `
+insert into ${board}.thread (
+  timestamp, title, content, image, imagetype
+) values (
+  ${Math.floor(Date.now() / 1000)},
+  '${t.title}', '${t.content}', '${t.image}', '${t.imagetype}'
+);`;
+
+    client.query(query, (err, res) => {
+        let r: { status: number, data: any } = { status: 404, data: undefined };
+        if (err) {
+            r.data = err.message;
+        } else {
+            r.status = 200;
+        }
+        callback(r);
+    });
+}
+
+export function GetThread(board: string, tid: string, callback: (r: { status: number, data: any }) => void) {
+    // copied from https://www.youtube.com/watch?v=_lLqt5exlMs&t=114s
+    let query = `
+with thread_comments as (
+  select DISTINCT c.tid, (
+    select jsonb_agg(_)
+    from (select id, timestamp, content, image, imagetype
+      from ${board}.comment cc where cc.tid = c.tid)
+    as _ order by c.timestamp asc
+    ) as comment
+  from ${board}.comment c
+)
+select t.*, COALESCE(c.comment, '[]'::jsonb) as comment
+from ${board}.thread t
+left join thread_comments c on (c.tid = t.id)
+where t.id = ${tid};`;
+
+    client.query(query, (err, res) => {
+        let r: { status: number, data: any } = { status: 404, data: undefined };
+        if (err) {
+            r.data = err.message;
+        } else if(res.rows.length != 0) {
+            r.status = 200; r.data = (res.rows as Array<Thread>)[0];
+        }
+        callback(r);
+    });
+}
+
+export function CreateComment(board: string, tid: string, comment: Comment, callback: (r: { status: number, data: any }) => void) {
+    let query = `
+insert into ${board}.comment (
+  tid, timestamp, content, image, imagetype
+) values (
+  ${tid}, ${Math.floor(Date.now() / 1000)},
+  '${comment.content}', '${comment.image}', '${comment.imagetype}'
+);`;
+
+    client.query(query, (err, res) => {
+        let r: { status: number, data: any } = { status: 404, data: undefined };
+        if (err) {
+            r.data = err.message;
+        } else {
+            r.status = 200;
+        }
+        callback(r);
+    });
+}
diff --git a/src/routes/board.ts b/src/routes/board.ts
index caa2d53..1a60b97 100644
--- a/src/routes/board.ts
+++ b/src/routes/board.ts
@@ -1,6 +1,6 @@
 import express from 'express';
 import type { Thread, Comment }  from '../models/Thread';
-import { CreateBoard, CustomQuery, GetBoards } from '../database/postgres'
+import { CustomQuery, GetBoards, CreateBoard, GetBoard, CreateThread, GetThread, CreateComment } from '../database/postgres'
 
 const router = express.Router();
 
@@ -23,22 +23,27 @@ router.post('/boards/:board', (req, res) => {
 });
 
 router.get('/board/:board', (req, res) => {
-    res.status(404).send(req.params.board);
+    GetBoard(req.params.board, (r) => {
+        res.status(r.status).send(r.data);
+    });
 });
 
 router.post('/board/:board', (req, res) => {
-    let t: Thread = req.body;
-    console.log(t);
-    res.status(403).send({board: req.params.board, thread: t});
+    CreateThread(req.params.board, req.body, (r) => {
+        res.status(r.status).send(r.data);
+    });
 });
 
 router.get('/board/:board/:tid', (req, res) => {
-    res.status(404).send([req.params.board, req.params.tid]);
+    GetThread(req.params.board, req.params.tid, (r) => {
+        res.status(r.status).send(r.data);
+    });
 });
 
 router.post('/board/:board/:tid', (req, res) => {
-    let c: Comment = req.body;
-    res.status(404).send({params: [req.params.board, req.params.tid], body: c});
+    CreateComment(req.params.board, req.params.tid, req.body, (r) => {
+        res.status(r.status).send(r.data);
+    });
 });
 
 export default router;