add sqldef and sqlc

10 files changed, 117 insertions, 35 deletions

diff --git a/Dockerfile.tools b/Dockerfile.tools
new file mode 100644
index 0000000..de025b8
--- /dev/null
+++ b/Dockerfile.tools
@@ -0,0 +1,13 @@
+FROM golang:1.22.3
+
+WORKDIR /tools
+
+RUN wget -O psqldef.tar.gz https://github.com/sqldef/sqldef/releases/download/v0.17.14/psqldef_linux_amd64.tar.gz
+
+RUN tar -xzf psqldef.tar.gz && \
+    rm -f psqldef.tar.gz
+
+RUN touch /usr/local/bin/psqldef && \
+    echo '#!/bin/sh' >> /usr/local/bin/psqldef && \
+    echo '/tools/psqldef --user="$POSTGRES_USER" --password="$POSTGRES_PASSWORD" --host=db "$POSTGRES_DB" "$@"' >> /usr/local/bin/psqldef && \
+    chmod +x /usr/local/bin/psqldef
diff --git a/Makefile b/Makefile
index 6cd9091..f8ef7b8 100644
--- a/Makefile
+++ b/Makefile
@@ -9,3 +9,15 @@ up:
 .PHONY: down
 down:
 	docker compose down
+
+.PHONY: psql
+psql: up
+	docker compose exec db psql --user=postgres albatross
+
+.PHONY: sqldef-dryrun
+sqldef-dryrun:
+	docker compose run --no-TTY tools psqldef --dry-run < ./backend/schema.sql
+
+.PHONY: sqldef
+sqldef:
+	docker compose run --no-TTY tools psqldef < ./backend/schema.sql
diff --git a/backend/main.go b/backend/main.go
index f0121ef..ae39b46 100644
--- a/backend/main.go
+++ b/backend/main.go
@@ -56,7 +56,6 @@ func loadEnv() (*Config, error) {
 
 const (
 	gameTypeGolf = "golf"
-	gameTypeRace = "race"
 )
 
 const (
@@ -68,24 +67,12 @@ const (
 
 type Game struct {
 	GameID int `db:"game_id"`
-	// "golf" or "race"
+	// "golf"
 	Type      string `db:"type"`
 	CreatedAt string `db:"created_at"`
 	State     string `db:"state"`
 }
 
-func initDB() error {
-	_, err := db.Exec(`
-		CREATE TABLE IF NOT EXISTS games (
-			game_id SERIAL PRIMARY KEY,
-			type VARCHAR(255) NOT NULL,
-			created_at TIMESTAMP NOT NULL DEFAULT NOW(),
-			state VARCHAR(255) NOT NULL
-		);
-	`)
-	return err
-}
-
 var gameHubs = map[int]*GameHub{}
 
 func startGame(game *Game) {
@@ -127,8 +114,7 @@ func handleGolfPost(w http.ResponseWriter, r *http.Request) {
 	http.Redirect(w, r, fmt.Sprintf("/golf/%d/%s/", waitingGame.GameID, yourTeam), http.StatusSeeOther)
 }
 
-func handleRacePost(w http.ResponseWriter, r *http.Request) {
-	http.Redirect(w, r, "/race/1/a/", http.StatusSeeOther)
+func handleApiLogin(w http.ResponseWriter, r *http.Request) {
 }
 
 func main() {
@@ -151,22 +137,13 @@ func main() {
 	}
 	defer db.Close()
 
-	err = initDB()
-	if err != nil {
-		log.Fatalf("Error initializing db %v", err)
-	}
-
 	server := http.NewServeMux()
 
 	server.HandleFunc("GET /assets/", func(w http.ResponseWriter, r *http.Request) {
 		http.ServeFile(w, r, "./public"+r.URL.Path)
 	})
 
-	server.HandleFunc("GET /{$}", func(w http.ResponseWriter, r *http.Request) {
-		http.ServeFile(w, r, "./public/index.html")
-	})
-
-	server.HandleFunc("GET /golf/{$}", func(w http.ResponseWriter, r *http.Request) {
+	server.HandleFunc("GET /", func(w http.ResponseWriter, r *http.Request) {
 		http.ServeFile(w, r, "./public/index.html")
 	})
 
@@ -174,10 +151,6 @@ func main() {
 		handleGolfPost(w, r)
 	})
 
-	server.HandleFunc("GET /golf/{gameId}/watch/{$}", func(w http.ResponseWriter, r *http.Request) {
-		http.ServeFile(w, r, "./public/index.html")
-	})
-
 	server.HandleFunc("GET /sock/golf/{gameId}/watch/{$}", func(w http.ResponseWriter, r *http.Request) {
 		gameId := r.PathValue("gameId")
 		gameIdInt, err := strconv.Atoi(gameId)
@@ -199,10 +172,6 @@ func main() {
 		serveWsWatcher(hub, w, r)
 	})
 
-	server.HandleFunc("GET /golf/{gameId}/{team}/{$}", func(w http.ResponseWriter, r *http.Request) {
-		http.ServeFile(w, r, "./public/index.html")
-	})
-
 	server.HandleFunc("GET /sock/golf/{gameId}/{team}/{$}", func(w http.ResponseWriter, r *http.Request) {
 		gameId := r.PathValue("gameId")
 		gameIdInt, err := strconv.Atoi(gameId)
@@ -225,6 +194,10 @@ func main() {
 		serveWs(hub, w, r, team)
 	})
 
+	server.HandleFunc("POST /api/login/{$}", func(w http.ResponseWriter, r *http.Request) {
+		handleApiLogin(w, r)
+	})
+
 	defer func() {
 		for _, hub := range gameHubs {
 			hub.Close()
diff --git a/backend/query.sql b/backend/query.sql
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/backend/query.sql
diff --git a/backend/schema.sql b/backend/schema.sql
new file mode 100644
index 0000000..6a2db2d
--- /dev/null
+++ b/backend/schema.sql
@@ -0,0 +1,23 @@
+CREATE TABLE users (
+    user_id SERIAL PRIMARY KEY,
+    username VARCHAR(64) NOT NULL UNIQUE,
+    display_username VARCHAR(64) NOT NULL,
+    icon_url VARCHAR(255),
+    is_admin BOOLEAN NOT NULL,
+    created_at TIMESTAMP NOT NULL DEFAULT NOW()
+);
+
+CREATE TABLE user_auths (
+    user_auth_id SERIAL PRIMARY KEY,
+    user_id INT NOT NULL,
+    auth_type VARCHAR(16) NOT NULL,
+    password_hash VARCHAR(256),
+    CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(user_id)
+);
+
+CREATE TABLE games (
+    game_id SERIAL PRIMARY KEY,
+    type VARCHAR(255) NOT NULL,
+    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
+    state VARCHAR(255) NOT NULL
+);
diff --git a/backend/sqlc.yaml b/backend/sqlc.yaml
new file mode 100644
index 0000000..4027d9b
--- /dev/null
+++ b/backend/sqlc.yaml
@@ -0,0 +1,10 @@
+version: "2"
+sql:
+  - engine: "postgresql"
+    queries: "query.sql"
+    schema: "schema.sql"
+    gen:
+      go:
+        package: "sqlc"
+        out: "sqlc"
+        sql_package: "pgx/v5"
diff --git a/compose.yaml b/compose.yaml
index c708494..1192903 100644
--- a/compose.yaml
+++ b/compose.yaml
@@ -22,8 +22,27 @@ services:
       POSTGRES_DB: albatross
     expose:
       - 5432
+    healthcheck:
+      test: ["CMD-SHELL", "pg_isready -U postgres"]
+      interval: 10s
+      timeout: 5s
+      retries: 5
     volumes:
       - db-data:/var/lib/postgresql/data
 
+  tools:
+    build:
+      context: .
+      dockerfile: Dockerfile.tools
+    depends_on:
+      db:
+        condition: service_healthy
+    environment:
+      POSTGRES_USER: postgres
+      POSTGRES_PASSWORD: eepei5reesoo0ov2ceelahd4Emi0au8ahJa6oochohheiquahweihoovahsee1oo
+      POSTGRES_DB: albatross
+    profiles:
+      - tools
+
 volumes:
   db-data:
diff --git a/frontend/src/main.tsx b/frontend/src/main.tsx
index 0df5185..eb5399f 100644
--- a/frontend/src/main.tsx
+++ b/frontend/src/main.tsx
@@ -5,6 +5,7 @@ import {
   RouterProvider,
 } from 'react-router-dom';
 import Home from './routes/Home.tsx';
+import Login from './routes/Login.tsx';
 import GolfEntry from './routes/golf/GolfEntry.tsx';
 import GolfPlay from './routes/golf/GolfPlay.tsx';
 import GolfWatch from './routes/golf/GolfWatch.tsx';
@@ -15,6 +16,10 @@ const router = createBrowserRouter([
     element: (<Home />),
   },
   {
+    path: "/login/",
+    element: (<Login />),
+  },
+  {
     path: "/golf/entry/",
     element: (<GolfEntry />),
   },
diff --git a/frontend/src/routes/Home.tsx b/frontend/src/routes/Home.tsx
index a448ffa..62d66b5 100644
--- a/frontend/src/routes/Home.tsx
+++ b/frontend/src/routes/Home.tsx
@@ -1,7 +1,15 @@
+import { Link } from 'react-router-dom';
+
 export default function Home() {
   return (
     <div>
-      <h1>Home</h1>
+      <h1>Albatross.swift</h1>
+      <p>
+        iOSDC 2024
+      </p>
+      <p>
+        <Link to="/login/">Login</Link>
+      </p>
     </div>
   );
 };
diff --git a/frontend/src/routes/Login.tsx b/frontend/src/routes/Login.tsx
new file mode 100644
index 0000000..1945abe
--- /dev/null
+++ b/frontend/src/routes/Login.tsx
@@ -0,0 +1,19 @@
+import { Form } from "react-router-dom";
+
+export default function Login() {
+  return (
+    <div>
+      <h1>Albatross.swift</h1>
+      <h2>
+        Login
+      </h2>
+      <Form method="post">
+        <label>Username</label>
+        <input type="text" name="username" />
+        <label>Password</label>
+        <input type="password" name="password" />
+        <button type="submit">Login</button>
+      </Form>
+    </div>
+  );
+};