sequelize#Op TypeScript Examples

/**
 * Removes all the playerIds from all ongoing/upcoming competitions of a specific group.
 *
 * This should be executed when players are removed from a group, so that they are
 * no longer participating in future or current group competitions.
 */
async function removeFromGroupCompetitions(groupId, playerIds) {
  // Find all upcoming/ongoing competitions for the group
  const competitionIds = (
    await Competition.findAll({
      attributes: ['id'],
      where: {
        groupId,
        endsAt: { [Op.gt]: new Date() }
      }
    })
  ).map(c => c.id);

  await Participation.destroy({ where: { competitionId: competitionIds, playerId: playerIds } });
}

deleteDraft = async (
  models,
  req: Request,
  res: Response,
  next: NextFunction
) => {
  const [chain, error] = await validateChain(models, req.body);
  if (error) return next(new Error(error));
  const [author, authorError] = await lookupAddressIsOwnedByUser(models, req);
  if (authorError) return next(new Error(authorError));

  if (!req.body.id) {
    return next(new Error(Errors.NoId));
  }

  try {
    const userOwnedAddressIds = (await req.user.getAddresses())
      .filter((addr) => !!addr.verified)
      .map((addr) => addr.id);
    const draft = await models.DiscussionDraft.findOne({
      where: {
        id: req.body.id,
        address_id: { [Op.in]: userOwnedAddressIds },
      },
    });
    if (!draft) {
      return next(new Error(Errors.NotFound));
    }
    await draft.destroy();
    return res.json({ status: 'Success' });
  } catch (e) {
    return next(e);
  }
}

async getSubmissions(query) {
        let limit = parseInt(query.limit, 10) || 500;
        let offset = parseInt(query.offset, 10) || 0;
        let sortDirection = query.sort_direction || 'ASC';
        let idGte = parseInt(query.id_greater_than_or_equal, 10) || 0;

        return await this.submissionModel.findAll({
            where: {
                id: {
                    [Op.gte]: idGte
                }
            },
            order: [['id', sortDirection]],
            limit,
            offset
        });
    }

search = async (req: Request, res: Response) => {
  try {
    const { name } = req.params;
    const { limit = 10, offset = 1 } = req.query;

    const result = await model.Character.findAll({
      limit: Number(limit),
      offset: Number(offset) - 1,
      where: {
        Name: { [Op.like]: `%${name}%` }
      },
      attributes: {
        exclude: ['Quest', 'Inventory', 'AccountID', 'MapPosX', 'MapPosY']
      },
      include: [
        {
          model: model.MEMB_STAT,
          attributes: {
            exclude: ['memb___id']
          }
        },
        {
          model: model.AccountCharacter,
          attributes: {
            exclude: ['Id']
          }
        }
      ]
    });

    res.json(result);
  } catch (error) {
    logger.error({ error, res });
  }
}

async function scheduleStarting(delayMs: number): Promise<void> {
  const startSearchDate = Date.now() - SAFETY_GAP + delayMs;
  const endSearchDate = startSearchDate + EXECUTION_FREQUENCY;

  const competitionsStarting = await Competition.findAll({
    where: { startsAt: { [Op.between]: [startSearchDate, endSearchDate] } }
  });

  competitionsStarting.forEach(c => {
    const eventDelay = Math.max(0, c.startsAt.getTime() - delayMs - Date.now());

    setTimeout(() => {
      // If competition is starting in < 1min, schedule the "started" event instead
      if (delayMs === 0) {
        onCompetitionStarted(c);
      } else {
        onCompetitionStarting(c, getEventPeriodDelay(delayMs));
      }
    }, eventDelay);
  });
}

async getAllBranches(projectId: string, options?: Pagination) {
    const order = [['branch', 'DESC']] as Order;
    // sequelize filter
    const where: WhereOptions<BuildAttributes> = { projectId };
    if (options?.search) {
      where.branch = { [Op.iLike]: `%${options.search}%` };
    }
    // findAllAncCount won't work on groupBy clause
    const count = await Build.count({
      where,
      attributes: undefined,
      distinct: true,
      col: 'branch',
    });
    /**
     * Branch is formed from the CI CD pipeline
     * To get branches we group branch property of a build
     */
    const rows = await Build.findAll({
      raw: true,
      order,
      limit: options?.limit || 1000,
      offset: options?.offset || 0,
      where,
      group: ['branch'],
      attributes: ['branch'],
    });
    return { count, rows };
  }

async function getEHPRank(player: Player, ehpValue: number): Promise<number> {
  const rank = await Player.count({
    where: {
      id: { [Op.not]: player.id },
      ehp: { [Op.gte]: ehpValue },
      type: player.type
    }
  });

  // If player is not in the top 50, a quick COUNT(*) query gives an acceptable
  // rank approximation, this however won't work for players in the top of the
  // leaderboards, and we'll have to use their registration date as a tie breaker
  if (rank > 50) return rank + 1;

  const topPlayers = await Player.findAll({
    where: {
      ehp: { [Op.gte]: ehpValue },
      type: player.type
    }
  });

  const smarterRank = topPlayers
    .sort((a, b) => b.ehp - a.ehp || a.registeredAt.getTime() - b.registeredAt.getTime())
    .findIndex(p => p.id === player.id);

  return smarterRank < 0 ? rank + 1 : smarterRank + 1;
}

deleteReaction = async (models: DB, req: Request, res: Response, next: NextFunction) => {
  if (!req.user) {
    return next(new Error(Errors.NotLoggedIn));
  }
  if (!req.body.reaction_id) {
    return next(new Error(Errors.NoReactionId));
  }

  try {
    const userOwnedAddressIds = (await req.user.getAddresses()).filter((addr) => !!addr.verified).map((addr) => addr.id);
    const reaction = await models.OffchainReaction.findOne({
      where: {
        id: req.body.reaction_id,
        address_id: { [Op.in]: userOwnedAddressIds },
      },
      include: [ models.Address ],
    });
    // actually delete
    await reaction.destroy();
    return res.json({ status: 'Success' });
  } catch (e) {
    return next(e);
  }
}

makeService = () => {
  return new Service({
    Model,
    multi: true,
    operators: {
      $not: Op.not
    },
    whitelist: ["$not"],
    casl: {
      availableFields: [
        "id", 
        "userId", 
        "hi", 
        "test", 
        "published",
        "supersecret", 
        "hidden"
      ]
    },
    paginate: {
      default: 10,
      max: 50
    }
  });
}

static async getCurrentReminders(): Promise<ReminderAttributes[]> {
        return Reminder.findAll({
            where: {
                remindAt: {
                    [Op.lte]: new Date()
                }
            }
        });
    }

findByAccountAndEra = (account: number, era: number): Promise<ValidatorStats> => {
  return ValidatorStats.findOne( 
    { 
      where: {
        accountId: { [Op.eq]: account },
        eraId: { [Op.eq]: era }
      }
  })
}

