Using prisma to figure out when a cert should be invalid

prismatest.ts

import { PrismaClient, Prisma } from "@prisma/client";
import Dayjs from "dayjs";
const prisma = new PrismaClient();

const account_id = "gtacodingtutor.testnet";
const issueDate = new Date("2021-09-21");
const expiryDate = new Date("2022-03-20");
const today = new Date("2022-03-01");

const inactivePeriodInDays = 180; 

async function main() {
    // find the most recent transaction from today. 
    // going backwards 6 months at a time, keep finding the earliest transaction until issue date is reached (or no transactions are found for a 6 month period)
    var start = Dayjs(new Date().getTime())
    console.log("\n\n\n checkCertificateValidity \n\n\n");
    await checkCertificateValidity(account_id, new Date("2022-04-10").getTime());
    var end = Dayjs(new Date().getTime())
    var durationMs = end.diff(start, "milliseconds");
    console.log("Total duration in ms: ", durationMs);
}

const toBlockTimestamp = (ts: number) => ts * 1000000;
const fromBlockTimestamp = (d: Prisma.Decimal) => d.dividedBy(1000000).toNumber();

async function checkCertificateValidity(account_id: string, certificateIssueTimestamp: number) {
    var endTimestamp = new Prisma.Decimal(toBlockTimestamp(new Date().getTime()));
    var startTimestamp = Dayjs(fromBlockTimestamp(endTimestamp)).subtract(inactivePeriodInDays, "day").toDate().getTime();

    var tx = await findTransactionInTimeRange(
        new Prisma.Decimal(toBlockTimestamp(startTimestamp)),
        endTimestamp,
        account_id
    );

    var counter = 0;
    // while transaction exists and startTimestamp is after certificateIssueTimestamp
    while (tx && certificateIssueTimestamp < startTimestamp) {
        endTimestamp = tx.block_timestamp.sub(1);
        startTimestamp = Dayjs(fromBlockTimestamp(tx.block_timestamp)).subtract(inactivePeriodInDays, "day").toDate().getTime();
        console.log("Progress: ",
            certificateIssueTimestamp,
            startTimestamp,
            endTimestamp.sub(1), // Just decrease the range by a small value, to avoid matching the same transaction (gte check)
            tx.transaction_hash
        );

        tx = await findTransactionInTimeRange(
            new Prisma.Decimal(toBlockTimestamp(startTimestamp)),
            endTimestamp,
            account_id
        );

        counter++;
        if (counter > 100) {
            break; // stopper; // avoid infinite loops
        }
    }

    if (!tx){
        return console.log("Done: INVALID");
    }

    console.log("Done: VALID");
    console.log("Done: ", tx, counter);
    console.log("Done: ", certificateIssueTimestamp, startTimestamp, endTimestamp);
}



async function testQ() {
    let account = await prisma.accounts.findFirst({
        where: {
            account_id
        }
    });
    console.log("prisma.accounts.findFirst", account);

    let mostRecentTransaction = await prisma.transactions.findFirst({
        orderBy: { block_timestamp: "desc" },
        take: 1,
        where: { signer_account_id: account_id }
    });

    console.log("mostRecentTransaction", mostRecentTransaction);

    const tx = await findTransactionInTimeRange(
        new Prisma.Decimal(toBlockTimestamp(new Date("2022-05-04").getTime())),
        new Prisma.Decimal(toBlockTimestamp(new Date("2022-05-05").getTime())),
        account_id
    )

    console.log("tx", tx);
}

async function findTransactionInTimeRange(startTimestamp: Prisma.Decimal, endTimestamp: Prisma.Decimal, account_id: string) {
    console.log(`
    findTransactionInTimeRange:  
    ${startTimestamp} (${Dayjs(fromBlockTimestamp(startTimestamp)).toString()}), ${endTimestamp} (${Dayjs(fromBlockTimestamp(endTimestamp)).toString()}), ${account_id}
    `);
    return await prisma.transactions.findFirst({
        // orderBy: { block_timestamp: "desc" },
        take: 1,
        where: {
            signer_account_id: account_id,
            block_timestamp: {
                gte: startTimestamp,
                lte: endTimestamp
            }
        }
    });
}


main().catch(console.error).finally(async () => {
    await prisma.$disconnect();
});

@sccheruku Oh ok, got it. Thanks.