Cython MTG Price compressor

from database_stuff  import  MTG, PRC, mtgcursor, priceconnection, pricecursor
from datetime        import  date, datetime
from sqlite3         import  Error
from time            import  gmtime, strftime
from tricks          import  tech
from PyQt5.Qt        import  QObject, QRunnable
from PyQt5.QtCore    import  pyqtSignal, pyqtSlot
from PyQt5.Qt        import  QThreadPool
from functools import partial
import traceback
import copy
import json
import os
import sqlite3
import sys
import tempfile
import time

memoryconnection = sqlite3.connect(":memory:")
memorycursor = memoryconnection.cursor()

class WorkerSignals(QObject):
    finished = pyqtSignal()
    error = pyqtSignal(tuple)
    result = pyqtSignal(object)
    progress = pyqtSignal(int)

class Worker(QRunnable):
    def __init__(self, function):
        super(Worker, self).__init__()
        self.fn = function
        self.signals = WorkerSignals()

    @pyqtSlot()
    def run(self):
        try:
            result = self.fn()
        except:
            traceback.print_exc()
            exctype, value = sys.exc_info()[:2]
            self.signals.error.emit((exctype, value, traceback.format_exc()))
        else:
            self.signals.result.emit(result)  # Return the result of the processing
        finally:
            self.signals.finished.emit()  # Done

class PrinterClass:
    """
    this is just a printer class that prints to the CLI for
    nicer user experience, dont think it drains much time
    """
    def __init__(self):
        cdef float start_time
        cdef dict time_keeper, time_timer
        cdef int total_count

        self.threadpool = QThreadPool(maxThreadCount=1)
        self.start_time = time.time() - 1.0
        self.time_keeper = {'work': 0, 'query': 0, 'deleting': 0, 'start': 0, 'iter': 0}
        self.time_timer = {}
        self.total_count = 0
        self.clear_screen = lambda: os.system('clear')
        self.skipped = 0
        self.from_memory = 0
        self.from_card_memory = 0

    def take_time(self, str who, bint start):
        """
        :param who: string name of the timer
        :param start:
        if True, timer restarts,
        if False timer stops and stores
        the value in self.time_keeper
        """
        if start == True:
            self.time_timer[who] = time.time()

        elif start == False:
            try: self.time_keeper[who] += time.time() - self.time_timer[who]
            except KeyError: self.time_keeper[who] = time.time() - self.time_timer[who]

    def output_results(self, int count, str uuid, str current_setcode):
        self.take_time('minute', False)
        if self.time_keeper['minute'] < 10:
            return

        self.take_time('minute', True)
        self.time_keeper['minute'] = 0.0

        cdef float time_elapsed = time.time() - self.time_timer['start']
        if time_elapsed <= 0:
            return

        cdef float time_per_input = time_elapsed / (count+1)
        if time_per_input <= 0:
            return

        cdef float inputs_per_minut = 60 / time_per_input
        if inputs_per_minut <= 0:
            return

        cdef str part1, part2, part3, part4, part5, part6, part7, part8, part9, parta
        cdef str ending = tech.color.END + "]}>-", card_memory
        cdef str current_color = tech.color.GREEN, end_col = tech.color.END

        self.clear_screen()

        part1 = "CURRENT{[" + tech.color.PURPLE + current_setcode + ' - ' + uuid + ending
        part2 = " PROGRESS{[" + current_color + str(count+1) + end_col + " of " + current_color + str(self.total_count) + ending
        part3 = " SPEED{[" + tech.color.YELLOW + str(round(inputs_per_minut)) + end_col + "]cards/min}>-"
        part4 = " DURATION{[" + tech.color.CYAN + str(round(time_elapsed)) + ending
        part5 = " WORK/ITER{[" + tech.color.DARKCYAN + str(round(self.time_keeper['work']))
        part6 = end_col + "/" + tech.color.BLUE + str(round(self.time_keeper['iter'])) + ending
        part7 = " QUERY TIME{[" + tech.color.YELLOW + str(round(self.time_keeper['query'])) + ending
        part8 = " DELETE TIME{[" + tech.color.RED + str(round(self.time_keeper['deleting'])) + ending
        part9 = " SKIPPED{[" + tech.color.RED + str(self.skipped) + ending

        if self.from_card_memory == True:
            current_color = tech.color.GREEN
        else:
            current_color = tech.color.RED

        card_memory = tech.color.END + '/' + current_color + 'JSON' + end_col + ']'

        if self.from_memory == True:
            current_color = tech.color.GREEN
        else:
            current_color = tech.color.RED

        parta = " MEMORY[" + current_color + 'LOCAL' + card_memory

        print(part1 + part2 + part3 + part4 + part5 + part6 + part7 + part8 + part9, parta)

