Implements event database operations

1 files changed, 207 insertions, 2 deletions

diff --git a/rust/scraper/src/database/postgres.rs b/rust/scraper/src/database/postgres.rs
index 3361308..ea082c7 100644
--- a/rust/scraper/src/database/postgres.rs
+++ b/rust/scraper/src/database/postgres.rs
@@ -1,9 +1,11 @@
 use std::mem;
+use std::time::Duration;
 use crate::database::error::DatabaseError;
 use chrono::{DateTime, NaiveDate, TimeZone, Utc};
-use proto::touchpad::common::v1::{Gender, SwimMeet, Swimmer, Team};
-use sqlx::postgres::{PgPool, PgPoolOptions};
+use proto::touchpad::common::v1::{Event, EventTime, EventTimeResult, Gender, individual_event, IndividualEvent, relay_event, RelayEvent, Stroke, SwimMeet, Swimmer, Team};
+use sqlx::postgres::{PgPool, PgPoolOptions, PgQueryResult};
 use proto::ProtoTimestamp;
+use proto::touchpad::common::v1;
 
 pub struct PostgresClient {
     pool: PgPool,
@@ -166,6 +168,98 @@ WHERE meets.id = $1;
 
         Ok(())
     }
+
+    async fn get_event(&self, id: u32) -> crate::database::Result<Event> {
+        let event: Vec<GetEventSchema> = sqlx::query_as!(
+            GetEventSchema,
+"
+SELECT *
+FROM events e
+LEFT JOIN timings t on e.id = t.event_id
+WHERE e.id = $1;
+",
+            id as i32,
+        )
+            .fetch_all(&self.pool)
+            .await?;
+
+        db_event_to_proto(event)
+    }
+
+    async fn get_event_by_number(&self, meet_id: u32, number: u32) -> crate::database::Result<Event> {
+        let event: Vec<GetEventSchema> = sqlx::query_as!(
+            GetEventSchema,
+"
+SELECT *
+FROM events e
+LEFT JOIN timings t on e.id = t.event_id
+WHERE e.meet_id = $1 AND e.event_num = $2;
+",
+            meet_id as i32,
+            number as i32,
+        )
+            .fetch_all(&self.pool)
+            .await?;
+
+        db_event_to_proto(event)
+    }
+
+    async fn add_event(&self, event: &Event) -> crate::database::Result<()> {
+        let mut tx = self.pool.begin().await?;
+
+        let stroke = Stroke::from_i32(event.stroke)
+            .ok_or(DatabaseError::BadInput("invalid stroke".into()))?;
+        let gender = Gender::from_i32(event.gender)
+            .ok_or(DatabaseError::BadInput("invalid gender".into()))?
+            .to_iso_5218();
+
+        sqlx::query!(
+            "INSERT INTO events VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9);",
+            event.id as i32,
+            event.meet_id as i32,
+            event.event_num as i16,
+            event.age_lo as i16,
+            event.age_hi as i16,
+            event.distance as i32,
+            stroke as i16,
+            gender as i16,
+            event.is_relay
+        )
+            .execute(&mut tx)
+            .await?;
+
+        match &event.event {
+            Some(v1::event::Event::Individual(ev)) => {
+                for t in &ev.times {
+                    if let Some(time) = &t.time {
+                        insert_event_timing(
+                            &mut tx,
+                            time,
+                            None,
+                            Some(t.swimmer_id),
+                        ).await?;
+                    }
+                }
+            }
+            Some(v1::event::Event::Relay(ev)) => {
+                for t in &ev.times {
+                    if let Some(time) = &t.time {
+                        insert_event_timing(
+                            &mut tx,
+                            time,
+                            Some(&t.name),
+                            None,
+                        ).await?;
+                    }
+                }
+            }
+            None => {}
+        }
+
+        tx.commit().await?;
+
+        Ok(())
+    }
 }
 
 struct SwimmersTableSchema {
@@ -186,3 +280,114 @@ struct GetMeetSchema {
     team_id: i32,
     team_name: String,
 }
+
+struct GetEventSchema {
+    id: i32,
+    meet_id: i32,
+    event_num: i16,
+    age_lo: i16,
+    age_hi: i16,
+    distance: i32,
+    stroke: i16,
+    gender: i16,
+    is_relay: bool,
+
+    name: Option<String>,
+    swimmer_id: Option<i32>,
+    event_id: i32,
+    team_id: i32,
+    heat: i16,
+    lane: i16,
+    result: i16,
+    final_time: Option<i64>,
+    seed_time: Option<i64>,
+    rank: i16,
+    points: f32,
+}
+
+fn db_event_to_proto(event: Vec<GetEventSchema>) -> crate::database::Result<Event> {
+    let first = event.first().ok_or(DatabaseError::NotFound)?;
+
+    let mut proto_event = Event {
+        id: first.event_id as u32,
+        meet_id: first.meet_id as u32,
+        event_num: first.event_num as u32,
+        age_lo: first.age_lo as u32,
+        age_hi: first.age_hi as u32,
+        distance: first.distance as u32,
+        stroke: Stroke::from_i32(first.stroke as i32).unwrap_or_default() as i32,
+        gender: Gender::from_iso_5218(first.stroke as u8) as i32,
+        is_relay: first.is_relay,
+        event: None,
+    };
+
+    if proto_event.is_relay {
+        proto_event.event = Some(v1::event::Event::Relay(RelayEvent {
+            times: event.into_iter()
+                .map(|mut e| relay_event::RelayEventTime {
+                    name: e.name.take().unwrap_or_default(),
+                    time: Some(create_proto_ev_time(&e)),
+                })
+                .collect()
+        }));
+    } else {
+        proto_event.event = Some(v1::event::Event::Individual(IndividualEvent {
+            times: event.into_iter()
+                .map(|e| individual_event::IndividualEventTime {
+                    swimmer_id: e.swimmer_id.unwrap_or_default() as u32,
+                    time: Some(create_proto_ev_time(&e)),
+                })
+                .collect()
+        }))
+    }
+
+    Ok(proto_event)
+}
+
+fn create_proto_ev_time(e: &GetEventSchema) -> EventTime {
+    EventTime {
+        event_id: e.event_id as u32,
+        team_id: e.team_id as u32,
+        heat: e.heat as u32,
+        lane: e.lane as u32,
+        result: EventTimeResult::from_i32(e.result as i32).unwrap_or_default() as i32,
+        time: e.final_time.map(|t| Duration::from_millis(t as u64).into()),
+        seed: e.seed_time.map(|t| Duration::from_millis(t as u64).into()),
+        rank: e.rank as i32,
+        points: e.points,
+    }
+}
+
+async fn insert_event_timing(
+    ex: impl sqlx::PgExecutor<'_>,
+    time: &EventTime,
+    name: Option<&str>,
+    swimmer_id: Option<u32>,
+) -> Result<PgQueryResult, sqlx::Error> {
+    let result = EventTimeResult::from_i32(time.result)
+        .unwrap_or_default();
+
+    let final_time: Option<i64> = time.time.clone()
+        .and_then(|t| t.try_into().ok())
+        .and_then(|t: Duration| t.as_millis().try_into().ok());
+    let seed_time: Option<i64> = time.seed.clone()
+        .and_then(|t| t.try_into().ok())
+        .and_then(|t: Duration| t.as_millis().try_into().ok());
+
+    sqlx::query!(
+        "INSERT INTO timings VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11);",
+        name,
+        swimmer_id.map(|id| id as i32),
+        time.event_id as i32,
+        time.team_id as i32,
+        time.heat as i16,
+        time.lane as i16,
+        result as i16,
+        time.rank as i32,
+        time.points,
+        final_time,
+        seed_time,
+    )
+        .execute(ex)
+        .await
+}
\ No newline at end of file