<?php

class Team {
    private $db;

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

    // Create a new team for a club
    public function create($data){
        $this->db->query('INSERT INTO teams (name, club_id, manager_id) VALUES (:name, :club_id, :manager_id)');
        
        // Bind values
        $this->db->bind(':name', $data['name']);
        $this->db->bind(':club_id', $data['club_id']);
        $this->db->bind(':manager_id', !empty($data['manager_id']) ? $data['manager_id'] : null);

        // Execute and return new team ID on success
        if($this->db->execute()){
            return $this->db->lastInsertId();
        } else {
            return false;
        }
    }

    // Get all teams for a specific club (public)
    public function getAllByClub($club_id){
        $this->db->query('
            SELECT t.id, t.name, t.club_id, t.manager_id, CONCAT(u.first_name, " ", u.last_name) as manager_name
            FROM teams t
            LEFT JOIN users u ON t.manager_id = u.id
            WHERE t.club_id = :club_id ORDER BY t.name ASC
        ');
        $this->db->bind(':club_id', $club_id);
        return $this->db->resultSet();
    }

    // Get all teams for a specific league (public)
    public function getAllByLeague($league_id){
        $this->db->query('
            SELECT t.id, t.name, c.name as club_name, c.logo as club_logo
            FROM teams t
            JOIN clubs c ON t.club_id = c.id
            WHERE c.league_id = :league_id
        ');
        $this->db->bind(':league_id', $league_id);
        return $this->db->resultSet();
    }

    // Get all teams with club and manager details for admin panel
    public function getAllWithDetails() {
        $this->db->query('
            SELECT 
                t.id, t.name,
                c.name as club_name, 
                CONCAT(u.first_name, " ", u.last_name) as manager_name
            FROM teams t
            JOIN clubs c ON t.club_id = c.id
            LEFT JOIN users u ON t.manager_id = u.id
            ORDER BY c.name, t.name ASC
        ');
        return $this->db->resultSet();
    }

    // Find team by name within a club to prevent duplicates
    public function findByNameAndClub($name, $club_id){
        $this->db->query('SELECT id FROM teams WHERE name = :name AND club_id = :club_id');
        $this->db->bind(':name', $name);
        $this->db->bind(':club_id', $club_id);
        $this->db->single();

        return $this->db->rowCount() > 0;
    }

    // Get team details by manager ID
    public function getTeamByManagerId($manager_id) {
        $this->db->query('
            SELECT t.id, t.name, t.club_id, t.roster_status, c.name as club_name, c.logo as club_logo, c.league_id
            FROM teams t
            JOIN clubs c ON t.club_id = c.id
            WHERE t.manager_id = :manager_id
        ');
        $this->db->bind(':manager_id', $manager_id);
        return $this->db->single();
    }

    // Find team by ID
    public function findById($id){
        $this->db->query('SELECT * FROM teams WHERE id = :id');
        $this->db->bind(':id', $id);
        $row = $this->db->single();

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

    // Assign a manager to a team
    public function assignManager($team_id, $manager_id){
        $this->db->query('UPDATE teams SET manager_id = :manager_id WHERE id = :id');
        $this->db->bind(':manager_id', $manager_id);
        $this->db->bind(':id', $team_id);

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

    // Update an existing team
    public function update($data) {
        $this->db->query('UPDATE teams SET name = :name, club_id = :club_id, manager_id = :manager_id WHERE id = :id');
        
        // Bind values
        $this->db->bind(':id', $data['id']);
        $this->db->bind(':name', $data['name']);
        $this->db->bind(':club_id', $data['club_id']);
        $this->db->bind(':manager_id', $data['manager_id']);

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

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

    // --- Roster Management ---

    public function getTeamsWithPendingRosters(int $league_id): array
    {
        $this->db->query("
            SELECT t.id, t.name as team_name, c.name as club_name, u.username as manager_name
            FROM teams t
            JOIN clubs c ON t.club_id = c.id
            LEFT JOIN users u ON t.manager_id = u.id
            WHERE c.league_id = :league_id AND t.roster_status = 'pending_approval'
        ");
        $this->db->bind(':league_id', $league_id);
        return $this->db->resultSet();
    }

    public function updateRosterStatus(int $team_id, string $status): bool
    {
        // Ensure status is a valid enum value to prevent SQL injection
        if (!in_array($status, ['draft', 'pending_approval', 'approved'])) return false;
        $this->db->query("UPDATE teams SET roster_status = :status WHERE id = :id");
        $this->db->bind(':status', $status);
        $this->db->bind(':id', $team_id);
        return $this->db->execute();
    }

    public function searchTeams($query) {
        $this->db->query("
            SELECT t.id, t.name, c.name as club_name, c.logo as club_logo
            FROM teams t
            JOIN clubs c ON t.club_id = c.id
            WHERE t.name LIKE :query1 OR c.name LIKE :query2
            LIMIT 20
        ");
        $this->db->bind(':query1', '%' . $query . '%');
        $this->db->bind(':query2', '%' . $query . '%');
        return $this->db->resultSet();
    }

    /**
     * Check if a club's registration fees are up to date based on games played.
     * 
     * @param int $club_id
     * @return bool True if active/paid, False if suspended/overdue
     */
    public function checkRegistrationStatus($club_id) {
        // Fetch League Settings for this Club
        try {
            $this->db->query("SELECT l.reg_fee_total, l.reg_deposit_pct, l.reg_inst2_pct, l.reg_inst2_games, l.reg_inst3_pct, l.reg_inst3_games 
                              FROM clubs c JOIN leagues l ON c.league_id = l.id WHERE c.id = :club_id");
            $this->db->bind(':club_id', $club_id);
            $league_settings = $this->db->single();
        } catch (Exception $e) {
            // Fallback if columns are missing (migration not run)
            return true;
        }
        
        $reg_fee_total = (float)($league_settings->reg_fee_total ?? 0);
        
        // If no fee is configured, everyone is active
        if ($reg_fee_total <= 0) {
            return true;
        }

        // 1. Get Club Wallet
        $this->db->query("SELECT id FROM wallets WHERE entity_type = 'club' AND entity_id = :club_id");
        $this->db->bind(':club_id', $club_id);
        $wallet = $this->db->single();
        
        if (!$wallet) {
            return false; // No wallet means no payments made
        }

        // 2. Get Total Paid by Club for Registration
        $this->db->query("SELECT SUM(amount) as total FROM wallet_transactions WHERE wallet_id = :wid AND type = 'registration_fee' AND (status = 'success' OR status = 'completed')");
        $this->db->bind(':wid', $wallet->id);
        $res = $this->db->single();
        $paid = (float)($res->total ?? 0);

        // 3. Get Games Played by Club (Max games played by any team in the club)
        $this->db->query("SELECT MAX(played_count) as max_played FROM (
                        SELECT COUNT(*) as played_count 
                        FROM fixtures f 
                        JOIN teams t ON (f.home_team_id = t.id OR f.away_team_id = t.id)
                        WHERE t.club_id = :club_id AND f.status = 'finished'
                        GROUP BY t.id
                    ) as counts");
        $this->db->bind(':club_id', $club_id);
        $games_res = $this->db->single();
        $games_played = (int)($games_res->max_played ?? 0);

        // 4. Calculate Due Amount based on Milestones
        $deposit_pct = (float)($league_settings->reg_deposit_pct ?? 50);
        $inst2_pct = (float)($league_settings->reg_inst2_pct ?? 30);
        $inst2_games = (int)($league_settings->reg_inst2_games ?? 5);
        $inst3_pct = (float)($league_settings->reg_inst3_pct ?? 20);
        $inst3_games = (int)($league_settings->reg_inst3_games ?? 10);

        $required_pct = $deposit_pct;
        if ($games_played >= $inst2_games) $required_pct += $inst2_pct;
        if ($games_played >= $inst3_games) $required_pct += $inst3_pct;

        $amount_required = $reg_fee_total * ($required_pct / 100);

        // Return true if paid amount covers the required amount (with small float tolerance)
        return $paid >= ($amount_required - 0.1);
    }
}
?>