player.dao.php

Aller à la documentation de ce fichier.

<?php
 
namespace ComusParty\Models;
 
use DateMalformedStringException;
use DateTime;
use Exception;
use PDO;
use Ramsey\Uuid\Uuid;

class PlayerDAO
{
    private ?PDO $pdo;

    public function __construct(?PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    public function getPdo(): ?PDO
    {
        return $this->pdo;
    }

    public function setPdo(?PDO $pdo): void
    {
        $this->pdo = $pdo;
    }

    public function findByUuid(string $uuid): ?Player
    {
        $stmt = $this->pdo->prepare(
            'SELECT *
            FROM ' . DB_PREFIX . 'player
            WHERE uuid = :uuid');
        $stmt->bindParam(':uuid', $uuid);
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $playerTab = $stmt->fetch();
        if ($playerTab === false) {
            return null;
        }
        return $this->hydrate($playerTab);
    }

    public function hydrate(array $data): Player
    {
        $player = new Player();
        $player->setStatistics(new Statistics());
        $player->setUuid($data['uuid']);
        $player->setUsername($data['username']);
        $player->setCreatedAt(new DateTime($data['created_at']));
        $player->setUpdatedAt(new DateTime($data['updated_at']));
        $player->setXp($data['xp']);
        $player->setElo($data['elo']);
        $player->setComusCoin($data['comus_coin']);
        $player->getStatistics()->setPlayerUuid($data['uuid'] ?? null);
        $player->getStatistics()->setGamesPlayed($data['games_played'] ?? null);
        $player->getStatistics()->setGamesWon($data['games_won'] ?? null);
        $player->getStatistics()->setGamesHosted($data['games_hosted'] ?? null);
        $player->setUserId($data['user_id']);
        $player->setActivePfp($data['active_pfp'] ?? 'default-pfp.jpg');
        $player->setActiveBanner($data['active_banner'] ?? 'default-banner.jpg');
        return $player;
    }

    public function findByUserId(int $userId): ?Player
    {
        $stmt = $this->pdo->prepare(
            'SELECT *
            FROM ' . DB_PREFIX . 'player
            WHERE user_id = :userId');
        $stmt->bindParam(':userId', $userId);
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $playerTab = $stmt->fetch();
        if ($playerTab === false) {
            return null;
        }
        return $this->hydrate($playerTab);
    }

    public function findWithDetailByUuid(string $uuid): ?Player
    {
        $stmt = $this->pdo->prepare(
            'SELECT 
            pr.*,
            u.email,
            u.created_at,
            u.updated_at,
            (SELECT COUNT(*) FROM ' . DB_PREFIX . 'played WHERE player_uuid = pr.uuid) as games_played,
            (SELECT COUNT(*) FROM ' . DB_PREFIX . 'won WHERE player_uuid = pr.uuid) as games_won,
            (SELECT COUNT(*) FROM ' . DB_PREFIX . 'game_record WHERE hosted_by = pr.uuid) as games_hosted,
            (SELECT a.file_path 
             FROM ' . DB_PREFIX . 'invoice  i 
             JOIN ' . DB_PREFIX . 'invoice_row ir ON ir.invoice_id = i.id AND ir.active = 1
             JOIN ' . DB_PREFIX . 'article a ON ir.article_id = a.id AND a.type = "pfp"
             WHERE i.player_uuid = pr.uuid
             ORDER BY i.created_at DESC
             LIMIT 1) as active_pfp,
             (SELECT a.file_path 
             FROM ' . DB_PREFIX . 'invoice  i 
             JOIN ' . DB_PREFIX . 'invoice_row ir ON ir.invoice_id = i.id AND ir.active = 1
             JOIN ' . DB_PREFIX . 'article a ON ir.article_id = a.id AND a.type = "banner"
             WHERE i.player_uuid = pr.uuid
             ORDER BY i.created_at DESC
             LIMIT 1) as active_banner
            FROM ' . DB_PREFIX . 'player pr
            JOIN ' . DB_PREFIX . 'user u ON pr.user_id = u.id
            LEFT JOIN ' . DB_PREFIX . 'invoice i ON i.player_uuid = pr.uuid
            WHERE pr.uuid = :uuid');
        $stmt->bindParam(':uuid', $uuid);
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $tabPlayer = $stmt->fetch();
        if ($tabPlayer === false || $tabPlayer['uuid'] === null) {
            return null;
        }
        return $this->hydrate($tabPlayer);
    }

    public function findWithDetailByUserId(int $userId): ?Player
    {
        $stmt = $this->pdo->prepare(
            'SELECT pr.*, u.email, u.created_at, u.updated_at,
            (SELECT COUNT(*) FROM ' . DB_PREFIX . 'played WHERE player_uuid = pr.uuid) as games_played,
            (SELECT COUNT(*) FROM ' . DB_PREFIX . 'won WHERE player_uuid = pr.uuid) as games_won,
            (SELECT COUNT(*) FROM ' . DB_PREFIX . 'game_record WHERE hosted_by = pr.uuid) as games_hosted
            FROM ' . DB_PREFIX . 'player pr
            JOIN ' . DB_PREFIX . 'user u ON pr.user_id = u.id
            LEFT JOIN ' . DB_PREFIX . 'invoice i ON i.player_uuid = pr.uuid
            LEFT JOIN ' . DB_PREFIX . 'invoice_row ir ON ir.invoice_id = i.id AND ir.active = 1
            LEFT JOIN ' . DB_PREFIX . 'article a ON ir.article_id = a.id AND a.type = "pfp"
            WHERE u.id = :userId');
        $stmt->bindParam(':userId', $userId);
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $tabPlayer = $stmt->fetch();
        if ($tabPlayer === false) {
            return null;
        }
        return $this->hydrate($tabPlayer);
    }

    public function findAll(): ?array
    {
        $stmt = $this->pdo->query(
            'SELECT *
            FROM ' . DB_PREFIX . 'player');
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $tabPlayers = $stmt->fetchAll();
        if ($tabPlayers === false) {
            return null;
        }
        return $this->hydrateMany($tabPlayers);
    }

    public function hydrateMany(array $data): array
    {
        $players = [];
        foreach ($data as $player) {
            $players[] = $this->hydrate($player);
        }
        return $players;
    }

    public function findAllWithDetail(): ?array
    {
        $stmt = $this->pdo->query(
            'SELECT pr.*, u.email, u.created_at, u.updated_at,
            (SELECT COUNT(*) FROM ' . DB_PREFIX . 'played WHERE player_uuid = pr.uuid) as games_played,
            (SELECT COUNT(*) FROM ' . DB_PREFIX . 'won WHERE player_uuid = pr.uuid) as games_won,
            (SELECT COUNT(*) FROM ' . DB_PREFIX . 'game_record WHERE hosted_by = pr.uuid) as games_hosted
            FROM ' . DB_PREFIX . 'player pr
            JOIN ' . DB_PREFIX . 'user u ON pr.user_id = u.id
            LEFT JOIN ' . DB_PREFIX . 'invoice i ON i.player_uuid = pr.uuid
            LEFT JOIN ' . DB_PREFIX . 'invoice_row ir ON ir.invoice_id = i.id AND ir.active = 1
            LEFT JOIN ' . DB_PREFIX . 'article a ON ir.article_id = a.id AND a.type = "pfp"');
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $tabPlayers = $stmt->fetchAll();
        if ($tabPlayers === false) {
            return null;
        }
        return $this->hydrateMany($tabPlayers);
    }

    public function createPlayer(string $username, string $email): bool
    {
        // Genération de l'uuid du joueur
        $uuid = Uuid::uuid4()->toString();
 
        $userDao = new UserDAO($this->pdo);
        $user = $userDao->findByEmail($email);
        $userId = $user->getId();
 
        $stmtPlayer = $this->pdo->prepare("INSERT INTO " . DB_PREFIX . "player (uuid, username, user_id) VALUES (:uuid, :username, :user_id)");
 
        $stmtPlayer->bindParam(':uuid', $uuid);
        $stmtPlayer->bindParam(':username', $username);
        $stmtPlayer->bindParam(':user_id', $userId);
 
        return $stmtPlayer->execute();
    }

    public function findByUsername(?string $username): ?Player
    {
        $stmt = $this->pdo->prepare("SELECT * FROM " . DB_PREFIX . "player WHERE username = :username");
        $stmt->bindParam(':username', $username);
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $result = $stmt->fetch();
        if ($result === false) {
            return null;
        }
        return $this->hydrate($result);
    }

    public function findInRangeOrderByEloDescWithDetails(int $start, int $end): ?array
    {
        $limit = $end - $start + 1; // Calculer le nombre d'éléments à récupérer
        $offset = $start - 1; // Décalage basé sur la position (index commence à 0)
 
        $stmt = $this->pdo->prepare(
            'SELECT DISTINCT pr.*, u.email, u.created_at, u.updated_at, 
                (SELECT a.file_path 
                 FROM ' . DB_PREFIX . 'invoice  i 
                 JOIN ' . DB_PREFIX . 'invoice_row ir ON ir.invoice_id = i.id AND ir.active = 1
                 JOIN ' . DB_PREFIX . 'article a ON ir.article_id = a.id AND a.type = "pfp"
                 WHERE i.player_uuid = pr.uuid
                 ORDER BY i.created_at DESC
                 LIMIT 1) as active_pfp,
                (SELECT COUNT(*) FROM ' . DB_PREFIX . 'played WHERE player_uuid = pr.uuid) as games_played,
                (SELECT COUNT(*) FROM ' . DB_PREFIX . 'won WHERE player_uuid = pr.uuid) as games_won,
                (SELECT COUNT(*) FROM ' . DB_PREFIX . 'game_record WHERE hosted_by = pr.uuid) as games_hosted
            FROM ' . DB_PREFIX . 'player pr
            JOIN ' . DB_PREFIX . 'user u ON pr.user_id = u.id
            WHERE u.email_verif_token IS NULL
            ORDER BY elo DESC
            LIMIT :limit OFFSET :offset;'
        );
 
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
 
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $tabPlayers = $stmt->fetchAll();
 
        if ($tabPlayers === false) {
            return null;
        }
        return $this->hydrateMany($tabPlayers);
    }

    public function update(Player $player): void
    {
        $stmt = $this->pdo->prepare(
            'UPDATE ' . DB_PREFIX . 'player
            SET username = :username, xp = :xp, elo = :elo, comus_coin = :comusCoin
            WHERE uuid = :uuid');
        $username = $player->getUsername();
        $stmt->bindParam(':username', $username);
        $xp = $player->getXp();
        $stmt->bindParam(':xp', $xp);
        $elo = $player->getElo();
        $stmt->bindParam(':elo', $elo);
        $comusCoin = $player->getComusCoin();
        $stmt->bindParam(':comusCoin', $comusCoin);
        $uuid = $player->getUuid();
        $stmt->bindParam(':uuid', $uuid);
        $stmt->execute();
    }

    public function findWithDetailByUsername(string $username): ?Player
    {
        $stmt = $this->pdo->prepare(
            'SELECT 
            pr.*,
            u.email,
            u.created_at,
            u.updated_at,
            (SELECT COUNT(*) FROM ' . DB_PREFIX . 'played WHERE player_uuid = pr.uuid) as games_played,
            (SELECT COUNT(*) FROM ' . DB_PREFIX . 'won WHERE player_uuid = pr.uuid) as games_won,
            (SELECT COUNT(*) FROM ' . DB_PREFIX . 'game_record WHERE hosted_by = pr.uuid) as games_hosted,
            (SELECT a.file_path 
             FROM ' . DB_PREFIX . 'invoice  i 
             JOIN ' . DB_PREFIX . 'invoice_row ir ON ir.invoice_id = i.id AND ir.active = 1
             JOIN ' . DB_PREFIX . 'article a ON ir.article_id = a.id AND a.type = "pfp"
             WHERE i.player_uuid = pr.uuid
             ORDER BY i.created_at DESC
             LIMIT 1) as active_pfp,
             (SELECT a.file_path 
             FROM ' . DB_PREFIX . 'invoice  i 
             JOIN ' . DB_PREFIX . 'invoice_row ir ON ir.invoice_id = i.id AND ir.active = 1
             JOIN ' . DB_PREFIX . 'article a ON ir.article_id = a.id AND a.type = "banner"
             WHERE i.player_uuid = pr.uuid
             ORDER BY i.created_at DESC
             LIMIT 1) as active_banner
            FROM ' . DB_PREFIX . 'player pr
            JOIN ' . DB_PREFIX . 'user u ON pr.user_id = u.id
            LEFT JOIN ' . DB_PREFIX . 'invoice i ON i.player_uuid = pr.uuid
            WHERE pr.username = :username');
        $stmt->bindParam(':username', $username);
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $tabPlayer = $stmt->fetch();
        if ($tabPlayer === false || $tabPlayer['uuid'] === null) {
            return null;
        }
        return $this->hydrate($tabPlayer);
    }
}