"""Unit tests for MetaDailySL stage loader."""

from unittest import mock
from unittest.mock import MagicMock

import pytest

from feed_ingestion.flows.meta_daily import config
from feed_ingestion.flows.meta_daily.stage_loader import MetaDailySL

STAGE_DB = 'stage_db'
STAGE_SCHEMA = 'stage_schema'


@pytest.fixture(autouse=True)
def patch_stage_sf(monkeypatch):
    monkeypatch.setitem(config.stage_sf, 'db', STAGE_DB)
    monkeypatch.setitem(config.stage_sf, 'schema', STAGE_SCHEMA)


class TestCleanStagingRawTable:
    """Tests for the delete-by-date-and-licensor override."""

    @pytest.mark.parametrize('licensor', ['sme', 'theorchard'])
    def test_clean_staging_raw_table(self, monkeypatch, licensor):
        executor = MagicMock()
        executor.sf_config = {'db': 'test_db', 'schema': 'test_schema'}
        sql_loader = MagicMock()
        execute_mock = MagicMock()

        monkeypatch.setattr(
            MetaDailySL, 'resolve_sql_loader_and_execute', execute_mock
        )

        stage_loader = MetaDailySL(executor, sql_loader)
        stage_loader.clean_staging_raw_table(
            'staging_raw_meta_consumption', '20260417', licensor=licensor
        )

        execute_mock.assert_called_once_with(
            'delete_from_staging_raw',
            params=mock.ANY,
        )
        call_params = execute_mock.call_args[1]['params']
        assert call_params['staging_raw_table'] == (
            'staging_raw_meta_consumption'
        )
        assert call_params['activity_date'] == '20260417'
        assert call_params['licensor'] == licensor
        assert call_params['db'] == 'test_db'
        assert call_params['schema'] == 'test_schema'


class TestLoadStagingRawTable:
    """Tests for the COPY INTO with derived columns."""

    @pytest.mark.parametrize('licensor', ['sme', 'theorchard'])
    def test_load_staging_raw_table_single_file(self, monkeypatch, licensor):
        executor = MagicMock()
        executor.sf_config = {'db': 'test_db', 'schema': 'test_schema'}
        sql_loader = MagicMock()
        execute_mock = MagicMock()

        monkeypatch.setattr(
            MetaDailySL, 'resolve_sql_loader_and_execute', execute_mock
        )

        stage_loader = MetaDailySL(executor, sql_loader)
        source_files_dict = {
            'files': [
                {
                    'file_name': 'sony_consumption_export_20260417.txt',
                    'file_size': 12345,
                    'found': True,
                }
            ]
        }

        stage_loader.load_staging_raw_table(
            'staging_raw_meta_consumption',
            source_files_dict,
            stage_name='meta_daily_consumption_20260417',
            licensor=licensor,
            activity_date='20260417',
            events_column='VIEWS',
        )

        # 4 calls per file: create_temp, load_temp, load_staging_raw, drop_temp
        assert execute_mock.call_count == 4
        query_names = [
            call[0][0] for call in execute_mock.call_args_list
        ]
        assert query_names == [
            'create_temp_staging_raw',
            'load_temp_staging_raw',
            'load_staging_raw',
            'drop_temp_table',
        ]

        # Verify load_staging_raw params — dest uses executor sf_config,
        # source temp table uses stage_sf
        load_params = execute_mock.call_args_list[2][1]['params']
        assert load_params['licensor'] == licensor
        assert load_params['activity_date'] == '20260417'
        assert load_params['events_column'] == 'VIEWS'
        assert load_params['staging_raw_table'] == (
            'staging_raw_meta_consumption'
        )
        assert load_params['temp_table_name'] == (
            'temp_staging_raw_meta_20260417'
        )
        assert load_params['db'] == 'test_db'
        assert load_params['schema'] == 'test_schema'
        assert load_params['stage_db'] == STAGE_DB
        assert load_params['stage_schema'] == STAGE_SCHEMA

        # Verify create_temp params use stage_sf
        create_params = execute_mock.call_args_list[0][1]['params']
        assert create_params['db'] == STAGE_DB
        assert create_params['schema'] == STAGE_SCHEMA

        # Verify load_temp params use stage_sf for table and stage
        temp_params = execute_mock.call_args_list[1][1]['params']
        assert temp_params['file_name'] == (
            'sony_consumption_export_20260417.txt'
        )
        assert temp_params['stage'] == 'meta_daily_consumption_20260417'
        assert temp_params['db'] == STAGE_DB
        assert temp_params['schema'] == STAGE_SCHEMA

        # Verify drop_temp params use stage_sf
        drop_params = execute_mock.call_args_list[3][1]['params']
        assert drop_params['db'] == STAGE_DB
        assert drop_params['schema'] == STAGE_SCHEMA

    def test_load_staging_raw_table_skips_not_found(self, monkeypatch):
        executor = MagicMock()
        executor.sf_config = {'db': 'test_db', 'schema': 'test_schema'}
        sql_loader = MagicMock()
        execute_mock = MagicMock()

        monkeypatch.setattr(
            MetaDailySL, 'resolve_sql_loader_and_execute', execute_mock
        )

        stage_loader = MetaDailySL(executor, sql_loader)
        source_files_dict = {
            'files': [
                {
                    'file_name': 'sony_consumption_export_20260417.txt',
                    'file_size': 12345,
                    'found': False,
                }
            ]
        }

        stage_loader.load_staging_raw_table(
            'staging_raw_meta_consumption',
            source_files_dict,
            stage_name='meta_daily_consumption_20260417',
            licensor='sme',
            activity_date='20260417',
            events_column='VIEWS',
        )

        execute_mock.assert_not_called()

    def test_load_staging_raw_table_multiple_files(self, monkeypatch):
        executor = MagicMock()
        executor.sf_config = {'db': 'test_db', 'schema': 'test_schema'}
        sql_loader = MagicMock()
        execute_mock = MagicMock()

        monkeypatch.setattr(
            MetaDailySL, 'resolve_sql_loader_and_execute', execute_mock
        )

        stage_loader = MetaDailySL(executor, sql_loader)
        source_files_dict = {
            'files': [
                {
                    'file_name': 'file_a.txt',
                    'file_size': 100,
                    'found': True,
                },
                {
                    'file_name': 'file_b.txt',
                    'file_size': 200,
                    'found': True,
                },
            ]
        }

        stage_loader.load_staging_raw_table(
            'staging_raw_meta_consumption',
            source_files_dict,
            stage_name='meta_daily_consumption_20260417',
            licensor='sme',
            activity_date='20260417',
            events_column='VIEWS',
        )

        # 4 calls per file × 2 files = 8
        assert execute_mock.call_count == 8
        query_names = [
            call[0][0] for call in execute_mock.call_args_list
        ]
        assert query_names == [
            'create_temp_staging_raw',
            'load_temp_staging_raw',
            'load_staging_raw',
            'drop_temp_table',
            'create_temp_staging_raw',
            'load_temp_staging_raw',
            'load_staging_raw',
            'drop_temp_table',
        ]
        # Verify file_name for each file's load_temp call
        first_temp_params = execute_mock.call_args_list[1][1]['params']
        second_temp_params = execute_mock.call_args_list[5][1]['params']
        assert first_temp_params['file_name'] == 'file_a.txt'
        assert second_temp_params['file_name'] == 'file_b.txt'