<?php

class Stat {
    private $db;

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

    /**
     * A private helper method to get top player stats based on a specific event.
     * @param int $league_id The ID of the league.
     * @param string $event_type The event type to count (e.g., 'goal', 'foul').
     * @param string $count_alias The alias for the COUNT column (e.g., 'goals').
     * @param int $limit The number of players to return.
     * @param string $join_on The column to join match_events on ('player_id' or 'assisted_by_player_id').
     * @return array
     */
    private function _getTopPlayerStats(int $league_id, string $event_type, string $count_alias, int $limit, string $join_on = 'player_id'): array {
        $this->db->query("
            SELECT u.id as player_id, u.first_name, u.last_name, u.profile_picture, c.name as club_name, c.logo as club_logo, COUNT(me.id) as {$count_alias}
            FROM users u
            JOIN players p ON u.id = p.user_id
            JOIN teams t ON p.team_id = t.id
            JOIN clubs c ON t.club_id = c.id
            JOIN match_events me ON u.id = me.{$join_on}
            WHERE c.league_id = :league_id AND me.event_type = :event_type
            GROUP BY u.id, u.first_name, u.last_name, c.name, c.logo
            ORDER BY {$count_alias} DESC
            LIMIT :limit
        ");
        $this->db->bind(':league_id', $league_id);
        $this->db->bind(':event_type', $event_type);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

    public function getTopScorers($league_id, $limit = 10, $team_id = null) {
        // Goals can be 'goal' or 'penalty_scored', so it needs a custom query.
        $sql = "
            SELECT u.id as player_id, u.first_name, u.last_name, u.profile_picture, c.name as club_name, c.logo as club_logo, COUNT(me.id) as goals
            FROM users u
            JOIN players p ON u.id = p.user_id
            JOIN teams t ON p.team_id = t.id
            JOIN clubs c ON t.club_id = c.id
            JOIN match_events me ON u.id = me.player_id
            WHERE c.league_id = :league_id AND me.event_type IN ('goal', 'penalty_scored')";
        
        if ($team_id) {
            $sql .= " AND p.team_id = :team_id";
        }

        $sql .= "
            GROUP BY u.id, u.first_name, u.last_name, c.name, c.logo
            ORDER BY goals DESC, u.last_name ASC
            LIMIT :limit
        ";

        $this->db->query($sql);
        $this->db->bind(':league_id', $league_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        if ($team_id) {
            $this->db->bind(':team_id', $team_id);
        }
        return $this->db->resultSet();
    }

    public function getTopAssists($league_id, $limit = 10, $team_id = null) {
        $sql = "
            SELECT u.id as player_id, u.first_name, u.last_name, u.profile_picture, c.name as club_name, c.logo as club_logo, COUNT(me.id) as assists
            FROM users u
            JOIN players p ON u.id = p.user_id
            JOIN teams t ON p.team_id = t.id
            JOIN clubs c ON t.club_id = c.id
            JOIN match_events me ON u.id = me.assisted_by_player_id
            WHERE c.league_id = :league_id AND me.event_type = 'goal'";
        
        if ($team_id) {
            $sql .= " AND p.team_id = :team_id";
        }

        $sql .= "
            GROUP BY u.id, u.first_name, u.last_name, c.name, c.logo
            ORDER BY assists DESC, u.last_name ASC
            LIMIT :limit
        ";

        $this->db->query($sql);
        $this->db->bind(':league_id', $league_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        if ($team_id) {
            $this->db->bind(':team_id', $team_id);
        }
        return $this->db->resultSet();
    }

    public function getMostCleanSheets($league_id, $limit = 10, $team_id = null) {
        $sql = "
            SELECT u.id as player_id, u.first_name, u.last_name, u.profile_picture, c.name as club_name, c.logo as club_logo, COUNT(f.id) as clean_sheets
            FROM users u
            JOIN players p ON u.id = p.user_id
            JOIN teams t ON p.team_id = t.id
            JOIN clubs c ON t.club_id = c.id
            JOIN fixtures f ON (
                (p.team_id = f.home_team_id AND f.away_team_score = 0) OR
                (p.team_id = f.away_team_id AND f.home_team_score = 0)
            )
            JOIN fixture_lineups ml ON u.id = ml.player_id AND f.id = ml.fixture_id
            WHERE c.league_id = :league_id AND p.position = 'Goalkeeper' AND f.status = 'finished' AND ml.lineup_status = 'Starting'";
        
        if ($team_id) {
            $sql .= " AND p.team_id = :team_id";
        }

        $sql .= "
            GROUP BY u.id, u.first_name, u.last_name, c.name, c.logo
            ORDER BY clean_sheets DESC
            LIMIT :limit
        ";

        $this->db->query($sql);
        $this->db->bind(':league_id', $league_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        if ($team_id) {
            $this->db->bind(':team_id', $team_id);
        }
        return $this->db->resultSet();
    }

    public function getMostCards($league_id, $limit = 10) {
        $this->db->query("
            SELECT u.id as player_id, u.first_name, u.last_name, u.profile_picture, c.name as club_name, c.logo as club_logo, 
                   SUM(CASE WHEN me.event_type = 'yellow_card' THEN 1 ELSE 0 END) + SUM(CASE WHEN me.event_type = 'red_card' THEN 2 ELSE 0 END) as card_points,
                   SUM(CASE WHEN me.event_type = 'yellow_card' THEN 1 ELSE 0 END) as yellow_cards,
                   SUM(CASE WHEN me.event_type = 'red_card' THEN 1 ELSE 0 END) as red_cards
            FROM users u
            JOIN players p ON u.id = p.user_id
            JOIN teams t ON p.team_id = t.id
            JOIN clubs c ON t.club_id = c.id
            JOIN match_events me ON u.id = me.player_id
            WHERE c.league_id = :league_id AND me.event_type IN ('yellow_card', 'red_card')
            GROUP BY u.id, u.first_name, u.last_name, c.name, c.logo
            ORDER BY card_points DESC, yellow_cards DESC
            LIMIT :limit
        ");
        $this->db->bind(':league_id', $league_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

    public function getMostShots($league_id, $limit = 10) {
        $this->db->query("
            SELECT u.id as player_id, u.first_name, u.last_name, u.profile_picture, c.name as club_name, c.logo as club_logo, COUNT(me.id) as shots
            FROM users u
            JOIN players p ON u.id = p.user_id
            JOIN teams t ON p.team_id = t.id
            JOIN clubs c ON t.club_id = c.id
            JOIN match_events me ON u.id = me.player_id
            WHERE c.league_id = :league_id AND me.event_type IN ('goal', 'penalty_scored', 'shot_on_target', 'shot_off_target')
            GROUP BY u.id, u.first_name, u.last_name, c.name, c.logo
            ORDER BY shots DESC
            LIMIT :limit
        ");
        $this->db->bind(':league_id', $league_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

    public function getMostShotsOnTarget($league_id, $limit = 10) {
        return $this->_getTopPlayerStats($league_id, 'shot_on_target', 'shots_on_target', $limit);
    }

    public function getMostYellowCards($league_id, $limit = 10) {
        return $this->_getTopPlayerStats($league_id, 'yellow_card', 'yellow_cards', $limit);
    }

    public function getMostRedCards($league_id, $limit = 10) {
        return $this->_getTopPlayerStats($league_id, 'red_card', 'red_cards', $limit);
    }

    public function getMostAppearances($league_id, $limit = 10) {
        $this->db->query("
            SELECT u.id as player_id, u.first_name, u.last_name, u.profile_picture, c.name as club_name, c.logo as club_logo, COUNT(DISTINCT fl.fixture_id) as appearances
            FROM users u
            JOIN players p ON u.id = p.user_id
            JOIN teams t ON p.team_id = t.id
            JOIN clubs c ON t.club_id = c.id
            JOIN fixture_lineups fl ON u.id = fl.player_id
            WHERE c.league_id = :league_id
            GROUP BY u.id, u.first_name, u.last_name, c.name, c.logo
            ORDER BY appearances DESC
            LIMIT :limit
        ");
        $this->db->bind(':league_id', $league_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

    public function getMostMinutesPlayed($league_id, $limit = 10) {
        // This relies on fantasy_player_points being populated
        $this->db->query("
            SELECT u.id as player_id, u.first_name, u.last_name, u.profile_picture, c.name as club_name, c.logo as club_logo, SUM(fpp.minutes_played) as minutes
            FROM users u
            JOIN players p ON u.id = p.user_id
            JOIN teams t ON p.team_id = t.id
            JOIN clubs c ON t.club_id = c.id
            JOIN fantasy_player_points fpp ON u.id = fpp.player_id
            WHERE c.league_id = :league_id
            GROUP BY u.id, u.first_name, u.last_name, c.name, c.logo
            HAVING minutes > 0
            ORDER BY minutes DESC
            LIMIT :limit
        ");
        $this->db->bind(':league_id', $league_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

    public function getMostFouls($league_id, $limit = 10) {
        return $this->_getTopPlayerStats($league_id, 'foul', 'fouls', $limit);
    }

    public function getTeamStatsByLeague($league_id) {
        $this->db->query("
            SELECT
                t.id as team_id,
                t.name as team_name,
                c.logo as club_logo,
                COUNT(DISTINCT f.id) as played,
                IFNULL(SUM(CASE WHEN f.home_team_id = t.id THEN f.home_team_score ELSE f.away_team_score END), 0) as goals_for,
                IFNULL(SUM(CASE WHEN f.home_team_id = t.id THEN f.away_team_score ELSE f.home_team_score END), 0) as goals_against,
                IFNULL(SUM(CASE WHEN (f.home_team_id = t.id AND f.away_team_score = 0) OR (f.away_team_id = t.id AND f.home_team_score = 0) THEN 1 ELSE 0 END), 0) as clean_sheets,
                (SELECT COUNT(*) FROM match_events me WHERE me.team_id = t.id AND me.event_type = 'shot_on_target') as shots_on_target,
                (SELECT COUNT(*) FROM match_events me WHERE me.team_id = t.id AND me.event_type = 'shot_off_target') as shots_off_target,
                (SELECT COUNT(*) FROM match_events me WHERE me.team_id = t.id AND me.event_type = 'corner') as corners,
                (SELECT COUNT(*) FROM match_events me WHERE me.team_id = t.id AND me.event_type IN ('goal', 'penalty_scored', 'shot_on_target', 'shot_off_target')) as shots,
                (SELECT COUNT(*) FROM match_events me WHERE me.team_id = t.id AND me.event_type = 'yellow_card') as yellow_cards,
                (SELECT COUNT(*) FROM match_events me WHERE me.team_id = t.id AND me.event_type = 'red_card') as red_cards,
                (SELECT COUNT(*) FROM match_events me WHERE me.team_id = t.id AND me.event_type = 'foul') as fouls_committed,
                (
                    SELECT COUNT(*)
                    FROM match_events me
                    JOIN fixtures f_opp ON me.fixture_id = f_opp.id
                    WHERE 
                        (f_opp.home_team_id = t.id AND me.team_id = f_opp.away_team_id AND me.event_type = 'foul') OR
                        (f_opp.away_team_id = t.id AND me.team_id = f_opp.home_team_id AND me.event_type = 'foul')
                ) as fouls_won
            FROM teams t
            JOIN clubs c ON t.club_id = c.id
            LEFT JOIN fixtures f ON (t.id = f.home_team_id OR t.id = f.away_team_id) AND f.status = 'finished'
            WHERE c.league_id = :league_id
            GROUP BY t.id, t.name, c.logo
            ORDER BY team_name ASC
        ");
        $this->db->bind(':league_id', $league_id);
        return $this->db->resultSet();
    }

    /**
     * Get aggregated career/season stats for a single player.
     * @param int $player_id The user ID of the player.
     * @param string|null $season The season to filter by (e.g., '2023/2024'), or null for career.
     * @param int|null $team_id The team ID to filter by, or null for all teams.
     * @return object An object containing the player's stats.
     */
    public function getStatsForPlayer($player_id, $season = null) {
        // --- Build WHERE clauses and parameters for each subquery ---
        $where_sql = '';

        if ($season) {
            $where_sql = 'AND f.season = :season';
        }

        $this->db->query("
            SELECT
                (SELECT COUNT(DISTINCT fl.fixture_id) FROM fixture_lineups fl JOIN fixtures f ON fl.fixture_id = f.id WHERE fl.player_id = :p_id1 {$where_sql}) as appearances,
                (SELECT SUM(fpp.minutes_played) FROM fantasy_player_points fpp JOIN fixtures f ON fpp.fixture_id = f.id WHERE fpp.player_id = :p_id2 {$where_sql}) as minutes_played,
                (SELECT COUNT(*) FROM match_events me JOIN fixtures f ON me.fixture_id = f.id WHERE me.player_id = :p_id3 AND me.event_type IN ('goal', 'penalty_scored') {$where_sql}) as goals,
                (SELECT COUNT(*) FROM match_events me JOIN fixtures f ON me.fixture_id = f.id WHERE me.assisted_by_player_id = :p_id4 AND me.event_type = 'goal' {$where_sql}) as assists,
                (SELECT COUNT(*) FROM match_events me JOIN fixtures f ON me.fixture_id = f.id WHERE me.player_id = :p_id5 AND me.event_type IN ('goal', 'penalty_scored', 'shot_on_target', 'shot_off_target') {$where_sql}) as shots,
                (SELECT COUNT(*) FROM match_events me JOIN fixtures f ON me.fixture_id = f.id WHERE me.player_id = :p_id6 AND me.event_type = 'shot_on_target' {$where_sql}) as shots_on_target,
                (SELECT COUNT(*) FROM match_events me JOIN fixtures f ON me.fixture_id = f.id WHERE me.player_id = :p_id7 AND me.event_type = 'yellow_card' {$where_sql}) as yellow_cards,
                (SELECT COUNT(*) FROM match_events me JOIN fixtures f ON me.fixture_id = f.id WHERE me.player_id = :p_id8 AND me.event_type = 'red_card' {$where_sql}) as red_cards,
                (SELECT COUNT(*) FROM match_events me JOIN fixtures f ON me.fixture_id = f.id WHERE me.player_id = :p_id9 AND me.event_type = 'foul' {$where_sql}) as fouls
        ");

        // Bind all parameters for all subqueries
        $this->db->bind(':p_id1', $player_id);
        $this->db->bind(':p_id2', $player_id);
        $this->db->bind(':p_id3', $player_id);
        $this->db->bind(':p_id4', $player_id);
        $this->db->bind(':p_id5', $player_id);
        $this->db->bind(':p_id6', $player_id);
        $this->db->bind(':p_id7', $player_id);
        $this->db->bind(':p_id8', $player_id);
        $this->db->bind(':p_id9', $player_id);

        if ($season) {
            $this->db->bind(':season', $season);
        }

        $stats = $this->db->single();

        // In a real scenario, you might want to return a default object if no stats are found.
        return $stats;
    }

    /**
     * Gets detailed performance data for charts.
     * @param int $player_id The user ID of the player.
     * @return object An object containing data for charts.
     */
    public function getPerformanceChartData(int $player_id): object
    {
        // This query now uses the fantasy_player_points table for minutes played.
        $this->db->query("
            SELECT
                (SELECT SUM(minutes_played) FROM fantasy_player_points WHERE player_id = :p_id1) as total_minutes,
                (SELECT COUNT(*) FROM match_events WHERE player_id = :p_id2 AND event_type IN ('goal', 'penalty_scored')) as total_goals,
                (SELECT COUNT(*) FROM match_events WHERE player_id = :p_id3 AND event_type = 'shot_on_target') as shots_on_target,
                (SELECT COUNT(*) FROM match_events WHERE player_id = :p_id4 AND event_type = 'shot_off_target') as shots_off_target,
                (SELECT COUNT(DISTINCT f.id) FROM fixture_lineups fl JOIN fixtures f ON fl.fixture_id = f.id WHERE fl.player_id = :p_id5 AND f.status = 'finished' AND ((fl.team_id = f.home_team_id AND f.away_team_score = 0) OR (fl.team_id = f.away_team_id AND f.home_team_score = 0))) as clean_sheets
        ");
        $this->db->bind(':p_id1', $player_id);
        $this->db->bind(':p_id2', $player_id);
        $this->db->bind(':p_id3', $player_id);
        $this->db->bind(':p_id4', $player_id);
        $this->db->bind(':p_id5', $player_id);

        return $this->db->single();
    }
}
?>