WorkshopTasker/server/utils/baaPagination.ts

/* global defineEventHandler, getQuery, createError, readBody, setResponseStatus */
import { QueryObject } from "ufo";
import { H3Event } from "h3";
import { ResultSetHeader } from "mysql2/promise";

import { data, database } from "./database";
import { isString } from "./isString";
import Snowflake from "~/utils/snowflake";
import { client } from "~/utils/types/database";

type queryType = {
  type: "before" | "after" | "around",
  id: string
} | {
  type: null
};

/**
 * Before, around, after pagination wrapper
 */
export default class BaaPagination<T extends {[k: string]: any}, keyType extends string = "id"> {
  readonly table: string;
  readonly key: keyType;
  readonly select: string;
  readonly groupBy: string;

  private get sqlGroupBy() {
    return this.groupBy !== "" ? `GROUP BY ${this.groupBy}` : "";
  }

  /**
   * Gets queryType for a given query with a value
   * @param query the query to parse
   * @throws if query malformed (multiple before/after/around)
   */
  static getLocationParameterType(query: QueryObject): queryType {
    const before = query.before;
    const after = query.after;
    const around = query.around;

    let setLocationParametersCount = 0;
    let rvalue: queryType = { type: null };

    if (isString(before)) {
      setLocationParametersCount++;
      rvalue = { type: "before", id: before };
    }

    if (isString(after)) {
      setLocationParametersCount++;
      rvalue = { type: "after", id: after };
    }

    if (isString(around)) {
      setLocationParametersCount++;
      rvalue = { type: "around", id: around };
    }

    if (setLocationParametersCount > 1) {
      throw createError({
        statusCode: 400,
        message: "multiple location parameters not allowed",
      });
    }

    return rvalue;
  }

  async getPagedResults(
    queryType: queryType,
    limit = 50,
    where = "",
    bind: Array<any> = [],
  ) {
    const sqlwhere = where !== "" ? `AND (${where})` : "";
    switch (queryType.type) {
      case "before": {
        const [data] = await database.query(
          `SELECT ${this.select}, CONVERT(\`${this.key}\`, CHAR) AS \`${this.key}\` FROM \`${this.table}\` WHERE \`${this.key}\` < ? ${sqlwhere} ORDER BY \`${this.key}\` DESC ${this.sqlGroupBy} LIMIT ?`,
          [queryType.id, ...bind, limit],
        ) as unknown as data<T>;
        return data;
      }
      case "after": {
        const [data] = await database.query(
          `SELECT ${this.select}, CONVERT(\`${this.key}\`, CHAR) AS \`${this.key}\` FROM \`${this.table}\` WHERE \`${this.key}\` > ? ${sqlwhere} ORDER BY \`${this.key}\` DESC ${this.sqlGroupBy} LIMIT ?`,
          [queryType.id, ...bind, limit],
        ) as unknown as data<T>;
        return data;
      }
      case "around": {
        const [data] = await database.query(
          ` SELECT ${this.select}, CONVERT(\`${this.key}\`, CHAR) AS \`${this.key}\` FROM (\n` +
          `(SELECT * FROM \`${this.table}\` WHERE \`${this.key}\` >= ? ${sqlwhere} ORDER BY \`${this.key}\` ${this.sqlGroupBy} ASC LIMIT ?)\n` +
          "UNION ALL\n" +
          `(SELECT ${this.select} FROM \`${this.table}\` WHERE \`${this.key}\` < ? ${sqlwhere} ORDER BY \`${this.key}\` DESC ${this.sqlGroupBy} LIMIT ?)\n` +
          `) as \`x\` ORDER BY \`${this.key}\` DESC`,
          [queryType.id, ...bind, Math.ceil(limit / 2), queryType.id, ...bind, Math.floor(limit / 2)],
        ) as unknown as data<T>;
        return data;
      }
      case null: {
        const [data] = await database.query(
          `SELECT ${this.select}, CONVERT(\`${this.key}\`, CHAR) AS \`${this.key}\` FROM \`${this.table}\` WHERE TRUE ${sqlwhere} ORDER BY \`${this.key}\` DESC ${this.sqlGroupBy} LIMIT ?`,
          [...bind, limit],
        ) as unknown as data<T>;
        return data;
      }
      default:
        throw createError("Not implemented");
    }
  }

