import discord
from discord.ext import commands
import pymysql.cursors
import re
from __main__ import user_allowed, send_cmd_help
import os

#global bool
global topCommandInUse
global mtopCommandInUse

topCommandInUse = False
mtopCommandInUse = False

# Connect to the database
def conconnect():
    try:
        self.connection.ping(True)
    except:
        connection = pymysql.connect(host='amethyst.relentlesshosting.com.au',
        user='thomasle_surf',
        password='TAJeTHX19bfK',
        db='thomasle_SurfTimer',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor)
        return connection

def convertTime(maptime):     #convert seconds to min:second.millesecond
    """ FLOAT -> String
    takes float sever time and changes it to min:sec.miliseconds
    """

    readtime = int(maptime)
    timeMin = readtime//60
    timeSec = readtime % 60
    timemili = int((maptime*1000)%1000)

    if (timeMin<10):
        strMin = "0" + str(timeMin)
    else:
        strMin = str(timeMin)

    if (timeSec<10):
        strSec = "0" + str(timeSec)
    else:
        strSec = str(timeSec)
    return strMin + ":" + strSec + "." + str(timemili)

class Surftimer:
    """Surftimer commands"""

    def __init__(self, bot):
        self.bot = bot
        conconnect();

    @commands.command(pass_context=True)
    async def wr(self, ctx, text : str):
        """<mapname> - Displays wr on map"""
        connection = conconnect();
        if text == ():
                await send_cmd_help(ctx)
                return
        server = ctx.message.server
        to_replace = ctx.message.content.find(text[0])
        text = ctx.message.content[to_replace:]
        with connection.cursor() as cursor:
            sqlwr = "SELECT db2.runtimepro, db1.name, db1.steamid FROM ck_playertimes as db2 INNER JOIN ck_playerrank as db1 on db1.steamid = db2.steamid WHERE db2.mapname LIKE %s AND db2.runtimepro  > -1.0 ORDER BY db2.runtimepro ASC LIMIT 1"
            cursor.execute(sqlwr, (text))
            sqlwrresult = cursor.fetchone()
            playername = sqlwrresult.get('name')
            maptime = sqlwrresult.get('runtimepro')

            await self.bot.say("SurfTimer | **%s** holds the record with time: **%s** on **%s**" % (playername, convertTime(maptime), text))

    @commands.command()
    async def top(self):
        """ - Shows top 10 players on the server"""
        if topCommandInUse:
            await self.bot.say("Surftimer | Command is already in use.")
        else:
            topCommandInUse = True
            connection = conconnect();
            with connection.cursor() as cursor:
                sqltop = "SELECT * FROM `ck_playerrank` ORDER BY `points` DESC LIMIT 0, 10"
                cursor.execute(sqltop)

                sqltopresult = cursor.fetchone()
                await self.bot.say("SurfTimer | **Top 10 Players:**")
                playerrank = 1

                while sqltopresult is not None:
                    playertopname = sqltopresult.get('name')
                    points = sqltopresult.get('points')
                    finishedmaps = sqltopresult.get('finishedmapspro')
                    country = sqltopresult.get('country')

                    await self.bot.say("Rank: **%i** Name: **%s** Points: **%i** Finished Maps: **%i** Country: **%s**" % (playerrank, playertopname, points, finishedmaps, country))
                    playerrank += 1
                    sqltopresult = cursor.fetchone()

                    if playerrank == 10:
                        topCommandInUse = False


    @commands.command(pass_context=True)
    async def mtop(self, ctx, text : str):
        """<mapname> - Shows top 10 players on map"""
        connection = conconnect();
        if text == ():
                await send_cmd_help(ctx)
                return
        server = ctx.message.server
        to_replace = ctx.message.content.find(text[0])
        text = ctx.message.content[to_replace:]
        with connection.cursor() as cursor:
            sqlmtop = "SELECT * FROM `ck_playertimes` WHERE `mapname` = %s ORDER BY runtimepro ASC LIMIT 0, 10"
            cursor.execute(sqlmtop, (text))

            sqlmtopresult = cursor.fetchone()
            await self.bot.say("SurfTimer | Top 10 Players on map: **%s**" % (text))
            playerrank = 1

            while sqlmtopresult is not None:
                playername = sqlmtopresult.get('name')
                maptime = sqlmtopresult.get('runtimepro')

                await self.bot.say("Rank: **%i** Name: **%s** Time: **%s**" % (playerrank, playername, convertTime(maptime)))
                playerrank += 1
                sqlmtopresult = cursor.fetchone()


    @commands.command(pass_context=True)
    async def m(self, ctx, text : str):
        """<mapname> - shows map info for map"""
        connection = conconnect();
        if text == ():
                await send_cmd_help(ctx)
                return
        server = ctx.message.server
        to_replace = ctx.message.content.find(text[0])
        text = ctx.message.content[to_replace:]
        with connection.cursor() as cursor:
            sqlmtier = "SELECT `tier` FROM `ck_maptier` WHERE `mapname` = %s"
            cursor.execute(sqlmtier, (text))

            sqlmtierresult = cursor.fetchone()
            maptier = sqlmtierresult.get('tier')

            sqlmtype = "SELECT * FROM `ck_zones` WHERE `mapname` = %s AND `zonetype` = '3'"
            cursor.execute(sqlmtype, (text))

            maptyperesult = cursor.rowcount
            maptype = ""

            sqlbcount = "SELECT COUNT(DISTINCT zonegroup) as bcount FROM `ck_zones` WHERE `mapname` = %s AND `zonegroup` >= 1"
            cursor.execute(sqlbcount, (text))

            sqlbcountresult = cursor.fetchone()
            bcount = sqlbcountresult.get('bcount')


            if maptyperesult:
                maptype = 'Staged'
                sqlstagecount = "SELECT COUNT(zonetype) as stagecount FROM `ck_zones` WHERE `mapname` = %s AND `zonegroup` = 0 AND `zonetype` = 3"
                cursor.execute(sqlstagecount, (text))

                sqlstagecountresult = cursor.fetchone()
                stagecount = sqlstagecountresult.get('stagecount') + 1

                await self.bot.say("SurfTimer | Map: **%s** - Type: **%s** - Tier: **%s** - Stages: **%i** - Bonuses: **%i**" % (text, maptype, maptier, stagecount, bcount))
            else:
                maptype = 'Linear'
                await self.bot.say("SurfTimer | Map: **%s** - Type: **%s** - Tier: **%s** - Bonuses: **%i**" % (text, maptype, maptier, bcount))



def setup(bot):
    bot.add_cog(Surftimer(bot))