solscan api and synchronizing transactions - updated

// load solana transactions into the database

const env = require('node-env-file')
env(__dirname + '/../.env')
const solscan = require('../external/solscan.js')

const { dbLocation, dbChainTx, dbChainTxSync, dbSplAccount, dbKnownWallets } = require('../db')
const { dateutils, decimals, signal, utils } = require('../utils')
const { timeFmtDb, dateNowUTC } = dateutils
const { weiToDisplay, bigD } = decimals

module.exports = {

  /* 
    Iterate recursively through solana transactions, until dbBreakpoint
  */
  syncSolanaAddress: async (locationId) => {
    try {
      let notifications = []
      const limit = 15
      const location = await dbLocation.getLocationbyId(locationId)
      const address = location.address

      // sync solana transactions
      let lastKnown = await dbChainTxSync.getHash(locationId, 'sol')
      let lastHash
      if (typeof lastKnown === 'undefined') {
        await dbChainTxSync.newSyncRecordHash(locationId, 'sol') // we want lastHash to be undefined in this case
      }
      let txns = await solscan.transactionsInRange(address, undefined, 1) // get the most recent by passing undefined
      txns = txns.sort((a, b) => { a.slot > b.slot ? 1 : -1 })
      lastHash = txns[0].txHash
      const forwardTxHash = txns[0].txHash // where we will be after the sync is complete
      let synced = false
      if (typeof lastKnown !== 'undefined') {
        if (lastKnown === lastHash) {
          console.log(timeFmtDb(dateNowUTC()) + ' sol sync is up to date for location: ' + locationId)
          synced = true
        }
      }
      if (!synced) {
        const message1 = await processSolTransaction(txns[0], address) // process the single tx
        notifications.push(message1)
        while (txns.length > 0) {
          txns = await solscan.transactionsInRange(address, lastHash, limit)
          if (txns.length > 0) {
            txns = txns.sort((a, b) => { a.slot > b.slot ? 1 : -1 })
            for (let i = 0; i < txns.length; i++) {
              const message = await processSolTransaction(txns[i], address)
              notifications.push(message)
              if (typeof lastKnown !== 'undefined') {
                if (txns[i].txHash === lastKnown) {
                  console.log(timeFmtDb(dateNowUTC()) + ' sol sync is up to date, new transactions were found for location: ' + locationId)
                  synced = true
                  break
                }
              }
            }
            lastHash = txns[txns.length - 1].txHash
          }
          if (synced) {
            txns = []
          }
        }
      }
      // the entire scan needs to be completed before updating location in the database
      await dbChainTxSync.updateSyncRecordHash(locationId, forwardTxHash, 'sol')

      if (notifications.length > 0) {
        if (notifications.length < 10) {
          let acc = 'New Solana Transactions:\n'
          notifications.forEach((n) => {
            acc += n + '\n'
          })
          await signal.sendMessageToGroup(acc, process.env.SIGNAL_GROUP_NOTIFICATIONS)
        } else {
          let acc = 'More than ten solana transactions were added to the database for location with id: ' + locationId + ' \n'
          await signal.sendMessageToGroup(acc, process.env.SIGNAL_GROUP_NOTIFICATIONS)
        }
      }
    } catch (error) {
      console.log(error)
      return { status: 'error', error: 'Application Error' }
    }
  },

  syncSplForLocation: async (locationId) => {

    // discover new spl transactions from the main account, annotate in db
    // poll for spl transactions from associated account rather than main account
    // there are separate synchronization states for each associated account.

    try {
      let notifications = []
      const limit = 15
      const location = await dbLocation.getLocationbyId(locationId)
      const ownerAddress = location.address
      
      // discover spl transactions
      let lastKnownSpl = await dbChainTxSync.getHash(locationId, 'spl')
      if (typeof lastKnownSpl === 'undefined') {
        await dbChainTxSync.newSyncRecordHash(locationId, 'spl') // we want lastHash to be undefined in this case
      }
      let splTxns = await solscan.splTransfersInRange(ownerAddress, undefined, undefined, 0, 1) // get the most recent by passing undefined
      const forwardSplTxHash = splTxns[0].blockTime // where we will be after the sync is complete
      let lastSplTime = splTxns[0].blockTime
      synced = false // reset
      if (typeof lastKnownSpl !== 'undefined') {
        if (lastKnownSpl == lastSplTime) {
          console.log(timeFmtDb(dateNowUTC()) + ' spl sync is up to date for location: ' + locationId)
          synced = true // true if no new transactions and already synced previously.
        }
      }
      let toTime = splTxns[0].blockTime
      let fromTime = 0
      let offset = 0
      if (!synced) {
        while (splTxns.length > 0) {
          splTxns = await solscan.splTransfersInRange(ownerAddress, fromTime, toTime, offset, limit)
          if (splTxns.length > 0) {
            splTxns = splTxns.sort((a, b) => { a.slot > b.slot ? 1 : -1 })
            for (let i = 0; i < splTxns.length; i++) {
              // iterate back in time on the sync look for a match and if found, no need to continue digging
              if (typeof lastKnownSpl !== 'undefined') {
                if (lastKnownSpl == lastSplTime) {
                  console.log(timeFmtDb(dateNowUTC()) + ' spl sync is up to date for location: ' + locationId)
                  synced = true
                }
              }
              if (!await dbSplAccount.accountExists(splTxns[i].address)) {
                if (typeof splTxns[i].symbol !== 'undefined' && splTxns[i].symbol !== 'SOL') { // concern that wSOL spl transactions will mess up account balances
                  console.log('new associated account: ' + splTxns[i].symbol + ' ' + splTxns[i].address)
                  const id = await dbSplAccount.newAccount(ownerAddress, splTxns[i].symbol, splTxns[i].address)
                  if (typeof id === 'undefined') {
                    console.log('couldnt add spl acssociated account.')
                  } else {
                    await dbKnownWallets.addAddress(splTxns[i].address, 'associated' + splTxns[i].symbol, 'solana')
                  }
                }
              }
            }
            toTime = splTxns[splTxns.length - 1].blockTime - 1 // -1: remove redundant entries
            offset += limit
            lastSplTime = splTxns[splTxns.length - 1].blockTime
          }
          if (synced) {
            splTxns = [] // we are done synchronizing associated accounts, now can proceed to find transactions
          }
        }
      }

      await dbChainTxSync.updateSyncRecordHash(locationId, forwardSplTxHash, 'spl') // mark stage as synced

      // now check for new transactions in time range for each detected spl account
      // basically do everything upstairs again, in a loop
      const associatedAccounts = await dbSplAccount.splAccountsForOwner(ownerAddress)
      for (let i = 0; i < associatedAccounts.length; i++) {
        
        const lastKnownSplAa = await dbChainTxSync.getAssociated(locationId, 'spl', associatedAccounts[i].address)
        if (typeof lastKnownSplAa === 'undefined') {
          await dbChainTxSync.newSyncRecordAssociated(locationId, 'spl', associatedAccounts[i].address) // we want lastHash to be undefined in this case
        }
        let aaTxns = await solscan.splTransfersInRange(associatedAccounts[i].address, undefined, undefined, 0, 1)
        const forwardSplAaTxHash = aaTxns[0].blockTime
        let lastAaTime = aaTxns[0].blockTime
        synced = false
        if (typeof lastKnownSplAa !== 'undefined') {
          if (lastKnownSplAa == lastAaTime) {
            console.log(timeFmtDb(dateNowUTC()) + ' spl sync is up to date for associated account: ' + associatedAccounts[i].address)
            synced = true
          }
        }
        let toTime = aaTxns[0].blockTime
        let fromTime = 0
        let offset = 0
        if (!synced) {
          while (aaTxns.length > 0) {
            aaTxns = await solscan.splTransfersInRange(associatedAccounts[i].address, fromTime, toTime, offset, limit)
            if (aaTxns.length > 0) {
              aaTxns = aaTxns.sort((a, b) => { a.slot > b.slot ? 1 : -1 })
              for (let j = 0; j < aaTxns.length; j++) {
                if (typeof lastKnownSplAa !== 'undefined') {
                  if (lastKnownSplAa == lastAaTime) {
                    console.log(timeFmtDb(dateNowUTC()) + ' spl sync is up to date for location: ' + locationId)
                    synced = true
                  }
                }
                const message = await processSplTransaction(aaTxns[j], associatedAccounts[i].address, ownerAddress)
                notifications.push(message)
              }
              toTime = aaTxns[aaTxns.length - 1].blockTime - 1 // -1: remove redundant entries
              offset += limit
              lastAaTime = aaTxns[aaTxns.length - 1].blockTime
            }
            if (synced) {
              aaTxns = [] // we are done synchronizing associated accounts, now can proceed to find transactions
            }
          }
        }
        await dbChainTxSync.updateSyncRecordAssociated(locationId, forwardSplAaTxHash, 'spl', associatedAccounts[i].address)
      }

      if (notifications.length > 0) {
        if (notifications.length < 10) {
          let acc = 'New Solana Transactions:\n'
          notifications.forEach((n) => {
            acc += n + '\n'
          })
          await signal.sendMessageToGroup(acc, process.env.SIGNAL_GROUP_NOTIFICATIONS)
          console.log(acc)
        } else {
          let acc = 'More than ten solana transactions were added to the database for location with id: ' + locationId + ' \n'
          await signal.sendMessageToGroup(acc, process.env.SIGNAL_GROUP_NOTIFICATIONS)
          console.log(acc)
        }
      }
    } catch (error) {
      console.log(error)
      return { status: 'error', error: 'Application Error' }
    }
  }

}

