"""Snowflake connector class for the Spotify's tasks."""
from datetime import datetime

from snowflake_connector.etl_connector import SQLLoader

from feed_ingestion.common.fact_analytics_sf.base_executor \
    import SnowflakeSQLExecutorFA
from feed_ingestion.common.staging_raw_sf.base_executor \
    import SnowflakeSQLExecutorSR
from feed_ingestion.flows.spotify import config
from feed_ingestion.util.snowflake.errors import JSONParserError
from feed_ingestion.util.snowflake.errors import JSONParserLoading

sql_loader = SQLLoader(__file__)


class Spotify(SnowflakeSQLExecutorFA, SnowflakeSQLExecutorSR):
    """Helper class to abstract Snowflake operations.

    This class inherits from SnowflakeSQLExecutor class, which provides basic
    set of methods. This class extends SnowflakeSQLExecutor with some specific
    methods, which are useful to encapsulate some flow specific operations.
    """

    @property
    def feed_name(self):
        """Name of the feed.

        Should match dir name of this feed, feed_name in config.py of a feed.

        Returns:
            str: Feed name
        """
        return config.feed_name

    @property
    def feedid(self):
        """Id of the feed.

        Returns:
            str: Feed id
        """
        return config.feedid

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

        Returns:
            str: staging_raw_{feed} table name.
        """
        return config.reports['streams']['staging_raw']

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

        Returns:
            str: staging_raw_{feed} table name.
        """
        return config.reports['tracks']['staging_raw']

    @property
    def storeid(self):
        """Storeid of feed data.

        Should match dim_store and feed config value.

        Returns:
            integer: Feed's storeid.
        """
        return config.storeid

    @property
    def licensor(self):
        """Licensor name.

        Returns
            str: Licensor name.
        """
        raise NotImplementedError()

    def create_temp_staging_raw_table(self, temp_staging_raw_table):
        """Create a temporary staging raw table.

        Args:
            temp_staging_raw_table (str): A table name in Snowflake.
        """
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'],
            temp_staging_raw_table=temp_staging_raw_table)
        self.execute_query(sql_loader, 'create_temp_staging_raw', params)

    def load_temp_staging_raw_table(
            self, temp_staging_raw_table, aws, key_dir, **kwargs):
        """Load temp staging raw table.

        Args:
            temp_staging_raw_table (str): A table name in Snowflake.
            aws (dict): AWS credentials to fill a template of COPY SQL
                statement. (deprecated)
            key_dir (str): A S3 path to load files from.
        """
        file_pattern = kwargs['file_pattern']
        aws_params = self.get_aws_params()
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'],
            table_name=temp_staging_raw_table,
            s3_path=key_dir,
            file_pattern=file_pattern,
            on_error_action='SKIP_FILE_{}'.format(
                config.snowflake_error_limit),
            **aws_params
        )
        return [JSONParserLoading(*e) for e in self.fetchall_query(
            sql_loader, 'load_temp_staging_raw', params)]

    def create_transitional_temp_staging_raw_table(
            self, report_name, transitional_temp_table):
        """Create a temporary staging raw table.

        Args:
            report_name (str): Name of the report to ingest.
            transitional_temp_table (str): A table name in Snowflake.
        """
        query = 'create_transitional_temp_staging_raw_{report_name}'.format(
            report_name=report_name)
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'],
            transitional_temp_table=transitional_temp_table)
        self.execute_query(sql_loader, query, params)

    def load_transitional_common_tables(
            self, date, temp_staging_raw_name, transitional_temp_table,
            staging_raw_table, report_name, licensor):
        """Load the temp_staging_raw data to the feed's staging_raw table.

        Args:
            date (str): Date of the data being process (YYYY-MM-DD).
            temp_staging_raw_name (dict): The name of temp_staging_raw.
            transitional_temp_table (str): The transitional table name.
            staging_raw_table (str): A table name in Snowflake.
            report_name (str): Name of the report to ingest.
            licensor (str): The name of licensor.
        """
        query = 'load_transitional_temp_staging_raw_{report_name}'.format(
            report_name=report_name)

        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'],
            date=date,
            temp_staging_raw_table=temp_staging_raw_name,
            transitional_temp_table=transitional_temp_table,
            staging_raw_table=staging_raw_table,
            licensor=licensor)
        self.execute_query(sql_loader, query, params)

    def load_common_staging_raw_table(
            self, date, transitional_temp_table, staging_raw_table,
            report_name, licensor):
        """Load the temp_staging_raw data to the feed's staging_raw table.

        Args:
            date (str): Date of the data being process (YYYY-MM-DD).
            transitional_temp_table (str): The transitional table name.
            staging_raw_table (str): A table name in Snowflake.
            report_name (str): Name of the report to ingest.
            licensor (str): The name of licensor.
        """
        query = 'load_staging_raw_{report_name}'.format(
            report_name=report_name)
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'],
            date=date,
            transitional_temp_table=transitional_temp_table,
            staging_raw_table=staging_raw_table,
            licensor=licensor)
        self.execute_query(sql_loader, query, params)

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

        Args:
            staging_raw_table (str): A table name in Snowflake.
            date (str): Date of the data being process (YYYY-MM-DD).
        """
        query = 'delete_from_staging_raw'
        if config.reports[kwargs['report_name']].get(
                'separate_query_for_cleaning_staging_raw', False):
            query = 'delete_from_staging_raw_{report_name}'.format(
                report_name=kwargs['report_name'])
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'],
            consumer_db=config.consumer_sf['db'],
            consumer_schema=config.consumer_sf['schema'],
            staging_raw_table=staging_raw_table,
            date=date,
            licensor=kwargs['licensor'],
            api_licensor=kwargs['licensor'])
        self.execute_query(sql_loader, query, params)

    def load_staging_raw_table(
            self, temp_staging_raw_table, staging_raw_table, date, **kwargs):
        """Load the temp_staging_raw data to the feed's staging_raw table.

        Args:
            temp_staging_raw_table (str): A table name in Snowflake.
            staging_raw_table (str): A table name in Snowflake.
            date (str): Date of the data being process (YYYY-MM-DD).
        """
        temp_staging_raw_names = kwargs['temp_staging_raw_names']
        filename = ''
        sql_loader_by_date = SQLLoader(__file__, date=date)

        if config.reports[kwargs['report_name']].get('filename', False):
            filename = config.file_pattern.format(
                report_name=kwargs['report_name'],
                date=datetime.strptime(date, '%Y-%m-%d'),
                country_code='',
                licensor=kwargs['licensor'])
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'],
            consumer_db=config.consumer_sf['db'],
            consumer_schema=config.consumer_sf['schema'],
            date=date,
            temp_streams_staging_raw_table=temp_staging_raw_names[
                kwargs['report_name']],
            temp_users_staging_raw_table=temp_staging_raw_names['users'],
            # for streams
            temp_tracks_staging_raw_table=temp_staging_raw_names['tracks'],
            # for other reports
            tracks_table=self.staging_raw_tracks_table,
            # for aggregated_streams
            filename=filename,
            licensor=kwargs['licensor'],
            staging_raw_table=staging_raw_table)
        self.execute_query(sql_loader_by_date, kwargs['query'], params)

    def delete_from_aggregated_skips_and_saves(self, date, licensor):
        """Delete from aggregated_skips_and_saves.

        Args:
            date (str): Date of the data being process (YYYY-MM-DD).
            licensor (str): The name of licensor.
        """
        self.execute_query(
            sql_loader, 'delete_from_aggregated_skips_and_saves', params=dict(
                db=self.sf_config['db'],
                schema=self.sf_config['schema'],
                reportdate=date,
                feedid=self.feedid,
                licensor=licensor))

    def load_aggregated_skips_and_saves(self, date, licensor):
        """Load aggregated_skips_and_saves.

        Args:
            date (str): Date of the data being process (YYYY-MM-DD).
            licensor (str): The name of licensor.
        """
        self.execute_query(
            sql_loader, 'load_aggregated_skips_and_saves',
            params=dict(
                db=self.sf_config['db'],
                schema=self.sf_config['schema'],
                reportdate=date,
                storeid=self.storeid,
                feedid=self.feedid,
                licensor=licensor))

    def validate_raw_data(
            self, temp_staging_raw_table, aws, error_limit, key_dir, **kwargs):
        """Validate data on s3.

        Args:
            temp_staging_raw_table (str): A table name in Snowflake.
            aws (dict): AWS credentials to fill a template of COPY SQL
                statement.
            error_limit (int): Snowflake error limit.
            key_dir (str): A S3 path to load files from.

        Returns:
            list: List of parser errors.
        """
        params = dict(
            db=self.sf_config['db'],
            schema=self.sf_config['schema'],
            table_name=temp_staging_raw_table,
            s3_path=key_dir,
            aws_key_id=aws['access_key'],
            aws_secret_key=aws['access_secret'],
            file_pattern=kwargs['file_pattern'],
            on_error_action='SKIP_FILE_{}'.format(error_limit))
        return [JSONParserError(*e) for e in self.fetchall_query(
            sql_loader, 'validate_temp_staging_raw', params)]

    def load_staging_fact_table(self, date):
        """Load staging fact_analytics table from staging_raw table.

        Args:
            date (str): Date of the data being process (YYYY-MM-DD).
        """
        query_name = 'load_staging_fact'
        self.execute_query(
            sql_loader, query_name, params=dict(
                db=self.sf_config['db'],
                schema=self.sf_config['schema'],
                consumer_db=config.consumer_sf['db'],
                consumer_schema=config.consumer_sf['schema'],
                staging_fact_analytics_table=self.staging_fact_table(date),
                staging_raw_table=self.staging_raw_table,
                reportdate=date,
                storeid=self.storeid,
                feedid=self.feedid,
                licensor=self.licensor))

    def delete_from_fact_table(self, date, *args):
        """Delete rows from fact_analytics table for the given date.

        Args:
            date (str): Date of the data being process (YYYY-MM-DD).
        """
        query_name = 'delete_from_fact_table'

        self.execute_query(
            sql_loader, query_name, params=dict(
                db=self.sf_config['db'],
                schema=self.sf_config['schema'],
                fact_table=self.fact_table,
                date=date,
                staging_raw_table=self.staging_raw_table,
                storeid=self.storeid,
                reportdate=date,
                feedid=self.feedid,
                licensor=self.licensor))

    def delete_from_fact_error_table(self, date, *args):
        """Delete rows from fact_analytics_error table for the given date.

        Args:
            date (str): Date of the data being process (YYYY-MM-DD).
        """
        query_name = 'delete_from_fact_table'

        self.execute_query(
            sql_loader, query_name, params=dict(
                db=self.sf_config['db'],
                schema=self.sf_config['schema'],
                fact_table=self.fact_error_table,
                date=date,
                staging_raw_table=self.staging_raw_table,
                storeid=self.storeid,
                reportdate=date,
                feedid=self.feedid,
                licensor=self.licensor))

    def load_fact_error_data(self, date):
        """Load unmatched data into fact_analytics_error.

        Args:
            date (str): Date of the data being process (YYYY-MM-DD).
        """
        query_name = 'load_fact_analytics_error'
        self.execute_query(
            sql_loader, query_name, params=dict(
                db=self.sf_config['db'],
                schema=self.sf_config['schema'],
                consumer_db=config.consumer_sf['db'],
                consumer_schema=config.consumer_sf['schema'],
                fact_error_table=self.fact_error_table,
                staging_fact_analytics_table=self.staging_fact_table(date),
                staging_raw_table=self.staging_raw_table,
                reportdate=date,
                storeid=self.storeid,
                feedid=self.feedid,
                licensor=self.licensor))

    def update_dimension_table(self, date, table_name):
        """Update dimension table with the new data.

        Args:
            date (str): Date of the data being process (YYYY-MM-DD).
            table_name (str): A table to update (corresponding query should be
                placed in the queries/ folder of the flow).
        """
        return self._update_dimension_table(
            date, table_name, sql_loader,
            licensor=self.licensor,
            consumer_db=config.consumer_sf['db'],
            consumer_schema=config.consumer_sf['schema'])


class SpotifyTheOrchardFA(Spotify):
    """Helper class to abstract Snowflake operations."""

    @property
    def feed_name(self):
        """Name of the feed.

        Should match dir name of this feed, feed_name in config.py of a feed.

        Returns:
            str: Feed name
        """
        return '_'.join(
            [config.feed_name, 'theorchard', config.fact_analytics_report])

    @property
    def licensor(self):
        """Licensor."""
        return 'theorchard'


class SpotifyAltafonteFA(Spotify):
    """Helper class to abstract Snowflake operations."""

    @property
    def feed_name(self):
        """Name of the feed.

        Should match dir name of this feed, feed_name in config.py of a feed.

        Returns:
            str: Feed name
        """
        return '_'.join(
            [config.feed_name, 'altafonte', config.fact_analytics_report])

    @property
    def licensor(self):
        """Licensor."""
        return 'altafonte'


class SpotifySMEFA(Spotify):
    """Helper class to abstract Snowflake operations."""

    @property
    def feed_name(self):
        """Name of the feed.

        Should match dir name of this feed, feed_name in config.py of a feed.

        Returns:
            str: Feed name
        """
        return '_'.join(
            [config.feed_name, 'sme', config.fact_analytics_report])

    @property
    def licensor(self):
        """Licensor."""
        return 'sme'


class SpotifySMEJPFA(Spotify):
    """Helper class to abstract Snowflake operations."""

    @property
    def feed_name(self):
        """Name of the feed.

        Should match dir name of this feed, feed_name in config.py of a feed.

        Returns:
            str: Feed name
        """
        return '_'.join(
            [config.feed_name, 'smejp', config.fact_analytics_report])

    @property
    def licensor(self):
        """Licensor."""
        return 'smejp'


class SpotifySMEJPIntLFA(Spotify):
    """Helper class to abstract Snowflake operations."""

    @property
    def feed_name(self):
        """Name of the feed.

        Should match dir name of this feed, feed_name in config.py of a feed.

        Returns:
            str: Feed name
        """
        return '_'.join(
            [config.feed_name, 'smejpintl', config.fact_analytics_report])

    @property
    def licensor(self):
        """Licensor."""
        return 'smejpintl'


class SpotifySMECharityFA(Spotify):
    """Helper class to abstract Snowflake operations."""

    @property
    def feed_name(self):
        """Name of the feed.

        Should match dir name of this feed, feed_name in config.py of a feed.

        Returns:
            str: Feed name
        """
        return '_'.join(
            [config.feed_name, 'smecharity', config.fact_analytics_report])

    @property
    def licensor(self):
        """Licensor."""
        return 'smecharity'