import { Injectable, Optional } from '@nestjs/common';
import { customAlphabet } from 'nanoid';
import { v7 as uuidv7 } from 'uuid';
import { BaseVersion } from 'nocodb-sdk';
import CryptoJS from 'crypto-js';
import dayjs from 'dayjs';
import utc from 'dayjs/plugin/utc';
import timezone from 'dayjs/plugin/timezone';
import type * as knex from 'knex';
import type { Knex } from 'knex';
import type { Condition } from '~/db/CustomKnex';
import XcMigrationSourcev0 from '~/meta/migrations/XcMigrationSourcev0';
import XcMigrationSource from '~/meta/migrations/XcMigrationSource';
import XcMigrationSourcev2 from '~/meta/migrations/XcMigrationSourcev2';
import { XKnex } from '~/db/CustomKnex';
import { NcConfig } from '~/utils/nc-config';
import {
  BaseRelatedMetaTables,
  MetaTable,
  RootScopes,
  RootScopeTables,
} from '~/utils/globals';
import { NcError } from '~/helpers/catchError';
import { isWorker } from '~/utils';

dayjs.extend(utc);
dayjs.extend(timezone);

const nanoid = customAlphabet('1234567890abcdefghijklmnopqrstuvwxyz_', 4);
const nanoidv2 = customAlphabet('1234567890abcdefghijklmnopqrstuvwxyz', 14);
const nanoidWorkspace = customAlphabet(
  '1234567890abcdefghijklmnopqrstuvwxyz',
  7,
);

@Injectable()
export class MetaService {
  protected _knex: knex.Knex;
  protected _config: any;

  constructor(
    config: NcConfig,
    @Optional() trx = null,
    @Optional() nested = 0,
  ) {
    this._config = config;
    this._knex = XKnex({
      ...this._config.meta.db,
      useNullAsDefault: true,
    });
    this.trx = trx;
    this.nested = nested;
  }

  get knexInstance(): knex.Knex {
    return this._knex;
  }

  get config(): NcConfig {
    return this._config;
  }

  public get connection() {
    return this.trx ?? this.knexInstance;
  }

  get knexConnection() {
    return this.connection;
  }

  public get knex(): any {
    return this.knexConnection;
  }

