hostbbq-monitoring/server/src/ctrl/mariadb-database.class.ts

import moment from 'moment';
import { MysqlError, Pool } from 'mysql';

import defaults from '../../../common/defaults.module';
import { ServiceConfig, validateParamType } from '../../../common/interfaces/service-config.interface';
import { HttpCheckData, HttpCheckStatus, ServiceCheckData, ServiceCheckDataEntry } from '../../../common/lib/http-check-data.module';
import { Logger } from '../../../common/util/logger.class';

import { DatabaseException } from '../lib/database.exception';
import { ServiceChangedStatus } from '../lib/service-changed-status.enum';
import { ValidationException } from '../lib/validation-exception.class';
import { DataProvider } from './data-provider.interface';
import { HealthCheckDataProvider } from './health-check-data-provider.interface';
// TODO: import { DBMigration } from './db-migration.class';
import { MariaDBConnector } from './mariadb-connector.class';

export class MariaDBDatabase implements DataProvider, HealthCheckDataProvider {
  private db: MariaDBConnector;

  constructor(pool: Pool) {
    this.db = new MariaDBConnector(pool);
  }

  public async open(migrate = false) {
    try {
      await this.db.connect();

      Logger.debug('Opened MariaDB Connection');

      if (migrate) {
        //TODO: RUN DB MIGRATIONS
        // const mig = new DBMigration(this.db);
        // await mig.update();
      }
    } catch (err) {
      Logger.error('[FATAL] Initializing Database failed:', err);
      Logger.error('[EXITING]');
      process.exit(1);
    }
  }

  public async getAllServerConfigs(): Promise<Server[]> {
    const res = await this.db.query(
      `SELECT 
        \`Server\`.*,
        \`ServerConfig\`.\`Key\`,
        \`ServerConfig\`.\`Value\`
        FROM \`Server\`
        LEFT OUTER JOIN \`ServerConfig\` ON \`Server\`.\`ID\` = \`ServerConfig\`.\`ServerID\`
        ORDER BY \`Server\`.\`Title\`, \`ServerConfig\`.\`Key\``,
      []
    );

    return res.reduce((res: Server[], line: any, i) => {
      const serverID = line['ID'];
      let server: Server;
      if (i === 0 || res[res.length - 1].id !== serverID) {
        server = { id: serverID, title: line['Title'], fqdn: line['FQDN'], config: {} };
        res.push(server);
      } else {
        server = res[res.length - 1];
      }

      if (!!line['Key']) {
        server.config[line['Key']] = line['Value'];
      }

      return res;
    }, [] as Server[]);
  }

  public async insertServerData(serverID: number, data: ReducedData[]) {
    if (!data.length) return;

    await this.db.beginTransaction();
    try {
      for (const entry of data) {
        let entryID = 0;
        try {
          const result = await this.db.query(
            `INSERT INTO \`ServerDataEntry\`(\`ServerID\`, \`Timestamp\`) VALUES(?, ?);
            SELECT LAST_INSERT_ID() as 'ID';`,
            [serverID, entry.time]
          );

          if (!result || result.length < 2) throw new DatabaseException('Unexpected result during insertServerData');

          entryID = (result[1] as any[])[0]['ID'];
        } catch (err: any) {
          if (err.code === 'ER_DUP_ENTRY' && (err as MysqlError).sqlMessage?.includes('UQ_ServerDataEntry_1')) {
            Logger.warn(`[server:${serverID}] Skipping`, err.sqlMessage);
            continue;
          }
        }

        for (const type of Object.keys(entry).filter(t => !['time', 'hdd'].includes(t))) {
          for (const key of Object.keys((entry as any)[type])) {
            await this.db.query('INSERT INTO `ServerDataValue`(`EntryID`, `Type`, `Key`, `Value`) VALUES(?, ?, ?, ?);', [
              entryID,
              type,
              key,
              (entry as any)[type][key]
            ]);
          }
        }

        if (entry.hdd) {
          for (const mount of Object.keys(entry.hdd)) {
            for (const key of Object.keys(entry.hdd[mount])) {
              await this.db.query('INSERT INTO `ServerDataValue`(`EntryID`, `Type`, `Key`, `Value`) VALUES(?, ?, ?, ?);', [
                entryID,
                `hdd:${mount}`,
                key,
                (entry.hdd[mount] as any)[key]
              ]);
            }
          }
        }
      }
      await this.db.commit();
    } catch (err) {
      await this.db.rollback();
      throw err;
    }
  }

