"""Snowflake connector for the Seated workflow."""
from snowflake_connector.etl_connector import SQLLoader

from feed_ingestion.common.staging_raw_sf.base_executor \
    import (SnowflakeSQLExecutorSR)

sql_loader = SQLLoader(__file__)


class SeatedSnowflakeExecutor(SnowflakeSQLExecutorSR):
    """Snowflake executor for Seated."""

    def clean_staging_raw_table(
            self, staging_raw_table, staging_raw_errors_table,
            file_name, date):
        """Delete rows from previous unsuccessful workflow run.

        Args:
            staging_raw_table (str): A table name in Snowflake.
            staging_raw_errors_table (str): A table name in Snowflake.
            file_name (str): A file names pattern.
            date (str): Date.
        """
        sql_template = sql_loader.load_query('delete_from_staging_raw')
        sql_template = sql_template.replace('%(file_name)s', file_name)
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'],
            staging_raw_table=staging_raw_table,
            date=date
        )
        sql_template_raw, non_identifier_params = (
            self.validator.format_identifiers(sql_template, params)
        )
        self.execute(sql_template_raw, params=non_identifier_params)

        params['staging_raw_table'] = staging_raw_errors_table
        sql_template_errors, non_identifier_params = (
            self.validator.format_identifiers(sql_template, params)
        )
        self.execute(sql_template_errors, params=non_identifier_params)

    def create_temp_staging_raw_table(self, report_name,
                                      temp_staging_raw_table):
        """Create temp staging raw table for json ingestion.

        Args:
            report_name (str): Report name from config.reports.
            temp_staging_raw_table (str): A table name in Snowflake.
        """
        sql_filename = f'create_temp_staging_raw_{report_name}'
        self.execute_query(
            sql_loader,
            query_name=sql_filename,
            params=dict(
                db=self.sf_config['db'],
                schema=self.sf_config['schema'],
                temp_staging_raw_table=temp_staging_raw_table,
            )
        )

    def assign_masking_on_temp_staging_raw_table(
        self, temp_staging_raw_table, pii_columns,
        masking_policy='SEATED_VARCHAR_MASK_ETL'
    ):
        """Assign a masking policy on temp staging raw table.

        Args:
            temp_staging_raw_table (str): A table name in Snowflake.
            pii_columns (list[str]): List of pii column names to be masked.
            masking_policy (str): The masking policy name.
        """
        sql_filename = 'assign_masking_policy'

        for column in pii_columns:
            self.execute_query(
                sql_loader,
                query_name=sql_filename,
                params=dict(
                    db=self.sf_config['db'],
                    schema=self.sf_config['schema'],
                    temp_staging_raw_table=temp_staging_raw_table,
                    column=column,
                    masking_policy=masking_policy
                )
            )

    def load_temp_staging_raw_table(self, temp_staging_raw_table,
                                    file_name, stage_name):
        """Load the temp_staging_raw table with csv data.

        Args:
            temp_staging_raw_table (str): A table name in Snowflake.
            file_name (str): A file names pattern.
            stage_name (str): Snowflake stage name.
        """
        sql_template = sql_loader.load_query('load_temp_staging_raw')
        sql_template = sql_template.replace('%(file_name)s', file_name)
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'],
            table_name=temp_staging_raw_table,
            stage=stage_name,
        )
        sql_template, non_identifier_params = (
            self.validator.format_identifiers(sql_template, params)
        )
        self.execute(sql_template, params=non_identifier_params)

    def create_stage(self, s3_dir_path, stage_name):
        """Create Snowflake stage.

        Args:
            s3_dir_path (str): s3 path.
            stage_name (str): Snowflake stage name.
        """
        sql_template = 'create_snowflake_stage'
        aws_params = self.get_aws_params()
        self.execute_query(
            sql_loader,
            query_name=sql_template,
            params=dict(
                db=self.sf_config['db'],
                schema=self.sf_config['schema'],
                stage=stage_name,
                s3_dir_path=s3_dir_path,
                **aws_params
            )
        )

    def drop_stage(self, stage_name):
        """Drop Snowflake stage.

        Args:
            stage_name (str): Snowflake stage name.
        """
        sql_template = 'drop_snowflake_stage'
        self.execute_query(
            sql_loader,
            query_name=sql_template,
            params=dict(
                db=self.sf_config['db'],
                schema=self.sf_config['schema'],
                stage=stage_name
            )
        )

    def load_staging_raw_table(
            self, staging_raw_table, staging_raw_errors_table,
            temp_staging_raw_table, date, report_name, file_name):
        """Load the staging_raw/errors data from temp_staging_raw table.

        Args:
            staging_raw_table (str): the staging raw table name.
            staging_raw_errors_table (str): the staging raw errors tbl name.
            temp_staging_raw_table (str): the temp table name in Snowflake.
            date (str): Date in format YYYY-MM-DD.
            report_name (str): Report name from config.reports.
            file_name (str): A file names pattern.
        """
        sql_template = sql_loader.load_query('load_staging_raw')
        sql_template = sql_template.replace('%(file_name)s', file_name)
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'],
            staging_raw_table=staging_raw_table,
            temp_staging_raw_table=temp_staging_raw_table,
            date=date,
            report_name=report_name,
        )
        sql_template_raw, non_identifier_params = (
            self.validator.format_identifiers(sql_template, params)
        )
        self.execute(sql_template_raw, params=non_identifier_params)

        sql_template = sql_loader.load_query('load_staging_raw_errors')
        sql_template = sql_template.replace('%(file_name)s', file_name)
        params['staging_raw_table'] = staging_raw_errors_table
        sql_template_err, non_identifier_params = (
            self.validator.format_identifiers(sql_template, params)
        )
        self.execute(sql_template_err, params=non_identifier_params)