import os
import datetime
import time

import db

import sqlite3

import discord
from discord.ext import commands

import gamelogic as gamlog

import re

from collections import Counter

from dotenv import load_dotenv

WORDLE_CHANNEL = 1317916234863480832

MISC_GEOGRAPHY = 1317916442342981722

GLOBLE_CHANNEL = 1320505660701413511

WHEN_WHERE_CHANNEL = 1320505906592612394

MISC_CHANNEL = 1317916469660618752

GENSHINDLE_CHANNEL = 1320505821112832010

CHANNELS = [
        WORDLE_CHANNEL,
        MISC_GEOGRAPHY,
        GLOBLE_CHANNEL,
        WHEN_WHERE_CHANNEL,
        MISC_CHANNEL,
        GENSHINDLE_CHANNEL
        ]

possible_games = [
            "Wordle",
            "Connections",
            "Satle",
            "Globle",
            "Airport Guessr",
            "WhereTaken",
            "WhenTaken",
            "Flagle",
            "Genshindle",
            "Planespottle",
            "Travle",
            "Costcodle",
            "Chrono"
        ]

chat_limit = 1000

load_dotenv()
TOKEN = os.getenv('DISCORD_TOKEN')

intents = discord.Intents.default()
intents.message_content = True

bot = commands.Bot(intents=intents)

db.create_table();