def copy_sqlite_table_setcode(fromcursor, toconnection, tocursor, str table, str setcode):
    """
    drops old table and inserts an entire
    table from another with all the data
    from a specific setCode ONLY!
    """
    cdef str query
    cdef list table_data, source_data

    fromcursor.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name='{}'".format(table, ))
    table_data = fromcursor.fetchall()

    with toconnection: # dropping old table
        query = 'drop table ' + table
        try: tocursor.execute(query)
        except Error: pass

    with toconnection: # copy same table structure
        query = table_data[0][0]
        try: tocursor.execute(query)
        except Error: pass

    query = 'select * from ' + table + ' where setCode = (?)'
    fromcursor.execute(query, (setcode,))
    source_data = fromcursor.fetchall()

    if source_data == []:
        return

    query, _ = tech.empty_insert_query(fromcursor, table)

    with toconnection:
        tocursor.executemany(query, source_data)

cdef bint price_guard(float newprice, float oldprice, float percent):
    if newprice > oldprice:
        if oldprice * ((percent / 100) +1) <= newprice:
            return True
    elif newprice < oldprice:
        if newprice * ((percent / 100) +1) <= oldprice:
            return True

    return False

cdef list price_masher(list combine_pricelist, list timelist, list values):
    """
    takes a list with prices, both new, old and mixed.
    mashes them together and flattens out the price
    returns a list of tiples
    :param combine_pricelist:
    :return: a sorted list of tuples
    """
    cdef list input_list, executemany_list = [], _values
    cdef dict single_dict = {}, newdict
    cdef tuple input
    cdef float input_time, pricetime, newprice, oldprice
    cdef int count, dict_count, weight_div, price
    cdef bint good_job, rv = False
    cdef list price_cycle = [PRC.mtgoReg, PRC.mtgoFoil, PRC.euReg, PRC.euFoil, PRC.usReg, PRC.usFoil]

    combine_pricelist.sort(key=lambda x:x[PRC.unixTime])

    if len(combine_pricelist) > 1:
        for count in range(1, len(combine_pricelist)):
            for price in price_cycle:
                if combine_pricelist[count][price] == None and combine_pricelist[count-1][price] != None:
                    _values = list(combine_pricelist[count])
                    _values[price] = combine_pricelist[count-1][price]
                    combine_pricelist[count] = tuple(_values)

    for input in combine_pricelist:

        input_time = input[PRC.unixTime]

        for count in range(1, len(timelist)):
            if input_time > timelist[count-1] and input_time <= timelist[count]:
                try: single_dict[timelist[count]].append(input)
                except KeyError: single_dict[timelist[count]] = [input]

    for dict_count, (pricetime, input_list) in enumerate(single_dict.items()):

        good_job = True
        _values = copy.copy(values)
        _values[PRC.unixTime] = pricetime
        _values[PRC.uuid] = combine_pricelist[0][PRC.uuid]
        _values[PRC.weight] = 0
        weight_div = 0
        newdict = {}

        for price in price_cycle:
            newdict[price] = [0,0]

        for input in input_list:
            for price in price_cycle:
                if input[price] != None and input[price] != 0:
                    newdict[price][0] += (input[price] * input[PRC.weight])
                    newdict[price][1] += input[PRC.weight]

        for price, valuelist in newdict.items():

            if valuelist[0] == 0 or valuelist[1] == 0:
                continue

            _values[price] = round(valuelist[0] / valuelist[1], 2)
            _values[PRC.weight] += valuelist[1]
            weight_div += 1

        for count, price in enumerate(price_cycle):
            if _values[price] != None:
                _values[PRC.weight] = round(_values[PRC.weight] / weight_div)
                break
            elif count+1 == len(price_cycle):
                good_job = False

        if len(executemany_list) > 0 and executemany_list[-1][PRC.uuid] == _values[PRC.uuid] and good_job == True:

            for price in price_cycle:
                if _values[price] == None and executemany_list[-1][price] != None:
                    _values[price] = executemany_list[-1][price]

            if dict_count+1 < len(single_dict): # always inlcudes the most previous price without checking criteria

                for count in range(2, len(price_cycle)):

                    price = price_cycle[count]

                    if _values[price] != None:

                        newprice = _values[price]

                        if executemany_list[-1][price] != None:

                            oldprice = executemany_list[-1][price]

                            if _values[price] > 100:
                                rv = price_guard(newprice, oldprice, 5.0)
                            elif _values[price] > 10:
                                rv = price_guard(newprice, oldprice, 15.0)
                            elif _values[price] >= 1:
                                rv = price_guard(newprice, oldprice, 30.0)
                            elif _values[price] < 1:
                                rv = price_guard(newprice, oldprice, 75.0)

                            if rv == True:
                                break

                            elif rv == False and count == len(price_cycle)-1:
                                good_job = False

        if good_job == True:
            executemany_list.append(tuple(_values))
    return executemany_list

