index.ts

import dayjs from 'dayjs';
import utc from 'dayjs/plugin/utc.js';
import { AdminForthResource, IAdminForthSingleFilter, IAdminForthAndOrFilter, IAdminForthDataSourceConnector, AdminForthConfig, IAggregationRule, IGroupByRule, IGroupByDateTrunc, AdminForthBaseConnector } from 'adminforth';
import { AdminForthDataTypes,  AdminForthFilterOperators, AdminForthSortDirections, } from 'adminforth';
import mysql from 'mysql2/promise';
import { dbLogger, afLogger, checkIfFieldIsInsideResourceColumns } from 'adminforth';

dayjs.extend(utc);

class MysqlConnector extends AdminForthBaseConnector implements IAdminForthDataSourceConnector {

  async setupClient(url: string): Promise<void> {
    try {
      this.client = mysql.createPool({
        uri: url,
        waitForConnections: true,
        connectionLimit: 10,  // Adjust based on your needs
        queueLimit: 0,
        timezone: 'Z',
      });
    } catch (e) {
      afLogger.error(`Failed to connect to MySQL: ${e}`);
    }
  }

  OperatorsMap = {
    [AdminForthFilterOperators.EQ]: '=',
    [AdminForthFilterOperators.NE]: '<>',
    [AdminForthFilterOperators.GT]: '>',
    [AdminForthFilterOperators.LT]: '<',
    [AdminForthFilterOperators.GTE]: '>=',
    [AdminForthFilterOperators.LTE]: '<=',
    [AdminForthFilterOperators.LIKE]: 'LIKE',
    [AdminForthFilterOperators.ILIKE]: 'ILIKE',
    [AdminForthFilterOperators.IN]: 'IN',
    [AdminForthFilterOperators.NIN]: 'NOT IN',
    [AdminForthFilterOperators.AND]: 'AND',
    [AdminForthFilterOperators.OR]: 'OR',
    [AdminForthFilterOperators.IS_EMPTY]: 'IS NULL',
    [AdminForthFilterOperators.IS_NOT_EMPTY]: 'IS NOT NULL',
  };

  SortDirectionsMap = {
    [AdminForthSortDirections.asc]: 'ASC',
    [AdminForthSortDirections.desc]: 'DESC',
  };

  async getAllTables(): Promise<Array<string>> {
    const [rows] = await this.client.query(
        `
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE';
        `
    );
    return rows.map((row: any) => row.TABLE_NAME);
  }

  async getAllColumnsInTable(tableName: string): Promise<Array<{ name: string; sampleValue?: any }>> {
    const [columns] = await this.client.query(
      `SELECT column_name FROM information_schema.columns WHERE table_name = ? AND table_schema = DATABASE()`,
      [tableName]
    );
  
    const columnNames = columns.map((c: any) => c.COLUMN_NAME);
    const orderByField = ['updated_at', 'created_at', 'id'].find(f => columnNames.includes(f));
  
    let [rows] = orderByField
      ? await this.client.query(`SELECT * FROM \`${tableName}\` ORDER BY \`${orderByField}\` DESC LIMIT 1`)
      : await this.client.query(`SELECT * FROM \`${tableName}\` LIMIT 1`);
  
    const sampleRow = rows[0] || {};
  
    return columns.map((col: any) => ({
      name: col.COLUMN_NAME,
      sampleValue: sampleRow[col.COLUMN_NAME],
    }));
  }
  

  async isDatabaseEmpty(): Promise<boolean> {
    const [rows] = await this.client.execute(`
      SELECT table_schema, table_name
      FROM information_schema.tables
      WHERE table_schema = DATABASE()
        AND table_type = 'BASE TABLE'
      LIMIT 1
    `);
    return rows.length === 0;
  }

