import csv
import logging
import os
import snowflake.connector
import json

# SNOWFLAKE credentials from environment variables
# sf_user = os.environ.get("SNOWFLAKE_USER")
# sf_password = os.environ.get("SNOWFLAKE_PASSWORD")
# sf_account = os.environ.get("SNOWFLAKE_ACCOUNT")
# sf_database = os.environ.get("SNOWFLAKE_DATABASE")

with open('config.json', 'r') as f:
    config = json.load(f)
sf_user = config['snowflake']["user"]
sf_password = config['snowflake']["password"]
sf_account = config['snowflake']["account"]
sf_database = config['snowflake']["database"]
sf_warehouse = config['snowflake']["warehouse"]

### get query data in dictionary format
def snowflake_fetch_data(sql_query):
    result_dict = []
    ctx = snowflake.connector.connect(
        user=sf_user,
        password=sf_password,
        account=sf_account,
        warehouse=sf_warehouse,
        database=sf_database
    )
    cs = ctx.cursor(snowflake.connector.DictCursor)
    try:
        cs.execute(sql_query)
        result_dict = cs.fetchall()
    except Exception as e:
        logging.error(e)
    finally:
        cs.close()
    ctx.close()
    return result_dict


### transform dictionary to csv file
def dict_to_csv(file_name: str, data_dict: list) -> None:
    sorted_sf_dict = sorted(data_dict, key=lambda row: row['ARTIST_NAME'].lower())
    columns = list(sorted_sf_dict[0].keys())
    with open(file_name, 'w', newline='') as csv_file:
        writer = csv.DictWriter(csv_file, fieldnames=columns, delimiter=",")
        writer.writeheader()
        writer.writerows(sorted_sf_dict)


def check_days_lag(data_dict: list) -> dict:
    ignored_users = [
        {'CM_ARTIST': 38204, 'network': ['FB_LAG', 'IG_LAG', 'TWITTER_LAG']},
        {'CM_ARTIST': 4994, 'network': ['FB_LAG', 'TWITTER_LAG']},
        {'CM_ARTIST': 20003, 'network': ['FB_LAG']},
        {'CM_ARTIST': 259605, 'network': ['FB_LAG']},
        {'CM_ARTIST': 1399531, 'network': ['FB_LAG']},
        {'CM_ARTIST': 3295565, 'network': ['IG_LAG']},
        {'CM_ARTIST': 884166, 'network': ['TWITTER_LAG']},
        {'CM_ARTIST': 906183, 'network': ['TWITTER_LAG']},
        {'CM_ARTIST': 1099766, 'network': ['TWITTER_LAG']}
    ]
    ignored_users_days_lag = []
    ignored_users_cm_artist_list = [x['CM_ARTIST'] for x in ignored_users]
    artists_lags = {}
    for row in data_dict:
        # collect data regarding the ignored Artists
        if row['CM_ARTIST'] in ignored_users_cm_artist_list:
            ignored_users_days_lag.append(row)
            continue
        else:
            artists_lags[row['CM_ARTIST']] = {'ARTIST_NAME': '', 'lags': {}}

            if isinstance(row['FB_LAG'], int) and 19 >= row['FB_LAG'] >= 5:
                artists_lags[row['CM_ARTIST']]['ARTIST_NAME'] = row['ARTIST_NAME']
                artists_lags[row['CM_ARTIST']]['lags']['FB_LAG'] = f"{row['FB_LAG']} days"
            if isinstance(row['IG_LAG'], int) and 19 >= row['IG_LAG'] >= 5:
                artists_lags[row['CM_ARTIST']]['ARTIST_NAME'] = row['ARTIST_NAME']
                artists_lags[row['CM_ARTIST']]['lags']['IG_LAG'] = f"{row['IG_LAG']} days"
            if isinstance(row['TWITTER_LAG'], int) and 19 >= row['TWITTER_LAG'] >= 5:
                artists_lags[row['CM_ARTIST']]['ARTIST_NAME'] = row['ARTIST_NAME']
                artists_lags[row['CM_ARTIST']]['lags']['TWITTER_LAG'] = f"{row['TWITTER_LAG']} days"

    artists_lags = {cm_artist: value for cm_artist, value in artists_lags.items() if value['lags']}
    return artists_lags


if __name__ == '__main__':
    pass