Notes

--Database name: ISS

CREATE TABLE [User](
[UserId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Email] [nchar](50) NULL,
[Password] [nchar](50) NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [Group](
[GroupId] int IDENTITY(1,1) PRIMARY KEY,
[GroupName] [varchar](50),
[ClaimTimeOut] DATETIME,
[MessageExpiryTime] DATETIME,
[MessagePoints] INT
)

CREATE TABLE Expert(
[ExpertId] [int] IDENTITY(1,1) PRIMARY KEY,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Email] [nchar](50) NULL,
[Password] [nchar](50) NULL,
[TotalPoints] [int] NULL,
[GroupId] [int] FOREIGN KEY REFERENCES [Group](GroupId)
)

CREATE TABLE MessageStatus
(
MessageStatusId INT PRIMARY KEY IDENTITY(1,1),
MessageStatus VARCHAR(25),
)

CREATE TABLE UserMessage
(
UserMessageId int PRIMARY KEY IDENTITY(1,1),
UserMessage NVARCHAR(MAX),
UserId INT FOREIGN KEY REFERENCES [User](UserId),
ExpertId INT FOREIGN KEY REFERENCES [Expert](ExpertId),
Expired BIT,
LastExpertDialogueDate DATETIME,
LastUserDialogueDate DATETIME,
IPAddress NVARCHAR(50),
CreatedDateTime DATETIME,
MessageStatusId INT FOREIGN KEY REFERENCES [MessageStatus](MessageStatusId),
)

CREATE TABLE [Dialogue]
(
DialogueId INT PRIMARY KEY IDENTITY(1,1),
CreatedDateTime DATETIME,
ExpertAnswer NVARCHAR(MAX),
GroupId INT FOREIGN KEY REFERENCES [Group](GroupId),
AnswerPoint INT,
UserMessageId INT FOREIGN KEY REFERENCES UserMessage(UserMessageId),
ExpertId INT FOREIGN KEY REFERENCES [Expert](ExpertId)
)

CREATE TABLE ExpertMessageInbox
(
ExpertMessageId INT PRIMARY KEY IDENTITY(1,1),
Responded BIT,
Deleted BIT,
UserId INT FOREIGN KEY REFERENCES [User](UserId),
UserMessageId INT FOREIGN KEY REFERENCES UserMessage(UserMessageId),
ExpertId INT FOREIGN KEY REFERENCES [Expert](ExpertId)
)

INSERT INTO MessageStatus VALUES('New');
INSERT INTO MessageStatus VALUES('In-Progress');
INSERT INTO MessageStatus VALUES('Completed');