From 4820c7696db3c54959258b1a5b00c77e1246cbd7 Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Fri, 6 Jan 2023 23:55:12 +0300
Subject: what the fuck is this mess (#21)

---
 backend/database/init.sql | 52 +++++++++++++++++++++++++++++++++++++++++------
 1 file changed, 46 insertions(+), 6 deletions(-)

(limited to 'backend/database/init.sql')

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
-- 
cgit v1.2.3