Method upsert_bluetooth

wardriving.py:512–621

Insert or update a discovered Bluetooth device. GPS position logic: - best_lat/best_lon: position at strongest RSSI (closest sighting) - latitude/longitude: confirmed position, updated only after GPS_CONFIRM_PINGS consecutive pings at a new location (>GPS_DRIFT_THR

(self, mac, name, rssi, device_type, lat, lon, alt)

                                        longitude = COALESCE(longitude, ?),
                                        altitude = COALESCE(altitude, ?),
                                        best_lat = COALESCE(best_lat, ?),
                                        best_lon = COALESCE(best_lon, ?)
                                    WHERE bssid = ?
                                """, (ssid, security, rssi, now, count,
                                      lat, lon, alt, lat, lon, bssid))
                            else:
                                conn.execute("""
                                    UPDATE networks SET
                                        ssid = COALESCE(NULLIF(?, ''), ssid),
                                        security = COALESCE(NULLIF(?, ''), security),
                                        rssi = ?, last_seen = ?, scan_count = ?
                                    WHERE bssid = ?
                                """, (ssid, security, rssi, now, count, bssid))
                    else:
                        conn.execute("""
                            INSERT INTO networks
                                (bssid, ssid, security, channel, frequency, rssi,
                                 latitude, longitude, altitude, speed_kmh, hdop,
                                 first_seen, last_seen, best_rssi, best_lat, best_lon,
                                 scan_count, interface, band, is_camera)
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, ?, ?, ?)
                        """, (bssid, ssid, security, channel, frequency, rssi,
                              lat, lon, alt, speed, hdop,
                              now, now, rssi, lat, lon, interface, band, is_camera))
                        self.unique_bssids.add(bssid)
                        self.network_count = len(self.unique_bssids)
                        return True

                    # Per-interface observation row — records what THIS adapter
                    # actually saw, independent of which adapter "owns" the
                    # canonical networks row. Used for antenna-coverage stats.
                    if interface:
                        obs = conn.execute(
                            "SELECT best_rssi, scan_count FROM network_observations "
                            "WHERE bssid = ? AND interface = ?",
                            (bssid, interface)
                        ).fetchone()
                        if obs:
                            obs_best = obs[0] if obs[0] is not None else -100
                            obs_count = (obs[1] or 0) + 1
                            new_best = max(obs_best, rssi)
                            conn.execute(
                                "UPDATE network_observations SET "
                                "last_seen = ?, last_rssi = ?, best_rssi = ?, "
                                "scan_count = ? WHERE bssid = ? AND interface = ?",
                                (now, rssi, new_best, obs_count, bssid, interface)
                            )
                        else:
                            conn.execute(
                                "INSERT INTO network_observations "
                                "(bssid, interface, first_seen, last_seen, "
                                "best_rssi, last_rssi, scan_count) "
                                "VALUES (?, ?, ?, ?, ?, ?, 1)",
                                (bssid, interface, now, now, rssi, rssi)
                            )

            except Exception as e:
                logger.error(f"DB upsert error: {e}")