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
    '''

    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')

    return all(store_data_complete.values())

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):

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

    import yaml, subprocess

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

    for key in data_loaded.keys():
        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}}}\''

        subprocess.run(dbt_job, shell=True)

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 AS ('

    table_names=list(data_loaded.keys())

    for key in table_names[:-1]:
        sql+='SELECT * FROM intelligence.prod.'+data_loaded[key]['table_name']+' UNION ALL '
    sql+='SELECT * FROM intelligence.prod.'+data_loaded[table_names[-1]]['table_name']+')'

    cursor.execute(sql)

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

    cursor.execute(sql)

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

    if check_store_availability([1,286], date, config):

        kill_existing_tables(config, 'top_content_config.yml')

        daily_top_content_report_generation('top_content_config.yml')

        daily_top_content_table_insertion(config, 'top_content_config.yml')

        print('Success!!!')

    else:
        print('Stores are showing incomplete data for: '+ date)