Merge branch 'main' of https://github.com/pektezol/LeastPortals

Former-commit-id: af8d8680aafc3d662f8b53a4f50f0ea356b26c26

1 files changed, 25 insertions, 19 deletions

diff --git a/backend/database/init.sql b/backend/database/init.sql
index 51a4881..50e7c15 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -9,13 +9,14 @@ CREATE TABLE users (
 );
 
 CREATE TABLE games (
-  id SMALLSERIAL,
+  id SERIAL,
   name TEXT NOT NULL,
+  is_coop BOOLEAN NOT NULL,
   PRIMARY KEY (id)
 );
 
 CREATE TABLE chapters (
-  id SMALLSERIAL,
+  id SERIAL,
   game_id SMALLINT NOT NULL,
   name TEXT NOT NULL,
   PRIMARY KEY (id),
@@ -23,52 +24,57 @@ CREATE TABLE chapters (
 );
 
 CREATE TABLE categories (
-  id SMALLSERIAL,
+  id SERIAL,
   name TEXT NOT NULL,
   PRIMARY KEY (id)
 );
 
 CREATE TABLE maps (
-  id SMALLSERIAL,
+  id SERIAL,
   game_id SMALLINT NOT NULL,
   chapter_id SMALLINT NOT NULL,
   name TEXT NOT NULL,
-  description TEXT NOT NULL,
-  showcase TEXT NOT NULL,
   is_disabled BOOLEAN NOT NULL DEFAULT false,
   PRIMARY KEY (id),
   FOREIGN KEY (game_id) REFERENCES games(id),
   FOREIGN KEY (chapter_id) REFERENCES chapters(id)
 );
 
+CREATE TABLE map_routes (
+  id SERIAL,
+  map_id SMALLINT NOT NULL,
+  category_id SMALLINT NOT NULL,
+  score_count SMALLINT NOT NULL,
+  description TEXT NOT NULL,
+  showcase TEXT NOT NULL,
+  PRIMARY KEY (id),
+  FOREIGN KEY (map_id) REFERENCES maps(id),
+  FOREIGN KEY (category_id) REFERENCES categories(id),
+  UNIQUE (map_id, category_id, score_count)
+);
+
 CREATE TABLE map_history (
-  id SMALLSERIAL,
+  id SERIAL,
   map_id SMALLINT NOT NULL,
+  category_id SMALLINT NOT NULL,
   user_name TEXT NOT NULL,
   score_count SMALLINT NOT NULL,
   record_date TIMESTAMP NOT NULL,
   PRIMARY KEY (id),
-  FOREIGN KEY (map_id) REFERENCES maps(id)
+  FOREIGN KEY (category_id) REFERENCES categories(id),
+  FOREIGN KEY (map_id) REFERENCES maps(id),
+  UNIQUE (map_id, category_id, score_count)
 );
 
 CREATE TABLE map_ratings (
   id SERIAL,
   map_id SMALLINT NOT NULL,
+  category_id SMALLINT NOT NULL,
   user_id TEXT NOT NULL,
   rating SMALLINT NOT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (map_id) REFERENCES maps(id),
-  FOREIGN KEY (user_id) REFERENCES users(steam_id)
-);
-
-CREATE TABLE map_routers (
-  id SMALLSERIAL,
-  map_id SMALLINT NOT NULL,
-  user_id TEXT,
-  user_name TEXT NOT NULL,
-  score_count SMALLINT NOT NULL,
-  PRIMARY KEY (id),
-  FOREIGN KEY (map_id) REFERENCES maps(id),
+  FOREIGN KEY (category_id) REFERENCES categories(id),
   FOREIGN KEY (user_id) REFERENCES users(steam_id)
 );