StrategyScope database creation

CREATE TABLE [USER](
    id INT IDENTITY(1,1) PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    passHash VARCHAR(200) NOT NULL,
    salt VARCHAR(200) NOT NULL UNIQUE,
    creationDate DateTime NOT NULL DEFAULT (getdate()),
    lastLogin DateTime NOT NULL DEFAULT (getdate())
);

CREATE TABLE Room(
    id INT IDENTITY(1,1) PRIMARY KEY,
    [name] VARCHAR(200) NOT NULL,
    maxUsers INT
);

CREATE TABLE Chat(
    id INT IDENTITY(1,1) PRIMARY KEY,
    roomId INT NOT NULL, /*Foreign key to room*/
    [name] VARCHAR(100)
);

CREATE TABLE [Message](
    id INT IDENTITY(1,1) PRIMARY KEY,
    userId INT NOT NULL, /*Foreign  key to user*/
    chatId INT NOT NULL, /*Foreign  key to chat*/
    content nvarchar(2000) NOT NULL,
    [TIMESTAMP] DateTime NOT NULL DEFAULT (getdate()),
);

CREATE TABLE TaskTree(
    id INT IDENTITY(1,1) PRIMARY KEY,
    roomId INT NOT NULL, /*Foreign  key to room*/
    [name] VARCHAR(100)
);

CREATE TABLE Task(
    id INT IDENTITY(1,1) PRIMARY KEY,
    taskTreeId INT NOT NULL, /*Foreign  key to TaskTree*/
    [name] VARCHAR(80),
    [description] VARCHAR(80),
    creationDate DateTime NOT NULL DEFAULT (getdate()),
    deadline DateTime,
    color INT
);

CREATE TABLE UserRoles(
    accessLevel INT NOT NULL PRIMARY KEY,
    roleName VARCHAR(50) NOT NULL
);


/* LINK TABLES */

CREATE TABLE Room_User(
    userId INT NOT NULL, /*Foreign  key to user*/
    roomId INT NOT NULL, /*Foreign  key to room*/
    userRolesAccessLevel INT NOT NULL, /*Foreign  key to UserRoles*/
    PRIMARY KEY(userId, roomId)
);

CREATE TABLE Task_User(
    userId INT NOT NULL, /*Foreign  key to user*/
    taskId INT NOT NULL, /*Foreign  key to task*/
    PRIMARY KEY(userId, taskId)
);

CREATE TABLE jwtBlacklist (
    id INT PRIMARY KEY IDENTITY(1,1),
    token VARCHAR(450) UNIQUE NOT NULL,
    expireTime DATE NOT NULL
)

/* Setting foreign key constraints */

/* Chat */
ALTER TABLE Chat
ADD FOREIGN KEY (roomId)
REFERENCES Room(id);

/* Message */
ALTER TABLE [Message]
ADD FOREIGN KEY (userId)
REFERENCES [USER](id);

ALTER TABLE [Message]
ADD FOREIGN KEY (chatId)
REFERENCES Chat(id);

/* Task tree */
ALTER TABLE TaskTree
ADD FOREIGN KEY (roomId)
REFERENCES Room(id);

/* Task */
ALTER TABLE Task
ADD FOREIGN KEY (taskTreeId)
REFERENCES TaskTree(id);

/* Room_User */
ALTER TABLE Room_User
ADD FOREIGN KEY (userId)
REFERENCES [USER](id);

ALTER TABLE Room_User
ADD FOREIGN KEY (roomId)
REFERENCES Room(id);

ALTER TABLE Room_User
ADD FOREIGN KEY (userRolesAccessLevel)
REFERENCES UserRoles(accessLevel);

/* Task_User */
ALTER TABLE Task_User
ADD FOREIGN KEY (userId)
REFERENCES [USER](id);

ALTER TABLE Task_User
ADD FOREIGN KEY (taskId)
REFERENCES Task(id);