async getAllProjects(options?: Pagination) {
    const order = [['createdAt', 'DESC']] as Order;
    // sequelize filter
    const where: WhereOptions<ProjectAttributes> = {};
    if (options?.search) {
      where.name = { [Op.iLike]: `%${options.search}%` };
    }

    return Project.findAndCountAll({
      raw: true,
      order,
      limit: options?.limit || 1000,
      offset: options?.offset || 0,
      where,
    });
  }

export function createDataProvider(
  sequelize: Sequelize,
  name: string,
  modelName: string,
  isIndexState?: boolean,
): DataProvider {
  const models = modelBuilder.build(sequelize);

  if (isIndexState) {
    models.push(sequelize.define(name, indexStateSequelizeFields));
  }

  const m = models.find(model => model.name == modelName);

  if (!m) {
    const errMsg = `Missing of entity name [${name}], modelName [${modelName}]`;
    config.logger.error(errMsg);
    throw new Error(errMsg);
  }

  return {
    name,
    setup: async () => {
      if (isIndexState) {
        return sequelize
          .sync({
            logging: false,
          })
          .then(() => {});
      }
    },

    count(): Promise<number> {
      return m.count();
    },

    findAll({ limit, after }, { entity: { key } }): Promise<any[]> {
      return m.findAll({
        limit,
        where: after && {
          [key]: {
            [Op.gt]: after,
          },
        },
      });
    },

    find(id: any, meta?: any): Promise<any> {
      return m.findByPk(id, { raw: true });
    },

    create(data: any, meta?: any): Promise<any> {
      return m.create(data).then(() => data);
    },

    update(data: any, { entity: { key } }): Promise<any> {
      return m.update(data, {
        where: {
          [key]: data[key],
        },
        logging: !isIndexState,
      });
    },

    delete(id: any, meta?: any): Promise<boolean> {
      return m.destroy(id).then(() => true);
    },
  };
}

static findExpiredBans = (now: Date) => Ban.findAll({
        where: {
            bannedUntil: {
                [Op.ne]: null,
                [Op.lte]: now
            }
        }
    });

/**
 *
 * @param model
 * @param prefixName
 * @returns
 */
function getFilteredQuery(model?: ModelCtor<any>, prefixName?: string): any {
  const sequelizeQuery = new SqlizeQuery()
  sequelizeQuery.addValueParser(parserString)
  sequelizeQuery.addQueryBuilder(
    (filterData: { id: string; value: any }, queryHelper) => {
      const { id, value } = filterData || {}
      const curId = getExactQueryIdModel(id, prefixName)
      if (!curId) {
        return
      }

      const type = typeof getPrimitiveDataType(
        model?.rawAttributes?.[curId]?.type
      )

      // check not number
      if (type !== 'number') {
        // check value uuid
        if (uuidValidate(value)) {
          queryHelper.setQuery(curId, {
            [Op.eq]: value,
          })
        } else if (DB_CONNECTION === 'postgres') {
          // check connection postgress case sensitive
          queryHelper.setQuery(curId, {
            [Op.iLike]: `%${value}%`,
          })
        } else {
          // default not postgres
          queryHelper.setQuery(curId, {
            [Op.like]: `%${value}%`,
          })
        }
      } else {
        // default number
        queryHelper.setQuery(
          curId,
          curId.endsWith('Id')
            ? value
            : {
                [Op.like]: `%${value}%`,
              }
        )
      }
    }
  )
  return sequelizeQuery
}

deleteThread = async (
  models: DB,
  req: Request,
  res: Response,
  next: NextFunction
) => {
  const { thread_id, chain_id } = req.body;
  if (!req.user) {
    return next(new Error(DeleteThreadErrors.NoUser));
  }
  if (!thread_id) {
    return next(new Error(DeleteThreadErrors.NoThread));
  }

  try {
    const userOwnedAddressIds = (await req.user.getAddresses())
      .filter((addr) => !!addr.verified)
      .map((addr) => addr.id);

    const myThread = await models.OffchainThread.findOne({
      where: {
        id: req.body.thread_id,
        address_id: { [Op.in]: userOwnedAddressIds },
      },
      include: [models.Chain],
    });

    let thread = myThread;
    if (!myThread) {
      const isAdminOrMod = validateRoles(models, req, 'moderator', chain_id);

      if (!isAdminOrMod) {
        return next(new Error(DeleteThreadErrors.NoPermission));
      }

      thread = await models.OffchainThread.findOne({
        where: {
          id: req.body.thread_id,
        },
        include: [models.Chain],
      });

      if (!thread) {
        return next(new Error(DeleteThreadErrors.NoThread));
      }
    }

    const topic = await models.OffchainTopic.findOne({
      where: { id: thread.topic_id },
      include: [{ model: models.OffchainThread, as: 'threads' }],
    });

    // find and delete all associated subscriptions
    const subscriptions = await models.Subscription.findAll({
      where: {
        offchain_thread_id: thread.id,
      },
    });
    await Promise.all(
      subscriptions.map((s) => {
        return s.destroy();
      })
    );

    await thread.destroy();
    return res.json({ status: 'Success' });
  } catch (e) {
    return next(e);
  }
}

async function deleteInvalidPeriodDeltas(period: string, thresholdDate: Date) {
  await Delta.destroy({
    where: {
      period,
      updatedAt: { [Op.lt]: thresholdDate }
    }
  });
}

activeThreads = async (
  models: DB,
  req: Request,
  res: Response,
  next: NextFunction
): Promise<Response | void> => {
  const [chain, error] = await validateChain(models, req.query);
  if (error) return next(new Error(error));

  let { threads_per_topic } = req.query;
  if (!threads_per_topic
    || Number.isNaN(threads_per_topic)
    || threads_per_topic < MIN_THREADS_PER_TOPIC
    || threads_per_topic > MAX_THREADS_PER_TOPIC) {
    threads_per_topic = 3;
  }

  const allThreads = [];
  try {
    const communityWhere = { chain_id: chain.id }
    const communityTopics = await models.OffchainTopic.findAll({
      where: communityWhere
    });

    const threadInclude = [
      { model: models.Address, as: 'Address', },
      { model: models.Address, as: 'collaborators',},
      { model: models.OffchainTopic, as: 'topic', },
      { model: models.LinkedThread, as: 'linked_threads' },
      { model: models.ChainEntity }
    ];

    await Promise.all(communityTopics.map(async (topic) => {
      const recentTopicThreads = await models.OffchainThread.findAll({
        where: {
          topic_id: topic.id,
          last_commented_on: {
            [Op.not]: null,
          }
        },
        include: threadInclude,
        limit: threads_per_topic,
        order: [['last_commented_on', 'DESC']]
      });

      // In absence of X threads with recent activity (comments),
      // commentless threads are fetched and included as active
      if (!recentTopicThreads || recentTopicThreads.length < threads_per_topic) {
        const commentlessTopicThreads = await models.OffchainThread.findAll({
          where: {
            topic_id: topic.id,
            last_commented_on: {
              [Op.is]: null,
            }
          },
          include: threadInclude,
          limit: threads_per_topic - (recentTopicThreads || []).length,
          order: [['created_at', 'DESC']]
        });

        recentTopicThreads.push(...(commentlessTopicThreads || []));
      }

      allThreads.push(...(recentTopicThreads || []));
    })).catch((err) => {
      return next(new Error(err));
    });

    return res.json({
      status: 'Success',
      result: allThreads.map((c) => c.toJSON()),
    });
  } catch (err) {
    return next(new Error(err));
  }
}

