Trivia-sql-statements.txt

CREATE TABLE `userdetails` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `fname` varchar(45) NOT NULL,
  `lname` varchar(45) NOT NULL,
  `email` varchar(320) NOT NULL,
  `age` int NOT NULL,
  `password` varchar(320) NOT NULL,
  `topic_key` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
);

CREATE TABLE `trivia-db`.`triviadetails` (
  `trivia_id` INT NOT NULL AUTO_INCREMENT,
  `trivia_content` VARCHAR(45) NOT NULL,
  `topic_id` INT NOT NULL,
  `likes` INT NOT NULL,
  `user_id` INT NOT NULL,
  PRIMARY KEY (`trivia_id`));

CREATE TABLE `trivia-db`.`likedetails` (
  `like_id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `trivia_id` INT NOT NULL,
  `status` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`like_id`));


CREATE TABLE `trivia-db`.`dislikedetails` (
  `dislike_id` INT NOT NULL,
  `user_id` INT NULL,
  `trivia_id` INT NULL,
  `dislike_status` VARCHAR(45) NULL,
  PRIMARY KEY (`dislike_id`));

CREATE PROCEDURE `addTopicKey` (IN topic_key varchar(45),email_id varchar(50))
BEGIN
UPDATE userdetails SET topic_key=topickey WHERE email=email_id;
END
  
CREATE DEFINER=`root`@`localhost` PROCEDURE `registerNewUser`(IN fname VARCHAR(45),lname VARCHAR(45),email VARCHAR(320),age INT,pass VARCHAR(320))
BEGIN
INSERT INTO userdetails(fname,lname,email,age,pass) VALUES(fname,lname,email,age,pass);
END


CREATE PROCEDURE `getUserId` ()
BEGIN
select MAX(user_id) from userdetails;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `addTrivia`(IN trivia_content VARCHAR(255),topic_id INT,likes INT,user_id INT)
BEGIN
INSERT INTO triviadetails(trivia_content,topic_id,likes,user_id) VALUES(trivia_content,topic_id,likes,user_id);
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `displayTrivia`(IN topicid INT)
BEGIN
select trivia_content,likes from triviadetails WHERE topic_id=topicid;
END


CREATE PROCEDURE `deleteTriviaById` (IN triviaid INT)
BEGIN
delete from triviadetails where trivia_id=triviaid;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `showTriviaByUser`(IN emailid varchar(255))
BEGIN
SELECT trivia_id,trivia_content from triviadetails where user_id=(select user_id from userdetails where email=emailid);
END

CREATE PROCEDURE `loginUser` (IN emailid varchar(320))
BEGIN
SELECT user_id,pass,topic_key from userdetails where email=emailid;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `addLikeByTopicId`(IN triviaid int)
BEGIN
update triviadetails set likes=likes+1 where trivia_id=triviaid;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `displayTriviaByTopic`(IN topicid int,userid int)
BEGIN
select trivia_id,trivia_content from triviadetails WHERE topic_id=topicid and not(user_id=userid) order by likes desc;
END

CREATE PROCEDURE `checkIfValidLike` (IN triviaid INT,userid INT)
BEGIN
select like_status from likedetails where (trivia_id=triviaid and user_id=userid);
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `addLikeInDB`(IN triviaid int,emailid varchar(320),likestatus varchar(45))
BEGIN
INSERT INTO likedetails(user_id,trivia_id,like_status) VALUES((select user_id from userdetails where email=emailid),triviaid,likestatus);
END

CREATE PROCEDURE `checkIfEmailExists` (IN email_id VARCHAR(50))
BEGIN
select user_id from userdetails where email=email_id;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `addTrivia`(IN email_id VARCHAR(255),trivia_content VARCHAR(255),topic_id INT,likes INT)
BEGIN
INSERT INTO triviadetails(trivia_content,topic_id,likes,user_id) VALUES(trivia_content,topic_id,likes,(select user_id from userdetails where email=email_id));
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `getTopicKey`(IN emailid varchar(50))
BEGIN
select topic_key from userdetails where email=email_id;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `displayTriviaByTopic`(IN emailid varchar(320),topicid int)
BEGIN
select trivia_id,trivia_content from triviadetails WHERE topic_id=topicid and not(user_id=(select user_id from userdetails where email=emailid)) order by likes desc;
END

CREATE PROCEDURE `addDislikeByTriviaId` (IN triviaid int)
BEGIN
update triviadetails set likes=likes-1 where trivia_id=triviaid;
END

CREATE PROCEDURE `addDislikeInDB` (IN triviaid int,emailid varchar(320),likestatus varchar(45))
BEGIN
INSERT INTO dislikedetails(user_id,trivia_id,dislike_status) VALUES((select user_id from userdetails where email=emailid),triviaid,dislikestatus);
END

USE `trivia-db`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `checkIfValidLike`(IN triviaid INT,emailid varchar(300))
BEGIN
select like_status from likedetails where (trivia_id=triviaid and user_id=(select user_id from userdetails where email=emailid));
END

USE `trivia-db`$$
CREATE PROCEDURE `checkIfValidDislike` (IN triviaid int,emailid varchar(200))
BEGIN
select dislike_status from dislikedetails where (trivia_id=triviaid and user_id=(select user_id from userdetails where email=emailid));
END

USE `trivia-db`$$
CREATE PROCEDURE `addFeedback` (IN emailid varchar(200),username varchar(200),feedback varchar(520),feeddate varchar(200))
BEGIN
insert into feedbackdetails(email,name,feedbackcontent,feedbackdate) values(emailid,username,feedback,feeddate);
END