@bot.slash_command(
  name="stats",
  description="Prints out your stats in a daily game, examples include wordle",
  guild_ids=[261345598987436033]
)
async def stats(
    interaction: discord.Interaction,
    game : discord.Option(
        str,
        choices=possible_games),
    user: discord.User = None
): 
    await interaction.response.defer()

    if user is None:
        user = interaction.user

    conn = sqlite3.connect("stats.db")
    cursor = conn.cursor()

    if not game in possible_games:

        print("SQL Injection???")
        await interaction.followup.send(
            "Sorry, something fucky wucky happened",
            ephemeral=True
        )
        return

    sql = 'SELECT * FROM ' + game.upper().replace(" ", "_") + ' WHERE NAME = ?'
    cursor.execute(sql, (user.id,))
    data = cursor.fetchone()
    conn.close()

    if not data:
        await interaction.followup.send(
            "No %s results found." % (game),
            ephemeral=True
        )
        return

    match game.lower():
        case "wordle":
            total_games = data[1]
            wins = data[2]
            win_rate = data[3]
            total_guesses = data[4]
            average_guesses = data[5]

            await interaction.followup.send(
                f"Wordle stats for {user.mention}:\n"
                f"Total Games Played: {total_games}\n"
                f"Average Guesses per Winning Game: {average_guesses:.2f}\n"
                f"Win Rate: {win_rate:.2f}%\n",
                ephemeral=False  # Send message only to the user who called the command
            )
        case "connections":
            total_games = data[1]
            wins = data[2]
            perfects = data[3]
            win_rate = data[4]
            perfect_rate = data[5]
            total_guesses = data[6]
            average_guesses = data[7]

            await interaction.followup.send(
                f"Connections stats for {user.mention}:\n"
                f"Total Games Played: {total_games}\n"
                f"Average Guesses per Winning Game: {average_guesses:.2f}\n"
                f"Win Rate: {win_rate:.2f}%\n"
                f"Perfect Games: {perfects}\n",

                ephemeral=False  # Send message only to the user who called the command
            )
        case "satle":
            total_games = data[1]
            wins = data[2]
            win_rate = data[3]
            total_guesses = data[4]
            average_guesses = data[5]

            await interaction.followup.send(
                f"Satle stats for {user.mention}:\n"
                f"Total Games Played: {total_games}\n"
                f"Average Guesses per Winning Game: {average_guesses:.2f}\n"
                f"Win Rate: {win_rate:.2f}%\n",
                ephemeral=False  # Send message only to the user who called the command
            )
        case "globle":
            total_games = data[1]
            total_guesses = data[2]
            average_guesses = data[3]

            await interaction.followup.send(
                f"Globle stats for {user.mention}:\n"
                f"Total Games Played: {total_games}\n"
                f"Average Guesses per Game: {average_guesses:.2f}\n",
                ephemeral=False  # Send message only to the user who called the command
            )
        case "airport guessr":
            total_games = data[1]
            wins = data[2]
            win_rate = data[3]
            total_guesses = data[4]
            average_guesses = data[5]

            await interaction.followup.send(
                f"Airport Guessr stats for {user.mention}:\n"
                f"Total Games Played: {total_games}\n"
                f"Average Guesses per Winning Game: {average_guesses:.2f}\n"
                f"Win Rate: {win_rate:.2f}%\n",
                ephemeral=False  # Send message only to the user who called the command
            )
        case "whentaken":
            total_games = data[1]
            total_distance = data[2]
            average_distance = data[3]
            total_points = data[4]
            average_points = data[5]
            total_years = data[6]
            average_years = data[7]

            await interaction.followup.send(
                f"When Taken stats for {user.mention}:\n"
                f"Total Games Played: {total_games}\n"
                f"Average Distance: {average_distance:.2f} km\n"
                f"Average Time Distance: {average_years:.2f} years\n"
                f"Average Score: {average_points:.2f}\n",
                ephemeral=False  # Send message only to the user who called the command
            )
        case "wheretaken":
            total_games = data[1]
            total_distance = data[2]
            average_distance = data[3]
            total_points = data[4]
            average_points = data[5]

            await interaction.followup.send(
                f"Where Taken stats for {user.mention}:\n"
                f"Total Games Played: {total_games}\n"
                f"Average Distance: {average_distance:.2f} km\n"
                f"Average Score: {average_points:.2f}\n",
                ephemeral=False  # Send message only to the user who called the command
            )
        case "flagle":
            total_games = data[1]
            wins = data[2]
            win_rate = data[3]
            total_guesses = data[4]
            average_guesses = data[5]

            await interaction.followup.send(
                f"Flagle stats for {user.mention}:\n"
                f"Total Games Played: {total_games}\n"
                f"Average Guesses per Winning Game: {average_guesses:.2f}\n"
                f"Win Rate: {win_rate:.2f}%\n",
                ephemeral=False  # Send message only to the user who called the command
            )
        case "genshindle":
            total_games = data[1]
            wins = data[2]
            win_rate = data[3]
            total_guesses = data[4]
            average_guesses = data[5]

            await interaction.followup.send(
                f"Genshindle stats for {user.mention}:\n"
                f"Total Games Played: {total_games}\n"
                f"Average Guesses per Winning Game: {average_guesses:.2f}\n"
                f"Win Rate: {win_rate:.2f}%\n",
                ephemeral=False  # Send message only to the user who called the command
            )
        case "planespottle":
            total_games = data[1]
            wins = data[2]
            win_rate = data[3]
            total_guesses = data[4]
            average_guesses = data[5]

            await interaction.followup.send(
                f"Planespottle stats for {user.mention}:\n"
                f"Total Games Played: {total_games}\n"
                f"Average Guesses per Winning Game: {average_guesses:.2f}\n"
                f"Win Rate: {win_rate:.2f}%\n",
                ephemeral=False  # Send message only to the user who called the command
            )
        case "travle":
            total_games = data[1]
            wins = data[2]
            perfects = data[3]
            win_rate = data[4]
            perfect_rate = data[5]
            total_guesses = data[6]
            average_guesses = data[7]

            await interaction.followup.send(
                f"Travle stats for {user.mention}:\n"
                f"Total Games Played: {total_games}\n"
                f"Average Extra Guesses per Game: {average_guesses:.2f}\n"
                f"Win Rate: {win_rate:.2f}%\n"
                f"Perfect Games: {perfects}\n",

                ephemeral=False  # Send message only to the user who called the command
            )
        case "costcodle":
            total_games = data[1]
            wins = data[2]
            win_rate = data[3]
            total_guesses = data[4]
            average_guesses = data[5]

            await interaction.followup.send(
                f"Costcodle stats for {user.mention}:\n"
                f"Total Games Played: {total_games}\n"
                f"Average Guesses per Winning Game: {average_guesses:.2f}\n"
                f"Win Rate: {win_rate:.2f}%\n",
                ephemeral=False  # Send message only to the user who called the command
            )
        case "chrono":
            total_games = data[1]
            wins = data[2]
            win_rate = data[3]
            total_guesses = data[4]
            average_guesses = data[5]

            await interaction.followup.send(
                f"Chrono stats for {user.mention}:\n"
                f"Total Games Played: {total_games}\n"
                f"Average Guesses per Winning Game: {average_guesses:.2f}\n"
                f"Win Rate: {win_rate:.2f}%\n",
                ephemeral=False  # Send message only to the user who called the command
            )
        case _:
            await interaction.followup.send(
                "Not a game.",
                ephemeral=True
            )

table_dict = {
        "games" : "GAMES",
        "total wins" : "WINS",
        "win rate" : "WIN_RATE",
        "average guesses" : "GUESS_AVG",
        "perfects" : "PERFECTS",
        "perfect rate" : "PERFECT_RATE",
        "distance" : "DISTANCE_AVG",
        "score" : "POINTS_AVG",
        "time distance" : "YEARS_AVG"
        }