  /***
   * Generate nanoid for the given target
   * @param target - Table name
   * @returns {string} - Generated nanoid
   * */
  public async genNanoid(target: string) {
    if (target === MetaTable.AUDIT) {
      return uuidv7();
    }

    const prefixMap: { [key: string]: string } = {
      [MetaTable.PROJECT]: 'p',
      [MetaTable.SOURCES]: 'b',
      [MetaTable.MODELS]: 'm',
      [MetaTable.COLUMNS]: 'c',
      [MetaTable.COL_RELATIONS]: 'l',
      [MetaTable.COL_SELECT_OPTIONS]: 's',
      [MetaTable.COL_LOOKUP]: 'lk',
      [MetaTable.COL_ROLLUP]: 'rl',
      [MetaTable.COL_FORMULA]: 'f',
      [MetaTable.FILTER_EXP]: 'fi',
      [MetaTable.SORT]: 'so',
      [MetaTable.SHARED_VIEWS]: 'sv',
      [MetaTable.ACL]: 'ac',
      [MetaTable.FORM_VIEW]: 'fv',
      [MetaTable.FORM_VIEW_COLUMNS]: 'fvc',
      [MetaTable.GALLERY_VIEW]: 'gv',
      [MetaTable.GALLERY_VIEW_COLUMNS]: 'gvc',
      [MetaTable.KANBAN_VIEW]: 'kv',
      [MetaTable.KANBAN_VIEW_COLUMNS]: 'kvc',
      [MetaTable.CALENDAR_VIEW]: 'cv',
      [MetaTable.CALENDAR_VIEW_COLUMNS]: 'cvc',
      [MetaTable.CALENDAR_VIEW_RANGE]: 'cvr',
      [MetaTable.USERS]: 'us',
      [MetaTable.ORGS_OLD]: 'org',
      [MetaTable.TEAMS]: 'tm',
      [MetaTable.VIEWS]: 'vw',
      [MetaTable.HOOKS]: 'hk',
      [MetaTable.HOOK_LOGS]: 'hkl',
      [MetaTable.API_TOKENS]: 'tkn',
      [MetaTable.EXTENSIONS]: 'ext',
      [MetaTable.COMMENTS]: 'com',
      [MetaTable.COMMENTS_REACTIONS]: 'cre',
      [MetaTable.USER_COMMENTS_NOTIFICATIONS_PREFERENCE]: 'cnp',
      [MetaTable.JOBS]: 'job',
      [MetaTable.INTEGRATIONS]: 'int',
      [MetaTable.FILE_REFERENCES]: 'at',
      [MetaTable.COL_BUTTON]: 'btn',
      [MetaTable.SNAPSHOT]: 'snap',
      [MetaTable.SYNC_CONFIGS]: 'sync',
      [MetaTable.PERMISSIONS]: 'perm',
      [MetaTable.PERMISSION_SUBJECTS]: 'pers',
      [MetaTable.DASHBOARDS]: 'dash',
      [MetaTable.WIDGETS]: 'wgt',
      [MetaTable.WORKSPACE]: 'w',
      [MetaTable.DEPENDENCY_TRACKER]: 'dt',
      [MetaTable.INSTALLATIONS]: 'inst',
      [MetaTable.AUTOMATIONS]: 'aut',
      [MetaTable.AUTOMATION_EXECUTIONS]: 'auex',
      [MetaTable.MANAGED_APPS]: 'ma',
      [MetaTable.MANAGED_APP_VERSIONS]: 'mav',
      [MetaTable.MANAGED_APP_DEPLOYMENT_LOGS]: 'madl',
    };

    const prefix = prefixMap[target] || 'nc';

    // using nanoid to avoid collision with existing ids when duplicating
    return `${prefix}${
      target === MetaTable.WORKSPACE ? nanoidWorkspace() : nanoidv2()
    }`;
  }

  // private connection: XKnex;
  // todo: need to fix
  protected trx: Knex.Transaction;
  protected nested: number;

  public contextCondition(
    query: Knex.QueryBuilder,
    workspace_id: string,
    base_id: string,
    target: string,
  ) {
    if (workspace_id === base_id) {
      return;
    }

    if (target === MetaTable.WORKSPACE) {
      return;
    }

    query.where('fk_workspace_id', workspace_id);

    if (base_id === RootScopes.WORKSPACE) {
      return;
    }

    if (target !== MetaTable.PROJECT) {
      query.where('base_id', base_id);
    } else {
      query.where('id', base_id);
    }
  }

  /***
   * Insert record into meta data
   * @param workspace_id - Workspace id
   * @param base_id - Base id
   * @param target - Table name
   * @param data - Data to be inserted
   * @param ignoreIdGeneration - If true, will not generate id for the record
   */
  public async metaInsert2(
    workspace_id: string,
    base_id: string,
    target: string,
    data: any,
    ignoreIdGeneration?: boolean,
  ): Promise<any> {
    const insertObj = {
      ...data,
      ...(ignoreIdGeneration
        ? {}
        : { id: data?.id || (await this.genNanoid(target)) }),
    };

    if (workspace_id === base_id) {
      if (!Object.values(RootScopes).includes(workspace_id as RootScopes)) {
        NcError.metaError({
          message: 'Invalid scope',
          sql: '',
        });
      }

      if (!RootScopeTables[workspace_id].includes(target)) {
        NcError.metaError({
          message: 'Table not accessible from this scope',
          sql: '',
        });
      }
    } else {
      if (!workspace_id) {
        NcError.metaError({
          message: 'Workspace ID is required',
          sql: '',
        });
      }

      insertObj.fk_workspace_id = workspace_id;

      if (!base_id && base_id !== RootScopes.WORKSPACE) {
        NcError.metaError({
          message: 'Base ID is required',
          sql: '',
        });
      }
      if (base_id !== RootScopes.WORKSPACE) insertObj.base_id = base_id;
    }

    const at = this.now();
    insertObj.created_at = at;
    insertObj.updated_at = at;

    const qb = this.knexConnection(target).insert(insertObj);

    this.logHelper(workspace_id, base_id, target, qb);

    await qb;

    return insertObj;
  }

