Use only one position per user

3 files changed, 96 insertions, 133 deletions

diff --git a/schema/init.sql b/schema/init.sql
index df76581..5f4fd4f 100644
--- a/schema/init.sql
+++ b/schema/init.sql
@@ -20,6 +20,8 @@ CREATE TABLE positions(
     PRIMARY KEY(id),
     CONSTRAINT fk_users_id
         FOREIGN KEY(user_id) REFERENCES users(id)
+        ON DELETE CASCADE,
+    CONSTRAINT unique_user_position UNIQUE(user_id)
 );
 
 CREATE TYPE level_alert AS ENUM ('One', 'Two', 'Three');
@@ -34,8 +36,9 @@ CREATE TABLE alerts(
     text3 text NOT NULL,
     reached_users INTEGER DEFAULT 0 NOT NULL,
     PRIMARY KEY(id),
-    CONSTRAINT fk_users_id
+    CONSTRAINT fk_users_ich 
         FOREIGN KEY(user_id) REFERENCES users(id)
+        ON DELETE CASCADE
 );
 
 CREATE TABLE notifications(
@@ -47,7 +50,9 @@ CREATE TABLE notifications(
     created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
     PRIMARY KEY(id),
     CONSTRAINT fk_alerts_id
-        FOREIGN KEY(alert_id) REFERENCES alerts(id),
+        FOREIGN KEY(alert_id) REFERENCES alerts(id)
+        ON DELETE CASCADE,
     CONSTRAINT fk_positions_id
         FOREIGN KEY(position_id) REFERENCES positions(id)
+        ON DELETE CASCADE
 );
diff --git a/src/graphql/query.rs b/src/graphql/query.rs
index 9e6e0c3..2ab26ec 100644
--- a/src/graphql/query.rs
+++ b/src/graphql/query.rs
@@ -46,43 +46,25 @@ impl Query {
         user::query::get_user_by_id(ctx, id).await
     }
 
-    /// Returns all the positions
+    /// Returns all the positions. It is restricted to admins only.
     ///
     /// Request example:
     /// ```text
     /// curl http://localhost:8000/graphql
     /// -H 'authorization: Bearer ***'
     /// -H 'content-type: application/json'
-    /// -d '{"query":"{positions {id, userId, createdAt, latitude, longitude, movingActivity}}"}'
+    /// -d '{"query":"{positions(movingActivity: IN_VEHICLE) {id, userId, createdAt, latitude, longitude, movingActivity}}"}'
     /// ```
     async fn positions<'ctx>(
         &self,
         ctx: &Context<'ctx>,
-        #[graphql(desc = "Filter by user id")] user_id: Option<i32>,
-        #[graphql(desc = "Limit results")] limit: Option<i64>,
-        #[graphql(desc = "Offset results")] offset: Option<i64>,
-    ) -> Result<Option<Vec<position::Position>>, AppError> {
-        position::query::get_positions(ctx, user_id, limit, offset).await
-    }
-
-    /// Returns all the last positions for each user.
-    /// It is restricted to only admin users.
-    ///
-    /// Request example:
-    /// ```text
-    /// curl http://localhost:8000/graphql
-    /// -H 'authorization: Bearer ***'
-    /// -H 'content-type: application/json'
-    /// -d '{"query":"lastPositions(movingActivity: IN_VEHICLE) {id, userId, createdAt, latitude, longitude, movingActivity}}"}'
-    /// ```
-    async fn last_positions<'ctx>(
-        &self,
-        ctx: &Context<'ctx>,
         #[graphql(desc = "Filter by moving activity")] moving_activity: Option<
             position::MovingActivity,
         >,
+        #[graphql(desc = "Limit results")] limit: Option<i64>,
+        #[graphql(desc = "Offset results")] offset: Option<i64>,
     ) -> Result<Option<Vec<position::Position>>, AppError> {
-        position::query::last_positions(ctx, moving_activity).await
+        position::query::get_positions(ctx, moving_activity, limit, offset).await
     }
 
     /// Returns all the positions
