## LIBRARIES
# //////////////////////////////////////////////////////////////////////////////

# import pandas as pd               # pandas for excel reading
import mysql.connector as mysql     # mysql connector
import re       # regex
import json     # json
import os.path  # ...
import datetime

t0_ = datetime.datetime.now()

## LOCAL FUNCTIONS
# //////////////////////////////////////////////////////////////////////////////


# do-me-INTeger
# ---
def domeInt(x) :
    if isinstance(x, str) :     # if string
        return int(x.strip())
    if isinstance(x, float) :   # if float
        return round(x)
    return x      # otherwise is int already


# do-me-Float
# ---
def domeFloat(x) :
    if isinstance(x, str) :
        return float(x.strip())
    else :
        return x + 0.00     # make sure that result is float


## Clean Text ...
# -> removes some general/neutral words and symbols
# -> ignores some in-line characters 
# -> also strips spare spaces
# function is applied onto the full title/description
# ---
def cleanText(x) :
    ignoreList = '" ( ) [ ]'.split(' ')

    for r in ignoreList :
        x = x.replace(r, ' ')
       
    x = x.replace('    ', ' ')      # remove spare spaces
    x = x.replace('   ', ' ')
    x = x.replace('  ', ' ')

    return x.replace('  ', ' ')     # one lase (just in case)


# isSignificant
# decides if the term is significant to be indexed;
# a term is significant if does not contain digit-chars [0-9], comma (,) or period (.)
# ---
def isSignificant(x) :
    # fisrts exclude some notable exceptions (mostly brands)
    if x in ['7UP', '3ΑΛΦΑ', '17'] :
        return True

    return not bool( re.match("\S*\d+\S*", x) )


def kbLatinString( txt ) :
    maTable = txt.maketrans(
        "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNM",
        "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviyqwertyuiopasdfghjklzxcvbnm"
    )
    txt = txt.replace('\'', '')
    return txt.translate(maTable).lower()


# check if word: w
# … has synonyms; return list of synonyms
# ---
def synonymKeys(w) :
    w_kb = kbLatinString(w)
    syns = [ w ]
    found = False
    # check if has synonyms
    for group in synonyms :
        possibles = group.split()  
        for wi in possibles :
            if kbLatinString(wi) == w_kb :
                syns = possibles
                found = True
                break
        if found :
            break
    return syns


# set root-keyword: wl (if not exist)
# update frequency: f
# into list: l
# NOTE:
# * wl is a list of synonym-words
# ** comparison is based on the *keyboard* format
## ---
def rootKey ( wl, f, l ) :
    keyExists = False
    w_kb = kbLatinString(wl[0])     # cache kb format

    # check if exists in root keys already
    # NOTE: you only need to check the 1st word of synonyms-list
    for it in l :
        if it['kb'] == w_kb :
            keyExists = True
            it['f'] += f
            break
    
    # if not exists, append keyword
    if keyExists == False :
        l.append({
            'w' : wl,
            'kb' : w_kb,
            'f' : f,
            'c' : []
        })


# connect keys: a , b (each one is a list of synonmyms)
# of product with id: i
# with frequency: f
# into list: l
## ---
def connectKeys( a, b, i, f, l ) :
    kbA = kbLatinString(a[0])
    kbB = kbLatinString(b[0])

    if kbA == kbB :
        return False        ## exclude just-in-case
    
    for it in l :
        if it['kb'] == kbA :
            # found: a;

            # let's update connection to: b
            bExists = False
            for jt in it['c'] :
                if jt['kb'] == kbB :
                    bExists = True
                    # update the connection's data
                    jt['f'] += f
                    jt['p'].append(i)
                    break
            
            if bExists == False :
                # create connection with word: b
                it['c'].append({
                    'w': b,
                    'kb': kbB,
                    'f': f,
                    'p': [ i ]
                })
            break