  public async getServerDataTypes(serverID: number) {
    const results: any[] = await this.db.query(
      `
      SELECT
        \`ServerDataValue\`.\`Type\` 
      FROM \`ServerDataEntry\`
      JOIN \`ServerDataValue\` ON \`ServerDataEntry\`.\`ID\` = \`ServerDataValue\`.\`EntryID\`
      WHERE \`ServerDataEntry\`.\`ServerID\` = ?
      GROUP BY \`ServerDataValue\`.\`Type\`
      ORDER BY \`ServerDataValue\`.\`Type\`;
    `,
      [serverID]
    );

    return results.reduce((res: Array<ServerDataTypesConfig>, { Type: type }) => {
      if (!type.startsWith('hdd:')) {
        res.push({ type });
      } else {
        let hdd = res.find(c => c.type === 'hdd');
        if (!hdd) {
          hdd = { type: 'hdd', subtypes: [] };
          res.push(hdd);
        }
        hdd.subtypes?.push({ type: type.substring(4) });
      }
      return res;
    }, []) as Array<ServerDataTypesConfig>;
  }

  public async queryServerData(serverID: number, type: ServerDataType, from: Date, to: Date): Promise<ServerData[]> {
    const diffMs = moment(to).diff(moment(from));
    const sectionMs = Math.floor(diffMs / 100);
    const select_max = type !== 'cpu';
    const select_types = select_max ? [type, type, type] : [type, type];
    const result: any[] = await this.db.query(
      `
      SELECT 
        CEIL(UNIX_TIMESTAMP(\`Timestamp\`) * 1000 / ?) * ? as 'Timegroup',
        AVG(\`VALUE_AVG\`.\`Value\`) as 'avg',
        MAX(\`VALUE_PEAK\`.\`Value\`) as 'peak'${
          select_max
            ? `,
        MAX(\`VALUE_MAX\`.\`Value\`) as 'max'`
            : ''
        }
      FROM \`ServerDataEntry\`
      JOIN \`ServerDataValue\` AS \`VALUE_AVG\` ON \`ServerDataEntry\`.\`ID\` = \`VALUE_AVG\`.\`EntryID\` AND \`VALUE_AVG\`.\`Type\` = ? AND \`VALUE_AVG\`.\`Key\` = 'avg'
      JOIN \`ServerDataValue\` AS \`VALUE_PEAK\` ON \`ServerDataEntry\`.\`ID\` = \`VALUE_PEAK\`.\`EntryID\` AND \`VALUE_PEAK\`.\`Type\` = ? AND \`VALUE_PEAK\`.\`Key\` = 'peak'
      ${
        select_max
          ? "JOIN `ServerDataValue` AS `VALUE_MAX` ON `ServerDataEntry`.`ID` = `VALUE_MAX`.`EntryID` AND `VALUE_MAX`.`Type` = ? AND `VALUE_MAX`.`Key` = 'max'"
          : ''
      }
      WHERE \`ServerDataEntry\`.\`ServerID\` = ?
      AND \`ServerDataEntry\`.\`Timestamp\` BETWEEN ? AND ?
      GROUP BY \`Timegroup\`
      ORDER BY \`Timegroup\`;
    `,
      [sectionMs, sectionMs, ...select_types, serverID, from, to]
    );

    return result.map(r => ({ time: new Date(r.Timegroup), avg: r.avg, peak: r.peak, max: r.max }));
  }

