<commit_before><?php
/* Copyright (c) 2012 Association France-ioi, MIT License http://opensource.org/licenses/MIT */

include_once("config.php");
include_once("../shared/common.php");
include_once("../shared/tinyORM.php");
include_once("common_contest.php");

function getGroupTeams($db, $groupID) {
   $stmt = $db->prepare("SELECT `team`.`ID`, `contestant`.`lastName`, `contestant`.`firstName` FROM `contestant` JOIN `team` ON `contestant`.`teamID` = `team`.`ID` JOIN `group` ON `team`.groupID = `group`.ID WHERE `team`.`groupID` = :groupID OR `group`.`parentGroupID` = :groupID");
   $stmt->execute(array("groupID" => $groupID));
   $teams = array();
   while ($row = $stmt->fetchObject()) {
      if (!isset($teams[$row->ID])) {
         $teams[$row->ID] = (object)array("nbContestants" => 0, "contestants" => array());
      }
      $teams[$row->ID]->nbContestants++;
      $contestant = (object)array("lastName" => htmlentities(utf8_decode($row->lastName)), "firstName" => htmlentities(utf8_decode($row->firstName)));
      $teams[$row->ID]->contestants[] = $contestant;
   }
   return $teams;
}

function getRandomID() {
   $rand = (string) mt_rand(100000, 999999999);
   $rand .= (string) mt_rand(1000000, 999999999);
   return $rand;
}

function handleLoadPublicGroups($db) {
   addBackendHint("ClientIP.loadPublicGroups");
   $stmt = $db->prepare("SELECT `group`.`name`, `group`.`code`, `contest`.`year`, `contest`.`category`, `contest`.`level` ".
      "FROM `group` JOIN `contest` ON (`group`.`contestID` = `contest`.`ID`) WHERE `isPublic` = 1 AND `contest`.`visibility` <> 'Hidden';");
   $stmt->execute(array());
   $groups = array();
   while ($row = $stmt->fetchObject()) {
      $groups[] = $row;
   }
   exitWithJson(array("success" => true, "groups" => $groups));
}

function getGroupForSubContest($db, $oldGroupID, $newContestID) {
   $query = "SELECT `group`.`ID`, `group`.`code` FROM `group` JOIN `group` `oldGroup` ON (`group`.`grade` = `oldGroup`.`grade` AND `group`.`schoolID` = `oldGroup`.`schoolID` AND `group`.`userID` = `oldGroup`.`userID`) WHERE `oldGroup`.ID = :oldGroupID AND `group`.`contestID` = :newContestID AND `group`.`parentGroupID` = :oldGroupID";
   $stmt = $db->prepare($query);
   $stmt->execute(array("oldGroupID" => $oldGroupID, "newContestID" => $newContestID));
   $row = $stmt->fetchObject();
   if ($row) {
      return array("code" => $row->code, "ID" => $row->ID);
   } else {
      $groupCode = genAccessCode($db);
      $groupPassword = genAccessCode($db);
      $groupID = getRandomID();
      $query = "INSERT INTO `group` (`ID`, `name`, `contestID`, `schoolID`, `userID`, `grade`, `expectedStartTime`, `startTime`, `code`, `password`, `isGenerated`, nbStudents, nbStudentsEffective, nbTeamsEffective, parentGroupID, language, minCategory, maxCategory) ".
         "SELECT :groupID, LEFT(CONCAT(IF(`oldGroup`.`isGenerated`, CONCAT(CONCAT('Indiv ', `oldGroup`.`grade`), ' '), CONCAT(`oldGroup`.`name`, '/')), `contest`.`name`), 50), :contestID, `oldGroup`.`schoolID`, `oldGroup`.`userID`, `oldGroup`.`grade`, NOW(), NOW(), :groupCode, :password, 1, 0, 0, 0, :oldGroupID, `contest`.`language`, `contest`.`categoryColor`, `contest`.`categoryColor` FROM `contest`, `group` `oldGroup` WHERE `contest`.`ID` = :contestID AND `oldGroup`.`ID` = :oldGroupID";
      $stmt = $db->prepare($query);
      $stmt->execute(array(
         "contestID" => $newContestID,
         "oldGroupID" => $oldGroupID,
         "groupID" => $groupID,
         "groupCode" => $groupCode,
         "password" => $groupPassword));
      return array("code" => $groupCode, "ID" => $groupID);
   }
}

