#!/usr/bin/env ruby

require 'sqlite3'
require 'syslog'

DB_FILE = "metrics.db"

# 入電数統計を作るために参照する過去時間数。
LOOKBACK_HOURS = 48

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

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|

  # low3 と low24 については現状の閾値と発報状態を取得しておく
  arow = db.get_first_row(<<~SQL, [center_id, metric])
    SELECT t.low3, t.low24, a.since_low3, a.since_low24
    FROM thresholds t
    LEFT JOIN alert_state a
    USING(center_id, metric)
    WHERE t.center_id=? AND t.metric=?;
  SQL
  cur_low3, cur_low24, since_low3, since_low24 = arow || [nil, nil, nil, nil]

  # 過去の入電状況を取得
  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 に対する閾値 low1, high1
  #

  if values.max > 0.0 then
    high1 = values.max * 3.0 + 1
  else
    high1 = 0.0
  end

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

  #
  # 3時間積算値 v3 に対する閾値 low3, high3
  #

  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 * 2.5
    low3 = [sums3.min * 0.8 - 1.0, 0.0].max
  end

  # low3 が発報状態の場合、閾値変更をしないで現状値に戻す。
  # これは入電数急減時に閾値が下がってゼロとなることによりすぐに注意解除が打たれてしまうのを防止するため
  if since_low3 and cur_low3 then
    low3 = cur_low3
  end

  #
  # v24
  #

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

  # cancel changing low24 when on alert
  if since_low24 and cur_low24 then
    low24 = cur_low24
  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
    ]
  )
  nrows+=db.changes
end

db.close
fac=if STDERR.tty? then Syslog::LOG_USER else Syslog::LOG_NEWS end
Syslog::open('iwg_threshold', Syslog::LOG_PID, fac) do |log|
  log.info("updated #{nrows}")
end