myheats

commit 52ea1bcf0aecaba35259cf03a69c82118e14b60b
parent 5b452279c155f41a94395f4858d8378437149bed
Author: Andreas Gruhler <andreas.gruhler@adfinis.com>
Date:   Tue, 14 Mar 2023 18:06:10 +0100

feat: rating ui for judges

Diffstat:
M
README.md
 | 
9
+++++++--
M
schema/ratings.sql
 | 
24
+++++++++++++++++++-----
M
src/Leaderboard.js
 | 
9
+++++----
M
src/Rate.js
 | 
87
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-

4 files changed, 117 insertions(+), 12 deletions(-)
diff --git a/README.md b/README.md
@@ -18,6 +18,11 @@ The Supabase schema is stored in the `schema` folder and can be created using pl
 
 ![supabase-schema](supabase-schema.png)
 
+To update the schema from the current database, use (example for table `startlist`):
+```bash
+pg_dump -h db.aaxkgqazjhwumoljibld.supabase.co -U postgres -t 'public.startlist' --schema-only > schema/startlist.sql
+```
+
 Additionally, following views and functions are required:
 
 ```sql
@@ -101,4 +106,4 @@ To export data to local csv:
 
 * Magic link TTL
 * Rating UI
-* New start list, create from existing runs
-\ No newline at end of file
+* Delete/Edit heats
+\ No newline at end of file
diff --git a/schema/ratings.sql b/schema/ratings.sql
@@ -27,10 +27,10 @@ SET default_table_access_method = heap;
 CREATE TABLE public.ratings (
     id bigint NOT NULL,
     created_at timestamp with time zone DEFAULT now(),
-    athlete bigint,
-    judge uuid,
+    athlete bigint NOT NULL,
+    judge uuid NOT NULL,
     rating double precision,
-    heat bigint
+    heat bigint NOT NULL
 );
 
 
@@ -58,11 +58,11 @@ ALTER TABLE public.ratings ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY 
 
 
 --
--- Name: ratings rating_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
+-- Name: ratings ratings_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
 --
 
 ALTER TABLE ONLY public.ratings
-    ADD CONSTRAINT rating_pkey PRIMARY KEY (id);
+    ADD CONSTRAINT ratings_pkey PRIMARY KEY (athlete, judge, heat);
 
 
 --
@@ -90,6 +90,20 @@ ALTER TABLE ONLY public.ratings
 
 
 --
+-- Name: ratings Enable insert for authenticated users only; Type: POLICY; Schema: public; Owner: postgres
+--
+
+CREATE POLICY "Enable insert for authenticated users only" ON public.ratings FOR INSERT TO authenticated WITH CHECK (true);
+
+
+--
+-- Name: ratings Enable judges to update their own ratings; Type: POLICY; Schema: public; Owner: postgres
+--
+
+CREATE POLICY "Enable judges to update their own ratings" ON public.ratings FOR UPDATE USING ((auth.uid() = judge));
+
+
+--
 -- Name: ratings Enable read access for all users; Type: POLICY; Schema: public; Owner: postgres
 --
 
diff --git a/src/Leaderboard.js b/src/Leaderboard.js
@@ -2,7 +2,7 @@ import { supabase } from './supabaseClient'
 import { Fragment, useEffect, useState, useRef } from 'react'
 import Select from 'react-select'
 
-async function getStartlistForHeats(heatIds) {
+export async function getStartlistForHeats(heatIds) {
   return supabase.rpc('distinct_startlist', { 'heat_ids': heatIds })
 }
 
@@ -134,7 +134,7 @@ function Leaderboard() {
   const selectHeatRef = useRef();
 
   // add options to select or rank by heat
-  let heatOpts = heats.map(h => {
+  const heatOpts = heats.map(h => {
     return {
       value: h.id,
       label: h.name
@@ -142,7 +142,7 @@ function Leaderboard() {
   })
 
   // add options to rank by best/worst heat
-  let rankOpts = heatOpts.concat([
+  const rankOpts = heatOpts.concat([
     {
       value: 'best',
       label: 'Best Heat'
@@ -273,4 +273,4 @@ function Leaderboard() {
   );
 }
 
-export default Leaderboard;
+export default Leaderboard;
+\ No newline at end of file
diff --git a/src/Rate.js b/src/Rate.js
@@ -1,11 +1,96 @@
-import { lazy } from 'react'
+import { lazy, useEffect, useState } from 'react'
+import { getStartlistForHeats } from './Leaderboard'
+import Select from 'react-select'
+import { supabase } from './supabaseClient';
 
 const Auth = lazy(() => import('./Auth'));
 
+async function updateRating(rating, heatId, athleteId, userId) {
+  await supabase.from('ratings').upsert({
+    rating: rating,
+    heat: heatId,
+    athlete: athleteId,
+    judge: userId
+  })
+}
+
 function RatingForm(session) {
+  const [heats, setHeats] = useState([])
+  const [heatSelection, setHeatSelection] = useState(0)
+  const [athleteOpts, setAthleteOpts] = useState([])
+  const [athleteSelection, setAthleteSelection] = useState(0)
+  const [rating, setRating] = useState(0)
+
+  // add options to select or rank by heat
+  const heatOpts = heats.map(h => {
+    return {
+      value: h.id,
+      label: h.name
+    }
+  })
+
+  useEffect(() => {
+    (async () => {
+      let heatList = {data: []}
+      heatList = await supabase.from('heats').select()
+      setHeats(heatList.data)
+
+      let startlist = {data: []}
+      startlist = await getStartlistForHeats([heatSelection.value])
+
+      if (startlist.error !== null) {
+        return {data: []}
+      }
+
+      setAthleteOpts(startlist.data.map(s => {
+        return {
+          value: s.athlete,
+          label: s.id + " " + s.firstname + " " + (s.lastname ? s.lastname : "")
+        }
+      }))
+
+      if (heatSelection.value === undefined || athleteSelection.value === undefined)
+        return
+
+      // check if existing rating for heat and athlete exists
+      const currentRating = await supabase.from('ratings').select()
+        .eq('heat', heatSelection.value)
+        .eq('athlete', athleteSelection.value)
+        .eq('judge', session.session.user.id)
+
+      if (rating === 0 && currentRating.data?.length > 0) {
+        // fallback to current rating when no rating was given
+        setRating(currentRating.data[0].rating)
+      } else {
+        // store new rating
+        updateRating(rating,
+          heatSelection.value,
+          athleteSelection.value,
+          session.session.user.id)
+      }
+    })();
+  }, [heatSelection, athleteSelection, session.session.user.id, rating]);
+
   return (
     <div>
       <h1>Rate Athletes</h1>
+      Heat:
+          <Select
+            options={heatOpts}
+            onChange={h => { setHeatSelection(h); setRating(0) }}
+          />
+      Athlete:
+      <Select
+            options={athleteOpts}
+            onChange={a => { setAthleteSelection(a); setRating(0) }}
+          />
+      Rating:
+      <input
+            type="number"
+            size="5"
+            value={rating}
+            onChange={(e) => setRating(e.target.value)}
+          />
     </div>
   )
 }