/**
 * Returns a list of all competitions that
 * match the query parameters (title, status, metric).
 */
async function getList(filter: CompetitionListFilter, pagination: Pagination) {
  const { title, status, metric, type } = filter;

  // The status is optional, however if present, should be valid
  if (status && !COMPETITION_STATUSES.includes(status.toLowerCase() as CompetitionStatus)) {
    throw new BadRequestError(`Invalid status.`);
  }

  // The metric is optional, however if present, should be valid
  if (metric && !METRICS.includes(metric.toLowerCase() as Metric)) {
    throw new BadRequestError(`Invalid metric.`);
  }

  // The type is optional, however if present, should be valid
  if (type && !COMPETITION_TYPES.includes(type.toLowerCase() as CompetitionType)) {
    throw new BadRequestError(`Invalid type.`);
  }

  const query = buildQuery({
    title: title && { [Op.iLike]: `%${sanitizeTitle(title)}%` },
    metric: metric?.toLowerCase(),
    type: type?.toLowerCase()
  });

  if (status) {
    const formattedStatus = status.toLowerCase();
    const now = new Date();

    if (formattedStatus === CompetitionStatus.FINISHED) {
      query.endsAt = { [Op.lt]: now };
    } else if (formattedStatus === CompetitionStatus.UPCOMING) {
      query.startsAt = { [Op.gt]: now };
    } else if (formattedStatus === CompetitionStatus.ONGOING) {
      query.startsAt = { [Op.lt]: now };
      query.endsAt = { [Op.gt]: now };
    }
  }

  const competitions = await Competition.findAll({
    where: query,
    order: [
      ['score', 'DESC'],
      ['createdAt', 'DESC']
    ],
    limit: pagination.limit,
    offset: pagination.offset
  });

  const extendedCompetitions = await extendCompetitions(competitions);

  return extendedCompetitions;
}

async getLHScores(projectId: string, buildIds: string[]) {
    /**
     * On passing multiple builds the filter from
     * checking as pk is changed to sql in operator
     */
    const buildFilter = buildIds.length <= 1 ? buildIds[0] : { [Op.in]: buildIds };

    /**
     * scores are fetched with
     * GROUPING : ["buildId","name"] and value is averaged
     * Thus gives LH average score of all properties from DB itself
     */
    const stats = await Statistic.findAll({
      raw: true,
      where: {
        projectId,
        buildId: buildFilter,
        name: {
          [Op.or]: Object.keys(DB_SCORE_KEY_TO_LH_KEY),
        },
      },
      group: ['buildId', 'name'],
      attributes: ['name', [fn('AVG', col('value')), 'value'], 'buildId'],
    });

    const lhScoreGroupByBuildId: Record<string, LighthouseScoreType> = {};

    stats.forEach((stat) => {
      const key = DB_SCORE_KEY_TO_LH_KEY[stat.name];
      const score = Math.min(Math.round(stat.value * 100), 100); // convert to percentage

      if (lhScoreGroupByBuildId?.[stat.buildId]) {
        lhScoreGroupByBuildId[stat.buildId][key] = score;
      } else {
        lhScoreGroupByBuildId[stat.buildId] = {
          [key]: score,
        } as LighthouseScoreType;
      }
    });
    return lhScoreGroupByBuildId;
  }

/**
 * Gets the best records for a specific metric and period.
 * Optionally, the records can be filtered by the playerType and playerBuild.
 */
async function getLeaderboard(filter: GlobalRecordsFilter, pagination: Pagination): Promise<Record[]> {
  const { metric, period, playerBuild, playerType, country } = filter;
  const countryCode = country ? findCountry(country)?.code : null;

  if (!period || !isValidPeriod(period)) {
    throw new BadRequestError(`Invalid period: ${period}.`);
  }

  if (!metric || !METRICS.includes(metric as Metric)) {
    throw new BadRequestError(`Invalid metric: ${metric}.`);
  }

  if (playerType && !PLAYER_TYPES.includes(playerType as PlayerType)) {
    throw new BadRequestError(`Invalid player type: ${playerType}.`);
  }

  if (playerBuild && !PLAYER_BUILDS.includes(playerBuild as PlayerBuild)) {
    throw new BadRequestError(`Invalid player build: ${playerBuild}.`);
  }

  if (country && !countryCode) {
    throw new BadRequestError(
      `Invalid country. You must either supply a valid code or name, according to the ISO 3166-1 standard. \
      Please see: https://en.wikipedia.org/wiki/ISO_3166-1_alpha-2`
    );
  }

  const query = buildQuery({ type: playerType, build: playerBuild, country: countryCode });

  // When filtering by player type, the ironman filter should include UIM and HCIM
  if (query.type && query.type === PlayerType.IRONMAN) {
    query.type = { [Op.or]: [PlayerType.IRONMAN, PlayerType.HARDCORE, PlayerType.ULTIMATE] };
  }

  const records = await Record.findAll({
    where: { period, metric },
    include: [
      {
        model: Player,
        where: query
      }
    ],
    order: [['value', 'DESC']],
    limit: pagination.limit,
    offset: pagination.offset
  });

  return records;
}

async function computeRevenueForBlocks(startBlockHeight: number, endBlockheight: number) {
  const spannedDays = await Day.findAll({
    attributes: ["aktPrice"],
    where: {
      lastBlockHeightYet: { [Op.gte]: startBlockHeight },
      firstBlockHeight: { [Op.lte]: endBlockheight }
    },
    include: [
      {
        model: Block,
        as: "lastBlockYet",
        attributes: ["totalUAktSpent"],
        required: true
      },
      {
        model: Block,
        as: "firstBlock",
        attributes: ["totalUAktSpent"],
        required: true
      }
    ]
  });

  let revenues = {
    uakt: 0,
    usd: 0
  };

  for (const day of spannedDays) {
    const lastValueOfDay = day.lastBlockYet.totalUAktSpent;
    const firstValueOfDay = day.firstBlock.totalUAktSpent;
    const uaktSpent = lastValueOfDay - firstValueOfDay;
    revenues.uakt += uaktSpent;
    revenues.usd += uaktSpent * day.aktPrice;
    // TODO Handle no price
  }

  return {
    ...revenues,
    akt: uaktToAKT(revenues.uakt, 6)
  };
}

