"""Script for generating SQL and executing LOAD of Analytics Data"""

import os

import config


cnx = config.credentials.cursor()

LOAD_QUERIES_PATH = 'queries/load_queries/'

FA_LOAD_STRING = "(fa.processeddaytime = '{processeddaytime}' " \
                "and fa.storeid = '{storeid}') \n OR "
FA_ERROR_LOAD_STRING = "(thisrow_max_fact_processeddaytime = " \
                      "'{processeddaytime}' and storeid = '{storeid}') \n OR "


def construct_load_statement(dates_stores_to_reprocess_df):
    """Create format for SQL dates/stores to load.


        Args:
            dates_stores_to_reprocess_df (dataframe): Trimmed Dataframe of
                dates/stores to either delete and or load

        Returns:
            List with two values, one is SQL for fact_analytics load and one for
                fact_analytics_error SQL
    """
    dates_stores_to_load_fa = ""
    dates_stores_to_load_fa_error = ""

    for index, row in dates_stores_to_reprocess_df.iterrows():
        if row['TABLE_SOURCE'] == 'fact_analytics':
            storeid = int(row['FA_STOREID'])
            processeddaytime = row['FA_PROCESSEDDAYTIME']
            dates_stores_to_load_fa += FA_LOAD_STRING \
                .format(storeid=storeid, processeddaytime=processeddaytime)

        else:
            storeid = int(row['FA_STOREID'])
            processeddaytime = row['FA_PROCESSEDDAYTIME']
            dates_stores_to_load_fa_error += FA_ERROR_LOAD_STRING \
                .format(storeid=storeid, processeddaytime=processeddaytime)

    dates_to_load = [dates_stores_to_load_fa[:-6], dates_stores_to_load_fa_error[:-6]]

    return dates_to_load


def load_dates_stores(constructed_load):
    """Execute load of dates/stores

        Args:
            constructed_load: Formatted SQL string to input
    """
    load_ds_queries = os.listdir(LOAD_QUERIES_PATH)

    for load_ds_query in load_ds_queries:
        print("\nNow running {load_ds_query}".format(load_ds_query=str(load_ds_query)))

        if load_ds_query[3:9] == 'format':
            sql = config.load_query(load_ds_query, LOAD_QUERIES_PATH)\
                .format(fa_load=constructed_load[0],
                        fa_error_load=constructed_load[1])
            cnx.execute(sql)

        else:
            sql = config.load_query(load_ds_query, LOAD_QUERIES_PATH)

            cnx.execute(sql)