const processSolTransaction = async (transaction, address) => {
  const tx = await solscan.getTransaction(transaction.txHash)
  let value
  for (let j = 0; j < tx.inputAccount.length; j++) {
    if (tx.inputAccount[j].account === address) {
      const pre = bigD(tx.inputAccount[j].preBalance)
      const post = bigD(tx.inputAccount[j].postBalance)
      value = post.subtract(pre)
    }
  }
  let from, to

  // todo determine from to as such, when i send solana out i am from else to
  const signNumber = Math.sign(value.getValue())
  if (tx.parsedInstruction.length > 0) {
    if (signNumber === -1) { // reduced funds means i am the sender
      to = tx.parsedInstruction[0].programId
      from = address
    } else {
      from = tx.parsedInstruction[0].programId
      to = address
    }
  }

  console.log(timeFmtDb(tx.blockTime * 1000) + ' ' + tx.slot + ' ' + from.padEnd(44) + ' -> ' + to.padEnd(44) + ' ' + (weiToDisplay(Math.abs(value.getValue()), 9)).padStart(25))
  const data = [
    null, // positionId
    'solana', // blockchain
    transaction.txHash,
    'sol', // sol or spl
    tx.slot, // block number (sol appears to not have a block number)
    tx.blockTime, // timestamp
    null, // nonce
    from, // from
    to, // to
    0, // insert zero if no contract address
    Math.abs(value.getValue()), // value
    'SOL',
    null,
    9,
    tx.fee ? tx.fee : null,
    null, //gasPrice
    null, // gasused
    null,
    null,
    tx.status ? tx.status.toLowerCase() : null
  ]
  let message
  const result = await dbChainTx.newChainTx(data)
  if (!result.success) {
    console.log(result.error_msg + ' tx:' + transaction.txHash + ' bn: ' + tx.slot)
    message = result.error_msg + ' tx:' + transaction.txHash + ' bn: ' + tx.slot
  } else {
    message = transaction.txHash + '\n' +
      '  From: ' + from + '\n  To: ' + to + '\n' +
      '  Value: ' + value.getValue() + ' ' + 'SOL' + '\n\n'
  }
  return message
}


