import Logger from "@/app/www/system/Logger";
import { ESSAY_ENTRY_PATH } from "@/app/www/utilities/Path";
import { equalCipher, isSU } from "@/app/www/utilities/Utility";
import { readdir, unlink } from "fs/promises";
import {
  Connection,
  createConnection,
  createPool,
  Pool,
  SqlError,
} from "mariadb";
import { join } from "path";

let pool: Pool;
if (process.env.NODE_ENV === "production") {
  pool = createPool(process.env.DB as string);
}
Logger.info("Loaded DB");

await ta(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_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`);
});

for (const fileName of await readdir(ESSAY_ENTRY_PATH)) {
  if (!(await isValidFile(fileName))) {
    await unlink(join(ESSAY_ENTRY_PATH, fileName));
    Logger.info(`Wiped ${fileName}`);
  }
}

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

export async function ta<T>(onHandle: (db: Connection) => Promise<T>) {
  return getDB<T>(async (db) => {
    try {
      await db.beginTransaction();
      const handled = onHandle(db);
      await db.commit();
      return handled;
    } catch (e) {
      await db.rollback();
      throw e;
    }
  });
}

export async function getDB<T>(onGetDB: (db: Connection) => Promise<T>) {
  let db;
  if (pool) {
    try {
      db = await pool.getConnection();
      return onGetDB(db);
    } finally {
      await db?.release();
    }
  } else {
    try {
      db = await createConnection(process.env.DB as string);
      return await onGetDB(db);
    } finally {
      await db?.end();
    }
  }
}

export async function getAvatar(avatarID: string) {
  return getDB(async (db) => {
    const data = (
      await db.query<
        {
          Avatar_ID: string;
          Avatar_Cipher: string;
          Avatar_Name: string;
          Level: number;
          Fax: string;
          Avatar_Intro: string;
        }[]
      >(
        `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"],
      };
    }
  });
}

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

