API de Sequence Analytics en Dune

pnpm install wrangler --save-dev
alias wrangler='./node_modules/.bin/wrangler'

wrangler login

wrangler init

[vars]
SECRET_API_ACCESS_KEY = "<SECRET_API_ACCESS_KEY>"
PROJECT_ID = <PROJECT_ID>
DAYS = <DAYS>

export interface Env {
  PROJECT_ID: number;
  SECRET_API_ACCESS_KEY: string;
  DAYS: number;
}

export default {
  async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise<Response> {
    const url = new URL(request.url);

    // Handle different endpoints
    if (url.pathname === "/dailyActiveUsers") {
      return handleDailyWallets(env, request);
    } else if (url.pathname === "/totalTransactionsSent") {
      return handleTotalTxns(env, request);
    } else {
      return new Response("No function for this URL", {status: 405});
    }
  }
};

const handleDailyWallets = async (env: Env, request: Request) => {
  return new Response(JSON.stringify({endpoint: "daily"}), {status: 200});
};

const handleTotalTxns = async (env: Env, request: Request) => {
  return new Response(JSON.stringify({endpoint: "total"}), {status: 200});
};

const endDate = () => {
  const today = new Date();
  return today.toISOString().substring(0, 10); // only including the YYYY-MM-DD date
};

const startDate = (env: Env) => {
  const today = new Date();

  // Format today's date as a string
  const daysBefore = new Date(today);
  daysBefore.setDate(daysBefore.getDate() - env.DAYS);

  // Format the date 7 days before as a string by only including the YYYY-MM-DD date
  const daysBeforeString = daysBefore.toISOString().substring(0, 10);
  return daysBeforeString;
};

const handleDailyWallets = async (env: Env, request: Request) => {
  const resp = await fetch(`https://api.sequence.build/rpc/Analytics/WalletsDaily`, {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      Authorization: `Bearer ${env.SECRET_API_ACCESS_KEY}`
    },
    body: JSON.stringify({
      filter: {
        dateInterval: "DAY",
        endDate: endDate(),
        projectId: env.PROJECT_ID,
        startDate: startDate(env)
      }
    })
  });

  const data: any = await resp.json();
  return new Response(JSON.stringify(data.walletStats), {status: 200});
};

const handleTotalTxns = async (env: Env, request: Request) => {
  const resp = await fetch(
    `https://api.sequence.build/rpc/Analytics/WalletsTxnSentTotal`,
    {
      method: "POST",
      headers: {
        "Content-Type": "application/json",
        Authorization: `Bearer ${env.SECRET_API_ACCESS_KEY}`
      },
      body: JSON.stringify({
        filter: {
          dateInterval: "DAY",
          endDate: endDate(),
          projectId: env.PROJECT_ID,
          startDate: startDate(env)
        }
      })
    }
  );

  const data: any = await resp.json();
  return new Response(JSON.stringify(data.walletStats), {status: 200});
};

const fillMissingDates = (data: any[], startDate: string, endDate: string) => {
  const filledData: {value: number; label: string}[] = [];
  const start = new Date(startDate);
  const end = new Date(endDate);

  for (let d = new Date(start); d <= end; d.setDate(d.getDate() + 1)) {
    const dateString = d.toISOString().substring(0, 10);
    const existingData = data.find((entry) => entry.label === dateString);
    if (existingData) {
      filledData.push(existingData);
    } else {
      filledData.push({value: 0, label: dateString});
    }
  }

  return filledData;
};

	...
	const data: any = await resp.json();
	const filledData = fillMissingDates(data.walletStats, startDate(env), endDate());
	return new Response(JSON.stringify(filledData), { status: 200 });
}

SELECT
  t.label as "Date", -- converting the label to "Date"
  t.value as "Count" -- converting the value field to "Count"
FROM UNNEST(
  TRY_CAST(
    JSON_PARSE(HTTP_GET('https://<URL>/dailyActiveUsers')) AS ARRAY(ROW(label VARCHAR, value DOUBLE))
  )
) AS t(label, value)

SELECT
  t.label,
  t.value
FROM UNNEST(
  TRY_CAST(
    JSON_PARSE(HTTP_GET('https://<URL>/totalTransactionsSent')) AS ARRAY(ROW(label VARCHAR, value DOUBLE))
  )
) AS t(label, value)