sequelize#QueryTypes TypeScript Examples

async function getGroupFirstSnapshots(playerIds: number[], startDate: Date, attributeSelector = '*') {
  const firstSnapshots: Snapshot[] = await sequelize.query(
    queries.FETCH_FIRST_SNAPSHOTS_IN_PERIOD_PLAYER_IDS(
      attributeSelector,
      playerIds.join(','),
      formatDate(startDate, 'YYYY-MM-DD HH:mm:ss')
    ),
    { type: QueryTypes.SELECT }
  );

  return firstSnapshots;
}

fetchReportPage = async (
    validatorStatsSql: string, 
    validatorStatsCountSql: string, 
    totalBlocksSql: string,
    firstBlockSql: string,
    lastBlockSql: string,
    ): Promise<IValidatorReport> => {

    const dbBlockStart = (await db.query<any>(firstBlockSql, opts)) // TODO <Block> instead of <any> produces an object with no get() function  
    const dbBlockEnd = (await db.query<any>(lastBlockSql, opts))
    const dbCount = (await db.query<ITotalCount>(validatorStatsCountSql, opts))
    const blockCount = (await db.query<ITotalBlockCount>(totalBlocksSql, opts))

    return db.query<IValidatorEraStats>(validatorStatsSql, {type: QueryTypes.SELECT}).then((stats: IValidatorEraStats[]) => {
        const validationReport: IValidatorReport = {
            pageSize: pageSize,
            totalCount: dbCount.totalCount,
            startBlock: dbBlockStart?.id,
            endBlock: dbBlockEnd?.id,
            startTime: dbBlockStart?.timestamp,
            endTime: dbBlockEnd?.timestamp,
            startEra: dbBlockStart?.eraId,
            endEra: dbBlockEnd?.eraId,
            totalBlocks: blockCount.totalBlocks | 0,
            report: stats
        }
        return validationReport
    })
}

async function getGroupLastSnapshots(playerIds: number[], endDate: Date, attributeSelector = '*') {
  const latestSnapshots: Snapshot[] = await sequelize.query(
    queries.FETCH_LAST_SNAPSHOTS_IN_PERIOD_PLAYER_IDS(
      attributeSelector,
      playerIds.join(','),
      formatDate(endDate, 'YYYY-MM-DD HH:mm:ss')
    ),
    { type: QueryTypes.SELECT }
  );

  return latestSnapshots;
}

/**
 * Gets the stats for every member of a group (latest snapshot)
 */
async function getMembersStats(groupId: number): Promise<Snapshot[]> {
  // Fetch all memberships for the group
  const memberships = await Membership.findAll({
    attributes: ['playerId'],
    where: { groupId }
  });

  if (!memberships || memberships.length === 0) {
    return [];
  }

  const memberIds = memberships.map(m => m.playerId);

  const query = `
    SELECT s.*
    FROM (SELECT q."playerId", MAX(q."createdAt") AS max_date
          FROM public.snapshots q
          WHERE q."playerId" IN (${memberIds.join(',')})
          GROUP BY q."playerId"
          ) r
    JOIN public.snapshots s
      ON s."playerId" = r."playerId" AND s."createdAt" = r.max_date
  `;

  // Execute the query above, which returns the latest snapshot for each member
  const latestSnapshots = await sequelize.query(query, { type: QueryTypes.SELECT });

  // Formats the snapshots to a playerId:snapshot map, for easier lookup
  const snapshotMap = mapValues(keyBy(latestSnapshots, 'playerId'));

  return memberships
    .filter(({ playerId }) => playerId in snapshotMap)
    .map(({ playerId }) => Snapshot.build({ ...snapshotMap[playerId] }));
}

deleteTopic = async (models: DB, req, res: Response, next: NextFunction) => {
  const [chain, error] = await validateChain(models, req.body);
  if (error) return next(new Error(error));
  if (!req.user) {
    return next(new Error(Errors.NotLoggedIn));
  }
  if (!req.body.id) {
    return next(new Error(Errors.NoTopicId));
  }
  if (req.body.featured_order && !req.user.isAdmin) {
    return next(new Error(Errors.NotAdmin));
  }

  const { id } = req.body;
  const topic = await models.OffchainTopic.findOne({ where: { id } });
  if (!topic) return next(new Error(Errors.TopicNotFound));

  const chainOrCommunity = 'chain = $chain';
  const bind = { chain: chain.id };
  bind['id'] = id;
  const query = `UPDATE "OffchainThreads" SET topic_id=null WHERE topic_id = $id AND ${chainOrCommunity};`;
  await models.sequelize.query(query, {
    bind,
    type: QueryTypes.UPDATE,
  });

  topic.destroy().then(() => {
    res.json({ status: 'Success' });
  }).catch((e) => {
    next(new Error(Errors.DeleteFail));
  });
}

resolver = {
    Query: {
        ping: () => {
            return 'pong'
        },
        nextId: async (parent: any, params: any, context: Context) => {
            context.checkAuth()
            const results:any = await sequelize.query("SELECT nextval('identifier_seq')", { 
                type: QueryTypes.SELECT
            });
            const id = (results[0]).nextval
            return id
        },
    }, 
    Mutation: {
        logLevel: async (parent: any, {level}: any, context: Context) => {
            context.checkAuth()    
            if (context.getCurrentUser()!.tenantId != '0' && !context.isAdmin()) {
                throw new Error('User '+ context.getCurrentUser()?.id+ ' does not has permissions to set log level, tenant: ' + context.getCurrentUser()!.tenantId)
            }

            logger.info('Setting log level to ' + level + ' by user: ' + context.getCurrentUser()?.login)

            logger.transports[0].level = level;
            return true
        }
    }
}

fetchUniqueAddressesByRootIds = async (
  models: DB,
  { chain, root_ids }
) => {
  const formattedIds = root_ids.map((root_id) => `${root_id}`);
  return sequelize.query<UniqueAddresses>(
    `
    SELECT distinct cts.address_id, address, root_id, cts.chain
    FROM "OffchainComments" cts INNER JOIN "Addresses" adr
    ON adr.id = cts.address_id
    WHERE root_id IN ($root_ids)
    AND cts.chain = $chain
    AND deleted_at IS NULL
    ORDER BY root_id
  `,
    {
      type: QueryTypes.SELECT,
      bind: {
        root_ids: formattedIds,
        chain,
      }
    }
  );
}

