import json
import os
import random
import requests
from passlib.hash import pbkdf2_sha256 as pbk
from PyQt5.QtSql import QSqlDatabase, QSqlQuery
from pprint import pprint


ENCODING = 'utf-8'
DB_PATH = os.path.join(os.path.curdir, 'inventory.db')


def scrambleWord(word):
    """Randomize the letters in word and return the resulting string."""
    word_list = list(word)
    random.shuffle(word_list)
    word = ''.join(word_list)
    return word


def generateItems():
    """Generate a dictionary of retail products and store the data in items.json.

    Pulls a list of items and artificially doubles it with scrambled item names.
    Each item is given a random PLU, UPC, and department number.
    Each dictionary key is the item's PLU.
    """
    response = requests.get('https://www.randomlists.com/data/things.json')
    json_data = response.json()
    items = json_data['RandL']['items']

    #double sample size by scrambling item names
    scrambled_list = []
    for item in items:
        scrambled_item = scrambleWord(item)
        scrambled_list.append(scrambled_item)

    items = items + scrambled_list

    data = {}
    for item in items:
        random.seed(item)
        upc = random.randint(100000000000, 999999999999)
        plu = random.randint(1000, 9999999)
        department = (plu % 7) + 1
        print('UPC:{0} | PLU:{1} | Item:{2} | D{3}'.format(upc, plu, item, department))

        if plu in data:
            print('Duplicate found: {}'.format(plu))
            continue

        data[plu] = {'upc':upc, 'department':department, 'model':item}

    with open('items.json', 'w') as f:
        json.dump(data, f)


def generatePO():
    """Create dumby Purchase Orders and store them in pos.json.

    Each PO is asigned one random vendor and department number,
    along with a random length list of items belonging to said department.

    Returns: True if items.json successfully opens, False otherwise.
    """
    try:
        with open('items.json', 'r') as f:
            items_dict = json.load(f)
    except FileNotFoundError:
        return False
    
    vendors = ['Dyson', 'Ingrammicro', 'LKG', 'Inland', 'Sandisk', 'Seagate', 'Hasbro', 'Mattel',\
               'Gear Head', 'Logitech', 'NTE', 'Dell', 'Microsoft', 'Right Stuff', 'Alliance', 'Energizer']

    po_dict = {}
    for i in range(50):
        po_num = 24000000 + random.randint(1, 999999)
        if po_num in po_dict:
            continue
        po_dict[po_num] = {'department': (po_num % 7) + 1, 'items': {}, 'vendor': random.choice(vendors)}

    for key in items_dict:
        match_found = False
        loops = 0
        while not match_found:
            loops += 1
            if loops > 200:
                print('\n\nToo many loops.\n\n')
                break
            po, department = random.choice(list(po_dict.items()))
            department = department['department']
            print('PO department: {}'.format(department))
            print('item plu: {} department: {}'.format(key, items_dict[key]['department']))
            if items_dict[key]['department'] == department:
                max_count = random.randint(1, 20)
                po_dict[po]['items'][key] = max_count
                match_found = True

    with open('pos.json', 'w') as f:
        json.dump(po_dict, f)
    return True