def extract_known_uuids(dict price_dict):
    """
    takes all data and sorts them i smart
    order for speeding up the later processing
    :param price_dict: all the uncut data
    :return:
    dictionary that replaces price_dict['data'] and a list
    with all UUID's so that we'll know later which ones we
    need to regather before deleting them all
    """
    cdef str current_uuid = 'NONE!', tmp_uuid
    cdef list price_data, unique_uuid_list = []
    cdef dict temp_dict = {'data': {}}
    cdef tuple price_tuple
    cdef list sort_cards = []
    cdef int count

    pricecursor.execute('select * from prices where setCode is not NULL')
    price_data = pricecursor.fetchall()

    if price_data != []:
        price_data.sort(key=lambda x:x[PRC.setCode])

    for price_tuple in price_data:
        if price_tuple[PRC.uuid] != current_uuid: # speeding trick
            current_uuid = price_tuple[PRC.uuid]
            unique_uuid_list.append(current_uuid)
            if current_uuid in price_dict['data']:
                temp_dict['data'][current_uuid] = copy.copy(price_dict['data'][current_uuid])
                price_dict['data'].pop(current_uuid)

    for current_uuid in price_dict['data']:
        mtgcursor.execute('select * from cards where uuid = (?)', (current_uuid,))
        price_tuple = mtgcursor.fetchone()
        if price_tuple != None:
            sort_cards.append(price_tuple)

    if sort_cards != []:
        sort_cards.sort(key=lambda x:x[MTG.cards.setCode])
        for price_tuple in sort_cards:
            current_uuid = price_tuple[MTG.cards.uuid]
            temp_dict['data'][current_uuid] = copy.copy(price_dict['data'][current_uuid])

    return temp_dict, unique_uuid_list

cdef list mash_together_new_and_old_pricelist(list combine_pricelist, dict current_work, list values):
    cdef list _values, suppliers_list = ['cardmarket', 'cardhoarder', 'tcgplayer']
    cdef str PO, surface, human_date, supplier
    cdef dict uuid_price
    cdef float unixtime, price
    cdef int pricekey = 0

    for PO in current_work:
        for supplier in current_work[PO]:

            if supplier not in suppliers_list:
                continue

            try: uuid_price = current_work[PO][supplier]['retail']
            except KeyError: continue

            for surface in uuid_price:
                _values = copy.copy(values)

                for human_date, price in uuid_price[surface].items():

                    unixtime = time.mktime(datetime.strptime(human_date, "%Y-%m-%d").timetuple())

                    if len(combine_pricelist) > 0 and unixtime <= combine_pricelist[-1][PRC.unixTime]:
                        continue

                    if supplier == 'cardmarket':

                        if surface == 'foil':
                            pricekey = PRC.euFoil
                        else:
                            pricekey = PRC.euReg

                    elif supplier == 'tcgplayer':

                        if surface == 'foil':
                            pricekey = PRC.usFoil
                        else:
                            pricekey = PRC.usReg
                    elif supplier == 'cardhoarder':

                        if surface == 'foil':
                            pricekey = PRC.mtgoFoil
                        else:
                            pricekey = PRC.mtgoReg

                    _values[PRC.unixTime] = unixtime
                    _values[pricekey] = price

                    combine_pricelist.append(tuple(_values))

    return combine_pricelist

