create database if not exists CAPRIPoL;




CREATE TABLE Framework (
frameworkID int PRIMARY KEY auto_increment,
frameworkName varchar(255)
);

SELECT * FROM Framework;

DELETE FROM Framework;

CREATE TABLE FrameworkFoci (
frameworkID int,
focusID int,
PRIMARY KEY (frameworkID, focusID)
);

SELECT * FROM FrameworkFoci;

CREATE TABLE Focus (
focusID int PRIMARY KEY auto_increment,
focusCategory varchar(255) NOT NULL default(''),
focusName varchar(255) NOT NULL default(''),
focusExplanation varchar(255) NOT NULL default(''),
focusImage blob
);

INSERT INTO Focus(focusID, focusName)
VALUES
	(1, 'Math'),
    (2, 'Science');

INSERT INTO Focus(focusImage)
VALUES(LOAD_FILE('C:\Users\samuel\Desktop\nic.png)'));

SELECT * FROM Focus;

CREATE TABLE Roles (
roleID int PRIMARY KEY auto_increment,
roleName varchar(255)
);

INSERT INTO Roles(roleID, roleName)
VALUES
    (1, 'Student'),
    (2, 'Teacher'),
    (3, 'Admin');

SELECT * FROM Roles;

CREATE TABLE UserRoles (
userRoleID int PRIMARY KEY  auto_increment,
roleID int,
userID int,
FOREIGN KEY(roleID) references Roles(roleID),
FOREIGN KEY (userID) references Users(userID)
);

#1st user bob student and admin, bob1 is user
INSERT INTO UserRoles(userRoleID, roleID, userID)
VALUES
    (1, 1, 1),
    (2, 3, 1),
    (3, 1, 3);

SELECT * FROM UserRoles;

DELETE FROM UserRoles;

#Index table
#Should primary key be a composite of sighter, sightee and focus?
CREATE TABLE Sighting (
sightingID int PRIMARY KEY auto_increment,
sightedFocusID int,
dateSighted DATETIME NOT NULL,
#location varchar(255) NOT NULL,
sighterUserID int,
sighterUserRatingFocusID NUMERIC(5,2),
sighterSighteeRelationship varchar(255), #clarify the nature of this
sightingValue NUMERIC(5,2) NOT NULL,
sighteeUserID int,
FOREIGN KEY(sighterUserID) REFERENCES Users(userID),
FOREIGN KEY(sighteeUserID) REFERENCES Users(userID),
FOREIGN KEY(sightedFocusID) REFERENCES Focus(focusID),
INDEX ratingIndex(sightedFocusID, sighterUserID)
);

DELETE FROM Sighting;

SELECT * FROM Sighting;

CREATE TABLE FocusGroups (
groupID int PRIMARY KEY auto_increment,
groupName varchar(255) UNIQUE NOT NULL,
groupImage blob,
frameworkID int,
FOREIGN KEY(frameworkID) REFERENCES Framework(frameworkID)
);

INSERT INTO FocusGroups (groupID, groupName)
VALUES
    (1, 'teambob'),
    (2, 'teamnotbob'),
    (3, 'otherteambob');

SELECT * FROM FocusGroups;

ALTER TABLE FocusGroups AUTO_INCREMENT = 3;

DELETE FROM FocusGroups;

CREATE TABLE GroupMembers (
groupMemberID int PRIMARY KEY auto_increment,
groupID int,
userID int,
FOREIGN KEY(groupID) REFERENCES FocusGroups(groupID),
FOREIGN KEY(userID) REFERENCES Users(userID)
);

SELECT * FROM GroupMembers;

SELECT * FROM FocusGroups g JOIN GroupMembers GM on g.groupID = GM.groupID
WHERE GM.userID = 3;

CREATE TABLE GroupFoci (
focusID int,
groupID int,
PRIMARY KEY(focusID, groupID)
);

SELECT * FROM GroupFoci;

CREATE TABLE Ratings(
userID int,
focusID int,
PRIMARY KEY (userID, focusID)
);


#calculate rating. Each sighting has a rating.
#Select all sightings before time of current sighting, calculate formula.
#3+ NF.
DELIMITER //
DROP PROCEDURE IF EXISTS CalculateSightingValue;
CREATE PROCEDURE CalculateSightingValue(
    IN userID int,
    IN focusID int,
    OUT sightingValue NUMERIC(5,2))
BEGIN
    CALL CalculateUserRating(userID, focusID, @userRating);
    SELECT (@userRating/100)
        INTO sightingValue;
END //

CALL CalculateSightingValue(1,1, @sightingValue);
SELECT @sightingValue;

#If first sighting it will return 1 as user rating.
DELIMITER //
DROP PROCEDURE IF EXISTS CalculateUserRating;
CREATE PROCEDURE CalculateUserRating(
	IN userID int,
	IN focusID int,
	OUT userRating NUMERIC(5,2))
BEGIN
    SELECT IFNULL(1 + SUM(s.sightingValue), 1)
    INTO userRating
    FROM Sighting s
    WHERE
          s.sighteeUserID = userID
        AND
          s.sightedFocusID = focusID
        AND
          s.sighterUserID != userID;

END //
CALL CalculateUserRating(1, 1, @userRating);
SELECT @userRating;

DELIMITER //
DROP PROCEDURE IF EXISTS GetSightingSummary;
CREATE PROCEDURE GetSightingSummary(
	IN userID int,
	IN focusID int,
	OUT totalSightings int,
	OUT selfSightings int,
	OUT peerSightings int,
	OUT otherSightings int)
BEGIN
    SELECT count(*) INTO totalSightings
    FROM Sighting s
    WHERE
        s.sighteeUserID = userID;
    SELECT count(*) INTO selfSightings
    FROM Sighting s
    WHERE
        s.sighteeUserID = s.sighterUserID
        AND
        s.sighteeUserID = userID;
    SELECT count(*) INTO peerSightings
    FROM Sighting s
    WHERE
          s.sighteeUserID = userID
        AND
          s.sightedFocusID = focusID
        AND
          s.sighterUserID != userID;

END //
CALL CalculateUserRating(1, 1, @userRating);
SELECT @userRating;


#can pull rating from the table or pass it as a parameter
# DELIMITER //
# DROP PROCEDURE IF EXISTS UpdateRatingValues;
# CREATE PROCEDURE UpdateRatingValues(
#     IN currentDate date)
# BEGIN
#     UPDATE
#         Sighting s
#     SET
#         s.sightingValue = (SELECT )
#     WHERE
#
# END //



# DELIMITER //
# DROP PROCEDURE IF EXISTS CalculateSightingUserRating;
# CREATE PROCEDURE CalculateSightingUserRating(
# 	IN userID int,
# 	IN focusID int,
#     IN inputSightingID int)
# BEGIN
#     SET @yearsPassed = (SELECT TIMESTAMPDIFF(YEAR, ));
#
# 	UPDATE
# 		Sighting s
# 	SET
# 		s.sighterUserRatingFocusID = s.sighterUserRatingFocusID + (SELECT
# 			SUM(sightingValue)
# 		FROM (SELECT * FROM Sighting) AS s
#
# 		WHERE s.sightedFocusID = focusID AND s.sighterUserID = userID
# 		)
# 	WHERE s.sightingID = inputSightingID;
# END //

# CALL CalculateSightingUserRating(1, 1, 1);
# SELECT * FROM Sighting;



