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

Former-commit-id: 221385f463b7f5b0fc43a093b2c7c46e68d46d68

1 files changed, 24 insertions, 3 deletions

diff --git a/backend/database/init.sql b/backend/database/init.sql
index 50e7c15..abace5c 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -3,6 +3,10 @@ CREATE TABLE users (
   user_name TEXT NOT NULL,
   avatar_link TEXT NOT NULL,
   country_code CHAR(2) NOT NULL,
+  p2sr TEXT NOT NULL DEFAULT '-',
+  steam TEXT NOT NULL DEFAULT '-',
+  youtube TEXT NOT NULL DEFAULT '-',
+  twitch TEXT NOT NULL DEFAULT '-',
   created_at TIMESTAMP NOT NULL DEFAULT now(),
   updated_at TIMESTAMP NOT NULL DEFAULT now(),
   PRIMARY KEY (steam_id)
@@ -59,7 +63,7 @@ CREATE TABLE map_history (
   category_id SMALLINT NOT NULL,
   user_name TEXT NOT NULL,
   score_count SMALLINT NOT NULL,
-  record_date TIMESTAMP NOT NULL,
+  record_date DATE NOT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (category_id) REFERENCES categories(id),
   FOREIGN KEY (map_id) REFERENCES maps(id),
@@ -117,9 +121,16 @@ CREATE TABLE records_mp (
 );
 
 CREATE TABLE titles (
-  user_id TEXT,
+  id SERIAL,
   title_name TEXT NOT NULL,
-  PRIMARY KEY (user_id),
+  title_color CHAR(6) NOT NULL,
+  PRIMARY KEY (id)
+);
+
+CREATE TABLE user_titles (
+  title_id INT NOT NULL,
+  user_id TEXT NOT NULL,
+  FOREIGN KEY (title_id) REFERENCES titles(id),
   FOREIGN KEY (user_id) REFERENCES users(steam_id)
 );
 
@@ -128,3 +139,13 @@ CREATE TABLE countries (
   country_name TEXT NOT NULL,
   PRIMARY KEY (country_code)
 );
+
+CREATE TABLE logs (
+  id SERIAL,
+  user_id TEXT NOT NULL,
+  type TEXT NOT NULL,
+  description TEXT NOT NULL,
+  date TIMESTAMP NOT NULL DEFAULT now(),
+  PRIMARY KEY (id),
+  FOREIGN KEY (user_id) REFERENCES users(steam_id)
+);
\ No newline at end of file