def gather_local_combine_pricelist(str uuid, list values, str current_setcode, object printer):
    """
    first looks into memoryconnection for inputs, if not it goes to priceconnection for one.
    If one is found, its entire setcode from pricecursor will be the new prices in memoryconnection.
    secondly setcode are'nt found it looks into memoryconnection for its uuid to find its setcode,
    if not it cathces the entire setcode from MTGJson and make a new memoryconnection from that set.
    :return: values and setcode + continue-bool
    """
    cdef list combine_pricelist = []
    try:
        memorycursor.execute('select * from prices where uuid = (?)', (uuid,))
        combine_pricelist = memorycursor.fetchall()
    except Error:
        combine_pricelist = []
    finally:
        if combine_pricelist == []:
            printer.from_memory = False
            pricecursor.execute('select * from prices where uuid = (?)', (uuid,))
            combine_pricelist = pricecursor.fetchall()

            if combine_pricelist != [] and combine_pricelist[0][PRC.setCode] != None:
                current_setcode = combine_pricelist[0][PRC.setCode]
                copy_sqlite_table_setcode(pricecursor, memoryconnection, memorycursor, 'prices', current_setcode)
        else:
            printer.from_memory = True

    if combine_pricelist == [] or combine_pricelist[0][PRC.setCode] == None:
        try:
            memorycursor.execute('select * from cards where uuid = (?)', (uuid,))
            mtgdata = memorycursor.fetchone()

        except Error:
            mtgdata = None

        finally:

            if mtgdata == None:
                printer.from_card_memory = False

                mtgcursor.execute('select * from cards where uuid = (?)', (uuid,))
                mtgdata = mtgcursor.fetchone()
                if mtgdata == None:
                    return combine_pricelist, values, current_setcode, False
                else:
                    copy_sqlite_table_setcode(mtgcursor, memoryconnection, memorycursor, 'cards', mtgdata[MTG.cards.setCode])
            else:
                printer.from_card_memory = True

            if mtgdata != None:
                current_setcode = mtgdata[MTG.cards.setCode]
                values[PRC.setCode] = current_setcode

                if combine_pricelist != [] and combine_pricelist[0][PRC.setCode] == None:

                    with priceconnection:
                        pricecursor.execute('update prices set setCode = (?) where uuid = (?)', (current_setcode, uuid,))

                    pricecursor.execute('select * from prices where uuid = (?)', (uuid,))
                    combine_pricelist = pricecursor.fetchall()

    else:
        values[PRC.setCode] = combine_pricelist[0][PRC.setCode]

    return combine_pricelist, values, current_setcode, True

def finaliaze_work(list uuidlist, list values, list timelist, list final_injectlist, str query):
    cdef str uuid
    cdef list combine_pricelist, execute_many_list, temp_list
    cdef int count

    print('Injecting orphans:', len(uuidlist))
    for uuid in uuidlist:
        pricecursor.execute('select * from prices where uuid = (?)', (uuid,))
        combine_pricelist = pricecursor.fetchall()

        if combine_pricelist == [] or combine_pricelist[0][PRC.setCode] == None:
            continue

        values[PRC.uuid] = uuid
        values[PRC.weight] = 1
        values[PRC.setCode] = combine_pricelist[0][PRC.setCode]

        execute_many_list = price_masher(combine_pricelist, timelist, values)
        final_injectlist += execute_many_list

    for count in range(len(final_injectlist)-1,-1,-1): # clears old sqlite ID!
        if final_injectlist[count][PRC.setCode] == None:
            final_injectlist.pop(count)
            continue

        if final_injectlist[count][0] == None:
            continue

        temp_list = list(final_injectlist[count])
        temp_list[0] = None
        final_injectlist[count] = tuple(temp_list)

    if final_injectlist != []:
        final_injectlist.sort(key=lambda x:x[PRC.setCode])

        with priceconnection:
            pricecursor.execute('delete from prices;')
            pricecursor.executemany(query, final_injectlist)

