const express = require('express')
const app = express()

const etherscan = require('etherscan-api').init('(my api key redacted)')

const { Client } = require('pg')
const postgres = new Client()
postgres.connect().catch((error) => { console.log('connecting to postgres: ' + error) })

const functions = require('./functions.js')

app.put('/ethtrancache/:address(0x[0-9a-fA-F]{40})/', handleAddAddress)
app.get('/ethtrancache/:address(0x[0-9a-fA-F]{40})/', handleGetBalance)
app.delete('/ethtrancache/:address(0x[0-9a-fA-F]{40})/', handleDeleteAddress)
app.get('/ethtrancache/:address(0x[0-9a-fA-F]{40})/transactions', handleGetTransactions)

async function handleDeleteAddress (request, response) {
  try {
    await functions.deleteAddress(postgres, request.params['address'])
  } catch (err) {
    response.send(
      { result: 'failure', msg: 'error deleting existing address: ' + err })
    return
  }
  response.send({result: 'success'})
}

async function handleAddAddress (request, response) {
  try {
    await functions.addAddress(postgres, etherscan, request.params['address'])
  } catch (err) {
    response.send({ result: 'failure', msg: 'error adding address: ' + err })
    return
  }
  response.send({ result: 'success' })
}

async function handleGetBalance (request, response) {
  var balance
  try {
    balance = await functions.getBalance(postgres, request.params['address'])
  } catch (err) {
    response.send({ result: 'failure', msg: 'error getting balance: ' + err })
    return
  }
  response.send({ result: 'success', balance: balance })
}

async function handleGetTransactions (request, response) {
  var transactions
  try {
    transactions = await functions.getTransactions(postgres, request.params['address'], request.query.other_address)
  } catch (err) {
    response.send({ result: 'failure', msg: 'error getting transactions: ' + err })
    return
  }
  response.send({ result: 'success', transactions: transactions })
}

app.listen(3000, () => console.log('listening on port 3000'))

async function addAddress (postgres, etherscan, address) {
  /*
  "Accept an Ethereum address as input, and then:
  1. query https://etherscan.io/apis and collects all transactions
  associated with that address.
      1. store the transactions in the DB.
      2. store the address balance in the DB."
  */

  // clear out existing balance and transactions
  try {
    await deleteAddress(postgres, address)
  } catch (err) { throw new Error('error deleting existing address: ' + err) }

  /* scrape and store eth balance */

  var balance

  try {
    balance = await etherscan.account.balance(address)
  } catch (err) { throw new Error('err getting eth balance: ' + err) }

  try {
    await postgres.query(
      'INSERT INTO eth_balances(address, balance) VALUES(LOWER($1), $2)',
      [address, balance['result']])
  } catch (err) { throw new Error('error storing eth balance: ' + err) }

  /* scrape and store transactions */

  var txlist

  try {
    txlist = await etherscan.account.txlist(address)
  } catch (err) { throw new Error('error getting transactions: ' + err) }

  try {
    for (var i = 0; i < txlist.result.length; i++) {
      await postgres.query(
        'INSERT INTO transactions(to_address, txn_id, from_address, value)' +
            ' VALUES(LOWER($1), LOWER($2), LOWER($3), $4)',
        [ txlist.result[i].to,
          txlist.result[i].hash,
          txlist.result[i].from,
          txlist.result[i].value ])
    }
  } catch (err) { throw new Error('error storing transactions: ' + err) }
}

async function deleteAddress (postgres, address) {
  try {
    await postgres.query(
      'DELETE FROM eth_balances WHERE address = LOWER($1)',
      [address])
    await postgres.query(
      'DELETE FROM transactions WHERE from_address = LOWER($1) OR to_address = LOWER($1)',
      [address])
  } catch (err) { throw new Error('PostgreSQL error: ' + err) }
}

async function getBalance (postgres, address) {
  /*
  Return stored address balance by ETH address
  */
  try {
    var result = await postgres.query(
      'SELECT balance FROM eth_balances WHERE address = LOWER($1)',
      [address])

    if (result.rows.length === 0) { throw new Error('no such address') }

    return result.rows[0].balance
  } catch (err) { throw new Error('error getting eth_balance: ' + err) }
}

async function getTransactions (postgres, address, otherAddress) {
  /*
  Return transactions of stored ETH address, and accept some form of search
  params (which params are up to you).
  */

  var query =
    'SELECT * FROM transactions WHERE ' +
      'from_address = LOWER($1) OR to_address = LOWER($1)'

  var values = [address]

  if (otherAddress !== undefined) {
    query += ' AND ( from_address = LOWER($2) OR to_address = LOWER($2) )'
    values.push(otherAddress)
  }

  query += ';'

  try {
    var result = await postgres.query(query, values)
  } catch (err) { throw new Error('error getting transactions: ' + err) }

  return result.rows
}

module.exports = {
  addAddress: addAddress,
  deleteAddress: deleteAddress,
  getBalance: getBalance,
  getTransactions: getTransactions
}

{
  "name": "assignment",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "lint": "node_modules/.bin/eslint *.js"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "etherscan-api": "^8.0.4",
    "express": "^4.16.3",
    "pg": "^7.4.3"
  },
  "devDependencies": {
    "eslint": "^4.19.1",
    "eslint-config-standard": "^11.0.0",
    "eslint-plugin-import": "^2.11.0",
    "eslint-plugin-node": "^6.0.1",
    "eslint-plugin-promise": "^3.7.0",
    "eslint-plugin-standard": "^3.1.0"
  }
}

#!/bin/sh

# tested with PostgreSQL version 9.3.22

. ./pgenv
dropdb -h $PGHOST -U $PGUSER $PGDATABASE || exit 1
createdb -h $PGHOST -U $PGUSER $PGDATABASE || exit 1
psql -h $PGHOST -U $PGUSER $PGDATABASE -f createTables.psql

export PGHOST=
export PGUSER=
export PGPASSWORD=
export PGDATABASE=assignment

create table eth_balances(address text PRIMARY KEY, balance bigint);
create table transactions(to_address text, txn_id text UNIQUE, from_address text, value bigint);