  async hasMySQLCascadeFk(resource: AdminForthResource, config: AdminForthConfig): Promise<boolean> {

    const cascadeColumn = resource.columns.find(c => c.foreignResource?.onDelete === 'cascade');
    if (!cascadeColumn) return false;
    //@ts-ignore
    const parentResource = config.resources.find(r => r.resourceId === cascadeColumn.foreignResource.resourceId);
    if (!parentResource) return false;

    const [rows] = await this.client.execute(
      `
      SELECT 1
      FROM information_schema.REFERENTIAL_CONSTRAINTS
      WHERE CONSTRAINT_SCHEMA = DATABASE()
        AND TABLE_NAME = ?
        AND REFERENCED_TABLE_NAME = ?
        AND DELETE_RULE = 'CASCADE'
      LIMIT 1
      `,
      [resource.table, parentResource.table]
    );

    const hasCascadeOnTable = (rows as any[]).length > 0;

    const isUploadPluginInstalled = resource.plugins?.some(p => p.className === "UploadPlugin");

    if (hasCascadeOnTable && isUploadPluginInstalled) {
      afLogger.warn(`Table "${resource.table}" has ON DELETE CASCADE and UploadPlugin installed, which may conflict with adminForth cascade deletion`);
    }
    return hasCascadeOnTable;
  }

  async discoverFields(resource: AdminForthResource, config: AdminForthConfig) {
    const [results] = await this.client.execute("SHOW COLUMNS FROM " + resource.table);
    await this.hasMySQLCascadeFk(resource, config);
    const fieldTypes: Record<string, any> = {};
    (results as any[]).forEach((row: any) => {
      const field: any = {};
      const baseType = row.Type.toLowerCase();
      if (baseType == 'tinyint(1)') {
        field.type = AdminForthDataTypes.BOOLEAN;
        field._underlineType = 'bool';
      } else if (baseType.startsWith('tinyint')) {
        field.type = AdminForthDataTypes.INTEGER;
        field._underlineType = 'tinyint';
        field.minValue = baseType.includes('unsigned') ? 0 : -128;
        field.maxValue = baseType.includes('unsigned') ? 255 : 127;
      } else if (baseType.startsWith('smallint')) {
        field.type = AdminForthDataTypes.INTEGER;
        field._underlineType = 'tinyint';
        field.minValue = baseType.includes('unsigned') ? 0 : -32768;
        field.maxValue = baseType.includes('unsigned') ? 65535 : 32767;
      } else if (baseType.startsWith('int') || baseType.endsWith('int')) {
        field.type = AdminForthDataTypes.INTEGER;
        field._underlineType = 'int';
        field.minValue = baseType.includes('unsigned') ? 0 : null;
      } else if (baseType.startsWith('dec') || baseType.startsWith('numeric')) {
        field.type = AdminForthDataTypes.DECIMAL;
        field._underlineType = 'decimal';
        const [precision, scale] = baseType.match(/\d+/g);
        field.precision = parseInt(precision);
        field.scale = parseInt(scale);
        field.minValue = baseType.includes('unsigned') ? 0 : null;
      } else if (baseType.startsWith('float') || baseType.startsWith('double') || baseType.startsWith('real')) {
        field.type = AdminForthDataTypes.FLOAT;
        field._underlineType = 'float';
        field.minValue = baseType.includes('unsigned') ? 0 : null;
      } else if (baseType.startsWith('varchar')) {
        field.type = AdminForthDataTypes.STRING;
        field._underlineType = 'varchar';
        const length = baseType.match(/\d+/);
        field.maxLength = length ? parseInt(length[0]) : null;
      } else if (baseType.startsWith('char')) {
        field.type = AdminForthDataTypes.STRING;
        field._underlineType = 'char';
        const length = baseType.match(/\d+/);
        field.minLength = length ? parseInt(length[0]) : null;
        field.maxLength = length ? parseInt(length[0]) : null;
      } else if (baseType.endsWith('text')) {
        field.type = AdminForthDataTypes.TEXT;
        field._underlineType = 'text';
      } else if (baseType.startsWith('enum')) {
        field.type = AdminForthDataTypes.STRING;
        field._underlineType = 'enum';
      } else if (baseType.startsWith('json')) {
        field.type = AdminForthDataTypes.JSON;
        field._underlineType = 'json';
      } else if (baseType.startsWith('time')) {
        field.type = AdminForthDataTypes.TIME;
        field._underlineType = 'time';
      } else if (baseType.startsWith('datetime') || baseType.startsWith('timestamp')) {
        field.type = AdminForthDataTypes.DATETIME;
        field._underlineType = 'timestamp';
      } else if (baseType.startsWith('date')) {
        field.type = AdminForthDataTypes.DATE;
        field._underlineType = 'date';
      } else if (baseType.startsWith('year')) {
        field.type = AdminForthDataTypes.INTEGER;
        field._underlineType = 'year';
        field.minValue = 1901;
        field.maxValue = 2155;
      } else {
        field.type = 'unknown'
      }
      field._baseTypeDebug = baseType;
      field.primaryKey = row.Key === 'PRI';
      field.default = row.Default;
      field.required = row.Null === 'NO' && !row.Default;
      fieldTypes[row.Field] = field
    });
    return fieldTypes;
  }

