import pytest
from decimal import Decimal
from unittest.mock import MagicMock

from src.snowflake_client import _validate_schema_name, get_store_info, resolve_gp, resolve_vendor_id


class TestValidateSchemaName:
    def test_valid_simple_identifier_passes(self):
        _validate_schema_name("shopify_test")

    def test_valid_with_dollar_passes(self):
        _validate_schema_name("shopify_test$schema")

    def test_valid_with_digits_not_leading_passes(self):
        _validate_schema_name("shopify_2_test")

    def test_underscore_leading_passes(self):
        _validate_schema_name("_shopify_test")

    def test_digit_leading_raises(self):
        with pytest.raises(ValueError, match="Invalid schema_name"):
            _validate_schema_name("2shopify_test")

    def test_none_raises(self):
        with pytest.raises(ValueError, match="Invalid schema_name"):
            _validate_schema_name(None)

    def test_empty_string_raises(self):
        with pytest.raises(ValueError, match="Invalid schema_name"):
            _validate_schema_name("")

    def test_special_chars_raises(self):
        with pytest.raises(ValueError, match="Invalid schema_name"):
            _validate_schema_name("shopify-test")

    def test_space_in_name_raises(self):
        with pytest.raises(ValueError, match="Invalid schema_name"):
            _validate_schema_name("shopify test")

    def test_dot_in_name_raises(self):
        with pytest.raises(ValueError, match="Invalid schema_name"):
            _validate_schema_name("shopify.test")


class TestGetStoreInfo:
    def _make_conn(self, row):
        conn = MagicMock()
        mock_cur = MagicMock()
        mock_cur.__enter__ = MagicMock(return_value=mock_cur)
        mock_cur.__exit__ = MagicMock(return_value=False)
        mock_cur.fetchone.return_value = row
        conn.cursor.return_value = mock_cur
        return conn

    def test_no_row_returns_all_none(self):
        conn = self._make_conn(None)
        assert get_store_info(conn, "test_schema") == (None, None, None, None)

    def test_normal_row_returns_tuple(self):
        conn = self._make_conn(("My Store", 999, "GB", "GBP"))
        name, store_id, country, currency = get_store_info(conn, "test_schema")
        assert name == "My Store"
        assert store_id == 999
        assert country == "GB"
        assert currency == "GBP"

    def test_name_is_trimmed(self):
        conn = self._make_conn(("  Padded Name  ", 1, "US", "USD"))
        name, _, _, _ = get_store_info(conn, "test_schema")
        assert name == "Padded Name"

    def test_blank_name_returns_none(self):
        conn = self._make_conn(("   ", 1, "US", "USD"))
        name, _, _, _ = get_store_info(conn, "test_schema")
        assert name is None

    def test_decimal_store_id_coerced_to_int(self):
        conn = self._make_conn(("Store", Decimal("12345678"), "DE", "EUR"))
        _, store_id, _, _ = get_store_info(conn, "test_schema")
        assert store_id == 12345678
        assert isinstance(store_id, int)

    def test_null_store_id_returns_none(self):
        conn = self._make_conn(("Store", None, "FR", "EUR"))
        _, store_id, _, _ = get_store_info(conn, "test_schema")
        assert store_id is None


