Instead of updating the spreadsheet every LOOP_INTERVAL_MINUTES, make it update every 11:10 PM EST

import asyncio
import aiohttp
from aiolimiter import AsyncLimiter
import time
from bisect import bisect_left
from tqdm.asyncio import tqdm
import backoff
import logging
from google.oauth2 import service_account
from googleapiclient.discovery import build
import discord
from discord import app_commands
from discord.ext import commands
from discord.ext import tasks
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import numpy as np
from config import RATE_LIMIT, RATE_PERIOD

# Import all configuration variables
from config import *

# --- Logging Setup ---
logging.basicConfig(level=logging.INFO, format='%(asctime)s %(levelname)s %(message)s')
logger = logging.getLogger(__name__)

# --- Discord Bot Setup ---
intents = discord.Intents.default()
intents.message_content = True
bot = commands.Bot(command_prefix='!', intents=intents)

# --- Google Sheets Setup ---
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name(SERVICE_ACCOUNT_FILE, scope)
client = gspread.authorize(creds)
sheet = client.open('Global Rankings - Polytrack').worksheet('0.4')

# --- Cooldown Implementation for Application Commands ---
_command_cooldowns = {}

# --- Global Variables ---
player_times = {}
sorted_track_times = {}
last_updated_time = None  # To track when the data was last updated

# --- Helper Functions ---

def format_average_rank(rank):
    if rank < 10:
        return f"{rank:.2f}"
    elif rank < 100:
        return f"{rank:.1f}"
    else:
        return f"{rank:.0f}"

# Create a limiter that allows 20 requests per second
limiter = AsyncLimiter(RATE_LIMIT, RATE_PERIOD)  # 20 requests per 1 second

@backoff.on_exception(backoff.expo,
                      (aiohttp.ClientError, asyncio.TimeoutError),
                      max_tries=RETRIES,
                      jitter=backoff.full_jitter,
                      max_time=60)  # Set a maximum backoff time of 60 seconds
async def fetch_data(session, url_template, skip, track_index):
    async with limiter:  # Use the limiter here
        url = url_template.format(skip=skip)
        try:
            async with session.get(url, timeout=30) as response:
                if response.status == 200:
                    data = await response.json()
                    return data.get("entries", [])
                elif response.status == 503:
                    logger.warning(f"Server overloaded (503). Retrying URL: {url}")
                    raise aiohttp.ClientError
                else:
                    logger.error(f"Error fetching data from {url}. Status code: {response.status}. Retrying...")
                    raise aiohttp.ClientError
        except (aiohttp.ClientError, asyncio.TimeoutError) as e:
            logger.error(f"Exception for URL {url}: {e}")
            raise

async def process_track(session, url_template, track_index, pbar):
    skip = 0
    max_entries = MAX_SKIP

    while skip < max_entries:
        try:
            entries = await fetch_data(session, url_template, skip, track_index)
            if entries:
                for entry in entries:
                    player_name = entry["name"]
                    frames = entry["frames"]
                    player_times.setdefault(player_name, [None] * len(TRACK_URLS))
                    if player_times[player_name][track_index] is None or frames < player_times[player_name][track_index]:
                        player_times[player_name][track_index] = frames
                sorted_track_times[url_template] = sorted([t for t in player_times.values() if t[track_index] is not None],
                                                          key=lambda x: x[track_index])
                skip += len(entries)
            else:
                logger.info(f"No more entries found for track {track_index} at skip {skip}.")
                break  # Exit only if no more entries are found
        except aiohttp.ClientError as e:
            logger.error(f"Failed to fetch data for {url_template.format(skip=skip)} after retries. Error: {e}.")
            # DO NOT BREAK HERE. Let backoff retry.
        finally:  # Ensure pbar update even on error
            pbar.update(len(entries) if entries else 0)  # Update with 0 if entries is None due to an error

def clear_google_sheet_columns():
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

    credentials = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=SCOPES)

    service = build('sheets', 'v4', credentials=credentials)

    RANGE_NAME = '0.4!A1:Z'

    body = {}

    result = service.spreadsheets().values().clear(
        spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME, body=body).execute()

    print(f"{result.get('clearedRange')} range cleared.")

    result = service.spreadsheets().values().update(
        spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME,
        valueInputOption='USER_ENTERED', body=body).execute()

    print(f"{result.get('updatedCells')} cells updated.")

def update_google_sheet(data):
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

    credentials = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=SCOPES)

    service = build('sheets', 'v4', credentials=credentials)

    RANGE_NAME = '0.4!A1'

    clear_google_sheet_columns()

    body = {
        'values': data
    }

    result = service.spreadsheets().values().update(
        spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME,
        valueInputOption='USER_ENTERED', body=body).execute()

    print(f"{result.get('updatedCells')} cells updated.")

