-- $Id$
-- $HeadURL$
--
-- Example:
--   sqlite3 ble_fieldtest_YYYYMMDD_HHMM.sqlite < create_exercise_26_ble_schema.sql
--
-- SQLite schema for Exercise 26 BLE Discovery field-test logs.
--
-- This schema targets the current 18-column Exercise 26 log format only.
--
-- Current Exercise 26 log format:
--
--   human_time,rx_epoch_ms,receiver,rx_lat,rx_lon,gps_fix_age_ms,
--   clock_valid,gps_valid,discipline_age_ms,last_discipline_epoch_ms,
--   heard,rssi,avg_rssi,age_s,count,seq,tx_payload_epoch_ms,payload
--
-- Current payload format:
--
--   B2|NODE|SEQ|TX_PAYLOAD_EPOCH_MS
--
-- Timestamp semantics:
--
--   rx_epoch_ms is the receiver's disciplined-clock time when the observation
--   row is written.
--
--   tx_payload_epoch_ms is the sender's disciplined-clock time when the BLE
--   advertising payload was generated/configured.  It is not a per-RF-packet
--   transmit timestamp.
--
--   rx_tx_payload_delta_ms is diagnostic only.  It is useful for clock,
--   scheduling, payload-age, and repeated-observation analysis.  It is not
--   BLE propagation delay.
--

PRAGMA foreign_keys = ON;

BEGIN TRANSACTION;

