feat: db tables and repo and service written

server/sql/tables.sql

@@ -16,13 +16,28 @@ CREATE TABLE IF NOT EXISTS wifi_scan (
     lp_ttn_end_device_uplinks_id UUID,
     mac VARCHAR(255),
     rssi NUMERIC,
-    latitude DOUBLE NOT NULL,
-    longitude DOUBLE NOT NULL,
     created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     FOREIGN KEY (lp_ttn_end_device_uplinks_id) REFERENCES lp_ttn_end_device_uplinks(lp_ttn_end_device_uplinks_id)
 );
 
+CREATE TABLE IF NOT EXISTS wifi_location (
+    mac VARCHAR(255) PRIMARY KEY,
+    latitude DOUBLE NOT NULL,
+    longitude DOUBLE NOT NULL,
+    created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS wifi_location_history (
+    wifi_location_history_id UUID PRIMARY KEY,
+    mac VARCHAR(255),
+    latitude DOUBLE NOT NULL,
+    longitude DOUBLE NOT NULL,
+    created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+);
+
 CREATE TABLE IF NOT EXISTS ttn_gateway_reception (
     ttn_gateway_reception_id UUID PRIMARY KEY,
     lp_ttn_end_device_uplinks_id UUID,

server/src/models/wifiLocation.ts

@@ -0,0 +1,44 @@
+import { DataTypes, Model } from "sequelize";
+import { sequelize } from "../database/database";
+
+export class WifiLocation extends Model {
+  public mac!: string;
+  public latitude!: number;
+  public longitude!: number;
+  public created_at_utc!: Date;
+  public updated_at_utc!: Date;
+}
+
+WifiLocation.init(
+  {
+    mac: {
+      type: DataTypes.STRING,
+      primaryKey: true,
+      allowNull: false,
+    },
+    latitude: {
+      type: DataTypes.NUMBER,
+      allowNull: false,
+    },
+    longitude: {
+        type: DataTypes.NUMBER,
+        allowNull: false,
+    },
+    created_at_utc: {
+      type: DataTypes.DATE,
+      defaultValue: DataTypes.NOW,
+      allowNull: false,
+    },
+    updated_at_utc: {
+      type: DataTypes.DATE,
+      defaultValue: DataTypes.NOW,
+      allowNull: false,
+    },
+  },
+  {
+    sequelize,
+    modelName: "WifiLocation",
+    tableName: "wifi_location",
+    timestamps: false,
+  }
+);

server/src/models/wifiLocationHistory.ts

@@ -0,0 +1,50 @@
+import { DataTypes, Model } from "sequelize";
+import { sequelize } from "../database/database";
+
+export class WifiLocationHistory extends Model {
+  public wifi_location_history_id!: string;
+  public mac!: string;
+  public latitude!: number;
+  public longitude!: number;
+  public created_at_utc!: Date;
+  public updated_at_utc!: Date;
+}
+
+WifiLocationHistory.init(
+  {
+    wifi_location_history_id: {
+      type: DataTypes.UUID,
+      defaultValue: DataTypes.UUIDV4,
+      primaryKey: true,
+      allowNull: false,
+    },
+    mac: {
+      type: DataTypes.STRING,
+      allowNull: false,
+    },
+    latitude: {
+      type: DataTypes.NUMBER,
+      allowNull: false,
+    },
+    longitude: {
+        type: DataTypes.NUMBER,
+        allowNull: false,
+    },
+    created_at_utc: {
+      type: DataTypes.DATE,
+      defaultValue: DataTypes.NOW,
+      allowNull: false,
+    },
+    updated_at_utc: {
+      type: DataTypes.DATE,
+      defaultValue: DataTypes.NOW,
+      allowNull: false,
+    },
+  },
+  {
+    sequelize,
+    modelName: "WifiLocation",
+    tableName: "wifi_location",
+    timestamps: false,
+  }
+);

server/src/models/wifiScan.ts

@@ -6,8 +6,6 @@ export class WifiScan extends Model {
   public wifi_scan_id!: string;
   public mac!: string;
   public rssi!: number;
-  public latitude!: number;
-  public longitude!: number;
   public created_at_utc!: Date;
   public updated_at_utc!: Date;
 }
@@ -32,14 +30,6 @@ WifiScan.init(
       type: DataTypes.NUMBER,
       allowNull: false,
     },
-    latitude: {
-      type: DataTypes.NUMBER,
-      allowNull: false,
-    },
-    longitude: {
-      type: DataTypes.NUMBER,
-      allowNull: false,
-    },
     created_at_utc: {
       type: DataTypes.DATE,
       defaultValue: DataTypes.NOW,

server/src/repositories/wifiLocationHistoryRepository.ts

@@ -0,0 +1,38 @@
+import { injectable } from "tsyringe";
+import { WifiLocationHistory } from "../models/wifiLocationHistory";
+
+@injectable()
+export class WifiLocationHistoryRepository {
+  public async findAll() {
+    return await WifiLocationHistory.findAll();
+  }
+
+  public async findById(id: string) {
+    return await WifiLocationHistory.findByPk(id);
+  }
+
+  public async create(data: Partial<WifiLocationHistory>) {
+    return await WifiLocationHistory.create(data);
+  }
+
+  public async createMany(data: Partial<WifiLocationHistory>[]) {
+    return await WifiLocationHistory.bulkCreate(data);
+  }
+
+  public async update(id: string, data: Partial<WifiLocationHistory>) {
+    const wifiScan = await this.findById(id);
+    if (wifiScan) {
+      return await wifiScan.update(data);
+    }
+    return null;
+  }
+
+  public async delete(id: string) {
+    const wifiScan = await this.findById(id);
+    if (wifiScan) {
+      await wifiScan.destroy();
+      return true;
+    }
+    return false;
+  }
+}

server/src/repositories/wifiLocationRepository.ts

@@ -0,0 +1,38 @@
+import { injectable } from "tsyringe";
+import { WifiLocation } from "../models/wifiLocation";
+
+@injectable()
+export class WifiLocationRepository {
+  public async findAll() {
+    return await WifiLocation.findAll();
+  }
+
+  public async findById(id: string) {
+    return await WifiLocation.findByPk(id);
+  }
+
+  public async create(data: Partial<WifiLocation>) {
+    return await WifiLocation.create(data);
+  }
+
+  public async createMany(data: Partial<WifiLocation>[]) {
+    return await WifiLocation.bulkCreate(data);
+  }
+
+  public async update(id: string, data: Partial<WifiLocation>) {
+    const wifiScan = await this.findById(id);
+    if (wifiScan) {
+      return await wifiScan.update(data);
+    }
+    return null;
+  }
+
+  public async delete(id: string) {
+    const wifiScan = await this.findById(id);
+    if (wifiScan) {
+      await wifiScan.destroy();
+      return true;
+    }
+    return false;
+  }
+}

server/src/services/wifiLocationHistoryService.ts

@@ -0,0 +1,43 @@
+import { inject, injectable } from "tsyringe";
+import { WifiScanRepository } from "../repositories/wifiScanRepository";
+import { WifiLocationRepository } from "../repositories/wifiLocationRepository";
+import { WifiLocationHistoryRepository } from "../repositories/wifiLocationHistoryRepository";
+
+interface CreateWifiLocationHistoryParams {
+  mac: string;
+  latitude: number;
+  longitude: number;
+}
+
+interface UpdateWifiLocationHistoryParams {
+  mac: string;
+  latitude: number;
+  longitude: number;
+}
+
+@injectable()
+export class WifiLocationHistoryService {
+  constructor(
+    @inject(WifiLocationHistoryRepository) private repository: WifiLocationHistoryRepository
+  ) {}
+
+  public async getAllWifiLocationHistories() {
+    return this.repository.findAll();
+  }
+
+  public async getWifiLocationHistoryById(id: string) {
+    return this.repository.findById(id);
+  }
+
+  public async createWifiLocationHistory(data: CreateWifiLocationHistoryParams) {
+      return this.repository.create(data);
+  }
+
+  public async updateWifiLocationHistory(data: UpdateWifiLocationHistoryParams) {
+    return this.repository.update(data.mac, data);
+  }
+
+  public async deleteWifiLocationHistory(id: string) {
+    return this.repository.delete(id);
+  }
+}

server/src/services/wifiLocationService.ts

@@ -0,0 +1,46 @@
+import { inject, injectable } from "tsyringe";
+import { WifiScanRepository } from "../repositories/wifiScanRepository";
+import { WifiLocationRepository } from "../repositories/wifiLocationRepository";
+
+interface CreateWifiLocationParams {
+  mac: string;
+  latitude: number;
+  longitude: number;
+}
+
+interface UpdateWifiLocationParams {
+  mac: string;
+  latitude: number;
+  longitude: number;
+}
+
+@injectable()
+export class WifiLocationService {
+  constructor(
+    @inject(WifiLocationRepository) private repository: WifiLocationRepository
+  ) {}
+
+  public async getAllWifiLocations() {
+    return this.repository.findAll();
+  }
+
+  public async getWifiLocationById(id: string) {
+    return this.repository.findById(id);
+  }
+
+  public async createWifiLocation(data: CreateWifiLocationParams) {
+      return this.repository.create(data);
+  }
+
+  public async createWifiLocations(data: CreateWifiLocationParams[]) {
+    return await this.repository.createMany(data);
+  }
+
+  public async updateWifiLocation(data: UpdateWifiLocationParams) {
+    return this.repository.update(data.mac, data);
+  }
+
+  public async deleteWifiLocation(id: string) {
+    return this.repository.delete(id);
+  }
+}

server/src/services/wifiScanService.ts

@@ -1,5 +1,4 @@
 import { inject, injectable } from "tsyringe";
-import { getLocationForWifiMemoized } from "../proxy/wigle";
 import { WifiScanRepository } from "../repositories/wifiScanRepository";
 
 interface CreateWifiScanParams {
@@ -12,8 +11,6 @@ interface UpdateWifiScanParams {
   wifi_scan_id: string;
   mac?: string;
   rssi?: number;
-  latitude?: number;
-  longitude?: number;
 }
 
 @injectable()
@@ -31,23 +28,11 @@ export class WifiScanService {
   }
 
   public async createWifiScan(data: CreateWifiScanParams) {
-    const apiResponse = await getLocationForWifiMemoized(data.mac);
-
-    if (apiResponse !== undefined && apiResponse.results.length > 0)
-      return this.repository.create({
-        ...data,
-        latitude: apiResponse.results[0].trilat,
-        longitude: apiResponse.results[0].trilong,
-      });
+      return this.repository.create(data);
   }
 
   public async createWifiScans(data: CreateWifiScanParams[]) {
-    let wifiScans = await Promise.all(
-      data.map(async (wifi) => {
-        return await this.createWifiScan(wifi);
-      })
-    );
-    return wifiScans.filter((wifi) => wifi !== undefined);
+    return await this.repository.createMany(data);
   }
 
   public async updateWifiScan(data: UpdateWifiScanParams) {