import config
import yaml
import subprocess
import snowflake.connector
from datetime import datetime, timedelta
import pandas as pd


def connect_to_snowflake(config):

    """
    Basic function to connect to snowflake. Requires a config contaning
    connection credentials
    """

    import snowflake.connector

    connection = snowflake.connector.connect(
        user=config.user,
        password=config.password,
        account=config.account,
        warehouse=config.warehouse,
        role=config.role,
        database=config.database,
        schema=config.schema,
    )

    return connection.cursor()


def get_store_availability(config):

    """
    Function returns a DataFrame corresponding, by store, days of complete analytics data

    """

    cursor = connect_to_snowflake(config)

    sql = """
    SELECT
        storeid,
        download_activity_date AS day_of_complete_data
    FROM facts.prod.data_availability_by_store_daily
    WHERE missing_date IS  NULL AND incomplete_date IS  NULL AND duplicated_date IS NULL
    ORDER BY storeid ASC

    """

    results = cursor.execute(sql)
    df = pd.DataFrame(results, columns=[x[0].lower() for x in results.description])
    df["day_of_complete_data"] = pd.to_datetime(df["day_of_complete_data"])
    return df


def check_store_availability(stores, date, config):

    """
    Inputs: stores- list of store ids
    date: string date you wish to check
    Returns True if all stores have complete data for the date you input
    """

    feed_dict = {1: 4, 286: 1}

    df = get_store_availability(config)

    store_data_complete = {}

    for store in stores:

        data_complete = False
        storeid = store
        for x in df[df["storeid"] == storeid]["day_of_complete_data"].isin([date]):
            if x == True:
                data_complete = True

            store_data_complete[store] = data_complete

    for key in store_data_complete.keys():
        if not store_data_complete[key]:
            print("Store " + str(key) + " has incomplete data")

    good_stores = []
    for x in store_data_complete:
        if store_data_complete[x] == True:
            good_stores.append(x)

    good_feeds = []
    for x in feed_dict.keys():
        if x in good_stores:
            good_feeds.append(feed_dict[x])

    return good_feeds


def kill_existing_tables(config, yml_config):

    """
    This function connects to snowflake and drops all "temp" tables as outlined in the yaml config
    """

    import yaml

    cursor = connect_to_snowflake(config)

    with open(yml_config, "r") as stream:
        data_loaded = yaml.safe_load(stream)

    sql = "DROP TABLE IF EXISTS intelligence.prod."
    for key in data_loaded.keys():
        table_name = data_loaded[key]["table_name"]
        cursor.execute(sql + table_name)

    cursor.execute("DROP TABLE IF EXISTS intelligence.prod.daily_top_content_temp")


def daily_top_content_report_generation(yml_config, stores, date, config):

    """
    This function connects to snowflake and creates daily top content reports for stores/territories
    outlined in the yaml config
    """

    import yaml, subprocess

    good_feeds = check_store_availability(stores, date, config)

    print(good_feeds)

    with open(yml_config, "r") as stream:
        data_loaded = yaml.safe_load(stream)

    for key in data_loaded.keys():
        if data_loaded[key]["feedid"] in good_feeds:
            feedid = data_loaded[key]["feedid"]
            store_name = data_loaded[key]["store_name"]
            country_name = data_loaded[key]["country_name"]
            trending_tracks_country_name = data_loaded[key][
                "trending_tracks_country_name"
            ]
            table_name = data_loaded[key]["table_name"]

            dbt_job = f"dbt run --models daily_top_content --target prod --profiles-dir ./profiles/sf_pswd_auth --vars '{{feedid: {feedid},store_name: {store_name}, country_name: {country_name}, trending_tracks_country_name: {trending_tracks_country_name}, table_name: {table_name}}}'"

            # print(dbt_job)
            subprocess.run(dbt_job, shell=True)
        else:
            pass


def daily_top_content_table_insertion(config, yml_config):

    """
    This function unions all daily top content reports and inserts into the master table
    """

    import yaml

    cursor = connect_to_snowflake(config)

    with open(yml_config, "r") as stream:
        data_loaded = yaml.safe_load(stream)

    sql = """CREATE OR REPLACE TABLE intelligence.prod.daily_top_content_temp
        (STORE VARCHAR,
         COUNTRY VARCHAR,
         LABELID NUMBER,
         ARTISTID NUMBER,
         TRACKID NUMBER,
         MOST_RECENT_DAY_STREAMS NUMBER,
         CURRENT_WEEK_STRREAMS NUMBER,
         LAST_WEEK_STREAMS NUMBER,
         LAST_FULL_WEEK_STREAMS NUMBER,
         LAST_12MONTH_STREAMS NUMBER,
         WEEKLY_TREND FLOAT,
         TRENDING_DATE DATE
        )

    """
    table_names = list(data_loaded.keys())

    cursor.execute(sql)

    for key in table_names:

        sql = (
            "INSERT INTO intelligence.prod.daily_top_content_temp ( SELECT * FROM intelligence.prod."
            + data_loaded[key]["table_name"]
            + ")"
        )

        try:

            cursor.execute(sql)
        except:
            continue

    sql = "INSERT INTO intelligence.prod.daily_top_content SELECT * FROM intelligence.prod.daily_top_content_temp"

    try:
        cursor.execute(sql)
    except:
        pass


if __name__ == "__main__":
    date = datetime.strftime((datetime.now() - timedelta(days=2)), "%Y-%m-%d")

    kill_existing_tables(config, "top_content_config.yml")

    daily_top_content_report_generation(
        "top_content_config.yml", [1, 286], date, config
    )

    daily_top_content_table_insertion(config, "top_content_config.yml")

    print("Success!!!")