#!/usr/bin/env bash

set -euo pipefail

if (( $# != 3 )); then
    echo "usage: $(basename "$0") <database> <table> <test directory>" 1>&2
    exit 1
fi
db=$1
table=$2
dir=$3

sqlite3 -batch "${db}" << EOF
DROP TABLE IF EXISTS ${table};
CREATE TABLE ${table} (
  job_id      INTEGER PRIMARY KEY,
  start       INTEGER NOT NULL,
  end         INTEGER NOT NULL,
  nodes       INTEGER NOT NULL,
  clock_limit INTEGER NOT NULL,
  slot        TEXT NOT NULL,
  smi_db      TEXT NOT NULL,
  job_dir     TEXT NOT NULL
);
EOF

tmp=$(mktemp)
sql_epoch="strftime('%s',SUBSTR(timestamp, 1, 4)||'-'||SUBSTR(timestamp, 5, 2)||'-'||SUBSTR(timestamp, 7, LENGTH(timestamp) - 6), 'utc')"
for nodes in 8 16; do
  for slot in A B; do
    smi_db=$(find "${dir}" -name "smi-dmon-${nodes}${slot}.db")
    for c in $(seq 210 15 1410); do
      job_dir=$(find "${dir}" -type d -name "power-*${nodes}${slot}-${c}.*")
      job_id=$(echo "${job_dir}" | grep -o -E '[0-9]+$')
      start=$(sqlite3 "${smi_db}" "SELECT MIN(${sql_epoch}) FROM clock_limit_${c}")
      end=$(sqlite3 "${smi_db}" "SELECT MAX(${sql_epoch}) FROM clock_limit_${c}")
      echo "${job_id}|${start}|${end}|${nodes}|${c}|${slot}|${smi_db}|${job_dir}" >> "${tmp}"
    done
  done
done
sqlite3 -batch "${db}" ".import ${tmp} ${table}"
rm -rf "${tmp}"