<?php
class Game
{
    private $conn;

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

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

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

        $sql = "SELECT
        g.id, g.livet1, g.livet2, g.s1t1, g.s2t1, g.s3t1, g.s1t2, g.s2t2, g.s3t2, g.date, g.end, g.type, g.entity, g.tournament, g.club, g.court, g.desc, g.serve,g.groups, g.state,
        
        COALESCE(p1_team.id, p1.id) AS p1,
        COALESCE(p1_team.pNombre, p1.pNombre) AS p1_pNombre,
        COALESCE(p1_team.pApellido, p1.pApellido) AS p1_pApellido,
        COALESCE(p1_team.pic, p1.pic) AS p1_pic,

        COALESCE(p2_team.id, p2.id) AS p2,
        COALESCE(p2_team.pNombre, p2.pNombre) AS p2_pNombre,
        COALESCE(p2_team.pApellido, p2.pApellido) AS p2_pApellido,
        COALESCE(p2_team.pic, p2.pic) AS p2_pic,

        COALESCE(p3_team.id, p3.id) AS p3,
        COALESCE(p3_team.pNombre, p3.pNombre) AS p3_pNombre,
        COALESCE(p3_team.pApellido, p3.pApellido) AS p3_pApellido,
        COALESCE(p3_team.pic, p3.pic) AS p3_pic,

        COALESCE(p4_team.id, p4.id) AS p4,
        COALESCE(p4_team.pNombre, p4.pNombre) AS p4_pNombre,
        COALESCE(p4_team.pApellido, p4.pApellido) AS p4_pApellido,
        COALESCE(p4_team.pic, p4.pic) AS p4_pic,

        t.id AS tournament_id,
        t.name AS tournament_name,
        t.date AS tournament_date,
        e.id AS entity,
        e.name AS entity_name,
        c.id AS club_id,
        c.name AS club_name,
        t1.id AS t1,
        t2.id AS t2,
        co.id AS court_id,
        co.name AS court_name

        FROM
            game g
        LEFT JOIN team t1 ON g.t1 = t1.code
        LEFT JOIN team t2 ON g.t2 = t2.code
        LEFT JOIN player p1_team ON t1.p1 = p1_team.code
        LEFT JOIN player p2_team ON t1.p2 = p2_team.code
        LEFT JOIN player p3_team ON t2.p1 = p3_team.code
        LEFT JOIN player p4_team ON t2.p2 = p4_team.code
        LEFT JOIN player p1 ON g.p1 = p1.code
        LEFT JOIN player p2 ON g.p2 = p2.code
        LEFT JOIN player p3 ON g.p3 = p3.code
        LEFT JOIN player p4 ON g.p4 = p4.code
        LEFT JOIN tournament t ON g.tournament = t.code
        LEFT JOIN entity e ON t.entity = e.code
        LEFT JOIN club c ON g.club = c.code
        LEFT JOIN court co ON g.court = co.code
        WHERE g.tournament = ?
        ORDER BY g.groups ASC, g.date DESC, g.desc ASC;
        ";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('s', $tournamentCode);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }

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

        $player = new Player($this->conn);
        $playerCode = $player->getPlayerCodeById($playerId);

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

        $sql = "SELECT
        g.id, g.livet1, g.livet2, g.s1t1, g.s2t1, g.s3t1, g.s1t2, g.s2t2, g.s3t2, g.date, g.end, g.type, g.entity, g.tournament, g.club, g.court, g.desc, g.serve,g.groups, g.state,
        
        COALESCE(p1_team.id, p1.id) AS p1,
        COALESCE(p1_team.pNombre, p1.pNombre) AS p1_pNombre,
        COALESCE(p1_team.pApellido, p1.pApellido) AS p1_pApellido,
        COALESCE(p1_team.pic, p1.pic) AS p1_pic,

        COALESCE(p2_team.id, p2.id) AS p2,
        COALESCE(p2_team.pNombre, p2.pNombre) AS p2_pNombre,
        COALESCE(p2_team.pApellido, p2.pApellido) AS p2_pApellido,
        COALESCE(p2_team.pic, p2.pic) AS p2_pic,

        COALESCE(p3_team.id, p3.id) AS p3,
        COALESCE(p3_team.pNombre, p3.pNombre) AS p3_pNombre,
        COALESCE(p3_team.pApellido, p3.pApellido) AS p3_pApellido,
        COALESCE(p3_team.pic, p3.pic) AS p3_pic,

        COALESCE(p4_team.id, p4.id) AS p4,
        COALESCE(p4_team.pNombre, p4.pNombre) AS p4_pNombre,
        COALESCE(p4_team.pApellido, p4.pApellido) AS p4_pApellido,
        COALESCE(p4_team.pic, p4.pic) AS p4_pic,

