#!/usr/bin/env bash

set -euo pipefail

if (( $# != 4 )); then
    echo "usage: $(basename "$0") <database> <table> <start epoch> <end epoch>" 1>&2
    exit 1
fi
db=$1
table=$2
start=$3
end=$4

sqlite3 -batch "${db}" << EOF
CREATE TEMP VIEW tmp_energy
AS
WITH tmp_diff AS (
  SELECT timestamp,
  LEAD(timestamp) OVER (ORDER BY timestamp) - timestamp AS timediff,
  0.5*(LEAD(rack_1) OVER (ORDER BY timestamp) + rack_1) AS rack_1_av,
  0.5*(LEAD(rack_2) OVER (ORDER BY timestamp) + rack_2) AS rack_2_av,
  0.5*(LEAD(rack_3) OVER (ORDER BY timestamp) + rack_3) AS rack_3_av,
  0.5*(LEAD(rack_4) OVER (ORDER BY timestamp) + rack_4) AS rack_4_av
  FROM ${table}
)
SELECT 
  ${table}.timestamp     AS timestamp,
  rack_1_av*timediff/3.6e+6 AS rack_1_energy_kWh,
  rack_2_av*timediff/3.6e+6 AS rack_2_energy_kWh,
  rack_3_av*timediff/3.6e+6 AS rack_3_energy_kWh,
  rack_4_av*timediff/3.6e+6 AS rack_4_energy_kWh
FROM ${table}
INNER JOIN tmp_diff ON ${table}.timestamp = tmp_diff.timestamp
WHERE timediff NOT NULL;
SELECT SUM(rack_1_energy_kWh) + SUM(rack_2_energy_kWh)
  FROM tmp_energy
  WHERE timestamp >= ${start} and timestamp <= ${end};
EOF