const processSplTransaction = async (tx, address, ownerAddress) => {
  let hash = tx.signature[0] // break if wrong
  let from, to
  const transaction = await solscan.getTransaction(tx.signature[0])
  let value = bigD(0)
  let decimals = tx.decimals
  let tokenName = tx.tokenName
  let symbol = tx.symbol
  for (let i = 0; i < transaction.tokenBalanes.length; i++) {
    const transfer = transaction.tokenBalanes[i]
    if (transfer.account === address) {
      value = bigD(transfer.amount.postAmount).subtract(bigD(transfer.amount.preAmount))
      decimals = transfer.token.decimals
      symbol = transfer.token.symbol
      tokenName = transfer.token.name
    }
  }
  let counterParty
  let backupCounterparty
  for (let i = 0; i < transaction.tokenBalanes.length; i++) { // address of equal sender/recipient
    const transfer = transaction.tokenBalanes[i]
    if (transfer.account !== address) {
      backupCounterparty = transfer
      const outValue = bigD(transfer.amount.postAmount).subtract(bigD(transfer.amount.preAmount))
      if (Math.abs(outValue.getValue()) === Math.abs(value.getValue())) {
        counterParty = transfer
      }
    }
  }
  if (typeof counterParty === 'undefined') {
    counterParty = backupCounterparty // couldnt locate an equal output
    // console.log('using backup counterParty') 
  }
  if (typeof counterParty === 'undefined') {
    counterParty = { account: '11111111111111111111111111111111' } // tokens were minted
    // console.log('using minting counterParty')
  }
  const signNumber = Math.sign(Number(value.getValue()))
  if (signNumber === -1) {
    from = ownerAddress
    to = counterParty.account
  } else {
    to = ownerAddress
    from = counterParty.account
  }
  slot = transaction.slot
  const sign = Math.sign(tx.changeAmount) === 1 ? '' : '-'
  console.log(timeFmtDb(tx.blockTime * 1000) + ' ' + slot + ' ' + from.padEnd(44) + ' -> ' + to.padEnd(44) + ' ' + (sign + weiToDisplay(Math.abs(tx.changeAmount), tx.decimals)).padStart(25))

  const data = [
    null, // positionId
    'solana', // blockchain
    hash ? hash : null,
    'spl', // sol or spl
    slot, // block number (sol appears to not have a block number)
    tx.blockTime ? tx.blockTime : null, // timestamp
    null, // nonce
    from, // from
    to, // to
    tx.tokenAddress ? tx.tokenAddress : 0, // insert zero if no contract address
    Math.abs(value.getValue()), // value
    tokenName,
    symbol,
    decimals ? decimals : null,
    tx.fee ? tx.fee : null,
    null, //gasPrice
    null, // gasused
    null, // input
    null, // confirmations
    tx.status ? tx.status.toLowerCase() : null // assuming all transactions from this endpoint were mined successfully
  ]
  // console.log(transaction)
  // console.log(data)

  let message
  const result = await dbChainTx.newChainTx(data)
  if (!result.success) {
    console.log(result.error_msg + ' tx:' + hash + ' bn: ' + slot)
    message = result.error_msg + ' tx:' + hash + ' bn: ' + slot
  } else {
    message = hash + '\n' +
      '  From: ' + from + '\n  To: ' + to + '\n' +
      '  Value: ' + tx.changeAmount + ' ' + symbol + '\n\n'
  }
  return message
  // return 'i did the thing'
}

  ; (async () => {
    try {
      const locations = await dbLocation.getLocationsChain('solana')
      for (let i = 0; i < locations.length; i++) {
        await module.exports.syncSolanaAddress(locations[i].id)
        await module.exports.syncSplForLocation(locations[i].id)
      }
      process.exit(0)
    } catch (error) {
      console.log(error)
    }
  })()

I updated this to reflect my current solution which discovers new associated accounts and polls for transactions using them, as opposed to relying on the parent address for calculating spl balances, which has proven difficult and / or unreliable