  public async reduceServerData(intervalSecs: number, to: Date, from?: Date) {
    const select_daterange = from ? [from, to] : [to];
    const result: any[] = await this.db.query(
      `
      SELECT
        MIN(\`ServerDataEntry\`.\`ID\`) AS 'minEntryID',
        MAX(\`ServerDataEntry\`.\`ID\`) AS 'maxEntryID',
        \`ServerDataEntry\`.\`ServerID\` AS 'serverID',
        FROM_UNIXTIME(CEIL(UNIX_TIMESTAMP(\`ServerDataEntry\`.\`Timestamp\`) / ?) * ?) AS 'timegroup',
        \`VALUE_AVG\`.\`Type\` AS 'type',
        MAX(\`VALUE_AVG\`.\`ID\`) AS 'maxAvgID',
        AVG(\`VALUE_AVG\`.\`Value\`) AS 'avg',
        MAX(\`VALUE_PEAK\`.\`ID\`) AS 'maxPeakID',
        MAX(\`VALUE_PEAK\`.\`Value\`) AS 'peak',
        MAX(\`VALUE_MAX\`.\`ID\`) AS 'maxMaxID',
        MAX(\`VALUE_MAX\`.\`Value\`) AS 'max'
      FROM \`ServerDataEntry\`
      LEFT OUTER JOIN \`ServerDataValue\` AS \`VALUE_AVG\` ON \`ServerDataEntry\`.\`ID\` = \`VALUE_AVG\`.\`EntryID\` AND \`VALUE_AVG\`.\`Key\` = 'avg'
      LEFT OUTER JOIN \`ServerDataValue\` AS \`VALUE_PEAK\` ON \`ServerDataEntry\`.\`ID\` = \`VALUE_PEAK\`.\`EntryID\` AND \`VALUE_AVG\`.\`Type\` = \`VALUE_PEAK\`.\`Type\` AND \`VALUE_PEAK\`.\`Key\` = 'peak'
      LEFT OUTER JOIN \`ServerDataValue\` AS \`VALUE_MAX\` ON \`ServerDataEntry\`.\`ID\` = \`VALUE_MAX\`.\`EntryID\` AND \`VALUE_PEAK\`.\`Type\` = \`VALUE_MAX\`.\`Type\` AND \`VALUE_MAX\`.\`Key\` = 'max'
      WHERE ${from ? '`ServerDataEntry`.`Timestamp` BETWEEN ? AND ?' : '`ServerDataEntry`.`Timestamp` < ?'}
      GROUP BY \`serverID\`, \`timegroup\`, \`type\`
      ORDER BY \`serverID\`, \`timegroup\`, \`maxAvgID\`;`,
      [intervalSecs, intervalSecs, ...select_daterange]
    );

    let lastRow: any | undefined = undefined;
    let queries: string[] = [];
    let params: any[] = [];
    const execTransaction = async (row: any) => {
      await this.db.beginTransaction();
      try {
        Logger.debug(
          '[MARIADB] Reducing Server Data:',
          JSON.stringify({
            server: row.serverID,
            time: row.timegroup
          })
        );
        await this.db.query(queries.join('\n'), params);
        await this.db.commit();
      } catch (error) {
        Logger.error('[MARIADB] Reducing Server Data failed:', new String(error).toString());
        await this.db.rollback();
      }
    };
    for (const row of result) {
      const { minEntryID, maxEntryID, serverID, timegroup } = row;

      if (lastRow?.maxEntryID !== maxEntryID) {
        if (queries.length) await execTransaction(row);

        queries = [];
        params = [];
      }

      if (!queries.length) {
        // PARENT ENTRIES
        queries.push(
          'DELETE FROM `ServerDataEntry` WHERE `ID` != ? AND `ServerID` = ? AND `Timestamp` = ?;', // Avoid ER_DUP_ENTRY on UQ(ServerID, Timestamp):
          'UPDATE `ServerDataEntry` SET `Timestamp` = ? WHERE ID = ?;'
        );
        params.push(maxEntryID, serverID, timegroup, timegroup, maxEntryID);
        if (maxEntryID - 1 - minEntryID >= 0) {
          queries.push('DELETE FROM `ServerDataEntry` WHERE ServerID = ? AND ID >= ? AND ID <= ?;');
          params.push(serverID, minEntryID, maxEntryID - 1);
        }
      }
      lastRow = row;
    }

    if (queries.length) {
      await execTransaction(lastRow);
    }
  }

