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() {
    client.end();
}

export function CustomQuery(query: string, callback: (r: { status: number, data: any }) => void) {
    client.query(query, (err, res) => {
        let r: { status: number, data: any } = { status: 500, data: undefined };
        if (err) {
            r.data = err.message;
        } else {
            r.status = 200; r.data = res.rows;
        }
        callback(r);
    });
}

export function GetBoards(callback: (r: { status: number, data: any }) => void) {
    let query = `
select s.nspname as board
from pg_catalog.pg_namespace s
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: 404, data: undefined };
        if (err) {
            r.data = err.message;
        } else {
            r.status = 200; r.data = [];
            for(let board of res.rows)
                r.data.push(board.board);
        }
        callback(r);
    });
}

export function CreateBoard(name: string, callback: (r: { status: number, data: any }) => void) {
    let query = `
create schema ${name};
create table ${name}.thread(
  id serial primary key,
  timestamp bigint,
  title text NOT NULL,
  content text NOT NULL,
  image text,
  imagetype text
);
create table ${name}.comment(
  id serial primary key,
  tid int,
  timestamp bigint,
  content text NOT NULL,
  image text,
  imagetype text,
  constraints fk_tid
    foreign key(tid)
      references ${name}.thread(id)
);`;

    client.query(query, (err, res) => {
        let r: { status: number, data: any } = { status: 500, data: undefined };
        if (err) {
            r.data = err.message;
        } else {
            r.status = 200;
        }
        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 comments
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);
    });
}