"""Integration tests for Snowflake adapter using fakesnow.

Run with: SNOWFLAKE_TEST=1 pytest -m integration tests/db/test_snowflake_integration.py -v
"""

import os

import pytest

from sidemantic import Dimension, Metric, Model, Relationship, SemanticLayer

# Mark all tests in this module as integration tests
pytestmark = [
    pytest.mark.integration,
    pytest.mark.skipif(
        os.getenv("SNOWFLAKE_TEST") != "1",
        reason="Set SNOWFLAKE_TEST=1 to run Snowflake integration tests with fakesnow",
    ),
]


@pytest.fixture(scope="module", autouse=True)
def patch_snowflake():
    """Patch snowflake.connector with fakesnow for all tests in this module."""
    import fakesnow

    with fakesnow.patch():
        yield


@pytest.fixture(scope="module")
def snowflake_layer():
    """Create SemanticLayer with Snowflake connection."""
    # Use a dummy connection URL - fakesnow will intercept the connection
    layer = SemanticLayer(connection="snowflake://test:test@test/testdb/public?warehouse=test_warehouse")
    yield layer
    layer.adapter.close()


def test_semantic_layer_basic_metric(snowflake_layer):
    """Test basic metric query with SemanticLayer."""
    # Define a model using a CTE as table
    orders = Model(
        name="orders",
        table="(SELECT 1 as order_id, 100.0 as amount UNION ALL SELECT 2, 200.0 UNION ALL SELECT 3, 150.0)",
        primary_key="order_id",
        metrics=[
            Metric(name="total_revenue", agg="sum", sql="amount"),
            Metric(name="avg_revenue", agg="avg", sql="amount"),
            Metric(name="order_count", agg="count", sql="order_id"),
        ],
    )
    snowflake_layer.add_model(orders)

    # Test sum
    result = snowflake_layer.query(metrics=["orders.total_revenue"])
    row = result.fetchone()
    assert row[0] == 450.0

    # Test avg
    result = snowflake_layer.query(metrics=["orders.avg_revenue"])
    row = result.fetchone()
    assert row[0] == 150.0

    # Test count
    result = snowflake_layer.query(metrics=["orders.order_count"])
    row = result.fetchone()
    assert row[0] == 3


def test_semantic_layer_dimension_grouping(snowflake_layer):
    """Test querying with dimensions and grouping."""
    sales = Model(
        name="sales",
        table="""(
            SELECT 'Electronics' as category, 'US' as region, 100 as amount UNION ALL
            SELECT 'Electronics', 'EU', 150 UNION ALL
            SELECT 'Clothing', 'US', 200 UNION ALL
            SELECT 'Clothing', 'EU', 250
        )""",
        primary_key="category",
        dimensions=[
            Dimension(name="category", type="categorical"),
            Dimension(name="region", type="categorical"),
        ],
        metrics=[Metric(name="total_sales", agg="sum", sql="amount")],
    )
    snowflake_layer.add_model(sales)

    # Group by one dimension
    result = snowflake_layer.query(metrics=["sales.total_sales"], dimensions=["sales.category"])
    rows = result.fetchall()
    cols = [desc[0] for desc in result.description]
    results = [dict(zip(cols, row)) for row in rows]
    # Snowflake returns uppercase column names
    results_dict = {r["CATEGORY"]: r["TOTAL_SALES"] for r in results}

    assert results_dict["Electronics"] == 250
    assert results_dict["Clothing"] == 450


def test_semantic_layer_joins(snowflake_layer):
    """Test joins between models."""
    customers_join = Model(
        name="customers_join",
        table="""(
            SELECT 1 as customer_id, 'Alice' as name, 'US' as region UNION ALL
            SELECT 2, 'Bob', 'EU'
        )""",
        primary_key="customer_id",
        dimensions=[Dimension(name="region", type="categorical")],
    )

    orders_join = Model(
        name="orders_join",
        table="""(
            SELECT 1 as order_id, 1 as customer_id, 100.0 as amount UNION ALL
            SELECT 2, 1, 150.0 UNION ALL
            SELECT 3, 2, 200.0
        )""",
        primary_key="order_id",
        metrics=[Metric(name="total_revenue", agg="sum", sql="amount")],
        relationships=[Relationship(name="customers_join", type="many_to_one", foreign_key="customer_id")],
    )

    snowflake_layer.add_model(customers_join)
    snowflake_layer.add_model(orders_join)

    # Query with join
    result = snowflake_layer.query(metrics=["orders_join.total_revenue"], dimensions=["customers_join.region"])
    rows = result.fetchall()
    cols = [desc[0] for desc in result.description]
    # Snowflake returns uppercase column names
    results_dict = {dict(zip(cols, row))["REGION"]: dict(zip(cols, row))["TOTAL_REVENUE"] for row in rows}

    assert results_dict["US"] == 250.0
    assert results_dict["EU"] == 200.0


