import datetime
from unittest.mock import MagicMock, patch

from snowflake.snowpark import Row

from backend.dtos import DsarResult, ExportRecord, Profile, Subscription
from backend.queries import (
    query_dsar_report,
    query_fan_exports,
    query_fan_profile,
    query_fan_subscriptions,
)


def _mock_session(*sql_return_sequences):
    """Return a mock session whose .sql().collect() calls return each sequence in order."""
    session = MagicMock()
    session.sql.return_value.collect.side_effect = list(sql_return_sequences)
    return session


def test_query_fan_profile_returns_full_pii():
    profile_row = Row(
        ID="abc-123",
        CRM_ID="crm-456",
        EMAIL="test@example.com",
        FIRST_NAME="Jane",
        LAST_NAME="Doe",
        PHONE_NUMBER="555",
        COUNTRY_CODE="US",
        CITY="NYC",
        REGION="NY",
        ADDRESS="1 Main St",
        ZIP_CODE="10001",
        DATE_OF_BIRTH=datetime.date(1990, 6, 15),
        BIRTHDAY="Jun 15",
        DELETED=False,
    )
    session = _mock_session([], [profile_row])  # deleted_rows=[], profile_rows=[row]

    result = query_fan_profile(session, "test@example.com")

    assert isinstance(result, Profile)
    assert result.first_name == "Jane"
    assert result.city == "NYC"
    assert result.region == "NY"
    assert result.zip_code == "10001"
    assert result.date_of_birth == datetime.date(1990, 6, 15)
    assert result.deleted is False
    assert result.deleted_at is None


def test_query_fan_profile_case_insensitive_email_in_sql():
    session = _mock_session([], [])  # both return empty — just check the SQL

    query_fan_profile(session, "Test@Example.COM")

    sql_calls = [c.args[0] for c in session.sql.call_args_list]
    assert any("lower('test@example.com')" in sql.lower() for sql in sql_calls)


def test_query_fan_profile_not_found_returns_none():
    session = _mock_session([], [])  # no deleted record, no profile

    result = query_fan_profile(session, "nobody@example.com")

    assert result is None


def test_query_fan_profile_deleted_fan_synthesises_profile():
    deleted_row = Row(
        EMAIL="gone@example.com", DELETED_AT=datetime.datetime(2024, 1, 15)
    )
    session = _mock_session([deleted_row], [])  # deleted record exists, no profile row

    result = query_fan_profile(session, "gone@example.com")

    assert result is not None
    assert result.deleted is True
    assert result.deleted_at == datetime.datetime(2024, 1, 15)
    assert result.first_name is None


def test_query_fan_profile_attaches_deletion_date_to_active_fan():
    deleted_row = Row(
        EMAIL="both@example.com", DELETED_AT=datetime.datetime(2024, 3, 1)
    )
    profile_row = Row(
        ID="abc-123",
        CRM_ID=None,
        EMAIL="both@example.com",
        FIRST_NAME="Active",
        LAST_NAME="Fan",
        PHONE_NUMBER=None,
        COUNTRY_CODE=None,
        CITY=None,
        REGION=None,
        ADDRESS=None,
        ZIP_CODE=None,
        DATE_OF_BIRTH=None,
        BIRTHDAY=None,
        DELETED=False,
    )
    session = _mock_session([deleted_row], [profile_row])

    result = query_fan_profile(session, "both@example.com")

    assert result is not None
    assert result.first_name == "Active"
    assert result.deleted_at == datetime.datetime(2024, 3, 1)


def test_query_fan_subscriptions_returns_rows():
    now = datetime.datetime(2024, 5, 1)
    sub_row = Row(CREATED_AT=now, ARTIST_NAME="Cool Artist")
    session = _mock_session([sub_row])

    result = query_fan_subscriptions(session, "fan@example.com")

    assert len(result) == 1
    assert isinstance(result[0], Subscription)
    assert result[0].artist_name == "Cool Artist"
    assert result[0].created_at == now


def test_query_fan_subscriptions_empty():
    session = _mock_session([])

    result = query_fan_subscriptions(session, "nobody@example.com")

    assert result == []


def test_query_fan_subscriptions_email_lowercased_in_sql():
    session = _mock_session([])

    query_fan_subscriptions(session, "FAN@EXAMPLE.COM")

    sql = session.sql.call_args.args[0]
    assert "lower('fan@example.com')" in sql.lower()


