import pytest

from pingcap_datapipelines.salesforce_compat import (
    render_salesforce_order_compatibility_sql,
    render_salesforce_order_cutover_sql,
    render_salesforce_order_materialize_sql,
)


def test_salesforce_order_compatibility_query_targets_nexuscrm_final() -> None:
    sql = render_salesforce_order_compatibility_sql()

    assert "`gcp-digital-bi.nexuscrm_final.nexuscrm_order`" in sql
    assert "`gcp-digital-bi.nexuscrm_final.nexuscrm_orderitem`" in sql
    assert "`gcp-digital-bi.nexuscrm_final.nexuscrm_product2`" in sql
    assert "nexuscrm_order_product_c" not in sql
    assert "`gcp-digital-bi.nexuscrm_final.nexuscrm_account`" in sql
    assert "`gcp-digital-bi.data_platform.monthly_cloud_order`" in sql
    assert "`gcp-digital-bi.salesforce.order`" not in sql


def test_salesforce_order_compatibility_query_exposes_finance_fields() -> None:
    sql = render_salesforce_order_compatibility_sql()

    for field in [
        "real_booking_date_c",
        "exchange_rate_to_use_c",
        "is_nonstandard_order_c",
        "partner_name_c",
        "prepayment_or_not_c",
        "termination_date_c",
        "ping_cap_rev_rec_entity_c",
        "payment_term_c",
        "payment_type_c",
        "total_amount",
        "tcv_amount_c",
        "acv_amount_c",
        "cash_collected_c",
        "deferred_revenue_c",
        "recognized_revenue_c",
        "total_fees_c",
        "net_fees_c",
        "account_id_c",
        "region_c",
        "ping_cap_contracting_entity_c",
    ]:
        assert f" AS {field}" in sql


def test_salesforce_order_compatibility_query_derives_safe_known_values() -> None:
    sql = render_salesforce_order_compatibility_sql()

    assert "WHEN CurrencyIsoCode = 'USD' THEN 1.0" in sql
    assert "product_family_value = 'TiDB Cloud' AND term_value < 1.5 THEN effective_date_value" in sql
    assert "net_fees_value * SAFE_CAST(1 + IF(ABS(tax_value) > 1, tax_value / 100, tax_value) AS BIGNUMERIC)" in sql


def test_salesforce_order_compatibility_query_uses_active_orderitems_without_legacy_correlation_filter() -> None:
    sql = render_salesforce_order_compatibility_sql()

    assert "WITH order_item_source AS" in sql
    assert "oi.OrderId AS order_id" in sql
    assert "oi.Id AS sf_id" in sql
    assert "oi.Product2Id" in sql
    assert "oi.ServiceDate" in sql
    assert "oi.EndDate" in sql
    assert "oi.Net_fees__c" in sql
    assert "oi.UnitPrice" in sql
    assert "WHERE oi.IsDeleted = 0\n    AND p.IsDeleted = 0" in sql
    assert "OrderProduct__c" not in sql


def test_salesforce_order_materialize_sql_writes_compat_table() -> None:
    sql = render_salesforce_order_materialize_sql(project="example-project", dataset="salesforce")

    assert sql.startswith("CREATE OR REPLACE TABLE `example-project.salesforce.order_compat_from_nexuscrm` AS\n")
    assert "`example-project.nexuscrm_final.nexuscrm_order`" in sql


def test_salesforce_order_cutover_sql_backs_up_materializes_and_replaces() -> None:
    sql = render_salesforce_order_cutover_sql(backup_suffix="20260703T120000Z")

    assert "CREATE OR REPLACE TABLE `gcp-digital-bi.salesforce.order_legacy_backup_20260703T120000Z` AS" in sql
    assert "CREATE OR REPLACE TABLE `gcp-digital-bi.salesforce.order_compat_from_nexuscrm` AS" in sql
    assert "CREATE OR REPLACE TABLE `gcp-digital-bi.salesforce.order` AS" in sql
    assert "Rollback:" in sql


def test_salesforce_order_cutover_sql_rejects_unsafe_backup_suffix() -> None:
    with pytest.raises(ValueError):
        render_salesforce_order_cutover_sql(backup_suffix="bad-suffix;drop")