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

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

def convertTime(wrtime):
    """
    Float -> String
    Takes float server time and changes it to min:sec.milliseconds
    """

    readtime = int(wrtime)
    timeMin = readtime//60
    timeSec = readtime % 60
    timeMilli = int((wrtime*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(timeMilli)

def checkBool(cbool):
    if cbool:
        print("test")

class Surftimer:
    """Surftimer commands"""

    #inuse = False

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

    @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')
            wrtime = sqlwrresult.get('runtimepro')

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

    @commands.command()
    async def top(self):
        """ - Shows top 10 players on the server"""
        #inuse = True
        #if inuse = 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:
                checkBool(inuse)
                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()
        #inuse = False
        #else:
           # await self.bot.say("Surftimer | Command already in use.")

    @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))

            #sqlmtyperesult = cursor.fetchone()
            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))