  getFieldValue(field: any, value: any) {
    if (field.type == AdminForthDataTypes.DATETIME) {
      if (!value) {
        return null;
      }
      return dayjs.utc(value).toISOString();
    } else if (field.type == AdminForthDataTypes.DATE) {
      return value || null;
    } else if (field.type == AdminForthDataTypes.TIME) {
      return value || null;
    } else if (field.type == AdminForthDataTypes.BOOLEAN) {
      return value === null ? null : !!value;
    } else if (field.type == AdminForthDataTypes.JSON) {
      if (typeof value === 'string') {
        try {
          return JSON.parse(value);
        } catch (e: any) {
          return {'error': `Failed to parse JSON: ${e?.message}`}
        }
      } else if (typeof value === 'object') {
        return value;
      } else {
        afLogger.error(`JSON field value is not string or object, but has type: ${typeof value}`);
        afLogger.error(`Field:, ${field}`);
        return {}
      }
    }

    return value;
  }


  setFieldValue(field: any, value: any) {
    if (field.type == AdminForthDataTypes.DATETIME) {
      if (!value) {
        return null;
      }
      return dayjs.utc(value).format('YYYY-MM-DD HH:mm:ss');
    } else if (field.type == AdminForthDataTypes.BOOLEAN) {
      return value === null ? null : (value ? true : false);
    } else if (field.type == AdminForthDataTypes.JSON) {
      if (field._underlineType === 'json') {
        return value;
      } else {
        return JSON.stringify(value);
      }
    }
    return value;
  }


