ChasseTresorPange/require/database.php

<?php
    class Database {
        public PDO $pdo_article;
        public PDO $pdo_teams;

        public function __construct() {
            $data_path = "/../data/";
            try {
                // Création des bases de données
                // Base des énigmes :
                $this->pdo_article = new PDO("sqlite:" . dirname(__FILE__) . $data_path . "article.db");
                $this->pdo_article->query('CREATE TABLE IF NOT EXISTS "puzzles" (
                    "id"	INTEGER NOT NULL UNIQUE,
                    "title"	TEXT NOT NULL,
                    "text"	TEXT NOT NULL,
                    "hint"	TEXT,
                    "answer"	TEXT NOT NULL,
                    "location"	TEXT NOT NULL,
                    "code"	TEXT NOT NULL,
                    PRIMARY KEY("id" AUTOINCREMENT)
                )');

                // Base des équipes :
                $this->pdo_teams = new PDO("sqlite:" . dirname(__FILE__) . $data_path . "teams.db");
                $this->pdo_teams->query('CREATE TABLE IF NOT EXISTS "teams" (
                    "id"	INTEGER NOT NULL UNIQUE,
                    "bonus"    INTEGER NOT NULL,
                    PRIMARY KEY("id" AUTOINCREMENT)
                )');

                $this->pdo_teams->query('CREATE TABLE IF NOT EXISTS "solved" (
                    "puzzle_id"	INTEGER NOT NULL,
                    "team_id"	INTEGER NOT NULL
                )');

                $this->pdo_teams->query('CREATE TABLE IF NOT EXISTS "members" (
                    "name"	TEXT NOT NULL,
                    "team_id"	INTEGER NOT NULL
                )');
            }

            catch (PDOException $exception) {
                var_dump($exception);
                die();
            }
        }

        public function getArticle(int $id) {
            $stmt = $this->pdo_article->prepare("SELECT * FROM puzzles WHERE id == :id");
            $stmt->bindValue(":id", $id);
            $stmt->execute();
            return $stmt->fetch();
        }

        public function getArticleNb() {
            $query = $this->pdo_article->query("SELECT COUNT(*) FROM puzzles");
            return $query->fetch()["COUNT(*)"];
        }

        public function checkTeamExists(int $id) {
            $stmt = $this->pdo_teams->prepare("SELECT * FROM teams WHERE id == :id");
            $stmt->bindValue(":id", $id);
            $stmt->execute();
            return !empty($stmt->fetch());
        }

        public function checkArticleExists(int $id) {
            return !empty($this->getArticle($id));
        }

        public function checkPuzzle(int $id, string $code) {
            $stmt = $this->pdo_article->prepare("SELECT * FROM puzzles WHERE (id == :id AND code == :code)");
            $stmt->bindValue(":id", $id);
            $stmt->bindValue(":code", $code);
            $stmt->execute();
            return !empty($stmt->fetch());
        }

        public function isPuzzleSolved(int $id, int $team) {
            $stmt = $this->pdo_teams->prepare("SELECT * FROM solved WHERE (team_id == :team_id AND puzzle_id == :puzzle_id)");
            $stmt->bindValue(":team_id", $team);
            $stmt->bindValue(":puzzle_id", $id);
            $stmt->execute();
            return !empty($stmt->fetch());
        }

        public function solvePuzzle(int $id, int $team) {
            $stmt = $this->pdo_teams->prepare("INSERT INTO solved VALUES (:puzzle_id, :team_id)");
            $stmt->bindValue(":puzzle_id", $id);
            $stmt->bindValue(":team_id", $team);
            $stmt->execute();
        }

        public function getSolvedPuzzles(int $team) {
            // $team doit être une équipe existante :
            $stmt = $this->pdo_teams->prepare("SELECT * FROM solved WHERE team_id == :id");
            $stmt->bindValue(":id", $team);
            $stmt->execute();
            $res = $stmt->fetchAll();
            $solved = [];
            foreach ($res as $r) {
                array_push($solved, $r["puzzle_id"]);
            }
            return $solved;
        }

        function getUnsolvedPuzzles(int $team) {
            $solved = $this->getSolvedPuzzles($team);
            $unsolved = range(1, $this->getArticleNb());
            // On supprime les énigmes résolues :
            foreach ($solved as $p) {
                $key = array_search($p, $unsolved);
                array_splice($unsolved, $key, 1);
            }
            return $unsolved;
        }

        public function getNextPuzzle(int $team) {
            $unsolved = $this->getUnsolvedPuzzles($team);
            $next = -1; // Quand il n'y a plus d'autre énigme à résoudre
            if (!empty($unsolved)) {
                $next = $unsolved[array_rand($unsolved)];
            }
            return $next;
        }

        public function checkTeamBonus(int $team) {
            // $team doit être une équipe existante :
            $stmt = $this->pdo_teams->prepare("SELECT bonus FROM teams WHERE id == :id");
            $stmt->bindValue(":id", $team);
            $stmt->execute();
            return $stmt->fetch()[0] == 1;
        }

        public function getTeams() {
            $stmt = $this->pdo_teams->prepare("SELECT * FROM teams");
            $stmt->execute();
            return $stmt->fetchAll();
        }

        // public function getTeamsNb() {
        //     $query = $this->pdo_teams->query("SELECT COUNT(*) FROM teams");
        //     return $query->fetch()["COUNT(*)"];
        // }
    }
?>