feat: discussions (#59)

Former-commit-id: ac6ac59367650b6a37650f5aec0587c6ce4d3dd1

1 files changed, 34 insertions, 0 deletions

diff --git a/backend/database/init.sql b/backend/database/init.sql
index abace5c..0f8196b 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -82,6 +82,40 @@ CREATE TABLE map_ratings (
   FOREIGN KEY (user_id) REFERENCES users(steam_id)
 );
 
+CREATE TABLE map_discussions (
+  id SERIAL,
+  map_id SMALLINT NOT NULL,
+  user_id TEXT NOT NULL,
+  title TEXT NOT NULL,
+  content TEXT NOT NULL,
+  created_at TIMESTAMP NOT NULL DEFAULT now(),
+  updated_at TIMESTAMP NOT NULL DEFAULT now(),
+  PRIMARY KEY (id),
+  FOREIGN KEY (map_id) REFERENCES maps(id),
+  FOREIGN KEY (user_id) REFERENCES users(steam_id)
+);
+
+CREATE TABLE map_discussions_comments (
+  id SERIAL,
+  discussion_id INT NOT NULL,
+  user_id TEXT NOT NULL,
+  comment TEXT NOT NULL,
+  created_at TIMESTAMP NOT NULL DEFAULT now(),
+  PRIMARY KEY (id),
+  FOREIGN KEY (discussion_id) REFERENCES map_discussions(id),
+  FOREIGN KEY (user_id) REFERENCES users(steam_id)
+);
+
+CREATE TABLE map_discussions_upvotes (
+  id SERIAL,
+  discussion_id INT NOT NULL,
+  user_id TEXT NOT NULL,
+  upvoted BOOLEAN NOT NULL,
+  PRIMARY KEY (id),
+  FOREIGN KEY (discussion_id) REFERENCES map_discussions(id),
+  FOREIGN KEY (user_id) REFERENCES users(steam_id)
+);
+
 CREATE TABLE demos (
   id UUID,
   location_id TEXT NOT NULL,