what the fuck is this mess (#21)

1 files changed, 46 insertions, 6 deletions

diff --git a/backend/database/init.sql b/backend/database/init.sql
index e877db3..1585e33 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -1,11 +1,51 @@
+DROP TABLE IF EXISTS showcases;
+DROP TABLE IF EXISTS titles;
+DROP TABLE IF EXISTS records;
+DROP TABLE IF EXISTS maps;
 DROP TABLE IF EXISTS users;
+
 CREATE TABLE users (
-  steam_id BIGINT,
-  username VARCHAR(128) NOT NULL,
-  avatar_link VARCHAR(128) NOT NULL,
+  steam_id TEXT,
+  username TEXT NOT NULL,
+  avatar_link TEXT NOT NULL,
   country_code CHAR(2) NOT NULL DEFAULT 'XX',
-  created_at TIMESTAMP NOT NULL,
-  updated_at TIMESTAMP,
-  user_type SMALLINT NOT NULL,
+  created_at TIMESTAMP NOT NULL DEFAULT now(),
+  updated_at TIMESTAMP NOT NULL DEFAULT now(),
   PRIMARY KEY (steam_id)
+);
+
+CREATE TABLE maps (
+  id SMALLSERIAL,
+  map_name TEXT NOT NULL,
+  wr_score SMALLINT NOT NULL,
+  is_coop BOOLEAN NOT NULL,
+  PRIMARY KEY (id)
+);
+
+CREATE TABLE records (
+  id SERIAL,
+  map_id SMALLINT,
+  host_id TEXT NOT NULL,
+  score_count SMALLINT NOT NULL,
+  score_time INTEGER NOT NULL,
+  is_coop BOOLEAN NOT NULL DEFAULT false,
+  partner_id TEXT NOT NULL DEFAULT '',
+  PRIMARY KEY (id),
+  FOREIGN KEY (map_id) REFERENCES maps(id),
+  FOREIGN KEY (host_id) REFERENCES users(steam_id),
+  FOREIGN KEY (partner_id) REFERENCES users(steam_id)
+);
+
+CREATE TABLE titles (
+  user_id TEXT,
+  title_name TEXT NOT NULL,
+  PRIMARY KEY (user_id),
+  FOREIGN KEY (user_id) REFERENCES users(steam_id)
+);
+
+CREATE TABLE showcases (
+  record_id INT,
+  video_id TEXT NOT NULL,
+  PRIMARY KEY (record_id),
+  FOREIGN KEY (record_id) REFERENCES records(id)
 );
\ No newline at end of file