#!/usr/bin/env ruby

require 'sqlite3'

DB_FILE = "metrics.db"

LOOKBACK_HOURS = 48

db = SQLite3::Database.new(DB_FILE)
now = Time.now.to_i

db.execute <<~SQL
  CREATE TABLE IF NOT EXISTS thresholds (
    center_id TEXT,
    metric TEXT,

    low1 REAL,
    low3 REAL,
    low24 REAL,

    high1 REAL,
    high3 REAL,

    updated_at INTEGER,

    PRIMARY KEY(center_id, metric)
  );
SQL

series_list = db.execute(<<~SQL)
  SELECT DISTINCT center_id, metric
  FROM metrics
SQL

series_list.each do |center_id, metric|

  rows = db.execute(<<~SQL, [center_id, metric, now - LOOKBACK_HOURS * 3600])
    SELECT ts, value
    FROM metrics
    WHERE center_id = ?
      AND metric = ?
      AND ts >= ?
    ORDER BY ts
  SQL

  next if rows.empty?

  values = rows.map { |_, v| v.to_f }

  #
  # v1
  #

  high1 = values.max * 1.5

  low1 =
    if values.min > 0
      1
    else
      0
    end

  #
  # v3
  #

  sums3 = []

  values.each_cons(3) do |a, b, c|
    sums3 << a + b + c
  end

  if sums3.empty?
    high3 = 0
    low3 = 0
  else
    high3 = sums3.max * 1.5
    low3 = sums3.min * 0.95
  end

  #
  # v24
  #

  low24 =
    if values.any? { |v| v > 0 }
      1
    else
      0
    end

  db.execute(
    <<~SQL,
      INSERT OR REPLACE INTO thresholds
      (
        center_id,
        metric,
        low1,
        low3,
        low24,
        high1,
        high3,
        updated_at
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    SQL
    [
      center_id,
      metric,
      low1,
      low3,
      low24,
      high1,
      high3,
      now
    ]
  )
end

db.close