  getFilterString(filter: IAdminForthSingleFilter | IAdminForthAndOrFilter): string {
    if ((filter as IAdminForthSingleFilter).field) {
      // Field-to-field comparison support
      if ((filter as IAdminForthSingleFilter).rightField) {
        const left = (filter as IAdminForthSingleFilter).field;
        const right = (filter as IAdminForthSingleFilter).rightField;
        const operator = this.OperatorsMap[(filter as any).operator as keyof typeof this.OperatorsMap] as string;
        return `${left} ${operator} ${right}`;
      }
      // filter is a Single filter
      let placeholder = '?';
      let field = (filter as IAdminForthSingleFilter).field;
      let operator = this.OperatorsMap[(filter as any).operator as keyof typeof this.OperatorsMap] as string;
      
      // Handle IS_EMPTY and IS_NOT_EMPTY operators
      if (filter.operator == AdminForthFilterOperators.IS_EMPTY || filter.operator == AdminForthFilterOperators.IS_NOT_EMPTY) {
        return `${field} ${operator}`;
      } else if (filter.operator == AdminForthFilterOperators.IN || filter.operator == AdminForthFilterOperators.NIN) {
        placeholder = `(${(filter as IAdminForthSingleFilter).value.map(() => '?').join(', ')})`;
      } else if (filter.operator == AdminForthFilterOperators.ILIKE) {
        placeholder = `LOWER(?)`;
        field = `LOWER(${field})`;
        operator = 'LIKE';
      } else if (filter.operator == AdminForthFilterOperators.NE) {
        if (filter.value === null) {
          operator = 'IS NOT';
          placeholder = 'NULL';
        } else {
          // for not equal, we need to add a null check
          // because nullish field will not match != value
          placeholder = `${placeholder} OR ${field} IS NULL)`;
          field = `(${field}`;
        }
      } else if (filter.operator == AdminForthFilterOperators.EQ && filter.value === null) {
        operator = 'IS';
        placeholder = 'NULL';
      }
      return `${field} ${operator} ${placeholder}`;
    }

    // filter is a single insecure raw sql
    if ((filter as IAdminForthSingleFilter).insecureRawSQL) {
      return (filter as IAdminForthSingleFilter).insecureRawSQL as string;
    }

    // filter is a AndOr filter
    return (filter as IAdminForthAndOrFilter).subFilters.map((f) => {
      if ((f as IAdminForthSingleFilter).field || (f as IAdminForthSingleFilter).insecureRawSQL) {
        // subFilter is a Single filter
        return this.getFilterString(f);
      }

      // subFilter is a AndOr filter - add parentheses
      return `(${this.getFilterString(f)})`;
    }).join(` ${this.OperatorsMap[(filter as any).operator as keyof typeof this.OperatorsMap] as string} `);
  }
  getFilterParams(filter: IAdminForthSingleFilter | IAdminForthAndOrFilter): any[] {
    if ((filter as IAdminForthSingleFilter).field) {
      if ((filter as IAdminForthSingleFilter).rightField) {
        // No params for field-to-field comparisons
        return [];
      }
      // filter is a Single filter
      
      // Handle IS_EMPTY and IS_NOT_EMPTY operators - no params needed
      if (filter.operator == AdminForthFilterOperators.IS_EMPTY || filter.operator == AdminForthFilterOperators.IS_NOT_EMPTY) {
        return [];
      } else if (filter.operator == AdminForthFilterOperators.LIKE || filter.operator == AdminForthFilterOperators.ILIKE) {
        return [`%${filter.value}%`];
      } else if (filter.operator == AdminForthFilterOperators.IN || filter.operator == AdminForthFilterOperators.NIN) {
        return filter.value;
      } else if (filter.operator == AdminForthFilterOperators.EQ && (filter as IAdminForthSingleFilter).value === null) {
        return [];
      } else if (filter.operator == AdminForthFilterOperators.NE && (filter as IAdminForthSingleFilter).value === null) {
        return [];
      } else {
        return [(filter as IAdminForthSingleFilter).value];
      }
    }

    // filter is a Single insecure raw sql
    if ((filter as IAdminForthSingleFilter).insecureRawSQL) {
      return [];
    }

    // filter is a AndOrFilter
    return (filter as IAdminForthAndOrFilter).subFilters.reduce((params: any[], f: IAdminForthSingleFilter | IAdminForthAndOrFilter) => {
      return params.concat(this.getFilterParams(f));
    }, []);
  }

  whereClauseAndValues(filters: IAdminForthAndOrFilter) : {
    sql: string,
    values: any[],
  } {
    return filters.subFilters.length ? {
      sql: `WHERE ${this.getFilterString(filters)}`,
      values: this.getFilterParams(filters)
    } : { sql: '', values: [] };
  }

