import sqlite3
from datetime import datetime, timedelta

class db:
    def init():
        try:
            conn = sqlite3.connect('accounts.db')
            cursor = conn.cursor()
            cursor.execute('''CREATE TABLE IF NOT EXISTS sessions (email text UNIQUE, password integer, code integer, datedel text)''')
            cursor.execute('''CREATE TABLE IF NOT EXISTS accounts (email text UNIQUE, password integer, discord text UNIQUE, mc text UNIQUE, code integer, datedel text)''')
            conn.commit()
            conn.close()
            return True
        except:
            return False
    
    def get_session_passwd(email):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        passwd = '''SELECT password FROM sessions WHERE email = ? LIMIT 1'''
        cursor.execute(passwd, (email, ))
        fetch_passwd = cursor.fetchone()
        conn.close()
        return fetch_passwd[0]


    def del_session(email):
         conn = sqlite3.connect('accounts.db')
         cursor = conn.cursor()
         session = '''DELETE FROM sessions WHERE email = ?'''
         cursor.execute(session, (email, ))
         conn.commit()
         conn.close()
         return True

    def get_session(email):
        exists = '''SELECT email FROM sessions WHERE email = ? LIMIT 1'''
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        cursor.execute(exists, (email, ))
        result = cursor.fetchone()
        conn.close()
        return result

    def check_date(email):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        past_sql = '''SELECT datedel FROM sessions WHERE email = ? LIMIT 1'''
        cursor.execute(past_sql, (email, ))
        fetch_past = cursor.fetchone()
        past = fetch_past[0]
        conn.close()
        return past

    def fetch_session_code(email):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        match_sql = '''SELECT code FROM sessions WHERE email = ? LIMIT 1'''
        cursor.execute(match_sql, (email, ))
        fetch_otp = cursor.fetchone()
        otp = fetch_otp[0]
        conn.close()
        return otp

    def add_session(email, password, code):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()

        insert_email = "INSERT OR IGNORE INTO sessions (email) VALUES (?)"
        insert_password = "UPDATE sessions SET password = ? WHERE email = ?"
        insert_code = "UPDATE sessions SET code = ? WHERE email = ?"
        insert_datedel = "UPDATE sessions SET datedel = ? WHERE email = ?"

        cursor.execute(insert_email, (email, ))
        cursor.execute(insert_password, (password, email, ))
        cursor.execute(insert_code, (code, email, ))
        cursor.execute(insert_datedel, (datetime.now() + timedelta(minutes=5), email,  ))
    
        conn.commit()
        conn.close()
        return True

    def add_account(email, password):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        insert_email = "INSERT OR IGNORE INTO accounts (email) VALUES (?)"
        insert_password = "UPDATE accounts SET password = ? WHERE email = ?"
        cursor.execute(insert_email, (email, ))
        cursor.execute(insert_password, (password, email, ))
        conn.commit()
        conn.close()
        return True

    def account_exists(email):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        match_sql = '''SELECT email FROM accounts WHERE email = ? LIMIT 1'''
        cursor.execute(match_sql, (email, ))
        fetch_acc = cursor.fetchone()
        if fetch_acc is None or False:
            return False
        conn.close()
        return True
    
    def account_add_otp(email, otp):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        insert_code = "UPDATE accounts SET code = ? WHERE email = ?"
        insert_datedel = "UPDATE accounts SET datedel = ? WHERE email = ?"

        cursor.execute(insert_code, (otp, email, ))
        cursor.execute(insert_datedel, (datetime.now() + timedelta(minutes=5), email,  ))
    
        conn.commit()


        conn.close()
        return True
    
    def account_del_otp(email):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        empty = None
        remove_code = "UPDATE sessions SET code = ? WHERE email = ?"
        remove_datedel = "UPDATE sessions SET datedel = ? WHERE email = ?"
        cursor.execute(remove_code, (empty, email, ))
        cursor.execute(remove_datedel, (empty, email, ))
        conn.commit()
        conn.close()
        return True

        

    def check_account_otp(email):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        past_sql = '''SELECT datedel FROM accounts WHERE email = ? LIMIT 1'''
        cursor.execute(past_sql, (email, ))
        fetch_past = cursor.fetchone()
        past = fetch_past[0]
        if (past is None or False):
            return False
        present = datetime.now()
        conn.close()
        if (str(present) > past):
            db.account_del_otp(email)
            return False
        else:
            return True

    def get_account_otp(email):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        getotp = '''SELECT code FROM accounts WHERE email = ?'''
        cursor.execute(getotp, (email, ))
        fetch_code = cursor.fetchone()
        code = fetch_code[0]
        if (code is None or False):
            return False
        else:
            return code

    def discord_exists(email):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        get_discord = '''SELECT discord FROM accounts WHERE email = ?'''
        cursor.execute(get_discord, (email, ))
        fetch_discord = cursor.fetchone()
        if (fetch_discord == None):
            return False
        discord = fetch_discord[0]
        if (discord == None):
            return False
        return discord

    def add_discord(discord, email):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        update_discord = '''UPDATE accounts SET discord = ? WHERE email = ?'''
        cursor.execute(update_discord, (discord, email))
        conn.commit()
        conn.close()
        return True
    
    def add_mc(mc, email):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        add = "UPDATE accounts SET mc = ? WHERE email = ?"
        cursor.execute(add, (mc, email, ))
        conn.commit()
        conn.close()
        return True

    def replace_mc(new, email):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        replace_name = "UPDATE accounts SET mc = ? WHERE email = ?"
        cursor.execute(replace_name, (new, email, ))
        conn.commit()
        conn.close()
        return True

    def mc_email_lookup(mc):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        get_mc = '''SELECT email FROM accounts WHERE mc = ?'''
        cursor.execute(get_mc, (mc, ))
        fetch_mc = cursor.fetchone()
        if (fetch_mc == None):
            return False
        email = fetch_mc[0]
        return email

    def mc_username_lookup(email):
        conn = sqlite3.connect('accounts.db')
        cursor = conn.cursor()
        get_email = '''SELECT mc FROM accounts WHERE email = ?'''
        cursor.execute(get_email, (email, ))
        fetch_email = cursor.fetchone()
        if (fetch_email == None):
            return False
        mc = fetch_email[0]
        if (mc == None):
            return False
        return mc