Save notification's position info

Since we save the last user's position only, we can't be able to link a
notification to a position. So, the better thing is to save a redundant lat/lng

4 files changed, 98 insertions, 99 deletions

diff --git a/schema/init.sql b/schema/init.sql
index f6a1fbd..68f1f92 100644
--- a/schema/init.sql
+++ b/schema/init.sql
@@ -44,7 +44,9 @@ CREATE TABLE alerts(
 CREATE TABLE notifications(
     id SERIAL NOT NULL,
     alert_id INTEGER NOT NULL,
-    position_id INTEGER NOT NULL,
+    user_id INTEGER NOT NULL,
+    location GEOGRAPHY(Point, 4326) NOT NULL,
+    activity moving_activity NOT NULL,
     seen BOOLEAN DEFAULT false,
     level level_alert NOT NULL,
     created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
@@ -52,7 +54,7 @@ CREATE TABLE notifications(
     CONSTRAINT fk_alerts_id
         FOREIGN KEY(alert_id) REFERENCES alerts(id)
         ON DELETE CASCADE,
-    CONSTRAINT fk_positions_id
-        FOREIGN KEY(position_id) REFERENCES positions(id)
+    CONSTRAINT fk_users_id
+        FOREIGN KEY(user_id) REFERENCES users(id)
         ON DELETE CASCADE
 );
diff --git a/src/graphql/query.rs b/src/graphql/query.rs
index 96b8a5b..63ca49c 100644
--- a/src/graphql/query.rs
+++ b/src/graphql/query.rs
@@ -93,13 +93,10 @@ impl Query {
     /// curl http://localhost:8000/graphql
     /// -H 'authorization: Bearer ***'
     /// -H 'content-type: application/json'
-    /// -d '{"query":"{notifications(seen: false alertId: 1) {
+    /// -d '{"query":"{notifications {
     /// id,
     /// alert { id, userId, createdAt, area, areaLevel2, areaLevel3, text1, text2, text3, reachedUsers },
-    /// position {id, userId, createdAt, latitude, longitude, movingActivity},
-    /// seen,
-    /// level,
-    /// createdAt
+    /// userId, latitude, longitude, movingActivity, level, seen, createdAt
     /// }}"}'
     /// ```
     async fn notifications<'ctx>(
diff --git a/src/graphql/types/alert.rs b/src/graphql/types/alert.rs
index 6d27626..007f471 100644
--- a/src/graphql/types/alert.rs
+++ b/src/graphql/types/alert.rs
@@ -74,17 +74,17 @@ pub mod query {
                         client
                             .query(
                                 "SELECT id,
-                            user_id,
-                            extract(epoch from created_at)::double precision as created_at,
-                            ST_AsText(area) as area,
-                            ST_AsText(ST_Buffer(area::geography, 1000)) as area_level2,
-                            ST_AsText(ST_Buffer(area::geography, 2000)) as area_level3,
-                            text1,
-                            text2,
-                            text3,
-                            reached_users
-                    FROM alerts
-                    WHERE id = $1",
+                                    user_id,
+                                    extract(epoch from created_at)::double precision as created_at,
+                                    ST_AsText(area) as area,
+                                    ST_AsText(ST_Buffer(area::geography, 1000)) as area_level2,
+                                    ST_AsText(ST_Buffer(area::geography, 2000)) as area_level3,
+                                    text1,
+                                    text2,
+                                    text3,
+                                    reached_users
+                                FROM alerts
+                                WHERE id = $1",
                                 &[&id],
                             )
                             .await?
@@ -93,19 +93,19 @@ pub mod query {
                         client
                             .query(
                                 "SELECT id,
-                        user_id,
-                        extract(epoch from created_at)::double precision as created_at,
-                        ST_AsText(area) as area,
-                        ST_AsText(ST_Buffer(area::geography, 1000)) as area_level2,
-                        ST_AsText(ST_Buffer(area::geography, 2000)) as area_level3,
-                        text1,
-                        text2,
-                        text3,
-                        reached_users
-                    FROM alerts
-                    ORDER BY id DESC
-                    LIMIT $1
-                    OFFSET $2",
+                                    user_id,
+                                    extract(epoch from created_at)::double precision as created_at,
+                                    ST_AsText(area) as area,
+                                    ST_AsText(ST_Buffer(area::geography, 1000)) as area_level2,
+                                    ST_AsText(ST_Buffer(area::geography, 2000)) as area_level3,
+                                    text1,
+                                    text2,
+                                    text3,
+                                    reached_users
+                                FROM alerts
+                                ORDER BY id DESC
+                                LIMIT $1
+                                OFFSET $2",
                                 &[&limit.unwrap_or(20), &offset.unwrap_or(0)],
                             )
                             .await?
@@ -135,7 +135,7 @@ pub mod query {
 }
 
 pub mod mutations {
-    use crate::audio;
+    use crate::{audio, graphql::types::position::Position};
 
     use super::*;
 
@@ -238,13 +238,13 @@ pub mod mutations {
                     },
                 ];
 
-                let mut positions: Vec<i32> = vec![];
+                let mut alerted_positions: Vec<i32> = vec![];
 
                 // Send notifications for each available level
                 for level in levels {
-                    let position_ids: Vec<i32> = client
+                    let positions: Vec<Position> = client
                         .query(
-                            "SELECT id
+                            "SELECT id, user_id, extract(epoch from created_at)::double precision as created_at, ST_Y(location::geometry) AS latitude, ST_X(location::geometry) AS longitude, activity
                             FROM positions p
                             WHERE ST_DWithin(
                                     p.location::geography,
@@ -260,16 +260,23 @@ pub mod mutations {
                         )
                         .await?
                         .iter()
-                        .map(|row| row.get(0))
-                        .filter(|id| !positions.contains(id))
+                        .map(|row| Position {
+                            id: row.get("id"),
+                            user_id: row.get("user_id"),
+                            created_at: row.get::<_, f64>("created_at") as i64,
+                            latitude: row.get("latitude"),
+                            longitude: row.get("longitude"),
+                            moving_activity: row.get("activity"),
+                        })
+                        .filter(|p| !alerted_positions.contains(&p.id))
                         .collect();
 
                     let mut notification_ids = vec![];
-                    for id in &position_ids {
+                    for p in &positions {
                         let notification = Notification::insert_db(
                             client,
                             alert.id,
-                            *id,
+                            &p,
                             LevelAlert::from_str(level.text).unwrap(),
                         )
                         .await?;
@@ -277,25 +284,19 @@ pub mod mutations {
                     }
 
                     alert.reached_users += notification_ids.len() as i32;
-                    let placeholders: Vec<String> = (1..=position_ids.len())
-                        .map(|i| format!("${}", i))
-                        .collect();
+                    // Users placeholders
+                    let placeholders: Vec<String> =
+                        positions.iter().map(|p| format!("{}", p.user_id)).collect();
 
                     if !placeholders.is_empty() {
                         let query = format!(
-                            "SELECT DISTINCT u.notification_token FROM positions p JOIN users u ON u.id = p.user_id
-                            WHERE p.id IN ({}) AND notification_token IS NOT NULL",
+                            "SELECT DISTINCT u.notification_token FROM users u
+                            WHERE u.id IN ({}) AND notification_token IS NOT NULL",
                             placeholders.join(", ")
                         );
 
                         let tokens: Vec<String> = client
-                            .query(
-                                &query,
-                                &position_ids
-                                    .iter()
-                                    .map(|id| id as &(dyn tokio_postgres::types::ToSql + Sync))
-                                    .collect::<Vec<&(dyn tokio_postgres::types::ToSql + Sync)>>(),
-                            )
+                            .query(&query, &[])
                             .await?
                             .iter()
                             .map(|row| {
@@ -316,7 +317,7 @@ pub mod mutations {
                         .await?;
                     }
 
-                    positions.extend(position_ids);
+                    alerted_positions.extend(positions.iter().map(|p| p.id).collect::<Vec<i32>>());
                 }
 
                 client
diff --git a/src/graphql/types/notification.rs b/src/graphql/types/notification.rs
index 88860a7..fc71c11 100644
--- a/src/graphql/types/notification.rs
+++ b/src/graphql/types/notification.rs
@@ -1,6 +1,11 @@
 use crate::{
     errors::AppError,
-    graphql::types::{alert::Alert, jwt::Authentication, position::Position, user::find_user},
+    graphql::types::{
+        alert::Alert,
+        jwt::Authentication,
+        position::{MovingActivity, Position},
+        user::find_user,
+    },
     state::AppState,
 };
 use async_graphql::{Context, Enum, FieldResult, InputObject, SimpleObject};
@@ -77,7 +82,10 @@ impl ToSql for LevelAlert {
 pub struct Notification {
     pub id: i32,
     pub alert: Alert,
-    pub position: Position,
+    pub user_id: i32,
+    pub latitude: f64,
+    pub longitude: f64,
+    pub moving_activity: MovingActivity,
     pub seen: bool,
     pub level: LevelAlert,
     pub created_at: i64,
@@ -91,21 +99,28 @@ pub struct NotificationUpdateInput {
 }
 
 impl Notification {
-    /// Create a new notification into the database from an alert_id and a position_id.
+    /// Create a new notification into the database from an alert_id and a position.
     /// Returns the new ID.
     pub async fn insert_db(
         client: &Client,
         alert_id: i32,
-        position_id: i32,
+        position: &Position,
         level: LevelAlert,
     ) -> Result<i32, AppError> {
         match client
             .query(
-                "INSERT INTO notifications(alert_id, position_id, level)
-                VALUES($1, $2, $3)
+                "INSERT INTO notifications(alert_id, user_id, location, activity, level)
+                VALUES($1, $2, ST_SetSRID(ST_MakePoint($3, $4), 4326), $5, $6)
                 RETURNING id
                 ",
-                &[&alert_id, &position_id, &level],
+                &[
+                    &alert_id,
+                    &position.user_id,
+                    &position.longitude,
+                    &position.latitude,
+                    &position.moving_activity,
+                    &level,
+                ],
             )
             .await
         {
@@ -155,10 +170,13 @@ pub mod query {
 
                 let base_query = "SELECT n.id,
                                 n.alert_id,
-                                n.position_id,
                                 n.seen,
                                 n.level,
                                 extract(epoch from n.created_at)::double precision as created_at,
+                                ST_Y(n.location::geometry) AS latitude,
+                                ST_X(n.location::geometry) AS longitude,
+                                n.activity,
+                                n.user_id,
                                 a.id as alert_id,
                                 a.user_id as alert_user_id,
                                 extract(epoch from a.created_at)::double precision as alert_created_at,
@@ -168,16 +186,9 @@ pub mod query {
                                 a.text1 as alert_text1,
                                 a.text2 as alert_text2,
                                 a.text3 as alert_text3,
-                                a.reached_users as alert_reached_users,
-                                p.id as position_id,
-                                p.user_id as position_user_id,
-                                extract(epoch from p.created_at)::double precision as position_created_at,
-                                ST_Y(p.location::geometry) AS position_latitude,
-                                ST_X(p.location::geometry) AS position_longitude,
-                                p.activity as position_activity
+                                a.reached_users as alert_reached_users
                         FROM notifications n
-                        JOIN alerts a ON n.alert_id = a.id
-                        JOIN positions p ON n.position_id = p.id".to_string();
+                        JOIN alerts a ON n.alert_id = a.id".to_string();
 
                 let base_query = match id {
                     Some(idn) => format!("{} WHERE n.id = {}", base_query, idn),
@@ -200,7 +211,7 @@ pub mod query {
                 Some (ida) =>
                     client
                     .query(&format!(
-                        "{base_query} AND p.user_id = $1 AND n.alert_id = $2 ORDER BY n.id DESC LIMIT $3 OFFSET $4",
+                        "{base_query} AND n.user_id = $1 AND n.alert_id = $2 ORDER BY n.id DESC LIMIT $3 OFFSET $4",
                     ), &[&claim_user.id, &ida, &limit, &offset])
                     .await?,
                 None if claim_user.is_admin => client
@@ -211,7 +222,7 @@ pub mod query {
                     .await?,
                 None =>
                     client.query(
-                        &format!("{base_query} AND p.user_id = $1 ORDER BY n.id DESC LIMIT $2 OFFSET $3"),
+                        &format!("{base_query} AND n.user_id = $1 ORDER BY n.id DESC LIMIT $2 OFFSET $3"),
                         &[&claim_user.id, &limit, &offset],
                     )
                     .await?,
@@ -233,16 +244,12 @@ pub mod query {
                             text3: row.get("alert_text3"),
                             reached_users: row.get("alert_reached_users"),
                         },
-                        position: Position {
-                            id: row.get("position_id"),
-                            user_id: row.get("position_user_id"),
-                            created_at: row.get::<_, f64>("position_created_at") as i64,
-                            latitude: row.get("position_latitude"),
-                            longitude: row.get("position_longitude"),
-                            moving_activity: row.get("position_activity"),
-                        },
                         seen: row.get("seen"),
                         level: row.get("level"),
+                        user_id: row.get("user_id"),
+                        latitude: row.get("latitude"),
+                        longitude: row.get("longitude"),
+                        moving_activity: row.get("activity"),
                         created_at: row.get::<_, f64>("created_at") as i64,
                     })
                     .collect();
@@ -273,10 +280,13 @@ pub mod mutations {
 
                 let notification = client.query("SELECT n.id,
                                 n.alert_id,
-                                n.position_id,
-                                n.level,
                                 n.seen,
+                                n.level,
                                 extract(epoch from n.created_at)::double precision as created_at,
+                                ST_Y(n.location::geometry) AS latitude,
+                                ST_X(n.location::geometry) AS longitude,
+                                n.activity,
+                                n.user_id,
                                 a.id as alert_id,
                                 a.user_id as alert_user_id,
                                 extract(epoch from a.created_at)::double precision as alert_created_at,
@@ -286,16 +296,9 @@ pub mod mutations {
                                 a.text1 as alert_text1,
                                 a.text2 as alert_text2,
                                 a.text3 as alert_text3,
-                                a.reached_users as alert_reached_users,
-                                p.id as position_id,
-                                p.user_id as position_user_id,
-                                extract(epoch from p.created_at)::double precision as position_created_at,
-                                ST_Y(p.location::geometry) AS position_latitude,
-                                ST_X(p.location::geometry) AS position_longitude,
-                                p.activity as position_activity
+                                a.reached_users as alert_reached_users
                         FROM notifications n
                         JOIN alerts a ON n.alert_id = a.id
-                        JOIN positions p ON n.position_id = p.id
                         WHERE n.id = $1
                         ",
                        &[&input.id])
@@ -315,16 +318,12 @@ pub mod mutations {
                             text3: row.get("alert_text3"),
                             reached_users: row.get("alert_reached_users"),
                         },
-                        position: Position {
-                            id: row.get("position_id"),
-                            user_id: row.get("position_user_id"),
-                            created_at: row.get::<_, f64>("position_created_at") as i64,
-                            latitude: row.get("position_latitude"),
-                            longitude: row.get("position_longitude"),
-                            moving_activity: row.get("position_activity"),
-                        },
                         seen: row.get("seen"),
                         level: row.get("level"),
+                        user_id: row.get("user_id"),
+                        latitude: row.get("latitude"),
+                        longitude: row.get("longitude"),
+                        moving_activity: row.get("activity"),
                         created_at: row.get::<_, f64>("created_at") as i64,
                     })
                     .collect::<Vec<Notification>>()
@@ -332,7 +331,7 @@ pub mod mutations {
                     .cloned()
                     .ok_or_else(|| AppError::NotFound("Notification".to_string()))?;
 
-                if notification.position.user_id != user.id {
+                if notification.user_id != user.id {
                     return Err(AppError::NotFound("Notification".to_string()).into());
                 }