# --- Discord Bot Commands ---

@bot.event
async def on_ready():
    print(f'{bot.user} has connected to Discord!')
    update_spreadsheet_task.start()  # Start the background task
    await bot.tree.sync()

async def check_cooldown(interaction: discord.Interaction, command_name: str) -> bool:
    current_time = time.time()
    if command_name == "rankings":  # bypass cooldown for "rankings" command
        return True
    cooldown_duration = RELEASE_COOLDOWN_TIME if command_name == "updaterelease" else COOLDOWN_TIME

    if command_name in _command_cooldowns and current_time < _command_cooldowns[command_name]:
        remaining_time = _command_cooldowns[command_name] - current_time
        await interaction.response.send_message(
            f"This command is on cooldown. Try again in {remaining_time:.2f} seconds.",
            ephemeral=True,
        )
        return False
    else:
        _command_cooldowns[command_name] = current_time + cooldown_duration
        return True

@bot.tree.command(name="player", description="Get stats for a player")
@app_commands.describe(player_name="The name of the player to look up")
async def player(interaction: discord.Interaction, player_name: str):
    if await check_cooldown(interaction, "player"):
        await interaction.response.defer()
        try:
            # Removed the API calls; now only fetches data from the spreadsheet
            cells = sheet.findall(player_name, in_column=3, case_sensitive=True)  # Assuming Player Name is in column 3

            if cells:
                cell = cells[0]
                row_number = cell.row
                stats = sheet.row_values(row_number)

                headers = sheet.row_values(1)

                embed = discord.Embed(title=f"Stats for {player_name}")

                embed.add_field(name=headers[0], value=stats[0], inline=True)  # Rank

                # Add the rest of the fields, starting from index 1
                for i, (header, stat) in enumerate(zip(headers[1:], stats[1:])):
                    embed.add_field(name=header, value=stat, inline=True)

                # Compute data updated message
                if last_updated_time is not None:
                    delta_seconds = time.time() - last_updated_time
                    delta_minutes = delta_seconds / 60
                    data_updated_message = f"Data last updated {delta_minutes:.1f} minutes ago."
                else:
                    data_updated_message = "Data update time is not available."

                embed.set_footer(text=data_updated_message)

                await interaction.followup.send(embed=embed)
            else:
                await interaction.followup.send(
                    f"{player_name} not found. Please complete every track!")
        except Exception as e:
            await interaction.followup.send(f"An error occurred: {e}")

@bot.tree.command(name="rankings", description="Get the link to the global rankings")
async def rankings(interaction: discord.Interaction):
    if await check_cooldown(interaction, "rankings"):
        await interaction.response.send_message(
            f"<https://docs.google.com/spreadsheets/d/1szNRW_nla-XuGTwgIdGHdmuyd2HhTQ8NqWoqn6l9uGw/edit?usp=sharing>")

@bot.tree.command(name="wr", description="Shows the world record for each track and players with the most WRs.")
async def world_records(interaction: discord.Interaction):
    if await check_cooldown(interaction, "wr"):
        await interaction.response.defer()
        embed = discord.Embed(title="Polytrack World Records", color=discord.Color.blue())
        wr_holders = {}  # Dictionary to store players and their WR count

        async with aiohttp.ClientSession() as session:
            for i, url in enumerate(TRACK_URLS[:13]):
                try:
                    async with session.get(url.format(skip=0), timeout=30) as response:
                        if response.status == 200:
                            data = await response.json()
                            entries = data.get("entries", [])
                            if entries:
                                world_record_frames = entries[0]["frames"]
                                world_record_seconds = world_record_frames / 1000
                                world_record_holder = entries[0]["name"]
                                embed.add_field(name=TRACK_NAMES[i],
                                                value=f"**{world_record_seconds:.3f}s** by **{world_record_holder}**",
                                                inline=False)

                                # Update WR count for the player
                                if world_record_holder in wr_holders:
                                    wr_holders[world_record_holder] += 1
                                else:
                                    wr_holders[world_record_holder] = 1
                        else:
                            embed.add_field(name=TRACK_NAMES[i],
                                            value=f"Error fetching data. Status code: {response.status}",
                                            inline=False)
                except Exception as e:
                    embed.add_field(name=TRACK_NAMES[i],
                                    value=f"An error occurred: {e}",
                                    inline=False)

        # Sort players by WR count in descending order
        sorted_wr_holders = sorted(wr_holders.items(), key=lambda item: item[1], reverse=True)

        # Add WR holders and their count to the embed without the header
        embed.add_field(name="-" * 20, value="", inline=False)  # Add a separator line without space
        for player, count in sorted_wr_holders:
            embed.add_field(name=player, value=f"{count} WRs", inline=False)

        await interaction.followup.send(embed=embed)