// same query as leaderboard but for a particule project's branch
  async getLHRankingOfAProjectBranch({
    projectId,
    branch,
    sort = 'DESC',
    type = 'overall',
  }: BuildLeaderboardRankOption) {
    let group = [];

    const isOveralCategory = type === 'overall';
    const scoreFieldsDb = Object.keys(DB_SCORE_KEY_TO_LH_KEY);

    if (isOveralCategory) {
      group = ['projectId', 'branch'];
    } else {
      group = ['projectId', 'branch', 'name'];
    }

    /**
     * Same as ranking. Only key difference is
     * Its done for a projectid and branch specifically
     */
    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
        where "projectId" = :projectId AND  "branch" = :branch
        LIMIT 1`,
      {
        type: QueryTypes.SELECT,
        replacements: {
          projectId,
          branch,
          name: isOveralCategory ? scoreFieldsDb : type,
        },
        model: Statistic,
        raw: true,
      },
    )) as unknown as LeadboardStatistic[];

    if (!stats.length) {
      throw Error('No stats found');
    }

    const leaderBoardScores = await this.getAllScoresOfProjectBranches([
      { branchName: branch, projectId },
    ]);

    const stat = stats[0];

    const project = await Project.findOne({
      raw: true,
      where: {
        id: projectId,
      },
    });

    if (!project) {
      throw Error('Project not found');
    }

    stat.project = project as any;
    stat.score = leaderBoardScores[`${projectId}:${branch}`];
    return stat;
  }

opts = {type: QueryTypes.SELECT, plain: true} as QueryOptionsWithType<QueryTypes.SELECT> & { plain: true }

/**
 * Gets the group hiscores for a specific metric.
 * All members which HAVE SNAPSHOTS will included and sorted by rank.
 */
async function getHiscores(groupId: number, metric: string, pagination: Pagination) {
  if (!metric || !METRICS.includes(metric as Metric)) {
    throw new BadRequestError(`Invalid metric: ${metric}.`);
  }

  // Fetch all memberships for the group
  const memberships = await Membership.findAll({
    attributes: ['playerId'],
    where: { groupId },
    include: [{ model: Player }]
  });

  if (!memberships || memberships.length === 0) {
    return [];
  }

  const valueKey = getMetricValueKey(metric as Metric);
  const rankKey = getMetricRankKey(metric as Metric);
  const measure = getMetricMeasure(metric as Metric);
  const memberIds = memberships.map(m => m.player.id);

  const query = `
    SELECT s.*
    FROM (SELECT q."playerId", MAX(q."createdAt") AS max_date
          FROM public.snapshots q
          WHERE q."playerId" IN (${memberIds.join(',')})
          GROUP BY q."playerId"
          ) r
    JOIN public.snapshots s
      ON s."playerId" = r."playerId" AND s."createdAt" = r.max_date
    ORDER BY s."${valueKey}" DESC
    LIMIT :limit
    OFFSET :offset
  `;

  // Execute the query above, which returns the latest snapshot for each member
  const latestSnapshots = await sequelize.query(query, {
    type: QueryTypes.SELECT,
    replacements: { ...pagination }
  });

  // Formats the experience snapshots to a key:value map.
  // Example: { '1623': { rank: 350567, experience: 6412215 } }
  const experienceMap = mapValues(keyBy(latestSnapshots, 'playerId'), d => {
    const data = {
      rank: parseInt(d[rankKey], 10),
      [measure]: parseInt(d[valueKey], 10)
    };

    if (isSkill(metric as Metric)) {
      data.level = metric === Metrics.OVERALL ? getTotalLevel(d as Snapshot) : getLevel(data.experience);
    }

    return data;
  });

  // Format all the members, add each experience to its respective player, and sort them by exp
  return memberships
    .filter(({ playerId }: any) => experienceMap[playerId] && experienceMap[playerId].rank > 0)
    .map(({ player }: any) => ({ player: player.toJSON(), ...experienceMap[player.id] }))
    .sort((a, b) => b[measure] - a[measure]);
}

public async createItem(parentIdentifier: string, typeIdentifier: string, identifier: string, name: any, values: any, skipActions = false) {
        if (!/^[A-Za-z0-9_-]*$/.test(identifier)) throw new Error('Identifier must not has spaces and must be in English only: ' + identifier + ', tenant: ' + this.#context.getCurrentUser()!.tenantId)

        const tst = await Item.applyScope(this.#context).findOne({
            where: {
                identifier: identifier
            }
        })
        if (tst) {
            throw new Error('Identifier: ' + identifier + ' already exists, tenant: ' + this.#context.getCurrentUser()!.tenantId)
        }

        const mng = ModelsManager.getInstance().getModelManager(this.#context.getCurrentUser()!.tenantId)
        const type = mng.getTypeByIdentifier(typeIdentifier)
        if (!type) {
            throw new Error('Failed to find type by identifier: ' + typeIdentifier + ', tenant: ' + mng.getTenantId())
        }
        const nTypeId = type.getValue()!.id;

        const results:any = await sequelize.query("SELECT nextval('items_id_seq')", { 
            type: QueryTypes.SELECT
        });
        const id = (results[0]).nextval
        
        let path:string
        if (parentIdentifier) {
            const parentItem = await Item.applyScope(this.#context).findOne({
                where: {
                    identifier: parentIdentifier
                }
            })
            if (!parentItem) {
                throw new Error('Failed to find parent item by identifier: ' + parentIdentifier + ', tenant: ' + this.#context.getCurrentUser()!.tenantId)
            }

            const parentType = mng.getTypeById(parentItem.typeId)!
            const tstType = parentType.getChildren().find(elem => (elem.getValue().id === nTypeId) || (elem.getValue().link === nTypeId))
            if (!tstType) {
                throw new Error('Failed to create item with type: ' + nTypeId + ' under type: ' + parentItem.typeId + ', tenant: ' + this.#context.getCurrentUser()!.tenantId)
            }

            parentIdentifier = parentItem.identifier
            path = parentItem.path + "." + id
        } else {
            const tstType = mng.getRoot().getChildren().find(elem => elem.getValue().id === nTypeId)
            if (!tstType) {
                throw new Error('Failed to create root item with type: ' + nTypeId + ', tenant: ' + this.#context.getCurrentUser()!.tenantId)
            }

            parentIdentifier = ''
            path = '' + id
        }

        if (!this.#context.canEditItem2(nTypeId, path)) {
            throw new Error('User :' + this.#context.getCurrentUser()?.login + ' can not create such item , tenant: ' + this.#context.getCurrentUser()!.tenantId)
        }

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

        if (!values) values = {}

        if (!skipActions) await processItemActions(this.#context, EventType.BeforeCreate, item, parentIdentifier, name, values, {}, false)

        filterValues(this.#context.getEditItemAttributes2(nTypeId, path), values)
        checkValues(mng, values)

        item.values = values

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

        if (!skipActions) await processItemActions(this.#context, EventType.AfterCreate, item, parentIdentifier, name, values, {}, false)

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

        return makeItemProxy(item)
    }

searchComments = 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));
  }

  // 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 "OffchainComments".chain = $chain `;
    bind = { chain: chain.id };
  }

  const sort =
    req.query.sort === 'Newest'
      ? 'ORDER BY "OffchainComments".created_at DESC'
      : req.query.sort === 'Oldest'
      ? 'ORDER BY "OffchainComments".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 comments;
  try {
    comments = await models.sequelize.query(
      `
  SELECT
      "OffchainThreads".title,
      "OffchainComments".text,
      "OffchainComments".root_id as proposalId,
      'comment' as type,
      "Addresses".id as address_id,
      "Addresses".address,
      "Addresses".chain as address_chain,
      "OffchainComments".created_at,
      "OffchainThreads".chain,
      ts_rank_cd("OffchainComments"._search, query) as rank
    FROM "OffchainComments"
    JOIN "OffchainThreads" ON "OffchainThreads".id =
        CASE WHEN root_id ~ '^discussion_[0-9\\.]+$' THEN CAST(REPLACE(root_id, 'discussion_', '') AS int) ELSE NULL END
    JOIN "Addresses" ON "OffchainComments".address_id = "Addresses".id, 
    websearch_to_tsquery('english', $searchTerm) as query
    WHERE query @@ "OffchainComments"._search ${communityOptions} AND "OffchainComments".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: comments,
  });
}

async getAllScoresOfProjectBranches(
    data: Array<{ branchName: string; projectId: string }>,
  ) {
    const projectIds = data.map(({ projectId: id }) => id);
    const branches = data.map(({ branchName: branch }) => branch);

    /**
     * 1. Get all other variations of a project branch average score same Grouping as ranking
     * 2. Filter only needed branch, projects and values
     */
    const leaderboardOtherScores = (await sequelize.query(
      `SELECT * FROM (
          SELECT "projectId", "name",
          ${this.getLeaderboardAvgField} * 100 AS "value",
          (SELECT "branch" FROM "builds" WHERE "statistics"."buildId" = "builds"."id") AS "branch"
            FROM "statistics" AS "statistics"
            WHERE "statistics"."name" IN(:name) AND
            "statistics"."projectId" IN(:projectId)
            GROUP BY "projectId", "branch", "name"
        ) AS a
        WHERE "branch" IN(:branch)`,
      {
        type: QueryTypes.SELECT,
        replacements: {
          name: Object.keys(DB_SCORE_KEY_TO_LH_KEY),
          projectId: projectIds,
          branch: branches,
        },
        model: Statistic,
        raw: true,
      },
    )) as Array<StatisticInstance & { branch: string }>;

    return leaderboardOtherScores.reduce<Record<string, LighthouseScoreType>>(
      (prev, {
        branch: branchName, value, name, projectId,
      }) => {
        const buildScores = prev;
        const key = `${projectId}:${branchName}`;
        const roundedValue = Math.min(value, 100);
        if (key in buildScores) {
          buildScores[key][DB_SCORE_KEY_TO_LH_KEY[name]] = roundedValue;
        } else {
          buildScores[key] = {
            [DB_SCORE_KEY_TO_LH_KEY[name]]: roundedValue,
          } as LighthouseScoreType;
        }
        return buildScores;
      },
      {},
    );
  }

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,
  });
}

/*
mutation { import(
    config: {
        mode: CREATE_UPDATE
        errors: PROCESS_WARN
    },
    types: [
        {
            delete: false
            identifier: "tst",
            parentIdentifier: "",
            name: {ru: "test type2"}
            icon: "folder",
            iconColor: "red"
        }
    ]
    ) {
    types {
	  identifier
	  result
	  id
	  errors { code message }
	  warnings { code message }
    }}}
    
*/
export async function importType(context: Context, config: IImportConfig, type: ITypeImportRequest): Promise<ImportResponse> {
    const result = new ImportResponse(type.identifier)

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

    try {
        const mng = ModelsManager.getInstance().getModelManager(context.getCurrentUser()!.tenantId)
        if (type.delete) {
            const typeNode = mng.getTypeByIdentifier(type.identifier)
            if (!typeNode) {
                result.addError(ReturnMessage.TypeNotFound)
                result.result = ImportResult.REJECTED
            } else {
                if (typeNode.getChildren().length > 0) {
                    result.addError(ReturnMessage.TypeDeleteFailed)
                    result.result = ImportResult.REJECTED
                } else {
                    const parentNode = typeNode.getParent()!
                    parentNode.deleteChild(typeNode)
        
                    const type:Type = typeNode.getValue()

                    const nId = type.id
                    // check Roles
                    const tst4 = mng.getRoles().find(role => role.itemAccess.valid.includes(nId))
                    // check Relations
                    //const tst3 = await Relation.applyScope(context).findOne({
                    //    where: {[Op.or]: [{sources: { [Op.contains]: nId}}, {targets: { [Op.contains]: nId}}]}
                    //})
                    const tst3 = await Relation.applyScope(context).findOne({
                        where: {[Op.or]: [literal("sources @> '"+nId+"'"), literal("targets @> '"+nId+"'")]}
                    })
                    // check Attributes
                    // const tst2 = await Attribute.applyScope(context).findOne({where: {valid: { [Op.contains]: nId}}})
                    const tst2 = await Attribute.applyScope(context).findOne({where: literal("valid @> '"+nId+"'")})
                    // check Items
                    const tst1 = await Item.applyScope(context).findOne({where: {typeId: nId}})
                    // check Linked types
                    const tst5 = mng.getTypeByLinkId(nId)

                    if (tst1 || tst2 || tst3 || tst4 || tst5) {
                        result.addError(ReturnMessage.TypeCanNotDelete)
                        result.result = ImportResult.REJECTED
                        return result
                    }

                    type.updatedBy = context.getCurrentUser()!.login
                    type.identifier = type.identifier + '_d_' + Date.now() 
                    await sequelize.transaction(async (t) => {
                        await type.save({transaction: t})
                        await type.destroy({transaction: t})
                    })
        
                    result.result = ImportResult.DELETED
                }
            }
            return result
        }

        const typeNode = mng.getTypeByIdentifier(type.identifier)
        if (config.mode === ImportMode.CREATE_ONLY) {
            if (typeNode) {
                result.addError(ReturnMessage.TypeExist)
                result.result = ImportResult.REJECTED
                return result
            }
        } else if (config.mode === ImportMode.UPDATE_ONLY) {
            if (!typeNode) {
                result.addError(ReturnMessage.TypeNotFound)
                result.result = ImportResult.REJECTED
                return result
            }
        }        

        if (!typeNode) {
            // create
            let parentNode: TreeNode<any> | null = null
            if (type.parentIdentifier) {
                parentNode = mng.getTypeByIdentifier(type.parentIdentifier)
                if (!parentNode) {
                    result.addError(ReturnMessage.TypeParentNotFound)
                    result.result = ImportResult.REJECTED
                    return result
                }
            }

            let link:Type
            if (type.linkIdentifier) {
                link = mng.getTypeByIdentifier(type.linkIdentifier)?.getValue()
                if (!link) {
                    result.addError(ReturnMessage.TypeLinkNotFound)
                    result.result = ImportResult.REJECTED
                    return result
                }
            }

            const results:any = await sequelize.query("SELECT nextval('types_id_seq')", { 
                type: QueryTypes.SELECT
            });
            const newId = (results[0]).nextval

            let path = '' + newId
            let parentId = null
            if (parentNode) {
                parentId = parentNode?.getValue().id
                while(parentNode != mng.getRoot()) {
                    path = parentNode!.getValue().id + '.' + path
                    parentNode = parentNode!.getParent()
                }
            }

            let images = checkRelations(type, mng, result)
            if (result.result) return result

            let mainImageId = 0
            if (type.mainImage) {
                const tst = mng.getRelationByIdentifier(type.mainImage)
                if (!tst) {
                    result.addWarning(ReturnMessage.RelationNotFound)
                } else {
                    mainImageId = tst.id;
                }
            }

            const data = await sequelize.transaction(async (t) => {
                return await Type.create ({
                    id: newId,
                    path: path,
                    identifier: type.identifier,
                    icon: type.icon,
                    iconColor: type.iconColor,
                    tenantId: context.getCurrentUser()!.tenantId,
                    createdBy: context.getCurrentUser()!.login,
                    updatedBy: context.getCurrentUser()!.login,
                    name: type.name || null,
                    link: link ? link.id : 0,
                    file: type.file != null ? type.file : false,
                    mainImage: mainImageId,
                    images: images,
                    options: type.options ?  type.options : []
                }, {transaction: t})
            })
            mng.addType(parentId, data)

            result.id = ""+data.id
            result.result = ImportResult.CREATED
        } else {
            // update
            if (type.parentIdentifier) {
                result.addWarning(ReturnMessage.TypeUpdateParent);
                if (config.errors == ErrorProcessing.WARN_REJECTED) {
                    result.result = ImportResult.REJECTED
                    return result
                }
            }

            if (type.linkIdentifier) {
                result.addWarning(ReturnMessage.TypeUpdateLink);
                if (config.errors == ErrorProcessing.WARN_REJECTED) {
                    result.result = ImportResult.REJECTED
                    return result
                }
            }

            const data: Type = typeNode.getValue()
            if (type.name) data.name = type.name
            if (type.icon) data.icon = type.icon
            if (type.iconColor) data.iconColor = type.iconColor
            if (type.file != null) data.file = type.file

            if (type.images) {
                data.images = checkRelations(type, mng, result)
                if (result.result) return result
            }

            if (type.mainImage) {
                const tst = mng.getRelationByIdentifier(type.mainImage)
                if (!tst) {
                    result.addWarning(ReturnMessage.RelationNotFound)
                } else {
                    data.mainImage = tst.id;
                }
            }
            if (type.options != null) data.options = type.options

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

            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
}

/*

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
}

export async function processCreateUpload(context: Context, req: Request, res: Response) {
    const form = new IncomingForm({maxFileSize: 500*1024*1024, keepExtensions: true})
 
    form.parse(req, async (err, fields, files) => {
        try {
            if (err) {
                logger.error(err)
                res.status(400).send(err)
                return
            }
            context.checkAuth();

            // file, fileItemTypeId, parentId, relationId
            const file = <File>files['file']
            const itemIdStr =  <string>fields['itemId']
            const fileItemTypeIdStr =  <string>fields['fileItemTypeId']
            const parentIdStr =  <string>fields['parentId']
            const relationIdStr =  <string>fields['relationId']
            const lang = <string>fields['lang']
            const fileName = <string>fields['fileName']
            const fileIdentifier = <string>fields['fileIdentifier']

            if (!file) throw new Error('Failed to find "file" parameter')
            if (!itemIdStr) throw new Error('Failed to find "itemId" parameter')
            if (!fileItemTypeIdStr) throw new Error('Failed to find "fileItemTypeId" parameter')
            if (!parentIdStr) throw new Error('Failed to find "parentId" parameter')
            if (!relationIdStr) throw new Error('Failed to find "relationId" parameter')
            if (!lang) throw new Error('Failed to find "lang" parameter')
            if (!fileName) throw new Error('Failed to find "fileName" parameter')
            if (!fileIdentifier) throw new Error('Failed to find "fileIdentifier" parameter')

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

            // *** create file item ***
            const tmp = mng.getTypeById(parseInt(fileItemTypeIdStr))
            if (!tmp) throw new Error('Failed to find type by id: ' + fileItemTypeIdStr)
            const fileItemType = <Type>tmp!.getValue()

            // TODO: do we need to check if we have such item already?
            const fileItemIdent = fileIdentifier

            let results:any = await sequelize.query("SELECT nextval('items_id_seq')", { 
                type: QueryTypes.SELECT
            });
            const id = (results[0]).nextval
            
            let path:string
            let parentIdentifier:string
            const pId = parseInt(parentIdStr)
            const parentItem = await Item.applyScope(context).findByPk(pId)
            if (!parentItem) {
                throw new Error('Failed to find parent item by id: ' + parentIdStr + ', tenant: ' + context.getCurrentUser()!.tenantId)
            }
            const parentType = mng.getTypeById(parentItem.typeId)!
            const tstType = parentType.getChildren().find(elem => (elem.getValue().id === fileItemType.id) || (elem.getValue().link === fileItemType.id))
            if (!tstType) {
                throw new Error('Failed to create item with type: ' + fileItemType.id + ' under type: ' + parentItem.typeId + ', tenant: ' + context.getCurrentUser()!.tenantId)
            }
            parentIdentifier = parentItem.identifier
            path = parentItem.path + "." + id
            if (!context.canEditItem2(fileItemType.id, path)) {
                throw new Error('User :' + context.getCurrentUser()?.login + ' can not create such item , tenant: ' + context.getCurrentUser()!.tenantId)
            }
            const name:any = {}
            name[lang] = fileName || file.originalFilename || ''

            const item:Item = Item.build ({
                id: id,
                path: path,
                identifier: fileItemIdent,
                tenantId: context.getCurrentUser()!.tenantId,
                createdBy: context.getCurrentUser()!.login,
                updatedBy: context.getCurrentUser()!.login,
                name: name,
                typeId: fileItemType.id,
                typeIdentifier: fileItemType.identifier,
                parentIdentifier: parentIdentifier, 
                values: {},
                channels: {},
                fileOrigName: '',
                storagePath: '',
                mimeType: ''
            })

            // *** upload file ***
            const type = mng.getTypeById(item.typeId)?.getValue()
            if (!type!.file) throw new Error('Item with id: ' + id + ' is not a file, user: ' + context.getCurrentUser()!.login + ", tenant: " + context.getCurrentUser()!.tenantId)

            const fm = FileManager.getInstance()
            await fm.saveFile(context.getCurrentUser()!.tenantId, item, file.filepath, file.mimetype, file.originalFilename)

            item.fileOrigName = file.originalFilename || ''
            item.mimeType = file.mimetype || ''

            const values = {}
            await processItemActions(context, EventType.BeforeCreate, item, parentIdentifier, name, values, item.channels, false)
            checkValues(mng, values)
            item.values = mergeValues(values, item.values)        
            item.name = name

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

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

            await processItemActions(context, EventType.AfterCreate, item, item.parentIdentifier, item.name, item.values, item.channels, false)

            if (audit.auditEnabled()) {
                const itemChanges: AuditItem = {
                    added: {
                        mimeType: file.mimetype || '',
                        fileOrigName: file.originalFilename || ''
                    }
                }
                audit.auditItem(ChangeType.CREATE, item.id, item.identifier, itemChanges, context.getCurrentUser()!.login, item.updatedAt)
            }


            // *** create link to item ***
            const rel = mng.getRelationById(parseInt(relationIdStr))
            if (!rel) throw new Error('Failed to find relation by id: ' + relationIdStr)

            if (!context.canEditItemRelation(rel.id)) {
                throw new Error('User :' + context.getCurrentUser()?.login + ' can not edit item relation:' + rel.id + ', tenant: ' + context.getCurrentUser()!.tenantId)
            }

            const nItemId = parseInt(itemIdStr)
            const source = await Item.applyScope(context).findByPk(nItemId)
            if (!source) {
                throw new Error('Failed to find item by id: ' + itemIdStr + ', tenant: ' + context.getCurrentUser()!.tenantId)
            }

            const relIdent = source.identifier + "_" + fileItemIdent

            const tst3 = rel.targets.find((typeId: number) => typeId === item.typeId)
            if (!tst3) {
                throw new Error('Relation with id: ' + rel.id + ' can not have target with type: ' + item.typeId + ', tenant: ' + mng.getTenantId())
            }

            if (!rel.multi) {
                const count = await ItemRelation.applyScope(context).count( {
                    where: {
                        itemId: nItemId,
                        relationId: rel.id
                    }
                })

                if (count > 0) {
                    throw new Error('Relation with id: ' + rel.id + ' can not have more then one target, tenant: ' + mng.getTenantId())
                }
            }

            const itemRelation = await ItemRelation.build ({
                identifier: relIdent,
                tenantId: context.getCurrentUser()!.tenantId,
                createdBy: context.getCurrentUser()!.login,
                updatedBy: context.getCurrentUser()!.login,
                relationId: rel.id,
                relationIdentifier: rel.identifier,
                itemId: nItemId,
                itemIdentifier: source.identifier,
                targetId: item.id,
                targetIdentifier: item.identifier,
                values: {}
            })

            const irValues = {}
            await processItemRelationActions(context, EventType.BeforeCreate, itemRelation, irValues, false)

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

            if (irValues) {
                filterValues(context.getEditItemRelationAttributes(itemRelation.relationId), irValues)
                checkValues(mng, irValues)

                itemRelation.values = irValues
            }

            if (audit.auditEnabled()) {
                const itemRelationChanges: ItemRelationChanges = {
                    relationIdentifier: itemRelation.relationIdentifier,
                    itemIdentifier: itemRelation.itemIdentifier,
                    targetIdentifier: itemRelation.targetIdentifier,
                    values: itemRelation.values
                }
                audit.auditItemRelation(ChangeType.CREATE, itemRelation.id, itemRelation.identifier, {added: itemRelationChanges}, context.getCurrentUser()!.login, itemRelation.createdAt)
            }

            await processItemRelationActions(context, EventType.AfterUpdate, itemRelation, itemRelation.values, false)


            res.send('OK')
        } catch (error: any) {
            logger.error(error)
            res.status(400).send(error.message)
        }
    });
}

status = async (
  models: DB,
  req: Request,
  res: Response,
  next: NextFunction
) => {
  try {
    const [
      chains,
      nodes,
      contractCategories,
      notificationCategories,
      chainCategories,
      chainCategoryTypes,
    ] = await Promise.all([
      models.Chain.findAll({
        where: { active: true },
        include: [
          {
            model: models.OffchainTopic,
            as: 'topics',
          },
          {
            model: models.ChainNode,
          }
        ],
      }),
      models.ChainNode.findAll(),
      models.ContractCategory.findAll(),
      models.NotificationCategory.findAll(),
      models.ChainCategory.findAll(),
      models.ChainCategoryType.findAll(),
    ]);

    const thirtyDaysAgo = new Date(
      (new Date() as any) - 1000 * 24 * 60 * 60 * 30
    );
    const { user } = req;
    type ThreadCountQueryData = {
      concat: string;
      count: number;
    };

    if (!user) {
      const threadCountQueryData: ThreadCountQueryData[] =
        await models.sequelize.query(
          `
        SELECT "OffchainThreads".chain, COUNT("OffchainThreads".id) 
        FROM "OffchainThreads"
        WHERE "OffchainThreads".deleted_at IS NULL
        AND NOT "OffchainThreads".pinned
        AND "OffchainThreads".chain IS NOT NULL
        GROUP BY "OffchainThreads".chain;
        `,
          { replacements: { thirtyDaysAgo }, type: QueryTypes.SELECT }
        );

      return res.json({
        chains,
        nodes,
        contractCategories,
        notificationCategories,
        chainCategories,
        chainCategoryTypes,
        recentThreads: threadCountQueryData,
        loggedIn: false,
      });
    }

    const unfilteredAddresses = await user.getAddresses();
    // TODO: fetch all this data with a single query
    const [
      addresses,
      socialAccounts,
      selectedChain,
      isAdmin,
      disableRichText,
      lastVisited,
    ] = await Promise.all([
      unfilteredAddresses.filter((address) => !!address.verified),
      user.getSocialAccounts(),
      user.getSelectedChain(),
      user.isAdmin,
      user.disableRichText,
      user.lastVisited,
    ]);

    // look up my roles & private communities
    const myAddressIds: number[] = Array.from(
      addresses.map((address) => address.id)
    );
    const roles = await models.Role.findAll({
      where: {
        address_id: { [Op.in]: myAddressIds },
      },
      include: [models.Address],
    });
    const discussionDrafts = await models.DiscussionDraft.findAll({
      where: {
        address_id: { [Op.in]: myAddressIds },
      },
      include: [models.Address, models.OffchainAttachment],
    });

    const threadCountQueryData: ThreadCountQueryData[] =
      await models.sequelize.query(
        `
      SELECT "OffchainThreads".chain, COUNT("OffchainThreads".id) 
      FROM "OffchainThreads"
      WHERE 
        "OffchainThreads".deleted_at IS NULL
          AND NOT "OffchainThreads".pinned
          AND "OffchainThreads".chain IS NOT NULL
      GROUP BY "OffchainThreads".chain;
      `,
        {
          replacements: {
            thirtyDaysAgo,
          },
          type: QueryTypes.SELECT,
        }
      );

    // get starred communities for user
    const starredCommunities = await models.StarredCommunity.findAll({
      where: { user_id: user.id },
    });

    // get invites for user
    const invites = await models.InviteCode.findAll({
      where: {
        invited_email: user.email,
        used: false,
      },
    });

    // TODO: Remove or guard JSON.parse calls since these could break the route if there was an error
    /**
     * Purpose of this section is to count the number of threads that have new updates grouped by community
     */
    const commsAndChains = Object.entries(JSON.parse(user.lastVisited));
    const unseenPosts = {};
    let query = ``;
    let replacements = [];

    // this loops through the communities/chains for which we want to see if there are any new updates
    // for each community a UNION SELECT query is appended to the query so that that communities updated threads are
    // included in the final result. This method allows us to submit a single query for all the communities rather
    // than a new query for each community
    for (let i = 0; i < commsAndChains.length; i++) {
      const name = commsAndChains[i][0];
      let time: any = commsAndChains[i][1];
      time = new Date(time as string)

      // if time is invalid reset + skip this chain
      if (Number.isNaN(time.getDate())) {
        unseenPosts[name] = {};
        continue;
      }

      // adds a union between SELECT queries if the number of SELECT queries is greater than 1
      if (i != 0) query += ' UNION '
      // add the chain and timestamp to replacements so that we can safely populate the query with dynamic parameters
      replacements.push(name, time.getTime());
      // append the SELECT query
      query += `SELECT id, chain FROM "OffchainThreads" WHERE (kind IN ('forum', 'link') OR chain = ?) AND created_at > TO_TIMESTAMP(?)`
      if (i == commsAndChains.length - 1) query += ';';
    }

    // populate the query replacements and execute the query
    const threadNum: {id: string, chain: string}[] = <any>(await sequelize.query(query, {
      raw: true, type: QueryTypes.SELECT, replacements
    }));

    // this section iterates through the retrieved threads counting the number of threads and keeping a set of activePosts
    // the set of activePosts is used to compare with the comments under threads so that there are no duplicate active threads counted
    for (const thread of threadNum) {
      if (!unseenPosts[thread.chain]) unseenPosts[thread.chain] = {}
      unseenPosts[thread.chain].activePosts ? unseenPosts[thread.chain].activePosts.add(thread.id) : unseenPosts[thread.chain].activePosts = new Set(thread.id);
      unseenPosts[thread.chain].threads ? unseenPosts[thread.chain].threads++ : unseenPosts[thread.chain].threads = 1;
    }

    // reset var
    query = ``;
    replacements = []

    // same principal as the loop above but for comments instead of threads
    for (let i = 0; i < commsAndChains.length; i++) {
      const name = commsAndChains[i][0];
      let time: any = commsAndChains[i][1];
      time = new Date(time as string)

      // if time is invalid reset + skip this chain
      if (Number.isNaN(time.getDate())) {
        unseenPosts[name] = {};
        continue;
      }

      // adds a union between SELECT queries if the number of SELECT queries is greater than 1
      if (i != 0) query += ' UNION ';
      // add the chain and timestamp to replacements so that we can safely populate the query with dynamic parameters
      replacements.push(name, time.getTime())
      // append the SELECT query
      query += `SELECT root_id, chain FROM "OffchainComments" WHERE chain = ? AND created_at > TO_TIMESTAMP(?)`
      if (i == commsAndChains.length - 1) query += ';';
    }

    // populate query and execute
    const commentNum: {root_id: string, chain: string}[] = <any>(await sequelize.query(query, {
      raw: true, type: QueryTypes.SELECT, replacements
    }));

    // iterates through the retrieved comments and adds each thread id to the activePosts set
    for (const comment of commentNum) {
      if (!unseenPosts[comment.chain]) unseenPosts[comment.chain] = {}
      // extract the thread id from the comments root id
      const id = comment.root_id.split('_')[1];
      unseenPosts[comment.chain].activePosts ? unseenPosts[comment.chain].activePosts.add(id) : unseenPosts[comment.chain].activePosts = new Set(id);
      unseenPosts[comment.chain].comments ? unseenPosts[comment.chain].comments++ : unseenPosts[comment.chain].comments = 1;
    }

    // set the activePosts to num in set
    for (const chain of commsAndChains) {
      // again checks for invalid time values
      const [name, time] = chain;
      if (Number.isNaN(new Date(time as string).getDate())) {
        unseenPosts[name] = {};
        continue;
      }
      // if the time is valid but the chain is not defined in the unseenPosts object then initialize the object with zeros
      if (!unseenPosts[name]) {
        unseenPosts[name] = {
          activePosts: 0,
          threads: 0,
          comments: 0
        }
      } else {
        // if the chain does have activePosts convert the set of ids to simply the length of the set
        unseenPosts[name].activePosts = unseenPosts[name].activePosts.size;
      }
    }
    /**
     * Example Result:
     * {
     *     ethereum: {
     *         activePosts: 10,
     *         threads: 8,
     *         comments: 2
     *     },
     *     aave: {
     *         ...
     *     }
     * }
     */

    const jwtToken = jwt.sign({ id: user.id, email: user.email }, JWT_SECRET);
    return res.json({
      chains,
      nodes,
      contractCategories,
      notificationCategories,
      chainCategories,
      chainCategoryTypes,
      recentThreads: threadCountQueryData,
      roles,
      invites,
      loggedIn: true,
      user: {
        email: user.email,
        emailVerified: user.emailVerified,
        emailInterval: user.emailNotificationInterval,
        jwt: jwtToken,
        addresses,
        socialAccounts,
        selectedChain,
        isAdmin,
        disableRichText,
        lastVisited: JSON.parse(lastVisited),
        starredCommunities,
        discussionDrafts,
        unseenPosts,
      },
    });
  } catch (error) {
    console.log(error);
    throw new ServerError('something broke', error);
  }
}

deleteChain = async (models: DB, req: Request, res: Response, next: NextFunction) => {
  if (!req.user) {
    return next(new Error(Errors.NotLoggedIn));
  }
  if (!req.user.isAdmin) {
    return next(new Error(Errors.NotAdmin));
  }
  if (!req.body.id) {
    return next(new Error(Errors.NeedChainId));
  }
  if (!['[email protected]', '[email protected]', '[email protected]'].includes(req.user.email)) {
    return next(new Error(Errors.NotAcceptableAdmin));
  }

  await models.sequelize.transaction(async (t) => {
    const chain = await models.Chain.findOne({
      where: {
        id: req.body.id,
      }
    });
    if (!chain) {
      return next(new Error(Errors.NoChain));
    }

    await models.sequelize.query(`DELETE FROM "ChainNodes" WHERE chain='${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    await models.sequelize.query(`DELETE FROM "ChainEntities" WHERE chain='${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    await models.sequelize.query(`DELETE FROM "OffchainReactions" WHERE chain='${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    await models.sequelize.query(`DELETE FROM "OffchainComments" WHERE chain='${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    await models.sequelize.query(`DELETE FROM "OffchainTopics" WHERE chain_id='${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    await models.sequelize.query(`DELETE FROM "Roles" WHERE chain_id='${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    await models.sequelize.query(`DELETE FROM "InviteCodes" WHERE chain_id='${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    await models.sequelize.query(`DELETE FROM "Subscriptions" WHERE chain_id='${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    await models.sequelize.query(`DELETE FROM "Webhooks" WHERE chain_id='${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    await models.sequelize.query(`DELETE FROM "Collaborations"
        USING "Collaborations" AS c
        LEFT JOIN "OffchainThreads" t ON offchain_thread_id = t.id
        WHERE t.chain = '${chain.id}'
        AND c.offchain_thread_id  = "Collaborations".offchain_thread_id 
        AND c.address_id = "Collaborations".address_id;`, {
      raw: true,
      type: QueryTypes.DELETE,
      transaction: t,
    });

    await models.sequelize.query(`DELETE FROM "LinkedThreads"
        USING "LinkedThreads" AS l
        LEFT JOIN "OffchainThreads" t ON linked_thread = t.id
        WHERE t.chain = '${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    await models.sequelize.query(`DELETE FROM "OffchainThreads" WHERE chain='${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    await models.sequelize.query(`DELETE FROM "StarredCommunities" WHERE chain='${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    await models.sequelize.query(`DELETE FROM "OffchainProfiles" AS profilesGettingDeleted
        USING "OffchainProfiles" AS profilesBeingUsedAsReferences
        LEFT JOIN "Addresses" a ON profilesBeingUsedAsReferences.address_id = a.id
        WHERE a.chain = '${chain.id}'
        AND profilesGettingDeleted.address_id  = profilesBeingUsedAsReferences.address_id;`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    await models.sequelize.query(`DELETE FROM "ChainEventTypes" WHERE chain='${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    // notifications + notifications_read (cascade)
    await models.sequelize.query(`DELETE FROM "Notifications" WHERE chain_id='${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    // TODO: Remove this once we figure out a better way to relate addresses across many chains (token communities)
    await models.sequelize.query(`DELETE FROM "Addresses" WHERE chain='${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });

    await models.sequelize.query(`DELETE FROM "Chains" WHERE id='${chain.id}';`, {
      type: QueryTypes.DELETE,
      transaction: t,
    });
  });

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

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

  if (!req.user) {
    return next(new Error('Not logged in'));
  }

  // TODO: factor this pattern into a util
  const userAddressIds = (await req.user.getAddresses()).filter((addr) => !!addr.verified).map((addr) => addr.id);
  const adminRoles = await models.Role.findAll({
    where: {
      address_id: { [Op.in]: userAddressIds },
      permission: { [Op.in]: ['admin', 'moderator'] },
      chain_id: chain.id,
    },
  });
  if (!req.user.isAdmin && adminRoles.length === 0) {
    return next(new Error('Must be admin'));
  }

  // get new objects created over the last 14 days
  const newObjectsQuery = async (table) => {
    console.log(`SELECT seq.date, COUNT(${table}.*) AS new_items
FROM ( SELECT CURRENT_DATE - seq.date AS date FROM generate_series(0, 14) AS seq(date) ) seq
LEFT JOIN ${table} ON ${table}.created_at::date = seq.date
WHERE 'chain_id' = ?
GROUP BY seq.date
ORDER BY seq.date DESC;`);
    return models.sequelize.query(`SELECT seq.date, COUNT(${table}.*) AS new_items
FROM ( SELECT CURRENT_DATE - seq.date AS date FROM generate_series(0, 14) AS seq(date) ) seq
LEFT JOIN ${table} ON ${table}.created_at::date = seq.date
WHERE 'chain_id' = :chainOrCommunity
GROUP BY seq.date
ORDER BY seq.date DESC;`, {
      type: QueryTypes.SELECT,
      replacements: { chainOrCommunity: chain.id },
    });
  };
  const roles = await newObjectsQuery('"Roles"');
  const threads = await newObjectsQuery('"OffchainThreads"');
  const comments = await newObjectsQuery('"OffchainComments"');

  // get total number of roles, threads, and comments as of today
  const totalObjectsQuery = async (table) => {
    return models.sequelize.query(
      `SELECT COUNT(id) AS new_items FROM ${table} WHERE 'chain_id' = :chainOrCommunity;`, {
        type: QueryTypes.SELECT,
        replacements: { chainOrCommunity: chain.id },
      }
    );
  };
  const totalRoles = await totalObjectsQuery('"Roles"');
  const totalThreads = await totalObjectsQuery('"OffchainThreads"');
  const totalComments = await totalObjectsQuery('"OffchainComments"');

  // get number of active accounts by day
  const activeAccounts = await models.sequelize.query(`
SELECT seq.date, COUNT(DISTINCT objs.address_id) AS new_items
FROM ( SELECT CURRENT_DATE - seq.date AS date FROM generate_series(0, 14) AS seq(date) ) seq
LEFT JOIN (
  SELECT address_id, created_at FROM "OffchainThreads" WHERE created_at > CURRENT_DATE - 14
    AND ${chain ? 'chain' : 'community'} = :chainOrCommunity
  UNION
  SELECT address_id, created_at FROM "OffchainComments" WHERE created_at > CURRENT_DATE - 14
    AND ${chain ? 'chain' : 'community'} = :chainOrCommunity
  UNION
  SELECT address_id, created_at FROM "OffchainReactions" WHERE created_at > CURRENT_DATE - 14
    AND ${chain ? 'chain' : 'community'} = :chainOrCommunity
) objs
ON objs.created_at::date = seq.date
GROUP BY seq.date
ORDER BY seq.date DESC;
`, {
    type: QueryTypes.SELECT,
    replacements: { chainOrCommunity: chain.id },
  });

  return res.json({
    status: 'Success',
    result: {
      roles,
      threads,
      comments,
      totalRoles,
      totalThreads,
      totalComments,
      activeAccounts,
    },
  });
}

bulkThreads = async (
  models: DB,
  req: Request,
  res: Response,
  next: NextFunction
) => {
  const [chain, error] = await validateChain(models, req.query);
  if (error) return next(new Error(error));
  const { cutoff_date, topic_id, stage } = req.query;

  const bind = { chain: chain.id };

  let topicOptions = '';
  if (topic_id) {
    topicOptions += `AND t.topic_id = $topic_id `;
    bind['topic_id'] = topic_id;
  }
  if (stage) {
    topicOptions += `AND t.stage = $stage `;
    bind['stage'] = stage;
  }

  bind['created_at'] = cutoff_date;

  let threads;
  if (cutoff_date) {
    const query = `
      SELECT addr.id AS addr_id, addr.address AS addr_address, last_commented_on,
        addr.chain AS addr_chain, thread_id, thread_title,
        thread_chain, thread_created, threads.kind,
        threads.read_only, threads.body, threads.stage, threads.snapshot_proposal,
        threads.has_poll,
        threads.url, threads.pinned, topics.id AS topic_id, topics.name AS topic_name,
        topics.description AS topic_description, topics.chain_id AS topic_chain,
        topics.telegram AS topic_telegram,
        collaborators, chain_entities, linked_threads
      FROM "Addresses" AS addr
      RIGHT JOIN (
        SELECT t.id AS thread_id, t.title AS thread_title, t.address_id, t.last_commented_on,
          t.created_at AS thread_created,
          t.chain AS thread_chain, t.read_only, t.body,
          t.has_poll,
          t.stage, t.snapshot_proposal, t.url, t.pinned, t.topic_id, t.kind, ARRAY_AGG(DISTINCT
            CONCAT(
              '{ "address": "', editors.address, '", "chain": "', editors.chain, '" }'
              )
            ) AS collaborators,
          ARRAY_AGG(DISTINCT
            CONCAT(
              '{ "id": "', chain_entities.id, '",
                  "type": "', chain_entities.type, '",
                 "type_id": "', chain_entities.type_id, '",
                 "completed": "', chain_entities.completed, '" }'
              )
            ) AS chain_entities,
          ARRAY_AGG(DISTINCT
            CONCAT(
              '{ "id": "', linked_threads.id, '",
                  "linked_thread": "', linked_threads.linked_thread, '",
                  "linking_thread": "', linked_threads.linking_thread, '" }'
            )
          ) AS linked_threads 
        FROM "OffchainThreads" t
        LEFT JOIN "LinkedThreads" AS linked_threads
        ON t.id = linked_threads.linking_thread
        LEFT JOIN "Collaborations" AS collaborations
        ON t.id = collaborations.offchain_thread_id
        LEFT JOIN "Addresses" editors
        ON collaborations.address_id = editors.id
        LEFT JOIN "ChainEntities" AS chain_entities
        ON t.id = chain_entities.thread_id
        WHERE t.deleted_at IS NULL
          AND t.chain = $chain 
          ${topicOptions}
          AND COALESCE(t.last_commented_on, t.created_at) < $created_at
          AND t.pinned = false
          GROUP BY (t.id, COALESCE(t.last_commented_on, t.created_at))
          ORDER BY COALESCE(t.last_commented_on, t.created_at) DESC LIMIT 20
        ) threads
      ON threads.address_id = addr.id
      LEFT JOIN "OffchainTopics" topics
      ON threads.topic_id = topics.id`;
    let preprocessedThreads;
    try {
      preprocessedThreads = await models.sequelize.query(query, {
        bind,
        type: QueryTypes.SELECT,
      });
    } catch (e) {
      console.log(e);
      return next(new Error('Could not fetch threads'));
    }

    const root_ids = [];
    threads = preprocessedThreads.map((t) => {
      const root_id = `discussion_${t.thread_id}`;
      root_ids.push(root_id);
      const collaborators = JSON.parse(t.collaborators[0]).address?.length
        ? t.collaborators.map((c) => JSON.parse(c))
        : [];
      const chain_entities = JSON.parse(t.chain_entities[0]).id
        ? t.chain_entities.map((c) => JSON.parse(c))
        : [];
      const linked_threads = JSON.parse(t.linked_threads[0]).id
        ? t.linked_threads.map((c) => JSON.parse(c))
        : [];
      const last_edited = getLastEdited(t);

      const data = {
        id: t.thread_id,
        title: t.thread_title,
        url: t.url,
        body: t.body,
        last_edited,
        kind: t.kind,
        stage: t.stage,
        read_only: t.read_only,
        pinned: t.pinned,
        chain: t.thread_chain,
        created_at: t.thread_created,
        collaborators,
        linked_threads,
        chain_entities,
        snapshot_proposal: t.snapshot_proposal,
        has_poll: t.has_poll,
        last_commented_on: t.last_commented_on,
        Address: {
          id: t.addr_id,
          address: t.addr_address,
          chain: t.addr_chain,
        },
      };
      if (t.topic_id) {
        data['topic'] = {
          id: t.topic_id,
          name: t.topic_name,
          description: t.topic_description,
          chainId: t.topic_chain,
          telegram: t.telegram,
        };
      }
      return data;
    });
  } else {
    threads =
      // TODO: May need to include last_commented_on in order, if this else is used
      (
        await models.OffchainThread.findAll({
          where: { chain: chain.id },
          include: [
            {
              model: models.Address,
              as: 'Address',
            },
            {
              model: models.Address,
              as: 'collaborators',
            },
            {
              model: models.OffchainTopic,
              as: 'topic',
            },
            {
              model: models.ChainEntity,
            },
            {
              model: models.LinkedThread,
              as: 'linked_threads',
            },
          ],
          attributes: { exclude: ['version_history'] },
          order: [['created_at', 'DESC']],
        })
      ).map((t, idx) => {
        const row = t.toJSON();
        const last_edited = getLastEdited(row);
        row['last_edited'] = last_edited;
        return row;
      });
  }

  const countsQuery = `
     SELECT id, title, stage FROM "OffchainThreads"
     WHERE chain = $chain AND (stage = 'proposal_in_review' OR stage = 'voting')`;

  const threadsInVoting: OffchainThreadInstance[] =
    await models.sequelize.query(countsQuery, {
      bind,
      type: QueryTypes.SELECT,
    });
  const numVotingThreads = threadsInVoting.filter(
    (t) => t.stage === 'voting'
  ).length;

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

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

  // globally shared SQL replacements
  const communityOptions = 'chain = :chain';
  const replacements = { chain: chain.id };

  // parallelized queries
  const [topics, pinnedThreads, admins, mostActiveUsers, threadsInVoting, chatChannels] =
    await (<
      Promise<
        [
          OffchainTopicInstance[],
          unknown,
          RoleInstance[],
          unknown,
          OffchainThreadInstance[],
          ChatChannelInstance[]
        ]
      >
    >Promise.all([
      // topics
      models.OffchainTopic.findAll({
        where: { chain_id: chain.id },
      }),
      // threads, comments, reactions
      new Promise(async (resolve, reject) => {
        try {
          const threadParams = Object.assign(replacements, { pinned: true });
          const rawPinnedThreads = await models.OffchainThread.findAll({
            where: threadParams,
            include: [
              {
                model: models.Address,
                as: 'Address',
              },
              {
                model: models.Address,
                as: 'collaborators',
              },
              {
                model: models.OffchainTopic,
                as: 'topic',
              },
              {
                model: models.ChainEntity,
              },
              {
                model: models.LinkedThread,
                as: 'linked_threads',
              },
              {
                model: models.OffchainReaction,
                as: 'reactions',
                include: [
                  {
                    model: models.Address,
                    as: 'Address',
                    required: true,
                  },
                ],
              },
            ],
            attributes: { exclude: ['version_history'] },
          });

          resolve(
            rawPinnedThreads.map((t) => {
              return t.toJSON();
            })
          );
        } catch (e) {
          console.log(e);
          reject(new Error('Could not fetch threads, comments, or reactions'));
        }
      }),
      // admins
      models.Role.findAll({
        where: {
          chain_id: chain.id,
          permission: { [Op.in]: ['admin', 'moderator'] },
        },
        include: [models.Address],
        order: [['created_at', 'DESC']],
      }),
      // most active users
      new Promise(async (resolve, reject) => {
        try {
          const thirtyDaysAgo = new Date(
            (new Date() as any) - 1000 * 24 * 60 * 60 * 30
          );
          const activeUsers = {};
          const where = {
            updated_at: { [Op.gt]: thirtyDaysAgo },
            chain: chain.id,
          };

          const monthlyComments = await models.OffchainComment.findAll({
            where,
            include: [models.Address],
          });
          const monthlyThreads = await models.OffchainThread.findAll({
            where,
            attributes: { exclude: ['version_history'] },
            include: [{ model: models.Address, as: 'Address' }],
          });

          (monthlyComments as any).concat(monthlyThreads).forEach((post) => {
            if (!post.Address) return;
            const addr = post.Address.address;
            if (activeUsers[addr]) activeUsers[addr]['count'] += 1;
            else
              activeUsers[addr] = {
                info: post.Address,
                count: 1,
              };
          });
          const mostActiveUsers_ = Object.values(activeUsers).sort((a, b) => {
            return (b as any).count - (a as any).count;
          });
          resolve(mostActiveUsers_);
        } catch (e) {
          reject(new Error('Could not fetch most active users'));
        }
      }),
      models.sequelize.query(
        `
     SELECT id, title, stage FROM "OffchainThreads"
     WHERE ${communityOptions} AND (stage = 'proposal_in_review' OR stage = 'voting')`,
        {
          replacements,
          type: QueryTypes.SELECT,
        }
      ),
      models.ChatChannel.findAll({
        where: {
          chain_id: chain.id
        },
        include: {
          model: models.ChatMessage,
          required: false // should return channels with no chat messages
        }
      }),
    ]));

  const numVotingThreads = threadsInVoting.filter(
    (t) => t.stage === 'voting'
  ).length;

  return res.json({
    status: 'Success',
    result: {
      topics: topics.map((t) => t.toJSON()),
      numVotingThreads,
      pinnedThreads, // already converted to JSON earlier
      admins: admins.map((a) => a.toJSON()),
      activeUsers: mostActiveUsers,
      chatChannels: JSON.stringify(chatChannels)
    },
  });
}

/**
   * 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 };
  }