def start_working_on_files(str unpack_location, list timelist):
    """ the larger part of the process starts here """

    cdef list final_injectlist = [], values, uuidlist = []
    cdef int count_count
    cdef str uuid, query, current_setcode = ":NONE:"
    cdef dict price_data, current_work
    cdef bint carry_on

    query, values = tech.empty_insert_query(pricecursor, 'prices')

    with open(unpack_location, 'r') as raw_dump:
        print('Loading dump:', unpack_location)
        price_data = json.load(raw_dump)

        printer = PrinterClass()

        printer.total_count = len(price_data['data'])

        printer.take_time('start', True)
        printer.take_time('minute', True)

        printer.take_time('query', True)
        price_data, uuidlist = extract_known_uuids(price_data)
        printer.take_time('query', False)

        printer.skipped =  printer.total_count - len(price_data['data'])

        printer.total_count = len(price_data['data'])

        for count, uuid in enumerate(price_data['data']):

            thread = Worker(partial(printer.output_results, count, uuid, current_setcode))
            printer.threadpool.start(thread)

            values[PRC.uuid] = uuid
            values[PRC.weight] = 1

            current_work = price_data['data'][uuid]

            # if uuid != "b9f2a87e-3b67-5547-8cca-91b6bd06b4f1":
            #     continue

            printer.take_time('query', True)
            combine_pricelist, values, current_setcode, carry_on = gather_local_combine_pricelist(uuid, values, current_setcode, printer)
            printer.take_time('query', False)

            if carry_on == False:
                printer.skipped += 1
                continue

            printer.take_time('iter', True)
            combine_pricelist = mash_together_new_and_old_pricelist(combine_pricelist, current_work, values)
            printer.take_time('iter', False)

            if combine_pricelist == []:
                printer.skipped += 1
                continue

            printer.take_time('work', True)
            execute_many_list = price_masher(combine_pricelist, timelist, values)
            final_injectlist += execute_many_list
            printer.take_time('work', False)

            printer.take_time('deleting', True)
            for count_count in range(len(uuidlist)-1,-1,-1):
                if uuidlist[count_count] == uuid:
                    uuidlist.pop(count_count)
                    break
            printer.take_time('deleting', False)

    finaliaze_work(uuidlist, values, timelist, final_injectlist, query)
    printer.output_results(count, uuid, current_setcode)

cdef list make_timelist():
    """
    makes a list with time thresholds that price inputs will fall inbetween
    :return: list with unixtimes
    """
    cdef float unixtime = time.mktime(datetime.strptime("2019-1-1", "%Y-%m-%d").timetuple())

    cdef float one_year = time.time() - (86400 * 365)
    cdef float three_months = time.time() - (86400 * 90)
    cdef float one_months = time.time() - (86400 * 30)
    cdef float one_week = time.time() - (86400 * 7)
    cdef float three_days = time.time() - (86400 * 3)
    cdef float one_day = time.time() - 86400

    cdef list cycle = [
        [unixtime, one_year, 86400*90],
        [one_year, three_months, 86400*30],
        [three_months, one_months, 86400*7],
        [one_months, one_week, 86400*3],
        [one_week, one_day, 86400]
    ]

    cdef list timelist = [unixtime]

    cdef int count
    for count in range(len(cycle)):
        while timelist[-1] + cycle[count][2] < cycle[count][1]:
            timelist.append(timelist[-1] + cycle[count][2])

    return timelist

def loading_raw_files():
    """
    prepeares local files and user
    input decides if to be redownloaded
    """
    cdef str default_download_location = '/home/plutonergy/Downloads/AllPrices.json'
    cdef str default_unpack_location = '/mnt/ramdisk/AllPrices.txt'
    cdef str unpack_location = tempfile.gettempdir() + "/AllPrices.txt"
    cdef str delete = 'n'

    print('Default download location: /home/plutonergy/Downloads/AllPrices.json pressing enter == NO CHANGE!')
    cdef str download_to = input('Channge download location to: ')
    cdef list timelist

    if download_to == "":
        download_to = default_download_location

    if os.path.exists(download_to):
        delete = input('File exists, would you like to redownload ' + download_to + ": ")
        if delete.lower() == 'y':
            os.remove(download_to)
            print('Downloading https://mtgjson.com/api/v5/AllPrices.json to', download_to)
            tech.download_file('https://mtgjson.com/api/v5/AllPrices.json', download_to)
    else:
        print('Downloading https://mtgjson.com/api/v5/AllPrices.json to', download_to)
        tech.download_file('https://mtgjson.com/api/v5/AllPrices.json', download_to)

    if os.path.exists(download_to) == False:
        print("Shit happened!")
        sys.exit()

    if os.path.exists('/mnt/ramdisk'):
        unpack_location = default_unpack_location

    if delete.lower() == 'y' or os.path.exists(unpack_location) == False:
        if os.path.exists(unpack_location):
            os.remove(unpack_location)

        with open(download_to, 'r') as raw:
            print('Loading:', download_to)
            load = json.load(raw)

        with open(unpack_location, "w") as file:
            print('Dumping raw-data to:', unpack_location)
            json.dump(load, file)

    timelist = make_timelist()
    start_working_on_files(unpack_location, timelist)