## let mysql to return valid strings
## (otherwise it returns strings with missed characters)
# credit: https://stackoverflow.com/a/68784172
# analytical credit: https://stackoverflow.com/questions/27566078/
def get_data_from_db(cursor, sql):
    output = []
    cursor.execute(sql)
    row = cursor.fetchone()
    while row is not None:
        row_to_return = row.decode('utf-8') if isinstance(row, bytearray) else row
        output.append(row_to_return)
        row = cursor.fetchone()
    
    return output



# letters-only translation to key-pressed characters (latin)
# ---
def kbLatinLetter( txt ) :
    maTable = txt.maketrans(
        "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫ",
        "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviy"
    )
    return txt.translate(maTable).lower()


## mark a link to a text
# conecting them with a dash/minus character
# ---
def markLink(lws, text) :
    text_kb = kbLatinLetter(text.replace(' ', '-'))
    lws_kb  = kbLatinLetter(lws)
    try:
        index_l = text_kb.lower().index(lws_kb.lower())
    except:
        return text
    else:
        return text[:index_l] + lws + text[index_l + len(lws):]


### # --- list of normalized word combinations
### replaceWords = [
###     'HEAD & SHOULDERS; HEAD&SOULDERS',
###     'ΟΛΙΚΗΣ 'ΑΛΕΣΗΣ; Ολικής Άλεσης',
###     'Χωρίς προσθήκη ζάχαρης; Χωρίς-Ζάχαρη'
### ]


# --- list of linked-words
linkedWords = [
    'Χωρίς-Γλουτένη',
    'Χωρίς-Ζάχαρη',
    'Χωρίς-Αλάτι',
    'Χωρίς-Λακτόζη',
    'Χωρίς-Συντηρητικά',
    'Χωρίς-Αλκοόλ',
    'Υψηλής-Παστερίωσης',
    'Ολικής-Άλεσης',
    'Ολικής-Aλέσεως',
    'Χαρτί-Υγείας',
    'ρολό-υγείας',
    'χαρτί-τουαλέτας',
    'Χαρτί-Κουζίνας',
    'Μπάρες-Δημητριακών',
    'Μπαρμα-Στάθης',
    'Coca-Cola'
    'Aς-Μαγειρέψουμε',
    'ΚΡΙΣ-ΚΡΙΣ',
    'ΚΡΙ-ΚΡΙ',
    'ΕΛ-ΓΚΡΕΚΟ',
    'FREE-STEP',
    'EL-SABOR',
    'LE-PETIT-MARSEILLAIS',
    'DOUWE-EGBERTS',
    'ΕΝ-ΕΛΛΑΔΙ',
    'SPIN-SPAN',
    'CRETA-FARMS',
    'CRETA-FARM',
    'NES-CAFE'
]


# text after "all-links" marked
# ---
def markLinkedWords(text) :
    for lw in linkedWords :
        text = markLink( lw, text )
    
    return text



## LOCAL CONSTANTS
# //////////////////////////////////////////////////////////////////////////////

_COL = {
    # -- main info
    'freq'  : 0,    # frequency (based on recent orders)
    'pid'   : 1,    # product id
    'brand' : 2,    # brand
    'barcd' : 3,    # barcode
    'sklcd' : 4,
    'eyscd' : 5,
    'descr' : 6,    # product description
    'sap2'  : 7     # SAP category level-2 id
}



## PREPADE (or build) exception objects
# //////////////////////////////////////////////////////////////////////////////


# --- list of words to exclude from keywords
# NOTE:
# APPLIED in PER-WORD base -> after spliting description to words
removeList = []
removeOriginals = 'μας με σε για του της των από στο στον & r s ft l τ e g h k m n o p s x'.split(' ')
for it in removeOriginals :
    removeList.append(kbLatinString(it))