class TestResolveVendorIdHardOverrides:
    """Pure-logic tests for the hard-override branches — no DB interaction."""

    def test_kings_road_returns_20977(self):
        conn = MagicMock()
        vendor_id, vendor_name, vendor_brand, method = resolve_vendor_id(conn, None, "Kings Road")
        assert vendor_id == 20977
        assert method == "SOURCE_RULE"
        conn.cursor.assert_not_called()

    def test_kings_road_merch_eu_returns_20977(self):
        conn = MagicMock()
        vendor_id, _, _, method = resolve_vendor_id(conn, None, "Kings Road Merch EU")
        assert vendor_id == 20977
        assert method == "SOURCE_RULE"
        conn.cursor.assert_not_called()

    def test_kings_road_merch_us_returns_20977(self):
        conn = MagicMock()
        vendor_id, _, _, method = resolve_vendor_id(conn, None, "Kings Road Merch US")
        assert vendor_id == 20977
        assert method == "SOURCE_RULE"
        conn.cursor.assert_not_called()

    def test_indiomerch_returns_791836(self):
        conn = MagicMock()
        vendor_id, _, _, method = resolve_vendor_id(conn, None, "IndieMerch")
        assert vendor_id == 791836
        assert method == "SOURCE_RULE"
        conn.cursor.assert_not_called()

    def test_unknown_merch_company_returns_none_without_db(self):
        conn = MagicMock()
        vendor_id, vendor_name, vendor_brand, method = resolve_vendor_id(conn, None, "Some Unknown Company")
        assert vendor_id is None
        assert method is None
        conn.cursor.assert_not_called()

    def test_none_merch_company_returns_none_without_db(self):
        conn = MagicMock()
        vendor_id, vendor_name, vendor_brand, method = resolve_vendor_id(conn, None, None)
        assert vendor_id is None
        assert method is None
        conn.cursor.assert_not_called()

    def test_ceremony_of_roses_with_no_gp_falls_back_to_merch_company_vendor(self):
        conn = MagicMock()
        mock_cur = MagicMock()
        mock_cur.__enter__ = MagicMock(return_value=mock_cur)
        mock_cur.__exit__ = MagicMock(return_value=False)
        mock_cur.fetchone.return_value = None
        conn.cursor.return_value = mock_cur

        vendor_id, _, _, method = resolve_vendor_id(conn, gp_id=None, merch_company="Ceremony of Roses")

        # No GP resolution query is run if gp_id is None.
        # Falls through to ARTIST_STORES lookup (no shop_domain given) → merch fallback
        assert vendor_id == 11111
        assert method == "SOURCE_RULE"

    def test_ceremony_of_roses_with_no_gp_no_domain_returns_fallback(self):
        conn = MagicMock()
        mock_cur = MagicMock()
        mock_cur.__enter__ = MagicMock(return_value=mock_cur)
        mock_cur.__exit__ = MagicMock(return_value=False)
        mock_cur.fetchone.return_value = None
        conn.cursor.return_value = mock_cur

        vendor_id, _, _, method = resolve_vendor_id(
            conn, gp_id=None, merch_company="Ceremony of Roses", shop_domain=None
        )

        assert vendor_id == 11111
        assert method == "SOURCE_RULE"

    def test_cm_distro_fallback_vendor(self):
        conn = MagicMock()
        mock_cur = MagicMock()
        mock_cur.__enter__ = MagicMock(return_value=mock_cur)
        mock_cur.__exit__ = MagicMock(return_value=False)
        mock_cur.fetchone.return_value = None
        conn.cursor.return_value = mock_cur

        vendor_id, _, _, method = resolve_vendor_id(conn, gp_id=None, merch_company="CM Distro")
        assert vendor_id == 34562
        assert method == "SOURCE_RULE"


class TestResolveGp:
    def test_like_patterns_use_escaped_percent(self):
        """Guard against reintroducing bare % in LIKE patterns.

        The Snowflake connector uses pyformat, so a bare % in the query string
        causes TypeError at runtime. All LIKE wildcards must be %%.
        """
        conn = MagicMock()
        mock_cur = MagicMock()
        mock_cur.__enter__ = MagicMock(return_value=mock_cur)
        mock_cur.__exit__ = MagicMock(return_value=False)
        mock_cur.fetchone.return_value = None
        conn.cursor.return_value = mock_cur

        resolve_gp(conn, "Test Artist")

        import re

        # _statement_timeout opens its own cursors via cursor(conn), but the test wires
        # conn.cursor to always return mock_cur, so all execute calls land on the same mock.
        # call_args (last call) is the timeout reset — filter to find the resolve_gp SQL.
        gp_sql_calls = [
            c.args[0]
            for c in mock_cur.execute.call_args_list
            if not c.args[0].strip().upper().startswith("ALTER SESSION")
        ]
        assert len(gp_sql_calls) == 1, f"Expected one resolve_gp SQL call, got {len(gp_sql_calls)}"
        sql = gp_sql_calls[0]
        # Strip valid pyformat named placeholders %(...)s and doubled %% before checking for bare %.
        # A bare % (not part of a placeholder or doubled) would cause TypeError at runtime.
        cleaned = re.sub(r"%\(\w+\)s", "", sql.replace("%%", ""))
        assert "%" not in cleaned, "Bare % found in resolve_gp SQL — use %% for LIKE wildcards"