#!/bin/bash

sqlite3 /usr/local/rs485/data.db <<EOF | while IFS='|' read -r dev_eui payload_start payload_length decode_start decode_length downlink_start downlink_length decode_key fport; do
-- 连接downlink数据库
ATTACH DATABASE '/usr/local/rs485/downlink.db' AS downlink_db;

-- 查询合并数据
SELECT 
  l.dev_eui,
  l.register_start AS payload_start,
  l.register_length AS payload_length,
  dm.register_start AS decode_start,
  dm.register_length AS decode_length,
  dl.register_start AS downlink_start,
  dl.register_length AS downlink_length,
  dm.type AS decode_key,
  l.fport
FROM listening l
LEFT JOIN decode_modbus dm ON l.dev_eui = dm.dev_eui
LEFT JOIN downlink_db.downlink_setting dl ON l.dev_eui = dl.dev_eui
WHERE l.dev_eui IS NOT NULL

UNION

-- 只在decode_modbus表中的设备
SELECT 
  dm.dev_eui,
  NULL AS payload_start,
  NULL AS payload_length,
  dm.register_start AS decode_start,
  dm.register_length AS decode_length,
  dl.register_start AS downlink_start,
  dl.register_length AS downlink_length,
  dm.type AS decode_key,
  NULL AS fport
FROM decode_modbus dm
LEFT JOIN listening l ON dm.dev_eui = l.dev_eui
LEFT JOIN downlink_db.downlink_setting dl ON dm.dev_eui = dl.dev_eui
WHERE l.dev_eui IS NULL;
EOF
    # 格式化输出JSON
    if [ ! -z "$dev_eui" ]; then
        json="{ 'device_eui': '$dev_eui', 'decode_key': '$decode_key', 'payload_start': '${payload_start:-}', 'payload_length': '${payload_length:-}', 'decode_start': '${decode_start:-}', 'decode_length': '${decode_length:-}', 'downlink_start': '${downlink_start:-}', 'downlink_length': '${downlink_length:-}'"
        if [ ! -z "$fport" ]; then
            json="$json, 'filter_fport': '$fport'"
        fi
        json="$json },"
        echo "$json"
    fi
done