  public async queryServerStats(serverID: number, type: ServerDataType, from: Date, to: Date): Promise<ReducedValuesPerc> {
    const select_max = type !== 'cpu';
    const select_types = select_max ? [type, type, type] : [type, type];
    const result: any[] = await this.db.query(
      `
      SELECT 
        AVG(\`VALUE_AVG\`.\`Value\`) as 'avg',
        AVG(\`VALUE_PEAK\`.\`Value\`) as 'peak'${
          select_max
            ? `,
        MAX(\`VALUE_MAX\`.\`Value\`) as 'max'`
            : ''
        }
      FROM \`ServerDataEntry\`
      JOIN \`ServerDataValue\` AS \`VALUE_AVG\` ON \`ServerDataEntry\`.\`ID\` = \`VALUE_AVG\`.\`EntryID\` AND \`VALUE_AVG\`.\`Type\` = ? AND \`VALUE_AVG\`.\`Key\` = 'avg'
      JOIN \`ServerDataValue\` AS \`VALUE_PEAK\` ON \`ServerDataEntry\`.\`ID\` = \`VALUE_PEAK\`.\`EntryID\` AND \`VALUE_PEAK\`.\`Type\` = ? AND \`VALUE_PEAK\`.\`Key\` = 'peak'
      ${
        select_max
          ? "JOIN `ServerDataValue` AS `VALUE_MAX` ON `ServerDataEntry`.`ID` = `VALUE_MAX`.`EntryID` AND `VALUE_MAX`.`Type` = ? AND `VALUE_MAX`.`Key` = 'max'"
          : ''
      }
      WHERE \`ServerDataEntry\`.\`ServerID\` = ?
      AND \`ServerDataEntry\`.\`Timestamp\` BETWEEN ? AND ?;
    `,
      [...select_types, serverID, from, to]
    );

    const row = result[0];
    if (Object.keys(row).includes('max')) {
      return {
        avg: ((row['avg'] as number) / (row['max'] as number)) * 100,
        peak: ((row['peak'] as number) / (row['max'] as number)) * 100
      };
    } else {
      return {
        avg: row['avg'] as number,
        peak: row['peak'] as number
      };
    }
  }

  private async getHealthCheckConfigs(serverID?: number, type = 'http') {
    const res: any[] = await this.db.query(
      `SELECT 
        \`HealthCheckConfig\`.*,
        \`HealthCheckParams\`.\`Type\` as '_ParamType',
        \`HealthCheckParams\`.\`Key\` as '_ParamKey',
        \`HealthCheckParams\`.\`Value\` as '_ParamValue'
        FROM \`HealthCheckConfig\`
        LEFT OUTER JOIN \`HealthCheckParams\` ON \`HealthCheckConfig\`.\`ID\` = \`HealthCheckParams\`.\`ConfigID\`
        WHERE \`HealthCheckConfig\`.\`Type\` = ?
        ${!!serverID ? 'AND `HealthCheckConfig`.`ServerID` = ?' : ''}
        ORDER BY \`HealthCheckConfig\`.\`Title\`, _ParamType, _ParamKey`,
      [type, serverID]
    );

    return this.configFromResultRows(res);
  }

  public async getHttpCheckConfigs(serverID?: number) {
    return (await this.getHealthCheckConfigs(serverID)).map(this.httpCheckConfigFrom);
  }

  private async getHealthCheckConfigByID(serverID: number, configID: number) {
    if (!serverID && !configID) return null;

    const res: any[] = await this.db.query(
      `SELECT 
        \`HealthCheckConfig\`.*,
        \`HealthCheckParams\`.\`Type\` as '_ParamType',
        \`HealthCheckParams\`.\`Key\` as '_ParamKey',
        \`HealthCheckParams\`.\`Value\` as '_ParamValue'
        FROM \`HealthCheckConfig\`
        LEFT OUTER JOIN \`HealthCheckParams\` ON \`HealthCheckConfig\`.\`ID\` = \`HealthCheckParams\`.\`ConfigID\`
        WHERE \`HealthCheckConfig\`.\`ID\` = ?
        AND \`HealthCheckConfig\`.\`ServerID\` = ?
        ORDER BY \`HealthCheckConfig\`.\`Title\`, _ParamType, _ParamKey`,
      [configID, serverID]
    );

    if (!res.length) return null;

    const configs = this.configFromResultRows(res);

    return configs[0];
  }

