add_v3_pools_liquidity_into_bigquery.ts

import {
  AIRDROP_BIGQUERY_V3_POOLS_LIQUIDITY,
  VOLTAGE_EXCHAGE_V3_SUBGRAPH_URL,
} from "../constants";
import { getAllPositionSnapshotsBetweenDatesQuery } from "../graph_ql/queries";
import {
  V3PoolPosition,
  V3PoolPositionSnapshot,
  V3PoolPositionSnapshots,
} from "../graph_ql/types";
import { createBigQueryService } from "../services/big_query_service";
import { GraphQLService } from "../services/graphql_service";
import { UserRepository } from "../user/user_repository";
import * as admin from "firebase-admin";
import dotenv from "dotenv";
import { SqrtPriceMath, TickMath } from "@voltage-finance/v3-sdk";
import { FUSE_PROVIDER } from "../constants";
import { formatUnits } from "ethers/lib/utils";
import JSBI from "jsbi";
import { ethers } from "ethers";
import { TokenType } from "../enums";
import { TokenService } from "../services/token_service";
import fs from "fs";

async function addV3PoolLiquidityIntoBigQuery() {
  dotenv.config();
  admin.initializeApp();

  const allPositionSnapshots =
    await getAllPositionSnapshotsBetweenApril22AndMay25();

  console.log("Total number of positions: ", allPositionSnapshots.length);

  const userRepository = new UserRepository();

  const positionSnapshotsOfAirdropUsers = [];

  for (const positionSnapshot of allPositionSnapshots) {
    const walletAddress = positionSnapshot.origin.toLowerCase();
    const user = await userRepository.getUserByEoaAddress(walletAddress);

    if (user) positionSnapshotsOfAirdropUsers.push(positionSnapshot);
  }

  console.log(
    "Total number of position snapshots that belong to Airdrop users: ",
    positionSnapshotsOfAirdropUsers.length
  );

  const graphQLService = new GraphQLService();
  const tokenService = new TokenService(graphQLService);

  const rows = [];

  for (const positionSnapshot of positionSnapshotsOfAirdropUsers) {
    const rawLiquidityInUsd = await getUserV3PositionLiquidityUSD(
      positionSnapshot.position,
      tokenService
    );

    const amountUsdFixed = rawLiquidityInUsd.toFixed(2);
    const amountUsd = parseFloat(amountUsdFixed);

    const walletAddress = positionSnapshot.origin.toLowerCase();

    rows.push({
      amountUsd: amountUsd,
      timestamp: positionSnapshot.timestamp,
      walletAddress: walletAddress,
    });
  }

  const bigQueryService = createBigQueryService();

  await bigQueryService.insertData({
    tableId: AIRDROP_BIGQUERY_V3_POOLS_LIQUIDITY,
    rows: rows,
  });
}

async function getAllPositionSnapshotsBetweenApril22AndMay25() {
  const april22 = new Date("2024-04-22T00:00:00Z");
  const april22InSeconds = Math.floor(april22.getTime() / 1000);

  const may25 = new Date("2024-05-25T00:00:00Z");
  const may25InSeconds = Math.floor(may25.getTime() / 1000);

  const twelveHoursInSeconds = 3600 * 12;

  const allPositionSnapshots: V3PoolPositionSnapshot[] = [];

  for (
    let currentTimestamp = april22InSeconds;
    currentTimestamp < may25InSeconds;
    currentTimestamp += twelveHoursInSeconds
  ) {
    const startInSeconds = currentTimestamp.toString();
    const endInSeconds = (currentTimestamp + twelveHoursInSeconds).toString();

    const positionSnapshots = await getAllPositionSnapshotsBetweenDates({
      startTimestampInSeconds: startInSeconds,
      endTimestampInSeconds: endInSeconds,
    });

    allPositionSnapshots.push(...positionSnapshots);
  }

  return allPositionSnapshots;
}