# --- list of synonyms
# in fact
synonyms = [
    'μπίρα μπύρα μπίρες μπύρες',
    'αυγά αβγά αυγό αβγό',
    'σίκαλης σικάλεως',
    'ξηρά ξερά',
    'ρολό ρολλό',
    'coca-cola cocacola coke',
    'χαρτί-υγείας ρολό-υγείας χαρτί-τουαλέτας',
    'χαρτί-κουζίνας ρολό-κουζίνας',
    'οινος κρασι',
    'ΚΑΤΣΕΛΗΣ ΚΑΤΣΕΛΗ',
    'DR-OETKER OETKER',
    'DR.BECKMANN BECKMANN',
    'NES-CAFE NESCAFE',
    'Ολικής-Άλεσης Ολικής-Aλέσεως Ολικής',
    'τσίπουρο ρακή',
    'Βρώμη Βρώμης',
    'Φράουλα Φράουλες Φράουλας',
    'Μαλλιά Μαλλιών',
    'Κέικ, Cake',
    'CRETA-FARMS CRETA-FARM',
    'MARSEILLAIS LE-PETIT-MARSEILLAIS',
    'Γαϊδούρας Γαϊδάρου',
    'ΚΑΛΟΓΕΡΑΚΗΣ ΚΑΛΟΓΕΡΑΚΗ',
    'ΚΑΪΔΑΝΤΖΗΣ ΚΑΪΔΑΝΤΖΗ',
    'ΥΦΑΝΤΗΣ ΥΦΑΝΤΗ',
    'ΣΥΝΑΓΡΙΔΑ ΣΥΝΑΓΡΙΔΕΣ'
]


## Read data
# //////////////////////////////////////////////////////////////////////////////

# enter your 
HOST = "127.0.0.1"              # server IP address/domain name
DATABASE = "dev_pythia_db"      # database name
USER = "pythia_db_user_dev"
PASSWORD = "VnEP0eysjiXDHcfM"

# connect to MySQL server
_dbc = mysql.connect(
        host=HOST,
        database=DATABASE,
        user=USER,
        password=PASSWORD,
        use_unicode=True,
        charset='utf8'
    )
print("Connected to:", _dbc.get_server_info())

# execute SQL to get all data you need
crs = _dbc.cursor()
query = '''
    SELECT count(pl.eys_code) as FREQuency,
        pl.product_id as product_id,
        pb.brand_name,
        pl.barcode, pl.skl_code, pl.eys_code,
        IF( pd.description IS NOT NULL , pd.description , pl.product_description) AS product_description
    FROM product_list as pl
    LEFT JOIN delivery_orders_products AS dop ON dop.product_id = pl.eys_code
    LEFT JOIN delivery_orders AS do ON dop.order_id = do.id
    LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code
    LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code
    LEFT JOIN product_brands pb ON pl.brand_id = pb.id
    WHERE pl.active = 1 AND pl.sap_code IS NOT NULL AND pl.product_category_sap_4 NOT LIKE '72%'
    GROUP BY pl.product_id
    ORDER BY FREQuency DESC
'''
results_ = get_data_from_db(crs, query)

t_db = datetime.datetime.now()


# --- Lists to fill
keywords_ = []     # all data; main exported object
minilist_ = []
products_ = []

## keywords format:
## [
##    {
##       w : [ word, word-synonym, ... ],
##      kb : = kbLatinString(word)
##       f : 150,
##       c : [
##             { w: ['fish', 'fishes'],  f: 150,  p: [122, 254, 907] },
##             { w: ['juice'],           f: 50,   p: [254, 351] }
##       ]
##    },
##    ...
## ]
##
## --- index:
## w : words / list of synonyms (str/utf-8)
# kb : ascii-latin-keypoard format of first item of "w" list
## f : frequency (int)
## c : combos / connections (list of objects)
## p : list of product-ids found in specific words-combination (list of int)


