from snowflake_connector.etl_connector import SnowflakeSQLExecutor
from snowflake_connector.etl_connector import SQLLoader

from config import SF_AWS_CONFIG

import csv

# Load SQL templates
sql_loader = SQLLoader(__file__)

# Setup scoped vars
CMS_ACCOUNTS = ['theorchardmusic', 'IODA']
PAGE_LIMIT = 2000
HEADER_ROW = ['asset_id','enable_content_id','match_policy','update_all_claims']

select_table = 'staging_raw_youtube_asset_report'
asset_count = 0
page_count = 0

# Define connection object
sf_executor = SnowflakeSQLExecutor(SF_AWS_CONFIG)

# Begin looping through accounts
for account_name in CMS_ACCOUNTS:

    sql_template = (sql_loader.load_query(
        'sf_select_' + account_name + '_' + 'assets').format(
            db=sf_executor.sf_config['db'],
            schema=sf_executor.sf_config['schema'],
            table=select_table))

    params_to_bind = {'cms_account': account_name}

    # Chunk and process responses
    for page in sf_executor.fetchmany(sql_template, PAGE_LIMIT, params_to_bind):

        row_count = 0

        for row in page:
            if (row_count/PAGE_LIMIT == 0):
                page_count += 1
                f = open('match_policy_update/'+account_name+'/match_policy_update_' +
                         account_name + '_' +
                         str(page_count).zfill(6) + '.csv', 'w')

                writer = csv.writer(f, delimiter=',', quotechar='\"', dialect='excel')

                writer.writerow(HEADER_ROW)

            writer.writerow(row)

            asset_count += 1
            row_count += 1