diff --git a/src/graphql/types/position.rs b/src/graphql/types/position.rs
index cbd0148..958edfd 100644
--- a/src/graphql/types/position.rs
+++ b/src/graphql/types/position.rs
@@ -6,7 +6,10 @@ use crate::{
 use async_graphql::{Context, Enum, FieldResult, InputObject, SimpleObject};
 use serde::{Deserialize, Serialize};
 use std::error::Error;
-use tokio_postgres::types::{to_sql_checked, FromSql, IsNull, ToSql, Type};
+use tokio_postgres::{
+    types::{to_sql_checked, FromSql, IsNull, ToSql, Type},
+    Client,
+};
 
 #[derive(Enum, Debug, Serialize, Deserialize, Clone, Copy, PartialEq, Eq)]
 /// Enumeration which refers to the kind of moving activity
@@ -82,16 +85,47 @@ pub struct PositionInput {
     pub moving_activity: MovingActivity,
 }
 
+/// Find a position with user_id = `id` using the PostgreSQL `client`
+pub async fn find_user_position(client: &Client, id: i32) -> Result<Position, AppError> {
+    let rows = client
+        .query(
+            "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
+            WHERE user_id = $1",
+            &[&id],
+        )
+        .await
+        .unwrap();
+
+    let positions: Vec<Position> = rows
+        .iter()
+        .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"),
+        })
+        .collect();
+
+    if positions.len() == 1 {
+        Ok(positions[0].clone())
+    } else {
+        Err(AppError::NotFound("Position".to_string()))
+    }
+}
+
 pub mod query {
     use super::*;
 
-    /// Get positions from the database
+    /// Get positions from the database for each user.
+    /// It is restricted to only admin users.
     pub async fn get_positions<'ctx>(
         ctx: &Context<'ctx>,
 
-        // Optional filter by user id. If not defined returns only available positions:
-        // If claimed user is admin returns everything, otherwise only positions linked to that user.
-        user_id: Option<i32>,
+        // Optional filter by moving activity
+        moving_activity: Option<MovingActivity>,
 
         // Optional limit results
         limit: Option<i64>,
@@ -105,7 +139,6 @@ pub mod query {
         match auth {
             Authentication::NotLogged => Err(AppError::Unauthorized),
             Authentication::Logged(claims) => {
-                let rows;
                 let limit = limit.unwrap_or(20);
                 let offset = offset.unwrap_or(0);
 
@@ -113,112 +146,38 @@ pub mod query {
                     .await
                     .expect("Should not be here");
 
-                if claim_user.is_admin {
-                    match user_id {
-                        Some(id) => {
-                            rows = client.query("
-                            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
-                            WHERE user_id = $1
-                            ORDER BY id DESC
-                            LIMIT $2
-                            OFFSET $3",
-                            &[&id, &limit, &offset]).await?;
-                        }
-                        None => {
-                            rows = client.query("
-                            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
-                            ORDER BY id DESC
-                            LIMIT $1
-                            OFFSET $2",
-                            &[&limit, &offset]).await?;
-                        }
-                    }
-                } else {
-                    rows = client.query("
-                    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
-                    WHERE user_id = $1
-                    ORDER BY id DESC
-                    LIMIT $2
-                    OFFSET $3",
-                    &[&claim_user.id, &limit, &offset]).await?;
-                }
-
-                let positions: Vec<Position> = rows
-                    .iter()
-                    .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"),
-                    })
-                    .collect();
-
-                Ok(Some(positions))
-            }
-        }
-    }
-
-    /// Get last positions from the database for each user.
-    /// It is restricted to only admin users.
-    pub async fn last_positions<'ctx>(
-        ctx: &Context<'ctx>,
-
-        // Optional filter by moving activity
-        moving_activity: Option<MovingActivity>,
-    ) -> Result<Option<Vec<Position>>, AppError> {
-        let state = ctx.data::<AppState>().expect("Can't connect to db");
-        let client = &*state.client;
-        let auth: &Authentication = ctx.data()?;
-        match auth {
-            Authentication::NotLogged => Err(AppError::Unauthorized),
-            Authentication::Logged(claims) => {
-                let claim_user = find_user(client, claims.user_id)
-                    .await
-                    .expect("Should not be here");
-
                 if !claim_user.is_admin {
                     return Err(AppError::Unauthorized);
                 }
 
                 let rows = client
-                        .query(
-                            "SELECT DISTINCT ON (user_id)
-                                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 ORDER BY user_id, created_at DESC",
-                            &[],
+                        .query("
+                            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
+                            LIMIT $1
+                            OFFSET $2
+                            ",
+                            &[&limit, &offset],
                         )
                         .await?;
 
-                let positions: Vec<Position> = match moving_activity {
-                    Some(activity) => rows
-                        .iter()
-                        .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"),
-                        })
+                let mapped_positions = rows.iter().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"),
+                });
+
+                let positions: Vec<Position>;
+                if let Some(activity) = moving_activity {
+                    positions = mapped_positions
                         .filter(|x| x.moving_activity == activity)
-                        .collect(),
-                    None => rows
-                        .iter()
-                        .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"),
-                        })
-                        .collect(),
-                };
+                        .collect();
+                } else {
+                    positions = mapped_positions.collect();
+                }
 
                 Ok(Some(positions))
             }
@@ -229,7 +188,8 @@ pub mod query {
 pub mod mutations {
     use super::*;
 
-    /// Create a new position for a logged user
+    /// Create a new position for a logged user. If a position already exists, just edit that
+    /// position.
     pub async fn new_position<'ctx>(
         ctx: &Context<'ctx>,
         input: PositionInput,
@@ -243,8 +203,24 @@ pub mod mutations {
                 Err(AppError::NotFound("Can't find the owner".to_string()).into())
             }
             Authentication::Logged(claims) => {
-                let rows = client
-                    .query(
+                let rows = if find_user_position(client, claims.user_id).await.is_ok() {
+                    client.query(
+                        "UPDATE positions SET
+                        location = ST_SetSRID(ST_MakePoint($1, $2), 4326),
+                        activity = $3
+                        WHERE user_id = $4
+                        RETURNING 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
+                        ",
+                        &[
+                            &input.longitude,
+                            &input.latitude,
+                            &input.moving_activity,
+                            &claims.user_id,
+                        ],
+                    )
+                    .await?
+                } else {
+                    client.query(
                         "INSERT INTO positions (user_id, location, activity)
                         VALUES (
                             $1,
@@ -260,7 +236,8 @@ pub mod mutations {
                             &input.moving_activity,
                         ],
                     )
-                    .await?;
+                    .await?
+                };
 
                 let positions: Vec<Position> = rows
                     .iter()
@@ -273,7 +250,6 @@ pub mod mutations {
                         moving_activity: row.get("activity"),
                     })
                     .collect();
-
                 Ok(positions[0].clone())
             }
         }