database_setup.txt

DROP TABLE IF EXISTS players CASCADE;
DROP TABLE IF EXISTS player_sessions CASCADE;
DROP TABLE IF EXISTS campaigns CASCADE;
DROP TABLE IF EXISTS campaign_levels CASCADE;
DROP TABLE IF EXISTS levels CASCADE;
DROP TABLE IF EXISTS ratings CASCADE;
DROP TABLE IF EXISTS logings CASCADE;
DROP TABLE IF EXISTS rounds CASCADE;
DROP TABLE IF EXISTS levels_img CASCADE;


CREATE TABLE players(
	id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	username VARCHAR(30) NOT NULL UNIQUE,
	email VARCHAR(60) NOT NULL UNIQUE,
	type VARCHAR(10) NOT NULL,
	password_hash VARCHAR(250),
	google_id VARCHAR(500),
	creation_timestamp TIMESTAMP DEFAULT NOW()
    CONSTRAINT correct_auth CHECK (
        (type = 'google' AND google_id IS NOT NULL) OR
        (type = 'db' AND password_hash IS NOT NULL)
    )
);

CREATE TABLE player_sessions (
	id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	player_id INT NOT NULL,
	session_token VARCHAR(120) NOT NULL UNIQUE,
	expiration_timestamp TIMESTAMP DEFAULT NOW() + interval '7 days',
	FOREIGN KEY (player_id) REFERENCES players(id)
);


CREATE TABLE levels (
	id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	name VARCHAR(30) NOT NULL UNIQUE,
	creator_id INT NOT NULL,
	max_players INT NOT NULL,
  type VARCHAR(30) NOT NULL,
  status VARCHAR(30) NOT NULL,
	content JSON NOT NULL,
	creation_timestamp TIMESTAMP DEFAULT NOW(),
	FOREIGN KEY (creator_id) REFERENCES players(id)
);

CREATE TABLE campaigns (
	id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
	name VARCHAR(30) NOT NULL UNIQUE,
	creator_id INT NOT NULL,
	description VARCHAR(300) NOT NULL,
	creation_timestamp TIMESTAMP DEFAULT NOW(),
	FOREIGN KEY (creator_id) REFERENCES players(id)
);

CREATE TABLE campaign_levels (
	id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	campaign_id INT NOT NULL,
	level_id INT NOT NULL,
	order_index INT NOT NULL,
	FOREIGN KEY (campaign_id) REFERENCES campaigns(id),
	FOREIGN KEY (level_id) REFERENCES levels(id)
);

CREATE TABLE ratings(
	id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	stars INT NOT NULL,
	level_id INT NOT NULL,
	player_id INT NOT NULL,
    UNIQUE (level_id, player_id),
	FOREIGN KEY (level_id) REFERENCES levels(id),
	FOREIGN KEY (player_id) REFERENCES players(id)
);


CREATE TABLE logings (
	id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	player_id INT NOT NULL,
	ip_address varchar NOT NULL,
	attempt_timestamp TIMESTAMP DEFAULT NOW(),
	status varchar(30) NOT NULL,
	FOREIGN KEY (player_id) REFERENCES players(id)
);

CREATE TABLE rounds (
  id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  player_id INT,
  level_id INT NOT NULL,
  wins INT NOT NULL,
  kills INT NOT NULL,
  deaths INT NOT NULL,
  shots INT NOT NULL,
  hits INT NOT NULL,
  plants INT NOT NULL,
  blocks_destroyed INT NOT NULL,
  timestamp TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY (player_id) REFERENCES players(id),
  FOREIGN KEY (level_id) REFERENCES levels(id)
);


CREATE TABLE levels_img(
	id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	level_id INT NOT NULL,
	img bytea NOT NULL,
	FOREIGN KEY (level_id) REFERENCES levels(id)
);