<?php

class FantasyLeague {
    private $db;

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

    // Create a new fantasy league
    public function create($data) {
        $join_code = substr(str_shuffle(str_repeat('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', 8)), 0, 8);

        $this->db->beginTransaction();

        try {
            // 1. Create the league
            $this->db->query('INSERT INTO fantasy_leagues (name, creator_user_id, season, join_code) VALUES (:name, :creator_user_id, :season, :join_code)');
            $this->db->bind(':name', $data['name']);
            $this->db->bind(':creator_user_id', $data['user_id']);
            $this->db->bind(':season', $data['season']);
            $this->db->bind(':join_code', $join_code);
            $this->db->execute();
            
            $league_id = $this->db->lastInsertId();

            // 2. Add creator as member
            $this->db->query('INSERT INTO fantasy_league_members (league_id, fantasy_team_id) VALUES (:league_id, :fantasy_team_id)');
            $this->db->bind(':league_id', $league_id);
            $this->db->bind(':fantasy_team_id', $data['fantasy_team_id']);
            $this->db->execute();

            $this->db->commit();
            // Return an object with both ID and code for convenience
            return (object)['id' => $league_id, 'join_code' => $join_code];
        } catch (Exception $e) {
            $this->db->rollBack();
            return false;
        }
    }

    // Add a fantasy team to a league
    public function addMember($league_id, $fantasy_team_id) {
        $this->db->query('INSERT INTO fantasy_league_members (league_id, fantasy_team_id) VALUES (:league_id, :fantasy_team_id)');
        $this->db->bind(':league_id', $league_id);
        $this->db->bind(':fantasy_team_id', $fantasy_team_id);
        return $this->db->execute();
    }

    // Find a league by its invite code
    public function findByJoinCode($join_code) {
        $this->db->query('SELECT * FROM fantasy_leagues WHERE join_code = :join_code');
        $this->db->bind(':join_code', $join_code);
        return $this->db->single();
    }

    public function getById($id) {
        $this->db->query('SELECT * FROM fantasy_leagues WHERE id = :id');
        $this->db->bind(':id', $id);
        return $this->db->single();
    }

    // Check if a team is already in a league
    public function isMember($league_id, $fantasy_team_id) {
        $this->db->query('SELECT COUNT(*) as count FROM fantasy_league_members WHERE league_id = :league_id AND fantasy_team_id = :fantasy_team_id');
        $this->db->bind(':league_id', $league_id);
        $this->db->bind(':fantasy_team_id', $fantasy_team_id);
        return $this->db->single()->count > 0;
    }

    // Get all leagues a user is a member of
    public function getLeaguesForUser($fantasy_team_id) {
        $this->db->query('
            SELECT 
                fl.id, 
                fl.name, 
                fl.join_code, 
                (SELECT COUNT(*) FROM fantasy_league_members WHERE league_id = fl.id) as member_count,
                (
                    SELECT GROUP_CONCAT(ft_inner.name ORDER BY ft_inner.total_points DESC SEPARATOR ", ")
                    FROM fantasy_league_members flm_inner
                    JOIN fantasy_teams ft_inner ON flm_inner.fantasy_team_id = ft_inner.id
                    WHERE flm_inner.league_id = fl.id
                    LIMIT 3
                ) as top_teams
            FROM fantasy_leagues fl
            JOIN fantasy_league_members flm ON fl.id = flm.league_id
            WHERE flm.fantasy_team_id = :fantasy_team_id
            ORDER BY fl.name ASC
        ');
        $this->db->bind(':fantasy_team_id', $fantasy_team_id);
        return $this->db->resultSet();
    }

    // Get the full standings for a league
    public function getLeagueStandings($league_id) {
        $this->db->query('
            SELECT
                flm.fantasy_team_id,
                ft.name as team_name,
                ft.gameweek_points,
                ft.total_points,
                CONCAT(u.first_name, " ", u.last_name) as manager_name,
                u.profile_picture
            FROM fantasy_league_members flm
            JOIN fantasy_teams ft ON flm.fantasy_team_id = ft.id
            JOIN users u ON ft.user_id = u.id
            WHERE flm.league_id = :league_id
            ORDER BY ft.total_points DESC, ft.gameweek_points DESC
        ');
        $this->db->bind(':league_id', $league_id);
        return $this->db->resultSet();
    }

    /**
     * Gets the rank of a user in a specific league.
     * @param int $league_id
     * @param int $fantasy_team_id
     * @return int The rank (1-based).
     */
    public function getUserRank($league_id, $fantasy_team_id) {
        $this->db->query('SELECT total_points FROM fantasy_teams WHERE id = :id');
        $this->db->bind(':id', $fantasy_team_id);
        $team = $this->db->single();
        
        if (!$team) return 0;
        
        $this->db->query('
            SELECT COUNT(*) as rank 
            FROM fantasy_league_members flm
            JOIN fantasy_teams ft ON flm.fantasy_team_id = ft.id
            WHERE flm.league_id = :league_id AND ft.total_points > :points
        ');
        $this->db->bind(':league_id', $league_id);
        $this->db->bind(':points', $team->total_points);
        $result = $this->db->single();
        
        return $result->rank + 1;
    }
}