import sqlite3, typing
from Types import *

class UserRepository:

    def __init__(self):
        self.__initialize()
    
    def __initialize(self):
        cur = self.__getConnection().cursor()
        cur.execute("CREATE TABLE IF NOT EXISTS user(chat_id INT, alias TEXT)")
        cur.execute("CREATE UNIQUE INDEX IF NOT EXISTS chat_id_idx ON user(chat_id)")
    
    def __getConnection(self):
        return sqlite3.connect('db.sqlite')
    
    def getUser(self, chat_id):
        connection = self.__getConnection()
        cur = connection.cursor()
        query = "select * from user where chat_id = {chat_id}".format(chat_id = chat_id)
        cur.execute(query)
        return cur.fetchone()

    def getAllUsers(self):
        connection = self.__getConnection()
        cur = connection.cursor()
        query = "select * from user"
        cur.execute(query)
        return cur.fetchall()
    
    def removeChat(self, chat_id):
        connection = self.__getConnection()
        cur = connection.cursor()
        query = "DELETE FROM user where chat_id = {chat_id}".format(
            chat_id = chat_id)
        cur.execute(query)
        connection.commit()

    
    def putUser(self, chat_id, alias):
        if not self.getUser(chat_id):
            connection = self.__getConnection()
            cur = connection.cursor()
            query = "INSERT INTO user(chat_id, alias) VALUES({chat_id}, '{text}')".format(
                chat_id = chat_id,
                text = alias)
            cur.execute(query)
            connection.commit()

class StatsRepository:

    def __init__(self):
        self.__initialize()
    
    def __getConnection(self):
        return sqlite3.connect('db.sqlite')
    
    def __initialize(self):
        cur = self.__getConnection().cursor()
        cur.execute("CREATE TABLE IF NOT EXISTS stats(user_id TEXT, display_name TEXT, level INT, matches_played INT, kills INT, wins INT)")
        cur.execute("CREATE UNIQUE INDEX IF NOT EXISTS user_id_idx ON stats(user_id)")
    
    async def putStats(self, user: User):
        stats: UserStats = await user.fetch_stats()

        connection = self.__getConnection()
        cur = connection.cursor()
        query = "INSERT OR REPLACE INTO stats(user_id, display_name, level, matches_played, kills, wins) VALUES('{user_id}', '{display_name}', {level}, {matches_played}, {kills}, {wins})".format(
            user_id = user.id,
            display_name = user.display_name,
            level = stats.level,
            matches_played = stats.matches_played,
            kills = stats.kills,
            wins = stats.wins)
        cur.execute(query)
        connection.commit()
    
    def getStats(self) -> typing.List[UserStats]:
        connection = self.__getConnection()
        cur = connection.cursor()
        query = "SELECT * FROM stats"
        cur.execute(query)
        result = cur.fetchall()
        return [self.__mapFromDb(stats) for stats in result]
    
    def __mapFromDb(self, record):
        userStats = UserStats()
        userStats.user_id = str(record[0])
        userStats.user_display_name = str(record[1])
        userStats.level = int(record[2])
        userStats.matches_played = int(record[3])
        userStats.kills = int(record[4])
        userStats.wins = int(record[5])
        return userStats