  /***
   * Insert multiple records into meta data
   * @param workspace_id - Workspace id
   * @param base_id - Base id
   * @param target - Table name
   * @param data - Data to be inserted
   * @param ignoreIdGeneration - If true, will not generate id for the record
   */
  public async bulkMetaInsert(
    workspace_id: string,
    base_id: string,
    target: string,
    data: any | any[],
    ignoreIdGeneration?: boolean,
  ): Promise<any> {
    if (Array.isArray(data) ? !data.length : !data) {
      return [];
    }

    const insertObj = [];
    const at = this.now();

    const commonProps: Record<string, any> = {
      created_at: at,
      updated_at: at,
    };

    if (workspace_id === base_id) {
      if (!Object.values(RootScopes).includes(workspace_id as RootScopes)) {
        NcError.metaError({
          message: 'Invalid scope',
          sql: '',
        });
      }

      if (!RootScopeTables[workspace_id].includes(target)) {
        NcError.metaError({
          message: 'Table not accessible from this scope',
          sql: '',
        });
      }
    } else {
      if (!workspace_id) {
        NcError.metaError({
          message: 'Workspace ID is required',
          sql: '',
        });
      }

      if (!base_id) {
        NcError.metaError({
          message: 'Base ID is required',
          sql: '',
        });
      }

      commonProps.fk_workspace_id = workspace_id;
      commonProps.base_id = base_id;
    }

    for (const d of Array.isArray(data) ? data : [data]) {
      const id =
        d?.id || (ignoreIdGeneration ? null : await this.genNanoid(target));
      const tempObj = {
        ...d,
        ...(ignoreIdGeneration ? {} : { id }),
        ...commonProps,
      };
      insertObj.push(tempObj);
    }
    await this.knexConnection.batchInsert(target, insertObj);

    return insertObj;
  }

  /***
   * Update multiple records in meta data
   * @param workspace_id - Workspace id
   * @param base_id - Base id
   * @param target - Table name
   * @param data - Data to be updated
   * @param ids - Ids of the records to be updated
   */
  public async bulkMetaUpdate(
    workspace_id: string,
    base_id: string,
    target: string,
    data: any | any[],
    ids: string[],
    condition?: { [p: string]: any },
  ): Promise<any> {
    if (Array.isArray(data) ? !data.length : !data) {
      return [];
    }

    const query = this.knexConnection(target);

    const at = this.now();

    if (workspace_id === base_id) {
      if (!Object.values(RootScopes).includes(workspace_id as RootScopes)) {
        NcError.metaError({
          message: 'Invalid scope',
          sql: '',
        });
      }

      if (!RootScopeTables[workspace_id].includes(target)) {
        NcError.metaError({
          message: 'Table not accessible from this scope',
          sql: '',
        });
      }
    } else {
      if (!workspace_id) {
        NcError.metaError({
          message: 'Workspace ID is required',
          sql: '',
        });
      }

      if (!base_id) {
        NcError.metaError({
          message: 'Base ID is required',
          sql: '',
        });
      }
    }

    const updateObj = {
      ...data,
      updated_at: at,
    };

    if (!condition) {
      query.whereIn('id', ids).update(updateObj);
    } else {
      if (![MetaTable.FILE_REFERENCES].includes(target as MetaTable)) {
        NcError.metaError({
          message: 'This table does not support conditional bulk update',
          sql: '',
        });
      }

      query.where(condition);

      // Check if a condition is present in the query builder and throw an error if not.
      this.checkConditionPresent(query, 'update');

      query.update(updateObj);
    }

    this.contextCondition(query, workspace_id, base_id, target);

    return query;
  }