  async getAggregateWithOriginalTypes({ resource, filters, aggregations, groupBy }: {
    resource: AdminForthResource;
    filters: IAdminForthAndOrFilter;
    aggregations: { [alias: string]: IAggregationRule };
    groupBy?: IGroupByRule | IGroupByRule[];
  }): Promise<Array<{ group?: string, [key: string]: any }>> {
    const tableName = resource.table;
    const selectParts: string[] = [];
    const medianFields: { alias: string; field: string }[] = [];
    const groupExprs: string[] = [];
    const groupAliases: string[] = [];
    const groupByRules = this.normalizeGroupByRules(groupBy);

    for (const [index, groupByRule] of groupByRules.entries()) {
      let groupExpr: string;
      if (groupByRule.type === 'field') {
        groupExpr = `\`${groupByRule.field}\``;
      } else {
        const g = groupByRule as IGroupByDateTrunc;
        const tz = g.timezone ?? 'UTC';
        const innerExpr = `COALESCE(CONVERT_TZ(\`${g.field}\`, 'UTC', '${tz}'), \`${g.field}\`)`;
        switch (g.truncation) {
          case 'day':   groupExpr = `DATE_FORMAT(${innerExpr}, '%Y-%m-%d')`; break;
          case 'month': groupExpr = `DATE_FORMAT(${innerExpr}, '%Y-%m-01')`; break;
          case 'year':  groupExpr = `DATE_FORMAT(${innerExpr}, '%Y-01-01')`; break;
          case 'week':  groupExpr = `DATE_FORMAT(DATE_SUB(${innerExpr}, INTERVAL WEEKDAY(${innerExpr}) DAY), '%Y-%m-%d')`; break;
        }
      }
      const groupAlias = this.getGroupByResultAlias(groupByRule, index, groupByRules.length);
      groupExprs.push(groupExpr);
      groupAliases.push(groupAlias);
      selectParts.push(`${groupExpr} AS \`${groupAlias}\``);
    }

    for (const [alias, rule] of Object.entries(aggregations)) {
      const f = `\`${rule.field as string}\``;
      switch (rule.operation) {
        case 'sum':    selectParts.push(`SUM(${f}) AS \`${alias}\``); break;
        case 'count':  selectParts.push(`COUNT(*) AS \`${alias}\``); break;
        case 'count_distinct': selectParts.push(`COUNT(DISTINCT ${f}) AS \`${alias}\``); break;
        case 'avg':    selectParts.push(`AVG(${f}) AS \`${alias}\``); break;
        case 'min':    selectParts.push(`MIN(${f}) AS \`${alias}\``); break;
        case 'max':    selectParts.push(`MAX(${f}) AS \`${alias}\``); break;
        case 'median': medianFields.push({ alias, field: rule.field as string }); break;
      }
    }

    const { sql: where, values: filterValues } = this.whereClauseAndValues(filters);

    type AggRow = { group?: string } & Record<string, number | string | null>;

    // Run non-median aggregations
    let rows: AggRow[] = [];
    const hasNonMedian = selectParts.length > groupExprs.length;
    if (hasNonMedian) {
      let query = `SELECT ${selectParts.join(', ')} FROM \`${tableName}\` ${where}`;
      if (groupExprs.length) query += ` GROUP BY ${groupExprs.join(', ')} ORDER BY ${groupExprs.join(', ')} ASC`;
      dbLogger.trace(`🪲📜 MySQL AGG Q: ${query} values: ${JSON.stringify(filterValues)}`);
      const [result] = await this.client.execute(query, filterValues);
      rows = result as AggRow[];
    }

    // Run each median via window functions (MySQL 8+) — no session variables, no memory pressure
    for (const { alias, field } of medianFields) {
      const f = `\`${field}\``;
      const nullGuard = where ? `${where} AND ${f} IS NOT NULL` : `WHERE ${f} IS NOT NULL`;

      let medianQuery: string;
      if (groupExprs.length) {
        const groupSelect = groupExprs.map((expr, index) => `${expr} AS \`${groupAliases[index]}\``).join(', ');
        const groupColumns = groupAliases.map(alias => `\`${alias}\``).join(', ');
        medianQuery = `
          SELECT ${groupColumns}, AVG(${f}) AS \`${alias}\`
          FROM (
            SELECT ${groupSelect}, ${f},
              ROW_NUMBER() OVER (PARTITION BY ${groupExprs.join(', ')} ORDER BY ${f}) AS rn,
              COUNT(*) OVER (PARTITION BY ${groupExprs.join(', ')}) AS cnt
            FROM \`${tableName}\` ${nullGuard}
          ) t
          WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEIL((cnt + 1) / 2.0))
          GROUP BY ${groupColumns}
          ORDER BY ${groupColumns} ASC
        `;
      } else {
        medianQuery = `
          SELECT AVG(${f}) AS \`${alias}\`
          FROM (
            SELECT ${f},
              ROW_NUMBER() OVER (ORDER BY ${f}) AS rn,
              COUNT(*) OVER () AS cnt
            FROM \`${tableName}\` ${nullGuard}
          ) t
          WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEIL((cnt + 1) / 2.0))
        `;
      }

      dbLogger.trace(`🪲📜 MySQL MEDIAN Q: ${medianQuery} values: ${JSON.stringify(filterValues)}`);
      const [medianResult] = await this.client.execute(medianQuery, filterValues);
      const medianRows = medianResult as AggRow[];

      if (groupExprs.length) {
        const groupKey = (row: AggRow) => groupAliases.map(alias => String(row[alias])).join('\u0000');
        if (rows.length === 0) {
          rows = medianRows.map((r) => ({ ...Object.fromEntries(groupAliases.map(groupAlias => [groupAlias, r[groupAlias]])), [alias]: r[alias] }));
        } else {
          const byGroup = new Map(medianRows.map((r) => [groupKey(r), r[alias]]));
          for (const row of rows) {
            row[alias] = byGroup.get(groupKey(row)) ?? null;
          }
        }
      } else {
        const medianVal = medianRows[0]?.[alias] ?? null;
        if (rows.length === 0) {
          rows = [{ [alias]: medianVal }];
        } else {
          rows[0][alias] = medianVal;
        }
      }
    }

    return rows;
  }