  public async getHttpCheckConfigByID(serverID: number, configID: number) {
    return this.httpCheckConfigFrom(await this.getHealthCheckConfigByID(serverID, configID));
  }

  public async saveHttpCheckConfig(serverID: number, conf: HttpCheckConfig) {
    const validationErrors = this.validateHttpCheckConfig(conf);
    if (validationErrors) throw new ValidationException('Validation of HttpCheckConfig object failed', validationErrors);

    conf.serverId = serverID;
    let status = ServiceChangedStatus.None;

    const oldConf = await this.getHttpCheckConfigByID(serverID, conf.id);
    await this.db.beginTransaction();
    try {
      if (oldConf) {
        // UPDATE
        Logger.debug('Updating HealthCheckConfig', conf.title, `(${oldConf.id})`);
        if (oldConf.title !== conf.title) {
          await this.db.query('UPDATE `HealthCheckConfig` SET `Title` = ? WHERE `ID` = ?', [conf.title, oldConf.id]);
        }

        let updValues: any[][] = [];
        if (oldConf.url !== conf.url) updValues.push([conf.url, conf.id, 'url']);
        if (oldConf.interval !== conf.interval) {
          updValues.push([conf.interval, conf.id, 'interval']);
          status = ServiceChangedStatus.Rescheduled;
        }
        if (oldConf.timeout !== conf.timeout) updValues.push([conf.timeout ?? defaults.serviceChecks.httpTimeout, conf.id, 'timeout']);
        if (oldConf.active !== conf.active) {
          updValues.push([conf.active ?? defaults.serviceChecks.active ? 1 : 0, conf.id, 'active']);
          status = conf.active ?? defaults.serviceChecks.active ? ServiceChangedStatus.Activated : ServiceChangedStatus.Deactivated;
        }
        if (oldConf.notify !== conf.notify) updValues.push([conf.notify ?? defaults.serviceChecks.notify ? 1 : 0, conf.id, 'notify']);
        if (oldConf.notifyThreshold !== conf.notifyThreshold)
          updValues.push([conf.notifyThreshold ?? defaults.serviceChecks.notifyThreshold, conf.id, 'notifyThreshold']);
        if (updValues.length) {
          for (const data of updValues) {
            await this.db.query('UPDATE `HealthCheckParams` SET `Value` = ? WHERE `ConfigID` = ? AND `Key` = ?;', data);
          }
        }

        const res = await this.db.query('SELECT * FROM `HealthCheckParams` WHERE `ConfigID` = ? and `Key` = "check";', [conf.id]);
        updValues = [];
        const delIDs: number[] = [];
        res.forEach((row: any, i) => {
          if (i < conf.checks.length) {
            updValues.push([JSON.stringify(conf.checks[i]), row['ID']]);
          } else {
            delIDs.push(row['ID']);
          }
        });

        if (delIDs.length) {
          const delSql = `DELETE FROM \`HealthCheckParams\` WHERE \`ID\` IN (${delIDs.map(() => '?').join(',')});`;
          await this.db.query(delSql, delIDs);
        }

        if (updValues.length) {
          for (const data of updValues) {
            await this.db.query('UPDATE `HealthCheckParams` SET `Value` = ? WHERE `ID` = ?;', data);
          }
        }
        const insValues = conf.checks.filter((c, i) => i > res.length - 1).map(c => [conf.id, 'regexp', 'check', JSON.stringify(c)]);
        if (insValues.length) {
          for (const data of insValues) {
            await this.db.query('INSERT INTO `HealthCheckParams`(`ConfigID`, `Type`, `Key`, `Value`) VALUES(?, ?, ?, ?);', data);
          }
        }
      } else {
        // INSERT
        Logger.debug('Inserting new HealthCheckConfig', conf.title);
        const res = await this.db.query(
          `INSERT INTO \`HealthCheckConfig\`(\`ServerID\`, \`Type\`, \`Title\`) VALUES(?, ?, ?);
          SELECT LAST_INSERT_ID() as ID;`,
          [serverID, 'http', conf.title]
        );

        if (!res || res.length < 2) throw new DatabaseException('Unexpected result during saveHttpCheckConfig');
        conf.id = (res[1] as any[])[0]['ID'];

        if (conf.active ?? defaults.serviceChecks.active) {
          status = ServiceChangedStatus.Created;
        }

        const insCheckValues = conf.checks.map(c => [conf.id, 'regexp', 'check', c]);
        await this.db.query(
          `INSERT INTO \`HealthCheckParams\`(\`ConfigID\`, \`Type\`, \`Key\`, \`Value\`) VALUES
          (?, ?, ?, ?),
          (?, ?, ?, ?),
          (?, ?, ?, ?),
          (?, ?, ?, ?),
          (?, ?, ?, ?),
          (?, ?, ?, ?)${conf.checks.length ? `,${insCheckValues.map(() => '(?, ?, ?, ?)').join(',')}` : ''}`,
          [
            ...[conf.id, 'text', 'url', conf.url],
            ...[conf.id, 'boolean', 'active', conf.active ?? defaults.serviceChecks.active ? 1 : 0],
            ...[conf.id, 'number', 'interval', conf.interval],
            ...[conf.id, 'number', 'timeout', conf.timeout ?? defaults.serviceChecks.httpTimeout],
            ...[conf.id, 'boolean', 'notify', conf.notify ?? defaults.serviceChecks.notify],
            ...[conf.id, 'number', 'notifyThreshold', conf.notifyThreshold ?? defaults.serviceChecks.notifyThreshold],
            ...conf.checks.reduce((ret, check) => [...ret, conf.id, 'regexp', 'check', JSON.stringify(check)], [] as any[])
          ]
        );
      }

      await this.db.commit();
      return { status, result: conf };
    } catch (err) {
      await this.db.rollback();
      throw err;
    }
  }