  /***
   * Delete meta data
   * @param workspace_id - Workspace id
   * @param base_id - Base id
   * @param target - Table name
   * @param idOrCondition - If string, will delete the record with the given id. If object, will delete the record with the given condition.
   * @param xcCondition - Additional nested or complex condition to be added to the query.
   * @param force - If true, will not check if a condition is present in the query builder and will execute the query as is.
   */
  public async metaDelete(
    workspace_id: string,
    base_id: string,
    target: string,
    idOrCondition: string | { [p: string]: any },
    xcCondition?: Condition,
    force = false,
  ): Promise<void> {
    const query = this.knexConnection(target);

    if (workspace_id === base_id) {
      if (!Object.values(RootScopes).includes(workspace_id as RootScopes)) {
        NcError.metaError({
          message: 'Invalid scope',
          sql: '',
        });
      }

      if (!RootScopeTables[workspace_id].includes(target)) {
        NcError.metaError({
          message: 'Table not accessible from this scope',
          sql: '',
        });
      }
    } else {
      if (!workspace_id) {
        NcError.metaError({
          message: 'Workspace ID is required',
          sql: '',
        });
      }

      if (!base_id && base_id !== RootScopes.WORKSPACE) {
        NcError.metaError({
          message: 'Base ID is required',
          sql: '',
        });
      }
    }

    if (typeof idOrCondition !== 'object') {
      query.where('id', idOrCondition);
    } else if (idOrCondition) {
      query.where(idOrCondition);
    }

    if (xcCondition) {
      query.condition(xcCondition, {});
    }

    // Check if a condition is present in the query builder and throw an error if not.
    if (!force) {
      this.checkConditionPresent(query, 'delete');
    }

    // Apply context condition
    this.contextCondition(query, workspace_id, base_id, target);

    this.logHelper(workspace_id, base_id, target, query);

    return query.del();
  }

  /***
   * Get single record from meta data
   * @param workspace_id - Workspace id
   * @param base_id - Base alias
   * @param target - Table name
   * @param idOrCondition - If string, will get the record with the given id. If object, will get the record with the given condition.
   * @param fields - Fields to be selected
   */
  public async metaGet(
    workspace_id: string,
    base_id: string,
    target: string,
    idOrCondition: string | { [p: string]: any },
    fields?: string[],
    // xcCondition?
  ): Promise<any> {
    return this.metaGet2(workspace_id, base_id, target, idOrCondition, fields);
  }

  /***
   * Get meta data
   * @param workspace_id - Workspace id
   * @param base_id - Base id
   * @param target - Table name
   * @param idOrCondition - If string, will get the record with the given id. If object, will get the record with the given condition.
   * @param fields - Fields to be selected
   * @param xcCondition - Additional nested or complex condition to be added to the query.
   */
  public async metaGet2(
    workspace_id: string,
    base_id: string,
    target: string,
    idOrCondition: string | { [p: string]: any },
    fields?: string[],
    xcCondition?: Condition,
  ): Promise<any> {
    const query = this.knexConnection(target);

    if (xcCondition) {
      query.condition(xcCondition);
    }

    if (fields?.length) {
      query.select(...fields);
    }

    if (
      workspace_id === RootScopes.FULL_BYPASS &&
      base_id === RootScopes.FULL_BYPASS
    ) {
      // With full bypass, no context condition is applied
    } else if (
      workspace_id === RootScopes.BYPASS &&
      base_id === RootScopes.BYPASS
    ) {
      // bypass is only allowed for v2 bases, so we join the base table to ensure the base is v2
      if (BaseRelatedMetaTables.includes(target as MetaTable)) {
        query.whereExists(function () {
          this.select(1)
            .from(`${MetaTable.PROJECT} as p`)
            .whereRaw('p.id = base_id')
            .andWhere('p.version', BaseVersion.V2);
        });
      }
    } else if (workspace_id === base_id) {
      if (!Object.values(RootScopes).includes(workspace_id as RootScopes)) {
        NcError.metaError({
          message: 'Invalid scope',
          sql: '',
        });
      }

      if (!RootScopeTables[workspace_id].includes(target)) {
        NcError.metaError({
          message: 'Table not accessible from this scope',
          sql: '',
        });
      }
    } else {
      if (!workspace_id) {
        NcError.metaError({
          message: 'Workspace ID is required',
          sql: '',
        });
      }

      if (!base_id) {
        NcError.metaError({
          message: 'Base ID is required',
          sql: '',
        });
      }

      this.contextCondition(query, workspace_id, base_id, target);
    }

    if (!idOrCondition) {
      this.logHelper(workspace_id, base_id, target, query);

      return null;
    }

    if (typeof idOrCondition !== 'object') {
      query.where('id', idOrCondition);
    } else {
      query.where(idOrCondition);
    }

    this.logHelper(workspace_id, base_id, target, query);

    return query.first();
  }