        t.id AS tournament_id,
        t.name AS tournament_name,
        t.date AS tournament_date,
        e.id AS entity,
        e.name AS entity_name,
        c.id AS club_id,
        c.name AS club_name,
        t1.id AS t1,
        t2.id AS t2,
        co.id AS court_id,
        co.name AS court_name

        FROM
            game g
        LEFT JOIN team t1 ON g.t1 = t1.code
        LEFT JOIN team t2 ON g.t2 = t2.code
        LEFT JOIN player p1_team ON t1.p1 = p1_team.code
        LEFT JOIN player p2_team ON t1.p2 = p2_team.code
        LEFT JOIN player p3_team ON t2.p1 = p3_team.code
        LEFT JOIN player p4_team ON t2.p2 = p4_team.code
        LEFT JOIN player p1 ON g.p1 = p1.code
        LEFT JOIN player p2 ON g.p2 = p2.code
        LEFT JOIN player p3 ON g.p3 = p3.code
        LEFT JOIN player p4 ON g.p4 = p4.code
        LEFT JOIN tournament t ON g.tournament = t.code
        LEFT JOIN entity e ON t.entity = e.code
        LEFT JOIN club c ON g.club = c.code
        LEFT JOIN court co ON g.court = co.code
        WHERE t1.p1 = ? OR t1.p2 = ? OR t2.p1 = ? OR t2.p2 = ? AND g.tournament = ?
        ORDER BY g.date DESC, g.groups ASC, g.desc ASC;
        ";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('sssss', $playerCode, $playerCode, $playerCode, $playerCode, $tournamentCode);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }

    public function getGameById($id)
    {
        $sql = "SELECT
        g.id, g.livet1, g.livet2, g.s1t1, g.s2t1, g.s3t1, g.s1t2, g.s2t2, g.s3t2, g.date, g.t1, g.t2, g.end, g.type, g.entity, g.tournament, g.club, g.court, g.desc, g.serve,g.groups, g.state,
        
        COALESCE(p1_team.id, p1.id) AS p1,
        COALESCE(p1_team.pNombre, p1.pNombre) AS p1_pNombre,
        COALESCE(p1_team.pApellido, p1.pApellido) AS p1_pApellido,
        COALESCE(p1_team.pic, p1.pic) AS p1_pic,

        COALESCE(p2_team.id, p2.id) AS p2,
        COALESCE(p2_team.pNombre, p2.pNombre) AS p2_pNombre,
        COALESCE(p2_team.pApellido, p2.pApellido) AS p2_pApellido,
        COALESCE(p2_team.pic, p2.pic) AS p2_pic,

        COALESCE(p3_team.id, p3.id) AS p3,
        COALESCE(p3_team.pNombre, p3.pNombre) AS p3_pNombre,
        COALESCE(p3_team.pApellido, p3.pApellido) AS p3_pApellido,
        COALESCE(p3_team.pic, p3.pic) AS p3_pic,

        COALESCE(p4_team.id, p4.id) AS p4,
        COALESCE(p4_team.pNombre, p4.pNombre) AS p4_pNombre,
        COALESCE(p4_team.pApellido, p4.pApellido) AS p4_pApellido,
        COALESCE(p4_team.pic, p4.pic) AS p4_pic,

        t.id AS tournament_id,
        t.name AS tournament_name,
        t.date AS tournament_date,
        e.id AS entity,
        e.name AS entity_name,
        c.id AS club_id,
        c.name AS club_name,
        t.id AS t1,
        t2.id AS t2,
        co.id AS court_id,
        co.name AS court_name

        FROM
            game g
        LEFT JOIN team t1 ON g.t1 = t1.code
        LEFT JOIN team t2 ON g.t2 = t2.code
        LEFT JOIN player p1_team ON t1.p1 = p1_team.code
        LEFT JOIN player p2_team ON t1.p2 = p2_team.code
        LEFT JOIN player p3_team ON t2.p1 = p3_team.code
        LEFT JOIN player p4_team ON t2.p2 = p4_team.code
        LEFT JOIN player p1 ON g.p1 = p1.code
        LEFT JOIN player p2 ON g.p2 = p2.code
        LEFT JOIN player p3 ON g.p3 = p3.code
        LEFT JOIN player p4 ON g.p4 = p4.code
        LEFT JOIN tournament t ON g.tournament = t.code
        LEFT JOIN entity e ON t.entity = e.code
        LEFT JOIN club c ON g.club = c.code
        LEFT JOIN court co ON g.court = co.code
        WHERE g.id = ?
        ORDER BY g.groups ASC, g.date DESC, g.desc ASC
        ";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('i', $id);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }

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