#!/usr/bin/lua

local uci = require("uci")
local cur = uci.cursor()
local json = require "json"

local logdb = arg[1]

require "wm_util"

if not logdb then

  print("no sqlite log defined!")
  return

end

function shallowcopy(orig)
    local orig_type = type(orig)
    local copy
    if orig_type == 'table' then
        copy = {}
        for orig_key, orig_value in pairs(orig) do
            copy[orig_key] = orig_value
        end
    else -- number, string, boolean, etc
        copy = orig
    end
    return copy
end

function median(dataset)

  table.sort(shallowcopy(dataset))
  return dataset[math.floor(#dataset/2)]

end

function filter_data(dataset,width)

  if not width then 
    width = 7
  end  

  if #dataset <= width then
    return dataset
  end  

  local result = {}
  
  local window_spread = math.floor(width/2)
  local window = {}

  for i = 1,window_spread do
    window[#window+1] = dataset[i]["y"]
  end
  
  for key,value in pairs(dataset) do
    nextelem = dataset[key+window_spread]
    if nextelem then
      window[#window+1] = nextelem["y"]
    end  
    if not nextelem or #window>width then
      table.remove(window,1)
    end  
    row = {}
    row["t"]=value["t"]
    row["y"]=median(window)
    result[#result+1] = row
  end
  
  return result

end

function average_results(dataset,con)
  local name = os.tmpname()
  touch(name)
  local tmpcon = assert(env:connect(name))
  assert(tmpcon:execute("create table series(time_stamp datetime,value float)"))
  for key,value in pairs(dataset) do
    assert(tmpcon:execute(string.format("INSERT INTO series(time_stamp,value) VALUES ('%s','%s')",value["t"],value["y"])))
  end
  local sql = "select rounded as t,avg(value) as y from (select substr(strftime('%Y-%m-%dT%H:%M',time_stamp),1,15)||'5:00' rounded,value from series) group by rounded order by rounded"
  results = run_sql(sql,tmpcon)
  tmpcon:close()
  os.remove(name)
  return results
end

function run_sql(sql,con)
  local result = {}

  cursor = assert(con:execute(sql))
  row = cursor:fetch ({}, "a")
  while row do
    result[#result+1] = row
    row = cursor:fetch ({}, "a")
  end
 
  return result
end

function get_list(day,con)
  if day == "-" then
    sql = string.format("SELECT DISTINCT sensor_id,sensor,param FROM log WHERE time_stamp>=datetime('now','-1 day','localtime') ORDER BY sensor_id,sensor,param")
  else
    sql = string.format("SELECT DISTINCT sensor_id,sensor,param FROM log WHERE time_stamp>='%s' and time_stamp<date('%s','+1 day') ORDER BY sensor_id,sensor,param",day,day)
  end  
  return run_sql(sql,con)
end

function get_raw(day,con,sensor_id,sensor_type,param)
  format = '%Y-%m-%dT%H:%M:%S'
  if day == "-" then
    sql = string.format("SELECT strftime('%s',time_stamp) as t,value as y FROM log WHERE time_stamp>=datetime('now','-1 day','localtime') and sensor_id='%s' and sensor='%s' and param='%s' ORDER BY time_stamp",format,sensor_id,sensor_type,param)
  else
    sql = string.format("SELECT strftime('%s',time_stamp) as t,value as y FROM log WHERE time_stamp>='%s' and time_stamp<date('%s','+1 day') and sensor_id='%s' and sensor='%s' and param='%s' ORDER BY time_stamp",format,day,day,sensor_id,sensor_type,param)
  end
  return run_sql(sql,con)
end

function get_filtered(day,con,sensor_id,sensor_type,param,width)
  format = '%Y-%m-%dT%H:%M:%S'
  if day == "-" then
    sql = string.format("SELECT strftime('%s',time_stamp) as t,value as y FROM log WHERE time_stamp>=datetime('now','-1 day','localtime') and sensor_id='%s' and sensor='%s' and param='%s' ORDER BY time_stamp",format,sensor_id,sensor_type,param)
  else
    sql = string.format("SELECT strftime('%s',time_stamp) as t,value as y FROM log WHERE time_stamp>='%s' and time_stamp<date('%s','+1 day') and sensor_id='%s' and sensor='%s' and param='%s' ORDER BY time_stamp",format,day,day,sensor_id,sensor_type,param)
  end
  return filter_data(run_sql(sql,con),width)
end

function dump_json(dataset,file)
  local f
  if file then
    f = io.open(file,"w")
    io.output(f)
  end
  io.write(json.encode(dataset))
  if f then
    io.close(f)
  end   
end

function dump_txt(dataset,file)
  local f
  if file then
    f = io.open(file,"w")
    io.output(f)
  end
  for key,row in pairs(dataset) do
    io.write(row["t"].." "..row["y"].."\n")
  end
  if f then
    io.close(f)
  end   
end

function dump_list(dataset,file)
  local f
  if file then
    f = io.open(file,"w")
    io.output(f)
  end
  for key,row in pairs(dataset) do
    io.write(row["sensor_id"].." "..row["sensor"].." "..row["param"].."\n")
  end
  if f then
    io.close(f)
  end   
end

local command = arg[2]
local day = arg[3]

local dbdriver = require "luasql.sqlite3"
env = assert(dbdriver.sqlite3())
con = assert(env:connect(logdb,'READONLY'))

if command == "list" then

  dump_list(get_list(day,con))

elseif command == "get" then

  sensor_id = arg[4]
  sensor_type = arg[5]
  param = arg[6]

  dump_txt(get_raw(day,con,sensor_id,sensor_type,param))
  
elseif command == "get-filtered" then

  sensor_id = arg[4]
  sensor_type = arg[5]
  param = arg[6]

  width = arg[7]
  if not width then
    width = 5
  end  

  dataset =get_filtered(day,con,sensor_id,sensor_type,param)
  dump_txt(dataset)

elseif command == "get-compacted" then

  sensor_id = arg[4]
  sensor_type = arg[5]
  param = arg[6]

  width = arg[7]
  if not width then
    width = 5
  end  

  dataset =get_filtered(day,con,sensor_id,sensor_type,param)
  dump_txt(average_results(dataset))

elseif command == "dump" then

  local directory = arg[4]
  if not directory then 
    directory = "."
  end  
  
  list = get_list(day,con)
  dump_json(list,directory.."/sensors.json")
  for key,value in pairs(list) do
    dump_json(get_filtered(day,con,value["sensor_id"],value["sensor"],value["param"]),directory.."/"..value["sensor_id"].."."..value["sensor"].."."..value["param"]..".json")
  end

elseif command == "dump-compacted" then

  local directory = arg[4]
  if not directory then 
    directory = "."
  end  
  
  list = get_list(day,con)
  dump_json(list,directory.."/sensors.json")
  for key,value in pairs(list) do
    dump_json(average_results(get_filtered(day,con,value["sensor_id"],value["sensor"],value["param"])),directory.."/"..value["sensor_id"].."."..value["sensor"].."."..value["param"]..".json")
  end
  
end