async def get_table_options(ctx : discord.AutocompleteContext):
    game = ctx.options['game']
    match game.lower():
        case "wordle":
            return ["games","total wins","win rate","average guesses"]
        case "connections":
            return ["games","total wins","win rate","average guesses","perfects","perfect_rate"]
        case "satle":
            return ["games","total wins","win rate","average guesses"]
        case "globle":
            return ["games","average guesses"]
        case "airport guessr":
            return ["games","total wins","win rate","average guesses"]
        case "whentaken":
            return ["games", "distance", "score", "time distance"]
        case "wheretaken":
            return ["games", "distance", "score"]
        case "flagle":
            return ["games","total wins","win rate","average guesses"]
        case "genshindle":
            return ["games","total wins","win rate","average guesses"]
        case "planespottle":
            return ["games","total wins","win rate","average guesses"]
        case "travle":
            return ["games","total wins","win rate","average guesses","perfects","perfect_rate"]
        case "costcodle":
            return ["games","total wins","win rate","average guesses"]
        case "chrono":
            return ["games","total wins","win rate","average guesses"]
        case _:
            return ["HOW DID YOU GET HERE? SCREW YOU FOR MESSING WITH MY PROGRAM"]


@bot.slash_command(
  name="top",
  description="Prints out the top players of the given game.",
  guild_ids=[261345598987436033],
)
async def top(
    interaction: discord.Interaction,
    game : discord.Option(
        str,
        choices=possible_games),
    sort_by : discord.Option(str, autocomplete=discord.utils.basic_autocomplete(get_table_options)),
    count : discord.Option(int) = 3
): 
    await interaction.response.defer()
    
    table_column = table_dict[sort_by]

    conn = sqlite3.connect("stats.db")
    cursor = conn.cursor()

    if not game in possible_games:

        print("SQL Injection???")
        await interaction.followup.send(
            "Sorry, something fucky wucky happened",
            ephemeral=True
        )
        return

    order = "DESC"
    if table_column in ["GUESS_AVG", "DISTANCE_AVG", "YEARS_AVG"]:
        order = "ASC"

    sql = 'SELECT NAME,' + table_column + ' FROM ' + game.upper().replace(" ", "_") + ' ORDER BY ' + table_column + '=0, ' + table_column + " " + order
    cursor.execute(sql)
    data = cursor.fetchmany(count)
    conn.close()

    if not data:
        await interaction.followup.send(
            "No %s results found." % (game),
            ephemeral=True
        )
        return

    return_message = "Top " + str(len(data)) + " Players for " + game + " by " + sort_by + "\n"

    for i, stat in enumerate(data):
        modifier = ''
        match table_column:
            case "WIN_RATE" | "PERFECT_RATE":
                modifier = "%"
            case "DISTANCE_AVG":
                modifier = " km"
            case "YEARS_AVG":
                modifier = " years"
            case "GAMES":
                modifier = " games"
            case "GUESS_AVG":
                modifier = " guesses"

        line = f"#{i+1} <@{stat[0]}> Stats: {stat[1]:.2f}{modifier}\n"
        
        return_message += line


    await interaction.followup.send(
        return_message,
        ephemeral=False  # Send message only to the user who called the command
    )

def process_message(message : discord.message, reverse : bool):
    channel = bot.get_channel(WORDLE_CHANNEL)

    PATTERNS = {
            gamlog.wordle : r"Wordle (\d{1,3}(?:,\d{3})*) (X|\d+)/(\d+)",
            gamlog.connections : r"Connections\nPuzzle #",
            gamlog.satle : r"🛰Satle #[0-9]+ (\d)/6",
            gamlog.bad_globle: r"🌎 [a-zA-Z]{3} [0-9]+, [0-9]{4} 🌍",
            gamlog.globle : r"I guessed today’s Globle in ([0-9]+) tries:",
            gamlog.airport_guessr : r"I (got the|did not guess the) airport (in \d (guess|guesses) )?today:",
            gamlog.wheretaken : r"#WhereTaken #[0-9]+ \([0-9]{2}\.[0-9]{2}\.[0-9]{4}\)",
            gamlog.whentaken : r"#WhenTaken #[0-9]+ \([0-9]{2}\.[0-9]{2}\.[0-9]{4}\)",
            gamlog.flagle : r"#Flagle #[0-9]+ \(.*\) ./6",
            gamlog.genshindle : r"I (found|couldn't find) today's #Genshindle",
            gamlog.planespottle : r"Planespottle #[0-9]+ (failed to guess|in \d/5 guesses)!",
            gamlog.travle : r"#travle.* #[0-9]+ (\+([0-9]+)|\(([0-9]+) away\))",
            gamlog.costcodle : r"Costcodle #[0-9]+ ./6",
            gamlog.chrono : r". CHRONO  ?#[0-9]+"
            }
    

    for game, pattern in PATTERNS.items(): 
        match = re.search(pattern, message.content.strip())
        if match:
            game(message, reverse)
            continue

        
@bot.event
async def on_ready():

    for channel in CHANNELS:
        channel = bot.get_channel(channel)
        async for message in channel.history(limit=chat_limit,oldest_first=True):
            process_message(message, False)

    print('Ready!')

@bot.event
async def on_message(message : discord.message):
    process_message(message, False)

@bot.event
async def on_message_delete(message : discord.message):
    process_message(message, True)

@bot.event
async def on_message_edit(
        message_before : discord.message,
        message_after : discord.message
        ):
    process_message(message_before, True)
    process_message(message_after, False)

bot.run(TOKEN)