records_counter = 0
## LOOP through the rows to pre-proccess all products
## ---
for row in results_ :
    records_counter += 1

    description = row[_COL['descr']]        # product description
    pid  = domeInt( row[_COL['pid']] )      # product-id
    fq   = domeInt( row[_COL['freq']] )     # frequency

    # setup product
    # ---
    products_.append({
        'w'  : description,
        'id' : pid,
        'f'  : fq
    })

    # TODO:
    # identify brands
    # then ...

    description = cleanText(description)        # clean description string before spliting

    description = markLinkedWords(description)  # ...

    keys = []                                   # list of product's key(word)s
    words = description.split()                 # split to words
    for w in words :
        if kbLatinString(w) not in removeList:      # if not in removeList            
            if isSignificant(w) :                   # and if significant
                keys.append(w)                      # keep it


    ## print(pid, description, words, keys)
    print(pid, keys)

    # append words (and their combos) to the list
    for w in keys :
        wl = synonymKeys(w)
        rootKey( wl, fq, keywords_ )
        for w2 in keys :
            if w2 != w :
                w2syns = synonymKeys(w2)
                connectKeys( wl, w2syns, pid, fq, keywords_ )


## SORT keywords
# //////////////////////////////////////////////////////////////////////////////

# --- sort childs of each key (per frequency, desc)
for it in keywords_ :
    it['c'].sort(key=lambda x: x['f'], reverse=True)


# --- sort root keys
keywords_.sort(key=lambda x: x['f'], reverse=True)




## alternative formats to test ------------------------------------------- START

with open("results/keywords-full.json", "w", encoding="utf-8") as outfile :
    data = json.dump(keywords_, outfile, sort_keys=False, indent=2, ensure_ascii=False)

keyhashes_ = []
hashedkeys_ = []

# --- remove 'kb' keywords
for ki in keywords_ :
    h = ki['kb']
    keyhashes_.append({ h : ki['w'] })
    conns = []
    del ki['kb']
    for ci in ki['c'] :
        conns.append({
            'h' : ci['kb'],
            'f' : ci['f'],
            'p' : ci['p']
        })
        del ci['kb']
    hashedkeys_.append({
        'h' : h,
        'f' : ci['f'],
        'c' : conns
    })

with open("results/hashes.json", "w", encoding="utf-8") as outfile :
    data = json.dump(keyhashes_, outfile, sort_keys=False, indent=2, ensure_ascii=False)

with open("results/hashedkeys.json", "w", encoding="utf-8") as outfile :
    data = json.dump(hashedkeys_, outfile, sort_keys=False, indent=2, ensure_ascii=False)

# --- create mini list based on the sorted keywords_
### for it in keywords_ :
###     minilist_.append({
###         'w' : it['w'],
###         'f' : it['f'],
###         'kb': it['kb']
###     })
###
### with open("results/minilist.json", "w", encoding="utf-8") as outfile :
###     data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False)


## alternative formats to test --------------------------------------------- END




t_main = datetime.datetime.now()
print('Proccessing ended; saving results in json format ...')


## OUTPUT final data to a json-format file
# //////////////////////////////////////////////////////////////////////////////

with open("results/keywords.json", "w", encoding="utf-8") as outfile :
    data = json.dump(keywords_, outfile, sort_keys=False, indent=2, ensure_ascii=False)

with open("results/products.json", "w", encoding="utf-8") as outfile :
    data = json.dump(products_, outfile, sort_keys=False, indent=2, ensure_ascii=False)


t_end = datetime.datetime.now()


print(records_counter, 'products proccessed')
print('execution time:', (t_end - t0_))
print('from which ... database:', (t_db - t0_))
print('... records proccessing:', (t_main - t_db))



## NOTE:
## prepare cloud-sql-proxy
## ---
## * install cloud-sql-proxy
##   : sudo wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O /usr/local/cloud_sql_proxy
##   : sudo chmod +x /usr/local/cloud_sql_proxy
##
## * prepare/export/publish/copy credentials ...
##   : sudo cp /some/path/to/cloudsqlproxy.json /usr/local
##
## * finaly run the database instance
##   : /usr/local/cloud_sql_proxy -instances=pythia-251711:europe-west4:pythia-db-eu=tcp:3306 -credential_file=cloudsqlproxy.json
##
## after installation only the last command needs to run before connecting to the cloud-sql