"""Execute SQL queries to transform processed dig sales.

In series, this will set multiple fields' values on a processed sales row
by referencing values from related snowflake tables.

Some validation is performed prior to modifying live data.
In the case of failed validation, an Exception will be raised.

"""

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

from royalties import config

QUERIES = [
    '1_set_artist_genre_imprint_label',
    '2_set_subaccount',
    '3_set_country',
    '4_set_transaction_type',
    '5_set_catalog',
    '6_set_isrc',
    '7_set_track',
    '8_set_imprint',
    '9_set_imprint_no_subaccount',
    '10_set_track_isrc_upc',
    '11_set_track_upc_cd_track',
    '12_set_payout_currency_id',
    '13_set_original_currency_id',
    '14_set_activity_period_id',
    '15_set_isrc_upc_cd_track',
]

PROD_DATA_QUERIES = [
    '16_insert_select_into_fact_sales',
]


class snowflakeSalesImporter():
    """Importer."""

    def __init__(self):
        """Initialize loader."""
        self.sql_loader = False
        self.executor = False

    def s3_import(self):
        """Build data, verify it, and load it."""
        self._connect()

        print('Building staged data')
        for query_name in QUERIES:
            self.execute_a_query(query_name)

        print('Checking for staged statements already in fact sales.')
        self.select_an_error(
            'select_duplicate_statements',
            'Duplicate statement_detail_ids found in staging and prod tables')

        print('Finding rows with missing data.')
        self.execute_a_query('17_insert_select_into_fact_sales_error')

        self.select_an_error(
            '18_check_fact_sales_error',
            'Found rows with missing data. See fact_sales_error')

        print('Updating production data')
        for query_name in PROD_DATA_QUERIES:
            self.execute_a_query(query_name)

        print('Inserting new stores')
        self.execute_a_query('19_insert_new_stores')

        print('Complete')

    def _connect(self):
        """Establish a connection."""
        if not self.sql_loader:
            self.sql_loader = SQLLoader(__file__)
            self.executor = SnowflakeSQLExecutor(config.sf_config)

    def execute_a_query(self, query_name):
        """Execute the given sql file."""
        print('Executing: {}'.format(query_name))
        self.executor.execute_query(
            self.sql_loader, query_name, {
                'db': config.sf_config['db'],
                'schema': config.sf_config['schema'],
                'period_id': config.period_id,
                's3_path': config.s3_path,
                'table_name': config.staging_table,
                'aws_key_id': config.aws['access_key'],
                'aws_secret_key': config.aws['access_secret']
            }
        )

    def select_an_error(self, query_name, msg):
        """Execute sql that should return null sets if everything is good."""
        print('Executing: {}'.format(query_name))
        err_count, *_ = self.executor.fetchone_query(
            self.sql_loader, query_name, {
                'db': config.sf_config['db'],
                'schema': config.sf_config['schema'],
                'table_name': config.staging_table,
            }
        )
        if err_count > 0:
            raise Exception('{msg} | Rows affected: {count}'.format(
                count=err_count, msg=msg))


if __name__ == '__main__':
    importer = snowflakeSalesImporter()
    importer.s3_import()