<?php declare(strict_types=1);

class Fixture {
    private $db;

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

    // Create a new fixture
    public function create($data){
        $this->db->query('INSERT INTO fixtures (league_id, home_team_id, away_team_id, match_datetime, gameweek, venue, referee_id, commissioner_id, status, home_team_score, away_team_score) VALUES (:league_id, :home_team_id, :away_team_id, :match_datetime, :gameweek, :venue, :referee_id, :commissioner_id, :status, :home_team_score, :away_team_score)');
        
        // Bind values
        $this->db->bind(':league_id', $data['league_id']);
        $this->db->bind(':home_team_id', $data['home_team_id']);
        $this->db->bind(':away_team_id', $data['away_team_id']);
        $this->db->bind(':match_datetime', $data['match_datetime']);
        $this->db->bind(':gameweek', $data['gameweek']);
        $this->db->bind(':venue', $data['venue']);

        // Optional values
        $this->db->bind(':referee_id', $data['referee_id'] ?? null);
        $this->db->bind(':commissioner_id', $data['commissioner_id'] ?? null);
        $this->db->bind(':status', $data['status'] ?? 'scheduled');
        $this->db->bind(':home_team_score', $data['home_team_score'] ?? null);
        $this->db->bind(':away_team_score', $data['away_team_score'] ?? null);

        // Execute
        if($this->db->execute()){
            return $this->db->lastInsertId();
        } else {
            return false;
        }
    }

    // Get all fixtures for a league (public)
    public function getAllByLeague($league_id, $gameweek = null, $fixture_id = null){
        $sql = '
            SELECT 
                f.id, f.match_datetime, f.status, f.home_team_score, f.away_team_score, f.gameweek, f.first_half_start_time, f.second_half_start_time, f.home_team_id, f.away_team_id, f.first_half_end_time,
                TIMESTAMPDIFF(SECOND, f.first_half_start_time, NOW()) as elapsed_1st,
                TIMESTAMPDIFF(SECOND, f.second_half_start_time, NOW()) as elapsed_2nd,
                TIMESTAMPDIFF(SECOND, f.first_half_end_time, NOW()) as elapsed_half_time,
                l.name as league_name,
                c_ht.name as home_team_name,
                c_at.name as away_team_name,
                c_ht.logo as home_team_logo,
                c_at.logo as away_team_logo
            FROM fixtures f
            JOIN leagues l ON f.league_id = l.id
            JOIN teams ht ON f.home_team_id = ht.id
            JOIN teams at ON f.away_team_id = at.id
            JOIN clubs c_ht ON ht.club_id = c_ht.id
            JOIN clubs c_at ON at.club_id = c_at.id';

        $params = [];

        if ($fixture_id !== null) {
            $sql .= ' WHERE f.id = :fixture_id';
            $params[':fixture_id'] = $fixture_id;
        } else if ($league_id !== null) {
            $sql .= ' WHERE f.league_id = :league_id';
            $params[':league_id'] = $league_id;

            if ($gameweek !== null) {
                $sql .= ' AND f.gameweek = :gameweek';
                $params[':gameweek'] = $gameweek;
            }
        }
        $sql .= ' ORDER BY f.match_datetime ASC';
        $this->db->query($sql);

        foreach ($params as $key => $value) {
            $this->db->bind($key, $value);
        }
        return $this->db->resultSet();
    }

    // Get all finished fixtures for a league
    public function getFinishedByLeague($league_id){
        $this->db->query("SELECT * FROM fixtures WHERE league_id = :league_id AND status = 'finished'");
        $this->db->bind(':league_id', $league_id);
        return $this->db->resultSet();
    }

