-- Flattens SenFlat v2 into WIDE rows for the PostgreSQL output in "One row per result item" mode.
-- One row per (source, time); columns = metrics. History messages (h=1, point series [[dt, value], ...]) are included:
-- points with the same time are merged into one row. Change the METRICS list to match your table columns.
-- Extra columns: device_id, t_ms (epoch milliseconds), time_trusted, is_history (h=1), q.
local METRICS = {
  "temp_inlet", "t_out", "voltage", "current", "power_draw", "energy", "frequency",
  "power_factor", "door_front_open", "door_rear_open", "do1", "do2", "do3",
  "alarm_level", "uptime", "heap_free",
}

function logicFunction()
  local p = message.payload
  if type(p) ~= "table" or p.f ~= "flat" or p.v ~= 2 or type(p.s) ~= "table" then
    return nil                                  -- not SenFlat v2: not forwarded
  end
  local now = message.created * 1000            -- receive time at the broker (ms)
  local rows, index = {}, {}

  for _, src in ipairs(p.s) do
    local m = src.m
    if type(m) == "table" and src.id ~= nil and type(src.t0) == "number" then
      -- Collect points: { {dt, name, value}, ... }. A scalar = one point at dt 0.
      local points, dtmax = {}, 0
      for _, name in ipairs(METRICS) do
        local v = m[name]
        if type(v) == "table" then
          for _, pt in ipairs(v) do
            points[#points + 1] = { pt[1], name, pt[2] }
            if pt[1] > dtmax then dtmax = pt[1] end
          end
        elseif v ~= nil then
          points[#points + 1] = { 0, name, v }
        end
      end

      -- The source clock is trusted when ts is not 0 and every point is within -1 year to +1 day of the receive time.
      -- Otherwise the time is estimated from the receive time: time = receive - (dt_max - dt), the same as the server rule.
      local trusted = src.ts ~= 0
      if trusted then
        for _, pt in ipairs(points) do
          local t = src.t0 + pt[1]
          if t < now - 31536000000 or t > now + 86400000 then
            trusted = false
            break
          end
        end
      end

      for _, pt in ipairs(points) do
        local t
        if trusted then
          t = src.t0 + pt[1]
        else
          t = now - (dtmax - pt[1])
        end
        local key = src.id .. "|" .. string.format("%.0f", t)
        local r = index[key]
        if r == nil then
          r = { device_id = src.id, t_ms = t, time_trusted = trusted, is_history = (p.h == 1), q = p.q }
          index[key] = r
          rows[#rows + 1] = r
        end
        r[pt[2]] = pt[3]
      end
    end
  end

  table.sort(rows, function(a, b)
    if a.device_id ~= b.device_id then return a.device_id < b.device_id end
    return a.t_ms < b.t_ms
  end)
  return rows
end
return logicFunction()