"""Unit tests for DeezerSL class overriding StageLoader."""

from unittest import mock
from unittest.mock import MagicMock

import pytest
from snowflake_connector import validator
from snowflake_connector.etl_connector import SQLLoader

from feed_ingestion.flows.deezer import config
from feed_ingestion.flows.deezer.stage_loader import DeezerSL

FRAUD_DB = config.fraud_report_sf['db']
FRAUD_SCHEMA = config.fraud_report_sf['schema']


def test_create_stage(mock_aws, monkeypatch):
    """Test create_stage method."""
    executor = MagicMock()
    sql_loader = MagicMock()
    execute_mock = MagicMock()

    stage_name = 'my_stage_20170101'
    s3_dir_path = '/path/to/some/dir'
    aws = {
        'access_key': 'user',
        'access_secret': 'pass'
    }
    kwargs = {
        # 'licensor': 'theorchard',
    }

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

    stage_loader = DeezerSL(executor, sql_loader)

    stage_loader.create_stage(
        stage_name, s3_dir_path, aws, licensor='theorchard', **kwargs)

    execute_mock.assert_called_once_with(
        'create_snowflake_stage_theorchard_v1', params=mock.ANY)


@pytest.mark.parametrize('licensor', ['theorchard', 'sme'])
def test_load_staging_raw_table(monkeypatch, licensor):
    """Test load_staging_raw_table method."""
    executor = MagicMock()
    executor.validator = validator.BaseValidator()
    executor.sf_config = {'db': 'test_db', 'schema': 'test_schema'}
    sql_loader = SQLLoader(config.__file__)

    stage_name = 'my_stage'
    stage_raw_table = 'my_staging_raw_table'
    date = '2020-01-01'

    source_files_dict = {
        'files': [
                'awesome_file',
                'gorgeous_file',
                'sublime_file',
        ]
    }

    stage_loader = DeezerSL(executor, sql_loader)

    stage_loader.load_staging_raw_table(
        stage_raw_table, source_files_dict, date, stage_name,
        licensor=licensor)

    assert executor.execute.call_count == 3
    for filename, call_args in zip(source_files_dict['files'],
                                   executor.execute.call_args_list):
        sql, params = call_args
        assert f'/{filename}' in sql[0], \
            'file shall be placed to SQL as stage path'
        assert params['params']['file_name'] == filename
        assert params['params']['licensor'] == licensor


def test_fraud_report_create_stage_uses_fraud_db_schema(
        mock_aws, monkeypatch):
    """Test create_stage uses fraud db/schema for fraud stage."""
    executor = MagicMock()
    executor.sf_config = {'db': 'facts', 'schema': 'prod'}
    execute_mock = MagicMock()

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

    stage_loader = DeezerSL(executor, MagicMock())

    stage_loader.create_stage(
        'deezer_daily_sme_stage_20260504',
        's3://cucumbers/DeezerV3/temp/2026-05-04/sme/',
        {},
        licensor='sme',
    )

    calls = execute_mock.call_args_list
    assert len(calls) == 2
    main_stage_call, fraud_stage_call = calls

    # main stage uses default db/schema
    assert main_stage_call[1]['params']['db'] == 'facts'
    assert main_stage_call[1]['params']['schema'] == 'prod'

    # fraud stage uses fraud_reporting.deezer
    fraud_query = 'create_snowflake_stage_all_v3_fraud'
    assert fraud_stage_call[0][0] == fraud_query
    assert fraud_stage_call[1]['params']['db'] == FRAUD_DB
    assert fraud_stage_call[1]['params']['schema'] == FRAUD_SCHEMA


def test_fraud_report_drop_stage_uses_fraud_db_schema(monkeypatch):
    """Test drop_stage uses fraud db/schema for fraud stage."""
    executor = MagicMock()
    executor.sf_config = {'db': 'facts', 'schema': 'prod'}
    execute_mock = MagicMock()

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

    stage_loader = DeezerSL(executor, MagicMock())

    stage_loader.drop_stage(
        'deezer_daily_sme_stage_20260504', licensor='sme')

    calls = execute_mock.call_args_list
    assert len(calls) == 2
    main_drop_call, fraud_drop_call = calls

    # main stage drop uses default db/schema
    main_params = main_drop_call[1]['params']
    assert main_params['db'] == 'facts'
    assert main_params['schema'] == 'prod'

    # fraud stage drop uses fraud_reporting.deezer
    fraud_params = fraud_drop_call[1]['params']
    assert fraud_params['db'] == FRAUD_DB
    assert fraud_params['schema'] == FRAUD_SCHEMA
    assert fraud_params['stage'].endswith('_fraud')