    // Find fixture by ID
    public function findById($id){
        $this->db->query("
            SELECT 
                f.*,
                TIMESTAMPDIFF(SECOND, f.first_half_start_time, NOW()) as elapsed_1st,
                TIMESTAMPDIFF(SECOND, f.second_half_start_time, NOW()) as elapsed_2nd,
                TIMESTAMPDIFF(SECOND, f.first_half_end_time, NOW()) as elapsed_half_time,
                c_ht.name as home_team_name,
                c_at.name as away_team_name,
                c_ht.logo as home_team_logo,
                c_at.logo as away_team_logo,
                u_hm.id as home_manager_id,
                CONCAT(u_hm.first_name, ' ', u_hm.last_name) as home_manager_name,
                u_am.id as away_manager_id,
                CONCAT(u_am.first_name, ' ', u_am.last_name) as away_manager_name,
                CONCAT(u_ref.first_name, ' ', u_ref.last_name) as referee_name,
                u_motm.first_name as motm_first_name,
                u_motm.last_name as motm_last_name
            FROM fixtures f
            JOIN teams ht ON f.home_team_id = ht.id
            JOIN teams at ON f.away_team_id = at.id
            JOIN clubs c_ht ON ht.club_id = c_ht.id
            JOIN clubs c_at ON at.club_id = c_at.id
            LEFT JOIN users u_hm ON ht.manager_id = u_hm.id
            LEFT JOIN users u_am ON at.manager_id = u_am.id
            LEFT JOIN users u_ref ON f.referee_id = u_ref.id
            LEFT JOIN users u_motm ON f.man_of_the_match_player_id = u_motm.id
            WHERE f.id = :id
        ");
        $this->db->bind(':id', $id);
        $row = $this->db->single();

        if($this->db->rowCount() > 0){
            return $row;
        } else {
            return false;
        }
    }

    // Update a fixture's score and status
    public function updateScoreAndStatus($data){
        $sql = 'UPDATE fixtures SET home_team_score = :home_team_score, away_team_score = :away_team_score';
        if (isset($data['status'])) {
            $sql .= ', status = :status';
        }
        $sql .= ' WHERE id = :id';
        $this->db->query($sql);
        
        // Bind values
        $this->db->bind(':id', $data['fixture_id']);
        $this->db->bind(':home_team_score', $data['home_team_score']);
        $this->db->bind(':away_team_score', $data['away_team_score']);
        if (isset($data['status'])) {
            $this->db->bind(':status', $data['status']);
        }

        // Execute
        return $this->db->execute();
    }

    // Update just the status of a fixture
    public function updateStatus($fixture_id, $status, $time_column = null) {
        $sql = 'UPDATE fixtures SET status = :status';
        if ($time_column) {
            $sql .= ", $time_column = NOW()";
        }
        $sql .= ' WHERE id = :id';

        $this->db->query($sql);
        $this->db->bind(':id', $fixture_id);
        $this->db->bind(':status', $status);

        return $this->db->execute();
    }

    // Increment the score for a team in a fixture
    public function incrementScore($fixture_id, $team_id) {
        $fixture = $this->findById($fixture_id);
        if (!$fixture) {
            return false; // Fixture not found
        }

        $columnToUpdate = null;
        if ($team_id == $fixture->home_team_id) {
            $columnToUpdate = 'home_team_score';
        } elseif ($team_id == $fixture->away_team_id) {
            $columnToUpdate = 'away_team_score';
        } else {
            return false; // Team not part of this fixture
        }

        $this->db->query("UPDATE fixtures SET {$columnToUpdate} = IFNULL({$columnToUpdate}, 0) + 1 WHERE id = :id");
        $this->db->bind(':id', $fixture_id);
        return $this->db->execute();
    }

    // Assign officials (referee, commissioner) to a fixture
    public function assignOfficials($data){
        $this->db->query('UPDATE fixtures SET referee_id = :referee_id, commissioner_id = :commissioner_id WHERE id = :fixture_id');
        
        // Bind values
        $this->db->bind(':fixture_id', $data['fixture_id']);
        $this->db->bind(':referee_id', $data['referee_id']);
        $this->db->bind(':commissioner_id', $data['commissioner_id']);

        // Execute
        return $this->db->execute();
    }

    // Get live matches for a league
    public function getLiveMatches($league_id) {
        $this->db->query("
            SELECT f.id, f.match_datetime, f.status, f.home_team_score, f.away_team_score, f.first_half_start_time, f.second_half_start_time, f.first_half_end_time, f.home_team_id, f.away_team_id, 
            TIMESTAMPDIFF(SECOND, f.first_half_start_time, NOW()) as elapsed_1st,
            TIMESTAMPDIFF(SECOND, f.second_half_start_time, NOW()) as elapsed_2nd,
            TIMESTAMPDIFF(SECOND, f.first_half_end_time, NOW()) as elapsed_half_time,
            c_ht.name as home_team_name, c_at.name as away_team_name, c_ht.logo as home_team_logo, c_at.logo as away_team_logo
            FROM fixtures f
            JOIN teams ht ON f.home_team_id = ht.id JOIN teams at ON f.away_team_id = at.id
            JOIN clubs c_ht ON ht.club_id = c_ht.id JOIN clubs c_at ON at.club_id = c_at.id
            WHERE f.league_id = :league_id AND f.status IN ('live', 'half_time', 'full_time')
            ORDER BY f.match_datetime ASC
        ");
        $this->db->bind(':league_id', $league_id);
        return $this->db->resultSet();
    }

    // Get recent results for a league
    public function getRecentResults($league_id, $limit = 3) {
        $this->db->query("
            SELECT f.id, f.match_datetime, f.status, f.gameweek, f.home_team_score, f.away_team_score, f.home_team_id, f.away_team_id, c_ht.name as home_team_name, c_at.name as away_team_name, c_ht.logo as home_team_logo, c_at.logo as away_team_logo
            FROM fixtures f
            JOIN teams ht ON f.home_team_id = ht.id JOIN teams at ON f.away_team_id = at.id
            JOIN clubs c_ht ON ht.club_id = c_ht.id JOIN clubs c_at ON at.club_id = c_at.id
            WHERE f.league_id = :league_id AND f.status = 'finished'
            ORDER BY f.match_datetime DESC
            LIMIT :limit
        ");
        $this->db->bind(':league_id', $league_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

    // Get upcoming fixtures for a league
    public function getUpcomingFixtures($league_id, $limit = 3) {
        $this->db->query("
            SELECT f.id, f.match_datetime, f.gameweek, f.home_team_id, f.away_team_id, c_ht.name as home_team_name, c_at.name as away_team_name, c_ht.logo as home_team_logo, c_at.logo as away_team_logo
            FROM fixtures f
            JOIN teams ht ON f.home_team_id = ht.id JOIN teams at ON f.away_team_id = at.id
            JOIN clubs c_ht ON ht.club_id = c_ht.id JOIN clubs c_at ON at.club_id = c_at.id
            WHERE f.league_id = :league_id AND f.status = 'scheduled'
            ORDER BY f.match_datetime ASC
            LIMIT :limit
        ");
        $this->db->bind(':league_id', $league_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

    // Get all fixtures for a specific commissioner
    public function getAllByCommissioner($commissioner_id) {
        $this->db->query('
            SELECT
                f.id, f.match_datetime, f.status, f.home_team_score, f.away_team_score, f.gameweek,
                c_ht.name as home_team_name,
                c_at.name as away_team_name,
                c_ht.logo as home_team_logo,
                c_at.logo as away_team_logo
            FROM fixtures f
            JOIN teams ht ON f.home_team_id = ht.id JOIN teams at ON f.away_team_id = at.id
            JOIN clubs c_ht ON ht.club_id = c_ht.id JOIN clubs c_at ON at.club_id = c_at.id
            WHERE f.commissioner_id = :commissioner_id ORDER BY f.match_datetime DESC
        ');
        $this->db->bind(':commissioner_id', $commissioner_id);
        return $this->db->resultSet();
    }

    // Get recent and upcoming matches for a specific club
    public function getMatchesByClub($club_id, $limit = 10) {
        $this->db->query("
            SELECT f.id, f.match_datetime, f.status, f.home_team_id, f.away_team_id, f.home_team_score, f.away_team_score,
                   c_ht.name as home_team_name, c_at.name as away_team_name,
                   c_ht.logo as home_team_logo, c_at.logo as away_team_logo
            FROM fixtures f
            JOIN teams ht ON f.home_team_id = ht.id
            JOIN teams at ON f.away_team_id = at.id
            JOIN clubs c_ht ON ht.club_id = c_ht.id
            JOIN clubs c_at ON at.club_id = c_at.id
            WHERE ht.club_id = :home_club_id OR at.club_id = :away_club_id
            ORDER BY f.match_datetime DESC
            LIMIT :limit
        ");
        $this->db->bind(':home_club_id', $club_id);
        $this->db->bind(':away_club_id', $club_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

    // Get all fixtures with details for the admin panel
    public function getAllForAdmin() {
        $this->db->query('
            SELECT
                f.id, f.match_datetime, f.gameweek, f.status,
                l.name as league_name,
                c_ht.name as home_team_name,
                c_at.name as away_team_name,
                ref.username as referee_name,
                com.username as commissioner_name
            FROM fixtures f
            JOIN leagues l ON f.league_id = l.id
            JOIN teams ht ON f.home_team_id = ht.id
            JOIN teams at ON f.away_team_id = at.id
            JOIN clubs c_ht ON ht.club_id = c_ht.id
            JOIN clubs c_at ON at.club_id = c_at.id
            LEFT JOIN users ref ON f.referee_id = ref.id
            LEFT JOIN users com ON f.commissioner_id = com.id
            ORDER BY f.match_datetime DESC
        ');
        return $this->db->resultSet();
    }

    // Get all fixtures with details for the admin panel (Alias for getAllForAdmin to fix error)
    public function getAllFixturesWithDetails() {
        $this->db->query('
            SELECT
                f.id, f.match_datetime, f.gameweek, f.status, f.referee_payment_status,
                l.name as league_name,
                c_ht.name as home_team_name,
                c_at.name as away_team_name,
                ref.username as referee_name,
                com.username as commissioner_name
            FROM fixtures f
            JOIN leagues l ON f.league_id = l.id
            JOIN teams ht ON f.home_team_id = ht.id
            JOIN teams at ON f.away_team_id = at.id
            JOIN clubs c_ht ON ht.club_id = c_ht.id
            JOIN clubs c_at ON at.club_id = c_at.id
            LEFT JOIN users ref ON f.referee_id = ref.id
            LEFT JOIN users com ON f.commissioner_id = com.id
            ORDER BY f.match_datetime DESC
        ');
        return $this->db->resultSet();
    }

    // Update an existing fixture
    public function update($data) {
        $this->db->query('UPDATE fixtures SET league_id = :league_id, home_team_id = :home_team_id, away_team_id = :away_team_id, match_datetime = :match_datetime, gameweek = :gameweek, venue = :venue, referee_id = :referee_id, commissioner_id = :commissioner_id WHERE id = :id');
        $this->db->bind(':id', $data['id']);
        $this->db->bind(':league_id', $data['league_id']);
        $this->db->bind(':home_team_id', $data['home_team_id']);
        $this->db->bind(':away_team_id', $data['away_team_id']);
        $this->db->bind(':match_datetime', $data['match_datetime']);
        $this->db->bind(':gameweek', $data['gameweek']);
        $this->db->bind(':venue', $data['venue']);
        $this->db->bind(':referee_id', $data['referee_id']);
        $this->db->bind(':commissioner_id', $data['commissioner_id']);
        return $this->db->execute();
    }

    // Delete a fixture
    public function delete($id) {
        $this->db->query('DELETE FROM fixtures WHERE id = :id');
        $this->db->bind(':id', $id);
        return $this->db->execute();
    }

    /**
     * Get the number of the most recently completed gameweek for a league.
     * @param int $league_id The ID of the league.
     * @return int|null The gameweek number or null if none are finished.
     */
    public function getLatestFinishedGameweek($league_id) {
        // Only consider a gameweek finished if ALL matches in it are finished, postponed, or cancelled.
        $this->db->query("
            SELECT MAX(f1.gameweek) as latest_gameweek 
            FROM fixtures f1
            WHERE f1.league_id = :league_id
            AND NOT EXISTS (
                SELECT 1 FROM fixtures f2 
                WHERE f2.league_id = f1.league_id AND f2.gameweek = f1.gameweek AND f2.status IN ('scheduled', 'live', 'half_time', 'full_time')
            )
        ");
        $this->db->bind(':league_id', $league_id);
        $row = $this->db->single();
        return $row ? (int)$row->latest_gameweek : null;
    }

    /**
     * Get the very next single upcoming fixture for a league.
     * @param int $league_id The ID of the league.
     * @return object|null The fixture object or null if none are scheduled.
     */
    public function getNextUpcomingFixture($league_id) {
        $this->db->query("SELECT match_datetime, gameweek FROM fixtures WHERE league_id = :league_id AND status = 'scheduled' ORDER BY match_datetime ASC LIMIT 1");
        $this->db->bind(':league_id', $league_id);
        $row = $this->db->single();
        return $row ?: null;
    }

    /**
     * Get all upcoming fixtures for a specific team.
     * @param int $team_id The ID of the team.
     * @return array An array of fixture objects.
     */
    public function getUpcomingFixturesForTeam(int $team_id): array {
        $this->db->query('
            SELECT 
                f.id, f.match_datetime, f.gameweek, f.status,
                f.home_team_id, f.away_team_id, f.home_team_paid_referee, f.away_team_paid_referee,
                c_ht.name as home_team_name,
                c_at.name as away_team_name,
                c_ht.logo as home_team_logo,
                c_at.logo as away_team_logo
            FROM fixtures f
            INNER JOIN teams ht ON f.home_team_id = ht.id
            INNER JOIN teams at ON f.away_team_id = at.id
            INNER JOIN clubs c_ht ON ht.club_id = c_ht.id
            INNER JOIN clubs c_at ON at.club_id = c_at.id
            WHERE (f.home_team_id = :home_team_id OR f.away_team_id = :away_team_id)
            AND f.status = "scheduled"
            ORDER BY f.match_datetime ASC
        ');
        $this->db->bind(':home_team_id', $team_id);
        $this->db->bind(':away_team_id', $team_id);
        return $this->db->resultSet();
    }

    /**
     * Get recent results for a specific team.
     * @param int $team_id The ID of the team.
     * @param int $limit The number of results to return.
     * @return array An array of fixture objects.
     */
    public function getRecentResultsForTeam(int $team_id, int $limit = 5): array
    {
        $this->db->query('
            SELECT 
                f.id, f.match_datetime, f.status, f.home_team_score, f.away_team_score,
                c_ht.name as home_team_name,
                c_at.name as away_team_name
            FROM fixtures f
            INNER JOIN teams ht ON f.home_team_id = ht.id
            INNER JOIN teams at ON f.away_team_id = at.id
            INNER JOIN clubs c_ht ON ht.club_id = c_ht.id
            INNER JOIN clubs c_at ON at.club_id = c_at.id
            WHERE (f.home_team_id = :home_team_id OR f.away_team_id = :away_team_id)
            AND f.status = "finished"
            ORDER BY f.match_datetime DESC
            LIMIT :limit
        ');
        $this->db->bind(':home_team_id', $team_id);
        $this->db->bind(':away_team_id', $team_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

    /**
     * Get all upcoming fixtures for a specific club.
     * @param int $club_id The ID of the club.
     * @param int $limit The number of fixtures to return.
     * @return array An array of fixture objects.
     */
    public function getUpcomingFixturesForClub(int $club_id, int $limit = 3): array {
        $this->db->query('
            SELECT 
                f.id, f.match_datetime, f.gameweek, f.status,
                c_ht.name as home_team_name,
                c_at.name as away_team_name,
                c_ht.logo as home_team_logo,
                c_at.logo as away_team_logo
            FROM fixtures f
            INNER JOIN teams ht ON f.home_team_id = ht.id
            INNER JOIN teams at ON f.away_team_id = at.id
            INNER JOIN clubs c_ht ON ht.club_id = c_ht.id
            INNER JOIN clubs c_at ON at.club_id = c_at.id
            WHERE (ht.club_id = :club_id OR at.club_id = :club_id2)
            AND f.status = "scheduled"
            ORDER BY f.match_datetime ASC
            LIMIT :limit
        ');
        $this->db->bind(':club_id', $club_id);
        $this->db->bind(':club_id2', $club_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

    /**
     * Get recent results for a specific club.
     * @param int $club_id The ID of the club.
     * @param int $limit The number of results to return.
     * @return array An array of fixture objects.
     */
    public function getRecentResultsForClub(int $club_id, int $limit = 5): array {
        $this->db->query('
            SELECT 
                f.id, f.match_datetime, f.status, f.home_team_score, f.away_team_score,
                c_ht.name as home_team_name,
                c_at.name as away_team_name
            FROM fixtures f
            INNER JOIN teams ht ON f.home_team_id = ht.id
            INNER JOIN teams at ON f.away_team_id = at.id
            INNER JOIN clubs c_ht ON ht.club_id = c_ht.id
            INNER JOIN clubs c_at ON at.club_id = c_at.id
            WHERE (ht.club_id = :club_id OR at.club_id = :club_id2)
            AND f.status = "finished"
            ORDER BY f.match_datetime DESC
            LIMIT :limit
        ');
        $this->db->bind(':club_id', $club_id);
        $this->db->bind(':club_id2', $club_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

    /**
     * Get all fixtures for a specific gameweek in a league.
     * This is an alias for getAllByLeague.
     * @param int $league_id The ID of the league.
     * @param int $gameweek The gameweek number.
     * @return array An array of fixture objects.
     */
    public function getFixturesByGameweek($league_id, $gameweek) {
        return $this->getAllByLeague($league_id, $gameweek);
    }

    /**
     * Get head-to-head match history between two teams.
     * @param int $team1_id
     * @param int $team2_id
     * @param int $limit
     * @return array
     */
    public function getHeadToHead(int $team1_id, int $team2_id, int $limit = 10): array
    {
        $this->db->query('
            SELECT 
                f.id, f.match_datetime, f.status, f.home_team_score, f.away_team_score,
                f.home_team_id, f.away_team_id,
                c_ht.name as home_team_name,
                c_at.name as away_team_name,
                c_ht.logo as home_team_logo,
                c_at.logo as away_team_logo
            FROM fixtures f
            INNER JOIN teams ht ON f.home_team_id = ht.id
            INNER JOIN teams at ON f.away_team_id = at.id
            INNER JOIN clubs c_ht ON ht.club_id = c_ht.id
            INNER JOIN clubs c_at ON at.club_id = c_at.id
            WHERE 
                ((f.home_team_id = :team1_id AND f.away_team_id = :team2_id) OR (f.home_team_id = :team2_id AND f.away_team_id = :team1_id))
            AND f.status = "finished"
            ORDER BY f.match_datetime DESC
            LIMIT :limit
        ');
        $this->db->bind(':team1_id', $team1_id);
        $this->db->bind(':team2_id', $team2_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

}