  /***
   * Get list of meta data
   * @param workspace_id - Workspace id
   * @param base_id - Base id
   * @param target - Table name
   * @param args.condition - Condition to be applied
   * @param args.limit - Limit of records
   * @param args.offset - Offset of records
   * @param args.xcCondition - Additional nested or complex condition to be added to the query.
   * @param args.fields - Fields to be selected
   * @param args.orderBy - Order by fields
   * @returns {Promise<any[]>} - List of records
   * */
  public async metaList2(
    workspace_id: string,
    base_id: string,
    target: string,
    args?: {
      condition?: { [p: string]: any };
      limit?: number;
      offset?: number;
      xcCondition?: Condition;
      fields?: string[];
      orderBy?: { [key: string]: 'asc' | 'desc' };
    },
  ): Promise<any[]> {
    const query = this.knexConnection(target);

    if (workspace_id === base_id) {
      if (!Object.values(RootScopes).includes(workspace_id as RootScopes)) {
        NcError.metaError({
          message: 'Invalid scope',
          sql: '',
        });
      }

      const r = RootScopeTables[workspace_id];
      if (!r) {
        console.log('Invalid scope', RootScopeTables);
      }

      if (!RootScopeTables[workspace_id].includes(target)) {
        NcError.metaError({
          message: 'Table not accessible from this scope',
          sql: '',
        });
      }
    } else {
      if (!workspace_id) {
        NcError.metaError({
          message: 'Workspace ID is required',
          sql: '',
        });
      }

      if (!base_id) {
        NcError.metaError({
          message: 'Base ID is required',
          sql: '',
        });
      }

      this.contextCondition(query, workspace_id, base_id, target);
    }

    if (args?.condition) {
      query.where(args.condition);
    }
    if (args?.limit) {
      query.limit(args.limit);
    }
    if (args?.offset) {
      query.offset(args.offset);
    }
    if (args?.xcCondition) {
      (query as any).condition(args.xcCondition);
    }

    if (args?.orderBy) {
      for (const [col, dir] of Object.entries(args.orderBy)) {
        query.orderBy(col, dir);
      }
    }
    if (args?.fields?.length) {
      query.select(...args.fields);
    }

    this.logHelper(workspace_id, base_id, target, query);

    return query;
  }

  /***
   * Get count of meta data
   * @param workspace_id - Workspace id
   * @param base_id - Base id
   * @param target - Table name
   * @param args.condition - Condition to be applied
   * @param args.xcCondition - Additional nested or complex condition to be added to the query.
   * @param args.aggField - Field to be aggregated
   * @returns {Promise<number>} - Count of records
   * */
  public async metaCount(
    workspace_id: string,
    base_id: string,
    target: string,
    args?: {
      condition?: { [p: string]: any };
      xcCondition?: Condition;
      aggField?: string;
    },
  ): Promise<number> {
    const query = this.knexConnection(target);

    if (workspace_id === RootScopes.BYPASS && base_id === RootScopes.BYPASS) {
      // bypass
    } else if (workspace_id === base_id) {
      if (!Object.values(RootScopes).includes(workspace_id as RootScopes)) {
        NcError.metaError({
          message: 'Invalid scope',
          sql: '',
        });
      }

      if (!RootScopeTables[workspace_id].includes(target)) {
        NcError.metaError({
          message: 'Table not accessible from this scope',
          sql: '',
        });
      }
    } else {
      if (!workspace_id) {
        NcError.metaError({
          message: 'Workspace ID is required',
          sql: '',
        });
      }

      if (!base_id) {
        NcError.metaError({
          message: 'Base ID is required',
          sql: '',
        });
      }

      this.contextCondition(query, workspace_id, base_id, target);
    }

    if (args?.condition) {
      query.where(args.condition);
    }

    if (args?.xcCondition) {
      (query as any).condition(args.xcCondition);
    }

    query.count(args?.aggField || 'id', { as: 'count' }).first();

    this.logHelper(workspace_id, base_id, target, query);

    return +(await query)?.['count'] || 0;
  }