def test_query_dsar_report_bundles_profile_subscriptions_exports():
    with (
        patch("backend.queries.query_fan_profile") as mock_profile,
        patch("backend.queries.query_fan_subscriptions") as mock_subs,
        patch("backend.queries.query_fan_exports") as mock_exports,
    ):
        mock_profile.return_value = Profile(
            profile_id=None,
            crm_id=None,
            email="fan@example.com",
            first_name="Fan",
            last_name="One",
            phone_number=None,
            country_code=None,
            city=None,
            region=None,
            address=None,
            zip_code=None,
            date_of_birth=None,
            birthday=None,
            deleted=False,
            deleted_at=None,
        )
        mock_subs.return_value = [
            Subscription(
                created_at=datetime.datetime(2024, 5, 1), artist_name="My Artist"
            )
        ]
        mock_exports.return_value = ([], [])

        session = MagicMock()
        result = query_dsar_report(session, "fan@example.com", "QA")

    assert isinstance(result, DsarResult)
    assert result.profile is not None
    assert result.profile.first_name == "Fan"
    assert len(result.subscriptions) == 1
    assert result.subscriptions[0].artist_name == "My Artist"
    assert result.exports == []
    mock_profile.assert_called_once_with(session, "fan@example.com", "QA")
    mock_subs.assert_called_once_with(session, "fan@example.com", "QA")
    mock_exports.assert_called_once_with(session, "fan@example.com", "QA")


# --- query_fan_exports ---


def test_query_fan_exports_maps_fansifter_rows():
    now = datetime.datetime(2024, 6, 1)
    fansifter_row = Row(
        LAST_EXPORT_DATE=now,
        JUSTIFICATION="marketing",
        EXPORTER_NAME="John",
        REASON="campaign",
        FIRST_NAME="Jane",
        EMAIL="fan@example.com",
    )
    session = _mock_session(
        [fansifter_row], [], []
    )  # fansifter rows, salesforce empty, hightouch empty

    exports, warnings = query_fan_exports(session, "fan@example.com")

    assert len(exports) == 1
    assert isinstance(exports[0], ExportRecord)
    assert exports[0].source == "Fansifter"
    assert exports[0].export_date == now
    assert exports[0].business_reason == "marketing"
    assert exports[0].exported_by == "John"
    assert exports[0].export_type == "campaign"
    assert exports[0].fan_first_name == "Jane"
    assert exports[0].fan_email == "fan@example.com"
    assert exports[0].fan_last_name is None
    assert warnings == []


def test_query_fan_exports_maps_salesforce_rows():
    now = datetime.datetime(2024, 7, 1)
    sf_row = Row(
        EXPORT_DATE=now,
        BUSINESS_REASON="promo",
        RECIPIENT_EMAIL_C="rec@label.com",
        RECIPIENT_NAME_C="Recipient",
        EXPORT_TYPE_C="mailing list",
        EXPORTED_BY_C="Alice",
        FIRST_NAME_C="Jane",
        LAST_NAME_C="Doe",
        EMAIL_C="fan@example.com",
    )
    session = _mock_session(
        [], [sf_row], []
    )  # fansifter empty, one salesforce row, hightouch empty

    exports, warnings = query_fan_exports(session, "fan@example.com")

    assert len(exports) == 1
    assert exports[0].source == "Salesforce"
    assert exports[0].export_date == now
    assert exports[0].business_reason == "promo"
    assert exports[0].recipient_email == "rec@label.com"
    assert exports[0].recipient_name == "Recipient"
    assert exports[0].fan_first_name == "Jane"
    assert exports[0].fan_last_name == "Doe"
    assert warnings == []


def test_query_fan_exports_combines_both_sources():
    now = datetime.datetime(2024, 6, 1)
    fansifter_row = Row(
        LAST_EXPORT_DATE=now,
        JUSTIFICATION=None,
        EXPORTER_NAME=None,
        REASON=None,
        FIRST_NAME=None,
        EMAIL="fan@example.com",
    )
    sf_row = Row(
        EXPORT_DATE=now,
        BUSINESS_REASON=None,
        RECIPIENT_EMAIL_C=None,
        RECIPIENT_NAME_C=None,
        EXPORT_TYPE_C=None,
        EXPORTED_BY_C=None,
        FIRST_NAME_C=None,
        LAST_NAME_C=None,
        EMAIL_C="fan@example.com",
    )
    session = _mock_session([fansifter_row], [sf_row], [])

    exports, warnings = query_fan_exports(session, "fan@example.com")

    assert len(exports) == 2
    sources = {r.source for r in exports}
    assert sources == {"Fansifter", "Salesforce"}
    assert warnings == []