/**
 * Submit a new name change request, from oldName to newName.
 */
async function submit(oldName: string, newName: string): Promise<NameChange> {
  if (!playerService.isValidUsername(oldName)) {
    throw new BadRequestError('Invalid old name.');
  }

  if (!playerService.isValidUsername(newName)) {
    throw new BadRequestError('Invalid new name.');
  }

  if (playerService.sanitize(oldName) === playerService.sanitize(newName)) {
    throw new BadRequestError('Old name and new name cannot be the same.');
  }

  const stOldName = playerService.standardize(oldName);
  const stNewName = playerService.standardize(newName);

  // Check if a player with the "oldName" username is registered
  const oldPlayer = await playerService.find(stOldName);

  if (!oldPlayer) {
    throw new BadRequestError(`Player '${oldName}' is not tracked yet.`);
  }

  // If these are the same name, just different capitalizations, skip these checks
  if (stOldName !== stNewName) {
    // Check if there's any pending name changes for these names
    const pending = await NameChange.findOne({
      where: {
        oldName: { [Op.iLike]: stOldName },
        newName: { [Op.iLike]: stNewName },
        status: NameChangeStatus.PENDING
      }
    });

    if (pending) {
      throw new BadRequestError(`There's already a similar pending name change. (Id: ${pending.id})`);
    }

    const newPlayer = await playerService.find(stNewName);

    // To prevent people from submitting duplicate name change requests, which then
    // will waste time and resources to process and deny, it's best to check if this
    // exact same name change has been approved.
    if (newPlayer) {
      const lastChange = await NameChange.findOne({
        where: { playerId: newPlayer.id, status: NameChangeStatus.APPROVED },
        order: [['createdAt', 'DESC']]
      });

      if (lastChange && playerService.standardize(lastChange.oldName) === stOldName) {
        throw new BadRequestError(`Cannot submit a duplicate (approved) name change. (Id: ${lastChange.id})`);
      }
    }
  }

  // Create a new instance (a new name change request)
  const nameChange = await NameChange.create({
    playerId: oldPlayer.id,
    oldName: oldPlayer.displayName,
    newName: playerService.sanitize(newName)
  });

  return nameChange;
}

updateThreadLinkedChainEntities = async (models: DB, req: Request, res: Response, next: NextFunction) => {
  const [chain, error] = await validateChain(models, req.body);
  if (error) return next(new Error(error));
  const { thread_id } = req.body;

  const thread = await models.OffchainThread.findOne({
    where: {
      id: thread_id,
    },
  });
  if (!thread) return next(new Error(Errors.NoThread));
  const userOwnedAddressIds = (await req.user.getAddresses())
    .filter((addr) => !!addr.verified).map((addr) => addr.id);
  if (!userOwnedAddressIds.includes(thread.address_id)) { // is not author
    const roles = await models.Role.findAll({
      where: {
        address_id: { [Op.in]: userOwnedAddressIds, },
        permission: { [Op.in]: ['admin', 'moderator'] },
      }
    });
    const role = roles.find((r) => {
      return r.chain_id === thread.chain;
    });
    if (!role) return next(new Error(Errors.NotAdminOrOwner));
  }

  const chain_entity_ids = typeof req.body['chain_entity_id[]'] === 'string' ? [req.body['chain_entity_id[]']]
    : req.body['chain_entity_id[]'] ? req.body['chain_entity_id[]'].map((id) => +id) : [];

  // remove any chain entities no longer linked to this thread
  const existingChainEntities = await models.ChainEntity.findAll({
    where: { thread_id }
  });
  const entitiesToClear = existingChainEntities.filter((ce) => chain_entity_ids.indexOf(ce.id) === -1);
  for (let i = 0; i < entitiesToClear.length; i++) {
    entitiesToClear[i].thread_id = null;
    await entitiesToClear[i].save();
  }

  // add any chain entities newly linked to this thread
  const existingEntityIds = existingChainEntities.map((ce) => ce.id);
  const entityIdsToSet = chain_entity_ids.filter((id) => existingEntityIds.indexOf(id) === -1);
  const entitiesToSet = await models.ChainEntity.findAll({
    where: {
      id: { [Op.in]: entityIdsToSet }
    }
  });
  for (let i = 0; i < entitiesToSet.length; i++) {
    if (entitiesToSet[i].thread_id) {
      return next(new Error(Errors.ChainEntityAlreadyHasThread));
    }
    entitiesToSet[i].thread_id = thread_id;
    await entitiesToSet[i].save();
  }

  const finalThread = await models.OffchainThread.findOne({
    where: { id: thread_id, },
    include: [
      {
        model: models.Address,
        as: 'Address'
      },
      {
        model: models.Address,
        // through: models.Collaboration,
        as: 'collaborators'
      },
      models.OffchainAttachment,
      {
        model: models.OffchainTopic,
        as: 'topic'
      }
    ],
  });

  return res.json({ status: 'Success', result: finalThread.toJSON() });
}

