"""Test FingerprintRule model operations."""

import datetime
from unittest.mock import MagicMock
from unittest.mock import call
from unittest.mock import patch

from flask import Flask

from neo4j.time import DateTime

import pytest

from sound_recordings.cypher import fingerprint_rules as cypher
from sound_recordings.models import fingerprint_rules

_app = Flask(__name__)


@patch('sound_recordings.models.fingerprint_rules.get_session')
def test_get_rules(mock_neo4j_get_session, make_graph_node, make_graph_rel):
    """Test returning rule from node + rel."""
    utc = datetime.timezone(datetime.timedelta(0))
    mock_neo4j_session = mock_neo4j_get_session.return_value
    mock_neo4j_results = mock_neo4j_session.run.return_value
    mock_neo4j_results.__iter__ = MagicMock(
        return_value=iter([
            {
                'rule': make_graph_node(111, data={
                    'service': 'meta',
                    'territory': 'US',
                    'policy': 'monetize'
                }),
                'rel': make_graph_rel(222, data={
                    'start': DateTime(2019, 6, 15, 0, 0, 0.0, 0, utc),
                    'end': DateTime(2020, 6, 15, 0, 0, 0.0, 0, utc),
                    'createdAt': DateTime(2023, 1, 1, 0, 0, 0.0, 0, utc),
                    'createdBy': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile',  # noqa:E501
                    'lastModifiedAt': DateTime(2023, 1, 1, 0, 0, 0.0, 0, utc),
                    'lastModifiedBy': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile'  # noqa:E501
                })
            },
            {
                'rule': make_graph_node(333, data={
                    'service': 'meta',
                    'territory': 'GB',
                    'policy': 'monetize'
                }),
                'rel': make_graph_rel(444, data={
                    'createdAt': DateTime(2023, 1, 1, 0, 0, 0.0, 0, utc),
                    'createdBy': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile',  # noqa:E501
                    'lastModifiedAt': DateTime(2023, 1, 1, 0, 0, 0.0, 0, utc),
                    'lastModifiedBy': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile'  # noqa:E501
                })
            }
        ])
    )

    results = fingerprint_rules.get_rules('Track', 12345)

    assert mock_neo4j_session.run.call_args_list == [
        call(
            cypher.GET_RULES.format(obj_type='Track', rule_rel='HAS_FINGERPRINT_RULE'),
            obj_id=12345
        )
    ]
    assert results == [
        {
            'start': datetime.datetime(2019, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            'end': datetime.datetime(2020, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            'territory': 'US',
            'policy': 'monetize',
            'service': 'meta',
            'created_at': datetime.datetime(2023, 1, 1, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
            'created_by': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile',  # noqa:E501
            'last_modified_at': datetime.datetime(2023, 1, 1, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
            'last_modified_by': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile',  # noqa:E501
            'element_id': '222'
        },
        {
            'start': None,
            'end': None,
            'territory': 'GB',
            'policy': 'monetize',
            'service': 'meta',
            'created_at': datetime.datetime(2023, 1, 1, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
            'created_by': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile',  # noqa:E501
            'last_modified_at': datetime.datetime(2023, 1, 1, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
            'last_modified_by': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile',  # noqa:E501
            'element_id': '444'
        }
    ]


@patch('sound_recordings.models.fingerprint_rules.features.is_has_fingerprint_rule_in_snowflake_enabled', return_value=False)  # noqa:E501
@patch('sound_recordings.models.fingerprint_rules.get_session')
def test_get_bulk_rules(mock_neo4j_get_session, mock_flag, make_graph_node, make_graph_rel):
    """Test returning rules from node + rel."""
    utc = datetime.timezone(datetime.timedelta(0))
    mock_neo4j_session = mock_neo4j_get_session.return_value
    mock_neo4j_results = mock_neo4j_session.run.return_value
    mock_neo4j_results.__iter__ = MagicMock(
        return_value=iter([
            {
                'obj': make_graph_node(123),
                'details': [{
                    'rule': make_graph_node(111, data={
                        'service': 'meta',
                        'territory': 'US',
                        'policy': 'monetize'
                    }),
                    'rel': make_graph_rel(222, data={
                        'start': DateTime(2019, 6, 15, 0, 0, 0.0, 0, utc),
                        'end': DateTime(2020, 6, 15, 0, 0, 0.0, 0, utc),
                        'createdAt': DateTime(2023, 1, 1, 0, 0, 0.0, 0, utc),
                        'createdBy': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile',  # noqa:E501
                        'lastModifiedAt': DateTime(2023, 1, 1, 0, 0, 0.0, 0, utc),
                        'lastModifiedBy': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile'  # noqa:E501
                    })
                }]
            },
            {
                'obj': make_graph_node(456),
                'details': [{
                    'rule': make_graph_node(333, data={
                        'service': 'meta',
                        'territory': 'GB',
                        'policy': 'monetize'
                    }),
                    'rel': make_graph_rel(444, data={
                        'createdAt': DateTime(2023, 1, 1, 0, 0, 0.0, 0, utc),
                        'createdBy': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile',  # noqa:E501
                        'lastModifiedAt': DateTime(2023, 1, 1, 0, 0, 0.0, 0, utc),
                        'lastModifiedBy': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile'  # noqa:E501
                    })
                }]
            }
        ])
    )

    # no Orchard-Identity-Id -> flag disabled -> Neo4j path
    with _app.test_request_context():
        results = fingerprint_rules.get_bulk_rules('Track', [123, 456])

    assert mock_neo4j_session.run.call_args_list == [
        call(
            cypher.GET_BULK_RULES.format(
                obj_type='Track', rule_rel='HAS_FINGERPRINT_RULE'),
            obj_ids=[123, 456]
        )
    ]
    assert results == {
        123: [{
            'start': datetime.datetime(2019, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            'end': datetime.datetime(2020, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            'territory': 'US',
            'policy': 'monetize',
            'service': 'meta',
            'created_at': datetime.datetime(2023, 1, 1, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
            'created_by': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile',  # noqa:E501
            'last_modified_at': datetime.datetime(2023, 1, 1, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
            'last_modified_by': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile',  # noqa:E501
            '_id': 222
        }],
        456: [{
            'start': None,
            'end': None,
            'territory': 'GB',
            'policy': 'monetize',
            'service': 'meta',
            'created_at': datetime.datetime(2023, 1, 1, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
            'created_by': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile',  # noqa:E501
            'last_modified_at': datetime.datetime(2023, 1, 1, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
            'last_modified_by': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile',  # noqa:E501
            '_id': 444
        }]
    }


@patch('sound_recordings.models.fingerprint_rules.by', return_value='test-by')
@patch('sound_recordings.models.fingerprint_rules.get_session')
def test_delete_rules(mock_neo4j_get_session, mock_by):
    """Test delete rules by rel ids."""
    delete_ids = [111, 222, 333]
    profile = ('ContentProfile', 123)
    mock_neo4j_session = mock_neo4j_get_session.return_value
    mock_neo4j_results = mock_neo4j_session.run.return_value
    mock_neo4j_results.__iter__ = MagicMock(
        return_value=iter([MagicMock() for _ in delete_ids])
    )

    fingerprint_rules.delete_rules('Track', 12345, delete_ids, profile)

    assert mock_neo4j_session.run.call_args_list == [
        call(
            cypher.DELETE_RULES.format(
                obj_type='Track',
                rule_rel='HAS_FINGERPRINT_RULE',
                deleted_rule_rel='DELETED_HAS_FINGERPRINT_RULE'
            ),
            rel_ids=delete_ids,
            obj_id=12345,
            modified_by='test-by'
        )
    ]


@patch('sound_recordings.models.fingerprint_rules.by', return_value='test-by')
@patch('sound_recordings.models.fingerprint_rules.get_session')
def test_delete_rule_error(mock_neo4j_get_session, mock_by):
    """Test delete failed."""
    delete_ids = [111, 222, 333]
    profile = ('ContentProfile', 123)
    mock_neo4j_session = mock_neo4j_get_session.return_value
    mock_neo4j_results = mock_neo4j_session.run.return_value
    mock_neo4j_results.__iter__ = MagicMock(
        return_value=iter([MagicMock() for _ in delete_ids[1:]])
    )

    with pytest.raises(fingerprint_rules.DeleteException):
        fingerprint_rules.delete_rules('Track', 12345, delete_ids, profile)


@patch('sound_recordings.models.fingerprint_rules.by', return_value='test-by')
@patch('sound_recordings.models.fingerprint_rules.get_session')
def test_create_rule(mock_neo4j_get_session, mock_by):
    """Test create rules."""
    profile = ('ContentProfile', 123)
    mock_neo4j_session = mock_neo4j_get_session.return_value
    new_rules = [
        {
            'start': datetime.datetime(2019, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            'end': datetime.datetime(2020, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            'territory': 'US',
            'policy': 'monetize',
            'service': 'meta'
        },
        {
            'start': None,
            'end': None,
            'territory': 'GB',
            'policy': 'monetize',
            'service': 'meta'
        }
    ]

    fingerprint_rules.create_rules('Track', 12345, new_rules, profile)

    assert mock_neo4j_session.run.call_args_list == [
        call(
            cypher.CREATE_RULE.format(
                obj_type='Track',
                rule_rel='HAS_FINGERPRINT_RULE'
            ),
            obj_id=12345,
            territory='US',
            policy='monetize',
            service='meta',
            start=datetime.datetime(2019, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            end=datetime.datetime(2020, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            created_by='test-by'
        ),
        call(
            cypher.CREATE_RULE.format(
                obj_type='Track',
                rule_rel='HAS_FINGERPRINT_RULE'
            ),
            obj_id=12345,
            territory='GB',
            policy='monetize',
            service='meta',
            start=None,
            end=None,
            created_by='test-by'
        )
    ]


@patch('sound_recordings.models.fingerprint_rules.by', return_value='test-by')
@patch('sound_recordings.models.fingerprint_rules.get_session')
def test_create_rule_error(mock_neo4j_get_session, mock_by):
    """Test create failed."""
    profile = ('ContentProfile', 123)
    mock_neo4j_session = mock_neo4j_get_session.return_value
    mock_neo4j_results = mock_neo4j_session.run.return_value
    mock_neo4j_results.single.return_value = None
    new_rules = [
        {
            'start': datetime.datetime(2019, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            'end': datetime.datetime(2020, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            'territory': 'US',
            'policy': 'monetize',
            'service': 'meta'
        },
        {
            'start': None,
            'end': None,
            'territory': 'GB',
            'policy': 'monetize',
            'service': 'meta'
        }
    ]

    with pytest.raises(fingerprint_rules.CreateException):
        fingerprint_rules.create_rules('Track', 12345, new_rules, profile)


@patch('sound_recordings.models.fingerprint_rules.snowflake_util')
def test_get_history(mock_neo4j_snowflake_util):
    """Test get history."""
    fetch_all = mock_neo4j_snowflake_util.fetchall
    fetch_all.side_effect = [
        [(
            'US',
            'meta',
            'monetize',
            datetime.datetime(2019, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            datetime.datetime(2020, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            datetime.datetime(2021, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            'ows-sound-recordings/post-vendors-33811-rules/123/ContentProfile',
            datetime.datetime(2021, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            'ows-sound-recordings/post-vendors-33811-rules/123/ContentProfile'
        )],
        [(
            'FR',
            'meta',
            'monetize',
            datetime.datetime(2019, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            datetime.datetime(2020, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            datetime.datetime(2021, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            'ows-sound-recordings/post-vendors-33811-rules/123/ContentProfile',
            datetime.datetime(2021, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),
            'ows-sound-recordings/post-vendors-33811-rules/123/ContentProfile'
        )]
    ]

    expected_result = {
        'created': [
            {
                'territory': 'US',
                'service': 'meta',
                'policy': 'monetize',
                'period_start': datetime.datetime(2019, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
                'period_end': datetime.datetime(2020, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
                'created_at': datetime.datetime(2021, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
                'created_by': 'ows-sound-recordings/post-vendors-33811-rules/123/ContentProfile',  # noqa:E501
                'last_modified_at': datetime.datetime(2021, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
                'last_modified_by': 'ows-sound-recordings/post-vendors-33811-rules/123/ContentProfile'  # noqa:E501
            }
        ],
        'deleted': [
            {
                'territory': 'FR',
                'service': 'meta',
                'policy': 'monetize',
                'period_start': datetime.datetime(2019, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
                'period_end': datetime.datetime(2020, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
                'created_at': datetime.datetime(2021, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
                'created_by': 'ows-sound-recordings/post-vendors-33811-rules/123/ContentProfile',  # noqa:E501
                'last_modified_at': datetime.datetime(2021, 6, 15, 0, 0, tzinfo=datetime.timezone.utc),  # noqa:E501
                'last_modified_by': 'ows-sound-recordings/post-vendors-33811-rules/123/ContentProfile'  # noqa:E501
            }
        ]
    }
    results = fingerprint_rules.get_history('Vendor', 123, {'service': 'meta', 'policy': 'monetize'})  # noqa:E501

    assert fetch_all.called
    assert fetch_all.call_args_list == [
        call(
            "SELECT\n  territory,\n  service,\n  policy,\n  period_start,\n  period_end,\n  created_at,\n  created_by,\n  last_modified_at,\n  last_modified_by\nFROM\n  HAS_FINGERPRINT_RULE\nWHERE\n  orchard_obj_type = :orchard_obj_type\nAND\n  orchard_obj_id = :orchard_obj_id\nAND service = 'meta' AND policy = 'monetize'\nORDER BY last_modified_at\n",  # noqa:E501
            {'orchard_obj_type': 'Vendor', 'orchard_obj_id': 123}
        ),
        call(
            "SELECT\n  territory,\n  service,\n  policy,\n  period_start,\n  period_end,\n  created_at,\n  created_by,\n  last_modified_at,\n  last_modified_by\nFROM\n  DELETED_HAS_FINGERPRINT_RULE\nWHERE\n  orchard_obj_type = :orchard_obj_type\nAND\n  orchard_obj_id = :orchard_obj_id\nAND service = 'meta' AND policy = 'monetize'\nORDER BY last_modified_at\n",  # noqa:E501
            {'orchard_obj_type': 'Vendor', 'orchard_obj_id': 123}
        )
    ]
    assert results == expected_result


@patch('sound_recordings.models.fingerprint_rules.features.is_has_fingerprint_rule_in_snowflake_enabled')  # noqa:E501
@patch('sound_recordings.models.fingerprint_rules.snowflake_util')
def test_get_bulk_rules_snowflake(mock_snowflake_util, mock_flag_enabled):
    """Test get_bulk_rules reads from Snowflake when the flag is enabled."""
    mock_flag_enabled.return_value = True
    utc = datetime.timezone.utc
    mock_snowflake_util.fetchall.return_value = [
        (
            '123',
            'US',
            'meta',
            'monetize',
            datetime.datetime(2019, 6, 15, 0, 0, tzinfo=utc),
            datetime.datetime(2020, 6, 15, 0, 0, tzinfo=utc),
            datetime.datetime(2023, 1, 1, 0, 0, tzinfo=utc),
            'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile',
            datetime.datetime(2023, 1, 1, 0, 0, tzinfo=utc),
            'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile'
        )
    ]

    results = fingerprint_rules.get_bulk_rules('Vendor', [123, 456])

    assert mock_snowflake_util.fetchall.call_count == 1
    sql_arg, params_arg = mock_snowflake_util.fetchall.call_args[0]
    assert 'HAS_FINGERPRINT_RULE' in sql_arg
    assert "orchard_obj_id IN ('123', '456')" in sql_arg
    assert params_arg == {'orchard_obj_type': 'Vendor'}
    # requested id with no rules is present with an empty list
    assert results == {
        123: [
            {
                'start': datetime.datetime(2019, 6, 15, 0, 0, tzinfo=utc),
                'end': datetime.datetime(2020, 6, 15, 0, 0, tzinfo=utc),
                'territory': 'US',
                'policy': 'monetize',
                'service': 'meta',
                'created_at': datetime.datetime(2023, 1, 1, 0, 0, tzinfo=utc),
                'created_by': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile',  # noqa:E501
                'last_modified_at': datetime.datetime(2023, 1, 1, 0, 0, tzinfo=utc),  # noqa:E501
                'last_modified_by': 'ows-sound-recordings/post-vendors-123-rules/456/ContentProfile'  # noqa:E501
            }
        ],
        456: []
    }


@patch('sound_recordings.models.fingerprint_rules.features.is_has_fingerprint_rule_in_snowflake_enabled')  # noqa:E501
@patch('sound_recordings.models.fingerprint_rules.snowflake_util')
def test_get_bulk_rules_snowflake_subaccount_casing(
        mock_snowflake_util, mock_flag_enabled):
    """Snowflake stores 'SubAccount'; 'Subaccount' must be mapped before query."""
    mock_flag_enabled.return_value = True
    mock_snowflake_util.fetchall.return_value = []

    fingerprint_rules.get_bulk_rules('Subaccount', [123])

    _, params_arg = mock_snowflake_util.fetchall.call_args[0]
    assert params_arg == {'orchard_obj_type': 'SubAccount'}


@patch('sound_recordings.models.fingerprint_rules._get_bulk_rules_neo4j')
@patch('sound_recordings.models.fingerprint_rules._get_bulk_rules_snowflake')
@patch('sound_recordings.models.fingerprint_rules.features.is_has_fingerprint_rule_in_snowflake_enabled')  # noqa:E501
def test_get_bulk_rules_dispatches_to_snowflake_when_ff_enabled(
        mock_flag_enabled, mock_snowflake, mock_neo4j):
    """FF enabled -> Snowflake backend."""
    mock_flag_enabled.return_value = True

    result = fingerprint_rules.get_bulk_rules('Vendor', [1, 2])

    mock_flag_enabled.assert_called_once_with()
    mock_snowflake.assert_called_once_with('Vendor', [1, 2])
    mock_neo4j.assert_not_called()
    assert result == mock_snowflake.return_value


@patch('sound_recordings.models.fingerprint_rules._get_bulk_rules_neo4j')
@patch('sound_recordings.models.fingerprint_rules._get_bulk_rules_snowflake')
@patch('sound_recordings.models.fingerprint_rules.features.is_has_fingerprint_rule_in_snowflake_enabled')  # noqa:E501
def test_get_bulk_rules_dispatches_to_neo4j_when_ff_disabled(
        mock_flag_enabled, mock_snowflake, mock_neo4j):
    """FF disabled -> Neo4j backend."""
    mock_flag_enabled.return_value = False

    result = fingerprint_rules.get_bulk_rules('Vendor', [1, 2])

    mock_flag_enabled.assert_called_once_with()
    mock_neo4j.assert_called_once_with('Vendor', [1, 2])
    mock_snowflake.assert_not_called()
    assert result == mock_neo4j.return_value


def test_get_rules_snowflake():
    """Reads within the transaction, maps rows, and maps Subaccount casing."""
    utc = datetime.timezone.utc
    start = datetime.datetime(2019, 6, 15, 0, 0, tzinfo=utc)
    end = datetime.datetime(2020, 6, 15, 0, 0, tzinfo=utc)
    ts = datetime.datetime(2023, 1, 1, 0, 0, tzinfo=utc)
    tx = MagicMock()
    tx.fetchall.return_value = [
        ('US', 'meta', 'monetize', start, end, ts, 'cb', ts, 'lmb')
    ]

    result = fingerprint_rules.get_rules_snowflake(tx, 'Subaccount', 123)

    sql_arg, params = tx.fetchall.call_args[0]
    assert 'HAS_FINGERPRINT_RULE' in sql_arg
    assert params == {'orchard_obj_type': 'SubAccount', 'orchard_obj_id': '123'}
    assert result == [{
        'territory': 'US', 'service': 'meta', 'policy': 'monetize',
        'start': start, 'end': end, 'created_at': ts, 'created_by': 'cb',
        'last_modified_at': ts, 'last_modified_by': 'lmb'
    }]


@patch('sound_recordings.models.fingerprint_rules.by', return_value='test-by')
def test_create_rules_snowflake(mock_by):
    """Inserts each rule with mapped obj type/id and audit author."""
    utc = datetime.timezone.utc
    start = datetime.datetime(2019, 6, 15, 0, 0, tzinfo=utc)
    modified_at = datetime.datetime(2026, 6, 11, 0, 0, tzinfo=utc)
    tx = MagicMock()
    tx.execute.return_value.rowcount = 1
    rules = [{'territory': 'US', 'service': 'meta', 'policy': 'monetize',
              'start': start, 'end': None}]

    fingerprint_rules.create_rules_snowflake(
        tx, 'Vendor', 99, rules, ('ContentProfile', 123), modified_at)

    tx.execute.assert_called_once()
    sql_arg, params = tx.execute.call_args[0]
    assert 'INSERT INTO HAS_FINGERPRINT_RULE' in sql_arg
    assert params == [{
        'orchard_obj_type': 'Vendor', 'orchard_obj_id': '99',
        'territory': 'US', 'service': 'meta', 'policy': 'monetize',
        'period_start': start, 'period_end': None, 'created_by': 'test-by',
        'modified_at': modified_at
    }]


@patch('sound_recordings.models.fingerprint_rules.by', return_value='test-by')
def test_create_rules_snowflake_error(mock_by):
    """Raises CreateException when no row is inserted."""
    tx = MagicMock()
    tx.execute.return_value.rowcount = 0
    rules = [{'territory': 'US', 'service': 'meta', 'policy': 'monetize',
              'start': None, 'end': None}]
    modified_at = datetime.datetime(2026, 6, 11, tzinfo=datetime.timezone.utc)

    with pytest.raises(fingerprint_rules.CreateException):
        fingerprint_rules.create_rules_snowflake(
            tx, 'Vendor', 99, rules, ('ContentProfile', 123), modified_at)


@patch('sound_recordings.models.fingerprint_rules.by', return_value='test-by')
def test_delete_rules_snowflake(mock_by):
    """Archives then deletes the whole batch in one call each."""
    tx = MagicMock()
    tx.execute.return_value.rowcount = 1
    start = datetime.datetime(2026, 6, 10, tzinfo=datetime.timezone.utc)
    end = datetime.datetime(2026, 6, 20, tzinfo=datetime.timezone.utc)
    created_at = datetime.datetime(2026, 1, 1, tzinfo=datetime.timezone.utc)
    modified_at = datetime.datetime(2026, 6, 11, tzinfo=datetime.timezone.utc)
    rules = [{'territory': 'US', 'service': 'meta', 'policy': 'monetize',
              'start': start, 'end': end, 'created_at': created_at,
              'created_by': 'orig-creator'}]

    fingerprint_rules.delete_rules_snowflake(
        tx, 'Subaccount', 17663, rules, ('ContentProfile', 123), modified_at)

    archive_call, delete_call = tx.execute.call_args_list
    assert 'INSERT INTO DELETED_HAS_FINGERPRINT_RULE' in archive_call.args[0]
    assert archive_call.args[1] == [{
        'orchard_obj_type': 'SubAccount', 'orchard_obj_id': '17663',
        'territory': 'US', 'service': 'meta', 'policy': 'monetize',
        'period_start': start, 'period_end': end,
        'created_at': created_at, 'created_by': 'orig-creator',
        'modified_by': 'test-by', 'modified_at': modified_at
    }]
    assert 'MERGE INTO HAS_FINGERPRINT_RULE' in delete_call.args[0]
    assert 'WHEN MATCHED THEN DELETE' in delete_call.args[0]
    assert ':service_0' in delete_call.args[0]
    assert delete_call.args[1] == {
        'orchard_obj_type': 'SubAccount', 'orchard_obj_id': '17663',
        'service_0': 'meta', 'territory_0': 'US', 'policy_0': 'monetize',
        'period_start_0': start, 'period_end_0': end
    }


@patch('sound_recordings.models.fingerprint_rules.by', return_value='test-by')
def test_delete_rules_snowflake_error(mock_by):
    """Raises DeleteException when not every active row is removed."""
    tx = MagicMock()
    tx.execute.return_value.rowcount = 0
    rules = [{'territory': 'US', 'service': 'meta', 'policy': 'monetize',
              'start': None, 'end': None, 'created_at': None,
              'created_by': 'orig-creator'}]
    modified_at = datetime.datetime(2026, 6, 11, tzinfo=datetime.timezone.utc)

    with pytest.raises(fingerprint_rules.DeleteException):
        fingerprint_rules.delete_rules_snowflake(
            tx, 'Subaccount', 17663, rules, ('ContentProfile', 123), modified_at)


@patch('sound_recordings.models.fingerprint_rules.by', return_value='test-by')
def test_delete_rules_snowflake_batches_multiple_rules(mock_by):
    """Builds one VALUES row per rule with uniquely indexed bind params."""
    tx = MagicMock()
    tx.execute.return_value.rowcount = 2
    modified_at = datetime.datetime(2026, 6, 11, tzinfo=datetime.timezone.utc)
    rules = [
        {'territory': 'US', 'service': 'meta', 'policy': 'monetize',
         'start': None, 'end': None, 'created_at': None,
         'created_by': 'orig-creator'},
        {'territory': 'CA', 'service': 'tiktok', 'policy': 'block',
         'start': None, 'end': None, 'created_at': None,
         'created_by': 'orig-creator'},
    ]

    fingerprint_rules.delete_rules_snowflake(
        tx, 'Vendor', 5, rules, ('ContentProfile', 123), modified_at)

    archive_call, delete_call = tx.execute.call_args_list
    assert len(archive_call.args[1]) == 2
    assert ':service_0' in delete_call.args[0]
    assert ':service_1' in delete_call.args[0]
    assert delete_call.args[1]['service_0'] == 'meta'
    assert delete_call.args[1]['service_1'] == 'tiktok'