  async deleteHealthCheckConfig(serverID: number, confID: number) {
    const conf = await this.getHealthCheckConfigByID(serverID, confID);
    if (!conf) return false;

    await this.db.query('DELETE FROM `HealthCheckConfig` WHERE `ID` = ?;', [confID]);
    return true;
  }

  async insertHealthCheckData(confID: number, time: Date, status: HttpCheckStatus, message: string) {
    const res = await this.db.query(
      `INSERT INTO \`HealthCheckDataEntry\`(\`ConfigID\`, \`Timestamp\`, \`Status\`, \`Message\`) VALUES(?, ?, ?, ?);
      SELECT LAST_INSERT_ID() as ID;`,
      [confID, time, status, message]
    );

    if (!res || res.length < 2) throw new DatabaseException('Unexpected result during insertHealthCheckData');
    const id = (res[1] as any[])[0]['ID'];

    return {
      id,
      configId: confID,
      time,
      status,
      message
    } as HttpCheckData;
  }

  async queryServiceCheckData(serverID: number, confID: number, from: Date, to: Date) {
    const result = await this.db.query(
      `
      SELECT \`DataEntryChanges\`.*
      FROM \`HealthCheckConfig\`
      JOIN (
        SELECT * FROM (
          SELECT 
            *
          FROM \`HealthCheckDataEntry\`
          WHERE \`ConfigID\` = ?
          AND \`Timestamp\` BETWEEN ? AND ?
          ORDER BY \`ID\`
          LIMIT 0, 1
        ) AS \`FIRST_STATE\`

        UNION

        SELECT 
          \`ID\`,
          \`ConfigID\`,
          \`Timestamp\`,
          \`Status\`,
          \`Message\`
        FROM
        (
          SELECT 
            \`HealthCheckDataEntry\`.*, 
            LAG(\`Status\`) OVER (ORDER BY \`ConfigID\`, \`Timestamp\`) AS previous_state, 
            LAG(\`Message\`) OVER (ORDER BY \`ConfigID\`, \`Timestamp\`) AS previous_msg
          FROM \`HealthCheckDataEntry\`
          WHERE \`ConfigID\` = ?
        ) AS HCDE2
        WHERE \`Status\` != previous_state
        AND \`Message\` != previous_msg
        
        UNION
        
        SELECT * FROM (
          SELECT 
            *
          FROM \`HealthCheckDataEntry\`
          WHERE \`ConfigID\` = ?
          AND \`Timestamp\` BETWEEN ? AND ?
          ORDER BY ID DESC
          LIMIT 0, 1
        ) AS \`LAST_STATE\`
        ORDER BY \`ConfigID\`, \`Timestamp\`
      ) AS \`DataEntryChanges\` ON \`DataEntryChanges\`.\`ConfigID\` = \`HealthCheckConfig\`.\`ID\`
      WHERE \`HealthCheckConfig\`.\`ServerID\` = ?
        AND \`DataEntryChanges\`.\`Timestamp\` BETWEEN ? AND ?
      ORDER BY \`Timestamp\`, \`ID\`;`,
      [confID, from, to, confID, confID, from, to, serverID, from, to]
    );

    const mapByTimestamp = this.mapServiceCheckDataByTimestamp(result);

    const arr: ServiceCheckData[] = [];
    for (const entry of mapByTimestamp.entries()) {
      arr.push({
        time: new Date(entry[0]),
        data: entry[1]
      });
    }
    return arr;
  }