def fillDB():
    """Create a database and populate two tables(named items and purchase_order).

    The 'items' and 'purchase_order' tables are populated with the data from items.json
    and pos.json respectively.
    """
    with open('items.json') as f:
        data = json.load(f)

    db = QSqlDatabase.addDatabase('QSQLITE')
    db.setDatabaseName(DB_PATH)
    if not db.open():
        print('DB could not be opened')
        error = QSqlDatabase.lastError()
        print(error.text())
        return False

    query = QSqlQuery()
    if query.exec_("drop table items"):
        print('successfully dropped table')
    else:
        print('unsuccessfully dropped table')
        print(query.lastError().text())
    if query.exec_("create table items(plu int primary key, upc varchar(12) unique, "
                "model varchar(20), department int)"):
        print('success')
    else:
        print('failure')
        print(query.lastError().text())
    for key in data:
        if query.exec_("insert into items values({}, '{}', '{}', {})".format(key, data[key]['upc'],
                                                                      data[key]['model'], data[key]['department'])):
            print("values({}, {}, {}, {}) successfully inserted.".format(key, data[key]['upc'], data[key]['model'], data[key]['department']))
        else:
            print("values({}, {}, {}, {}) unsuccessfully inserted.".format(key, data[key]['upc'], data[key]['model'], data[key]['department']))
            print(query.lastError().text())

    with open('pos.json') as f:
        po_dict = json.load(f)

    if query.exec_("drop table purchase_order"):
        print('successfully dropped table')
    else:
        print('unsuccessfully dropped table')
        print(query.lastError().text())
    if query.exec_("create table purchase_order(po int primary key, vendor varchar(30), "
                   "department int, items blob)"):
        print('success')
    else:
        print('failure')
        print(query.lastError().text())
    for key in po_dict:
        item_string = json.dumps(po_dict[key]['items'])
        item_blob = item_string.encode(ENCODING)
        if query.exec_("insert into purchase_order values({}, '{}', {}, '{}')"\
                       .format(key, po_dict[key]['vendor'], po_dict[key]['department'], item_string)):
            print("values({}, {}, {}, {}) successfully inserted."\
                  .format(key, po_dict[key]['vendor'], po_dict[key]['department'], item_string))
        else:
            print("values({}, {}, {}, {}) unsuccessfully inserted."\
                  .format(key, po_dict[key]['vendor'], po_dict[key]['department'], item_blob))
            print(query.lastError().text())


def createEmployeeTable():
    db = QSqlDatabase.addDatabase('QSQLITE')
    db.setDatabaseName(DB_PATH)
    if not db.open():
        print('DB could not be opened')
        error = QSqlDatabase.lastError()
        print(error.text())
        return False
    query = QSqlQuery()
    if not query.exec_("drop table employee"):
        print(query.lastError().text())
    if not query.exec_("create table employee(id int primary key, first_name varchar(10), "\
                "last_name varchar(10), posistion int, pass_hash varchar(200))"):
        print(query.lastError().text())
    if not query.exec_("insert into employee values({}, '{}', '{}', {}, '{}')".\
                format(162973, 'Jon', 'Michie', 2, pbk.hash('Michie'))):
        print(query.lastError().text())
    query.exec_("insert into employee values({}, '{}', '{}', {}, '{}')".\
                format(131901, 'Ben', 'Terry', 3, pbk.hash('Terry')))
    query.exec_("insert into employee values({}, '{}', '{}', {}, '{}')".\
                format(150697, 'Daniel', 'Silva', 2, pbk.hash('Silva')))
    query.exec_("insert into employee values({}, '{}', '{}', {}, '{}')".\
                format(68412, 'James', 'Hutchetson', 2, pbk.hash('Hutchetson')))
    query.exec_("insert into employee values({}, '{}', '{}', {}, '{}')".\
                format(161844, 'MacKenly', 'Gamble', 1, pbk.hash('Gamble')))
    query.exec_("insert into employee values({}, '{}', '{}', {}, '{}')".\
                format(141047, 'George', 'Huston', 1, pbk.hash('Huston')))
    query.exec_("insert into employee values({}, '{}', '{}', {}, '{}')".\
                format(46045, 'Arthur', 'Art', 1, pbk.hash('Art')))


def testHashVerification(name):
    db = QSqlDatabase.addDatabase('QSQLITE')
    db.setDatabaseName(DB_PATH)
    if not db.open():
        print('DB could not be opened')
        error = QSqlDatabase.lastError()
        print(error.text())
        return False
    query = QSqlQuery()
    if not query.exec_("select pass_hash from employee where last_name = '{}'".format(name)):
        print(query.lastError().text())
    elif not query.next():
        print('Table values not found')
    else:
        pass_hash = query.value(0)

        if pbk.verify(name, pass_hash):
            print('It\'s a match!')
        else:
            print('Match not found.')