/**
   * Statistic table contains all infomation of various type of scores of a build
   * Its stored in key - value model
   * The major issue with lighthouse table is, it uses associate keys rather than foreign keys
   * Thus you cannot use joins to obtains relational ones
   *              -------------------------------------------------
   * There is major two flows one due to search and other not
   * In search (by project name): first projects are fetched then corresponding stats and builds
   * When not using search first stats are taken then build -> projects
   */
  async getLeaderBoard({
    limit = 10,
    offset = 0,
    sort = 'DESC',
    type = 'overall',
    search,
    pickCategory = [],
  }: LeaderBoardOptions = {}) {
    // filters that gets added on based on conditions
    let group = [];

    const scoreFieldsDb = pickCategory?.length
      ? pickCategory
      : Object.keys(DB_SCORE_KEY_TO_LH_KEY);
    const isOveralCategory = type === 'overall';

    let projects: ProjectInstance[] = []; // due to two flows

    if (search) {
      // find projects and then find stats
      projects = await Project.findAll({
        raw: true,
        where: {
          name: { [Op.iLike]: `%${search}%` },
        },
        limit: 10,
      });

      if (!projects.length) {
        return { count: 0, rows: [] };
      }
    }

    /**
     * When category is overall we need to take avg of whole build stats
     * Other ones we need to pick it up key name
     */
    if (isOveralCategory) {
      group = ['projectId', 'branch'];
    } else {
      group = ['projectId', 'branch', 'name'];
    }

    /**
     * raw query is used to handle filtering after ranking as sub query is not feasible in orm
     * To avoid sql injection conditionally replacement values are placed and values are injected from replacement
     * Operations done
     * 1. Get field projectId, average * 100 and branch name as subquery from build table
     * 2. Group projectId, branch and name if not overall
     * 3. Only select specific categories of our interest PWA, Accessbility etc
     * 4. Order by Rank and createdAt as tie breaker
     * 5. Then keeping rank apply limit and offset projectId
     */
    const stats = (await sequelize.query(
      `SELECT * FROM (
          SELECT "projectId", ${this.getLeaderboardAvgField} * 100 AS "score",
          (DENSE_RANK() OVER (ORDER BY  ${
      this.getLeaderboardAvgField
      } DESC)) AS "rank"
          ${isOveralCategory ? '' : ', "name"'},
          (SELECT "branch" FROM "builds" WHERE "statistics"."buildId" = "builds"."id") AS "branch"
          FROM "statistics" AS "statistics"
          WHERE "statistics"."name" ${
      isOveralCategory ? 'IN(:name)' : '= :name'
      }
          GROUP BY "${group.join('","')}"
          ORDER BY ${this.getLeaderboardAvgField} ${
        sort === 'DESC' ? 'DESC' : 'ASC'
      },
          max("createdAt") ${sort === 'DESC' ? 'DESC' : 'ASC'}
        ) AS a
        ${projects.length ? 'WHERE "projectId" IN(:projectId)' : ''}
        LIMIT :limit OFFSET :offset`,
      {
        type: QueryTypes.SELECT,
        replacements: {
          name: isOveralCategory ? scoreFieldsDb : type,
          projectId: projects.map(({ id }) => id),
          limit,
          offset,
        },
        model: Statistic,
        raw: true,
      },
    )) as unknown as LeadboardStatistic[];

    const projectBranches = stats.map(({ projectId, branch }) => ({
      branchName: branch,
      projectId,
    }));

    /**
     * 1. Get by same grouping as rank tanle
     * 2. Then count the number of rows generated
     * 3. If search is done do it with projectId filter
     */
    const count = (await sequelize.query(
      `
    SELECT COUNT(*) FROM (
      SELECT "projectId",
      (SELECT "branch" FROM "builds" WHERE "statistics"."buildId" = "builds"."id") AS "branch"
      FROM "statistics"
      ${projects.length ? 'WHERE "projectId" IN(:projectId)' : ''}
      GROUP BY "projectId", "branch"
    )
    AS a`,
      {
        type: QueryTypes.SELECT,
        raw: true,
        replacements: {
          projectId: projects.map(({ id }) => id),
        },
      },
    )) as { count: string }[];

    if (!projectBranches.length) {
      return { count: count[0].count, rows: [] };
    }

    const leaderBoardScores = await this.getAllScoresOfProjectBranches(
      projectBranches,
    );

    const uniqueProjectIds: Record<string, boolean> = {};
    const projectIds = stats.reduce((ids, stat) => {
      if (!uniqueProjectIds?.[stat.projectId]) ids.push(stat.projectId);
      return ids;
    }, [] as string[]);

    // if not search flow find the projects
    if (!search) {
      projects = await Project.findAll({
        raw: true,
        where: {
          id: {
            [Op.in]: projectIds,
          },
        },
      });
    }

    const projectsGroupedById = groupBy(projects, 'id');

    stats.forEach((stat) => {
      stat.score = leaderBoardScores[`${stat.projectId}:${stat.branch}`];
      stat.project = projectsGroupedById[stat.projectId][0] as any;
    });

    return { count: count[0].count, rows: stats };
  }

getHof = async (req: Request, res: Response) => {
  try {
    const sm = await model.Character.findOne({
      where: { [Op.or]: [{ Class: 0 }, { Class: 1 }] },
      attributes: ['Name', 'Class', 'HOFWins'],
      order: [
        ['HOFWins', 'DESC'],
        ['Resets', 'DESC'],
        ['cLevel', 'DESC']
      ],
      include: [
        {
          model: model.MEMB_STAT,
          attributes: {
            exclude: ['memb___id']
          }
        },
        {
          model: model.AccountCharacter,
          attributes: {
            exclude: ['Id']
          }
        }
      ]
    });

    const bk = await model.Character.findOne({
      where: { [Op.or]: [{ Class: 16 }, { Class: 17 }] },
      attributes: ['Name', 'Class', 'HOFWins'],
      order: [
        ['HOFWins', 'DESC'],
        ['Resets', 'DESC'],
        ['cLevel', 'DESC']
      ],
      include: [
        {
          model: model.MEMB_STAT,
          attributes: {
            exclude: ['memb___id']
          }
        },
        {
          model: model.AccountCharacter,
          attributes: {
            exclude: ['Id']
          }
        }
      ]
    });

    const elf = await model.Character.findOne({
      where: { [Op.or]: [{ Class: 32 }, { Class: 33 }] },
      attributes: ['Name', 'Class', 'HOFWins'],
      order: [
        ['HOFWins', 'DESC'],
        ['Resets', 'DESC'],
        ['cLevel', 'DESC']
      ],
      include: [
        {
          model: model.MEMB_STAT,
          attributes: {
            exclude: ['memb___id']
          }
        },
        {
          model: model.AccountCharacter,
          attributes: {
            exclude: ['Id']
          }
        }
      ]
    });

    const mg = await model.Character.findOne({
      where: {
        Class: 48
      },
      attributes: ['Name', 'Class', 'HOFWins'],
      order: [
        ['HOFWins', 'DESC'],
        ['Resets', 'DESC'],
        ['cLevel', 'DESC']
      ],
      include: [
        {
          model: model.MEMB_STAT,
          attributes: {
            exclude: ['memb___id']
          }
        },
        {
          model: model.AccountCharacter,
          attributes: {
            exclude: ['Id']
          }
        }
      ]
    });

    const dl = await model.Character.findOne({
      where: {
        Class: 64
      },
      attributes: ['Name', 'Class', 'HOFWins'],
      order: [
        ['HOFWins', 'DESC'],
        ['Resets', 'DESC'],
        ['cLevel', 'DESC']
      ],
      include: [
        {
          model: model.MEMB_STAT,
          attributes: {
            exclude: ['memb___id']
          }
        },
        {
          model: model.AccountCharacter,
          attributes: {
            exclude: ['Id']
          }
        }
      ]
    });

    res.json(!sm && !bk && !elf && !mg && !dl ? false : [sm, bk, elf, mg, dl]);
  } catch (error) {
    logger.error({ error, res });
  }
}