export async function doModifyAvatar(
  avatarID: string,
  avatarCipher: string,
  avatarCipherModified: string,
  avatarName: string,
  fax: string,
  avatarIntro: string,
) {
  return ta(async (db) => {
    const defaultAvatarCipher = (
      await db.query<{ Avatar_Cipher: string }[]>(
        `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
    );
  });
}

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

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

export async function getForum(db: Connection, forumID: string) {
  const data = (
    await db.query<
      {
        Title: string;
        Title_1042: string;
        Level: number;
      }[]
    >(
      `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"],
    };
  }
}

export async function getForums(forumGroup?: string) {
  return getDB(async (db) => {
    return (
      forumGroup
        ? await db.query<
            {
              Forum_ID: string;
              Title: string;
              Title_1042: string;
            }[]
          >(
            `SELECT Forum_ID, Title, Title_1042
                FROM tn_forum
                WHERE Forum_Group = ?`,
            [forumGroup],
          )
        : await db.query<
            {
              Forum_ID: string;
              Title: string;
              Title_1042: string;
            }[]
          >(
            `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"],
    }));
  });
}

export async function getMostEssays(forumID: string) {
  return getEssays(forumID, 1, 5);
}

export async function getBeforeEssay(forumID: string, datetime: string) {
  return getDB(async (db) => {
    const dataBefore = (
      await db.query<
        {
          Essay_ID: string;
          Avatar_ID: string;
          Avatar_Name: string;
          Date: string;
          Title: string;
        }[]
      >(
        `SELECT Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title
            FROM tn_essay
            INNER JOIN tn_avatar USING(Avatar_ID)
            WHERE Forum_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 getCommentCount(essayIDBefore)),
        hitCount: Number(await getHitCount(essayIDBefore)),
      };
    }
  });
}

export async function getLaterEssay(forumID: string, datetime: string) {
  return getDB(async (db) => {
    const dataLater = (
      await db.query<
        {
          Essay_ID: string;
          Avatar_ID: string;
          Avatar_Name: string;
          Date: string;
          Title: string;
        }[]
      >(
        `SELECT Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title
            FROM tn_essay
            INNER JOIN tn_avatar USING(Avatar_ID)
            WHERE Forum_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 getCommentCount(essayIDLater)),
        hitCount: Number(await getHitCount(essayIDLater)),
      };
    }
  });
}

export async function getLatestEssays() {
  return getDB(async (db) => {
    const data = await db.query<
      {
        Forum_ID: string;
        Essay_ID: number;
        Avatar_ID: string;
        Avatar_Name: string;
        Date: string;
        Title: string;
        Text: string;
      }[]
    >(
      `SELECT Forum_ID, Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title, tn_essay.Text
          FROM tn_essay
          INNER JOIN tn_avatar USING(Avatar_ID)
          ORDER BY tn_essay.Date DESC
          LIMIT 5`,
    );
    return Promise.all(
      data.map(async (data) => {
        const forumID = data["Forum_ID"];
        const forumTitles = await getForumTitles(forumID);
        if (!forumTitles) {
          throw new Error(forumID);
        }

        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 getCommentCount(essayID)),
          hitCount: Number(await getHitCount(essayID)),
        };
      }),
    );
  });
}

export async function getCommentCount(essayID: number) {
  return 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"];
    }
  });
}

export async function getHitCount(essayID: number) {
  return 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"];
    }
  });
}

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

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

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

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

export async function doModifyEssay(
  db: Connection,
  essayID: number,
  avatarID: string,
  title: string,
  text: string,
  level: number,
) {
  const { affectedRows } = isSU(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;
}

export async function wipeEssay(
  essayID: number,
  avatarID: string,
  level: number,
) {
  return getDB(async (db) => {
    const { affectedRows } = isSU(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;
  });
}

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

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

export async function getEssay(essayID: number) {
  return getDB(async (db) => {
    const data = (
      await db.query<
        {
          Forum_ID: string;
          Essay_ID: number;
          Avatar_ID: string;
          Avatar_Name: string;
          Date: string;
          Title: string;
          Text: string;
        }[]
      >(
        `SELECT Forum_ID, Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title, Text
            FROM tn_essay
            INNER JOIN tn_avatar USING(Avatar_ID)
            WHERE Essay_ID = ?`,
        [essayID],
      )
    )[0];

    if (data) {
      const forumID = data["Forum_ID"];
      const forumTitles = await getForumTitles(forumID);
      if (forumTitles) {
        const essayID = 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 getHitCount(essayID)),
          essayBefore: await getBeforeEssay(forumID, date),
          essayLater: await getLaterEssay(forumID, date),
        };
      }
    }
  });
}

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

export async function getEssays(
  forumID: string,
  page: number,
  viewUnit: number,
) {
  return getDB(async (db) => {
    const data = await db.query<
      {
        Essay_ID: number;
        Avatar_ID: string;
        Avatar_Name: string;
        Date: string;
        Title: string;
      }[]
    >(
      `SELECT Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title
          FROM tn_essay
          INNER JOIN tn_avatar USING(Avatar_ID)
          WHERE Forum_ID = ?
          ORDER BY tn_essay.Date DESC
          LIMIT ?, ?`,
      [forumID, (page - 1) * viewUnit, viewUnit],
    );
    return Promise.all(
      data.map(async (data) => {
        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 getCommentCount(essayID)),
          hitCount: Number(await getHitCount(essayID)),
        };
      }),
    );
  });
}

export async function wantEssay(
  wantInput: string,
  page: number,
  viewUnit: number,
) {
  return getDB(async (db) => {
    return Promise.all(
      (
        await db.query<
          {
            Forum_ID: string;
            Essay_ID: number;
            Avatar_ID: string;
            Avatar_Name: string;
            Date: string;
            Title: string;
          }[]
        >(
          `SELECT Forum_ID, Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title
              FROM tn_essay
              INNER JOIN tn_avatar USING(Avatar_ID)
              WHERE MATCH(Text) AGAINST(? IN BOOLEAN MODE)
              ORDER BY tn_essay.Date DESC
              LIMIT ?, ?`,
          [`${wantInput}*`, (page - 1) * viewUnit, viewUnit],
        )
      ).map(async (data) => {
        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 getCommentCount(essayID)),
          hitCount: Number(await getHitCount(essayID)),
        };
      }),
    );
  });
}

export async function wantComment(
  wantInput: string,
  page: number,
  viewUnit: number,
) {
  return getDB(async (db) =>
    (
      await db.query<
        {
          Forum_ID: string;
          Essay_ID: number;
          Avatar_ID: string;
          Avatar_Name: string;
          Date: string;
          Text: string;
        }[]
      >(
        `SELECT Forum_ID, tn_comment.Essay_ID, tn_comment.Avatar_ID, Avatar_Name, tn_comment.Date, tn_comment.Text
          FROM tn_comment
          INNER JOIN tn_essay USING(Essay_ID)
          INNER JOIN tn_avatar USING(Avatar_ID)
          WHERE MATCH(tn_comment.Text) AGAINST(? IN BOOLEAN MODE)
          ORDER BY tn_comment.Date DESC
          LIMIT ?, ?`,
        [`${wantInput}*`, (page - 1) * viewUnit, viewUnit],
      )
    ).map((data) => ({
      forumID: data["Forum_ID"],
      essayID: Number(data["Essay_ID"]),
      avatarID: data["Avatar_ID"],
      avatarName: data["Avatar_Name"],
      date: data["Date"],
      text: data["Text"],
    })),
  );
}

export async function postEssayHit(
  essayID: number,
  avatarIP: string,
  date: string,
) {
  return 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;
      }
    }
  });
}

``;

export async function postComment(
  targetCommentID: number,
  essayID: number,
  avatarID: string,
  datetime: string,
  text: string,
) {
  return 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;
  });
}

export async function doModifyComment(
  commentID: number,
  avatarID: string,
  text: string,
  level: number,
) {
  return getDB(async (db) => {
    const { affectedRows } = isSU(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;
  });
}

export async function wipeComment(
  commentID: number,
  avatarID: string,
  level: number,
) {
  return getDB(async (db) => {
    const { affectedRows } = isSU(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;
  });
}

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

export async function getLatestComments() {
  return getDB(async (db) => {
    const data = await db.query<
      {
        Comment_ID: number;
        Forum_ID: string;
        Essay_ID: number;
        Avatar_ID: string;
        Avatar_Name: string;
        Date: string;
        Text: string;
      }[]
    >(
      `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
          INNER JOIN tn_essay USING(Essay_ID)
          INNER JOIN tn_avatar ON tn_comment.Avatar_ID = tn_avatar.Avatar_ID
          ORDER BY tn_comment.Date DESC
          LIMIT 5`,
    );
    return data.map((data) => ({
      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"],
    }));
  });
}

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

export async function postCommentary(
  avatarName: string,
  avatarCipher: string,
  avatarIP: string,
  datetime: string,
  text: string,
) {
  return 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;
  });
}

export async function doModifyCommentary(
  commentaryID: number,
  avatarCipher: string,
  text: string,
) {
  return 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;
  });
}

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

export async function getHit(date: string, dateBefore: string) {
  return getDB(async (db) => {
    const data = 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(
        data.find(({ Date }: { Date: string }) => Date === dateBefore)?.[
          "Count"
        ] ?? 0,
      ),
      Number(
        data.find(({ Date }: { Date: string }) => Date === date)?.["Count"] ??
          0,
      ),
    ];
  });
}

export async function postHit(avatarIP: string, date: string) {
  return 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;
    }
  });
}

export async function postFileHit(
  fileName: string,
  avatarIP: string,
  date: string,
) {
  return 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;
      }
    }
  });
}

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

export async function getFileHit(fileName: string) {
  return 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,
    );
  });
}

export async function getLatestLoggedInAvatars() {
  return getDB(async (db) => {
    return (
      await db.query<
        { Avatar_ID: string; Avatar_Name: string; Last_Date: string }[]
      >(
        `SELECT Avatar_ID, Avatar_Name, Last_Date
                FROM tn_avatar
                ORDER BY Last_Date DESC
                LIMIT 5`,
      )
    ).map(({ Avatar_ID, Avatar_Name, Last_Date }) => ({
      avatarID: Avatar_ID,
      avatarName: Avatar_Name,
      date: Last_Date,
    }));
  });
}

export async function getLatestEnrolledAvatars() {
  return getDB(async (db) => {
    return (
      await db.query<
        { Avatar_ID: string; Avatar_Name: string; Date: string }[]
      >(
        `SELECT Avatar_ID, Avatar_Name, Date
                FROM tn_avatar
                ORDER BY Date DESC
                LIMIT 5`,
      )
    ).map(({ Avatar_ID, Avatar_Name, Date }) => ({
      avatarID: Avatar_ID,
      avatarName: Avatar_Name,
      date: Date,
    }));
  });
}

export async function postAutoEssay(
  forumID: string,
  avatarID: string,
  title: string,
  text: string,
  datetime: string,
) {
  return 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 Number(insertId);
  });
}

export async function getAutoEssays(forumID: string, avatarID: string) {
  return getDB(async (db) => {
    return (
      await db.query<
        {
          Auto_Essay_ID: number;
          Title: string;
          Text: string;
          Date: string;
        }[]
      >(
        `SELECT Auto_Essay_ID, Title, Text, Date
            FROM tn_auto_essay
            WHERE Avatar_ID = ? AND Forum_ID = ?`,
        [avatarID, forumID],
      )
    ).map(({ Auto_Essay_ID, Title, Text, Date }) => ({
      autoEssayID: Auto_Essay_ID,
      title: Title,
      text: Text,
      date: Date,
    }));
  });
}

export async function doModifyAutoEssay(
  autoEssayID: number,
  avatarID: string,
  title: string,
  text: string,
  datetime: string,
) {
  return 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;
  });
}

export async function wipeAutoEssay(autoEssayID: number, avatarID: string) {
  return 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;
  });
}