-- ---------------------------------------------------------------------------
-- trial
--
-- One row per field experiment.
--
-- A trial may contain multiple logs, usually one log per T-Beam receiver.
-- Example:
--   trial_label = peck_cottage_four_unit_20260526_1430
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS trial (
    trial_id              INTEGER PRIMARY KEY AUTOINCREMENT,

    trial_label           TEXT NOT NULL UNIQUE,
    trial_name            TEXT NOT NULL,

    field_site            TEXT,
    operator              TEXT,

    firmware_exercise     TEXT,
    firmware_git_commit   TEXT,

    trial_start_epoch_ms  INTEGER,
    trial_end_epoch_ms    INTEGER,

    notes                 TEXT,

    created_at_utc        TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

-- ---------------------------------------------------------------------------
-- unit
--
-- One row per named T-Beam unit encountered or described.
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS unit (
    unit_name             TEXT PRIMARY KEY,

    gnss_chip             TEXT,
    case_description      TEXT,
    antenna_notes         TEXT,
    battery_notes         TEXT,
    sd_notes              TEXT,
    hardware_notes        TEXT,

    created_at_utc        TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

-- ---------------------------------------------------------------------------
-- log_file
--
-- One row per imported physical log file.
--
-- sha256 prevents accidental duplicate ingestion.
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS log_file (
    log_file_id           INTEGER PRIMARY KEY AUTOINCREMENT,

    trial_id              INTEGER NOT NULL REFERENCES trial(trial_id) ON DELETE CASCADE,
    receiver              TEXT NOT NULL REFERENCES unit(unit_name),

    path                  TEXT NOT NULL,
    basename              TEXT NOT NULL,
    manifest_path         TEXT,

    sha256                TEXT NOT NULL UNIQUE,
    byte_count            INTEGER,
    source_row_count      INTEGER,

    first_rx_epoch_ms     INTEGER,
    last_rx_epoch_ms      INTEGER,

    receiver_role         TEXT,
    receiver_start_description TEXT,
    receiver_notes        TEXT,
    test_notes            TEXT,

    imported_at_utc       TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

CREATE INDEX IF NOT EXISTS idx_log_file_trial_id
ON log_file(trial_id);

CREATE INDEX IF NOT EXISTS idx_log_file_receiver
ON log_file(receiver);

-- ---------------------------------------------------------------------------
-- log_manifest_kv
--
-- Raw key/value manifest entries.
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS log_manifest_kv (
    manifest_kv_id        INTEGER PRIMARY KEY AUTOINCREMENT,

    log_file_id           INTEGER NOT NULL REFERENCES log_file(log_file_id) ON DELETE CASCADE,

    key                   TEXT NOT NULL,
    value                 TEXT,

    UNIQUE(log_file_id, key)
);

-- ---------------------------------------------------------------------------
-- ble_observation_raw
--
-- Faithful row-level import from the current 18-column CSV log.
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS ble_observation_raw (
    raw_id                INTEGER PRIMARY KEY AUTOINCREMENT,

    trial_id              INTEGER NOT NULL REFERENCES trial(trial_id) ON DELETE CASCADE,
    log_file_id           INTEGER NOT NULL REFERENCES log_file(log_file_id) ON DELETE CASCADE,

    source_line_no        INTEGER NOT NULL,

    human_time            TEXT,
    rx_epoch_ms           INTEGER,
    receiver              TEXT,
    rx_lat                REAL,
    rx_lon                REAL,
    gps_fix_age_ms        INTEGER,

    clock_valid           INTEGER,
    gps_valid             INTEGER,
    discipline_age_ms     INTEGER,
    last_discipline_epoch_ms INTEGER,

    heard                 TEXT,
    rssi                  INTEGER,
    avg_rssi              INTEGER,
    age_s                 INTEGER,
    count                 INTEGER,
    seq                   INTEGER,
    tx_payload_epoch_ms   INTEGER,
    payload               TEXT,

    UNIQUE(log_file_id, source_line_no)
);

CREATE INDEX IF NOT EXISTS idx_raw_trial_time
ON ble_observation_raw(trial_id, rx_epoch_ms);

CREATE INDEX IF NOT EXISTS idx_raw_receiver_heard_time
ON ble_observation_raw(receiver, heard, rx_epoch_ms);

CREATE INDEX IF NOT EXISTS idx_raw_rx_position
ON ble_observation_raw(rx_lat, rx_lon);

CREATE INDEX IF NOT EXISTS idx_raw_validity
ON ble_observation_raw(clock_valid, gps_valid, gps_fix_age_ms);

-- ---------------------------------------------------------------------------
-- ble_observation
--
-- Parsed and normalized BLE observation table.
--
-- rx_tx_payload_delta_ms is the receiver row timestamp minus the sender
-- payload-generation timestamp.  It is diagnostic only.
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS ble_observation (
    obs_id                INTEGER PRIMARY KEY AUTOINCREMENT,

    raw_id                INTEGER NOT NULL UNIQUE REFERENCES ble_observation_raw(raw_id) ON DELETE CASCADE,

    trial_id              INTEGER NOT NULL REFERENCES trial(trial_id) ON DELETE CASCADE,
    log_file_id           INTEGER NOT NULL REFERENCES log_file(log_file_id) ON DELETE CASCADE,

    source_line_no        INTEGER NOT NULL,

    rx_epoch_ms           INTEGER NOT NULL,
    rx_epoch_s            REAL NOT NULL,

    tx_payload_epoch_ms   INTEGER,
    tx_payload_epoch_s    REAL,

    rx_tx_payload_delta_ms INTEGER,

    receiver              TEXT NOT NULL REFERENCES unit(unit_name),
    heard                 TEXT NOT NULL REFERENCES unit(unit_name),

    rx_lat                REAL,
    rx_lon                REAL,
    gps_fix_age_ms        INTEGER,

    clock_valid           INTEGER,
    gps_valid             INTEGER,
    discipline_age_ms     INTEGER,
    last_discipline_epoch_ms INTEGER,

    rssi                  INTEGER,
    avg_rssi              INTEGER,
    age_s                 INTEGER,
    receiver_count        INTEGER,

    receiver_seq_field    INTEGER,

    payload_raw           TEXT,
    payload_kind          TEXT,
    payload_node          TEXT,
    payload_seq           INTEGER,
    payload_tx_payload_epoch_ms INTEGER,
    payload_legacy_uptime INTEGER,

    parse_warning         TEXT
);

CREATE INDEX IF NOT EXISTS idx_obs_trial_time
ON ble_observation(trial_id, rx_epoch_ms);

CREATE INDEX IF NOT EXISTS idx_obs_receiver_heard_time
ON ble_observation(receiver, heard, rx_epoch_ms);

CREATE INDEX IF NOT EXISTS idx_obs_heard_time
ON ble_observation(heard, rx_epoch_ms);

CREATE INDEX IF NOT EXISTS idx_obs_position
ON ble_observation(rx_lat, rx_lon);

CREATE INDEX IF NOT EXISTS idx_obs_rssi
ON ble_observation(receiver, heard, rssi);

CREATE INDEX IF NOT EXISTS idx_obs_validity
ON ble_observation(clock_valid, gps_valid, gps_fix_age_ms);

CREATE INDEX IF NOT EXISTS idx_obs_payload_time
ON ble_observation(tx_payload_epoch_ms, rx_tx_payload_delta_ms);

-- ---------------------------------------------------------------------------
-- v_log_file_summary
-- ---------------------------------------------------------------------------

CREATE VIEW IF NOT EXISTS v_log_file_summary AS
SELECT
    lf.log_file_id,
    t.trial_label,
    lf.receiver,
    lf.basename,
    lf.receiver_role,
    lf.source_row_count,
    lf.first_rx_epoch_ms,
    lf.last_rx_epoch_ms,
    ROUND((lf.last_rx_epoch_ms - lf.first_rx_epoch_ms) / 1000.0, 3) AS duration_s,
    lf.sha256,
    lf.imported_at_utc
FROM log_file lf
JOIN trial t ON t.trial_id = lf.trial_id;

-- ---------------------------------------------------------------------------
-- v_receiver_gps_summary
--
-- Per receiver GPS spread and validity summary in degrees.
-- Distance conversion remains outside SQLite.
-- ---------------------------------------------------------------------------

CREATE VIEW IF NOT EXISTS v_receiver_gps_summary AS
SELECT
    trial_id,
    log_file_id,
    receiver,

    COUNT(*) AS row_count,
    COUNT(DISTINCT printf('%.7f,%.7f', rx_lat, rx_lon)) AS distinct_coordinate_pairs,

    MIN(rx_epoch_ms) AS first_rx_epoch_ms,
    MAX(rx_epoch_ms) AS last_rx_epoch_ms,

    AVG(rx_lat) AS mean_rx_lat,
    AVG(rx_lon) AS mean_rx_lon,

    MIN(rx_lat) AS min_rx_lat,
    MAX(rx_lat) AS max_rx_lat,
    MIN(rx_lon) AS min_rx_lon,
    MAX(rx_lon) AS max_rx_lon,

    MAX(rx_lat) - MIN(rx_lat) AS lat_range_deg,
    MAX(rx_lon) - MIN(rx_lon) AS lon_range_deg,

    MIN(gps_fix_age_ms) AS min_gps_fix_age_ms,
    AVG(gps_fix_age_ms) AS avg_gps_fix_age_ms,
    MAX(gps_fix_age_ms) AS max_gps_fix_age_ms,

    MIN(clock_valid) AS min_clock_valid,
    MAX(clock_valid) AS max_clock_valid,
    MIN(gps_valid) AS min_gps_valid,
    MAX(gps_valid) AS max_gps_valid,

    MIN(discipline_age_ms) AS min_discipline_age_ms,
    AVG(discipline_age_ms) AS avg_discipline_age_ms,
    MAX(discipline_age_ms) AS max_discipline_age_ms,

    MIN(last_discipline_epoch_ms) AS min_last_discipline_epoch_ms,
    MAX(last_discipline_epoch_ms) AS max_last_discipline_epoch_ms
FROM ble_observation
GROUP BY trial_id, log_file_id, receiver;

-- ---------------------------------------------------------------------------
-- v_link_rssi_summary
--
-- Per receiver/heard link RSSI summary.
-- ---------------------------------------------------------------------------

CREATE VIEW IF NOT EXISTS v_link_rssi_summary AS
SELECT
    trial_id,
    receiver,
    heard,

    COUNT(*) AS observation_count,

    MIN(rx_epoch_ms) AS first_rx_epoch_ms,
    MAX(rx_epoch_ms) AS last_rx_epoch_ms,
    ROUND((MAX(rx_epoch_ms) - MIN(rx_epoch_ms)) / 1000.0, 3) AS duration_s,

    MIN(rssi) AS min_rssi,
    AVG(rssi) AS avg_rssi_raw,
    MAX(rssi) AS max_rssi,

    MIN(avg_rssi) AS min_rolling_avg_rssi,
    AVG(avg_rssi) AS avg_rolling_avg_rssi,
    MAX(avg_rssi) AS max_rolling_avg_rssi,

    MIN(payload_seq) AS min_payload_seq,
    MAX(payload_seq) AS max_payload_seq,

    MIN(clock_valid) AS min_clock_valid,
    MIN(gps_valid) AS min_gps_valid
FROM ble_observation
GROUP BY trial_id, receiver, heard;

-- ---------------------------------------------------------------------------
-- v_rx_tx_payload_timing_summary
--
-- Per receiver/heard timing-delta summary.
--
-- rx_tx_payload_delta_ms is useful for detecting clock disagreement,
-- scheduling, repeated payload observations, and payload-age behavior.
-- It is not RF propagation delay.
-- ---------------------------------------------------------------------------

CREATE VIEW IF NOT EXISTS v_rx_tx_payload_timing_summary AS
SELECT
    trial_id,
    receiver,
    heard,

    COUNT(*) AS observation_count,

    MIN(rx_tx_payload_delta_ms) AS min_rx_tx_payload_delta_ms,
    AVG(rx_tx_payload_delta_ms) AS avg_rx_tx_payload_delta_ms,
    MAX(rx_tx_payload_delta_ms) AS max_rx_tx_payload_delta_ms
FROM ble_observation
WHERE tx_payload_epoch_ms IS NOT NULL
  AND tx_payload_epoch_ms > 0
GROUP BY trial_id, receiver, heard;

-- ---------------------------------------------------------------------------
-- v_rx_tx_payload_delta_by_seq
-- ---------------------------------------------------------------------------

CREATE VIEW IF NOT EXISTS v_rx_tx_payload_delta_by_seq AS
SELECT
    trial_id,
    receiver,
    heard,
    payload_seq,
    COUNT(*) AS observations,
    MIN(rx_tx_payload_delta_ms) AS min_delta_ms,
    AVG(rx_tx_payload_delta_ms) AS avg_delta_ms,
    MAX(rx_tx_payload_delta_ms) AS max_delta_ms,
    MIN(rx_epoch_ms) AS first_rx_epoch_ms,
    MAX(rx_epoch_ms) AS last_rx_epoch_ms
FROM ble_observation
WHERE tx_payload_epoch_ms IS NOT NULL
  AND tx_payload_epoch_ms > 0
GROUP BY trial_id, receiver, heard, payload_seq;

-- ---------------------------------------------------------------------------
-- v_payload_seq_summary
-- ---------------------------------------------------------------------------

CREATE VIEW IF NOT EXISTS v_payload_seq_summary AS
SELECT
    trial_id,
    receiver,
    heard,
    MIN(payload_seq) AS min_payload_seq,
    MAX(payload_seq) AS max_payload_seq,
    COUNT(DISTINCT payload_seq) AS distinct_payload_seq,
    COUNT(*) AS observations,
    COUNT(*) - COUNT(DISTINCT payload_seq) AS repeated_seq_observations
FROM ble_observation
GROUP BY trial_id, receiver, heard;

-- ---------------------------------------------------------------------------
-- v_payload_seq_receive_spread
--
-- Shows how long the same advertised payload sequence was observed by a
-- receiver.  Useful for confirming repeated observation of one payload.
-- ---------------------------------------------------------------------------

CREATE VIEW IF NOT EXISTS v_payload_seq_receive_spread AS
SELECT
    trial_id,
    receiver,
    heard,
    payload_seq,
    COUNT(*) AS observations,
    MIN(rx_epoch_ms) AS first_rx_epoch_ms,
    MAX(rx_epoch_ms) AS last_rx_epoch_ms,
    MAX(rx_epoch_ms) - MIN(rx_epoch_ms) AS rx_spread_ms,
    MIN(rx_tx_payload_delta_ms) AS min_delta_ms,
    AVG(rx_tx_payload_delta_ms) AS avg_delta_ms,
    MAX(rx_tx_payload_delta_ms) AS max_delta_ms
FROM ble_observation
WHERE tx_payload_epoch_ms IS NOT NULL
  AND tx_payload_epoch_ms > 0
GROUP BY trial_id, receiver, heard, payload_seq;

-- ---------------------------------------------------------------------------
-- v_payload_tx_epoch_step
--
-- Shows elapsed sender payload-generation time between successive payload
-- sequence values as observed by each receiver/heard pair.
-- ---------------------------------------------------------------------------

CREATE VIEW IF NOT EXISTS v_payload_tx_epoch_step AS
WITH x AS (
    SELECT
        trial_id,
        receiver,
        heard,
        payload_seq,
        MIN(payload_tx_payload_epoch_ms) AS payload_tx_payload_epoch_ms
    FROM ble_observation
    WHERE payload_tx_payload_epoch_ms IS NOT NULL
      AND payload_tx_payload_epoch_ms > 0
    GROUP BY trial_id, receiver, heard, payload_seq
),
y AS (
    SELECT
        x.*,
        LAG(payload_tx_payload_epoch_ms) OVER (
            PARTITION BY trial_id, receiver, heard
            ORDER BY payload_seq
        ) AS prev_payload_tx_payload_epoch_ms
    FROM x
)
SELECT
    *,
    payload_tx_payload_epoch_ms - prev_payload_tx_payload_epoch_ms AS tx_payload_epoch_step_ms
FROM y
WHERE prev_payload_tx_payload_epoch_ms IS NOT NULL;

-- ---------------------------------------------------------------------------
-- v_map_observation_points
--
-- Mapping-oriented observation view.
-- ---------------------------------------------------------------------------

CREATE VIEW IF NOT EXISTS v_map_observation_points AS
SELECT
    obs_id,
    trial_id,
    log_file_id,
    source_line_no,
    receiver,
    heard,
    rx_epoch_ms,
    rx_epoch_s,
    rx_lat,
    rx_lon,
    gps_fix_age_ms,
    clock_valid,
    gps_valid,
    discipline_age_ms,
    last_discipline_epoch_ms,
    rssi,
    avg_rssi,
    payload_seq,
    tx_payload_epoch_ms,
    rx_tx_payload_delta_ms
FROM ble_observation
WHERE rx_lat IS NOT NULL
  AND rx_lon IS NOT NULL;

CREATE VIEW IF NOT EXISTS v_map_good_gps_points AS
SELECT *
FROM v_map_observation_points
WHERE clock_valid = 1
  AND gps_valid = 1
  AND gps_fix_age_ms <= 5000;

COMMIT;