editComment = async (models: DB, req: Request, res: Response, next: NextFunction) => {
  const [chain, error] = await validateChain(models, req.body);
  if (error) return next(new Error(error));
  const [author, authorError] = await lookupAddressIsOwnedByUser(models, req);
  if (authorError) return next(new Error(authorError));

  if (!req.body.id) {
    return next(new Error(Errors.NoId));
  }

  const attachFiles = async () => {
    if (req.body['attachments[]'] && typeof req.body['attachments[]'] === 'string') {
      await models.OffchainAttachment.create({
        attachable: 'comment',
        attachment_id: req.body.id,
        url: req.body['attachments[]'],
        description: 'image',
      });
    } else if (req.body['attachments[]']) {
      await Promise.all(req.body['attachments[]'].map((u) => models.OffchainAttachment.create({
        attachable: 'comment',
        attachment_id: req.body.id,
        url: u,
        description: 'image',
      })));
    }
  };

  try {
    const userOwnedAddressIds = (await req.user.getAddresses()).filter((addr) => !!addr.verified).map((addr) => addr.id);
    const comment = await models.OffchainComment.findOne({
      where: {
        id: req.body.id,
        address_id: { [Op.in]: userOwnedAddressIds },
      },
    });
    let latestVersion;
    try {
      latestVersion = JSON.parse(comment.version_history[0]).body;
    } catch (e) {
      console.log(e);
    }
    // If new comment body text has been submitted, create another version history entry
    if (decodeURIComponent(req.body.body) !== latestVersion) {
      const recentEdit = {
        timestamp: moment(),
        body: decodeURIComponent(req.body.body)
      };
      const arr = comment.version_history;
      arr.unshift(JSON.stringify(recentEdit));
      comment.version_history = arr;
    }
    comment.text = req.body.body;
    comment.plaintext = (() => {
      try {
        return renderQuillDeltaToText(JSON.parse(decodeURIComponent(req.body.body)));
      } catch (e) {
        return decodeURIComponent(req.body.body);
      }
    })();
    await comment.save();
    await attachFiles();
    const finalComment = await models.OffchainComment.findOne({
      where: { id: comment.id },
      include: [models.Address, models.OffchainAttachment],
    });
    // get thread for crafting commonwealth url
    let proposal;
    const [prefix, id] = comment.root_id.split('_');
    if (prefix === 'discussion') {
      proposal = await models.OffchainThread.findOne({
        where: { id }
      });
    } else if (prefix.includes('proposal') || prefix.includes('referendum') || prefix.includes('motion')) {
      // TODO: better check for on-chain proposal types
      proposal = id;
    } else {
      log.error(`No matching proposal of thread for root_id ${comment.root_id}`);
    }
    if (!proposal) {
      throw new Error(Errors.NoProposal);
    }

    const cwUrl = typeof proposal === 'string'
      ? getProposalUrlWithoutObject(prefix, comment.chain, proposal, finalComment)
      : getProposalUrl(prefix, proposal, comment);
    const root_title = typeof proposal === 'string' ? '' : (proposal.title || '');

    // dispatch notifications to subscribers of the comment/thread
    await models.Subscription.emitNotifications(
      models,
      NotificationCategories.CommentEdit,
      '',
      {
        created_at: new Date(),
        root_id: comment.root_id,
        root_title,
        root_type: prefix,
        comment_id: +finalComment.id,
        comment_text: finalComment.text,
        chain_id: finalComment.chain,
        author_address: finalComment.Address.address,
        author_chain: finalComment.Address.chain,
      },
      // don't send webhook notifications for edits
      {
        user: finalComment.Address.address,
        url: cwUrl,
        title: proposal.title || '',
        chain: finalComment.chain,
      },
      req.wss,
      [ finalComment.Address.address ],
    );

    let mentions;
    try {
      const previousDraftMentions = parseUserMentions(latestVersion);
      const currentDraftMentions = parseUserMentions(decodeURIComponent(req.body.body));
      mentions = currentDraftMentions.filter((addrArray) => {
        let alreadyExists = false;
        previousDraftMentions.forEach((addrArray_) => {
          if (addrArray[0] === addrArray_[0] && addrArray[1] === addrArray_[1]) {
            alreadyExists = true;
          }
        });
        return !alreadyExists;
      });
    } catch (e) {
      return next(new Error('Failed to parse mentions'));
    }

    // grab mentions to notify tagged users
    let mentionedAddresses;
    if (mentions?.length > 0) {
      mentionedAddresses = await Promise.all(mentions.map(async (mention) => {
        try {
          const user = await models.Address.findOne({
            where: {
              chain: mention[0],
              address: mention[1],
            },
            include: [ models.User, models.Role ]
          });
          return user;
        } catch (err) {
          return next(new Error(err));
        }
      }));
      // filter null results
      mentionedAddresses = mentionedAddresses.filter((addr) => !!addr);
    }

    // notify mentioned users, given permissions are in place
    if (mentionedAddresses?.length > 0) {
      await Promise.all(mentionedAddresses.map(async (mentionedAddress) => {
        if (!mentionedAddress.User) return; // some Addresses may be missing users, e.g. if the user removed the address
        await models.Subscription.emitNotifications(
          models,
          NotificationCategories.NewMention,
          `user-${mentionedAddress.User.id}`,
          {
            created_at: new Date(),
            root_id: +id,
            root_title,
            root_type: prefix,
            comment_id: +finalComment.id,
            comment_text: finalComment.text,
            chain_id: finalComment.chain,
            author_address: finalComment.Address.address,
            author_chain: finalComment.Address.chain,
          },
          {
            user: finalComment.Address.address,
            author_chain: finalComment.Address.chain,
            url: cwUrl,
            title: proposal.title || '',
            chain: finalComment.chain,
            body: finalComment.text,
          },
          req.wss,
          [ finalComment.Address.address ],
        );
      }));
    }

    // update author.last_active (no await)
    author.last_active = new Date();
    author.save();

    return res.json({ status: 'Success', result: finalComment.toJSON() });
  } catch (e) {
    return next(e);
  }
}

/*

mutation { import(
    config: {
        mode: CREATE_UPDATE
        errors: PROCESS_WARN
    },
    items: [
        {
            identifier: "itemSa1",
            parentIdentifier: "itemLevel1",
            typeIdentifier: "sa1",
            name: {ru:"Продукт1"},
            values: {
                attr1: "aaa"
                attr2: {ru: "test"}
            }
        }]
    ) {
    items {
	  identifier
	  result
	  id
	  errors { code message }
	  warnings { code message }
	}}}

*/

