CREATE TABLE Timelog( user INT, project INT, date DATE, time FLOAT, id INT AUTO_INCREMENT PRIMARY KEY, FOREIGN KEY (user) REFERENCES User (id));
CREATE TABLE Project( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE User ( f_name VARCHAR(50) , l_name VARCHAR(50) NOT NULL, mail VARCHAR(50) NOT NULL, id INT AUTO_INCREMENT PRIMARY KEY);

####
DELIMITER //

CREATE PROCEDURE InsertIntoUser(IN f_name_in VARCHAR(50), IN l_name_in VARCHAR(50), IN mail_in VARCHAR(50))
BEGIN
   INSERT INTO User(f_name,l_name,mail) VALUES(f_name_in,l_name_in,mail_in);
END //

DELIMITER ;

######
DELIMITER //

CREATE PROCEDURE InsertIntoProject()
BEGIN
   INSERT INTO Project(name) VALUES("My own");
   INSERT INTO Project(name) VALUES("Outcons");
   INSERT INTO Project(name) VALUES("Free time");
END //

DELIMITER ;

####
DELIMITER //

CREATE PROCEDURE InsertIntoTimeLog(IN user_in INT, IN project_in INT, IN time_in FLOAT, IN date_in DATE)
BEGIN
   INSERT INTO Timelog(user, project, time, date) VALUES(user_in, project_in, time_in, date_in);
END //

DELIMITER ;

####
DELIMITER //

CREATE PROCEDURE CleanTables()
BEGIN
  TRUNCATE TABLE Timelog;
  TRUNCATE TABLE Project;
  SET foreign_key_checks = 0;
  TRUNCATE TABLE User;
  SET foreign_key_checks = 1;
END //

DELIMITER ;

SELECT ROUTINE_NAME 
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' 
  AND ROUTINE_SCHEMA = 'timelog';

################

CREATE TEMPORARY TABLE temp_fname (fname VARCHAR(255));
INSERT INTO temp_fname (fname) VALUES 
  ( "John" ),
  ( "Gringo" ),
  ( "Mark" ),
  ( "Lisa" ),
  ( "Maria" ),
  ( "Sonya" ),
  ( "Philip" ),
  ( "Jose" ),
  ( "Lorenzo" ),
  ( "George" ),
  ( "Justin" );

CREATE TEMPORARY TABLE temp_lname (lname VARCHAR(255));
INSERT INTO temp_lname (lname) VALUES 
  ( "Johnson" ),
  ( "Lamas" ),
  ( "Jackson" ),
  ( "Brown" ),
  ( "Mason" ),
  ( "Rodriguez" ),
  ( "Roberts" ),
  ( "Thomas" ),
  ( "Rose" ),
  ( "McDonalds" );

CREATE TEMPORARY TABLE temp_mail (mail VARCHAR(255));
INSERT INTO temp_mail (mail) VALUES 
  ( "hotmail.com" ),
  ( "gmail.com" ),
  ( "live.com" );

INSERT INTO User (f_name, l_name, mail)
SELECT 
    (SELECT fname FROM temp_fname ORDER BY RAND() LIMIT 1),
    (SELECT lname FROM temp_lname ORDER BY RAND() LIMIT 1),
    (SELECT mail FROM temp_mail ORDER BY RAND() LIMIT 1)
FROM 
    (SELECT 1 FROM information_schema.tables LIMIT 100) AS temp;

UPDATE User
SET User.mail = CONCAT(User.f_name,".", User.l_name,"@", User.mail);

DELIMITER $$

CREATE PROCEDURE fill_timelog ()
BEGIN
  DECLARE j INT DEFAULT 1;
  DECLARE users INT DEFAULT 1;
  DECLARE logs INT;
  DECLARE hours FLOAT;
  DECLARE project INT;
  DECLARE curDate DATE DEFAULT "2024-11-18";

WHILE users <= 100 DO
    SET logs = FLOOR(1+(RAND()*20));
    SET j=1;
    WHILE j <= logs DO
      SET hours = (RAND() * (8 - 0.25)) + 0.25;
      SET curDate = DATE_ADD(curDate, INTERVAL 1 DAY);
      SET project = FLOOR(1+(RAND()*3));
      INSERT INTO Timelog (user, project, date,time ) VALUES (users,project,curDate,hours);
      SET j=j+1;
    END WHILE;
    SET users=users+1;
END WHILE;
END$$

DELIMITER ;


    SELECT t.user,t.date,t.project,u.f_name,u.l_name,p.name,SUM(t.time) as total_time
    FROM Timelog t 
    INNER JOIN Project p ON p.id=t.project 
    INNER JOIN User u ON u.id=t.user 
    GROUP BY t.user  
    ORDER BY total_time DESC
    LIMIT 10;