<?php
require_once ("MySQLDB.php");
include("connect.php");

$host = 'localhost';
$dbUser = 'root';
$dbPass = '';
$dbName = 'ScoreboardDatabase';


// create a new empty database
$dbErrorHandling = new DBErrorHandling($host , $dbUser , $dbPass , $dbName );
$dbErrorHandling->createDatabase();
$dbErrorHandling->selectDatabase();

$sql = "drop table if exists leaderBoardGaming";
$result = $dbErrorHandling->query($sql);

$sql = "drop table if exists userActive";
$result = $dbErrorHandling->query($sql);

$sql = "drop table if exists userRegistry";
$result = $dbErrorHandling->query($sql);

$sql = "drop procedure if exists passwordVerify";
$result = $dbErrorHandling->query($sql);
// create tables
$table="userRegistry";
$sql = "CREATE TABLE `ScoreboardDatabase`.`userRegistry` (
  `userID` INT NOT NULL AUTO_INCREMENT,
  `userName` VARCHAR(45) NOT NULL,
  `userEmail` VARCHAR(45) NOT NULL,
  `userPassword` VARCHAR(255) NOT NULL,
  `userIDFriends` INT NULL,
  `userCountry` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`userID`));"  ;
$dbErrorHandling->createTable($table, $sql);

$table="userActiveTable";
$sql = "CREATE TABLE `ScoreboardDatabase`.`userActive` (
  `activeID` INT NOT NULL AUTO_INCREMENT,
  `userID` INT NULL,
  PRIMARY KEY (`activeID`),
  UNIQUE INDEX `userID_UNIQUE` (`userID`),
  CONSTRAINT `userID`
    FOREIGN KEY (`userID`)
    REFERENCES `scoreboarddatabase`.`userregistry` (`userID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);"  ;
$dbErrorHandling->createTable($table, $sql);

$table="leaderBoardGamingTable";
$sql = "CREATE TABLE `ScoreboardDatabase`.`leaderBoardGaming` (
  `leaderBoardID` INT NOT NULL AUTO_INCREMENT,
  `userIDL` INT NOT NULL,
  `userWin` INT NULL,
  `userLoss` INT NULL,
  `userScore` INT NULL,
  PRIMARY KEY (`leaderBoardID`),
  UNIQUE INDEX `userID_LeaderBoardGaming` (`userIDL`),
  CONSTRAINT `userIDL`
    FOREIGN KEY (`userIDL`)
    REFERENCES `scoreboarddatabase`.`userregistry` (`userID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);"  ;
$dbErrorHandling->createTable($table, $sql);

// Hash passwords
$clementsHashedPassword = password_hash('clementspassword123', PASSWORD_DEFAULT);
$johnsHashedPassword = password_hash('Pa$$w0rd', PASSWORD_DEFAULT);

// insert data
$sql = "INSERT INTO `userRegistry` (`userName`, `userEmail`, `userPassword`,  `userIDFriends`, `userCountry`)
VALUES ('Clement', 'Testmail.com', '$clementsHashedPassword', '2', 'New Zealand'), ('John', 'Testmail.com', '$johnsHashedPassword', '1', 'America');";
$dbErrorHandling->insertRow($sql);

$sql = "INSERT INTO `leaderBoardGaming` (`userIDL`, `userWin`, `userLoss`, `userScore`)
VALUES ('1', '2', '1', '1'), ('2', '3', '1', '2');";

$dbErrorHandling->insertRow($sql);

// insert procedure
$dbErrorHandling->query("CREATE PROCEDURE `passwordVerify` (IN `name` VARCHAR(45), IN `password` VARCHAR(45), OUT `passwordVerification` BOOLEAN)
BEGIN
SELECT IF(`userRegistry`.`userPassword` = `password`, TRUE, FALSE) AS 'verify'
FROM `userRegistry`
WHERE `userName` = `name`;
END;");

// insert triggers
$sql = "CREATE TRIGGER `ScoreboardDatabase`.`leaderBoardGaming_BEFORE_INSERT` BEFORE INSERT ON `leaderBoardGaming` FOR EACH ROW
BEGIN
	SET NEW.userScore = NEW.userWin / NEW.userLoss;
END;";

$dbErrorHandling->insertRow($sql);

$sql = "CREATE TRIGGER `ScoreboardDatabase`.`leaderBoardGaming_BEFORE_UPDATE` BEFORE UPDATE ON `leaderBoardGaming` FOR EACH ROW
BEGIN
	SET NEW.userScore = NEW.userWin / NEW.userLoss;
END;";

$dbErrorHandling->insertRow($sql);
?>
<br><br>