  /***
   * Update meta data
   * @param workspace_id - Workspace id
   * @param base_id - Base id
   * @param target - Table name
   * @param data - Data to be updated
   * @param idOrCondition - If string, will update the record with the given id. If object, will update the record with the given condition.
   * @param xcCondition - Additional nested or complex condition to be added to the query.
   * @param skipUpdatedAt - If true, will not update the updated_at field
   * @param force - If true, will not check if a condition is present in the query builder and will execute the query as is.
   */
  public async metaUpdate(
    workspace_id: string,
    base_id: string,
    target: string,
    data: any,
    idOrCondition?: string | { [p: string]: any },
    xcCondition?: Condition,
    skipUpdatedAt = false,
    force = false,
    allowCreatedAt = false,
  ): Promise<any> {
    const query = this.knexConnection(target);

    if (workspace_id === base_id) {
      if (!Object.values(RootScopes).includes(workspace_id as RootScopes)) {
        NcError.metaError({
          message: 'Invalid scope',
          sql: '',
        });
      }

      if (!RootScopeTables[workspace_id].includes(target)) {
        NcError.metaError({
          message: 'Table not accessible from this scope',
          sql: '',
        });
      }
    } else {
      if (!workspace_id) {
        NcError.metaError({
          message: 'Workspace ID is required',
          sql: '',
        });
      }

      if (!base_id) {
        NcError.metaError({
          message: 'Base ID is required',
          sql: '',
        });
      }
    }

    if (!allowCreatedAt) {
      delete data.created_at;
    }

    if (!skipUpdatedAt) {
      data.updated_at = this.now();
    }

    query.update(data);
    if (typeof idOrCondition !== 'object') {
      query.where('id', idOrCondition);
    } else if (idOrCondition) {
      query.where(idOrCondition);
    }
    if (xcCondition) {
      query.condition(xcCondition);
    }

    // Check if a condition is present in the query builder and throw an error if not.
    if (!force) {
      this.checkConditionPresent(query, 'update');
    }

    // Apply context condition
    this.contextCondition(query, workspace_id, base_id, target);

    this.logHelper(workspace_id, base_id, target, query);

    return await query;
  }

  /***
   * Get order value for the next record
   * @param target - Table name
   * @param condition - Condition to be applied
   * @param xcCondition - Additional nested or complex condition to be added to the query.
   * @returns {Promise<number>} - Order value
   * */
  public async metaGetNextOrder(
    target: string,
    condition: { [key: string]: any },
    xcCondition?: Condition,
  ): Promise<number> {
    const query = this.knexConnection(target);

    if (condition) {
      query.where(condition);
    }

    if (xcCondition) {
      (query as any).condition(xcCondition);
    }

    query.max('order', { as: 'order' });

    return (+(await query.first())?.order || 0) + 1;
  }

  protected async logHelper(workspace_id, base_id, target, q) {
    const qStr = q.toQuery();

    if (
      (workspace_id === RootScopes.BYPASS && base_id === RootScopes.BYPASS) ||
      (workspace_id === RootScopes.FULL_BYPASS &&
        base_id === RootScopes.FULL_BYPASS)
    ) {
      return;
    }

    if (target === MetaTable.PROJECT) {
      if (!qStr.includes('fk_workspace_id') || !qStr.includes('id')) {
        if (!(workspace_id in RootScopeTables)) {
          console.log(`Missing tenant isolation (${workspace_id}): ${qStr}`);
          console.log(new Error().stack);
        }
      }
    } else {
      if (
        !qStr.includes('fk_workspace_id') ||
        (base_id !== RootScopes.WORKSPACE && !qStr.includes('base_id'))
      ) {
        if (!(workspace_id in RootScopeTables)) {
          console.log(`Missing tenant isolation (${workspace_id}): ${qStr}`);
          console.log(new Error().stack);
        }
      }
    }
  }