export async function importItem(context: Context, config: IImportConfig, item: IItemImportRequest): Promise<ImportResponse> {
    const result = new ImportResponse(item.identifier)

    if (!item.identifier || !/^[A-Za-z0-9_-]*$/.test(item.identifier)) {
        result.addError(ReturnMessage.WrongIdentifier)
        result.result = ImportResult.REJECTED
        return result
    }

    try {
        if (item.delete) {
            const data = await Item.applyScope(context).findOne({where: { identifier: item.identifier } })
            if (!data) {
                result.addError(ReturnMessage.ItemNotFound)
                result.result = ImportResult.REJECTED
            } else {
                if (!context.canEditItem(data)) {
                    result.addError(ReturnMessage.ItemNoAccess)
                    result.result = ImportResult.REJECTED
                }

                const mng = ModelsManager.getInstance().getModelManager(context.getCurrentUser()!.tenantId)
                // check Roles
                const tst1 = mng.getRoles().find(role => role.itemAccess.fromItems.includes(data.id))
                // check Attributes
                // const tst2 = await Attribute.applyScope(context).findOne({where: {visible: { [Op.contains]: data.id}}})
                const tst2:any = await Attribute.applyScope(context).findOne({where: literal("visible @> '"+data.id+"'") })
                if (tst1 || tst2) {
                    result.addError(ReturnMessage.ItemDeleteFailed)
                    result.result = ImportResult.REJECTED
                    return result
                }
                // check children
                const cnt:any = await sequelize.query('SELECT count(*) FROM items where "deletedAt" IS NULL and "tenantId"=:tenant and path~:lquery', {
                    replacements: { 
                        tenant: context.getCurrentUser()!.tenantId,
                        lquery: data.path + '.*{1}',
                    },
                    plain: true,
                    raw: true,
                    type: QueryTypes.SELECT
                })
                const childrenNumber = parseInt(cnt.count)
                if (childrenNumber > 0) {
                    result.addError(ReturnMessage.ItemDeleteFailedChildren)
                    result.result = ImportResult.REJECTED
                    return result
                }
                // check relations
                const num = await ItemRelation.applyScope(context).count({
                    where: {
                        [Op.or]: [{itemId: data.id}, {targetId: data.id}]
                    },
                })
                if (num > 0) {
                    result.addError(ReturnMessage.ItemDeleteFailedRelations)
                    result.result = ImportResult.REJECTED
                    return result
                }

                data.updatedBy = context.getCurrentUser()!.login

                if (!item.skipActions) await processItemActions(context, EventType.BeforeDelete, data, "", "", null, null, true)

                const oldIdentifier = item.identifier
                data.identifier = item.identifier + '_d_' + Date.now() 
                await sequelize.transaction(async (t) => {
                    await data.save({transaction: t})
                    await data.destroy({transaction: t})
                })

                if (!item.skipActions) await processItemActions(context, EventType.AfterDelete, data, "", "", null, null, true)

                if (audit.auditEnabled()) {
                    const itemChanges: ItemChanges = {
                        typeIdentifier: data.typeIdentifier,
                        parentIdentifier: data.parentIdentifier,
                        name: data.name,
                        values: data.values
                    }
                    audit.auditItem(ChangeType.DELETE, data.id, oldIdentifier, {deleted: itemChanges}, context.getCurrentUser()!.login, data.updatedAt)
                }
    
                result.result = ImportResult.DELETED
            }
            return result
        }

        let data: Item | null = await Item.applyScope(context).findOne({where: { identifier: item.identifier } })
        if (config.mode === ImportMode.CREATE_ONLY) {
            if (data) {
                result.addError(ReturnMessage.ItemExist)
                result.result = ImportResult.REJECTED
                return result
            }
        } else if (config.mode === ImportMode.UPDATE_ONLY) {
            if (!data) {
                result.addError(ReturnMessage.ItemNotFound)
                result.result = ImportResult.REJECTED
                return result
            }
        }

        const mng = ModelsManager.getInstance().getModelManager(context.getCurrentUser()!.tenantId)

        if (!data) {
            // create
            const type = checkType(item, result, mng)
            if (result.result) return result
    
            let parent = await checkParent(item, result, mng, context)
            if (result.result) return result
    
            const results:any = await sequelize.query("SELECT nextval('items_id_seq')", { 
                type: QueryTypes.SELECT
            });
            const id = (results[0]).nextval

            let path:string
            if (parent) {
                path = parent.path + "." + id
            } else {
                path = '' + id
            }
    
            if (!context.canEditItem2(type!.getValue().id, path)) {
                result.addError(ReturnMessage.ItemNoAccess)
                result.result = ImportResult.REJECTED
                return result
            }

            const data = await Item.build ({
                id: id,
                path: path,
                identifier: item.identifier,
                tenantId: context.getCurrentUser()!.tenantId,
                createdBy: context.getCurrentUser()!.login,
                updatedBy: context.getCurrentUser()!.login,
                name: item.name,
                typeId: type!.getValue().id,
                typeIdentifier: type!.getValue().identifier,
                parentIdentifier: parent ? parent.identifier : "",
                values: null,
                fileOrigName: '',
                storagePath: '',
                mimeType: ''
            })

            if (!item.values) item.values = {}
            if (!item.skipActions) await processItemActions(context, EventType.BeforeCreate, data, item.parentIdentifier, item.name, item.values, item.channels, true)

            filterEditChannels(context, item.channels)
            checkSubmit(context, item.channels)

            filterValues(context.getEditItemAttributes2(type!.getValue().id, path), item.values)
            try {
                checkValues(mng, item.values)
            } catch (err: any) {
                result.addError(new ReturnMessage(0, err.message))
                result.result = ImportResult.REJECTED
                return result
            }

            data.values = item.values
            data.channels = item.channels

            await sequelize.transaction(async (t) => {
                await data.save({transaction: t})
            })

            if (!item.skipActions) await processItemActions(context, EventType.AfterCreate, data, item.parentIdentifier, item.name, item.values, item.channels, true)

            if (audit.auditEnabled()) {
                const itemChanges: ItemChanges = {
                    typeIdentifier: data.typeIdentifier,
                    parentIdentifier: data.parentIdentifier,
                    name: data.name,
                    values: data.values
                }
                audit.auditItem(ChangeType.CREATE, data.id, item.identifier, {added: itemChanges}, context.getCurrentUser()!.login, data.createdAt)
            }

            result.id = ""+data.id
            result.result = ImportResult.CREATED
        } else {
            // update
            if ((item.name || item.values) && !context.canEditItem(data)) {
                result.addError(ReturnMessage.ItemNoAccess)
                result.result = ImportResult.REJECTED
                return result
            }

            let itemDiff: AuditItem = {added:{}, changed:{}, old:{}, deleted: {}}
            if (item.typeIdentifier) {
                const type = checkType(item, result, mng)
                if (result.result) return result
                
                if (data.typeId !== type!.getValue().id) {
                    if (audit.auditEnabled()) {
                        itemDiff.changed!.typeIdentifier = type!.getValue().identifier
                        itemDiff.old!.typeIdentifier = data.typeIdentifier
                    }
                    data.typeId = type!.getValue().id
                    data.typeIdentifier = type!.getValue().identifier
                }
            } else {
                item.typeIdentifier = data.typeIdentifier
            }

            if (!item.values) item.values = {}
            if (!item.skipActions) await processItemActions(context, EventType.BeforeUpdate, data, item.parentIdentifier, item.name, item.values, item.channels, true)

            if (item.parentIdentifier && data.parentIdentifier !== item.parentIdentifier) {
                let parent = await checkParent(item, result, mng, context)
                if (result.result) return result

                if (audit.auditEnabled()) {
                    itemDiff.changed!.parentIdentifier = item.parentIdentifier
                    itemDiff.old!.parentIdentifier = data.parentIdentifier
                }

                let newPath: string
                if (parent) {
                    newPath = parent.path+"."+data.id
                } else {
                    newPath = ""+data.id
                }
                if (newPath !== data.path) {
                    // check children
                    const cnt: any = await sequelize.query('SELECT count(*) FROM items where "deletedAt" IS NULL and "tenantId"=:tenant and path~:lquery', {
                        replacements: {
                            tenant: context.getCurrentUser()!.tenantId,
                            lquery: data.path + '.*{1}',
                        },
                        plain: true,
                        raw: true,
                        type: QueryTypes.SELECT
                    })
                    const childrenNumber = parseInt(cnt.count)
                    if (childrenNumber > 0) { //move subtree
                        await sequelize.query('update items set path = text2ltree(:parentPath) || subpath(path,:level) where path <@ :oldPath and "tenantId"=:tenant', {
                            replacements: { 
                                tenant: context.getCurrentUser()!.tenantId,
                                oldPath: data.path,
                                parentPath: parent ? parent.path : '',
                                level: data.path.split('.').length - 1
                            },
                            plain: true,
                            raw: true,
                            type: QueryTypes.UPDATE
                        })
                    } else { // move leaf
                        data.path = newPath
                    }
                    data.parentIdentifier = parent ? parent.identifier : ""
                }
            }

            if (item.name) {
                if (audit.auditEnabled()) {
                    const nameDiff: AuditItem = diff({name:data.name}, {name:item.name})
                    itemDiff.added = {...itemDiff.added, ...nameDiff.added}
                    itemDiff.changed = {...itemDiff.changed, ...nameDiff.changed}
                    itemDiff.old = {...itemDiff.old, ...nameDiff.old}
                }
                data.name = {...data.name, ...item.name}
            }

            filterEditChannels(context, item.channels)
            checkSubmit(context, item.channels)
            filterValues(context.getEditItemAttributes(data), item.values)
            try {
                checkValues(mng, item.values)
            } catch (err:any) {
                result.addError(new ReturnMessage(0, err.message))
                result.result = ImportResult.REJECTED
                return result
            }

            if (audit.auditEnabled()) {
                const valuesDiff: AuditItem = diff({values:data.values}, {values:item.values})
                itemDiff.added = {...itemDiff.added, ...valuesDiff.added}
                itemDiff.changed = {...itemDiff.changed, ...valuesDiff.changed}
                itemDiff.old = {...itemDiff.old, ...valuesDiff.old}
            }

            data.values = mergeValues(item.values, data.values)
            data.channels = mergeValues(item.channels, data.channels)

            data.updatedBy = context.getCurrentUser()!.login
            await sequelize.transaction(async (t) => {
                await data!.save({transaction: t})
            })

            if (!item.skipActions) await processItemActions(context, EventType.AfterUpdate, data, item.parentIdentifier, item.name, item.values, item.channels, true)

            if (audit.auditEnabled()) {
                if (!isObjectEmpty(itemDiff!.added) || !isObjectEmpty(itemDiff!.changed) || !isObjectEmpty(itemDiff!.deleted)) audit.auditItem(ChangeType.UPDATE, data.id, item.identifier, itemDiff!, context.getCurrentUser()!.login, data.updatedAt)
            }

            result.id = ""+data.id
            result.result = ImportResult.UPDATED
        }
    } catch (error) {
        result.addError(new ReturnMessage(0, ""+error))
        result.result = ImportResult.REJECTED
        logger.error(error)
    }
    return result
}