if __name__ == '__main__':

    generateItems()
    generatePO()
    fillDB()
    createEmployeeTable()
    testHashVerification('Terry')

[((11, 10, 11, 54), 'os.path.join', 'os.path.join', ({(11, 23, 11, 37): 'os.path.curdir', (11, 39, 11, 53): '"""inventory.db"""'}, {}), "(os.path.curdir, 'inventory.db')", False, 'import os\n'), ((17, 4, 17, 29), 'random.shuffle', 'random.shuffle', ({(17, 19, 17, 28): 'word_list'}, {}), '(word_list)', False, 'import random\n'), ((29, 15, 29, 75), 'requests.get', 'requests.get', ({(29, 28, 29, 74): '"""https://www.randomlists.com/data/things.json"""'}, {}), "('https://www.randomlists.com/data/things.json')", False, 'import requests\n'), ((114, 9, 114, 44), 'PyQt5.QtSql.QSqlDatabase.addDatabase', 'QSqlDatabase.addDatabase', ({(114, 34, 114, 43): '"""QSQLITE"""'}, {}), "('QSQLITE')", False, 'from PyQt5.QtSql import QSqlDatabase, QSqlQuery\n'), ((122, 12, 122, 23), 'PyQt5.QtSql.QSqlQuery', 'QSqlQuery', ({}, {}), '()', False, 'from PyQt5.QtSql import QSqlDatabase, QSqlQuery\n'), ((170, 9, 170, 44), 'PyQt5.QtSql.QSqlDatabase.addDatabase', 'QSqlDatabase.addDatabase', ({(170, 34, 170, 43): '"""QSQLITE"""'}, {}), "('QSQLITE')", False, 'from PyQt5.QtSql import QSqlDatabase, QSqlQuery\n'), ((177, 12, 177, 23), 'PyQt5.QtSql.QSqlQuery', 'QSqlQuery', ({}, {}), '()', False, 'from PyQt5.QtSql import QSqlDatabase, QSqlQuery\n'), ((201, 9, 201, 44), 'PyQt5.QtSql.QSqlDatabase.addDatabase', 'QSqlDatabase.addDatabase', ({(201, 34, 201, 43): '"""QSQLITE"""'}, {}), "('QSQLITE')", False, 'from PyQt5.QtSql import QSqlDatabase, QSqlQuery\n'), ((208, 12, 208, 23), 'PyQt5.QtSql.QSqlQuery', 'QSqlQuery', ({}, {}), '()', False, 'from PyQt5.QtSql import QSqlDatabase, QSqlQuery\n'), ((43, 8, 43, 25), 'random.seed', 'random.seed', ({(43, 20, 43, 24): 'item'}, {}), '(item)', False, 'import random\n'), ((44, 14, 44, 56), 'random.randint', 'random.randint', ({(44, 29, 44, 41): '100000000000', (44, 43, 44, 55): '999999999999'}, {}), '(100000000000, 999999999999)', False, 'import random\n'), ((45, 14, 45, 43), 'random.randint', 'random.randint', ({(45, 29, 45, 33): '1000', (45, 35, 45, 42): '9999999'}, {}), '(1000, 9999999)', False, 'import random\n'), ((56, 8, 56, 26), 'json.dump', 'json.dump', ({(56, 18, 56, 22): 'data', (56, 24, 56, 25): 'f'}, {}), '(data, f)', False, 'import json\n'), ((101, 8, 101, 29), 'json.dump', 'json.dump', ({(101, 18, 101, 25): 'po_dict', (101, 27, 101, 28): 'f'}, {}), '(po_dict, f)', False, 'import json\n'), ((112, 15, 112, 27), 'json.load', 'json.load', ({(112, 25, 112, 26): 'f'}, {}), '(f)', False, 'import json\n'), ((118, 16, 118, 40), 'PyQt5.QtSql.QSqlDatabase.lastError', 'QSqlDatabase.lastError', ({}, {}), '()', False, 'from PyQt5.QtSql import QSqlDatabase, QSqlQuery\n'), ((143, 18, 143, 30), 'json.load', 'json.load', ({(143, 28, 143, 29): 'f'}, {}), '(f)', False, 'import json\n'), ((157, 22, 157, 55), 'json.dumps', 'json.dumps', ({(157, 33, 157, 54): "po_dict[key]['items']"}, {}), "(po_dict[key]['items'])", False, 'import json\n'), ((174, 16, 174, 40), 'PyQt5.QtSql.QSqlDatabase.lastError', 'QSqlDatabase.lastError', ({}, {}), '()', False, 'from PyQt5.QtSql import QSqlDatabase, QSqlQuery\n'), ((205, 16, 205, 40), 'PyQt5.QtSql.QSqlDatabase.lastError', 'QSqlDatabase.lastError', ({}, {}), '()', False, 'from PyQt5.QtSql import QSqlDatabase, QSqlQuery\n'), ((69, 25, 69, 37), 'json.load', 'json.load', ({(69, 35, 69, 36): 'f'}, {}), '(f)', False, 'import json\n'), ((78, 28, 78, 53), 'random.randint', 'random.randint', ({(78, 43, 78, 44): '(1)', (78, 46, 78, 52): '(999999)'}, {}), '(1, 999999)', False, 'import random\n'), ((81, 82, 81, 104), 'random.choice', 'random.choice', ({(81, 96, 81, 103): 'vendors'}, {}), '(vendors)', False, 'import random\n'), ((187, 50, 187, 67), 'passlib.hash.pbkdf2_sha256.hash', 'pbk.hash', ({(187, 59, 187, 66): '"""Terry"""'}, {}), "('Terry')", True, 'from passlib.hash import pbkdf2_sha256 as pbk\n'), ((189, 53, 189, 70), 'passlib.hash.pbkdf2_sha256.hash', 'pbk.hash', ({(189, 62, 189, 69): '"""Silva"""'}, {}), "('Silva')", True, 'from passlib.hash import pbkdf2_sha256 as pbk\n'), ((191, 56, 191, 78), 'passlib.hash.pbkdf2_sha256.hash', 'pbk.hash', ({(191, 65, 191, 77): '"""Hutchetson"""'}, {}), "('Hutchetson')", True, 'from passlib.hash import pbkdf2_sha256 as pbk\n'), ((193, 56, 193, 74), 'passlib.hash.pbkdf2_sha256.hash', 'pbk.hash', ({(193, 65, 193, 73): '"""Gamble"""'}, {}), "('Gamble')", True, 'from passlib.hash import pbkdf2_sha256 as pbk\n'), ((195, 54, 195, 72), 'passlib.hash.pbkdf2_sha256.hash', 'pbk.hash', ({(195, 63, 195, 71): '"""Huston"""'}, {}), "('Huston')", True, 'from passlib.hash import pbkdf2_sha256 as pbk\n'), ((197, 50, 197, 65), 'passlib.hash.pbkdf2_sha256.hash', 'pbk.hash', ({(197, 59, 197, 64): '"""Art"""'}, {}), "('Art')", True, 'from passlib.hash import pbkdf2_sha256 as pbk\n'), ((216, 11, 216, 38), 'passlib.hash.pbkdf2_sha256.verify', 'pbk.verify', ({(216, 22, 216, 26): 'name', (216, 28, 216, 37): 'pass_hash'}, {}), '(name, pass_hash)', True, 'from passlib.hash import pbkdf2_sha256 as pbk\n'), ((96, 28, 96, 49), 'random.randint', 'random.randint', ({(96, 43, 96, 44): '1', (96, 46, 96, 48): '20'}, {}), '(1, 20)', False, 'import random\n'), ((184, 51, 184, 69), 'passlib.hash.pbkdf2_sha256.hash', 'pbk.hash', ({(184, 60, 184, 68): '"""Michie"""'}, {}), "('Michie')", True, 'from passlib.hash import pbkdf2_sha256 as pbk\n')]