<?php
    class SubmitSystem {
        const DBHOST = "localhost";
        const DBNAME = "csgo-stats";
        const USERTABLE = "user";
        const DBUSER = "csgo";
        const DBPASS = "B9NH4D6JuNDUiWHtWv2E";
        const COST = 12;
        private static $pdo = null;

        private function getConnection(): PDO {
            if (is_null(self::$pdo)) {
                self::$pdo = new PDO('mysql:host='.self::DBHOST.';dbname='.self::DBNAME, self::DBUSER, self::DBPASS);
            }
            return self::$pdo;
        }
        //Fügt einen neuen Spieler hinzu, wenn ein neuer Name auftaucht
        public function addPlayer($playername){
            $pdo = self::getConnection();
            echo "Add new player" . $playername;
            //Einfügen des Spielers in die Spielerliste
            $statement = $pdo->prepare("INSERT INTO SPIELERNAMEN (SPIELERNAME) VALUES ( :SPIELERNAME)");
            $statement->bindParam(':SPIELERNAME', $playername, PDO::PARAM_STR, 50);
            $statement->execute();

            //Kreieren einer neuen Spieler_**Username** Tabelle
            $table = "Spieler_" . $playername;
            $sql = "CREATE TABLE IF NOT EXIST $table(
                ID_MATCH INT(6) UNSIGNED FOREIGN KEY REFERENCES MATCHES(ID_MATCH),
                ID_PLAYER INT(6) UNSIGNED,
                STATS VARCHAR(60) NOT NULL
                )";
            echo $sql;
            $pdo->exec($sql);
            $pdo = NULL;
        }
        //Sieht nach ob der Spieler schon existiert anhand des Spielernamens
        public function checkIfPlayerExists($playername){
            $pdo = self::getConnection();
            $statement = $pdo->prepare("SELECT ID_PLAYER FROM SPIELERNAMEN WHERE SPIELERNAME = :playername");
            $statement->bindParam(':playername', $playername, PDO::PARAM_STR, 50);
            $statement->execute();
            $pdo = NULL;
            if ($statement->rowcount() < 1)
                return false;
            else
                return true;
        }
        //Checkt ob das Spiel schon im System ist
        //Kann zur Not noch mit Details erweitert werden!
        public function checkIfGameAlreadyExists($datum, $map, $twin, $tloss, $ctwin, $ctloss){
            $result = $twin . ", " . $tloss . ", " . $ctwin . ", " . $ctloss;
            $pdo = self::getConnection();
            $statement = $pdo->prepare("SELECT ID_MATCH FROM MATCHES WHERE DATUM = :datum AND MAP = :map AND RESULT = :result");
            $statement->bindParam(':datum', $datum, PDO::PARAM_STR, 50);
            $statement->bindParam(':map', $map, PDO::PARAM_STR, 20);
            $statement->bindParam(':result', $result, PDO::PARAM_STR, 50);
            $statement->execute();
            $pdo = NULL;
            if ($statement->rowcount() < 1){
                return false;
            } else {
                return true;
            }
        }

        public function getUserIDFromName($playername){
            $pdo = self::getConnection();
            $statement = $pdo->prepare("SELECT ID_PLAYER FROM SPIELERNAMEN WHERE SPIELERNAME = : playername");
            $statement->bindParam(':playername', $playername, PDO::PARAM_STR, 50);
            $statement->execute();
            $playerid = $statement->fetch();
            $pdo = NULL;
            return $playerid;
        }

        public function insertMatchIntoMatchTable($statsF, $statsE, $res, $datum, $map){
            $pdo = self::getConnection();
            $statement = $pdo->prepare("INSERT INTO MATCHES (RESULT, TEAM1, TEAM2, DATUM, MAP) VALUES (:res, :t1, :t2, :datum, :map)");
            $statement->bindParam(':res', $res, PDO::PARAM_STR, 50);
            $statement->bindParam(':t1', $statsF, PDO::PARAM_STR, 60);
            $statement->bindParam(':t2', $statsE, PDO::PARAM_STR, 60);
            $statement->bindParam(':datum', $datum, PDO::PARAM_STR, 50);
            $statement->bindParam(':map', $map, PDO::PARAM_STR, 20);
            $statement->execute();


            //Query um die Match ID zurückzugeben
            $stmt = $pdo->prepare("SELECT ID_MATCH FROM MATCHES WHERE RESULT = :res AND DATUM = :datum AND MAP = :map");
            $stmt->bindParam(':datum', $datum, PDO::PARAM_STR, 50);
            $stmt->bindParam(':map', $map, PDO::PARAM_STR, 20);
            $stmt->bindParam(':res', $res, PDO::PARAM_STR, 50);
            $stmt->execute();
            $matchid = $stmt->fetch();
            $pdo = NULL;
            return $matchid;
        }

        public function insertMatchIntoUserTable($user, $uid, $matchid, $statsUser, $datum, $map){
            $pdo = self::getConnection();
            $table = "Spieler_" . $user;
            $statement = $pdo->prepare("INSERT INTO $table(ID_MATCH, DATUM, ID_PLAYER, STATS, MAP) VALUES (:idm, :datum, :idp, :stats, :map");
            $statement->bindParam(':idm', $matchid, PDO::PARAM_INT, 6);
            $statement->bindParam(':idp', $uid, PDO::PARAM_INT, 6);
            $statement->bindParam(':datum', $datum, PDO::PARAM_STR, 50);
            $statement->bindParam(':stats', $statsUser, PDO::PARAM_STR, 60);
            $statement->bindParam(':map', $map, PDO::PARAM_STR, 20);
            $statement->execute();
            $pdo = NULL;
        }
    }


?>