function handleCreateTeam($db) {
   global $tinyOrm, $config;
   if (!isset($_POST["contestants"])) {
      exitWithJsonFailure("Informations sur les candidats manquantes");
   }
   if (!isset($_SESSION["groupID"])) {
      exitWithJsonFailure("Groupe non chargé");
   }
   if ($_SESSION["groupClosed"]) {
      error_log("Hack attempt ? trying to create team on closed group ".$_SESSION["groupID"]);
      exitWithJsonFailure("Groupe fermé");
   }
   if (isset($_POST["contestID"])) {
      if ($_SESSION["contestID"] != $_POST["contestID"]) {
         $_SESSION["contestID"] = $_POST["contestID"];
         $stmt = $db->prepare("SELECT `folder` FROM contest WHERE ID = ?");
         $stmt->execute(array($_SESSION["contestID"]));
         $row = $stmt->fetchObject();
         $_SESSION["contestFolder"] = $row->folder;
         $groupData = getGroupForSubContest($db, $_SESSION["groupID"], $_SESSION["contestID"]);
         $_SESSION["groupID"] = $groupData["ID"];
      }
   }
   // $_SESSION['userCode'] is set by optional password handling function,
   // see comments of createTeamFromUserCode in common_contest.php.
   $groupID = $_SESSION["groupID"];
   if (isset($_SESSION["userCode"]) && isset($_SESSION["userCodeGroupCode"]) && $_SESSION["userCodeGroupCode"] == $_SESSION["groupCode"]) {
      $password = $_SESSION["userCode"];
   } else {
      $password = genAccessCode($db);
   }
   unset($_SESSION["userCode"]);
   unset($_SESSION["userCodeGroupCode"]);
   $teamID = getRandomID();
   $stmt = $db->prepare("INSERT INTO `team` (`ID`, `groupID`, `password`, `nbMinutes`, `contestID`) VALUES (?, ?, ?, ?, ?)");
   $stmt->execute(array($teamID, $groupID, $password, $_SESSION["nbMinutes"], $_SESSION["contestID"]));
   if ($config->db->use == 'dynamoDB') {
      try {
         $tinyOrm->insert('team', array(
            'ID'       => $teamID,
            'groupID'  => $groupID,
            'password' => <PASSWORD>,
            'nbMinutes' => $_SESSION["nbMinutes"]
         ));
      } catch (Aws\DynamoDb\Exception\DynamoDbException $e) {
         error_log($e->getAwsErrorCode() . " - " . $e->getAwsErrorType());
         error_log('DynamoDB error creating team, teamID: '.$teamID);
      }
   }

   $contestants = $_POST["contestants"];
   $stmt = $db->prepare("UPDATE `group` SET `startTime` = UTC_TIMESTAMP() WHERE `group`.`ID` = ? AND `startTime` IS NULL");
   $stmt->execute(array($groupID));
   $stmt = $db->prepare("UPDATE `group` SET `nbTeamsEffective` = `nbTeamsEffective` + 1, `nbStudentsEffective` = `nbStudentsEffective` + ? WHERE `ID` = ?");
   $stmt->execute(array(count($contestants), $groupID));

   if (isset($_SESSION['mysqlOnly'])) {
      unset($_SESSION['mysqlOnly']);
   }
   $_SESSION["teamID"] = $teamID;
   $_SESSION["teamPassword"] = $password;
   foreach ($contestants as $contestant) {
      if (isset($contestant["registrationCode"])) {
         $stmt = $db->prepare("INSERT INTO `contestant` (`ID`, `lastName`, `firstName`, `genre`, `grade`, `studentId`, `teamID`, `cached_schoolID`, `saniValid`, `email`, `zipCode`, `registrationID`)
         SELECT :contestantID, `lastName`, `firstName`, `genre`, `grade`, `studentId`, :teamID, `schoolID`, 1, `email`, `zipCode`, `ID`
         FROM `algorea_registration` WHERE `algorea_registration`.`code` = :code");
         $stmt->execute(array(
            "contestantID" => getRandomID(),
            "code" => $contestant["registrationCode"],
            "teamID" => $teamID
         ));
      } else {
         if ((!isset($contestant["grade"])) || ($contestant["grade"] == '')) {
            $contestant["grade"] = -2;
         }
         if (!isset($contestant["genre"])) {
            $contestant["genre"] = 0;
         }
         if (!isset($contestant["studentId"])) {
            $contestant["studentId"] = "";
         }
         list($contestant["firstName"], $contestant["lastName"], $saniValid, $trash) =
            DataSanitizer::formatUserNames($contestant["firstName"], $contestant["lastName"]);
         $stmt = $db->prepare("
            INSERT INTO `contestant` (`ID`, `lastName`, `firstName`, `genre`, `grade`, `studentId`, `teamID`, `cached_schoolID`, `saniValid`, `email`, `zipCode`)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
         $stmt->execute(array(getRandomID(), $contestant["lastName"], $contestant["firstName"], $contestant["genre"], $contestant["grade"], $contestant["studentId"], $teamID, $_SESSION["schoolID"], $saniValid, $contestant["email"], $contestant["zipCode"]));
      }
   }
   addBackendHint(sprintf("ClientIP.createTeam:%s", $_SESSION['isPublic'] ? 'public' : 'private'));
   addBackendHint(sprintf("Group(%s):createTeam", escapeHttpValue($groupID)));
   exitWithJson((object)array("success" => true, "teamID" => $teamID, "password" => <PASSWORD>));
}

function updateDynamoDBStartTime($db, $teamID) {
   global $tinyOrm, $config;
   if ($config->db->use == 'dynamoDB' && (!isset($_SESSION["mysqlOnly"]) || !$_SESSION["mysqlOnly"])) {
      $stmt = $db->prepare("SELECT `startTime` FROM `team` WHERE `ID` = :teamID");
      $stmt->execute(array("teamID" => $teamID));
      $startTime = $stmt->fetchColumn();
      try {
         $tinyOrm->update('team', array('startTime' => $startTime), array('ID'=>$teamID));
      } catch (Aws\DynamoDb\Exception\DynamoDbException $e) {
         error_log($e->getAwsErrorCode() . " - " . $e->getAwsErrorType());
         error_log('DynamoDB error updating team for teamID: '.$teamID);
      }
   }
}

function handleStartTimer($db) {
   $teamID = $_SESSION["teamID"];
   $stmt = $db->prepare("UPDATE `team` SET `startTime` = UTC_TIMESTAMP() WHERE `ID` = :teamID AND `startTime` IS NULL");
   $stmt->execute(array("teamID" => $teamID));
   updateDynamoDBStartTime($db, $teamID);

   $remainingSeconds = getRemainingSeconds($db, $teamID, true);

   if ($remainingSeconds <= 0) {
      $_SESSION["closed"] = true;
   } else {
      unset($_SESSION["closed"]);
   }

   exitWithJson((object)array(
      "success" => true,
      "remainingSeconds" => $remainingSeconds,
      "ended" => ($remainingSeconds <= 0) && ($_SESSION["nbMinutes"] > 0)
   ));
}

function handleLoadContestData($db) {
   global $tinyOrm, $config;
   if (!isset($_SESSION["teamID"])) {
      if (!isset($_POST["groupPassword"])) {
         exitWithJsonFailure("Mot de passe manquant");
      }
      if (!isset($_POST["teamID"])) {
         exitWithJsonFailure("Équipe manquante");
      }
      if (!isset($_SESSION["groupID"])) {
         exitWithJsonFailure("Groupe non chargé");
      }
      $password = strtolower(trim($_POST["groupPassword"]));
      reloginTeam($db, $password, $_POST["teamID"]);
   }
   $teamID = $_SESSION["teamID"];
   $stmt = $db->prepare("UPDATE `team` SET `createTime` = UTC_TIMESTAMP() WHERE `ID` = :teamID AND `createTime` IS NULL");
   $stmt->execute(array("teamID" => $teamID));
   
   $questionsData = getQuestions($db, $_SESSION["contestID"], $_SESSION["subsetsSize"], $teamID);
   $mode = null;
   if (isset($_SESSION['mysqlOnly']) && $_SESSION['mysqlOnly']) {
      $mode = 'mysql';
   }
   try {
      $results = $tinyOrm->select('team_question', array('questionID', 'answer', 'ffScore', 'score'), array('teamID' =>$teamID), null, $mode);
   } catch (Aws\DynamoDb\Exception\DynamoDbException $e) {
      if (strval($e->getAwsErrorCode()) != 'ConditionalCheckFailedException') {
         error_log($e->getAwsErrorCode() . " - " . $e->getAwsErrorType());
         error_log('DynamoDB error retrieving team_questions for teamID: '.$teamID);
      }
      $results = [];
   }
   $answers = array();
   $scores = array();
   foreach($results as $row) {
      if (isset($row['answer'])) {
         $answers[$row['questionID']] = $row['answer'];
      }
      if (isset($row['score'])) {
         $scores[$row['questionID']] = $row['score'];
      } elseif (isset($row['ffScore'])) {
         $scores[$row['questionID']] = $row['ffScore'];
      }
   }

   addBackendHint("ClientIP.loadContestData:pass");
   addBackendHint(sprintf("Team(%s):loadContestData", escapeHttpValue($teamID)));
   exitWithJson((object)array(
      "success" => true,
      "questionsData" => $questionsData,
      'scores' => $scores,
      "answers" => $answers,
      "isTimed" => ($_SESSION["nbMinutes"] > 0),
      "teamPassword" => $_SESSION["<PASSWORD>"]
   ));
}

function handleCloseContest($db) {
   if (!isset($_SESSION["teamID"]) && !reconnectSession($db)) {
      exitWithJsonFailure("Pas de session en cours");
   }
   $teamID = $_SESSION["teamID"];
   $stmtUpdate = $db->prepare("UPDATE `team` SET `endTime` = UTC_TIMESTAMP() WHERE `ID` = ? AND `endTime` is NULL");
   $stmtUpdate->execute(array($teamID));
   $_SESSION["closed"] = true;
   $stmt = $db->prepare("SELECT `endTime` FROM `team` WHERE `ID` = ?");
   $stmt->execute(array($teamID));
   $row = $stmt->fetchObject();
   addBackendHint("ClientIP.closeContest:pass");
   addBackendHint(sprintf("Team(%s):closeContest", escapeHttpValue($teamID)));
   exitWithJson((object)array("success" => true));
}

function handleLoadSession() {
   global $config;
   $sid = session_id();
   // If the session is new or closed, just return the SID.
   if (!isset($_SESSION["teamID"]) || isset($_SESSION["closed"])) {
      addBackendHint("ClientIP.loadSession:new");
      exitWithJson(['success' => true, "SID" => $sid]);
   }
   // Otherwise, data from the session is also returned.
   addBackendHint("ClientIP.loadSession:found");
   addBackendHint(sprintf("SessionId(%s):loadSession", escapeHttpValue($sid)));
   $message = "Voulez-vous reprendre l'épreuve commencée ?";
   if ($config->defaultLanguage == "es") {
      $message = "¿Desea reiniciar la prueba comenzada anteriormente?";
   }
   exitWithJson(array(
      "success" => true,
      "teamID" => $_SESSION["teamID"],
      "message" => $message,
      "nbMinutes" => $_SESSION["nbMinutes"],
      "bonusScore" => $_SESSION["bonusScore"],
      "allowTeamsOfTwo" => $_SESSION["allowTeamsOfTwo"],
      "newInterface" => $_SESSION["newInterface"],
      "customIntro" => $_SESSION["customIntro"],
      "fullFeedback" => $_SESSION["fullFeedback"],
      "nbUnlockedTasksInitial" => $_SESSION["nbUnlockedTasksInitial"],
      "subsetsSize" => $_SESSION["subsetsSize"],
      "contestID" => $_SESSION["contestID"],
      "isPublic" => $_SESSION["isPublic"],
      "contestFolder" => $_SESSION["contestFolder"],
      "contestName" => $_SESSION["contestName"],
      "contestOpen" => $_SESSION["contestOpen"],
      "contestShowSolutions" => $_SESSION["contestShowSolutions"],
      "contestVisibility" => $_SESSION["contestVisibility"],
      "SID" => $sid));
}

function handleDestroySession() {
   $sid = session_id();
   addBackendHint("ClientIP.destroySession");
   restartSession();
   exitWithJson(array("success" => true, "SID" => $sid));
}

function handleCheckPassword($db) {
   global $config;

   addFailureBackendHint("ClientIP.checkPassword:fail");
   addFailureBackendHint("ClientIP.error");

   // Check common.js version
   $commonJsVersion = isset($_POST['commonJsVersion']) ? intval($_POST['commonJsVersion']) : 0;
   $commonJsTimestamp = isset($_POST['commonJsTimestamp']) ? $_POST['commonJsTimestamp'] : '[none]';
   $timestamp = isset($_POST['timestamp']) ? $_POST['timestamp'] : '[none]';

   if($commonJsVersion < $config->minimumCommonJsVersion) {
      // Reject old common.js versions
      $errormsg = "Mauvaise version de common.js : client ".$commonJsVersion." < minimum ".$config->minimumCommonJsVersion." (server timestamp ".$timestamp.", common.js loaded at ".$commonJsTimestamp.").";

      // Log error
      $stmt = $db->prepare('insert into error_log (date, message) values (UTC_TIMESTAMP(), :errormsg);');
      $stmt->execute(['errormsg' => $errormsg]);
      unset($stmt);

      // Send back error message to user
      $userMsg = "Vous utilisez une ancienne version de l'interface, veuillez rafraîchir la page. Si cela ne règle pas le problème, essayez de vider votre cache.";
      if(isset($config->contestBackupURL)) {
         $userMsg .= " Vous pouvez aussi essayer le domaine alternatif du concours http://" . $config->contestBackupURL . ".";
      }
      exitWithJsonFailure($userMsg);
   }

   if (!isset($_POST["password"])) {
      exitWithJsonFailure("Mot de passe manquant");
   }
   $getTeams = array_key_exists('getTeams', $_POST) ? $_POST["getTeams"] : False;
   $password = strtolower($_POST["password"]);
   // Search for a group matching the entered password, and if found create
   // a team in that group (and end the request).
   handleCheckGroupPassword($db, $password, $getTeams);

   handleGroupFromRegistrationCode($db, $password);

   // If no matching group was found, look for a team with the entered password.
   handleCheckTeamPassword($db, $password);
}

function getRegistrationData($db, $code) {
   $query = "SELECT `algorea_registration`.`ID`, `code`, `category`, `firstName`, `lastName`, `genre`, `grade`, `studentID`, `email`, `zipCode`, ".
      "IFNULL(`algorea_registration`.`schoolID`, 0) as `schoolID`, IFNULL(`algorea_registration`.  `userID`, 0) as `userID`, IFNULL(`school_user`.`allowContestAtHome`, 1) as `allowContestAtHome` ".
      "FROM `algorea_registration` ".
      "LEFT JOIN `school_user` ON (`school_user`.`schoolID` = `algorea_registration`.`schoolID` AND `school_user`.`userID` = `algorea_registration`.`userID`) ".
      "WHERE `code` = :code";
   $stmt = $db->prepare($query);
   $stmt->execute(array("code" => $code));
   return $stmt->fetchObject();
}




function createGroupForContestAndRegistrationCode($db, $code, $contestID) {
   $groupCode = genAccessCode($db);
   $groupPassword = genAccessCode($db);
   $groupID = getRandomID();
   $query = "INSERT INTO `group` (`ID`, `name`, `contestID`, `schoolID`, `userID`, `grade`, `expectedStartTime`, `startTime`, `code`, `password`, `isGenerated`, nbStudents, nbStudentsEffective, nbTeamsEffective) ".
      "SELECT :groupID, LEFT(CONCAT(CONCAT(CONCAT('Indiv', `algorea_registration`.`grade`), ' '), `contest`.`name`), 50), :contestID, `algorea_registration`.`schoolID`, `algorea_registration`.`userID`, `algorea_registration`.`grade`, NOW(), NOW(), :groupCode, :password, 1, 0, 0, 0 FROM `contest`, `algorea_registration` WHERE `contest`.`ID` = :contestID AND `algorea_registration`.`code` = :code";
   $stmt = $db->prepare($query);
   $stmt->execute(array(
      "contestID" => $contestID,
      "code" => $code,
      "groupID" => $groupID,
      "groupCode" => $groupCode,
      "password" => $groupPassword));
   return array("code" => $groupCode, "ID" => $groupID);
}

function handleGroupFromRegistrationCode($db, $code) {
   $registrationData = getRegistrationData($db, $code);
   if (!$registrationData) {
      return;
   }
   $registrationData->category = updateRegisteredUserCategory($db, $registrationData->ID, $registrationData->category);
   
   $query = "SELECT IFNULL(tmp.score, 0) as score, IFNULL(tmp.sumScores, 0) as sumScores, tmp.password, tmp.startTime, tmp.contestName, tmp.contestID, tmp.parentContestID, tmp.contestCategory, ".
       "tmp.nbMinutes, tmp.remainingSeconds, tmp.teamID, ".
       "GROUP_CONCAT(CONCAT(CONCAT(contestant.firstName, ' '), contestant.lastName)) as contestants ".
       "FROM (SELECT team.ID as teamID, team.score, SUM(team_question.ffScore) as sumScores, team.password, team.startTime, contest.ID as contestID, contest.parentContestID, contest.name as contestName, contest.categoryColor as contestCategory, ".
       "team.nbMinutes, (team.`nbMinutes` * 60) - TIME_TO_SEC(TIMEDIFF(UTC_TIMESTAMP(), `team`.`startTime`)) as remainingSeconds ".
       "FROM `contestant` ".
       "JOIN team ON `contestant`.teamID = `team`.ID ".
       "JOIN `group` ON team.groupID = `group`.ID ".
       "JOIN `contest` ON `group`.contestID = `contest`.ID ".
       "LEFT JOIN `team_question` ON team_question.teamID = team.ID ".
       "WHERE contestant.registrationID = :registrationID ".
       "GROUP BY team.ID) tmp ".
       "JOIN contestant ON tmp.teamID = contestant.teamID ".
       "GROUP BY tmp.teamID ".
       "ORDER BY tmp.startTime ASC";
   $stmt = $db->prepare($query);
   $stmt->execute(array("registrationID" => $registrationData->ID));
   $participations = array();
   while ($row = $stmt->fetchObject()) {
      $participations[] = $row;
   }
   $registrationData->participations = $participations;
       
   addBackendHint("ClientIP.checkPassword:<PASSWORD>");
   addBackendHint(sprintf("Group(%s):checkPassword", escapeHttpValue($registrationData->ID))); // TODO : check hint
   $contestID = "485926402649945250"; // hard-coded training contest
   $isOfficialContest = false;
   if (isset($_POST["startOfficial"])) {
      $contestID = "884044050337033997"; // hard-coded real contest
      $isOfficialContest = true;
   }
   $query = "SELECT `code` FROM `group` WHERE `contestID` = :contestID AND `schoolID` = :schoolID AND `userID` = :userID AND `grade` = :grade";
   $stmt = $db->prepare($query);
   $stmt->execute(array("contestID" => $contestID, "schoolID" => $registrationData->schoolID, "userID" => $registrationData->userID, "grade" => $registrationData->grade));
   $rowGroup = $stmt->fetchObject();
   if (!$rowGroup) {
      $groupData = createGroupForContestAndRegistrationCode($db, $code, $contestID);<commit_msg>fix attaching participation to manual group<commit_after><?php
/* Copyright (c) 2012 Association France-ioi, MIT License http://opensource.org/licenses/MIT */

include_once("config.php");
include_once("../shared/common.php");
include_once("../shared/tinyORM.php");
include_once("common_contest.php");

function getGroupTeams($db, $groupID) {
   $stmt = $db->prepare("SELECT `team`.`ID`, `contestant`.`lastName`, `contestant`.`firstName` FROM `contestant` JOIN `team` ON `contestant`.`teamID` = `team`.`ID` JOIN `group` ON `team`.groupID = `group`.ID WHERE `team`.`groupID` = :groupID OR `group`.`parentGroupID` = :groupID");
   $stmt->execute(array("groupID" => $groupID));
   $teams = array();
   while ($row = $stmt->fetchObject()) {
      if (!isset($teams[$row->ID])) {
         $teams[$row->ID] = (object)array("nbContestants" => 0, "contestants" => array());
      }
      $teams[$row->ID]->nbContestants++;
      $contestant = (object)array("lastName" => htmlentities(utf8_decode($row->lastName)), "firstName" => htmlentities(utf8_decode($row->firstName)));
      $teams[$row->ID]->contestants[] = $contestant;
   }
   return $teams;
}

function getRandomID() {
   $rand = (string) mt_rand(100000, 999999999);
   $rand .= (string) mt_rand(1000000, 999999999);
   return $rand;
}

function handleLoadPublicGroups($db) {
   addBackendHint("ClientIP.loadPublicGroups");
   $stmt = $db->prepare("SELECT `group`.`name`, `group`.`code`, `contest`.`year`, `contest`.`category`, `contest`.`level` ".
      "FROM `group` JOIN `contest` ON (`group`.`contestID` = `contest`.`ID`) WHERE `isPublic` = 1 AND `contest`.`visibility` <> 'Hidden';");
   $stmt->execute(array());
   $groups = array();
   while ($row = $stmt->fetchObject()) {
      $groups[] = $row;
   }
   exitWithJson(array("success" => true, "groups" => $groups));
}

function getGroupForSubContest($db, $oldGroupID, $newContestID) {
   $query = "SELECT `group`.`ID`, `group`.`code` FROM `group` JOIN `group` `oldGroup` ON (`group`.`grade` = `oldGroup`.`grade` AND `group`.`schoolID` = `oldGroup`.`schoolID` AND `group`.`userID` = `oldGroup`.`userID`) WHERE `oldGroup`.ID = :oldGroupID AND `group`.`contestID` = :newContestID AND `group`.`parentGroupID` = :oldGroupID";
   $stmt = $db->prepare($query);
   $stmt->execute(array("oldGroupID" => $oldGroupID, "newContestID" => $newContestID));
   $row = $stmt->fetchObject();
   if ($row) {
      return array("code" => $row->code, "ID" => $row->ID);
   } else {
      $groupCode = genAccessCode($db);
      $groupPassword = genAccessCode($db);
      $groupID = getRandomID();
      $query = "INSERT INTO `group` (`ID`, `name`, `contestID`, `schoolID`, `userID`, `grade`, `expectedStartTime`, `startTime`, `code`, `password`, `isGenerated`, nbStudents, nbStudentsEffective, nbTeamsEffective, parentGroupID, language, minCategory, maxCategory) ".
         "SELECT :groupID, LEFT(CONCAT(IF(`oldGroup`.`isGenerated`, CONCAT(CONCAT('Indiv ', `oldGroup`.`grade`), ' '), CONCAT(`oldGroup`.`name`, '/')), `contest`.`name`), 50), :contestID, `oldGroup`.`schoolID`, `oldGroup`.`userID`, `oldGroup`.`grade`, NOW(), NOW(), :groupCode, :password, 1, 0, 0, 0, :oldGroupID, `contest`.`language`, `contest`.`categoryColor`, `contest`.`categoryColor` FROM `contest`, `group` `oldGroup` WHERE `contest`.`ID` = :contestID AND `oldGroup`.`ID` = :oldGroupID";
      $stmt = $db->prepare($query);
      $stmt->execute(array(
         "contestID" => $newContestID,
         "oldGroupID" => $oldGroupID,
         "groupID" => $groupID,
         "groupCode" => $groupCode,
         "password" => $groupPassword));
      return array("code" => $groupCode, "ID" => $groupID);
   }
}

function handleCreateTeam($db) {
   global $tinyOrm, $config;
   if (!isset($_POST["contestants"])) {
      exitWithJsonFailure("Informations sur les candidats manquantes");
   }
   if (!isset($_SESSION["groupID"])) {
      exitWithJsonFailure("Groupe non chargé");
   }
   if ($_SESSION["groupClosed"]) {
      error_log("Hack attempt ? trying to create team on closed group ".$_SESSION["groupID"]);
      exitWithJsonFailure("Groupe fermé");
   }
   if (isset($_POST["contestID"])) {
      if ($_SESSION["contestID"] != $_POST["contestID"]) {
         $_SESSION["contestID"] = $_POST["contestID"];
         $stmt = $db->prepare("SELECT `folder` FROM contest WHERE ID = ?");
         $stmt->execute(array($_SESSION["contestID"]));
         $row = $stmt->fetchObject();
         $_SESSION["contestFolder"] = $row->folder;
         $groupData = getGroupForSubContest($db, $_SESSION["groupID"], $_SESSION["contestID"]);
         $_SESSION["groupID"] = $groupData["ID"];
      }
   }
   // $_SESSION['userCode'] is set by optional password handling function,
   // see comments of createTeamFromUserCode in common_contest.php.
   $groupID = $_SESSION["groupID"];
   if (isset($_SESSION["userCode"]) && isset($_SESSION["userCodeGroupCode"]) && $_SESSION["userCodeGroupCode"] == $_SESSION["groupCode"]) {
      $password = $_SESSION["userCode"];
   } else {
      $password = genAccessCode($db);
   }
   unset($_SESSION["userCode"]);
   unset($_SESSION["userCodeGroupCode"]);
   $teamID = getRandomID();
   $stmt = $db->prepare("INSERT INTO `team` (`ID`, `groupID`, `password`, `nbMinutes`, `contestID`) VALUES (?, ?, ?, ?, ?)");
   $stmt->execute(array($teamID, $groupID, $password, $_SESSION["nbMinutes"], $_SESSION["contestID"]));
   if ($config->db->use == 'dynamoDB') {
      try {
         $tinyOrm->insert('team', array(
            'ID'       => $teamID,
            'groupID'  => $groupID,
            'password' => <PASSWORD>,
            'nbMinutes' => $_SESSION["nbMinutes"]
         ));
      } catch (Aws\DynamoDb\Exception\DynamoDbException $e) {
         error_log($e->getAwsErrorCode() . " - " . $e->getAwsErrorType());
         error_log('DynamoDB error creating team, teamID: '.$teamID);
      }
   }

   $contestants = $_POST["contestants"];
   $stmt = $db->prepare("UPDATE `group` SET `startTime` = UTC_TIMESTAMP() WHERE `group`.`ID` = ? AND `startTime` IS NULL");
   $stmt->execute(array($groupID));
   $stmt = $db->prepare("UPDATE `group` SET `nbTeamsEffective` = `nbTeamsEffective` + 1, `nbStudentsEffective` = `nbStudentsEffective` + ? WHERE `ID` = ?");
   $stmt->execute(array(count($contestants), $groupID));

   if (isset($_SESSION['mysqlOnly'])) {
      unset($_SESSION['mysqlOnly']);
   }
   $_SESSION["teamID"] = $teamID;
   $_SESSION["teamPassword"] = $password;
   foreach ($contestants as $contestant) {
      if (isset($contestant["registrationCode"])) {
         $stmt = $db->prepare("INSERT INTO `contestant` (`ID`, `lastName`, `firstName`, `genre`, `grade`, `studentId`, `teamID`, `cached_schoolID`, `saniValid`, `email`, `zipCode`, `registrationID`)
         SELECT :contestantID, `lastName`, `firstName`, `genre`, `grade`, `studentId`, :teamID, `schoolID`, 1, `email`, `zipCode`, `ID`
         FROM `algorea_registration` WHERE `algorea_registration`.`code` = :code");
         $stmt->execute(array(
            "contestantID" => getRandomID(),
            "code" => $contestant["registrationCode"],
            "teamID" => $teamID
         ));
      } else {
         if ((!isset($contestant["grade"])) || ($contestant["grade"] == '')) {
            $contestant["grade"] = -2;
         }
         if (!isset($contestant["genre"])) {
            $contestant["genre"] = 0;
         }
         if (!isset($contestant["studentId"])) {
            $contestant["studentId"] = "";
         }
         list($contestant["firstName"], $contestant["lastName"], $saniValid, $trash) =
            DataSanitizer::formatUserNames($contestant["firstName"], $contestant["lastName"]);
         $stmt = $db->prepare("
            INSERT INTO `contestant` (`ID`, `lastName`, `firstName`, `genre`, `grade`, `studentId`, `teamID`, `cached_schoolID`, `saniValid`, `email`, `zipCode`)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
         $stmt->execute(array(getRandomID(), $contestant["lastName"], $contestant["firstName"], $contestant["genre"], $contestant["grade"], $contestant["studentId"], $teamID, $_SESSION["schoolID"], $saniValid, $contestant["email"], $contestant["zipCode"]));
      }
   }
   addBackendHint(sprintf("ClientIP.createTeam:%s", $_SESSION['isPublic'] ? 'public' : 'private'));
   addBackendHint(sprintf("Group(%s):createTeam", escapeHttpValue($groupID)));
   exitWithJson((object)array("success" => true, "teamID" => $teamID, "password" => <PASSWORD>));
}

function updateDynamoDBStartTime($db, $teamID) {
   global $tinyOrm, $config;
   if ($config->db->use == 'dynamoDB' && (!isset($_SESSION["mysqlOnly"]) || !$_SESSION["mysqlOnly"])) {
      $stmt = $db->prepare("SELECT `startTime` FROM `team` WHERE `ID` = :teamID");
      $stmt->execute(array("teamID" => $teamID));
      $startTime = $stmt->fetchColumn();
      try {
         $tinyOrm->update('team', array('startTime' => $startTime), array('ID'=>$teamID));
      } catch (Aws\DynamoDb\Exception\DynamoDbException $e) {
         error_log($e->getAwsErrorCode() . " - " . $e->getAwsErrorType());
         error_log('DynamoDB error updating team for teamID: '.$teamID);
      }
   }
}

function handleStartTimer($db) {
   $teamID = $_SESSION["teamID"];
   $stmt = $db->prepare("UPDATE `team` SET `startTime` = UTC_TIMESTAMP() WHERE `ID` = :teamID AND `startTime` IS NULL");
   $stmt->execute(array("teamID" => $teamID));
   updateDynamoDBStartTime($db, $teamID);

   $remainingSeconds = getRemainingSeconds($db, $teamID, true);

   if ($remainingSeconds <= 0) {
      $_SESSION["closed"] = true;
   } else {
      unset($_SESSION["closed"]);
   }

   exitWithJson((object)array(
      "success" => true,
      "remainingSeconds" => $remainingSeconds,
      "ended" => ($remainingSeconds <= 0) && ($_SESSION["nbMinutes"] > 0)
   ));
}

function handleLoadContestData($db) {
   global $tinyOrm, $config;
   if (!isset($_SESSION["teamID"])) {
      if (!isset($_POST["groupPassword"])) {
         exitWithJsonFailure("Mot de passe manquant");
      }
      if (!isset($_POST["teamID"])) {
         exitWithJsonFailure("Équipe manquante");
      }
      if (!isset($_SESSION["groupID"])) {
         exitWithJsonFailure("Groupe non chargé");
      }
      $password = strtolower(trim($_POST["groupPassword"]));
      reloginTeam($db, $password, $_POST["teamID"]);
   }
   $teamID = $_SESSION["teamID"];
   $stmt = $db->prepare("UPDATE `team` SET `createTime` = UTC_TIMESTAMP() WHERE `ID` = :teamID AND `createTime` IS NULL");
   $stmt->execute(array("teamID" => $teamID));
   
   $questionsData = getQuestions($db, $_SESSION["contestID"], $_SESSION["subsetsSize"], $teamID);
   $mode = null;
   if (isset($_SESSION['mysqlOnly']) && $_SESSION['mysqlOnly']) {
      $mode = 'mysql';
   }
   try {
      $results = $tinyOrm->select('team_question', array('questionID', 'answer', 'ffScore', 'score'), array('teamID' =>$teamID), null, $mode);
   } catch (Aws\DynamoDb\Exception\DynamoDbException $e) {
      if (strval($e->getAwsErrorCode()) != 'ConditionalCheckFailedException') {
         error_log($e->getAwsErrorCode() . " - " . $e->getAwsErrorType());
         error_log('DynamoDB error retrieving team_questions for teamID: '.$teamID);
      }
      $results = [];
   }
   $answers = array();
   $scores = array();
   foreach($results as $row) {
      if (isset($row['answer'])) {
         $answers[$row['questionID']] = $row['answer'];
      }
      if (isset($row['score'])) {
         $scores[$row['questionID']] = $row['score'];
      } elseif (isset($row['ffScore'])) {
         $scores[$row['questionID']] = $row['ffScore'];
      }
   }

   addBackendHint("ClientIP.loadContestData:pass");
   addBackendHint(sprintf("Team(%s):loadContestData", escapeHttpValue($teamID)));
   exitWithJson((object)array(
      "success" => true,
      "questionsData" => $questionsData,
      'scores' => $scores,
      "answers" => $answers,
      "isTimed" => ($_SESSION["nbMinutes"] > 0),
      "teamPassword" => $_SESSION["<PASSWORD>"]
   ));
}

function handleCloseContest($db) {
   if (!isset($_SESSION["teamID"]) && !reconnectSession($db)) {
      exitWithJsonFailure("Pas de session en cours");
   }
   $teamID = $_SESSION["teamID"];
   $stmtUpdate = $db->prepare("UPDATE `team` SET `endTime` = UTC_TIMESTAMP() WHERE `ID` = ? AND `endTime` is NULL");
   $stmtUpdate->execute(array($teamID));
   $_SESSION["closed"] = true;
   $stmt = $db->prepare("SELECT `endTime` FROM `team` WHERE `ID` = ?");
   $stmt->execute(array($teamID));
   $row = $stmt->fetchObject();
   addBackendHint("ClientIP.closeContest:pass");
   addBackendHint(sprintf("Team(%s):closeContest", escapeHttpValue($teamID)));
   exitWithJson((object)array("success" => true));
}

function handleLoadSession() {
   global $config;
   $sid = session_id();
   // If the session is new or closed, just return the SID.
   if (!isset($_SESSION["teamID"]) || isset($_SESSION["closed"])) {
      addBackendHint("ClientIP.loadSession:new");
      exitWithJson(['success' => true, "SID" => $sid]);
   }
   // Otherwise, data from the session is also returned.
   addBackendHint("ClientIP.loadSession:found");
   addBackendHint(sprintf("SessionId(%s):loadSession", escapeHttpValue($sid)));
   $message = "Voulez-vous reprendre l'épreuve commencée ?";
   if ($config->defaultLanguage == "es") {
      $message = "¿Desea reiniciar la prueba comenzada anteriormente?";
   }
   exitWithJson(array(
      "success" => true,
      "teamID" => $_SESSION["teamID"],
      "message" => $message,
      "nbMinutes" => $_SESSION["nbMinutes"],
      "bonusScore" => $_SESSION["bonusScore"],
      "allowTeamsOfTwo" => $_SESSION["allowTeamsOfTwo"],
      "newInterface" => $_SESSION["newInterface"],
      "customIntro" => $_SESSION["customIntro"],
      "fullFeedback" => $_SESSION["fullFeedback"],
      "nbUnlockedTasksInitial" => $_SESSION["nbUnlockedTasksInitial"],
      "subsetsSize" => $_SESSION["subsetsSize"],
      "contestID" => $_SESSION["contestID"],
      "isPublic" => $_SESSION["isPublic"],
      "contestFolder" => $_SESSION["contestFolder"],
      "contestName" => $_SESSION["contestName"],
      "contestOpen" => $_SESSION["contestOpen"],
      "contestShowSolutions" => $_SESSION["contestShowSolutions"],
      "contestVisibility" => $_SESSION["contestVisibility"],
      "SID" => $sid));
}

function handleDestroySession() {
   $sid = session_id();
   addBackendHint("ClientIP.destroySession");
   restartSession();
   exitWithJson(array("success" => true, "SID" => $sid));
}

function handleCheckPassword($db) {
   global $config;

   addFailureBackendHint("ClientIP.checkPassword:fail");
   addFailureBackendHint("ClientIP.error");

   // Check common.js version
   $commonJsVersion = isset($_POST['commonJsVersion']) ? intval($_POST['commonJsVersion']) : 0;
   $commonJsTimestamp = isset($_POST['commonJsTimestamp']) ? $_POST['commonJsTimestamp'] : '[none]';
   $timestamp = isset($_POST['timestamp']) ? $_POST['timestamp'] : '[none]';

   if($commonJsVersion < $config->minimumCommonJsVersion) {
      // Reject old common.js versions
      $errormsg = "Mauvaise version de common.js : client ".$commonJsVersion." < minimum ".$config->minimumCommonJsVersion." (server timestamp ".$timestamp.", common.js loaded at ".$commonJsTimestamp.").";

      // Log error
      $stmt = $db->prepare('insert into error_log (date, message) values (UTC_TIMESTAMP(), :errormsg);');
      $stmt->execute(['errormsg' => $errormsg]);
      unset($stmt);

      // Send back error message to user
      $userMsg = "Vous utilisez une ancienne version de l'interface, veuillez rafraîchir la page. Si cela ne règle pas le problème, essayez de vider votre cache.";
      if(isset($config->contestBackupURL)) {
         $userMsg .= " Vous pouvez aussi essayer le domaine alternatif du concours http://" . $config->contestBackupURL . ".";
      }
      exitWithJsonFailure($userMsg);
   }

   if (!isset($_POST["password"])) {
      exitWithJsonFailure("Mot de passe manquant");
   }
   $getTeams = array_key_exists('getTeams', $_POST) ? $_POST["getTeams"] : False;
   $password = strtolower($_POST["password"]);
   // Search for a group matching the entered password, and if found create
   // a team in that group (and end the request).
   handleCheckGroupPassword($db, $password, $getTeams);

   handleGroupFromRegistrationCode($db, $password);

   // If no matching group was found, look for a team with the entered password.
   handleCheckTeamPassword($db, $password);
}

function getRegistrationData($db, $code) {
   $query = "SELECT `algorea_registration`.`ID`, `code`, `category`, `firstName`, `lastName`, `genre`, `grade`, `studentID`, `email`, `zipCode`, ".
      "IFNULL(`algorea_registration`.`schoolID`, 0) as `schoolID`, IFNULL(`algorea_registration`.  `userID`, 0) as `userID`, IFNULL(`school_user`.`allowContestAtHome`, 1) as `allowContestAtHome` ".
      "FROM `algorea_registration` ".
      "LEFT JOIN `school_user` ON (`school_user`.`schoolID` = `algorea_registration`.`schoolID` AND `school_user`.`userID` = `algorea_registration`.`userID`) ".
      "WHERE `code` = :code";
   $stmt = $db->prepare($query);
   $stmt->execute(array("code" => $code));
   return $stmt->fetchObject();
}




function createGroupForContestAndRegistrationCode($db, $code, $contestID) {
   $groupCode = genAccessCode($db);
   $groupPassword = genAccessCode($db);
   $groupID = getRandomID();
   $query = "INSERT INTO `group` (`ID`, `name`, `contestID`, `schoolID`, `userID`, `grade`, `expectedStartTime`, `startTime`, `code`, `password`, `isGenerated`, nbStudents, nbStudentsEffective, nbTeamsEffective) ".
      "SELECT :groupID, LEFT(CONCAT(CONCAT(CONCAT('Indiv', `algorea_registration`.`grade`), ' '), `contest`.`name`), 50), :contestID, `algorea_registration`.`schoolID`, `algorea_registration`.`userID`, `algorea_registration`.`grade`, NOW(), NOW(), :groupCode, :password, 1, 0, 0, 0 FROM `contest`, `algorea_registration` WHERE `contest`.`ID` = :contestID AND `algorea_registration`.`code` = :code";
   $stmt = $db->prepare($query);
   $stmt->execute(array(
      "contestID" => $contestID,
      "code" => $code,
      "groupID" => $groupID,
      "groupCode" => $groupCode,
      "password" => $groupPassword));
   return array("code" => $groupCode, "ID" => $groupID);
}

function handleGroupFromRegistrationCode($db, $code) {
   $registrationData = getRegistrationData($db, $code);
   if (!$registrationData) {
      return;
   }
   $registrationData->category = updateRegisteredUserCategory($db, $registrationData->ID, $registrationData->category);
   
   $query = "SELECT IFNULL(tmp.score, 0) as score, IFNULL(tmp.sumScores, 0) as sumScores, tmp.password, tmp.startTime, tmp.contestName, tmp.contestID, tmp.parentContestID, tmp.contestCategory, ".
       "tmp.nbMinutes, tmp.remainingSeconds, tmp.teamID, ".
       "GROUP_CONCAT(CONCAT(CONCAT(contestant.firstName, ' '), contestant.lastName)) as contestants ".
       "FROM (SELECT team.ID as teamID, team.score, SUM(team_question.ffScore) as sumScores, team.password, team.startTime, contest.ID as contestID, contest.parentContestID, contest.name as contestName, contest.categoryColor as contestCategory, ".
       "team.nbMinutes, (team.`nbMinutes` * 60) - TIME_TO_SEC(TIMEDIFF(UTC_TIMESTAMP(), `team`.`startTime`)) as remainingSeconds ".
       "FROM `contestant` ".
       "JOIN team ON `contestant`.teamID = `team`.ID ".
       "JOIN `group` ON team.groupID = `group`.ID ".
       "JOIN `contest` ON `group`.contestID = `contest`.ID ".
       "LEFT JOIN `team_question` ON team_question.teamID = team.ID ".
       "WHERE contestant.registrationID = :registrationID ".
       "GROUP BY team.ID) tmp ".
       "JOIN contestant ON tmp.teamID = contestant.teamID ".
       "GROUP BY tmp.teamID ".
       "ORDER BY tmp.startTime ASC";
   $stmt = $db->prepare($query);
   $stmt->execute(array("registrationID" => $registrationData->ID));
   $participations = array();
   while ($row = $stmt->fetchObject()) {
      $participations[] = $row;
   }
   $registrationData->participations = $participations;
       
   addBackendHint("ClientIP.checkPassword:pass");
   addBackendHint(sprintf("Group(%s):checkPassword", escapeHttpValue($registrationData->ID))); // TODO : check hint
   $contestID = "485926402649945250"; // hard-coded training contest
   $isOfficialContest = false;
   if (isset($_POST["startOfficial"])) {
      $contestID = "884044050337033997"; // hard-coded real contest
      $isOfficialContest = true;
   }
   $query = "SELECT `code` FROM `group` WHERE `contestID` = :contestID AND `schoolID` = :schoolID AND `userID` = :userID AND `grade` = :grade AND isGenerated = 1";
   $stmt = $db->prepare($query);
   $stmt->execute(array("contestID" => $contestID, "schoolID" => $registrationData->schoolID, "userID" => $registrationData->userID, "grade" => $registrationData->grade));
   $rowGroup = $stmt->fetchObject();
   if (!$rowGroup) {
      $groupData = createGroupForContestAndRegistrationCode($db, $code, $contestID);
{'fraction_non_alphanumeric': 0.19190787277669163, 'fraction_numerical': 0.004935386962948834, 'mean_word_length': 4.211605367793241, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 354, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 4, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2233725', 'n_tokens_mistral': 13930, 'n_tokens_neox': 13553, 'n_words': 2607}