import random

from dotenv import load_dotenv

from src.app.database.utility import SqlUtility


load_dotenv()


def getUserTuples():
    userRequest = """SELECT U.uuid FROM users U;"""
    userTuplesFound = SqlUtility.executeSelectRequest(userRequest)

    if len(userTuplesFound) == 0:
        raise ValueError('No tuples founds')

    return userTuplesFound


def getStockTuples():
    requestString = """SELECT S.ticker, S.suffix, S.regular_market_price FROM stocks S;"""
    stockTuplesFound = SqlUtility.executeSelectRequest(requestString)

    if len(stockTuplesFound) == 0:
        raise ValueError('No tuples founds')

    return stockTuplesFound


def addStocksInUsersWatchlist(users, stocks):
    stockTupleLength = len(stocks)
    insertionCounter = 0

    for userTuple in users:
        randomNumberOfStocks = random.randrange(0, 10)
        for i in range(randomNumberOfStocks):
            try:
                randomStockIndex = random.randrange(0, stockTupleLength - 1)
                pickedStock = stocks[randomStockIndex]

                watchQuery = f"""INSERT INTO watch VALUES ('{userTuple[0]}', '{pickedStock[0]}', '{pickedStock[1]}', {pickedStock[2]})"""
                SqlUtility.executeInsertRequest(watchQuery)
                insertionCounter += 1

            except Exception:
                print('error!')

    return insertionCounter


# userTuples = getUserTuples()
# stockTuples = getStockTuples()
# tuplesInserted = addStocksInUsersWatchlist(userTuples, stockTuples)
# print(tuplesInserted)

import random

from dotenv import load_dotenv

from src.app.database.utility import SqlUtility


load_dotenv()


def getUserTuples():
    userRequest = """SELECT U.uuid FROM users U;"""
    userTuplesFound = SqlUtility.executeSelectRequest(userRequest)

    if len(userTuplesFound) == 0:
        raise ValueError('No tuples founds')

    return userTuplesFound


def getStockTuples():
    requestString = """SELECT S.ticker, S.suffix, S.regular_market_price FROM stocks S;"""
    stockTuplesFound = SqlUtility.executeSelectRequest(requestString)

    if len(stockTuplesFound) == 0:
        raise ValueError('No tuples founds')

    return stockTuplesFound


def addStocksInUsersWatchlist(users, stocks):
    stockTupleLength = len(stocks)
    insertionCounter = 0

    for userTuple in users:
        randomNumberOfStocks = random.randrange(0, 10)
        for i in range(randomNumberOfStocks):
            try:
                randomStockIndex = random.randrange(0, stockTupleLength - 1)
                pickedStock = stocks[randomStockIndex]

                watchQuery = f"""INSERT INTO watch VALUES ('{userTuple[0]}', '{pickedStock[0]}', '{pickedStock[1]}', {pickedStock[2]})"""
                SqlUtility.executeInsertRequest(watchQuery)
                insertionCounter += 1

            except Exception:
                print('error!')

    return insertionCounter


# userTuples = getUserTuples()
# stockTuples = getStockTuples()
# tuplesInserted = addStocksInUsersWatchlist(userTuples, stockTuples)
# print(tuplesInserted)

SELECT U.uuid FROM users U; SELECT S.ticker, S.suffix, S.regular_market_price FROM stocks S; INSERT INTO watch VALUES ('{userTuple[0]}', '{pickedStock[0]}', '{pickedStock[1]}', {pickedStock[2]}) # userTuples = getUserTuples() # stockTuples = getStockTuples() # tuplesInserted = addStocksInUsersWatchlist(userTuples, stockTuples) # print(tuplesInserted)