"""YouTube specific Snowflake executor class."""
from datetime import datetime

from snowflake import connector
from snowflake_connector.etl_connector import SQLLoader

from feed_ingestion.common.base_executor import SnowflakeAWSExecutor

# Load SQL templates
sql_loader = SQLLoader(__file__)


class YouTubeExecutor(SnowflakeAWSExecutor):
    """Helper class to abstract common YouTube specific operations."""

    @property
    def date_col(self):
        """Name of the date column in staging raw table.

        Returns:
            str: date column name
        """
        return 'download_date'

    @property
    def channel_names_table(self):
        """Name of the channel name mapping table.

        Returns:
            str: channel mapping table name.
        """
        return 'dim_youtube_channel_names'

    @property
    def staging_raw_table(self):
        """Name of the staging_raw table for the feed.

        Returns:
            str: staging_raw_{feed} table name.
        """
        raise NotImplementedError()

    @property
    def channel_owner(self):
        """Name of the channel owner.

        Returns:
            str: channel owner.
        """
        return ''

    def update_channel_names_table(self, date):
        """Update dim_claim table."""
        return self.execute_query(
            sql_loader, 'update_channel_names_table', params=dict(
                db=self.sf_config['db'],
                schema=self.sf_config['schema'],
                channel_names_table=self.channel_names_table,
                staging_raw_table=self.staging_raw_table,
                date_col=self.date_col,
                download_date=date,
                channel_owner=self.channel_owner))

    def cleanup_duplicated_channel_names(self):
        """Cleanup duplicated channel names.

        The tasks uses it's own connection, because it's crucial
        to perform delete/insert in a single transaction.
        """
        def load_query(query_name, dup_table):
            """Load query from the file.

            Args:
                query_name (str): Query name.
                dup_table (str): Channels duplicates table name.

            Returns:
                (str, dict): Tuple of query string and params dictionary.
            """
            sql_template = sql_loader.load_query(query_name)

            sql, non_identifier_params = self.validator.format_identifiers(
                sql_template, params=dict(
                    db=self.sf_config['db'],
                    schema=self.sf_config['schema'],
                    dup_table=dup_table,
                    channel_names_table=self.channel_names_table))

            return sql, non_identifier_params

        dup_table = (
            f'dim_youtube_channel_names_duplicates_'
            f'{datetime.now().strftime("%Y_%m_%d_%H_%M_%S")}')
        query_list = [
            'create_channel_duplicates_table',
            'delete_duplicated_channels',
            'insert_deduplicated_channels',
            'drop_channel_duplicates_table']

        with connector.connect(
                user=self.sf_config.get('user', None),
                password=self.sf_config.get('password', None),
                account=self.sf_config['account'],
                warehouse=self.sf_config['warehouse'],
                database=self.sf_config['db'],
                schema=self.sf_config['schema'],
                role=self.sf_config['role'],
                private_key=self.sf_config.get('private_key', None),
                autocommit=False) as con:
            con.cursor().execute('BEGIN TRANSACTION;')

            for query in query_list:
                sql, params = load_query(query, dup_table)
                con.cursor().execute(sql, params=params)