spritzschema-next/lib/mysqlinterface.ts

import mysql from 'mysql2/promise';

const DB_HOST = process.env.DB_HOST || 'localhost';
const DB_PORT = parseInt(process.env.DB_PORT || '3306');
const DB_USER = process.env.DB_USER || 'root';
const DB_PASS = process.env.DB_PASS || '';
const DB_BASE = 'medizin';

// Singleton pool – überlebt Hot-Reloads im Dev-Modus
declare global {
  // eslint-disable-next-line no-var
  var _mysqlPool: mysql.Pool | undefined;
}

function getPool(): mysql.Pool {
  if (!global._mysqlPool) {
    global._mysqlPool = mysql.createPool({
      host: DB_HOST,
      port: DB_PORT,
      user: DB_USER,
      password: DB_PASS,
      database: DB_BASE,
      waitForConnections: true,
      connectionLimit: 10,
    });
  }
  return global._mysqlPool;
}

async function ensureTable(conn: mysql.PoolConnection, tableName: string) {
  await conn.query(`
    CREATE TABLE IF NOT EXISTS \`${tableName}\` (
      id INT AUTO_INCREMENT PRIMARY KEY,
      curdate VARCHAR(20) NOT NULL UNIQUE,
      months JSON NOT NULL,
      years JSON NOT NULL,
      data JSON NOT NULL,
      einheit INT NOT NULL DEFAULT 0,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )
  `);
}

export interface DataItem {
  day: string;
  status: boolean;
  einheit: number;
}

export interface Schema {
  curdate: string;
  months: string[];
  years: string[];
  data: DataItem[];
  einheit: number;
}

interface Options {
  testing?: string | boolean;
  curdate?: string;
  data?: Schema;
}

interface Result {
  err: null | string;
  data?: Schema | null;
  date?: unknown;
}

export async function doMySQL(cmd: string, options: Options): Promise<Result> {
  const erg: Result = { err: null };
  const tableName = options.testing ? 'spritzschema_test' : 'spritzschema';
  const pool = getPool();
  const conn = await pool.getConnection();
  try {
    await ensureTable(conn, tableName);
    if (cmd === 'getlastdata') {
      return await getLastData(conn, tableName);
    } else if (cmd === 'putdata') {
      return await putData(conn, tableName, options);
    } else if (cmd === 'getdata') {
      return await getData(conn, tableName, options);
    } else if (cmd === 'deldata') {
      return await delData(conn, tableName, options);
    } else {
      erg.err = 'Unknown Call';
    }
  } catch (e) {
    console.error(e);
    erg.err = String(e);
  } finally {
    conn.release();
  }
  return erg;
}

async function getLastData(conn: mysql.PoolConnection, tableName: string): Promise<Result> {
  const erg: Result = { err: null };
  try {
    const [rows] = await conn.query<mysql.RowDataPacket[]>(
      `SELECT curdate, months, years, data, einheit FROM \`${tableName}\` ORDER BY id DESC LIMIT 1`
    );
    if (rows.length > 0) {
      const row = rows[0];
      erg.data = {
        curdate: row.curdate,
        months: typeof row.months === 'string' ? JSON.parse(row.months) : row.months,
        years: typeof row.years === 'string' ? JSON.parse(row.years) : row.years,
        data: typeof row.data === 'string' ? JSON.parse(row.data) : row.data,
        einheit: row.einheit,
      };
    } else {
      erg.data = null;
    }
  } catch (e) {
    console.error(e);
    erg.err = String(e);
  }
  return erg;
}

async function getData(conn: mysql.PoolConnection, tableName: string, options: Options): Promise<Result> {
  const erg: Result = { err: null };
  try {
    const [rows] = await conn.query<mysql.RowDataPacket[]>(
      `SELECT curdate, months, years, data, einheit FROM \`${tableName}\` WHERE curdate = ?`,
      [options.curdate]
    );
    if (rows.length > 0) {
      const row = rows[0];
      erg.data = {
        curdate: row.curdate,
        months: typeof row.months === 'string' ? JSON.parse(row.months) : row.months,
        years: typeof row.years === 'string' ? JSON.parse(row.years) : row.years,
        data: typeof row.data === 'string' ? JSON.parse(row.data) : row.data,
        einheit: row.einheit,
      };
    } else {
      erg.data = null;
    }
  } catch (e) {
    console.error(e);
    erg.err = String(e);
  }
  return erg;
}

async function putData(conn: mysql.PoolConnection, tableName: string, options: Options): Promise<Result> {
  const erg: Result = { err: null, date: null };
  const schema = options.data!;
  try {
    const [result] = await conn.query(
      `INSERT INTO \`${tableName}\` (curdate, months, years, data, einheit)
       VALUES (?, ?, ?, ?, ?)
       ON DUPLICATE KEY UPDATE
         months = VALUES(months),
         years = VALUES(years),
         data = VALUES(data),
         einheit = VALUES(einheit),
         updated_at = CURRENT_TIMESTAMP`,
      [
        schema.curdate,
        JSON.stringify(schema.months),
        JSON.stringify(schema.years),
        JSON.stringify(schema.data),
        schema.einheit,
      ]
    );
    erg.date = result;
  } catch (e) {
    console.error(e);
    erg.err = String(e);
  }
  return erg;
}

async function delData(conn: mysql.PoolConnection, tableName: string, options: Options): Promise<Result> {
  const erg: Result = { err: null };
  try {
    const [result] = await conn.query(
      `DELETE FROM \`${tableName}\` WHERE curdate = ?`,
      [options.curdate]
    );
    erg.data = result as unknown as Schema;
  } catch (e) {
    console.error(e);
    erg.err = String(e);
  }
  return erg;
}