import { createPool, Pool, PoolConnection, SqlError } from "mariadb";
import { v4 } from "uuid";

import Configure from "src/system/Configure";
import { Comment } from "src/Comment";
import { Commentary } from "src/Commentry";
import { equalCipher, isTaehui } from "src/Utility";
import Logger from "src/system/Logger";

class DB {
  pool: Pool;

  constructor() {
    this.pool = createPool({
      host: Configure.db.remote,
      user: Configure.db.avatar,
      password: Configure.db.auth,
      database: Configure.db.db,
    });
  }

  async loadDB() {
    await this.getDB(async (db) => {
      await db.query(`CREATE TABLE IF NOT EXISTS tn_avatar (
        Avatar_ID VARCHAR(20),
        Avatar_Cipher TEXT,
        Avatar_Name TEXT,
        Avatar_IP TEXT,
        Fax TEXT,
        Level INTEGER,
        Date DATETIME,
        Avatar_Intro TEXT,
        Last_Date DATETIME,
        PRIMARY KEY (Avatar_ID)
      ) ENGINE=InnoDB`);

      await db.query(`CREATE TABLE IF NOT EXISTS tn_totem (
        Totem CHAR(36),
        Avatar_ID VARCHAR(20),
        Date DATETIME,
        PRIMARY KEY (Totem),
        FOREIGN KEY (Avatar_ID) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
      ) ENGINE=InnoDB`);

      await db.query(`CREATE TABLE IF NOT EXISTS tn_forum (
        Forum_ID VARCHAR(20),
        Title TEXT,
        Title_1042 TEXT,
        Level INTEGER,
        PRIMARY KEY (Forum_ID)
      ) ENGINE=InnoDB`);

      await db.query(`CREATE TABLE IF NOT EXISTS tn_essay (
        Essay_ID INTEGER AUTO_INCREMENT,
        Forum_ID VARCHAR(20),
        Avatar_ID VARCHAR(20),
        Date DATETIME,
        Title TEXT,
        Text TEXT,
        PRIMARY KEY (Essay_ID),
        FOREIGN KEY (Forum_ID) REFERENCES tn_forum(Forum_ID) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (Avatar_ID) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
      ) ENGINE=InnoDB`);

      await db.query(`CREATE TABLE IF NOT EXISTS tn_auto_essay (
        Auto_Essay_ID INTEGER AUTO_INCREMENT,
        Forum_ID VARCHAR(20),
        Avatar_ID VARCHAR(20),
        Title TEXT,
        Text TEXT,
        Date DATETIME,
        PRIMARY KEY (Auto_Essay_ID),
        FOREIGN KEY (Forum_ID) REFERENCES tn_forum(Forum_ID) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (Avatar_ID) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
      ) ENGINE=InnoDB`);

      await db.query(`CREATE TABLE IF NOT EXISTS tn_essay_hit (
        Essay_ID INTEGER,
        Date DATE,
        Avatar_IP VARCHAR(255),
        PRIMARY KEY (Essay_ID, Date, Avatar_IP),
        FOREIGN KEY (Essay_ID) REFERENCES tn_essay(Essay_ID) ON UPDATE CASCADE ON DELETE CASCADE
      ) ENGINE=InnoDB`);

      await db.query(`CREATE TABLE IF NOT EXISTS tn_file (
        Avatar_ID VARCHAR(20),
        File_Name VARCHAR(255),
        Essay_ID INTEGER,
        Posted_File_Name TEXT,
        PRIMARY KEY (File_Name),
        FOREIGN KEY (Avatar_ID) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (Essay_ID) REFERENCES tn_essay(Essay_ID) ON UPDATE CASCADE ON DELETE CASCADE
      ) ENGINE=InnoDB`);

      await db.query(`CREATE TABLE IF NOT EXISTS tn_file_hit (
        File_Name VARCHAR(255),
        Date DATE,
        Avatar_IP VARCHAR(255),
        PRIMARY KEY (File_Name, Date, Avatar_IP),
        FOREIGN KEY (File_Name) REFERENCES tn_file(File_Name) ON UPDATE CASCADE ON DELETE CASCADE
      ) ENGINE=InnoDB`);

      await db.query(`CREATE TABLE IF NOT EXISTS tn_comment (
        Comment_ID INTEGER AUTO_INCREMENT,
        Target_Comment_ID INTEGER,
        Essay_ID INTEGER,
        Avatar_ID VARCHAR(20),
        Date DATETIME,
        Text TEXT,
        PRIMARY KEY (Comment_ID),
        FOREIGN KEY (Essay_ID) REFERENCES tn_essay(Essay_ID) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (Avatar_ID) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
      ) ENGINE=InnoDB`);

      await db.query(`CREATE TABLE IF NOT EXISTS tn_commentary (
        Commentary_ID INTEGER AUTO_INCREMENT,
        Avatar_Name TEXT,
        Avatar_Cipher TEXT,
        Avatar_IP TEXT,
        Date DATETIME,
        Text TEXT,
        PRIMARY KEY (Commentary_ID)
      ) ENGINE=InnoDB`);

      await db.query(`CREATE TABLE IF NOT EXISTS tn_hit (
        Date DATE,
        Avatar_IP VARCHAR(255),
        PRIMARY KEY (Avatar_IP, Date)
      ) ENGINE=InnoDB`);
    });

    Logger.info("Loaded DB");
  }

