import datetime
from typing import List, Union, Type
from dbConnectionClass import DBConnectionSingleton
from sqlalchemy.orm import load_only
from database.ormDeclarations import SessionInfo, ProductsStaticInfo, ProductPriceHistory, productsViewDeclaration, Base
from database.dbFiles.dbFilesOperations import readAndConvertCSVToDbFormatDf
import pandas as pd
import gc
import glob
import os
import re


def bulkInsertToDBFromDf(tableClass: Union[Type[ProductPriceHistory], Type[ProductsStaticInfo], Type[SessionInfo]],
                         dfIn: pd.DataFrame, columnNamesForInsert: List[str], batchSize: int = 100000):
    """
    :param tableClass: any of the tables declared at ormDeclarations. They are explicitly identified under Union[] to avoid confusion
    :param dfIn: dataframe. It has to contain all relevant columns (see columnNamesForInsert) that are inserted to the database
    :param columnNamesForInsert: these columns are inserted to the table
    :param batchSize: number of rows to insert per batch
    when batch_size = -1, the entire df is inserted in one batch
    :return:
    """
    assert all([col in list(dfIn) for col in columnNamesForInsert]), "Not all required columns exist in dataframe. Aborting. %s" % columnNamesForInsert

    if len(dfIn) == 0:
        print("\tSKIPPING: dataframe was empty")
        return

    dbConnection = DBConnectionSingleton()
    conn = dbConnection.engine.connect()
    if batchSize == -1:
        nBatches = 1
        batchSize = len(dfIn)
    else:
        nBatches = int(round((len(dfIn) / batchSize) + 0.5))  # must has at *least* one batch (if dfIn is not empty)

    t0 = datetime.datetime.now()
    dfAsDictRecords: List[dict] = dfIn[columnNamesForInsert].to_dict('records')
    for i in range(0, nBatches):
        start = i * batchSize
        end = min((i + 1) * batchSize, len(dfIn) + 1)
        conn.execute(
            tableClass.__table__.insert(),
            dfAsDictRecords[start:end]
        )
    t_end = datetime.datetime.now()

    # conn.execute('commit;')  # fixme: test if I really need this commit
    # print(f"\tTotal time: {round((t_end - t0).total_seconds()/60, 2)} minutes")
    conn.close()


def createDataTimeFromFilenameConvention(filename: str) -> str:
    """
    :param filename: filename contains date-time in the following convention: "'2020-01-07___04-42-18.csv.bz2'"
    :return: 'YYYY-mm-dd HH:MM:SS' as string
    """
    baseFname = str(os.path.basename(filename).split('.')[0])
    baseFname = re.sub(r'_+', '_', baseFname)
    creationDateAsString = baseFname.split('_')[0] + ' ' + baseFname.split('_')[1].replace('-', ':')
    return creationDateAsString


if __name__ == '__main__':
    # ***************************************************************************************************************************
    gc.enable()
    pd.set_option("display.max_rows", None)
    pd.set_option("display.max_columns", None)
    pd.set_option('display.width', None)
    # mysqlConfigPath = '../db_configs/sqlConfig.json'
    mysqlConfigPath = '../db_configs/admin_postgres.json'
    batchSize = -1
    dbConnection = DBConnectionSingleton(mysqlConfigPath)
    dbSessionCreator = dbConnection.createDbSession()
    dbSessionInstance = dbSessionCreator()
    conn = dbConnection.engine.connect()

    # dataFilesList = [file for file in glob.glob(r"../daily/*.csv.bz2")][0:9]
    # dataFilesList = [file for file in glob.glob(r"../daily/*.csv.bz2")][0:20]
    # dataFilesList = [file for file in glob.glob(r"../daily/*.csv.bz2")][0:10]
    dataFilesList = [file for file in glob.glob(r"G:/development/zapPrice/daily/*.csv.bz2")][0:10]
    #   dataFilesList = [r'../daily/2019-12-08___04-42-22.csv.bz2', r'../daily/2019-12-09___04-42-13.csv.bz2']

    # INSERT_PRICE_HISTOTY = False
    start_global = datetime.datetime.now()
    for i, dataFileToInsert in enumerate(dataFilesList):
        t0 = datetime.datetime.now()
        df = readAndConvertCSVToDbFormatDf(dataFileToInsert, nrows=None)
        print(f"Finished reading file #{i+1}/{len(dataFilesList)}: {dataFileToInsert}")

        # Tests inserts to three main tables
        creationDateAsString = createDataTimeFromFilenameConvention(filename=dataFileToInsert)
        newZapSession = SessionInfo(sessionType='fromFile', creationDate=creationDateAsString)
        dbSessionInstance.add(newZapSession)
        dbSessionInstance.flush()
        sessionId = newZapSession.sessionId
        dbSessionInstance.commit()

        # getting all currently known productIds
        # update ProductStaticInfo only with previously unknown productIds
        # fixme: querying this way is a bit of a hack. Find better ways to retrieve from db as different data types
        q = dbSessionInstance.query(ProductsStaticInfo).options(load_only(ProductsStaticInfo.productId))
        result = dbSessionInstance.execute(q).cursor.fetchall()
        productIdsSet = list(set(pd.DataFrame(result, columns=['productId'])['productId'].values))
        dfForProductStaticInfo = df[~df['productId'].isin(productIdsSet)]
        print("\tUpdating %s\tnew products on ProductStaticInfo" % len(dfForProductStaticInfo))

        if len(dfForProductStaticInfo) > 0:
            bulkInsertToDBFromDf(ProductsStaticInfo, dfIn=dfForProductStaticInfo, columnNamesForInsert=['productId', 'link', 'description'], batchSize=batchSize)

        df['sessionId'] = sessionId
        print("\tUpdating %s\tnew products on ProductPriceHistory" % len(df))
        bulkInsertToDBFromDf(ProductPriceHistory, dfIn=df, columnNamesForInsert=['sessionId', 'productId', 'min_price', 'max_price'], batchSize=batchSize)
        print(f"\tFile time: {round((datetime.datetime.now()-t0).total_seconds() / 60, 2)} minutes")

        del df
        gc.collect()

    print(f"\tGlobal Total time: {round((datetime.datetime.now()-start_global).total_seconds() / 60, 2)} minutes")
    # print(f"\nGlobal Total time: {start_global-datetime.datetime.now()} seconds\n")

    # TODO: write a function to iterate over all existing files and load them to db
    # TODO: maybe use insert/ignore later, once everything has been debugged thoroughly