  public async queryServiceCheckLogs(serverID: number, confID: number, from: Date, to: Date) {
    const result = await this.db.query(
      `
      SELECT \`HealthCheckDataEntry\`.*
      FROM \`HealthCheckConfig\`
      JOIN \`HealthCheckDataEntry\` ON \`HealthCheckDataEntry\`.\`ConfigID\` = \`HealthCheckConfig\`.\`ID\`
      WHERE \`HealthCheckConfig\`.\`ID\` = ?
        AND \`HealthCheckConfig\`.\`ServerID\` = ?
        AND \`HealthCheckDataEntry\`.\`Timestamp\` BETWEEN ? AND ?
      ORDER BY \`Timestamp\`, \`ConfigID\`;`,
      [confID, serverID, from, to]
    );

    const mapByTimestamp = this.mapServiceCheckDataByTimestamp(result);

    const arr: ServiceCheckData[] = [];
    for (const entry of mapByTimestamp.entries()) {
      arr.push({
        time: new Date(entry[0]),
        data: entry[1]
      });
    }
    return arr;
  }

  public async getLastErrors(confID: number, threshold: number) {
    const result = await this.db.query(
      `SELECT HCDE1.* 
        FROM \`HealthCheckDataEntry\` as HCDE1
        INNER JOIN (
          SELECT \`Timestamp\` 
          FROM \`HealthCheckDataEntry\`
          WHERE \`ConfigID\` = ?
          GROUP BY \`Timestamp\`
          ORDER BY \`Timestamp\` DESC
          LIMIT 0, ?
        ) AS HCDE2 ON HCDE1.\`Timestamp\` = HCDE2.\`Timestamp\`
        WHERE \`ConfigID\` = ?
        ORDER BY HCDE1.\`Timestamp\` DESC, HCDE1.\`ID\` DESC`,
      [confID, threshold, confID]
    );

    const mapByTimestamp = this.mapServiceCheckDataByTimestamp(result);
    const errors: ServiceCheckData[] = [];
    for (const entry of mapByTimestamp.entries()) {
      const time = entry[0];
      const data = entry[1];

      const errorData = data.filter(d => d.status !== HttpCheckStatus.OK);
      if (!errorData.length) break;

      errors.push({
        time: new Date(time),
        data: errorData
      });
    }

    return errors;
  }

  private mapServiceCheckDataByTimestamp(rows: any[]) {
    return rows.reduce((res: Map<number, ServiceCheckDataEntry[]>, row) => {
      const time: number = row['Timestamp'];
      if (!res.has(time)) res.set(time, []);
      res.get(time)?.push({
        status: row['Status'] as number,
        message: row['Message']
      });
      return res;
    }, new Map()) as Map<number, ServiceCheckDataEntry[]>;
  }