  async isValidFile(fileName: string) {
    return this.getDB(
      async (db) =>
        !!(
          await db.query(
            `SELECT File_Name
              FROM tn_file
              WHERE File_Name = ?`,
            [fileName],
          )
        )[0],
    );
  }

  async ta<T>(
    onHandle: (db: PoolConnection) => Promise<T>,
  ): Promise<T | undefined> {
    return this.getDB<T | undefined>(async (db) => {
      try {
        await db.beginTransaction();
        const handled = onHandle(db);
        await db.commit();
        return handled;
      } catch (e) {
        await db.rollback();
        throw e;
      }
    });
  }

  async getDB<T>(onHandle: (db: PoolConnection) => Promise<T>) {
    let db;
    try {
      db = await this.pool.getConnection();
      return onHandle(db);
    } finally {
      db?.release();
    }
  }

  async getAvatarAsAvatarID(db: PoolConnection, avatarID: string) {
    const data = (
      await db.query(
        `SELECT Avatar_ID, Avatar_Cipher, Avatar_Name, Level, Fax, Avatar_Intro
          FROM tn_avatar
          WHERE Avatar_ID = ?`,
        [avatarID],
      )
    )[0];

    if (data) {
      return {
        avatarID: data["Avatar_ID"],
        avatarCipher: data["Avatar_Cipher"],
        avatarName: data["Avatar_Name"],
        level: data["Level"],
        fax: data["Fax"],
        avatarIntro: data["Avatar_Intro"],
      };
    }
  }

  async setLastDate(db: PoolConnection, avatarID: string, lastDate: string) {
    return db.query(
      `UPDATE tn_avatar
        SET Last_Date = ?
        WHERE Avatar_ID = ?`,
      [lastDate, avatarID],
    );
  }

  async getTotem(db: PoolConnection, avatarID: string) {
    const data = (
      await db.query(
        `SELECT Totem, Date
          FROM tn_totem
          WHERE Avatar_ID = ?`,
        [avatarID],
      )
    )[0];

    if (data) {
      return {
        totem: data["Totem"],
        date: data["Date"],
      };
    }
  }

  async setTotemDateAsAvatarID(
    db: PoolConnection,
    avatarID: string,
    datetime: string,
  ): Promise<void> {
    db.query(
      `UPDATE tn_totem
        SET Date = ?
        WHERE Avatar_ID = ?`,
      [datetime, avatarID],
    );
  }

  async putTotem(
    db: PoolConnection,
    avatarID: string,
    datetime: string,
  ): Promise<string> {
    const totem = v4();
    await db.query(
      `REPLACE INTO tn_totem
        VALUES(?, ?, ?)`,
      [totem, avatarID, datetime],
    );
    return totem;
  }

  async doModifyAvatar(
    db: PoolConnection,
    avatarID: string,
    avatarCipher: string,
    avatarCipherModified: string,
    avatarName: string,
    fax: string,
    avatarIntro: string,
  ) {
    const defaultAvatarCipher = (
      await db.query(
        `SELECT Avatar_Cipher
          FROM tn_avatar
          WHERE Avatar_ID = ?`,
        [avatarID],
      )
    )[0]?.["Avatar_Cipher"];
    return (
      defaultAvatarCipher &&
      (await equalCipher(defaultAvatarCipher, avatarCipher)) &&
      (
        await db.query(
          `UPDATE tn_avatar
            SET Avatar_Cipher = ?, Avatar_Name = ?, Fax = ?, Avatar_Intro = ?
            WHERE Avatar_ID = ?`,
          [
            avatarCipherModified || defaultAvatarCipher,
            avatarName,
            fax,
            avatarIntro,
            avatarID,
          ],
        )
      ).affectedRows > 0
    );
  }

