<?php

class PDOSquadro
{
    private static PDO $pdo;

    public static function initPDO(string $sgbd, string $host, string $db, string $user, string $password): void
    {
        switch ($sgbd) {
            case 'pgsql':
                self::$pdo = new PDO("pgsql:host=$host;dbname=$db", $user, $password);
                break;
            default:
                exit("Type de SGBD non correct : $sgbd fourni, 'pgsql' attendu");
        }

        // Activer les exceptions pour PDO
        self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    /* Requêtes préparées pour JoueurSquadro */
    private static PDOStatement $createPlayerSquadro;
    private static PDOStatement $selectPlayerByName;

    /******** Gestion des requêtes relatives à JoueurSquadro *************/

    public static function createPlayer(string $name): JoueurSquadro
    {
        if (!isset(self::$createPlayerSquadro)) {
            self::$createPlayerSquadro = self::$pdo->prepare(
                "INSERT INTO JoueurSquadro(joueurNom) VALUES (:name) RETURNING id"
            );
        }

        self::$createPlayerSquadro->execute(['name' => $name]);
        $id = self::$createPlayerSquadro->fetchColumn();
        return new JoueurSquadro($name, $id);
    }

    public static function selectPlayerByName(string $name): ?JoueurSquadro
    {
        if (!isset(self::$selectPlayerByName)) {
            self::$selectPlayerByName = self::$pdo->prepare(
                "SELECT * FROM JoueurSquadro WHERE joueurNom = :name"
            );
        }

        self::$selectPlayerByName->execute(['name' => $name]);
        $row = self::$selectPlayerByName->fetch(PDO::FETCH_ASSOC);
        return $row ? new JoueurSquadro($row['joueurNom'], $row['id']) : null;
    }

    /* Requêtes préparées pour PartieSquadro */
    private static PDOStatement $createPartieSquadro;
    private static PDOStatement $savePartieSquadro;
    private static PDOStatement $addPlayerToPartieSquadro;
    private static PDOStatement $selectPartieSquadroById;
    private static PDOStatement $selectAllPartieSquadro;
    private static PDOStatement $selectAllPartieSquadroByPlayerName;

    /******** Gestion des requêtes relatives à PartieSquadro *************/

    public static function createPartieSquadro(string $playerName, string $json): void
    {
        if (!isset(self::$createPartieSquadro)) {
            self::$createPartieSquadro = self::$pdo->prepare(
                "INSERT INTO PartieSquadro(playerOne, json) VALUES (
                    (SELECT id FROM JoueurSquadro WHERE joueurNom = :playerName), :json)"
            );
        }

        self::$createPartieSquadro->execute([
            'playerName' => $playerName,
            'json' => $json
        ]);
    }

    public static function savePartieSquadro(string $gameStatus, string $json, int $partieId): void
    {
        if (!isset(self::$savePartieSquadro)) {
            self::$savePartieSquadro = self::$pdo->prepare(
                "UPDATE PartieSquadro SET gameStatus = :gameStatus, json = :json WHERE partieId = :partieId"
            );
        }

        self::$savePartieSquadro->execute([
            'gameStatus' => $gameStatus,
            'json' => $json,
            'partieId' => $partieId
        ]);
    }

    public static function addPlayerToPartieSquadro(string $playerName, int $gameId): void
    {
        if (!isset(self::$addPlayerToPartieSquadro)) {
            self::$addPlayerToPartieSquadro = self::$pdo->prepare(
                "UPDATE PartieSquadro SET playerTwo = 
                    (SELECT id FROM JoueurSquadro WHERE joueurNom = :playerName) 
                WHERE partieId = :gameId"
            );
        }

        self::$addPlayerToPartieSquadro->execute([
            'playerName' => $playerName,
            'gameId' => $gameId
        ]);
    }

    public static function getPartieSquadroById(int $gameId): ?PartieSquadro
    {
        if (!isset(self::$selectPartieSquadroById)) {
            self::$selectPartieSquadroById = self::$pdo->prepare(
                "SELECT * FROM PartieSquadro WHERE partieId = :gameId"
            );
        }

        self::$selectPartieSquadroById->execute(['gameId' => $gameId]);
        $row = self::$selectPartieSquadroById->fetch(PDO::FETCH_ASSOC);

        return $row ? new PartieSquadro($row['partieId']) : null;
    }

    public static function getAllPartieSquadro(): array
    {
        if (!isset(self::$selectAllPartieSquadro)) {
            self::$selectAllPartieSquadro = self::$pdo->prepare(
                "SELECT * FROM PartieSquadro"
            );
        }

        self::$selectAllPartieSquadro->execute();
        return self::$selectAllPartieSquadro->fetchAll(PDO::FETCH_ASSOC);
    }

    public static function getAllPartieSquadroByPlayerName(string $playerName): array
    {
        if (!isset(self::$selectAllPartieSquadroByPlayerName)) {
            self::$selectAllPartieSquadroByPlayerName = self::$pdo->prepare(
                "SELECT * FROM PartieSquadro WHERE playerOne = 
                    (SELECT id FROM JoueurSquadro WHERE joueurNom = :playerName) 
                OR playerTwo = 
                    (SELECT id FROM JoueurSquadro WHERE joueurNom = :playerName) 
                OR playerTwo IS NULL"
            );
        }

        self::$selectAllPartieSquadroByPlayerName->execute(['playerName' => $playerName]);
        return self::$selectAllPartieSquadroByPlayerName->fetchAll(PDO::FETCH_ASSOC);
    }

    public static function getLastGameIdForPlayer(string $playerName): int
    {
        $games = self::getAllPartieSquadroByPlayerName($playerName);
        return empty($games) ? 0 : $games[count($games) - 1]['partieId'];
    }
}