def test_query_fan_exports_empty_when_no_records():
    session = _mock_session([], [], [])

    exports, warnings = query_fan_exports(session, "nobody@example.com")

    assert exports == []
    assert warnings == []


def test_query_fan_exports_email_lowercased_in_sql():
    session = _mock_session([], [], [])

    query_fan_exports(session, "FAN@EXAMPLE.COM")

    sql_calls = [c.args[0] for c in session.sql.call_args_list]
    assert all("fan@example.com" in sql for sql in sql_calls)


def test_query_fan_exports_uses_env_schema():
    session = _mock_session([], [], [])

    query_fan_exports(session, "fan@example.com", "PROD")

    sql_calls = [c.args[0] for c in session.sql.call_args_list]
    assert any("fansifter_pg_reporting.prod_ows_dmp_public" in sql for sql in sql_calls)
    assert any("fansifter_app_reporting.PROD.audience_fan" in sql for sql in sql_calls)
    assert any("FACTS.PROD.IDENTITY" in sql for sql in sql_calls)


def test_query_fan_exports_maps_hightouch_rows():
    now = datetime.datetime(2024, 8, 1)
    ht_row = Row(
        EXPORT_DATE=now,
        OP_TYPE="insert",
        DESTINATION="Salesforce CRM",
        MODEL_NAME="Fan Sync",
        FAN_EMAIL="fan@example.com",
    )
    session = _mock_session(
        [], [], [ht_row]
    )  # fansifter empty, salesforce empty, one hightouch row

    exports, warnings = query_fan_exports(session, "fan@example.com")

    assert len(exports) == 1
    assert exports[0].source == "Hightouch"
    assert exports[0].export_date == now
    assert exports[0].business_reason == "retargeting"
    assert exports[0].recipient_name == "Salesforce CRM"
    assert exports[0].export_type == "Fan Sync"
    assert exports[0].fan_email == "fan@example.com"
    assert warnings == []


def test_query_fan_exports_hightouch_sfmc_business_reason():
    now = datetime.datetime(2024, 8, 1)
    ht_row = Row(
        EXPORT_DATE=now,
        OP_TYPE="insert",
        DESTINATION="sfmc",
        MODEL_NAME="Email Sync",
        FAN_EMAIL="fan@example.com",
    )
    session = _mock_session([], [], [ht_row])

    exports, warnings = query_fan_exports(session, "fan@example.com")

    assert exports[0].business_reason == "email audience"


def test_query_fan_exports_warns_on_fansifter_error():
    from unittest.mock import MagicMock

    session = MagicMock()
    session.sql.side_effect = [
        Exception("permission denied"),
        MagicMock(collect=lambda: []),
        MagicMock(collect=lambda: []),
    ]

    exports, warnings = query_fan_exports(session, "fan@example.com")

    assert exports == []
    assert len(warnings) == 1
    assert "Fansifter" in warnings[0]
    assert "permission denied" in warnings[0]


def test_query_fan_exports_warns_on_salesforce_error():
    from unittest.mock import MagicMock

    session = MagicMock()
    session.sql.side_effect = [
        MagicMock(collect=lambda: []),
        Exception("access denied"),
        MagicMock(collect=lambda: []),
    ]

    exports, warnings = query_fan_exports(session, "fan@example.com")

    assert exports == []
    assert len(warnings) == 1
    assert "Salesforce" in warnings[0]
    assert "access denied" in warnings[0]


def test_query_fan_exports_warns_on_hightouch_error():
    from unittest.mock import MagicMock

    session = MagicMock()
    session.sql.side_effect = [
        MagicMock(collect=lambda: []),
        MagicMock(collect=lambda: []),
        Exception("table not found"),
    ]

    exports, warnings = query_fan_exports(session, "fan@example.com")

    assert exports == []
    assert len(warnings) == 1
    assert "Hightouch" in warnings[0]
    assert "table not found" in warnings[0]