@pytest.mark.parametrize('table,expected_db,expected_schema', [
    ('staging_raw_deezer_v3_activity_metrics', 'facts', 'prod'),
    ('staging_raw_deezer_v3_fraud_report', FRAUD_DB, FRAUD_SCHEMA),
])
def test_clean_staging_raw_table_uses_correct_db_schema_per_table(
        monkeypatch, table, expected_db, expected_schema):
    """Test clean_staging_raw_table routes fraud to fraud db."""
    executor = MagicMock()
    executor.sf_config = {'db': 'facts', 'schema': 'prod'}
    execute_mock = MagicMock()

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

    stage_loader = DeezerSL(executor, MagicMock())

    stage_loader.clean_staging_raw_table(
        [table], '2026-05-04', licensor='sme')

    assert execute_mock.call_count == 1
    params = execute_mock.call_args[1]['params']
    assert params['db'] == expected_db
    assert params['schema'] == expected_schema
    assert params['staging_raw_table'] == table


@pytest.mark.parametrize('licensor', ['sme', 'theorchard'])
def test_load_staging_raw_table_fraud_file_uses_fraud_db_schema(
        monkeypatch, licensor):
    """Test load_staging_raw_table routes fraud COPY to fraud db."""
    executor = MagicMock()
    executor.validator = validator.BaseValidator()
    executor.sf_config = {'db': 'facts', 'schema': 'prod'}
    sql_loader = SQLLoader(config.__file__)

    date = '2026-05-04'
    stage_name = 'deezer_daily_sme_stage_20260504'

    source_files_dict = {'files': ['fraud_report_*.txt']}

    stage_loader = DeezerSL(executor, sql_loader)

    stage_loader.load_staging_raw_table(
        config.snowflake_table_names['staging_raw']['v3'][-1],
        source_files_dict,
        date,
        stage_name,
        licensor=licensor,
    )

    assert executor.execute.call_count == 1
    executed_sql = executor.execute.call_args[0][0]
    # db/schema are substituted as SQL identifiers
    assert FRAUD_DB in executed_sql
    assert FRAUD_SCHEMA in executed_sql
    assert 'facts' not in executed_sql
    assert 'prod' not in executed_sql


@pytest.mark.parametrize('licensor,expected_name', [
    ('sme', 'fraud_report_sony-20260504.txt'),
    ('theorchard', 'fraud_report_theorchard-20260504.txt'),
])
def test_load_staging_raw_fraud_uses_mapped_licensor_name(
        licensor, expected_name):
    """Test fraud filename uses mapped licensor name."""
    executor = MagicMock()
    executor.validator = validator.BaseValidator()
    executor.sf_config = {'db': 'facts', 'schema': 'prod'}
    sql_loader = SQLLoader(config.__file__)

    date = '2026-05-04'
    stage_name = 'deezer_daily_sme_stage_20260504'

    source_files_dict = {'files': ['fraud_report_*.txt']}

    stage_loader = DeezerSL(executor, sql_loader)

    stage_loader.load_staging_raw_table(
        config.snowflake_table_names['staging_raw']['v3'][-1],
        source_files_dict,
        date,
        stage_name,
        licensor=licensor,
    )

    executed_sql = executor.execute.call_args[0][0]
    assert expected_name in executed_sql


def test_load_staging_raw_table_non_fraud_uses_default_db(
        monkeypatch):
    """Test non-fraud v3 files still use the default facts.prod db/schema."""
    executor = MagicMock()
    executor.validator = validator.BaseValidator()
    executor.sf_config = {'db': 'facts', 'schema': 'prod'}
    sql_loader = SQLLoader(config.__file__)

    date = '2026-05-04'
    stage_name = 'deezer_daily_sme_stage_20260504'

    source_files_dict = {'files': ['sony_activity_metrics_daily.csv']}

    stage_loader = DeezerSL(executor, sql_loader)

    stage_loader.load_staging_raw_table(
        config.snowflake_table_names['staging_raw']['v3'][0],
        source_files_dict,
        date,
        stage_name,
        licensor='sme',
    )

    assert executor.execute.call_count == 1
    executed_sql = executor.execute.call_args[0][0]
    assert 'facts' in executed_sql
    assert 'prod' in executed_sql
    assert FRAUD_DB not in executed_sql