CREATE TABLE `Project` (
`ProjectID` INT( 3 ) NOT NULL AUTO_INCREMENT ,
`ProjectName` VARCHAR( 30 ) NOT NULL ,
`StartDate` DATE NOT NULL ,
`EndDate` DATE NOT NULL ,
`AddedBy` INT( 2 ) NOT NULL ,
PRIMARY KEY ( `ProjectID` ) 
) COMMENT = 'Keeps project information';


CREATE TABLE `TeamMember` (
`MemberID` INT( 3 ) NOT NULL AUTO_INCREMENT ,
`UserName` VARCHAR( 20 ) NOT NULL ,
`Password` VARCHAR( 20 ) NOT NULL ,
`FirstName` VARCHAR( 20 ) NOT NULL ,
`LastName` VARCHAR( 20 ) NOT NULL ,
`EntryDate` DATE NOT NULL ,
`Phone` INT( 14 ) NOT NULL ,
`AccessRights` VARCHAR( 20 ) NOT NULL ,
`Designation` VARCHAR( 20 ) NOT NULL ,
PRIMARY KEY ( `MemberID` ) 
);


CREATE TABLE `Messenger` (
`MessengerID` INT( 2 ) NOT NULL AUTO_INCREMENT ,
`MessengerName` VARCHAR( 20 ) NOT NULL ,
`PortNumber` INT( 4 ) NOT NULL ,
PRIMARY KEY ( `MessengerID` ) 
) COMMENT = 'Keeps chat messengers information';


CREATE TABLE `MembersMsngrAccount` (
`MemberID` INT( 3 ) NOT NULL ,
`MessengerID` INT( 2 ) NOT NULL ,
`MesengerAccountID` VARCHAR( 30 ) NOT NULL 
) COMMENT = 'Keeps record of messengers Ids(which member has which messenger id(s)) ';

CREATE TABLE `MemberInProject` (
`ProjectID` INT( 3 ) NOT NULL ,
`MemberID` INT( 3 ) NOT NULL 
) COMMENT = 'Keeps team members record in which project they are working';

CREATE TABLE `Guest` (
`GuestID` INT( 3 ) NOT NULL AUTO_INCREMENT ,
`GuestUseMessengerID` INT( 2 ) NOT NULL ,
`GuestMessengerAddress` VARCHAR( 30 ) NOT NULL ,
`ChatID` INT( 6 ) NOT NULL ,
PRIMARY KEY ( `GuestID` ) 
) COMMENT = 'Keeps guests information participating in chatting';


CREATE TABLE `FeedBack` (
`SenderID` INT( 4 ) NOT NULL AUTO_INCREMENT ,
`FirstName` VARCHAR( 20 ) NOT NULL ,
`LastName` VARCHAR( 20 ) NOT NULL ,
`Email` VARCHAR( 30 ) NOT NULL ,
`CurrentDate` DATE NOT NULL ,
`Comments` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `SenderID` ) 
) COMMENT = 'saves feedback from users';

CREATE TABLE `ChatSession` (
`TBlID` INT( 6 ) NOT NULL AUTO_INCREMENT ,
`ChatID` INT( 6 ) NOT NULL ,
`MemberID` INT( 3 ) NOT NULL ,
`ProjectID` INT( 3 ) NOT NULL ,
`MessengerID` INT( 2 ) NOT NULL ,
PRIMARY KEY ( `TBlID` ) 
) COMMENT = 'Keeps chat sessions';

CREATE TABLE `ChatDetail` (
`ChatID` INT( 6 ) NOT NULL AUTO_INCREMENT ,
`ConversationStartDateTime` DATETIME NOT NULL ,
`ConversationEndDateTime` DATETIME NOT NULL ,
`RecordedBy` INT( 3 ) NOT NULL ,
`Conversation` TEXT NOT NULL ,
PRIMARY KEY ( `ChatID` ) 
) COMMENT = 'Saves chat details and conversation';

CREATE TABLE `Admin` (
`UserName` VARCHAR( 20 ) NOT NULL ,
`Password` VARCHAR( 20 ) NOT NULL ,
`FirstName` VARCHAR( 20 ) NOT NULL ,
`LastName` VARCHAR( 20 ) NOT NULL ,
`Designation` VARCHAR( 20 ) NOT NULL ,
`AccessRights` VARCHAR( 20 ) NOT NULL ,
`EntryDate` DATETIME NOT NULL ,
PRIMARY KEY ( `UserName` ) 
) COMMENT = 'Keeps Admin record';

alter  TABLE  `Project` 
Add FOREIGN KEY (AddedBy) REFERENCES TeamMember(MemberID)

alter  TABLE  `ChatSession` 
Add FOREIGN KEY (ChatID) REFERENCES ChatDetail(ChatID)

alter  TABLE  `ChatSession` 
Add FOREIGN KEY (MemberID) REFERENCES TeamMember(MemberID);

alter  TABLE  `ChatSession` 
Add FOREIGN KEY (ProjectID) REFERENCES Project(ProjectID);

alter  TABLE  `ChatSession` 
Add FOREIGN KEY (MessengerID) REFERENCES MembersMsngrAccount(MessengerAccountID);

alter  TABLE  `Guest` 
Add FOREIGN KEY (GuestUseMessengerID) REFERENCES Messenger(MessengerID);

alter  TABLE  `Guest` 
Add FOREIGN KEY (ChatID) REFERENCES ChatDetail(ChatID);

alter  TABLE  `ChatDetail` 
Add FOREIGN KEY (RecordedBy) REFERENCES MembersMsngrAccount(MemberID);

alter  TABLE  `MemberInProject` 
Add FOREIGN KEY (ProjectID) REFERENCES Project(ProjectID);

alter  TABLE  `MemberInProject` 
Add FOREIGN KEY (MemberID) REFERENCES MemberMsngrAccount(MemberID);

alter  TABLE  `MembersMsngrAccount` 
Add FOREIGN KEY (MemberID) REFERENCES TeamMember(MemberID);

alter  TABLE  `MembersMsngrAccount` 
Add FOREIGN KEY (MessengerID) REFERENCES Messenger(MessengerID);