<?php
// Game.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,
        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 getGameById($id)
    {
        $sql = "SELECT g.*, p1.pNombre AS p1_pNombre, p1.pApellido AS p1_pApellido, 
                       p2.pNombre AS p2_pNombre, p2.pApellido AS p2_pApellido
                FROM game g
                LEFT JOIN player p1 ON g.p1 = p1.code
                LEFT JOIN player p2 ON g.p2 = p2.code
                WHERE g.id = ?";
        $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;
    }

    public function createGame($data)
{
    $tournament = new Tournament($this->conn);
    $data['tournament'] = $tournament->getTournamentCodeById($data['tournament']);

    $team = new Team($this->conn);
    $data['t1'] = $team->getTeamCodeById($data['t1']);
    $data['t2'] = $team->getTeamCodeById($data['t2']);

    $club = new Club($this->conn);
    $data['club'] = $club->getClubCodeById($data['club']);

    $court = new Court($this->conn);
    $data['court'] = $court->getCourtCodeById($data['court']);
    
    if ($data['tournament'] === null) {
        throw new Exception('Uno o más códigos de relaciones son nulos. Verifica los datos.');
    }
    
    $data['groups'] = $data['groups'] === '' ? null : $data['groups'];
    $data['s1t1'] = $data['s1t1'] === '' ? null : $data['s1t1'];
    $data['s2t1'] = $data['s2t1'] === '' ? null : $data['s2t1'];
    $data['s3t1'] = $data['s3t1'] === '' ? null : $data['s3t1'];
    $data['s1t2'] = $data['s1t2'] === '' ? null : $data['s1t2'];
    $data['s2t2'] = $data['s2t2'] === '' ? null : $data['s2t2'];
    $data['s3t2'] = $data['s3t2'] === '' ? null : $data['s3t2'];
        
    $sql = "INSERT INTO game (`desc`, t1, t2, `date`, `end`, tournament, club, court, `groups`, s1t1, s2t1, s3t1, s1t2, s2t2, s3t2,`code`) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    $stmt = $this->conn->prepare($sql);

    if ($stmt === false) {
        return "Prepare failed: (" . $this->conn->errno . ") " . $this->conn->error;
    }

    $stmt->bind_param(
        'ssssssssssssssss',
        $data['desc'],
        $data['t1'],
        $data['t2'],
        $data['date'],
        $data['end'],
        $data['tournament'],
        $data['club'],
        $data['court'],
        $data['groups'],
        $data['s1t1'],
        $data['s2t1'],
        $data['s3t1'],
        $data['s1t2'],
        $data['s2t2'],
        $data['s3t2'],
        $data['code']
    );

    if ($stmt->execute()) {
        return "true";
    } else {
        return "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
    }
}

    public function updateGame($data)
{
    $tournament = new Tournament($this->conn);
    $data['tournament'] = $tournament->getTournamentCodeById($data['tournament']);
    if ($data['tournament'] === null) {
        return "Error: Tournament code is null.";
    }

    $team = new Team($this->conn);
    $data['t1'] = $team->getTeamCodeById($data['t1']);
    $data['t2'] = $team->getTeamCodeById($data['t2']);

    $club = new Club($this->conn);
    $data['club'] = $club->getClubCodeById($data['club']);
    if ($data['club'] === null) {
        return "Error: Club code is null.";
    }

    $court = new Court($this->conn);
    $data['court'] = $court->getCourtCodeById($data['court']);
    if ($data['court'] === null) {
        return "Error: Court code is null.";
    }

    $data['groups'] = $data['groups'] === '' ? null : $data['groups'];
    $data['s1t1'] = $data['s1t1'] === '' ? null : $data['s1t1'];
    $data['s2t1'] = $data['s2t1'] === '' ? null : $data['s2t1'];
    $data['s3t1'] = $data['s3t1'] === '' ? null : $data['s3t1'];
    $data['s1t2'] = $data['s1t2'] === '' ? null : $data['s1t2'];
    $data['s2t2'] = $data['s2t2'] === '' ? null : $data['s2t2'];
    $data['s3t2'] = $data['s3t2'] === '' ? null : $data['s3t2'];

    $sql = "UPDATE game 
        SET `desc` = ?, t1 = ?, t2 = ?, `date` = ?, `end` = ?, tournament = ?, club = ?, court = ?, `groups` = ?, s1t1 = ?, s2t1 = ?, s3t1 = ?, s1t2 = ?, s2t2 = ?, s3t2 = ?
        WHERE `id` = ?";

    $stmt = $this->conn->prepare($sql);

    // Check if statement preparation failed
    if (!$stmt) {
        return "Error: Failed to prepare SQL statement. (" . $this->conn->errno . ") " . $this->conn->error;
    }

    // Bind parameters
    $stmt->bind_param(
        'sssssssssssssssi',
        $data['desc'],
        $data['t1'],
        $data['t2'],
        $data['date'],
        $data['end'],
        $data['tournament'],
        $data['club'],
        $data['court'],
        $data['groups'],
        $data['s1t1'],
        $data['s2t1'],
        $data['s3t1'],
        $data['s1t2'],
        $data['s2t2'],
        $data['s3t2'],
        $data['id']
    );

    // Execute statement and check for errors
    if (!$stmt->execute()) {
        return "Error: Failed to execute SQL statement. (" . $stmt->errno . ") " . $stmt->error;
    }

    // If no error occurred, return success message
    return true;
}

}
