"""Snowflake stage loader for Meta Daily Ingestion Workflow."""

from datetime import datetime

from snowflake_connector.etl_connector import SQLLoader

from feed_ingestion.common.staging_raw_sf.snowflake_stage_loader import (
    StageLoader,
)
from feed_ingestion.flows.meta_daily import config


class MetaDailySL(StageLoader):
    """StageLoader for Meta Daily.

    Customises the base class to:
    - Delete staging_raw rows by both activity_date AND licensor
      so that a partial reload of one licensor does not clobber
      the other's already-loaded data.
    - Use a temp table with MATCH_BY_COLUMN_NAME to handle files
      with or without the UPC column.
    """

    sql_loader = SQLLoader(__file__)

    def create_stage(self, stage_name, s3_dir_path, aws, **kwargs):
        """Create Snowflake stage in the stage database/schema.

        Args:
            stage_name (str): Name of the Snowflake stage.
            s3_dir_path (str): S3 URL of the archive directory.
            aws (dict): DEPRECATED aws credentials.
        """
        aws_params = self.get_aws_params()
        self.resolve_sql_loader_and_execute(
            'create_snowflake_stage',
            params=dict(
                db=config.stage_sf['db'],
                schema=config.stage_sf['schema'],
                stage=stage_name,
                s3_dir_path=s3_dir_path,
                **aws_params,
            ),
        )

    def drop_stage(self, stage_name, **kwargs):
        """Drop Snowflake stage from the stage database/schema.

        Args:
            stage_name (str): Name of the Snowflake stage.
        """
        self.resolve_sql_loader_and_execute(
            'drop_snowflake_stage',
            params=dict(
                db=config.stage_sf['db'],
                schema=config.stage_sf['schema'],
                stage=stage_name,
            ),
        )

    def clean_staging_raw_table(
        self, staging_raw_table, activity_date, licensor=None, **kwargs
    ):
        """Delete rows for activity_date + licensor.

        Prevents duplication on resume.

        Args:
            staging_raw_table (str): Target Snowflake table.
            activity_date (str): Activity date (YYYYMMDD).
            licensor (str): Licensor whose rows to remove ('sme' or
                'theorchard').
            **kwargs: Additional keyword arguments (unused).
        """
        self.resolve_sql_loader_and_execute(
            'delete_from_staging_raw',
            params=dict(
                db=self.executor.sf_config['db'],
                schema=self.executor.sf_config['schema'],
                staging_raw_table=staging_raw_table,
                activity_date=activity_date,
                licensor=licensor,
            ),
        )

    def create_temp_staging_raw_table(self, temp_table_name):
        """Create a transient temp table matching the file schema.

        Args:
            temp_table_name (str): Name of the temp table to create.
        """
        self.resolve_sql_loader_and_execute(
            'create_temp_staging_raw',
            params=dict(
                db=config.stage_sf['db'],
                schema=config.stage_sf['schema'],
                temp_table_name=temp_table_name,
            ),
        )

    def load_temp_staging_raw_table(
        self, temp_table_name, stage_name, file_name
    ):
        """COPY a source file into the temp table using MATCH_BY_COLUMN_NAME.

        Args:
            temp_table_name (str): Target temp table.
            stage_name (str): External Snowflake stage name.
            file_name (str): File name within the stage to load.
        """
        self.resolve_sql_loader_and_execute(
            'load_temp_staging_raw',
            params=dict(
                db=config.stage_sf['db'],
                schema=config.stage_sf['schema'],
                stage=stage_name,
                temp_table_name=temp_table_name,
                file_name=file_name,
            ),
        )

    def load_staging_raw_table(
        self, staging_raw_table, source_files_dict, stage_name, **kwargs
    ):
        """Load source files into staging_raw via a temp table.

        For each file:
        1. Create (or replace) a temp table matching the file headers
           in the stage database/schema (not visible to end users).
        2. COPY into the temp table using MATCH_BY_COLUMN_NAME so columns
           are mapped by name — handles files with/without UPC correctly.
        3. INSERT into the final staging_raw table (in the main
           database/schema) from the temp table, adding derived metadata
           columns.

        Args:
            staging_raw_table (str): Target Snowflake table.
            source_files_dict (dict): {'files': [{'file_name', 'file_size',
                'found'}]}.
            stage_name (str): Name of the external Snowflake stage.
            **kwargs: Must include licensor (str), activity_date (str
                YYYYMMDD), and events_column (str).
        """
        ingestion_time = datetime.now().replace(microsecond=0)
        temp_table_name = f'temp_staging_raw_meta_{kwargs["activity_date"]}'

        for file_dict in filter(
            lambda f: f.get('found'), source_files_dict['files']
        ):
            self.create_temp_staging_raw_table(temp_table_name)
            try:
                self.load_temp_staging_raw_table(
                    temp_table_name, stage_name, file_dict['file_name']
                )
                self.resolve_sql_loader_and_execute(
                    'load_staging_raw',
                    params=dict(
                        db=self.executor.sf_config['db'],
                        schema=self.executor.sf_config['schema'],
                        stage_db=config.stage_sf['db'],
                        stage_schema=config.stage_sf['schema'],
                        staging_raw_table=staging_raw_table,
                        temp_table_name=temp_table_name,
                        ingestion_time=ingestion_time,
                        activity_date=kwargs['activity_date'],
                        events_column=kwargs['events_column'],
                        licensor=kwargs['licensor'],
                    ),
                )
            finally:
                self.resolve_sql_loader_and_execute(
                    'drop_temp_table',
                    params=dict(
                        db=config.stage_sf['db'],
                        schema=config.stage_sf['schema'],
                        table_name=temp_table_name,
                    ),
                )