searchDiscussions = async (
  models: DB,
  req: Request,
  res: Response,
  next: NextFunction
) => {
  let bind = {};

  if (!req.query.search) {
    return next(new Error(Errors.QueryMissing));
  }
  if (req.query.search.length < 4) {
    return next(new Error(Errors.QueryTooShort));
  }

  if (req.query.thread_title_only === 'true') {
    if (!req.query.chain) {
      return next(new Error(Errors.NoChain));
    }
    const [chain, error] = await validateChain(models, req.query);
    if (error) return next(new Error(error));
    const encodedSearchTerm = encodeURIComponent(req.query.search);
    const params = {
      chain: chain.id,
      title: {
        [Op.or]: [
          { [Op.iLike]: `%${encodedSearchTerm}%` },
          { [Op.iLike]: `%${req.query.search}%` },
        ],
      },
    };

    try {
      const threads = await models.OffchainThread.findAll({
        where: params,
        limit: req.query.results_size || 20,
        attributes: {
          exclude: ['body', 'plaintext', 'version_history'],
        },
        include: [
          {
            model: models.Address,
            as: 'Address',
          },
        ],
      });
      return res.json({
        status: 'Success',
        result: threads,
      });
    } catch (e) {
      console.log(e);
      return next(new Error(Errors.UnexpectedError));
    }
  }

  // Community-scoped search
  let communityOptions = '';
  if (req.query.chain) {
    const [chain, error] = await validateChain(models, req.query);
    if (error) return next(new Error(error));

    // set up query parameters
    communityOptions = `AND "OffchainThreads".chain = $chain `;
    bind = { chain: chain.id };
  }

  const sort =
    req.query.sort === 'Newest'
      ? 'ORDER BY "OffchainThreads".created_at DESC'
      : req.query.sort === 'Oldest'
      ? 'ORDER BY "OffchainThreads".created_at ASC'
      : 'ORDER BY rank DESC';

  bind['searchTerm'] = req.query.search;
  bind['limit'] = 50; // must be same as SEARCH_PAGE_SIZE on frontend

  // query for both threads and comments, and then execute a union and keep only the most recent :limit
  let threadsAndComments;
  try {
    threadsAndComments = await models.sequelize.query(
      `
  SELECT
      "OffchainThreads".title,
      "OffchainThreads".body,
      CAST("OffchainThreads".id as VARCHAR) as proposalId,
      'thread' as type,
      "Addresses".id as address_id,
      "Addresses".address,
      "Addresses".chain as address_chain,
      "OffchainThreads".created_at,
      "OffchainThreads".chain,
      ts_rank_cd("OffchainThreads"._search, query) as rank
    FROM "OffchainThreads"
    JOIN "Addresses" ON "OffchainThreads".address_id = "Addresses".id, 
    websearch_to_tsquery('english', $searchTerm) as query
    WHERE query @@ "OffchainThreads"._search ${communityOptions} AND "OffchainThreads".deleted_at IS NULL
    ${sort} LIMIT $limit
`,
      {
        bind,
        type: QueryTypes.SELECT,
      }
    );
  } catch (e) {
    console.log(e);
    return next(new Error(Errors.UnexpectedError));
  }

  return res.json({
    status: 'Success',
    result: threadsAndComments,
  });
}