  private configFromResultRows(rows: any[]) {
    return rows.reduce((res: ServiceConfig[], line, i) => {
      const configID = line['ID'];
      let config: ServiceConfig;
      if (i === 0 || res[res.length - 1].id !== configID) {
        config = {
          id: configID,
          title: line['Title'],
          type: line['Type'],
          serverId: line['ServerID'],
          params: []
        };
        res.push(config);
      } else {
        config = res[res.length - 1];
      }

      if (!!line['_ParamKey']) {
        const type = validateParamType(line['_ParamType']);
        const key = line['_ParamKey'];
        if (key === 'check') {
          let checkParam = config.params.find(c => c.key === 'check');
          if (!checkParam) {
            config.params.push(
              (checkParam = {
                key: 'check',
                type: 'regexp',
                value: []
              })
            );
          }
          (checkParam.value as string[]).push(line['_ParamValue']);
        } else {
          config.params.push({
            type,
            key,
            value: type === 'number' ? Number(line['_ParamValue']) : type === 'boolean' ? Boolean(Number(line['_ParamValue'])) : line['_ParamValue']
          });
        }
      }

      return res;
    }, [] as ServiceConfig[]);
  }

  private httpCheckConfigFrom(hcConf: ServiceConfig | null): HttpCheckConfig | null {
    if (!hcConf) return null;
    const params = {
      url: hcConf.params?.find(p => p.key === 'url')?.value as string,
      active: (hcConf.params?.find(p => p.key === 'active')?.value as boolean) ?? defaults.serviceChecks.active,
      interval: hcConf.params?.find(p => p.key === 'interval')?.value as number,
      timeout: (hcConf.params?.find(p => p.key === 'timeout')?.value as number) ?? defaults.serviceChecks.httpTimeout,
      notify: (hcConf.params?.find(p => p.key === 'notify')?.value as boolean) ?? defaults.serviceChecks.notify,
      notifyThreshold: (hcConf.params?.find(p => p.key === 'notifyThreshold')?.value as number) ?? defaults.serviceChecks.notifyThreshold,
      checks: hcConf.params?.reduce(
        (res, p) => (p.key === 'check' && Array.isArray(p.value) ? [...res, ...p.value.map(c => JSON.parse(c))] : res),
        [] as string[]
      )
    };
    return {
      id: hcConf.id,
      title: hcConf.title,
      type: hcConf.type,
      serverId: hcConf.serverId,
      ...params
    };
  }

  private validateHttpCheckConfig(conf: Partial<HttpCheckConfig>): { [key: string]: string } | null {
    const errors = {} as any;
    if (!conf) return { null: 'Object was null or undefined' };
    if (!conf.title?.trim().length) errors['required|title'] = `Field 'title' is required.`;
    if (!conf.url?.trim().length) errors['required|url'] = `Field 'url' is required.`;
    if ((!conf.interval && conf.interval !== 0) || Number.isNaN(Number(conf.interval))) errors['required|interval'] = `Field 'interval' is required.`;

    if (!conf.checks || !Array.isArray(conf.checks))
      errors['required|checks'] = `Field 'checks' is required and must be an array of check expressions.`;

    return Object.keys(errors).length ? errors : null;
  }

  public cleanupData() {
    process.nextTick(async () => {
      await this.cleanupHealthCheckData();
      await this.cleanupServerData();
    });
  }

  private async cleanupHealthCheckData() {
    const nowMinus3M = moment().subtract(3, 'months').toDate();
    await this.db.query('DELETE FROM `HealthCheckDataEntry` WHERE Timestamp < ?;', [nowMinus3M]);
  }

  private async cleanupServerData() {
    const nowMinus3M = moment().subtract(3, 'months').toDate();
    const nowMinus1M = moment().subtract(1, 'month').toDate();
    const nowMinus2W = moment().subtract(2, 'weeks').toDate();

    // Reduce Data older than 3Mo to reduced data points like if requested in a 3Mo UI chart
    let intervalSeconds = Math.floor(((365.2422 / 12) * 3 * 24 * 60 * 60) / 100);
    await this.reduceServerData(intervalSeconds, nowMinus3M);

    // Reduce Data older than 1Mo to reduced data points like if requested in a 1Mo UI chart
    intervalSeconds = Math.floor(((365.2422 / 12) * 1 * 24 * 60 * 60) / 100);
    await this.reduceServerData(intervalSeconds, nowMinus1M, nowMinus3M);

    // Reduce Data older than 2We to reduced data points like if requested in a 2We UI chart
    intervalSeconds = Math.floor((14 * 24 * 60 * 60) / 100);
    await this.reduceServerData(intervalSeconds, nowMinus2W, nowMinus1M);
  }

  public async close(): Promise<void> {
    await this.db.close();
  }
}