  RESTget(
    e: H3Event,
    defaultLimit = 50,
    limitLimit = 200,
    where = "",
    bind: Array<any> = [],
  ) {
    const query = getQuery(e);

    let limit = defaultLimit;
    if (query.limit) limit = Number(query.limit);
    if (limit > limitLimit) {
      throw createError({
        statusCode: 400,
        message: `Cannot retrieve more than ${limitLimit} records`,
      });
    }
    if (limit <= 0) {
      throw createError({
        statusCode: 400,
        message: "Tried to retireve 0 or less records",
      });
    }

    const queryData = BaaPagination.getLocationParameterType(query);

    return this.getPagedResults(queryData, limit, where, bind);
  }

  async RESTpost<K extends keyof Omit<T, keyType>>(
    e: H3Event,
    fields: Array<K>,
    valueChecker: (obj: unknown) => obj is {[P in K]: T[P]},
  ) {
    const body = await readBody(e);
    const id = new Snowflake().toString();

    if (!valueChecker(body)) throw createError({ message: "Invalid body", statusCode: 400 });

    const arrayToInsert: Array<any> = [id];
    arrayToInsert.push(...fields.map(field => body[field]));

    await database.query(
      `INSERT INTO \`${this.table}\` ` +
      `(\`${this.key}\`,\`${fields.join("`, `")}\`) ` +
      "VALUES (" +
      "?, ".repeat(fields.length) +
      "?)",
      arrayToInsert,
    );

    setResponseStatus(e, 201);

    // FIXME: data may be turncated in the database
    //        either throw an error when data is too large or
    //        reply with turncated data
    return { id, ...body };
  }

  async RESTgetRecord(e: H3Event) {
    const key = e.context.params?.[this.key];
    const [data] = await database.query(
      `SELECT ${this.select}, CONVERT(\`${this.key}\`, CHAR) AS \`${this.key}\` FROM \`${this.table}\` WHERE \`${this.key}\` = ?`,
      [key],
    ) as data<T>;

    if (!data[0]) {
      throw createError({
        statusCode: 404,
      });
    }

    return data[0];
  }

  async RESTpatchRecord(
    e: H3Event,
    valueChecker: (obj: unknown) => obj is Partial<Omit<T, keyType>>,
  ) {
    const body = await readBody(e);
    const key = e.context.params?.[this.key];

    if (!valueChecker(body)) throw createError({ message: "Invalid body", statusCode: 400 });

    for (const [k, v] of Object.entries(body)) {
      // FIXME: use single database.query method instead of looping through keys and values
      const [res] = await database.query(
        // I believe it is safe to put key (k) in the template
        // because it is limited to 4 values here
        `UPDATE \`${this.table}\` SET \`${k}\` = ? WHERE \`${this.key}\` = ?`,
        [v, key],
      ) as unknown as [ResultSetHeader];

      if (res.affectedRows !== 1) {
        throw createError({
          statusCode: 404,
        });
      }
    }

    const [data] = await database.query(
      `SELECT ${this.select}, CONVERT(\`${this.key}\`, CHAR) AS \`${this.key}\` FROM \`${this.table}\` WHERE \`${this.key}\` = ?`,
      [key],
    ) as data<T>;

    return data[0];
  }

  async RESTdeleteRecord(e: H3Event) {
    const key = e.context.params?.[this.key];

    const [result] = await database.query(
      `DELETE FROM \`${this.table}\` WHERE \`${this.key}\` = ?`,
      [key],
    ) as unknown as [ResultSetHeader];

    if (result.affectedRows === 0) throw createError({ statusCode: 404 });

    return null;
  }

  async RESTrecordCount(
    e :H3Event,
    where = "",
    bind: Array<any> = [],
  ) {
    const sqlwhere = where !== "" ? `WHERE ${where}` : "";
    const [[data]] = await database.query(
      `SELECT COUNT(*) as \`count\` FROM \`${this.table}\` ${sqlwhere} ${this.sqlGroupBy}`,
      bind,
    ) as data<{count: number}>;

    if (!data) throw createError("Database returned no rows");
    return data;
  }

  constructor(
    table: string,
    key: keyType,
    select = "*",
    groupBy = "",
  ) {
    this.table = table;
    this.key = key;
    this.select = select;
    this.groupBy = groupBy;
  }
}