<?php
class Team
{
    private $conn;

    public function __construct($db)
    {
        $this->conn = $db;
    }

    public function getTeamsByTournament($tournamentId)
    {
        $tournament = new Tournament($this->conn);
        $tournamentCode = $tournament->getTournamentCodeById($tournamentId);

        if ($tournamentCode === null) {
            return [];
        }

        $sql = "SELECT t.id, t.p1, t.p2,
            p1.id AS p1,
            p1.pic AS p1_pic,
            p1.pApellido AS p1_lastname,
            p2.id AS p2,
            p2.pic AS p2_pic,
            p2.pApellido AS p2_lastname,
            s.matches_played AS matches_played,
            s.wins AS wins,
            s.losses AS losses,
            s.draws AS draws,
            s.sets_won AS sets_won,
            s.sets_lost AS sets_lost,
            s.points AS points,
            s.goal_difference AS goal_difference,
            s.group AS 'group'
            FROM team t
            LEFT JOIN player p1 ON t.p1 = p1.code
            LEFT JOIN player p2 ON t.p2 = p2.code
            LEFT JOIN standings s ON s.team = t.code
            WHERE t.tournament = ? ORDER BY s.points DESC, s.goal_difference DESC";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('s', $tournamentCode);
        $stmt->execute();
        $result = $stmt->get_result();
        $grouped_results = [];

        while ($row = $result->fetch_assoc()) {
            $group_code = $row['group'];
            if (!isset($grouped_results[$group_code])) {
                $grouped_results[$group_code] = [];
            }
            $grouped_results[$group_code][] = $row;
        }
        return $grouped_results;
    }

    public function getTeamsByPlayer($playerId)
    {
        $player = new Player($this->conn);
        $playerCode = $player->getPlayerCodeById($playerId);

        if ($playerCode === null) {
            return [];
        }

        $sql = "SELECT t.code, t.id, t.p1, t.p2, t.tournament
            FROM team t
            WHERE t.p1 = ? OR t.p2 = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('ss', $playerCode, $playerCode);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }

    public function getTeamById($id)
    {
        $sql = "SELECT * FROM team WHERE id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('i', $id);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }

    public function getTeamCodeById($id)
    {
        $sql = "SELECT code FROM team WHERE id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('i', $id);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc()['code'] ?? null;
    }

    public function getTeamIdByCode($code)
    {
        $sql = "SELECT id FROM team WHERE code = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('s', $code);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc()['id'] ?? null;
    }
}