  async doModifyAvatarIntro(avatarID: string, avatarIntro: string) {
    return this.getDB(async (db) => {
      return (
        (
          await db.query(
            `UPDATE tn_avatar
              SET Avatar_Intro = ?
              WHERE Avatar_ID = ?`,
            [avatarIntro, avatarID],
          )
        ).affectedRows > 0
      );
    });
  }

  async postAvatar(
    avatarID: string,
    avatarCipher: string,
    avatarName: string,
    avatarIP: string,
    fax: string,
    datetime: string,
  ) {
    return this.getDB(async (db) => {
      return (
        (
          await db.query(
            `INSERT INTO tn_avatar
              VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [
              avatarID,
              avatarCipher,
              avatarName,
              avatarIP,
              fax,
              1,
              datetime,
              "",
              datetime,
            ],
          )
        ).affectedRows > 0
      );
    });
  }

  async getAvatarAsTotem(db: PoolConnection, totem: string) {
    const data = (
      await db.query(
        `SELECT Avatar_ID, Date
          FROM tn_totem
          WHERE Totem = ?`,
        [totem],
      )
    )[0];
    if (data) {
      return {
        avatarID: data["Avatar_ID"],
        date: data["Date"],
      };
    }
  }

  async setTotemDateAsTotem(
    db: PoolConnection,
    totem: string,
    datetime: string,
  ) {
    await db.query(
      `UPDATE tn_totem
        SET Date = ?
        WHERE Totem = ?`,
      [datetime, totem],
    );
  }

  async wipeTotem(db: PoolConnection, avatarID: string) {
    const { affectedRows } = await db.query(
      `DELETE
        FROM tn_totem
        WHERE Avatar_ID = ?`,
      [avatarID],
    );
    return affectedRows;
  }

  async getForum(
    db: PoolConnection,
    forumID: string,
  ): Promise<
    | {
        title: string;
        title1042: string;
        level: number;
      }
    | undefined
  > {
    const data = (
      await db.query(
        `SELECT Title, Title_1042, Level
          FROM tn_forum
          WHERE Forum_ID = ?`,
        [forumID],
      )
    )[0];
    if (data) {
      return {
        title: data["Title"],
        title1042: data["Title_1042"],
        level: data["Level"],
      };
    }
  }

  async getForums(forumGroup?: string): Promise<
    {
      forumID: string;
      title: string;
      title1042: string;
    }[]
  > {
    return this.getDB(async (db) => {
      return (
        forumGroup
          ? await db.query(
              `SELECT Forum_ID, Title, Title_1042
                FROM tn_forum
                WHERE Forum_Group = ?`,
              [forumGroup],
            )
          : await db.query(
              `SELECT Forum_ID, Title, Title_1042
                FROM tn_forum`,
            )
      ).map(
        (data: { Forum_ID: string; Title: string; Title_1042: string }) => ({
          forumID: data["Forum_ID"],
          title: data["Title"],
          title1042: data["Title_1042"],
        }),
      );
    });
  }

  async getMostEssays(forumID: string) {
    return this.getEssays(forumID, 1, 5);
  }

  async getBeforeEssay(forumID: string, datetime: string) {
    return this.getDB(async (db) => {
      const dataBefore = (
        await db.query(
          `SELECT Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title
            FROM tn_essay, tn_avatar
            WHERE Forum_ID = ? AND tn_avatar.Avatar_ID = tn_essay.Avatar_ID AND tn_essay.Date < ?
            ORDER BY tn_essay.Date DESC
            LIMIT 1`,
          [forumID, datetime],
        )
      )[0];
      if (dataBefore) {
        const essayIDBefore = Number(dataBefore["Essay_ID"]);
        return {
          essayID: essayIDBefore,
          avatarID: dataBefore["Avatar_ID"],
          avatarName: dataBefore["Avatar_Name"],
          date: dataBefore["Date"],
          title: dataBefore["Title"],
          commentCount: Number(await this.getCommentCount(essayIDBefore)),
          hitCount: Number(await this.getHitCount(essayIDBefore)),
        };
      }
    });
  }

  async getLaterEssay(forumID: string, datetime: string) {
    return this.getDB(async (db) => {
      const dataLater = (
        await db.query(
          `SELECT Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title
            FROM tn_essay, tn_avatar
            WHERE Forum_ID = ? AND tn_avatar.Avatar_ID = tn_essay.Avatar_ID AND tn_essay.Date > ?
            ORDER BY tn_essay.Date
            LIMIT 1`,
          [forumID, datetime],
        )
      )[0];
      if (dataLater) {
        const essayIDLater = Number(dataLater["Essay_ID"]);
        return {
          essayID: essayIDLater,
          avatarID: dataLater["Avatar_ID"],
          avatarName: dataLater["Avatar_Name"],
          date: dataLater["Date"],
          title: dataLater["Title"],
          commentCount: Number(await this.getCommentCount(essayIDLater)),
          hitCount: Number(await this.getHitCount(essayIDLater)),
        };
      }
    });
  }

  async getLatestEssays() {
    return this.getDB(async (db) => {
      const rs = await db.query(
        `SELECT Forum_ID, Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title, tn_essay.Text
          FROM tn_essay, tn_avatar
          WHERE tn_avatar.Avatar_ID = tn_essay.Avatar_ID
          ORDER BY tn_essay.Date DESC
          LIMIT 5`,
      );
      return Promise.all(
        rs.map(
          async (data: {
            Forum_ID: string;
            Essay_ID: number;
            Avatar_ID: string;
            Avatar_Name: string;
            Date: string;
            Title: string;
            Text: string;
          }) => {
            const forumID = data["Forum_ID"];
            const forumTitles = await this.getForumTitles(forumID);
            if (forumTitles) {
              const essayID = Number(data["Essay_ID"]);
              return {
                forumID,
                forumTitle: forumTitles.title,
                forumTitle1042: forumTitles.title1042,
                essayID,
                avatarID: data["Avatar_ID"],
                avatarName: data["Avatar_Name"],
                date: data["Date"],
                title: data["Title"],
                text: data["Text"],
                commentCount: Number(await this.getCommentCount(essayID)),
                hitCount: Number(await this.getHitCount(essayID)),
              };
            }
          },
        ),
      );
    });
  }

  async getCommentCount(essayID: number) {
    return this.getDB(async (db) => {
      const data = (
        await db.query(
          `SELECT COUNT(Comment_ID) AS Comment_Count
            FROM tn_comment
            WHERE Essay_ID = ?`,
          [essayID],
        )
      )[0];
      if (data) {
        return data["Comment_Count"];
      }
    });
  }

  async getHitCount(essayID: number) {
    return this.getDB(async (db) => {
      const data = (
        await db.query(
          `SELECT COUNT(Avatar_IP) AS Hit_Count
            FROM tn_essay_hit
            WHERE Essay_ID = ?`,
          [essayID],
        )
      )[0];
      if (data) {
        return data["Hit_Count"];
      }
    });
  }

  async wipeFileNames(fileNames: string[]) {
    await this.ta(async (db) => {
      for await (const fileName of fileNames) {
        await db.query(
          `DELETE
            FROM tn_file
            WHERE File_Name = ?`,
          [fileName],
        );
      }
    });
  }

  async putFileNames(
    avatarID: string,
    essayID: number,
    fileNames: [string, string][],
  ) {
    return this.getDB(async (db) => {
      for await (const [fileName, postedFileName] of fileNames) {
        await db.query(
          `INSERT INTO tn_file
            VALUES(?, ?, ?, ?)`,
          [avatarID, fileName, essayID, postedFileName],
        );
      }
    });
  }

  async getFileNames(db: PoolConnection, essayID: number): Promise<string[]> {
    return (
      await db.query(
        `SELECT File_Name
          FROM tn_file
          WHERE Essay_ID = ?`,
        [essayID],
      )
    ).map((data: { File_Name: string }) => data["File_Name"]);
  }

  async postEssay(
    db: PoolConnection,
    forumID: string,
    avatarID: string,
    datetime: string,
    title: string,
    text: string,
  ): Promise<number> {
    const { insertId } = await db.query(
      `INSERT INTO tn_essay(Forum_ID, Avatar_ID, Date, Title, Text)
        VALUES(?, ?, ?, ?, ?)`,
      [forumID, avatarID, datetime, title, text],
    );
    return insertId;
  }

  async doModifyEssay(
    db: PoolConnection,
    essayID: number,
    avatarID: string,
    title: string,
    text: string,
    level: number,
  ): Promise<boolean> {
    const { affectedRows } = isTaehui(level)
      ? await db.query(
          `UPDATE tn_essay
            SET Title = ?, Text = ?
            WHERE Essay_ID = ?`,
          [title, text, essayID],
        )
      : await db.query(
          `UPDATE tn_essay
            SET Title = ?, Text = ?
            WHERE Essay_ID = ? AND Avatar_ID = ?`,
          [title, text, essayID, avatarID],
        );
    return affectedRows > 0;
  }

  async wipeEssay(
    essayID: number,
    avatarID: string,
    level: number,
  ): Promise<boolean> {
    return this.getDB(async (db) => {
      const { affectedRows } = isTaehui(level)
        ? await db.query(
            `DELETE 
              FROM tn_essay
              WHERE Essay_ID = ?`,
            [essayID],
          )
        : await db.query(
            `DELETE 
              FROM tn_essay
              WHERE Essay_ID = ? AND Avatar_ID = ?`,
            [essayID, avatarID],
          );
      return affectedRows > 0;
    });
  }

  async getForumTitles(forumID: string) {
    return this.getDB(async (db) => {
      const data = (
        await db.query(
          `SELECT Title, Title_1042
            FROM tn_forum
            WHERE Forum_ID = ?`,
          [forumID],
        )
      )[0];

      if (data) {
        return {
          title: data["Title"],
          title1042: data["Title_1042"],
        };
      }
    });
  }

  async getEssay(essayID: number) {
    return this.getDB(async (db) => {
      const data = (
        await db.query(
          `SELECT Forum_ID, Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title, Text
            FROM tn_essay, tn_avatar
            WHERE Essay_ID = ? AND tn_avatar.Avatar_ID = tn_essay.Avatar_ID`,
          [essayID],
        )
      )[0];

      if (data) {
        const forumID = data["Forum_ID"];
        const forumTitles = await this.getForumTitles(forumID);
        if (forumTitles) {
          const essayID = Number(data["Essay_ID"]);
          const date = data["Date"];
          return {
            forumTitle: forumTitles.title,
            forumTitle1042: forumTitles.title1042,
            essayID,
            avatarID: data["Avatar_ID"],
            avatarName: data["Avatar_Name"],
            date,
            title: data["Title"],
            text: data["Text"],
            hitCount: Number(await this.getHitCount(essayID)),
            essayBefore: await this.getBeforeEssay(forumID, date),
            essayLater: await this.getLaterEssay(forumID, date),
          };
        }
      }
    });
  }

  async getEssayCount(forumID: string) {
    return this.getDB(async (db) => {
      const data = (
        await db.query(
          `SELECT COUNT(Essay_ID) AS Essay_Count
            FROM tn_essay
            WHERE Forum_ID = ?`,
          [forumID],
        )
      )[0];
      if (data) {
        return data["Essay_Count"];
      }
    });
  }

  async getEssays(forumID: string, page: number, viewUnit: number) {
    return this.getDB(async (db) => {
      const rs = await db.query(
        `SELECT Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title
          FROM tn_essay, tn_avatar
          WHERE Forum_ID = ? AND tn_avatar.Avatar_ID = tn_essay.Avatar_ID
          ORDER BY tn_essay.Date DESC
          LIMIT ?, ?`,
        [forumID, (page - 1) * viewUnit, viewUnit],
      );
      return Promise.all(
        rs.map(
          async (data: {
            Essay_ID: number;
            Avatar_ID: string;
            Avatar_Name: string;
            Date: string;
            Title: string;
          }) => {
            const essayID = Number(data["Essay_ID"]);
            return {
              essayID: essayID,
              avatarID: data["Avatar_ID"],
              avatarName: data["Avatar_Name"],
              date: data["Date"],
              title: data["Title"],
              commentCount: Number(await this.getCommentCount(essayID)),
              hitCount: Number(await this.getHitCount(essayID)),
            };
          },
        ),
      );
    });
  }

  async wantEssay(wantInput: string, page: number, viewUnit: number) {
    return this.getDB(async (db) => {
      return Promise.all(
        (
          await db.query(
            `SELECT Forum_ID, Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title
              FROM tn_essay, tn_avatar
              WHERE Text LIKE ? AND tn_avatar.Avatar_ID = tn_essay.Avatar_ID
              ORDER BY tn_essay.Date DESC
              LIMIT ?, ?`,
            [`%${wantInput}%`, (page - 1) * viewUnit, viewUnit],
          )
        ).map(
          async (data: {
            Forum_ID: string;
            Essay_ID: number;
            Avatar_ID: string;
            Avatar_Name: string;
            Date: string;
            Title: string;
          }) => {
            const essayID = Number(data["Essay_ID"]);
            return {
              forumID: data["Forum_ID"],
              essayID: essayID,
              avatarID: data["Avatar_ID"],
              avatarName: data["Avatar_Name"],
              date: data["Date"],
              title: data["Title"],
              commentCount: Number(await this.getCommentCount(essayID)),
              hitCount: Number(await this.getHitCount(essayID)),
            };
          },
        ),
      );
    });
  }

  async wantComment(wantInput: string, page: number, viewUnit: number) {
    return this.getDB(async (db) => {
      return Promise.all(
        (
          await db.query(
            `SELECT Forum_ID, Essay_ID, tn_comment.Avatar_ID, Avatar_Name, tn_comment.Date, Text
              FROM tn_comment, tn_avatar
              WHERE Text LIKE ? AND tn_avatar.Avatar_ID = tn_comment.Avatar_ID
              ORDER BY tn_comment.Date DESC
              LIMIT ?, ?`,
            [`%${wantInput}%`, (page - 1) * viewUnit, viewUnit],
          )
        ).map(
          async (data: {
            Forum_ID: string;
            Essay_ID: number;
            Avatar_ID: string;
            Avatar_Name: string;
            Date: string;
            Text: string;
          }) => {
            return {
              forumID: data["Forum_ID"],
              essayID: Number(data["Essay_ID"]),
              avatarID: data["Avatar_ID"],
              avatarName: data["Avatar_Name"],
              date: data["Date"],
              text: data["Text"],
            };
          },
        ),
      );
    });
  }

  async postEssayHit(essayID: number, avatarIP: string, date: string) {
    return this.getDB(async (db) => {
      try {
        await db.query(
          `INSERT INTO tn_essay_hit
            VALUES(?, ?, ?)`,
          [essayID, date, avatarIP],
        );
      } catch (e) {
        if (!(e instanceof SqlError) || e.errno !== 1062) {
          throw e;
        }
      }
    });
  }

  async getEssayHit(essayID: number) {
    return this.getDB(async (db) => {
      return Number(
        (
          await db.query(
            `SELECT COUNT(Avatar_IP) AS Count
              FROM tn_essay_hit
              WHERE Essay_ID = ?`,
            [essayID],
          )
        )[0]?.Count ?? 0,
      );
    });
  }

  async postComment(
    targetCommentID: number,
    essayID: number,
    avatarID: string,
    datetime: string,
    text: string,
  ): Promise<boolean> {
    return this.getDB(async (db) => {
      const { insertId } = await db.query(
        `INSERT INTO tn_comment(Target_Comment_ID, Essay_ID, Avatar_ID, Date, Text)
          VALUES(?, ?, ?, ?, ?)`,
        [targetCommentID, essayID, avatarID, datetime, text],
      );
      return insertId > 0;
    });
  }

  async doModifyComment(
    commentID: number,
    avatarID: string,
    text: string,
    level: number,
  ): Promise<boolean> {
    return this.getDB(async (db) => {
      const { affectedRows } = isTaehui(level)
        ? await db.query(
            `UPDATE tn_comment
              SET Text = ?
              WHERE Comment_ID = ?`,
            [text, commentID],
          )
        : await db.query(
            `UPDATE tn_comment
              SET Text = ?
              WHERE Comment_ID = ? AND Avatar_ID = ?`,
            [text, commentID, avatarID],
          );
      return affectedRows > 0;
    });
  }

  async wipeComment(
    commentID: number,
    avatarID: string,
    level: number,
  ): Promise<boolean> {
    return this.getDB(async (db) => {
      const { affectedRows } = isTaehui(level)
        ? await db.query(
            `DELETE 
              FROM tn_comment
              WHERE Comment_ID = ?`,
            [commentID],
          )
        : await db.query(
            `DELETE 
              FROM tn_comment
              WHERE Comment_ID = ? AND Avatar_ID = ?`,
            [commentID, avatarID],
          );
      return affectedRows > 0;
    });
  }

  async getComments(essayID: number): Promise<Comment[]> {
    return this.getDB(async (db) => {
      const rs = await db.query(
        `SELECT Comment_ID, Target_Comment_ID, tn_comment.Avatar_ID, Avatar_Name, tn_comment.Date, Text
          FROM tn_comment, tn_avatar
          WHERE Essay_ID = ? AND tn_avatar.Avatar_ID = tn_comment.Avatar_ID
          ORDER BY tn_comment.Date`,
        [essayID],
      );
      return rs.map(
        (data: {
          Comment_ID: number;
          Target_Comment_ID: number;
          Avatar_ID: string;
          Avatar_Name: string;
          Date: string;
          Text: string;
        }) => ({
          commentID: data["Comment_ID"],
          targetCommentID: data["Target_Comment_ID"],
          avatarID: data["Avatar_ID"],
          avatarName: data["Avatar_Name"],
          date: data["Date"],
          text: data["Text"],
        }),
      );
    });
  }

  async getLatestComments() {
    return this.getDB(async (db) => {
      const rs = await db.query(
        `SELECT Comment_ID, Forum_ID, tn_comment.Essay_ID AS Essay_ID, tn_comment.Avatar_ID, Avatar_Name, tn_comment.Date, tn_comment.Text
          FROM tn_comment, tn_essay, tn_avatar
          WHERE tn_comment.Essay_ID = tn_essay.Essay_ID AND tn_avatar.Avatar_ID = tn_comment.Avatar_ID
          ORDER BY tn_comment.Date DESC
          LIMIT 5`,
      );
      return rs.map(
        (data: {
          Comment_ID: number;
          Forum_ID: string;
          Essay_ID: number;
          Avatar_ID: string;
          Avatar_Name: string;
          Date: string;
          Text: string;
        }) => ({
          commentID: data["Comment_ID"],
          forumID: data["Forum_ID"],
          essayID: data["Essay_ID"],
          avatarID: data["Avatar_ID"],
          avatarName: data["Avatar_Name"],
          date: data["Date"],
          text: data["Text"],
        }),
      );
    });
  }

  async getCommentary(): Promise<Commentary[]> {
    return this.getDB(async (db) => {
      const rs = await db.query(
        `SELECT Commentary_ID, Avatar_Name, Date, Text
          FROM tn_commentary
          ORDER BY Date DESC`,
      );
      return rs.map(
        (data: {
          Commentary_ID: number;
          Avatar_Name: string;
          Date: string;
          Text: string;
        }) => ({
          commentaryID: data["Commentary_ID"],
          avatarName: data["Avatar_Name"],
          date: data["Date"],
          text: data["Text"],
        }),
      );
    });
  }

  async postCommentary(
    avatarName: string,
    avatarCipher: string,
    avatarIP: string,
    datetime: string,
    text: string,
  ) {
    return this.getDB(async (db) => {
      const { insertId } = await db.query(
        `INSERT INTO tn_commentary(Avatar_Name, Avatar_Cipher, Avatar_IP, Date, Text)
          VALUES(?, ?, ?, ?, ?)`,
        [avatarName, avatarCipher, avatarIP, datetime, text],
      );
      return insertId > 0;
    });
  }

  async doModifyCommentary(
    commentaryID: number,
    avatarCipher: string,
    text: string,
  ): Promise<boolean> {
    return this.getDB(async (db) => {
      const { affectedRows } = await db.query(
        `UPDATE tn_commentary
          SET Text = ?
          WHERE Commentary_ID = ? AND Avatar_Cipher = ?`,
        [text, commentaryID, avatarCipher],
      );
      return affectedRows > 0;
    });
  }

  async wipeCommentary(
    commentaryID: number,
    avatarCipher: string,
  ): Promise<boolean> {
    return this.getDB(async (db) => {
      const { affectedRows } = await db.query(
        `DELETE 
          FROM tn_commentary
          WHERE Commentary_ID = ? AND Avatar_Cipher = ?`,
        [commentaryID, avatarCipher],
      );
      return affectedRows > 0;
    });
  }

  async getHit(date: string, dateBefore: string) {
    return this.getDB(async (db) => {
      const rs = await db.query(
        `SELECT DATE_FORMAT(Date, "%Y-%m-%d") AS Date, COUNT(Avatar_IP) AS Count
          FROM tn_hit
          GROUP BY Date
          HAVING Date = ? OR DATE = ?`,
        [dateBefore, date],
      );
      return [
        Number(
          rs.find(({ Date }: { Date: string }) => Date === dateBefore)?.Count ??
            0,
        ),
        Number(
          rs.find(({ Date }: { Date: string }) => Date === date)?.Count ?? 0,
        ),
      ];
    });
  }

  async postHit(avatarIP: string, date: string) {
    return this.getDB(async (db) => {
      try {
        await db.query(
          `INSERT INTO tn_hit
            VALUES(?, ?)`,
          [date, avatarIP],
        );
        return true;
      } catch (e) {
        if (!(e instanceof SqlError) || e.errno !== 1062) {
          throw e;
        }
        return false;
      }
    });
  }

  async postFileHit(fileName: string, avatarIP: string, date: string) {
    return this.getDB(async (db) => {
      try {
        await db.query(
          `INSERT INTO tn_file_hit
            VALUES(?, ?, ?)`,
          [fileName, date, avatarIP],
        );
      } catch (e) {
        if (!(e instanceof SqlError) || e.errno !== 1062) {
          throw e;
        }
      }
    });
  }

  async getPostedFileName(fileName: string) {
    return this.getDB(
      async (db) =>
        (
          await db.query(
            `SELECT Posted_File_Name
              FROM tn_file
              WHERE File_Name = ?`,
            [fileName],
          )
        )[0]?.["Posted_File_Name"],
    );
  }

  async getFileHit(fileName: string) {
    return this.getDB(async (db) => {
      return Number(
        (
          await db.query(
            `SELECT COUNT(Avatar_IP) AS Count
              FROM tn_file_hit
              WHERE File_Name = ?`,
            [fileName],
          )
        )[0]?.Count ?? 0,
      );
    });
  }

  async getLatestAvatars() {
    return this.getDB(async (db) => {
      return Promise.all(
        ["Date", "Last_Date"].map(async (date) => {
          return (
            await db.query(
              `SELECT Avatar_ID, Avatar_Name, ${date} AS Date
                FROM tn_avatar
                ORDER BY Date DESC
                LIMIT 5`,
            )
          ).map(
            ({
              Avatar_ID,
              Avatar_Name,
              Date,
            }: {
              Avatar_ID: string;
              Avatar_Name: string;
              Date: string;
            }) => ({
              avatarID: Avatar_ID,
              avatarName: Avatar_Name,
              date: Date,
            }),
          );
        }),
      );
    });
  }

  async postAutoEssay(
    forumID: string,
    avatarID: string,
    title: string,
    text: string,
    datetime: string,
  ) {
    return this.getDB(async (db) => {
      const { insertId } = await db.query(
        `INSERT INTO tn_auto_essay(Forum_ID, Avatar_ID, Title, Text, Date)
          VALUES(?, ?, ?, ?, ?)`,
        [forumID, avatarID, title, text, datetime],
      );
      return insertId;
    });
  }

  async getAutoEssays(forumID: string, avatarID: string) {
    return this.getDB(async (db) => {
      const data = await db.query(
        `SELECT Auto_Essay_ID, Title, Text, Date
            FROM tn_auto_essay
            WHERE Forum_ID = ? AND Avatar_ID = ?`,
        [forumID, avatarID],
      );

      return data.map(
        ({
          Auto_Essay_ID,
          Title,
          Text,
          Date,
        }: {
          Auto_Essay_ID: number;
          Title: string;
          Text: string;
          Date: string;
        }) => ({
          autoEssayID: Auto_Essay_ID,
          title: Title,
          text: Text,
          date: Date,
        }),
      );
    });
  }

  async doModifyAutoEssay(
    autoEssayID: number,
    avatarID: string,
    title: string,
    text: string,
    datetime: string,
  ) {
    return this.getDB(async (db) => {
      const { affectedRows } = await db.query(
        `UPDATE tn_auto_essay
          SET Title = ?, Text = ?, Date = ?
          WHERE Auto_Essay_ID = ? AND Avatar_ID = ?`,
        [title, text, datetime, autoEssayID, avatarID],
      );
      return affectedRows > 0;
    });
  }

  async wipeAutoEssay(autoEssayID: number, avatarID: string) {
    return this.getDB(async (db) => {
      const { affectedRows } = await db.query(
        `DELETE
          FROM tn_auto_essay
          WHERE Auto_Essay_ID = ? AND Avatar_ID = ?`,
        [autoEssayID, avatarID],
      );
      return affectedRows > 0;
    });
  }
}

export default new DB();