def test_semantic_layer_multiple_metrics(snowflake_layer):
    """Test multiple metrics in one query."""
    products = Model(
        name="products",
        table="""(
            SELECT 1 as product_id, 100 as price, 5 as quantity UNION ALL
            SELECT 2, 200, 3 UNION ALL
            SELECT 3, 150, 7
        )""",
        primary_key="product_id",
        metrics=[
            Metric(name="total_price", agg="sum", sql="price"),
            Metric(name="avg_price", agg="avg", sql="price"),
            Metric(name="total_quantity", agg="sum", sql="quantity"),
            Metric(name="product_count", agg="count", sql="product_id"),
        ],
    )
    snowflake_layer.add_model(products)

    result = snowflake_layer.query(
        metrics=["products.total_price", "products.avg_price", "products.total_quantity", "products.product_count"]
    )
    row = result.fetchone()
    cols = [desc[0] for desc in result.description]
    row_dict = dict(zip(cols, row))

    # Snowflake returns uppercase column names
    assert row_dict["TOTAL_PRICE"] == 450
    assert row_dict["AVG_PRICE"] == 150.0
    assert row_dict["TOTAL_QUANTITY"] == 15
    assert row_dict["PRODUCT_COUNT"] == 3


def test_semantic_layer_filters(snowflake_layer):
    """Test filtering with WHERE clause."""
    inventory = Model(
        name="inventory",
        table="""(
            SELECT 1 as item_id, 'A' as category, 100 as quantity UNION ALL
            SELECT 2, 'A', 200 UNION ALL
            SELECT 3, 'B', 150 UNION ALL
            SELECT 4, 'B', 250
        )""",
        primary_key="item_id",
        dimensions=[Dimension(name="category", type="categorical")],
        metrics=[Metric(name="total_quantity", agg="sum", sql="quantity")],
    )
    snowflake_layer.add_model(inventory)

    # Test with filter in SQL
    result = snowflake_layer.query(
        metrics=["inventory.total_quantity"],
        dimensions=["inventory.category"],
        filters=["inventory.category = 'A'"],
    )
    rows = result.fetchall()
    assert len(rows) == 1
    assert rows[0][1] == 300  # Total for category A


def test_semantic_layer_sql_generation(snowflake_layer):
    """Test SQL generation without executing."""
    metrics_model = Model(
        name="metrics_sql",
        table="(SELECT 1 as id, 100 as value)",
        primary_key="id",
        metrics=[Metric(name="total", agg="sum", sql="value")],
    )
    snowflake_layer.add_model(metrics_model)

    sql = snowflake_layer.compile(metrics=["metrics_sql.total"])
    assert "SELECT" in sql.upper()
    assert "SUM" in sql.upper()
    assert snowflake_layer.dialect == "snowflake"


def test_semantic_layer_order_by(snowflake_layer):
    """Test ORDER BY with SemanticLayer."""
    scores = Model(
        name="scores",
        table="""(
            SELECT 'Alice' as name, 85 as score UNION ALL
            SELECT 'Bob', 92 UNION ALL
            SELECT 'Charlie', 78 UNION ALL
            SELECT 'Diana', 95
        )""",
        primary_key="name",
        dimensions=[Dimension(name="name", type="categorical")],
        metrics=[Metric(name="avg_score", agg="avg", sql="score")],
    )
    snowflake_layer.add_model(scores)

    # Order by dimension
    result = snowflake_layer.query(
        dimensions=["scores.name"], metrics=["scores.avg_score"], order_by=["scores.avg_score DESC"]
    )
    rows = result.fetchall()
    # First row should have highest score
    assert rows[0][1] == 95  # Diana
    assert rows[-1][1] == 78  # Charlie


def test_semantic_layer_limit(snowflake_layer):
    """Test LIMIT with SemanticLayer."""
    items = Model(
        name="items",
        table="""(
            SELECT 1 as id, 'A' as category UNION ALL
            SELECT 2, 'B' UNION ALL
            SELECT 3, 'A' UNION ALL
            SELECT 4, 'C' UNION ALL
            SELECT 5, 'B'
        )""",
        primary_key="id",
        dimensions=[Dimension(name="category", type="categorical")],
        metrics=[Metric(name="count", agg="count", sql="id")],
    )
    snowflake_layer.add_model(items)

    result = snowflake_layer.query(dimensions=["items.category"], metrics=["items.count"], limit=2)
    rows = result.fetchall()
    assert len(rows) == 2


