<?php

class FantasySquad extends Model
{
    protected $table = 'fantasy_squads';

    /**
     * Saves a fantasy squad for a specific fantasy team.
     * This is a transactional operation that first deletes any existing squad.
     * @param int $fantasy_team_id The ID of the fantasy team.
     * @param array $starters An array of player IDs for the starters.
     * @param array $substitutes An array of player IDs for the bench.
     * @param string $formation The formation string.
     * @param int $gameweek The current gameweek.
     * @return bool True on success, false on failure.
     */
    public function setSquad(int $fantasy_team_id, array $starters, array $substitutes, string $formation, int $gameweek): bool
    {
        $this->db->beginTransaction();
        try {
            // 1. Clear existing squad for this team and gameweek
            $this->db->query('DELETE FROM ' . $this->table . ' WHERE fantasy_team_id = :fantasy_team_id');
            $this->db->bind(':fantasy_team_id', $fantasy_team_id);
            $this->db->execute();

            // 2. Insert starters
            $this->db->query('INSERT INTO ' . $this->table . ' (fantasy_team_id, player_id, gameweek_added, is_starter, formation) VALUES (:fantasy_team_id, :player_id, :gameweek, 1, :formation)');
            foreach ($starters as $player_id) {
                $this->db->bind(':fantasy_team_id', $fantasy_team_id);
                $this->db->bind(':player_id', $player_id);
                $this->db->bind(':gameweek', $gameweek);
                $this->db->bind(':formation', $formation);
                $this->db->execute();
            }

            // 3. Insert substitutes
            $this->db->query('INSERT INTO ' . $this->table . ' (fantasy_team_id, player_id, gameweek_added, is_starter, bench_order, is_captain, is_vice_captain) VALUES (:fantasy_team_id, :player_id, :gameweek, 0, :bench_order, 0, 0)');
            $bench_order = 1;
            foreach ($substitutes as $player_id) {
                $this->db->bind(':fantasy_team_id', $fantasy_team_id);
                $this->db->bind(':player_id', $player_id);
                $this->db->bind(':gameweek', $gameweek);
                $this->db->bind(':bench_order', $bench_order++);
                $this->db->execute();
            }

            return $this->db->commit();
        } catch (Exception $e) {
            $this->db->rollBack();
            error_log("Failed to set fantasy squad: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Gets the current squad for a specific fantasy team.
     * @param int $fantasy_team_id The ID of the fantasy team.
     * @return array An array of player objects with squad details.
     */
    public function getSquadByTeamId(int $fantasy_team_id): array
    {
        $this->db->query('
            SELECT 
                fs.player_id,
                fs.is_starter,
                fs.is_captain,
                fs.is_vice_captain,
                fs.bench_order,
                fs.formation,
                u.first_name, 
                u.last_name, 
                u.profile_picture,
                p.position, 
                p.squad_number,
                p.team_id,
                p.fantasy_price,
                c.name as club_name,
                c.logo as club_logo
            FROM ' . $this->table . ' fs
            JOIN players p ON fs.player_id = p.user_id
            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 fs.fantasy_team_id = :fantasy_team_id
        ');
        $this->db->bind(':fantasy_team_id', $fantasy_team_id);
        return $this->db->resultSet();
    }

    /**
     * Updates the lineup (starters and bench order) for a fantasy team.
     * @param int $fantasy_team_id The ID of the fantasy team.
     * @deprecated Use updateLineupAndCaptains for transactional safety.
     * @param array $starter_ids An array of player IDs for the starters.
     * @param array $bench_ids An array of player IDs for the bench, in order.
     * @return bool True on success, false on failure.
     */
    public function updateLineup(int $fantasy_team_id, array $starter_ids, array $bench_ids): bool
    {
        $this->db->beginTransaction();
        try {
            // 1. Set all players to be on the bench initially
            $this->db->query('UPDATE ' . $this->table . ' SET is_starter = 0, bench_order = NULL WHERE fantasy_team_id = :fantasy_team_id');
            $this->db->bind(':fantasy_team_id', $fantasy_team_id);
            $this->db->execute();

            // 2. Set the starters
            if (!empty($starter_ids)) {
                $starter_placeholders = implode(',', array_fill(0, count($starter_ids), '?'));
                $this->db->query("UPDATE " . $this->table . " SET is_starter = 1 WHERE fantasy_team_id = ? AND player_id IN ($starter_placeholders)");
                $this->db->execute(array_merge([$fantasy_team_id], $starter_ids));
            }

            // 3. Set the bench order
            $bench_order = 1;
            foreach ($bench_ids as $player_id) {
                $this->db->query('UPDATE ' . $this->table . ' SET is_starter = 0, bench_order = :bench_order WHERE fantasy_team_id = :fantasy_team_id AND player_id = :player_id');
                $this->db->bind(':bench_order', $bench_order++);
                $this->db->bind(':fantasy_team_id', $fantasy_team_id);
                $this->db->bind(':player_id', $player_id);
                $this->db->execute();
            }

            return $this->db->commit();
        } catch (Exception $e) {
            $this->db->rollBack();
            error_log("Failed to update lineup: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Sets the captain and vice-captain for a fantasy team.
     * @param int $fantasy_team_id The ID of the fantasy team.
     * @deprecated Use updateLineupAndCaptains for transactional safety.
     * @param int $captain_id The player ID of the new captain.
     * @param int $vice_captain_id The player ID of the new vice-captain.
     * @return bool True on success, false on failure.
     */
    public function setCaptains(int $fantasy_team_id, int $captain_id, int $vice_captain_id): bool
    {
        $this->db->beginTransaction();
        try {
            // 1. Reset all captaincy statuses for the team
            $this->db->query('UPDATE ' . $this->table . ' SET is_captain = 0, is_vice_captain = 0 WHERE fantasy_team_id = :fantasy_team_id');
            $this->db->bind(':fantasy_team_id', $fantasy_team_id);
            $this->db->execute();

            // 2. Set the new captain
            $this->db->query('UPDATE ' . $this->table . ' SET is_captain = 1 WHERE fantasy_team_id = :fantasy_team_id AND player_id = :player_id');
            $this->db->bind(':fantasy_team_id', $fantasy_team_id);
            $this->db->bind(':player_id', $captain_id);
            $this->db->execute();

            // 3. Set the new vice-captain
            $this->db->query('UPDATE ' . $this->table . ' SET is_vice_captain = 1 WHERE fantasy_team_id = :fantasy_team_id AND player_id = :player_id');
            $this->db->bind(':fantasy_team_id', $fantasy_team_id);
            $this->db->bind(':player_id', $vice_captain_id);
            $this->db->execute();

            return $this->db->commit();
        } catch (Exception $e) {
            $this->db->rollBack();
            error_log("Failed to set captains: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Updates the entire lineup state (starters, bench, captains) in a single transaction.
     * @param int $fantasy_team_id The ID of the fantasy team.
     * @param array $starter_ids An array of player IDs for the starters.
     * @param array $bench_ids An array of player IDs for the bench, in order.
     * @param int $captain_id The player ID of the new captain.
     * @param int $vice_captain_id The player ID of the new vice-captain.
     * @return bool True on success, false on failure.
     */
    public function updateLineupAndCaptains(int $fantasy_team_id, array $starter_ids, array $bench_ids, int $captain_id, int $vice_captain_id): bool
    {
        $this->db->beginTransaction();
        try {
            // 1. Reset all players to be on the bench initially
            $this->db->query('UPDATE ' . $this->table . ' SET is_starter = 0, bench_order = NULL, is_captain = 0, is_vice_captain = 0 WHERE fantasy_team_id = :fantasy_team_id');
            $this->db->bind(':fantasy_team_id', $fantasy_team_id);
            $this->db->execute();

            // 2. Set the starters
            if (!empty($starter_ids)) {
                $starter_placeholders = implode(',', array_fill(0, count($starter_ids), '?'));
                $this->db->query("UPDATE " . $this->table . " SET is_starter = 1 WHERE fantasy_team_id = ? AND player_id IN ($starter_placeholders)");
                $this->db->execute(array_merge([$fantasy_team_id], $starter_ids));
            }

            // 3. Set the bench order
            $bench_order = 1;
            foreach ($bench_ids as $player_id) {
                $this->db->query('UPDATE ' . $this->table . ' SET is_starter = 0, bench_order = :bench_order WHERE fantasy_team_id = :fantasy_team_id AND player_id = :player_id');
                $this->db->bind(':bench_order', $bench_order++);
                $this->db->bind(':fantasy_team_id', $fantasy_team_id);
                $this->db->bind(':player_id', $player_id);
                $this->db->execute();
            }

            // 4. Set the new captain
            $this->db->query('UPDATE ' . $this->table . ' SET is_captain = 1 WHERE fantasy_team_id = :fantasy_team_id AND player_id = :player_id');
            $this->db->bind(':fantasy_team_id', $fantasy_team_id);
            $this->db->bind(':player_id', $captain_id);
            $this->db->execute();

            // 5. Set the new vice-captain
            $this->db->query('UPDATE ' . $this->table . ' SET is_vice_captain = 1 WHERE fantasy_team_id = :fantasy_team_id AND player_id = :player_id');
            $this->db->bind(':fantasy_team_id', $fantasy_team_id);
            $this->db->bind(':player_id', $vice_captain_id);
            $this->db->execute();

            return $this->db->commit();

        } catch (Exception $e) {
            $this->db->rollBack();
            error_log("Failed to update lineup and captains: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Swaps a player in the squad with a new player.
     * Preserves the squad status (starter/bench) of the player being removed.
     * @param int $fantasy_team_id
     * @param int $player_out_id
     * @param int $player_in_id
     * @param int $gameweek
     * @return bool
     */
    public function swapPlayers(int $fantasy_team_id, int $player_out_id, int $player_in_id, int $gameweek): bool
    {
        // 1. Get the status of the player leaving
        $this->db->query('SELECT is_starter, bench_order, formation FROM ' . $this->table . ' WHERE fantasy_team_id = :fantasy_team_id AND player_id = :player_id');
        $this->db->bind(':fantasy_team_id', $fantasy_team_id);
        $this->db->bind(':player_id', $player_out_id);
        $player_out_data = $this->db->single();

        if (!$player_out_data) {
            return false;
        }

        // 2. Delete the player leaving
        $this->db->query('DELETE FROM ' . $this->table . ' WHERE fantasy_team_id = :fantasy_team_id AND player_id = :player_id');
        $this->db->bind(':fantasy_team_id', $fantasy_team_id);
        $this->db->bind(':player_id', $player_out_id);
        $this->db->execute();

        // 3. Insert the new player
        $this->db->query('INSERT INTO ' . $this->table . ' (fantasy_team_id, player_id, gameweek_added, is_starter, bench_order, formation, is_captain, is_vice_captain) VALUES (:fantasy_team_id, :player_id, :gameweek, :is_starter, :bench_order, :formation, 0, 0)');
        $this->db->bind(':fantasy_team_id', $fantasy_team_id);
        $this->db->bind(':player_id', $player_in_id);
        $this->db->bind(':gameweek', $gameweek);
        $this->db->bind(':is_starter', $player_out_data->is_starter);
        $this->db->bind(':bench_order', $player_out_data->bench_order);
        $this->db->bind(':formation', $player_out_data->formation);
        
        return $this->db->execute();
    }

    /**
     * Deletes the entire squad for a fantasy team.
     * @param int $fantasy_team_id
     * @return bool
     */
    public function deleteSquad(int $fantasy_team_id): bool {
        $this->db->query('DELETE FROM ' . $this->table . ' WHERE fantasy_team_id = :fantasy_team_id');
        $this->db->bind(':fantasy_team_id', $fantasy_team_id);
        return $this->db->execute();
    }
}