CREATE TABLE User ( f_name VARCHAR(50) , l_name VARCHAR(50) NOT NULL, mail VARCHAR(50) NOT NULL, id INT AUTO_INCREMENT PRIMARY KEY);
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));


DELIMITER $$

CREATE PROCEDURE CleanTables()
BEGIN
END $$

DELIMITER ;


DELIMITER $$
CREATE PROCEDURE InitDB()
BEGIN
DECLARE i INT DEFAULT 1;
TRUNCATE TABLE Timelog;
TRUNCATE TABLE Project;
SET foreign_key_checks = 0;
TRUNCATE TABLE User;
SET foreign_key_checks = 1;

INSERT INTO Project(name) VALUES("My own"),("Outcons"),("Free Time");

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" );
  
WHILE i <= 100 DO
  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);
  SET i = i + 1;
END WHILE;

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

CALL fill_timelog();
DROP TABLE temp_mail;
DROP TABLE temp_fname;
DROP TABLE temp_lname;
END$$
DELIMITER ;

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';
  DECLARE h2 INT;

WHILE users <= 100 DO
    SET logs = FLOOR(1+(RAND()*20));
    SET j=1;
    WHILE j <= logs DO
      SET project = FLOOR(1+(RAND()*3));
      SET curDate = DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND() * DATEDIFF('2020-02-01', '2020-01-01')) DAY);
      SET hours = (RAND() * (8 - 0.25)) + 0.25;

      SELECT SUM(time) INTO h2
      FROM Timelog
      WHERE date = curdate  && user = users ;

      WHILE (h2+hours) > 8 DO
        SET curDate = DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND() * DATEDIFF('2020-02-01', '2020-01-01')) DAY);

        SELECT SUM(time)INTO h2
        FROM Timelog
        WHERE date = curdate  && user = users ;

      END WHILE;
      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 ;

##
-- get data
SELECT t.time,t.date,p.name,u.f_name,u.l_name,u.mail FROM Timelog t INNER JOIN Project p ON p.id=t.project INNER JOIN User u ON u.id=t.user;
-- old timelog with adding each on a new day
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 project = FLOOR(1+(RAND()*3));
      SET curDate = DATE_ADD(curDate, INTERVAL 1 DAY);
      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 ;