  async getDataWithOriginalTypes({ resource, limit, offset, sort, filters, columns }: { resource: AdminForthResource; limit?: number; offset?: number; sort: Array<{ field: string; direction: AdminForthSortDirections }>; filters: IAdminForthAndOrFilter; columns?: Array<{ name: string }> }): Promise<any[]> {
    const selectedColumns = (columns ?? resource.dataSourceColumns).map((col: { name: string }) => `${col.name}`).join(', ');
    const tableName = resource.table;
    
    const { sql: where, values: filterValues } = this.whereClauseAndValues(filters);

    if (sort.some(s => !checkIfFieldIsInsideResourceColumns(s.field, resource))) {
      throw new Error(`Invalid sort field: ${sort.find(s => !checkIfFieldIsInsideResourceColumns(s.field, resource))?.field}`);
    }

    const orderBy = sort.length ? `ORDER BY ${sort.map((s: { field: string; direction: AdminForthSortDirections }) => `${s.field} ${this.SortDirectionsMap[s.direction]}`).join(', ')}` : '';
    let selectQuery = `SELECT ${selectedColumns} FROM ${tableName}`;
    if (where) selectQuery += ` ${where}`;
    if (orderBy) selectQuery += ` ${orderBy}`;
    if (limit) selectQuery += ` LIMIT ${limit}`;
    if (offset) selectQuery += ` OFFSET ${offset}`;

    dbLogger.trace(`🪲📜 MySQL Q: ${selectQuery} values: ${JSON.stringify(filterValues)}`);
    
    const [results] = await this.client.execute(selectQuery, filterValues);
    return (results as any[]).map((row: any) => {
      const newRow: Record<string, any> = {};
      for (const [key, value] of Object.entries(row as Record<string, any>)) {
        newRow[key] = value;
      }
      return newRow;
    });
  }