@pytest.mark.skip(reason="Date functions test - consistent with BigQuery/Postgres")
def test_semantic_layer_date_functions(snowflake_layer):
    """Test date/time functions in metrics."""
    events = Model(
        name="events",
        table="""(
            SELECT TO_DATE('2024-01-15') as event_date, 1 as event_id UNION ALL
            SELECT TO_DATE('2024-01-20'), 2 UNION ALL
            SELECT TO_DATE('2024-02-10'), 3 UNION ALL
            SELECT TO_DATE('2024-02-15'), 4
        )""",
        primary_key="event_id",
        dimensions=[
            Dimension(name="month", type="time", sql="TO_CHAR(event_date, 'YYYY-MM')", granularity="month"),
            Dimension(name="year", type="time", sql="TO_CHAR(event_date, 'YYYY')", granularity="year"),
        ],
        metrics=[Metric(name="event_count", agg="count", sql="event_id")],
    )
    snowflake_layer.add_model(events)

    result = snowflake_layer.query(dimensions=["events.month"], metrics=["events.event_count"])
    rows = result.fetchall()
    results_dict = {row[0]: row[1] for row in rows}

    assert results_dict["2024-01"] == 2
    assert results_dict["2024-02"] == 2


def test_semantic_layer_symmetric_aggregates(snowflake_layer):
    """Test symmetric aggregates handle fan-out joins correctly."""
    # Create a fan-out scenario: order has multiple line_items
    orders_sym = Model(
        name="orders_sym",
        table="""(
            SELECT 1 as order_id, 100 as subtotal UNION ALL
            SELECT 2, 200
        )""",
        primary_key="order_id",
        metrics=[Metric(name="total_subtotal", agg="sum", sql="subtotal")],
    )

    line_items_sym = Model(
        name="line_items_sym",
        table="""(
            SELECT 1 as item_id, 1 as order_id, 50 as price UNION ALL
            SELECT 2, 1, 30 UNION ALL
            SELECT 3, 1, 20 UNION ALL
            SELECT 4, 2, 100 UNION ALL
            SELECT 5, 2, 100
        )""",
        primary_key="item_id",
        metrics=[Metric(name="total_price", agg="sum", sql="price")],
        relationships=[Relationship(name="orders_sym", type="many_to_one", foreign_key="order_id")],
    )

    snowflake_layer.add_model(orders_sym)
    snowflake_layer.add_model(line_items_sym)

    # Query both metrics - should use symmetric aggregation to avoid fan-out
    result = snowflake_layer.query(metrics=["orders_sym.total_subtotal", "line_items_sym.total_price"])
    row = result.fetchone()
    cols = [desc[0] for desc in result.description]
    row_dict = dict(zip(cols, row))

    # Without symmetric aggregation, total_subtotal would be inflated
    # Snowflake returns uppercase column names
    assert row_dict["TOTAL_SUBTOTAL"] == 300  # 100 + 200, not inflated
    assert row_dict["TOTAL_PRICE"] == 300  # 50+30+20+100+100


def test_semantic_layer_multiple_joins(snowflake_layer):
    """Test joining 3+ models together."""
    users_multi = Model(
        name="users_multi",
        table="""(
            SELECT 1 as user_id, 'Alice' as name UNION ALL
            SELECT 2, 'Bob'
        )""",
        primary_key="user_id",
        dimensions=[Dimension(name="name", type="categorical")],
    )

    orders_multi = Model(
        name="orders_multi",
        table="""(
            SELECT 1 as order_id, 1 as user_id, 1 as product_id, 100 as amount UNION ALL
            SELECT 2, 1, 2, 150 UNION ALL
            SELECT 3, 2, 1, 200
        )""",
        primary_key="order_id",
        metrics=[Metric(name="total", agg="sum", sql="amount")],
        relationships=[
            Relationship(name="users_multi", type="many_to_one", foreign_key="user_id"),
            Relationship(name="products_multi", type="many_to_one", foreign_key="product_id"),
        ],
    )

    products_multi = Model(
        name="products_multi",
        table="""(
            SELECT 1 as product_id, 'Widget' as product_name UNION ALL
            SELECT 2, 'Gadget'
        )""",
        primary_key="product_id",
        dimensions=[Dimension(name="product_name", type="categorical")],
    )

    snowflake_layer.add_model(users_multi)
    snowflake_layer.add_model(products_multi)
    snowflake_layer.add_model(orders_multi)

    # Query across 3 models
    result = snowflake_layer.query(
        metrics=["orders_multi.total"], dimensions=["users_multi.name", "products_multi.product_name"]
    )
    rows = result.fetchall()
    results_dict = {(row[0], row[1]): row[2] for row in rows}

    assert results_dict[("Alice", "Widget")] == 100
    assert results_dict[("Alice", "Gadget")] == 150
    assert results_dict[("Bob", "Widget")] == 200


