feat: impl. use of new tables

server/src/controller/ttnController.ts

@@ -15,7 +15,6 @@ const ttnGatewayReceptionService = container.resolve(
   TtnGatewayReceptionService
 );
 const wifiScanService = container.resolve(WifiScanService);
-
 const locationService = container.resolve(LocationService);
 
 const router = express.Router();
@@ -59,8 +58,6 @@ router.post(
             rssi: w.rssi,
           })) ?? [];
 
-      console.log(wifiScans);
-
       const ttnGatewayReceptions = message.uplink_message.rx_metadata.map(
         (g) => ({
           lp_ttn_end_device_uplinks_id,
@@ -83,9 +80,8 @@ router.post(
 
         locationService.createLocationFromTriangulation({
           lp_ttn_end_device_uplinks_id,
-          wifi: wifiResults.map(({ latitude, longitude, rssi }) => ({
-            latitude,
-            longitude,
+          wifi: wifiResults.map(({ mac, rssi }) => ({
+            mac,
             rssi,
           })),
           ttn_gw: gatewayResults.map(({ latitude, longitude, rssi }) => ({

server/src/models/wifiLocation.ts

@@ -21,8 +21,8 @@ WifiLocation.init(
       allowNull: false,
     },
     longitude: {
-        type: DataTypes.NUMBER,
-        allowNull: false,
+      type: DataTypes.NUMBER,
+      allowNull: false,
     },
     created_at_utc: {
       type: DataTypes.DATE,

server/src/models/wifiLocationHistory.ts

@@ -27,8 +27,8 @@ WifiLocationHistory.init(
       allowNull: false,
     },
     longitude: {
-        type: DataTypes.NUMBER,
-        allowNull: false,
+      type: DataTypes.NUMBER,
+      allowNull: false,
     },
     created_at_utc: {
       type: DataTypes.DATE,
@@ -43,8 +43,8 @@ WifiLocationHistory.init(
   },
   {
     sequelize,
-    modelName: "WifiLocation",
-    tableName: "wifi_location",
+    modelName: "WifiLocationHistory",
+    tableName: "wifi_location_history",
     timestamps: false,
   }
 );

server/src/repositories/wifiLocationHistoryRepository.ts

@@ -1,3 +1,4 @@
+import { Attributes, FindOptions } from "sequelize";
 import { injectable } from "tsyringe";
 import { WifiLocationHistory } from "../models/wifiLocationHistory";
 
@@ -7,6 +8,10 @@ export class WifiLocationHistoryRepository {
     return await WifiLocationHistory.findAll();
   }
 
+  public async findOne(options?: FindOptions<Attributes<WifiLocationHistory>>) {
+    return await WifiLocationHistory.findOne(options);
+  }
+
   public async findById(id: string) {
     return await WifiLocationHistory.findByPk(id);
   }

server/src/repositories/wifiLocationRepository.ts

@@ -1,10 +1,11 @@
+import { Attributes, FindOptions } from "sequelize";
 import { injectable } from "tsyringe";
 import { WifiLocation } from "../models/wifiLocation";
 
 @injectable()
 export class WifiLocationRepository {
-  public async findAll() {
-    return await WifiLocation.findAll();
+  public async findAll(options?: FindOptions<Attributes<WifiLocation>>) {
+    return await WifiLocation.findAll(options);
   }
 
   public async findById(id: string) {

server/src/services/locationService.ts

@@ -1,6 +1,7 @@
 import { inject, injectable } from "tsyringe";
 import { Location } from "../models/location";
 import { LocationRepository } from "../repositories/locationRepository";
+import { WifiLocationService } from "./wifiLocationService";
 
 interface CreateLocationParams {
   lp_ttn_end_device_uplinks_id: string;
@@ -11,7 +12,10 @@ interface CreateLocationParams {
 
 interface CreateLocationTriangulationParams {
   lp_ttn_end_device_uplinks_id: string;
-  wifi: LocationSignal[];
+  wifi: {
+    mac: string;
+    rssi: number;
+  }[];
   ttn_gw: LocationSignal[];
   gnss?: Coordinates;
 }
@@ -39,7 +43,9 @@ interface UpdateTtnGatewayReceptionParams {
 export class LocationService {
   constructor(
     @inject(LocationRepository)
-    private repository: LocationRepository
+    private repository: LocationRepository,
+    @inject(WifiLocationService)
+    private wifiLocationService: WifiLocationService
   ) {}
 
   public async getAllLocations() {
@@ -65,7 +71,9 @@ export class LocationService {
   public async createLocationFromTriangulation(
     data: CreateLocationTriangulationParams
   ) {
-    const wifi_location = this.calculateVirtualLocation(data.wifi);
+    const wifi_location = this.calculateVirtualLocation(
+      await this.enrichWifiObjectWithLocation(data.wifi)
+    );
     const gateway_location = this.calculateVirtualLocation(data.ttn_gw);
 
     return this.createLocation({
@@ -84,6 +92,29 @@ export class LocationService {
     return this.repository.delete(id);
   }
 
+  private async enrichWifiObjectWithLocation(
+    wifis: CreateLocationTriangulationParams["wifi"]
+  ) {
+    const enrichedWifi = await Promise.all(
+      wifis.map(async (wifi) => {
+        const location = await this.wifiLocationService.getWifiLocationByMac(
+          wifi.mac
+        );
+
+        return location?.latitude !== undefined &&
+          location.longitude !== undefined
+          ? {
+              rssi: wifi.rssi,
+              latitude: location.latitude,
+              longitude: location.longitude,
+            }
+          : null;
+      })
+    );
+
+    return enrichedWifi.filter((wifi) => wifi !== null);
+  }
+
   private calculateVirtualLocation(locations: LocationSignal[]) {
     if (locations.length === 0) return undefined;
 

server/src/services/wifiLocationHistoryService.ts

@@ -1,6 +1,4 @@
 import { inject, injectable } from "tsyringe";
-import { WifiScanRepository } from "../repositories/wifiScanRepository";
-import { WifiLocationRepository } from "../repositories/wifiLocationRepository";
 import { WifiLocationHistoryRepository } from "../repositories/wifiLocationHistoryRepository";
 
 interface CreateWifiLocationHistoryParams {
@@ -18,7 +16,8 @@ interface UpdateWifiLocationHistoryParams {
 @injectable()
 export class WifiLocationHistoryService {
   constructor(
-    @inject(WifiLocationHistoryRepository) private repository: WifiLocationHistoryRepository
+    @inject(WifiLocationHistoryRepository)
+    private repository: WifiLocationHistoryRepository
   ) {}
 
   public async getAllWifiLocationHistories() {
@@ -29,11 +28,28 @@ export class WifiLocationHistoryService {
     return this.repository.findById(id);
   }
 
-  public async createWifiLocationHistory(data: CreateWifiLocationHistoryParams) {
-      return this.repository.create(data);
+  public async createWifiLocationHistory(
+    data: CreateWifiLocationHistoryParams
+  ) {
+    const existingEntry = await this.repository.findOne({
+      where: {
+        mac: data.mac,
+        latitude: data.latitude,
+        longitude: data.longitude,
+      },
+      order: [["updated_at_utc", "DESC"]],
+    });
+
+    if (!existingEntry) {
+      return await this.repository.create(data);
+    }
+
+    return existingEntry;
   }
 
-  public async updateWifiLocationHistory(data: UpdateWifiLocationHistoryParams) {
+  public async updateWifiLocationHistory(
+    data: UpdateWifiLocationHistoryParams
+  ) {
     return this.repository.update(data.mac, data);
   }
 

server/src/services/wifiLocationService.ts

@@ -1,12 +1,8 @@
+import { Op } from "sequelize";
 import { inject, injectable } from "tsyringe";
-import { WifiScanRepository } from "../repositories/wifiScanRepository";
+import { getLocationForWifi } from "../proxy/wigle";
 import { WifiLocationRepository } from "../repositories/wifiLocationRepository";
-
-interface CreateWifiLocationParams {
-  mac: string;
-  latitude: number;
-  longitude: number;
-}
+import { WifiLocationHistoryService } from "./wifiLocationHistoryService";
 
 interface UpdateWifiLocationParams {
   mac: string;
@@ -17,23 +13,43 @@ interface UpdateWifiLocationParams {
 @injectable()
 export class WifiLocationService {
   constructor(
-    @inject(WifiLocationRepository) private repository: WifiLocationRepository
+    @inject(WifiLocationRepository) private repository: WifiLocationRepository,
+    @inject(WifiLocationHistoryService)
+    private wifiLocationHistory: WifiLocationHistoryService
   ) {}
 
   public async getAllWifiLocations() {
     return this.repository.findAll();
   }
 
-  public async getWifiLocationById(id: string) {
-    return this.repository.findById(id);
+  public async getAllWifiLocationsByAddresses(macAddresses: string[]) {
+    return this.repository.findAll({
+      where: { mac: { [Op.in]: macAddresses } },
+    });
   }
 
-  public async createWifiLocation(data: CreateWifiLocationParams) {
-      return this.repository.create(data);
-  }
+  public async getWifiLocationByMac(mac: string) {
+    let wifiLocation = await this.repository.findById(mac);
 
-  public async createWifiLocations(data: CreateWifiLocationParams[]) {
-    return await this.repository.createMany(data);
+    if (wifiLocation?.longitude && wifiLocation.latitude) return wifiLocation;
+
+    const apiLocation = await getLocationForWifi(mac);
+
+    if (apiLocation && apiLocation.totalResults >= 0) {
+      wifiLocation = await this.repository.create({
+        mac,
+        latitude: apiLocation.results[0].trilat,
+        longitude: apiLocation.results[0].trilong,
+      });
+
+      await this.wifiLocationHistory.createWifiLocationHistory(
+        wifiLocation.dataValues
+      );
+
+      return wifiLocation;
+    }
+
+    return undefined;
   }
 
   public async updateWifiLocation(data: UpdateWifiLocationParams) {