CREATE TABLE Users (
    UserId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Username VARCHAR(50) NOT NULL UNIQUE,
    Password VARCHAR(50) NOT NULL,
    Email VARCHAR(50) NOT NULL UNIQUE,
)

CREATE TABLE Roles (
    RoleId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50) NOT NULL UNIQUE
)

CREATE TABLE Users_Roles (
    UserId INT NOT NULL,
    RoleId INT NOT NULL
    PRIMARY KEY (UserId, RoleId)
)

CREATE TABLE HierarchicalPositions (
    HierarchicalPositionId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50) NOT NULL UNIQUE
)

CREATE TABLE Users_HierarchicalPositions (
    UserId INT NOT NULL,
    HierarchicalPositionId INT NOT NULL
    PRIMARY KEY (UserId, HierarchicalPositionId)
)

ALTER TABLE Users_Roles ADD CONSTRAINT fk_UserIdr FOREIGN KEY (UserId) REFERENCES Users(UserId) ON DELETE cascade
ALTER TABLE Users_Roles ADD CONSTRAINT fk_RoleId FOREIGN KEY (RoleId) REFERENCES Roles(RoleId) ON DELETE cascade

ALTER TABLE Users_HierarchicalPositions ADD CONSTRAINT fk_UserIdh FOREIGN KEY (UserId) REFERENCES Users(UserId) ON DELETE cascade
ALTER TABLE Users_HierarchicalPositions ADD CONSTRAINT fk_HierarchicalPositionId FOREIGN KEY (HierarchicalPositionId) REFERENCES HierarchicalPositions(HierarchicalPositionId) ON DELETE cascade

INSERT INTO Roles(Name) VALUES ('Administrator')
INSERT INTO Roles(Name) VALUES ('Manager')
INSERT INTO Roles(Name) VALUES ('Contributor')
INSERT INTO Roles(Name) VALUES ('Cititor')

INSERT INTO HierarchicalPositions(Name) VALUES ('Rector')
INSERT INTO HierarchicalPositions(Name) VALUES ('Decan')
INSERT INTO HierarchicalPositions(Name) VALUES ('Profesor')
INSERT INTO HierarchicalPositions(Name) VALUES ('Doctorand')
INSERT INTO HierarchicalPositions(Name) VALUES ('Student')

INSERT INTO Users(Username, Password, Email) VALUES ('bogdan', 'bogdan', 'b@b.com')
INSERT INTO Users(Username, Password, Email) VALUES ('daniel', 'daniel', 'd@d.com')
INSERT INTO Users(Username, Password, Email) VALUES ('e', 'e', 'e@e.com')

INSERT INTO Users_Roles VALUES (1, 2)
INSERT INTO Users_Roles VALUES (2, 1)
INSERT INTO Users_Roles VALUES (2, 2)
INSERT INTO Users_Roles VALUES (3, 3)

INSERT INTO Users_HierarchicalPositions VALUES (1, 2)
INSERT INTO Users_HierarchicalPositions VALUES (2, 3)
INSERT INTO Users_HierarchicalPositions VALUES (3, 5)