create database if not exists CAPRIPoL;

CREATE TABLE Framework (
Framework_ID int PRIMARY KEY auto_increment,
Framework_Name varchar(255)
);

CREATE TABLE FrameworkFoci (
Framework_ID int,
Focus_ID int,
PRIMARY KEY (Framework_ID, Focus_ID)
);

CREATE TABLE Focus (
Focus_ID int PRIMARY KEY auto_increment,
Focus_Category varchar(255) NOT NULL,
Focus_Name varchar(255) NOT NULL,
Focus_Explanation varchar(255) NOT NULL,
Focus_Image blob
);


#Index table
CREATE TABLE Sighting (
Sighting_ID int PRIMARY KEY auto_increment,
Sighted_Focus_ID int,
#Date_Sighted date NOT NULL,
#Time_Sighted time NOT NULL,
#Location varchar(255) NOT NULL,
Sighter_User_ID int,
Sighter_User_Rating_Focus_ID int,
Sighter_Sightee_Relationship varchar(255), #clarify the nature of this
Sighting_Value int NOT NULL default(0),
Sightee_User_ID int,
FOREIGN KEY(Sighter_User_ID) REFERENCES MyUser(User_ID),
FOREIGN KEY(Sightee_User_ID) REFERENCES MyUser(User_ID),
FOREIGN KEY(Sighted_Focus_ID) REFERENCES Focus(Focus_ID)
);

INSERT INTO Sighting(Sighting_ID, Sighter_User_ID, Sighting_Value)
VALUES
	(1, 1, 10),
    (2, 1, 15),
    (3, 2, 10);
    
    
SELECT * FROM Sighting;

CREATE TABLE MyGroup ( #clarify
Group_ID int PRIMARY KEY auto_increment,
Group_Name varchar(255) NOT NULL,
Group_Image blob,
#Group_Member_User_ID int,
Group_Foci_Focus_ID int,
#foreign key(Group_Member_User_ID) references MyUser(User_ID),
foreign key(Group_Foci_Focus_ID) references Focus(Focus_ID)
);

CREATE TABLE GroupMember (
Group_ID int,
User_ID int, 
PRIMARY KEY(Group_ID, User_ID)
);

#calculate rating. Each sighting has a rating.
#Select all sightings before time of current sighting, calculate formula.
#3+ NF.

#Sighting is made, this gets called
#Updates the Sighter_User_Rating_Focus_ID based off previous sightings
DELIMITER //
DROP PROCEDURE IF EXISTS calculateRating;
CREATE PROCEDURE calculateRating(
	IN User_ID int,
    IN Sighting_ID int,
    OUT sightingValueSum int)
BEGIN
	UPDATE 
		Sighting s
	SET
		s.Sighter_User_Rating_Focus_ID = (SELECT
			SUM(Sighting_Value)
		-- INTO
-- 			sightingValueSum
		FROM 
			s
		WHERE
			s.Sighter_User_ID = User_ID
		)
	WHERE
		s.Sighting_ID = Sighting_ID;
END //
        
CALL calculateRating(1, @sightingValueSum);
SELECT @sightingValueSum;

SELECT * FROM Sighting;
		