  async commit() {
    if (this.trx && this.nested === 0) {
      await this.trx.commit();
      this.trx = null;
    }
  }

  async rollback(e?) {
    if (this.trx) {
      await this.trx.rollback(e);
    }
    this.trx = null;
  }

  async startTransaction(): Promise<MetaService> {
    const trx = this.connection.isTransaction
      ? this.connection
      : await this.connection.transaction();

    // todo: tobe done
    return new MetaService(
      this.config,
      trx,
      // we need to keep track of the nested transaction level
      this.connection.isTransaction ? this.nested + 1 : 0,
    );
  }

  /***
   * Get base list with decrypted config
   * @returns {Promise<any[]>} - List of bases
   * */
  public async legacyProjectList(): Promise<any[]> {
    // check if table exists
    const tableExists = await this.knexConnection.schema.hasTable(
      'nc_projects',
    );

    if (!tableExists) {
      return [];
    }

    return (await this.knexConnection('nc_projects').select()).map((p) => {
      p.config = CryptoJS.AES.decrypt(
        p.config,
        'secret', // todo: tobe replaced - this.config?.auth?.jwt?.secret
      ).toString(CryptoJS.enc.Utf8);
      return p;
    });
  }

  private getNanoId() {
    return nanoid();
  }

  private isMySQL(): boolean {
    return (
      this.connection.clientType() === 'mysql' ||
      this.connection.clientType() === 'mysql2'
    );
  }

  public now(): any {
    return dayjs()
      .utc()
      .format(this.isMySQL() ? 'YYYY-MM-DD HH:mm:ss' : 'YYYY-MM-DD HH:mm:ssZ');
  }

  public formatDateTime(date: string): string {
    return dayjs(date)
      .utc()
      .format(this.isMySQL() ? 'YYYY-MM-DD HH:mm:ss' : 'YYYY-MM-DD HH:mm:ssZ');
  }

  public async init(): Promise<boolean> {
    // skip migration in worker container
    if (isWorker) {
      return true;
    }

    /*
      if xc_knex_migrations table exists, then run the migrations (Existing installations)
      if xc_knex_migrations table does not exist, then run the migrations (Fresh installations)

      Soon we will have a version where we will remove old migrations
      So users will have to upgrade to specific version first and then to the latest version
    */
    if (await this.connection.schema.hasTable('xc_knex_migrations')) {
      // see if there are records in the v1 migration table
      const records = await this.connection('xc_knex_migrations')
        .select('*')
        .limit(1);

      if (records.length > 0) {
        await this.connection.migrate.latest({
          migrationSource: new XcMigrationSource(),
          tableName: 'xc_knex_migrations',
        });
        await this.connection.migrate.latest({
          migrationSource: new XcMigrationSourcev2(),
          tableName: 'xc_knex_migrationsv2',
        });
      }
    }

    await this.connection.migrate.latest({
      migrationSource: new XcMigrationSourcev0(),
      tableName: 'xc_knex_migrationsv0',
    });

    return true;
  }

  /**
   * Checks if a condition is present in the query builder and throws an error if not.
   *
   * @param queryBuilder - The Knex QueryBuilder instance to check.
   */
  protected checkConditionPresent(
    queryBuilder: Knex.QueryBuilder,
    operation: 'delete' | 'update',
  ) {
    // Convert the query builder to a SQL string to inspect the presence of a WHERE clause.
    const sql = queryBuilder.toString();

    // Ensure that a WHERE condition is present in the query builder.
    // Note: The `hasWhere` method alone is not sufficient since it can indicate an empty nested WHERE group.
    // Therefore, also check the SQL string for the presence of the 'WHERE' keyword.
    if (queryBuilder.hasWhere() && /\bWHERE\b/i.test(sql)) {
      return;
    }

    // Throw an error if no condition is found in the query builder.
    NcError.metaError({
      message: 'A condition is required to ' + operation + ' records.',
      sql,
    });
  }
}
