from datetime import datetime

from ssavva.sos_snowflake_performance import snowflake_db, config, consts

_sql_loader = snowflake_db.SQLLoader(sql_files_root='queries')


def get_vendors():
    vendors = []
    with snowflake_db.SnowflakeSQLExecutor(
            config.SNOWFLAKE_CONFIG) as sf_executor:
        sql = _sql_loader.load_query('vendors')
        labels_data = sf_executor.fetchall(sql)
        vendors.extend(
            [{'title': 'large label', 'labelid': v[0], 'subaccountid': None}
             for v in labels_data[0:5]])
        vendors.extend(
            [{'title': 'big label', 'labelid': v[0], 'subaccountid': None}
             for v in labels_data[100:105]])
        vendors.extend(
            [{'title': 'mid label', 'labelid': v[0], 'subaccountid': None}
             for v in labels_data[2500:2505]])
        vendors.extend(
            [{'title': 'small label', 'labelid': v[0], 'subaccountid': None}
             for v in labels_data[5000:5005]])

        sql = _sql_loader.load_query('subaccounts')
        subaccounts_data = sf_executor.fetchall(sql)
        vendors.extend(
            [{'title': 'large subaccount', 'labelid': v[0],
              'subaccountid': v[1]}
             for v in subaccounts_data[0:5]])
        vendors.extend(
            [{'title': 'big subaccount', 'labelid': v[0], 'subaccountid': v[1]}
             for v in subaccounts_data[100:105]])
        vendors.extend(
            [{'title': 'mid subaccount', 'labelid': v[0], 'subaccountid': v[1]}
             for v in subaccounts_data[2500:2505]])
        vendors.extend(
            [{'title': 'small subaccount', 'labelid': v[0],
              'subaccountid': v[1]}
             for v in subaccounts_data[5000:5005]])


    return vendors

def param_generator():
    vendors = get_vendors()

    date_ranges = [
        {'title': '1 year', 'start_date': '2016-10-01',
         'end_date': '2017-10-01'},
        {'title': '6 months', 'start_date': '2017-04-01',
         'end_date': '2017-10-01'},
        {'title': '1 month', 'start_date': '2017-09-01',
         'end_date': '2017-10-01'},
        {'title': '1 week', 'start_date': '2017-09-01',
         'end_date': '2017-09-07'},
    ]
    for vendor in vendors:
        for date_range in date_ranges:
            yield {
                'title': ' :: '.join([vendor['title'], date_range['title']]),
                'labelid': vendor['labelid'],
                'subaccountid': vendor['subaccountid'],
                'start_date': date_range['start_date'],
                'end_date': date_range['end_date']}


def get_placements_sql(sql_template, params):
    return sql_template.format(
        subaccountid_column=(
            'subaccountid'
            if params.get('subaccountid') else
            'null as subaccountid'),
        subaccount_clause=(
            'and subaccountid = %(subaccountid)s'
            if params.get('subaccountid') else
            ''),
        subaccountid_column_group=(
            'subaccountid'
            if params.get('subaccountid') else
            'null'),
        subaccount_track_join=(
            'and track_data.subaccountid is null'
            if params.get('subaccountid') else
            'and sos.subaccountid = track_data.subaccountid'),
        storeid_clause=(
            'and storeid = %(storeid)s'
            if params.get('storeid') else
            ''),
        isrcs_clause=(
            'and isrc in (%(isrcs)s)'
            if params.get('isrcs') else
            ''),
    )


def run(query_name):
    api_connector = snowflake_db.SnowflakeMetadataConnector()
    api_connector.authenticate()

    sql_template = _sql_loader.load_query(query_name)

    executions_list = []

    with snowflake_db.SnowflakeSQLExecutor(
            config.SNOWFLAKE_CONFIG) as sf_executor:
        # warm_up_sql = sql_loader.load_query('placements_data_warm_up')
        # sf_executor.execute(warm_up_sql)

        for params in param_generator():
            start_time = datetime.now()
            sql = get_placements_sql(sql_template, params)
            result = sf_executor.execute(sql, params)
            total_time = (datetime.now() - start_time).total_seconds()

            execution_data = api_connector.get_query_data(result.sfqid)['queries'][0]
            execution = {
                'query': query_name,
                'link': consts.sf_query_link.format(execution_data['id']),
                # 'row_count': execution_data['stats']['producedRows'],
                'sf_execution_time': execution_data['totalDuration'] / 1000,
                'total_execution': total_time,
                'query_params': params
            }
            executions_list.append(execution)
            print('progress')

    return executions_list


if __name__ == '__main__':
    res = run('placements_data')
    print(consts.report_header)
    for r in res:
        s = consts.row_template.format(
            query_name=r['query'],
            exec_title=r['query_params']['title'],
            sf_time=r['sf_execution_time'],
            total_time=r['total_execution'],
            labelid=r['query_params']['labelid'],
            subaccountid=r['query_params']['subaccountid'],
            start_date=r['query_params']['start_date'],
            end_date=r['query_params']['end_date'],
            link=r['link']
        )
        print(s)