async function getAllPositionSnapshotsBetweenDates(params: {
  startTimestampInSeconds: string;
  endTimestampInSeconds: string;
}) {
  const graphQLService = new GraphQLService();

  const { startTimestampInSeconds, endTimestampInSeconds } = params;

  const start = parseInt(startTimestampInSeconds);
  const startDateAsString = new Date(start * 1000).toISOString();

  const end = parseInt(endTimestampInSeconds);
  const endDateAsString = new Date(end * 1000).toISOString();

  console.log(
    `Getting all position snapshots between ${startDateAsString} (including) ` +
      `and ${endDateAsString} (excluding)...`
  );

  const pageSize = 500;
  let fetchedAllData = false;
  let skip = 0;

  const allPositionSnapshots: V3PoolPositionSnapshot[] = [];

  while (!fetchedAllData) {
    const result =
      await graphQLService.fetchFromGraphQL<V3PoolPositionSnapshots>(
        VOLTAGE_EXCHAGE_V3_SUBGRAPH_URL,
        getAllPositionSnapshotsBetweenDatesQuery,
        {
          startBlockTimestamp: startTimestampInSeconds,
          endBlockTimestamp: endTimestampInSeconds,
          first: pageSize,
          skip: skip,
        }
      );

    const positionSnapshots = result.positionSnapshots;
    console.log("positionSnapshots.length: ", positionSnapshots.length);

    allPositionSnapshots.push(...positionSnapshots);

    skip += pageSize;

    if (positionSnapshots.length < pageSize) {
      fetchedAllData = true;
    }
  }

  return allPositionSnapshots;
}

function calculateTokenAmount(
  currentTick: string,
  position: V3PoolPosition,
  sqrtRatio: JSBI,
  tokenType: TokenType
) {
  const token = position[tokenType];

  const amount =
    tokenType === TokenType.token0
      ? getToken0Amount(
          +currentTick,
          +position.tickLower.tickIdx,
          +position.tickUpper.tickIdx,
          sqrtRatio,
          JSBI.BigInt(position.liquidity)
        )
      : getToken1Amount(
          +currentTick,
          +position.tickLower.tickIdx,
          +position.tickUpper.tickIdx,
          sqrtRatio,
          JSBI.BigInt(position.liquidity)
        );

  const formattedAmount = formatUnits(amount.toString(), token.decimals);

  return parseFloat(formattedAmount);
}

async function getUserV3PositionLiquidityUSD(
  position: V3PoolPosition,
  tokenService: TokenService
) {
  const { currentTick, sqrtRatio } = await getPool(position.pool.id);

  const [token0Amount, token1Amount] = [
    calculateTokenAmount(currentTick, position, sqrtRatio, TokenType.token0),
    calculateTokenAmount(currentTick, position, sqrtRatio, TokenType.token1),
  ];

  const [token0Price, token1Price] = await Promise.all([
    tokenService.getPrice(position.token0.id),
    tokenService.getPrice(position.token1.id),
  ]);

  return token0Amount * token0Price + token1Amount * token1Price;
}

function getToken0Amount(
  tickCurrent: number,
  tickLower: number,
  tickUpper: number,
  sqrtRatioX96: JSBI,
  liquidity: JSBI
): JSBI {
  if (tickCurrent < tickLower) {
    return SqrtPriceMath.getAmount0Delta(
      TickMath.getSqrtRatioAtTick(tickLower),
      TickMath.getSqrtRatioAtTick(tickUpper),
      liquidity,
      false
    );
  }
  if (tickCurrent < tickUpper) {
    return SqrtPriceMath.getAmount0Delta(
      sqrtRatioX96,
      TickMath.getSqrtRatioAtTick(tickUpper),
      liquidity,
      false
    );
  }
  return JSBI.BigInt("0");
}

function getToken1Amount(
  tickCurrent: number,
  tickLower: number,
  tickUpper: number,
  sqrtRatioX96: JSBI,
  liquidity: JSBI
): JSBI {
  if (tickCurrent < tickLower) {
    return JSBI.BigInt("0");
  }
  if (tickCurrent < tickUpper) {
    return SqrtPriceMath.getAmount1Delta(
      TickMath.getSqrtRatioAtTick(tickLower),
      sqrtRatioX96,
      liquidity,
      false
    );
  }
  return SqrtPriceMath.getAmount1Delta(
    TickMath.getSqrtRatioAtTick(tickLower),
    TickMath.getSqrtRatioAtTick(tickUpper),
    liquidity,
    false
  );
}

async function getPool(poolAddress: string) {
  const voltageV3PoolState = fs.readFileSync(
    "./voltage_v3_pool_state.json",
    "utf-8"
  );

  const poolState = JSON.parse(voltageV3PoolState);

  const poolContract = new ethers.Contract(
    poolAddress,
    poolState.abi,
    FUSE_PROVIDER
  );

  const { tick, sqrtPriceX96 } = await poolContract.slot0();

  return {
    currentTick: tick.toString(),
    sqrtRatio: JSBI.BigInt(sqrtPriceX96.toString()),
  };
}

addV3PoolLiquidityIntoBigQuery();