@bot.tree.command(name="improvement", description="Get a player's improvement potential for each track.")
@app_commands.describe(player_name="The name of the player to analyze.")
async def improvement(interaction: discord.Interaction, player_name: str):
    if await check_cooldown(interaction, "improvement"):
        await interaction.response.defer()
        try:
            # Fetch data for the player from the spreadsheet
            cells = sheet.findall(player_name, in_column=3, case_sensitive=True)  # Assuming Player Name is in column 3

            if not cells:
                await interaction.followup.send(f"Player '{player_name}' not found in the rankings.")
                return

            cell = cells[0]
            row_number = cell.row
            data_row = sheet.row_values(row_number)
            headers = sheet.row_values(1)

            # Extract player's track times
            player_track_times = []

            for i in range(len(TRACK_NAMES)):
                # Assuming that track times are in columns after a fixed index
                # You may need to adjust this index based on your spreadsheet structure
                player_time_str = data_row[7 + i]  # Replace 7 with the actual index where track times start
                if "(#" in player_time_str:
                    player_time = float(player_time_str.split('s')[0])
                else:
                    player_time = None
                player_track_times.append(player_time)

            # Now fetch WR times for each track
            wr_track_times = []

            async with aiohttp.ClientSession() as session:
                for i, url in enumerate(TRACK_URLS):
                    try:
                        async with session.get(url.format(skip=0), timeout=30) as response:
                            if response.status == 200:
                                data = await response.json()
                                entries = data.get("entries", [])
                                if entries:
                                    world_record_frames = entries[0]["frames"]
                                    world_record_seconds = world_record_frames / 1000
                                    wr_track_times.append(world_record_seconds)
                                else:
                                    wr_track_times.append(None)
                            else:
                                wr_track_times.append(None)
                    except Exception as e:
                        wr_track_times.append(None)

            # Proceed to calculate improvement potentials as before
            table_data = []
            total_pb_seconds = 0
            total_wr_seconds = 0
            for i in range(len(TRACK_NAMES)):
                pb_seconds = player_track_times[i]
                wr_seconds = wr_track_times[i]

                if pb_seconds is None or wr_seconds is None:
                    table_data.append(
                        [f"T{i+1}", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A"])
                    continue

                total_pb_seconds += pb_seconds
                total_wr_seconds += wr_seconds

                # Calculate metrics with significant figures
                difference = pb_seconds - wr_seconds
                sig_figs = 3  # Example: using 3 significant figures

                # Function to format a number with significant figures
                def format_sigfigs(value, sig_figs):
                    if value == 0:
                        return "0"
                    return f"{float(f'{value:.{sig_figs}g}')}"

                difference_str = format_sigfigs(difference, sig_figs)
                inverse_performance_ratio_str = format_sigfigs(wr_seconds / pb_seconds if pb_seconds else float("inf"),
                                                               sig_figs)
                relative_difference_str = format_sigfigs(difference / wr_seconds if wr_seconds else float("inf"),
                                                         sig_figs)
                logarithmic_difference_str = format_sigfigs(
                    np.log(pb_seconds / wr_seconds) if wr_seconds and pb_seconds > 0 else float("inf"), sig_figs)
                harmonic_mean_str = format_sigfigs(
                    2 * (pb_seconds * wr_seconds) / (pb_seconds + wr_seconds) if pb_seconds + wr_seconds else float(
                        "inf"), sig_figs)
                normalized_difference_str = format_sigfigs(
                    difference / (pb_seconds + wr_seconds) if pb_seconds + wr_seconds else float("inf"), sig_figs)
                exponential_difference_str = format_sigfigs(
                    np.exp(difference / wr_seconds) if wr_seconds else float("inf"), sig_figs)
                inverse_relative_difference_str = format_sigfigs(
                    wr_seconds / difference if difference else float("inf"), sig_figs)
                quadratic_difference_str = format_sigfigs(
                    (difference / wr_seconds) ** 2 if wr_seconds else float("inf"), sig_figs)

                table_data.append([
                    f"T{i+1}",  # Abbreviated Track column
                    f"{pb_seconds:.3f}",
                    f"{wr_seconds:.3f}",
                    difference_str,
                    inverse_performance_ratio_str,
                    relative_difference_str,
                    logarithmic_difference_str,
                    harmonic_mean_str,
                    normalized_difference_str,
                    exponential_difference_str,
                    inverse_relative_difference_str,
                    quadratic_difference_str
                ])

            # Total Row Calculations (using the same significant figures)
            total_difference = total_pb_seconds - total_wr_seconds
            total_difference_str = format_sigfigs(total_pb_seconds - total_wr_seconds, sig_figs)
            total_inverse_performance_ratio_str = format_sigfigs(
                total_wr_seconds / total_pb_seconds if total_pb_seconds else float("inf"), sig_figs)
            total_relative_difference_str = format_sigfigs(
                total_difference / total_wr_seconds if total_wr_seconds else float("inf"), sig_figs)
            total_logarithmic_difference_str = format_sigfigs(
                np.log(total_pb_seconds / total_wr_seconds) if total_wr_seconds and total_pb_seconds > 0 else float(
                    "inf"), sig_figs)
            total_harmonic_mean_str = format_sigfigs(2 * (total_pb_seconds * total_wr_seconds) / (
                    total_pb_seconds + total_wr_seconds) if total_pb_seconds + total_wr_seconds else float("inf"),
                                                     sig_figs)
            total_normalized_difference_str = format_sigfigs(total_difference / (
                    total_pb_seconds + total_wr_seconds) if total_pb_seconds + total_wr_seconds else float("inf"),
                                                             sig_figs)
            total_exponential_difference_str = format_sigfigs(
                np.exp(total_difference / total_wr_seconds) if total_wr_seconds else float("inf"), sig_figs)
            total_inverse_relative_difference_str = format_sigfigs(
                total_wr_seconds / total_difference if total_difference else float("inf"), sig_figs)
            total_quadratic_difference_str = format_sigfigs(
                (total_difference / total_wr_seconds) ** 2 if total_wr_seconds else float("inf"), sig_figs)

            table_data.append([
                "All",
                f"{total_pb_seconds:.3f}",
                f"{total_wr_seconds:.3f}",
                total_difference_str,
                total_inverse_performance_ratio_str,
                total_relative_difference_str,
                total_logarithmic_difference_str,
                total_harmonic_mean_str,
                total_normalized_difference_str,
                total_exponential_difference_str,
                total_inverse_relative_difference_str,
                total_quadratic_difference_str
            ])

            # Compact column widths further
            table_str = "```\n"
            table_str += f"{'T':<3} {'PB':<7} {'WR':<7} {'Diff':<5} {'IPR':<5} {'RelD':<8} {'LogD':<8} {'Harm':<5} {'NormD':<8} {'ExpD':<6} {'InvRD':<7} {'QuadD':<6}\n"  # Shortened column names
            table_str += "-" * 94 + "\n"  # Adjusted separator length
            for row in table_data:
                table_str += f"{row[0]:<3} {row[1]:<7} {row[2]:<7} {row[3]:<5} {row[4]:<5} {row[5]:<8} {row[6]:<8} {row[7]:<5} {row[8]:<8} {row[9]:<6} {row[10]:<7} {row[11]:<6}\n"
            table_str += "```\n"

            # Compute data updated message
            if last_updated_time is not None:
                delta_seconds = time.time() - last_updated_time
                delta_minutes = delta_seconds / 60
                data_updated_message = f"Data last updated {delta_minutes:.1f} minutes ago."
            else:
                data_updated_message = "Data update time is not available."

            await interaction.followup.send(f"Improvement potential for **{player_name}**:\n{table_str}\n{data_updated_message}")

        except Exception as e:
            await interaction.followup.send(f"An error occurred: {e}")

# --- Metric Explanation Commands ---

def create_metric_explanation_command(metric_name, explanation):
    """Creates a slash command function for a metric explanation."""

    @bot.tree.command(name=metric_name.lower(), description=f"Explanation of the {metric_name} metric.")
    async def metric_explanation_command(interaction: discord.Interaction):
        if await check_cooldown(interaction, metric_name.lower()):
            embed = discord.Embed(title=f"{metric_name} Metric Explanation", color=discord.Color.blue())
            embed.add_field(name="Explanation", value=explanation, inline=False)
            await interaction.response.send_message(embed=embed)

    return metric_explanation_command

# Create a command for each metric explanation from config.py
for metric_name, explanation in METRIC_EXPLANATIONS.items():
    create_metric_explanation_command(metric_name, explanation)

@bot.tree.command(name="help", description="Show available commands and their usage.")
async def help_command(interaction: discord.Interaction):
    if await check_cooldown(interaction, "help"):
        embed = discord.Embed(title="Available Commands", color=discord.Color.blue())
        embed.add_field(name="/player <player_name>", value="Get stats for a player.", inline=False)
        embed.add_field(name="/rankings", value="Get the link to the global rankings.", inline=False)
        embed.add_field(name="/wr", value="Shows the world record for each track.", inline=False)
        embed.add_field(name="/improvement <player_name>",
                        value="Shows the improvement potential for each track for the given player.", inline=False)
        embed.add_field(name="/help", value="Show this help message.", inline=False)

        # Add a general note about metric explanation commands
        embed.add_field(name="Metric Explanations",
                        value="For detailed explanations of each metric, use commands like `/diff`, `/ipr`, `/reldiff`, etc.",
                        inline=False)

        await interaction.response.send_message(embed=embed)

# --- Main Ranking Update Function ---
async def update_rankings_function():
    global last_updated_time  # Declare as global to modify the global variable
    start_time = time.time()

    connector = aiohttp.TCPConnector(limit_per_host=13)
    async with aiohttp.ClientSession(connector=connector) as session:
        with tqdm(total=MAX_SKIP * len(TRACK_URLS), desc="Processing tracks") as pbar:
            tasks = [process_track(session, url_template, i, pbar) for i, url_template in enumerate(TRACK_URLS)]
            await asyncio.gather(*tasks)

    player_data = {}
    for player, times in player_times.items():
        if all(t is not None for t in times):
            total_time = sum(times)
            ranks = [bisect_left(sorted_track_times[TRACK_URLS[i]], times[i], key=lambda x: x[i]) + 1 for i in
                     range(len(TRACK_URLS))]
            average_rank = sum(ranks) / len(ranks)
            best_rank = min(ranks)
            worst_rank = max(ranks)

            player_data[player] = {
                "total_time": total_time,
                "average_rank": average_rank,
                "best_rank": best_rank,
                "worst_rank": worst_rank,
                "track_times": times,
                "ranks": ranks
            }

    sorted_players = sorted(player_data.items(), key=lambda x: x[1]["total_time"])

    sheet_data = []

    header = ["Rank", "Percentile", "Player Name", "Total Time", "Average Rank", "Best Rank", "Worst Rank"]
    header.extend(TRACK_NAMES)
    sheet_data.append(header)

    for i, (player, data) in enumerate(sorted_players, start=1):
        overall_percentile = (len(sorted_players) - i + 1) / len(sorted_players) * 100

        total_seconds = data["total_time"] / 1000
        hours, remainder = divmod(total_seconds, 3600)
        minutes, seconds = divmod(remainder, 60)
        milliseconds = int((seconds % 1) * 1000)
        seconds = int(seconds)
        minutes = int(minutes)
        hours = int(hours)

        if hours > 0:
            formatted_total_time = f"{hours}:{minutes:02d}:{seconds:02d}.{milliseconds:03d}"
        elif minutes > 0:
            formatted_total_time = f"{minutes}:{seconds:02d}.{milliseconds:03d}"
        else:
            formatted_total_time = f"{seconds}.{milliseconds:03d}"

        formatted_average_rank = format_average_rank(data['average_rank'])

        row = [i, f"{overall_percentile:.2f}", f"'{player}", formatted_total_time, formatted_average_rank,
               data["best_rank"], data["worst_rank"]]
        for j in range(len(TRACK_URLS)):
            track_time = data["track_times"][j]
            rank = data["ranks"][j]
            formatted_track_time = f"{track_time / 1000:.3f}"
            row.append(f"{formatted_track_time}s (#{rank})")
        sheet_data.append(row)

    update_google_sheet(sheet_data)

    end_time = time.time()
    total_time = end_time - start_time
    hours, remainder = divmod(total_time, 3600)
    minutes, seconds = divmod(remainder, 60)
    milliseconds = int((seconds % 1) * 1000)
    seconds = int(seconds)
    minutes = int(minutes)
    hours = int(hours)

    if hours > 0:
        formatted_time = f"{hours}:{minutes:02d}:{seconds:02d}.{milliseconds:03d}"
    elif minutes > 0:
        formatted_time = f"{minutes}:{seconds:02d}.{milliseconds:03d}"
    else:
        formatted_time = f"{seconds}.{milliseconds:03d}"

    print(f"Data updated in Google Sheets")
    print(f"Total execution time: {formatted_time}")

    # Update the last_updated_time
    last_updated_time = time.time()

# --- Background Task to Update Spreadsheet ---
@tasks.loop(minutes=LOOP_INTERVAL_MINUTES)
async def update_spreadsheet_task():
    try:
        await update_rankings_function()
    except Exception as e:
        print(f"Error updating spreadsheet: {e}")

# --- Run the Discord Bot ---
bot.run(DISCORD_BOT_TOKEN)