feat/db: multiple game/chapter support, improved map relations (#1)

5 files changed, 189 insertions, 162 deletions

diff --git a/backend/database/categories.sql b/backend/database/categories.sql
new file mode 100644
index 0000000..5cadad5
--- /dev/null
+++ b/backend/database/categories.sql
@@ -0,0 +1,5 @@
+INSERT INTO categories(id, name) VALUES
+(1, 'CM'),
+(2, 'No SLA'),
+(3, 'Inbounds SLA'),
+(4, 'Any%');
\ No newline at end of file
diff --git a/backend/database/chapters.sql b/backend/database/chapters.sql
new file mode 100644
index 0000000..f01ae9c
--- /dev/null
+++ b/backend/database/chapters.sql
@@ -0,0 +1,17 @@
+INSERT INTO chapters(id, game_id, name) VALUES
+(1, 1, 'The Coutesy Call'),
+(2, 1, 'The Cold Boot'),
+(3, 1, 'The Return'),
+(4, 1, 'The Surprise'),
+(5, 1, 'The Escape'),
+(6, 1, 'The Fall'),
+(7, 1, 'The Reunion'),
+(8, 1, 'The Itch'),
+(9, 1, 'The Part Where He Kills You'),
+(10, 2, 'Introduction'),
+(11, 2, 'Team Building'),
+(12, 2, 'Mass And Velocity'),
+(13, 2, 'Hard-Light Surfaces'),
+(14, 2, 'Excursion Funnels'),
+(15, 2, 'Mobility Gels'),
+(16, 2, 'Art Therapy');
\ No newline at end of file
diff --git a/backend/database/games.sql b/backend/database/games.sql
new file mode 100644
index 0000000..5e2f4ee
--- /dev/null
+++ b/backend/database/games.sql
@@ -0,0 +1,3 @@
+INSERT INTO games(id, name) VALUES
+(1, 'Portal 2 - Singleplayer'),
+(2, 'Portal 2 - Cooperative');
\ No newline at end of file
diff --git a/backend/database/init.sql b/backend/database/init.sql
index 7e225f0..a82404a 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -1,13 +1,3 @@
-DROP TABLE IF EXISTS showcases;
-DROP TABLE IF EXISTS titles;
-DROP TABLE IF EXISTS records_sp;
-DROP TABLE IF EXISTS records_mp;
-DROP TABLE IF EXISTS maps;
-DROP TABLE IF EXISTS users;
-DROP TABLE IF EXISTS countries;
-
-DROP TABLE IF EXISTS demos;
-
 CREATE TABLE users (
   steam_id TEXT,
   username TEXT NOT NULL,
@@ -18,14 +8,59 @@ CREATE TABLE users (
   PRIMARY KEY (steam_id)
 );
 
+CREATE TABLE games (
+  id SMALLSERIAL,
+  name TEXT NOT NULL,
+  PRIMARY KEY (id)
+);
+
+CREATE TABLE chapters (
+  id SMALLSERIAL,
+  game_id SMALLINT NOT NULL,
+  name TEXT NOT NULL,
+  PRIMARY KEY (id),
+  FOREIGN KEY (game_id) REFERENCES games(id)
+);
+
+CREATE TABLE categories (
+  id SMALLSERIAL,
+  name TEXT NOT NULL,
+  PRIMARY KEY (id)
+);
+
 CREATE TABLE maps (
   id SMALLSERIAL,
-  map_name TEXT NOT NULL,
-  wr_score SMALLINT NOT NULL,
-  wr_time INTEGER NOT NULL,
-  is_coop BOOLEAN NOT NULL,
+  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)
+  PRIMARY KEY (id),
+  FOREIGN KEY (game_id) REFERENCES games(id),
+  FOREIGN KEY (chapter_id) REFERENCES chapters(id)
+);
+
+CREATE TABLE map_history (
+  id SMALLSERIAL,
+  map_id SMALLINT NOT NULL,
+  user_id TEXT,
+  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 (user_id) REFERENCES users(steam_id)
+);
+
+CREATE TABLE map_rating (
+  id SERIAL,
+  map_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 demos (
@@ -78,36 +113,3 @@ CREATE TABLE countries (
   country_name TEXT NOT NULL,
   PRIMARY KEY (country_code)
 );
-
--- CREATE TABLE community_maps (
---   id SMALLSERIAL,
---   map_id SMALLINT NOT NULL,
---   category TEXT NOT NULL,
---   tutorial TEXT NOT NULL,
---   video_link TEXT NOT NULL,
---   PRIMARY KEY (id),
---   FOREIGN KEY (map_id) REFERENCES maps(id)
--- );
-
--- TODO
--- CREATE TABLE community_history (
---   id SERIAL,
---   map_id SMALLINT NOT NULL,
---   user_id TEXT NOT NULL,
---   score_count SMALLINT NOT NULL,
---   created_at TIMESTAMP NOT NULL DEFAULT now(),
---   PRIMARY KEY (id),
---   FOREIGN KEY (user_id) REFERENCES users(steam_id),
---   FOREIGN KEY (map_id) REFERENCES maps(id)
--- );
-
--- CREATE TABLE community_posts (
---   id SERIAL,
---   map_id SMALLINT NOT NULL,
---   user_id TEXT NOT NULL,
---   post TEXT NOT NULL,
---   created_at TIMESTAMP NOT NULL DEFAULT now(),
---   PRIMARY KEY (id),
---   FOREIGN KEY (user_id) REFERENCES users(steam_id),
---   FOREIGN KEY (map_id) REFERENCES maps(id)
--- );
\ No newline at end of file
diff --git a/backend/database/maps.sql b/backend/database/maps.sql
index 127eb58..50689e2 100644
--- a/backend/database/maps.sql
+++ b/backend/database/maps.sql
@@ -1,129 +1,129 @@
-DELETE FROM maps;
-INSERT INTO maps (map_name, wr_score, wr_time, is_coop, is_disabled) VALUES
--- Singleplayer
+INSERT INTO maps(game_id, chapter_id, name, description, showcase, is_disabled) VALUES
+-- Portal 2 Singleplayer
 -- 1
-('Container Ride',0,-1,false,true),
-('Portal Carousel',0,-1,false,true),
-('Portal Gun',0,-1,false,false),
-('Smooth Jazz',0,-1,false,false),
-('Cube Momentum',1,-1,false,false),
-('Future Starter',2,-1,false,false),
-('Secret Panel',0,-1,false,false),
-('Wakeup',0,-1,false,true),
-('Incinerator',0,-1,false,false),
+(1,1,'Container Ride','','',true),
+(1,1,'Portal Carousel','','',true),
+(1,1,'Portal Gun','','',false),
+(1,1,'Smooth Jazz','','',false),
+(1,1,'Cube Momentum','','',false),
+(1,1,'Future Starter','','',false),
+(1,1,'Secret Panel','','',false),
+(1,1,'Wakeup','','',true),
+(1,1,'Incinerator','','',false),
 -- 2
-('Laser Intro',0,-1,false,false),
-('Laser Stairs',0,-1,false,false),
-('Dual Lasers',2,-1,false,false),
-('Laser Over Goo',0,-1,false,false),
-('Catapult Intro',0,-1,false,true),
-('Trust Fling',2,-1,false,false),
-('Pit Flings',0,-1,false,false),
-('Fizzler Intro',2,-1,false,false),
+(1,2,'Laser Intro','','',false),
+(1,2,'Laser Stairs','','',false),
+(1,2,'Dual Lasers','','',false),
+(1,2,'Laser Over Goo','','',false),
+(1,2,'Catapult Intro','','',true),
+(1,2,'Trust Fling','','',false),
+(1,2,'Pit Flings','','',false),
+(1,2,'Fizzler Intro','','',false),
 -- 3
-('Ceiling Catapult',0,-1,false,false),
-('Ricochet',2,-1,false,false),
-('Bridge Intro',2,-1,false,false),
-('Bridge The Gap',0,-1,false,false),
-('Turret Intro',0,-1,false,false),
-('Laser Relays',0,-1,false,false),
-('Turret Blocker',0,-1,false,false),
-('Laser vs Turret',0,-1,false,false),
-('Pull The Rug',0,-1,false,false),
+(1,3,'Ceiling Catapult','','',false),
+(1,3,'Ricochet','','',false),
+(1,3,'Bridge Intro','','',false),
+(1,3,'Bridge The Gap','','',false),
+(1,3,'Turret Intro','','',false),
+(1,3,'Laser Relays','','',false),
+(1,3,'Turret Blocker','','',false),
+(1,3,'Laser vs Turret','','',false),
+(1,3,'Pull The Rug','','',false),
 -- 4
-('Column Blocker',0,-1,false,false),
-('Laser Chaining',0,-1,false,false),
-('Triple Laser',0,-1,false,false),
-('Jail Break',2,-1,false,false),
-('Escape',0,-1,false,false),
+(1,4,'Column Blocker','','',false),
+(1,4,'Laser Chaining','','',false),
+(1,4,'Triple Laser','','',false),
+(1,4,'Jail Break','','',false),
+(1,4,'Escape','','',false),
 -- 5
-('Turret Factory',5,-1,false,false),
-('Turret Sabotage',4,-1,false,false),
-('Neurotoxin Sabotage',0,-1,false,false),
-('Core',2,-1,false,false),
+(1,5,'Turret Factory','','',false),
+(1,5,'Turret Sabotage','','',false),
+(1,5,'Neurotoxin Sabotage','','',false),
+(1,5,'Core','','',false),
 -- 6
-('Underground',4,-1,false,false),
-('Cave Johnson',4,-1,false,false),
-('Repulsion Intro',0,-1,false,false),
-('Bomb Flings',3,-1,false,false),
-('Crazy Box',0,-1,false,false),
-('PotatOS',5,-1,false,false),
+(1,6,'Underground','','',false),
+(1,6,'Cave Johnson','','',false),
+(1,6,'Repulsion Intro','','',false),
+(1,6,'Bomb Flings','','',false),
+(1,6,'Crazy Box','','',false),
+(1,6,'PotatOS','','',false),
 -- 7
-('Propulsion Intro',2,-1,false,false),
-('Propulsion Flings',0,-1,false,false),
-('Conversion Intro',9,-1,false,false),
-('Three Gels',4,-1,false,false),
+(1,7,'Propulsion Intro','','',false),
+(1,7,'Propulsion Flings','','',false),
+(1,7,'Conversion Intro','','',false),
+(1,7,'Three Gels','','',false),
 -- 8
-('Test',2,-1,false,false),
-('Funnel Intro',0,-1,false,false),
-('Ceiling Button',0,-1,false,false),
-('Wall Button',0,-1,false,false),
-('Polarity',0,-1,false,false),
-('Funnel Catch',2,-1,false,false),
-('Stop The Box',0,-1,false,false),
-('Laser Catapult',0,-1,false,false),
-('Laser Platform',3,-1,false,false),
-('Propulsion Catch',0,-1,false,false),
-('Repulsion Polarity',2,-1,false,false),
+(1,8,'Test','','',false),
+(1,8,'Funnel Intro','','',false),
+(1,8,'Ceiling Button','','',false),
+(1,8,'Wall Button','','',false),
+(1,8,'Polarity','','',false),
+(1,8,'Funnel Catch','','',false),
+(1,8,'Stop The Box','','',false),
+(1,8,'Laser Catapult','','',false),
+(1,8,'Laser Platform','','',false),
+(1,8,'Propulsion Catch','','',false),
+(1,8,'Repulsion Polarity','','',false),
 -- 9
-('Finale 1',0,-1,false,false),
-('Finale 2',2,-1,false,false),
-('Finale 3',6,-1,false,false),
-('Finale 4',6,-1,false,false),
--- Coop
+(1,9,'Finale 1','','',false),
+(1,9,'Finale 2','','',false),
+(1,9,'Finale 3','','',false),
+(1,9,'Finale 4','','',false),
+-- Portal 2 Cooperative
+-- 0
+(2,10,'Calibration','','',false),
+(2,10,'Hub','','',true),
 -- 1
-('Calibration',4,-1,true,false),
-('Hub',0,-1,false,true),
-('Doors',0,-1,true,false),
-('Buttons',2,-1,true,false),
-('Lasers',3,-1,true,false),
-('Rat Maze',2,-1,true,false),
-('Laser Crusher',0,-1,true,false),
-('Behind The Scenes',0,-1,true,false),
+(2,11,'Doors','','',false),
+(2,11,'Buttons','','',false),
+(2,11,'Lasers','','',false),
+(2,11,'Rat Maze','','',false),
+(2,11,'Laser Crusher','','',false),
+(2,11,'Behind The Scenes','','',false),
 -- 2
-('Flings',4,-1,true,false),
-('Infinifling',0,-1,true,false),
-('Team Retrieval',0,-1,true,false),
-('Vertical Flings',2,-1,true,false),
-('Catapults',4,-1,true,false),
-('Multifling',2,-1,true,false),
-('Fling Crushers',0,-1,true,false),
-('Industrial Fan',0,-1,true,false),
+(2,12,'Flings','','',false),
+(2,12,'Infinifling','','',false),
+(2,12,'Team Retrieval','','',false),
+(2,12,'Vertical Flings','','',false),
+(2,12,'Catapults','','',false),
+(2,12,'Multifling','','',false),
+(2,12,'Fling Crushers','','',false),
+(2,12,'Industrial Fan','','',false),
 -- 3
-('Cooperative Bridges',3,-1,true,false),
-('Bridge Swap',2,-1,true,false),
-('Fling Block',2,-1,true,false),
-('Catapult Block',4,-1,true,false),
-('Bridge Fling',4,-1,true,false),
-('Turret Walls',4,-1,true,false),
-('Turret Assasin',0,-1,true,false),
-('Bridge Testing',0,-1,true,false),
+(2,13,'Cooperative Bridges','','',false),
+(2,13,'Bridge Swap','','',false),
+(2,13,'Fling Block','','',false),
+(2,13,'Catapult Block','','',false),
+(2,13,'Bridge Fling','','',false),
+(2,13,'Turret Walls','','',false),
+(2,13,'Turret Assasin','','',false),
+(2,13,'Bridge Testing','','',false),
 -- 4
-('Cooperative Funnels',0,-1,true,false),
-('Funnel Drill',0,-1,true,false),
-('Funnel Catch',0,-1,true,false),
-('Funnel Laser',0,-1,true,false),
-('Cooperative Polarity',0,-1,true,false),
-('Funnel Hop',0,-1,true,false),
-('Advanced Polarity',0,-1,true,false),
-('Funnel Maze',0,-1,true,false),
-('Turret Warehouse',0,-1,true,false),
+(2,14,'Cooperative Funnels','','',false),
+(2,14,'Funnel Drill','','',false),
+(2,14,'Funnel Catch','','',false),
+(2,14,'Funnel Laser','','',false),
+(2,14,'Cooperative Polarity','','',false),
+(2,14,'Funnel Hop','','',false),
+(2,14,'Advanced Polarity','','',false),
+(2,14,'Funnel Maze','','',false),
+(2,14,'Turret Warehouse','','',false),
 -- 5
-('Repulsion Jumps',0,-1,true,false),
-('Double Bounce',2,-1,true,false),
-('Bridge Repulsion',2,-1,true,false),
-('Wall Repulsion',2,-1,true,false),
-('Propulsion Crushers',0,-1,true,false),
-('Turret Ninja',0,-1,true,false),
-('Propulsion Retrieval',0,-1,true,false),
-('Vault Entrance',0,-1,true,false),
+(2,15,'Repulsion Jumps','','',false),
+(2,15,'Double Bounce','','',false),
+(2,15,'Bridge Repulsion','','',false),
+(2,15,'Wall Repulsion','','',false),
+(2,15,'Propulsion Crushers','','',false),
+(2,15,'Turret Ninja','','',false),
+(2,15,'Propulsion Retrieval','','',false),
+(2,15,'Vault Entrance','','',false),
 -- 6
-('Seperation',0,-1,true,false),
-('Triple Axis',0,-1,true,false),
-('Catapult Catch',0,-1,true,false),
-('Bridge Gels',2,-1,true,false),
-('Maintenance',0,-1,true,false),
-('Bridge Catch',0,-1,true,false),
-('Double Lift',0,-1,true,false),
-('Gel Maze',0,-1,true,false),
-('Crazier Box',0,-1,true,false);
\ No newline at end of file
+(2,16,'Seperation','','',false),
+(2,16,'Triple Axis','','',false),
+(2,16,'Catapult Catch','','',false),
+(2,16,'Bridge Gels','','',false),
+(2,16,'Maintenance','','',false),
+(2,16,'Bridge Catch','','',false),
+(2,16,'Double Lift','','',false),
+(2,16,'Gel Maze','','',false),
+(2,16,'Crazier Box','','',false);
\ No newline at end of file