  async getCount({ resource, filters }: { resource: AdminForthResource; filters: IAdminForthAndOrFilter; }): Promise<number> {
    const tableName = resource.table;
    let normalizedFilters = filters;

    // validate and normalize in case this method is called from dataAPI
    if (filters) {
      const filterValidation = this.validateAndNormalizeFilters(filters, resource);
      if (!filterValidation.ok) {
        throw new Error(filterValidation.error);
      }
      normalizedFilters = filterValidation.normalizedFilters as IAdminForthAndOrFilter;
    }
    const { sql: where, values: filterValues } = this.whereClauseAndValues(normalizedFilters);
    const q = `SELECT COUNT(*) FROM ${tableName} ${where}`;
    dbLogger.trace(`🪲📜 MySQL Q: ${q} values: ${JSON.stringify(filterValues)}`);
    const [results] = await this.client.execute(q, filterValues);
    return +results[0]["COUNT(*)"];
  }
  
  async getMinMaxForColumnsWithOriginalTypes({ resource, columns }: { resource: AdminForthResource; columns: Array<{ name: string }> }) {
    const tableName = resource.table;
    const result: Record<string, { min: any; max: any }> = {};
    await Promise.all(columns.map(async (col: { name: string }) => {
      const q = `SELECT MIN(${col.name}) as min, MAX(${col.name}) as max FROM ${tableName}`;
      dbLogger.trace(`🪲📜 MySQL Q: ${q}`);
      const [results] = await this.client.execute(q);
      const { min, max } = results[0];
      result[col.name] = {
        min, max,
      };
    }))
    return result;
  }

  async createRecordOriginalValues({ resource, record }: { resource: AdminForthResource; record: Record<string, any> }): Promise<string> {
    const tableName = resource.table;
    const columns = Object.keys(record);
    const placeholders = columns.map(() => '?').join(', ');
    const values = columns.map((colName) => typeof record[colName] === 'undefined' ? null : record[colName]);
    const q = `INSERT INTO ${tableName} (${columns.join(', ')}) VALUES (${placeholders})`;
    dbLogger.trace(`🪲📜 MySQL Q: ${q} values: ${JSON.stringify(values)}`);
    const ret = await this.client.execute(q, values);
    return ret.insertId;
  }

  async updateRecordOriginalValues({ resource, recordId,  newValues }: { resource: AdminForthResource; recordId: any; newValues: Record<string, any> }) {
    const values = [...Object.values(newValues), recordId];
    const columnsWithPlaceholders = Object.keys(newValues).map((col) => `${col} = ?`).join(', ');
    const q = `UPDATE ${resource.table} SET ${columnsWithPlaceholders} WHERE ${this.getPrimaryKey(resource)} = ?`;
    dbLogger.trace(`🪲📜 MySQL Q: ${q} values: ${JSON.stringify(values)}`);
    await this.client.execute(q, values);
  }

  async deleteRecord({ resource, recordId }: { resource: AdminForthResource; recordId: any }): Promise<boolean> {
    const q = `DELETE FROM ${resource.table} WHERE ${this.getPrimaryKey(resource)} = ?`;
    dbLogger.trace(`🪲📜 MySQL Q: ${q} values: ${JSON.stringify([recordId])}`);
    const res = await this.client.execute(q, [recordId]);
    return res.rowCount > 0;
  }

  async deleteMany({ resource, recordIds }: { resource: AdminForthResource; recordIds: string[] }): Promise<number> {
    if (!recordIds || recordIds.length === 0) {
      return 0;
    }
    const placeholders = recordIds.map(() => '?').join(',');
    const query = `DELETE FROM ${resource.table} WHERE ${this.getPrimaryKey(resource)} IN (${placeholders})`;
    dbLogger.trace(`🪲📜 MySQL Q: ${query} values: ${JSON.stringify([recordIds])}`);
    const [result] = await this.client.execute(query, recordIds);
    return result.affectedRows ?? 0;
  }

  async close() {
    await this.client.end();
  }
}

export default MysqlConnector;