def test_semantic_layer_catalog_qualified_table(snowflake_layer):
    """Test that fully-qualified table names (catalog.schema.table) work correctly.

    GitHub issue: User reported that catalog references in Cube models aren't working.
    Snowflake uses three-part naming: database.schema.table (catalog=database in Snowflake).
    """
    # Create a table in a specific database and schema using fakesnow
    # Note: In fakesnow, we create the database and schema first
    snowflake_layer.adapter.execute("CREATE DATABASE IF NOT EXISTS my_catalog")
    snowflake_layer.adapter.execute("CREATE SCHEMA IF NOT EXISTS my_catalog.my_schema")
    snowflake_layer.adapter.execute("""
        CREATE OR REPLACE TABLE my_catalog.my_schema.catalog_orders (
            order_id INTEGER,
            amount DECIMAL(10,2),
            status VARCHAR(50)
        )
    """)
    snowflake_layer.adapter.execute("""
        INSERT INTO my_catalog.my_schema.catalog_orders VALUES
        (1, 100.00, 'completed'),
        (2, 200.00, 'pending'),
        (3, 150.00, 'completed')
    """)

    # Define a model with fully-qualified table name (catalog.schema.table)
    catalog_orders = Model(
        name="catalog_orders",
        table="my_catalog.my_schema.catalog_orders",
        primary_key="order_id",
        dimensions=[
            Dimension(name="status", type="categorical"),
        ],
        metrics=[
            Metric(name="total_revenue", agg="sum", sql="amount"),
            Metric(name="order_count", agg="count", sql="order_id"),
        ],
    )
    snowflake_layer.add_model(catalog_orders)

    # Test that queries work with the fully-qualified table name
    result = snowflake_layer.query(metrics=["catalog_orders.total_revenue"])
    row = result.fetchone()
    assert row[0] == 450.0  # 100 + 200 + 150

    # Test with dimension grouping
    result = snowflake_layer.query(metrics=["catalog_orders.total_revenue"], dimensions=["catalog_orders.status"])
    rows = result.fetchall()
    cols = [desc[0] for desc in result.description]
    results_dict = {dict(zip(cols, row))["STATUS"]: dict(zip(cols, row))["TOTAL_REVENUE"] for row in rows}

    assert results_dict["completed"] == 250.0  # 100 + 150
    assert results_dict["pending"] == 200.0

    # Verify the generated SQL contains the full catalog.schema.table reference
    sql = snowflake_layer.compile(metrics=["catalog_orders.total_revenue"])
    assert "my_catalog.my_schema.catalog_orders" in sql.lower()


def test_semantic_layer_catalog_with_cube_import(snowflake_layer):
    """Test that Cube models with catalog-qualified tables work after import.

    This simulates importing a Cube model that references a catalog (database) in Snowflake.
    """
    import tempfile
    from pathlib import Path

    from sidemantic.adapters.cube import CubeAdapter

    # Create a Cube YAML file with catalog-qualified table
    cube_yaml = """
cubes:
  - name: sf_orders
    sql_table: my_database.my_schema.orders
    description: Orders from Snowflake with catalog reference

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

      - name: status
        sql: "${CUBE}.status"
        type: string

    measures:
      - name: count
        type: count

      - name: revenue
        sql: "${CUBE}.amount"
        type: sum
"""

    # Write to temp file
    with tempfile.NamedTemporaryFile(mode="w", suffix=".yml", delete=False) as f:
        f.write(cube_yaml)
        temp_path = Path(f.name)

    try:
        # Parse with Cube adapter
        adapter = CubeAdapter()
        graph = adapter.parse(temp_path)

        # Verify the model was parsed correctly
        assert "sf_orders" in graph.models
        sf_orders = graph.models["sf_orders"]

        # Verify the table name preserves the catalog reference
        assert sf_orders.table == "my_database.my_schema.orders"

        # Verify ${CUBE} was normalized to {model} in dimensions
        status_dim = sf_orders.get_dimension("status")
        assert status_dim is not None
        assert "{model}" in status_dim.sql
        assert "${CUBE}" not in status_dim.sql

        # Verify ${CUBE} was normalized in measures
        revenue_metric = sf_orders.get_metric("revenue")
        assert revenue_metric is not None
        assert "{model}" in revenue_metric.sql

    finally:
        temp_path.unlink(missing_ok=True)
