-- Copyright © 2025-26 l5yth & contributors
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.

CREATE TABLE IF NOT EXISTS telemetry (
    id                      INTEGER PRIMARY KEY,
    node_id                 TEXT,
    node_num                INTEGER,
    from_id                 TEXT,
    to_id                   TEXT,
    rx_time                 INTEGER NOT NULL,
    rx_iso                  TEXT NOT NULL,
    telemetry_time          INTEGER,
    channel                 INTEGER,
    portnum                 TEXT,
    hop_limit               INTEGER,
    snr                     REAL,
    rssi                    INTEGER,
    bitfield                INTEGER,
    payload_b64             TEXT,
    battery_level           REAL,
    voltage                 REAL,
    channel_utilization     REAL,
    air_util_tx             REAL,
    uptime_seconds          INTEGER,
    temperature             REAL,
    relative_humidity       REAL,
    barometric_pressure     REAL,
    gas_resistance          REAL,
    current                 REAL,
    iaq                     INTEGER,
    distance                REAL,
    lux                     REAL,
    white_lux               REAL,
    ir_lux                  REAL,
    uv_lux                  REAL,
    wind_direction          INTEGER,
    wind_speed              REAL,
    weight                  REAL,
    wind_gust               REAL,
    wind_lull               REAL,
    radiation               REAL,
    rainfall_1h             REAL,
    rainfall_24h            REAL,
    soil_moisture           INTEGER,
    soil_temperature        REAL,
    ingestor                TEXT,
    protocol                TEXT NOT NULL DEFAULT 'meshtastic',
    telemetry_type          TEXT,
    -- PowerMetrics (TI-A2): per-channel INA voltage/current readings.
    ch1_voltage             REAL,
    ch1_current             REAL,
    ch2_voltage             REAL,
    ch2_current             REAL,
    ch3_voltage             REAL,
    ch3_current             REAL,
    ch4_voltage             REAL,
    ch4_current             REAL,
    ch5_voltage             REAL,
    ch5_current             REAL,
    ch6_voltage             REAL,
    ch6_current             REAL,
    ch7_voltage             REAL,
    ch7_current             REAL,
    ch8_voltage             REAL,
    ch8_current             REAL,
    -- AirQualityMetrics (TI-A2): PM mass/particle series, CO2, formaldehyde, VOC/NOx.
    pm10_standard           INTEGER,
    pm25_standard           INTEGER,
    pm100_standard          INTEGER,
    pm40_standard           INTEGER,
    pm10_environmental      INTEGER,
    pm25_environmental      INTEGER,
    pm100_environmental     INTEGER,
    particles_03um          INTEGER,
    particles_05um          INTEGER,
    particles_10um          INTEGER,
    particles_25um          INTEGER,
    particles_40um          INTEGER,
    particles_50um          INTEGER,
    particles_100um         INTEGER,
    particles_tps           REAL,
    co2                     INTEGER,
    co2_temperature         REAL,
    co2_humidity            REAL,
    form_formaldehyde       REAL,
    form_humidity           REAL,
    form_temperature        REAL,
    pm_temperature          REAL,
    pm_humidity             REAL,
    pm_voc_idx              REAL,
    pm_nox_idx              REAL,
    -- HealthMetrics (TI-A2): body sensors; kept apart from ambient temperature.
    heart_bpm               INTEGER,
    spo2                    INTEGER,
    health_temperature      REAL,
    -- LocalStats (TI-A2): radio/network counters.
    num_packets_tx          INTEGER,
    num_packets_rx          INTEGER,
    num_packets_rx_bad      INTEGER,
    num_online_nodes        INTEGER,
    num_total_nodes         INTEGER,
    num_rx_dupe             INTEGER,
    num_tx_relay            INTEGER,
    num_tx_relay_canceled   INTEGER,
    heap_total_bytes        INTEGER,
    heap_free_bytes         INTEGER,
    num_tx_dropped          INTEGER,
    noise_floor             INTEGER,
    -- HostMetrics (TI-A2): companion-host gauges.
    freemem_bytes           INTEGER,
    diskfree1_bytes         INTEGER,
    diskfree2_bytes         INTEGER,
    diskfree3_bytes         INTEGER,
    load1                   INTEGER,
    load5                   INTEGER,
    load15                  INTEGER,
    user_string             TEXT,
    -- TrafficManagementStats (TI-A2): router traffic counters.
    packets_inspected       INTEGER,
    position_dedup_drops    INTEGER,
    nodeinfo_cache_hits     INTEGER,
    rate_limit_drops        INTEGER,
    unknown_packet_drops    INTEGER,
    hop_exhausted_packets   INTEGER,
    router_hops_preserved   INTEGER,
    -- EnvironmentMetrics repeated one-wire probe list (JSON float array).
    one_wire_temperature    TEXT
);

CREATE INDEX IF NOT EXISTS idx_telemetry_rx_time ON telemetry(rx_time);
CREATE INDEX IF NOT EXISTS idx_telemetry_node_id ON telemetry(node_id);
CREATE INDEX IF NOT EXISTS idx_telemetry_time ON telemetry(telemetry_time);