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

# Load SQL templates
sql_loader = SQLLoader(__file__)


class SnowflakeSQLExecutorGDA(SnowflakeSQLExecutor):

    def get_count_from_audit_event_gda_submit_application(self, load_test_uuid):
        """Get count of events from facts.qa.audit_event_gda_submit_application.
        """
        sql_template = sql_loader.load_query(
            'get_count_from_audit_event_gda_submit_application')
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'],
            load_test_uuid=load_test_uuid)
        sql, non_identifier_params = self.validator.format_identifiers(
            sql_template, params)
        return self.fetchone(sql, params=non_identifier_params)[0]

    def get_urls(self, limit=10):
        """Get Spotify and other urls which can be submitted to API.
        """
        sql_template = sql_loader.load_query('get_urls')
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'],
            limit=limit)
        sql, non_identifier_params = self.validator.format_identifiers(
            sql_template, params)
        return self.fetchall(sql, params=non_identifier_params)

    def mark_used_urls(self, chartmetric_ids):
        """Mark used URLs in order to not submit the same data.
        """
        sql_template = sql_loader.load_query('mark_used_urls')
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'],
            chartmetric_ids=chartmetric_ids)
        sql, non_identifier_params = self.validator.format_identifiers(
            sql_template, params)
        return self.fetchone(sql, params=non_identifier_params)[0]

    def populate_table(self):
        """Populate table gda_test_data.
        """
        print('creating fs_aggregated...')
        sql_template = sql_loader.load_query('create_fs_aggregated')
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'])
        sql, non_identifier_params = self.validator.format_identifiers(
            sql_template, params)
        self.execute(sql, params=non_identifier_params)

        print('creating gda_test_data...')
        sql_template = sql_loader.load_query('create_gda_test_data')
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'])
        sql, non_identifier_params = self.validator.format_identifiers(
            sql_template, params)
        self.execute(sql, params=non_identifier_params)

        print('inserting spotify urls...')
        sql_template = sql_loader.load_query('insert_spotify_urls')
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'])
        sql, non_identifier_params = self.validator.format_identifiers(
            sql_template, params)
        self.execute(sql, params=non_identifier_params)

        print('inserting youtube urls...')
        sql_template = sql_loader.load_query('update_youtube_urls')
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'])
        sql, non_identifier_params = self.validator.format_identifiers(
            sql_template, params)
        self.execute(sql, params=non_identifier_params)

        print('inserting tiktok urls...')
        sql_template = sql_loader.load_query('update_tiktok_urls')
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'])
        sql, non_identifier_params = self.validator.format_identifiers(
            sql_template, params)
        self.execute(sql, params=non_identifier_params)