WITH asset_attrs AS (
  SELECT 
    name AS asset_name,
    (
      SELECT jsonb_object_agg(key, val->'value')
      FROM jsonb_each(attributes::jsonb) AS x(key, val)
      WHERE val->'value' IS NOT NULL
        AND key NOT IN ('notes', 'location', 'info', 'displayIndex', 'indexConfig')
    ) AS sensor_data,
    (
      SELECT to_timestamp(max((val->>'timestamp')::bigint) / 1000.0)
      FROM jsonb_each(attributes::jsonb) AS x(key, val)
      WHERE val->>'timestamp' IS NOT NULL AND val->>'timestamp' ~ '^[0-9]+$'
    ) AS attrs_timestamp
  FROM openremote.asset
  WHERE id = '4QJnSuo833PPbU5oGYIZus'
  ORDER BY version DESC LIMIT 1
),
latest_datapoints AS (
  SELECT DISTINCT ON (attribute_name)
    attribute_name,
    value,
    timestamp
  FROM openremote.asset_datapoint
  WHERE entity_id = '4QJnSuo833PPbU5oGYIZus'
  ORDER BY attribute_name, timestamp DESC
),
aggregated_datapoints AS (
  SELECT 
    COALESCE(jsonb_object_agg(attribute_name, value), '{}'::jsonb) AS sensor_data,
    max(timestamp) AS dp_timestamp
  FROM latest_datapoints
)
SELECT 
  asset_attrs.asset_name,
  NOW() + INTERVAL '3 hours' AS local_time,
  (COALESCE(asset_attrs.sensor_data, '{}'::jsonb) || 
   COALESCE(aggregated_datapoints.sensor_data, '{}'::jsonb) ||
   CASE 
     WHEN aggregated_datapoints.dp_timestamp IS NOT NULL AND asset_attrs.attrs_timestamp IS NOT NULL THEN
       jsonb_build_object('timestamp', GREATEST(aggregated_datapoints.dp_timestamp, asset_attrs.attrs_timestamp))
     WHEN aggregated_datapoints.dp_timestamp IS NOT NULL THEN
       jsonb_build_object('timestamp', aggregated_datapoints.dp_timestamp)
     WHEN asset_attrs.attrs_timestamp IS NOT NULL THEN
       jsonb_build_object('timestamp', asset_attrs.attrs_timestamp)
     ELSE
       '{}'::jsonb
   END
  ) AS sensor_data
FROM asset_attrs
CROSS JOIN aggregated_datapoints