<?php

class Player extends Model {

    // Fetch all players belonging to a specific team
    public function getPlayersByTeamId($team_id) {
        $this->db->query("SELECT p.*, p.user_id as id, u.first_name, u.last_name, u.email, u.profile_picture 
                          FROM players p 
                          JOIN users u ON p.user_id = u.id 
                          WHERE p.team_id = :team_id");
        $this->db->bind(':team_id', $team_id);
        return $this->db->resultSet();
    }

    // Get detailed information for a single player
    public function getPlayerDetailsById($player_id) {
        $this->db->query("SELECT p.*, u.first_name, u.last_name, u.email, u.profile_picture, 
                                 t.name as team_name, c.name as club_name, c.logo as club_logo, c.id as club_id
                          FROM players p 
                          JOIN users u ON p.user_id = u.id 
                          LEFT JOIN teams t ON p.team_id = t.id
                          LEFT JOIN clubs c ON t.club_id = c.id
                          WHERE p.user_id = :id");
        $this->db->bind(':id', $player_id);
        return $this->db->single();
    }

    // Update player attributes
    public function updatePlayerDetails($player_id, $data) {
        $this->db->query("UPDATE players SET 
                            squad_number = :squad_number, 
                            position = :position, 
                            height_cm = :height_cm, 
                            weight_kg = :weight_kg, 
                            date_of_birth = :date_of_birth, 
                            nationality = :nationality, 
                            preferred_foot = :preferred_foot 
                          WHERE user_id = :id");
        $this->db->bind(':squad_number', $data['squad_number']);
        $this->db->bind(':position', $data['position']);
        $this->db->bind(':height_cm', $data['height_cm']);
        $this->db->bind(':weight_kg', $data['weight_kg']);
        $this->db->bind(':date_of_birth', $data['date_of_birth']);
        $this->db->bind(':nationality', $data['nationality']);
        $this->db->bind(':preferred_foot', $data['preferred_foot']);
        $this->db->bind(':id', $player_id);
        return $this->db->execute();
    }

    // Remove a player from a team (Release)
    public function removePlayerFromTeam($player_id) {
        // Get user_id first to update role
        $this->db->query("SELECT user_id FROM players WHERE user_id = :id");
        $this->db->bind(':id', $player_id);
        $player = $this->db->single();

        if ($player) {
            // Unassign team
            $this->db->query("UPDATE players SET team_id = NULL WHERE user_id = :id");
            $this->db->bind(':id', $player_id);
            if ($this->db->execute()) {
                // Revert user role to 'user' (Free Agent/Fan)
                $this->db->query("UPDATE users SET role = 'user' WHERE id = :user_id");
                $this->db->bind(':user_id', $player->user_id);
                return $this->db->execute();
            }
        }
        return false;
    }

    // Assign a user (fan) to a team as a player
    public function assignFanAsPlayer($user_id, $team_id) {
        // Check if player record exists
        $this->db->query("SELECT id FROM players WHERE user_id = :user_id");
        $this->db->bind(':user_id', $user_id);
        $existing = $this->db->single();

        if ($existing) {
            $this->db->query("UPDATE players SET team_id = :team_id WHERE user_id = :user_id");
        } else {
            $this->db->query("INSERT INTO players (user_id, team_id) VALUES (:user_id, :team_id)");
        }
        $this->db->bind(':user_id', $user_id);
        $this->db->bind(':team_id', $team_id);
        
        if ($this->db->execute()) {
            $this->db->query("UPDATE users SET role = 'player' WHERE id = :user_id");
            $this->db->bind(':user_id', $user_id);
            return $this->db->execute();
        }
        return false;
    }

    // --- Injury & Suspension Methods ---

    public function getInjuredPlayersByTeamId($team_id) {
        $this->db->query("SELECT p.*, p.user_id as id, u.first_name, u.last_name, i.injury_description, i.start_date, i.expected_return_date, i.id as injury_id
                          FROM players p
                          JOIN users u ON p.user_id = u.id
                          JOIN injuries i ON p.user_id = i.player_id
                          WHERE p.team_id = :team_id AND i.status != 'Fit'");
        $this->db->bind(':team_id', $team_id);
        return $this->db->resultSet();
    }

    public function getSuspendedPlayersByTeamId($team_id) {
        $this->db->query("SELECT p.*, p.user_id as id, u.first_name, u.last_name, u.profile_picture, s.id as suspension_id, s.reason, s.matches_to_serve
                          FROM players p
                          JOIN users u ON p.user_id = u.id
                          JOIN player_suspensions s ON p.user_id = s.player_id
                          WHERE p.team_id = :team_id AND s.status = 'Active'");
        $this->db->bind(':team_id', $team_id);
        return $this->db->resultSet();
    }

    public function addInjuryRecord($data) {
        $this->db->query("INSERT INTO injuries (player_id, injury_description, start_date, expected_return_date, status) 
                          VALUES (:player_id, :injury_description, :start_date, :expected_return_date, 'Active')");
        $this->db->bind(':player_id', $data['player_id']);
        $this->db->bind(':injury_description', $data['injury_description']);
        $this->db->bind(':start_date', $data['start_date']);
        $this->db->bind(':expected_return_date', $data['expected_return_date'] ?: null);
        return $this->db->execute();
    }

    public function updateInjuryRecord($data) {
        $this->db->query("UPDATE injuries SET injury_description = :desc, start_date = :start, expected_return_date = :end, status = :status WHERE id = :id");
        $this->db->bind(':desc', $data['injury_description']);
        $this->db->bind(':start', $data['start_date']);
        $this->db->bind(':end', $data['expected_return_date'] ?: null);
        $this->db->bind(':status', $data['status']);
        $this->db->bind(':id', $data['injury_id']);
        return $this->db->execute();
    }

    public function getInjuryById($injury_id) {
        $this->db->query("SELECT i.*, u.first_name, u.last_name FROM injuries i JOIN players p ON i.player_id = p.user_id JOIN users u ON p.user_id = u.id WHERE i.id = :id");
        $this->db->bind(':id', $injury_id);
        return $this->db->single();
    }

    public function getInjuryHistoryByTeamId($team_id) {
        $this->db->query("SELECT p.*, u.first_name, u.last_name, i.injury_description, i.start_date, i.expected_return_date
                          FROM players p
                          JOIN users u ON p.user_id = u.id
                          JOIN injuries i ON p.user_id = i.player_id
                          WHERE p.team_id = :team_id AND i.status = 'Fit' ORDER BY i.start_date DESC");
        $this->db->bind(':team_id', $team_id);
        return $this->db->resultSet();
    }

    // --- History & Stats Methods ---

    public function getTransferHistory($player_id) {
        $this->db->query("SELECT t.*, t.created_at as transfer_date, c1.name as from_club, c2.name as to_club 
                          FROM transfers t 
                          LEFT JOIN clubs c1 ON t.from_club_id = c1.id 
                          LEFT JOIN clubs c2 ON t.to_club_id = c2.id 
                          WHERE t.player_id = :id ORDER BY t.created_at DESC");
        $this->db->bind(':id', $player_id);
        return $this->db->resultSet();
    }

    public function getInjuryHistory($player_id) {
        $this->db->query("SELECT * FROM injuries WHERE player_id = :id ORDER BY start_date DESC");
        $this->db->bind(':id', $player_id);
        return $this->db->resultSet();
    }

    public function getSuspensionHistory($player_id) {
        $this->db->query("SELECT *, created_at as date_incurred FROM player_suspensions WHERE player_id = :id ORDER BY created_at DESC");
        $this->db->bind(':id', $player_id);
        return $this->db->resultSet();
    }

    public function getAchievements($player_id) {
        $this->db->query("SELECT * FROM player_achievements WHERE player_id = :id ORDER BY season DESC");
        $this->db->bind(':id', $player_id);
        return $this->db->resultSet();
    }

    // --- Helper Methods ---

    public function getPlayerNamesForFixture($fixture_id) {
        // Get all players from both teams involved in the fixture
        $this->db->query("SELECT u.id, u.first_name, u.last_name 
                          FROM players p 
                          JOIN users u ON p.user_id = u.id
                          JOIN fixtures f ON f.id = :fixture_id
                          WHERE p.team_id = f.home_team_id OR p.team_id = f.away_team_id");
        $this->db->bind(':fixture_id', $fixture_id);
        $results = $this->db->resultSet();
        $names = [];
        foreach ($results as $row) {
            $names[$row->id] = $row->first_name . ' ' . $row->last_name;
        }
        return $names;
    }

    public function getPlayerDetailsForFixture($fixture_id) {
        $this->db->query("SELECT p.*, p.user_id as id, u.first_name, u.last_name, u.profile_picture 
                          FROM players p 
                          JOIN users u ON p.user_id = u.id
                          JOIN fixtures f ON (p.team_id = f.home_team_id OR p.team_id = f.away_team_id)
                          WHERE f.id = :fixture_id");
        $this->db->bind(':fixture_id', $fixture_id);
        $results = $this->db->resultSet();
        $details = [];
        foreach ($results as $row) {
            $details[$row->id] = $row;
        }
        return $details;
    }

    public function getPlayersForFantasySelection($league_id, $player_id = null) {
        $sql = "SELECT p.*, p.user_id as id, u.first_name, u.last_name, u.profile_picture, c.name as club_name, c.name as club_short_name, c.logo as club_logo
                FROM players p
                JOIN users u ON p.user_id = u.id
                JOIN teams t ON p.team_id = t.id
                JOIN clubs c ON t.club_id = c.id
                WHERE c.league_id = :league_id";
        if ($player_id) $sql .= " AND p.user_id = :player_id";
        
        $this->db->query($sql);
        $this->db->bind(':league_id', $league_id);
        if ($player_id) $this->db->bind(':player_id', $player_id);
        return $this->db->resultSet();
    }

    public function countPlayersByClubId($club_id) {
        $this->db->query("SELECT COUNT(*) as count FROM players p JOIN teams t ON p.team_id = t.id WHERE t.club_id = :club_id");
        $this->db->bind(':club_id', $club_id);
        return $this->db->single()->count;
    }

    public function searchPlayers($query) {
        $this->db->query("
            SELECT p.user_id as id, u.first_name, u.last_name, u.profile_picture, c.name as club_name, c.logo as club_logo, p.position
            FROM players p
            JOIN users u ON p.user_id = u.id
            LEFT JOIN teams t ON p.team_id = t.id
            LEFT JOIN clubs c ON t.club_id = c.id
            WHERE u.first_name LIKE :query1 OR u.last_name LIKE :query2
            LIMIT 20
        ");
        $this->db->bind(':query1', '%' . $query . '%');
        $this->db->bind(':query2', '%' . $query . '%');
        return $this->db->resultSet();
    }

    public function getAllPlayersWithClub() {
        $this->db->query("
            SELECT u.id, u.first_name, u.last_name, c.name as club_name
            FROM users u
            JOIN players p ON u.id = p.user_id
            LEFT JOIN teams t ON p.team_id = t.id
            LEFT JOIN clubs c ON t.club_id = c.id
            WHERE u.role = 'player'
            ORDER BY u.last_name, u.first_name
        ");
        return $this->db->resultSet();
    }

    public function updateFantasyPrice($player_id, $price) {
        $this->db->query("UPDATE players SET fantasy_price = :price WHERE user_id = :id");
        $this->db->bind(':price', $price);
        $this->db->bind(':id', $player_id);
        return $this->db->execute();
    }
}