"""Tests for OSI adapter parsing."""

import tempfile
from pathlib import Path

import pytest
import yaml

from sidemantic.adapters.osi import OSIAdapter
from sidemantic.core.dimension import Dimension
from sidemantic.core.metric import Metric
from sidemantic.core.model import Model
from sidemantic.core.relationship import Relationship
from sidemantic.core.semantic_graph import SemanticGraph

# =============================================================================
# BASIC PARSING TESTS
# =============================================================================


def test_import_simple_osi_example():
    """Test importing a simple OSI YAML file."""
    adapter = OSIAdapter()
    graph = adapter.parse("tests/fixtures/osi/simple.yaml")

    # Verify models loaded
    assert "events" in graph.models
    events = graph.models["events"]

    # Verify table/source
    assert events.table == "analytics.events"

    # Verify primary key
    assert events.primary_key == "event_id"

    # Verify dimensions
    dim_names = [d.name for d in events.dimensions]
    assert "event_id" in dim_names
    assert "user_id" in dim_names
    assert "event_type" in dim_names
    assert "event_time" in dim_names

    # Verify time dimension
    event_time = events.get_dimension("event_time")
    assert event_time.type == "time"

    # Verify default_time_dimension was set
    assert events.default_time_dimension == "event_time"


def test_import_current_version_and_semantic_model_metadata():
    """Test current OSI top-level metadata is preserved on the graph."""
    osi_yaml = {
        "version": OSIAdapter.OSI_VERSION,
        "semantic_model": [
            {
                "name": "current_model",
                "description": "Current OSI model",
                "ai_context": {"instructions": "Use for tests"},
                "custom_extensions": [{"vendor_name": "DBT", "data": '{"project_name": "analytics"}'}],
                "datasets": [
                    {
                        "name": "events",
                        "source": "analytics.events",
                        "fields": [
                            {
                                "name": "event_id",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "event_id"}]},
                            }
                        ],
                    }
                ],
            }
        ],
    }

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        yaml.dump(osi_yaml, f)
        temp_path = Path(f.name)

    try:
        graph = OSIAdapter().parse(temp_path)

        osi_meta = graph.metadata["osi"]
        assert osi_meta["version"] == OSIAdapter.OSI_VERSION
        sm_meta = osi_meta["semantic_models"][0]
        assert sm_meta["name"] == "current_model"
        assert sm_meta["description"] == "Current OSI model"
        assert sm_meta["ai_context"]["instructions"] == "Use for tests"
        assert sm_meta["custom_extensions"][0]["vendor_name"] == "DBT"

        OSIAdapter().export(graph, temp_path)
        with open(temp_path) as f:
            exported = yaml.safe_load(f)

        assert exported["version"] == OSIAdapter.OSI_VERSION
        exported_sm = exported["semantic_model"][0]
        assert exported_sm["name"] == "current_model"
        assert exported_sm["description"] == "Current OSI model"
        assert exported_sm["ai_context"]["instructions"] == "Use for tests"
        assert exported_sm["custom_extensions"][0]["vendor_name"] == "DBT"
    finally:
        temp_path.unlink()


def test_import_ontology_mapping_semantic_model():
    """Test ontology files expose mapped logical semantic models."""
    osi_yaml = {
        "version": OSIAdapter.OSI_VERSION,
        "name": "flight_ontology",
        "ontology": [
            {
                "concept": {"name": "Flight", "type": "EntityType", "identify_by": ["id"]},
                "relationships": [
                    {
                        "name": "id",
                        "roles": [{"concept": "String"}],
                        "verbalizes": ["{Flight} id {String}"],
                    }
                ],
            }
        ],
        "ontology_mappings": [
            {
                "name": "flights_map",
                "description": "Flight logical mapping",
                "semantic_model": {
                    "name": "logical_flights",
                    "description": "Mapped logical model",
                    "datasets": [
                        {
                            "name": "flights",
                            "source": "analytics.flights",
                            "fields": [
                                {
                                    "name": "flight_id",
                                    "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "flight_id"}]},
                                }
                            ],
                        }
                    ],
                },
                "concept_mappings": [{"concept": "Flight"}],
            }
        ],
    }

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        yaml.dump(osi_yaml, f)
        temp_path = Path(f.name)

    try:
        graph = OSIAdapter().parse(temp_path)

        assert "flights" in graph.models
        assert graph.models["flights"].table == "analytics.flights"
        osi_meta = graph.metadata["osi"]
        assert osi_meta["ontology"][0]["concept"]["name"] == "Flight"
        sm_meta = osi_meta["semantic_models"][0]
        assert sm_meta["source"] == "ontology_mappings[0].semantic_model"
        assert sm_meta["name"] == "logical_flights"
        assert sm_meta["ontology_mapping"]["name"] == "flights_map"
        assert sm_meta["ontology_mapping"]["concept_mappings"] == [{"concept": "Flight"}]
    finally:
        temp_path.unlink()


def test_import_ecommerce_osi_example():
    """Test importing the ecommerce OSI YAML file."""
    adapter = OSIAdapter()
    graph = adapter.parse("tests/fixtures/osi/ecommerce.yaml")

    # Verify models loaded
    assert "orders" in graph.models
    assert "customers" in graph.models

    orders = graph.models["orders"]
    customers = graph.models["customers"]

    # Verify table references
    assert orders.table == "sales.public.orders"
    assert customers.table == "sales.public.customers"

    # Verify dimensions
    order_dims = [d.name for d in orders.dimensions]
    assert "order_id" in order_dims
    assert "customer_id" in order_dims
    assert "order_date" in order_dims
    assert "status" in order_dims
    assert "amount" in order_dims

    # Verify time dimension
    order_date = orders.get_dimension("order_date")
    assert order_date.type == "time"

    # Verify relationships
    assert len(orders.relationships) == 1
    rel = orders.relationships[0]
    assert rel.name == "customers"
    assert rel.type == "many_to_one"
    assert rel.foreign_key == "customer_id"


def test_import_osi_with_metrics():
    """Test importing OSI file with metrics."""
    adapter = OSIAdapter()
    graph = adapter.parse("tests/fixtures/osi/ecommerce.yaml")

    # Verify graph-level metrics
    assert "total_revenue" in graph.metrics
    assert "order_count" in graph.metrics
    assert "avg_order_value" in graph.metrics
    assert "customer_count" in graph.metrics

    # Verify metric properties
    total_revenue = graph.metrics["total_revenue"]
    assert total_revenue.description == "Total revenue from all orders"
    assert total_revenue.agg == "sum"

    order_count = graph.metrics["order_count"]
    assert order_count.agg == "count"

    customer_count = graph.metrics["customer_count"]
    assert customer_count.agg == "count_distinct"


def test_import_kitchen_sink():
    """Test importing the kitchen sink OSI fixture."""
    adapter = OSIAdapter()
    graph = adapter.parse("tests/fixtures/osi/kitchen_sink.yaml")

    # Verify all models loaded
    assert "store_sales" in graph.models
    assert "customer" in graph.models
    assert "date_dim" in graph.models
    assert "store" in graph.models

    # Verify store_sales with composite primary key
    store_sales = graph.models["store_sales"]
    assert store_sales.table == "tpcds.public.store_sales"
    assert store_sales.primary_key == ["ss_item_sk", "ss_ticket_number"]  # Full composite key
    assert store_sales.primary_key_columns == ["ss_item_sk", "ss_ticket_number"]

    # Verify unique_keys
    assert store_sales.unique_keys == [["ss_item_sk", "ss_ticket_number"]]

    # Verify ai_context stored in meta
    assert store_sales.meta is not None
    assert "ai_context" in store_sales.meta
    assert store_sales.meta["ai_context"]["synonyms"] == ["sales transactions", "store purchases"]

    # Verify relationships
    assert len(store_sales.relationships) == 3
    rel_names = [r.name for r in store_sales.relationships]
    assert "customer" in rel_names
    assert "date_dim" in rel_names
    assert "store" in rel_names

    # Verify computed field with multi-dialect
    customer = graph.models["customer"]
    full_name = customer.get_dimension("c_full_name")
    assert full_name is not None
    # Should use ANSI_SQL dialect
    assert full_name.sql == "c_first_name || ' ' || c_last_name"
    assert full_name.label == "Full Name"

    # Verify metrics
    assert "total_sales" in graph.metrics
    assert "avg_basket_size" in graph.metrics


# =============================================================================
# DIMENSION PARSING TESTS
# =============================================================================


def test_osi_dimension_type_mapping():
    """Test OSI dimension.is_time mapping."""
    osi_yaml = {
        "semantic_model": [
            {
                "name": "test",
                "datasets": [
                    {
                        "name": "data",
                        "source": "test.data",
                        "fields": [
                            {
                                "name": "id",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "id"}]},
                            },
                            {
                                "name": "created_at",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "created_at"}]},
                                "dimension": {"is_time": True},
                            },
                            {
                                "name": "category",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "category"}]},
                                "dimension": {"is_time": False},
                            },
                        ],
                    }
                ],
            }
        ]
    }

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        yaml.dump(osi_yaml, f)
        temp_path = Path(f.name)

    try:
        adapter = OSIAdapter()
        graph = adapter.parse(temp_path)

        model = graph.models["data"]

        id_dim = model.get_dimension("id")
        assert id_dim.type == "categorical"

        created_at = model.get_dimension("created_at")
        assert created_at.type == "time"
        assert created_at.granularity == "day"

        category = model.get_dimension("category")
        assert category.type == "categorical"
    finally:
        temp_path.unlink()


def test_osi_multi_dialect_prefers_ansi():
    """Test that ANSI_SQL dialect is preferred when multiple dialects present."""
    osi_yaml = {
        "semantic_model": [
            {
                "name": "test",
                "datasets": [
                    {
                        "name": "data",
                        "source": "test.data",
                        "fields": [
                            {
                                "name": "full_name",
                                "expression": {
                                    "dialects": [
                                        {"dialect": "SNOWFLAKE", "expression": "CONCAT(first, ' ', last)"},
                                        {"dialect": "ANSI_SQL", "expression": "first || ' ' || last"},
                                        {"dialect": "DATABRICKS", "expression": "concat(first, ' ', last)"},
                                    ]
                                },
                            },
                        ],
                    }
                ],
            }
        ]
    }

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        yaml.dump(osi_yaml, f)
        temp_path = Path(f.name)

    try:
        adapter = OSIAdapter()
        graph = adapter.parse(temp_path)

        model = graph.models["data"]
        full_name = model.get_dimension("full_name")
        assert full_name.sql == "first || ' ' || last"  # ANSI_SQL preferred
    finally:
        temp_path.unlink()


# =============================================================================
# RELATIONSHIP PARSING TESTS
# =============================================================================


def test_osi_relationship_parsing():
    """Test OSI relationship parsing."""
    adapter = OSIAdapter()
    graph = adapter.parse("tests/fixtures/osi/ecommerce.yaml")

    orders = graph.models["orders"]
    assert len(orders.relationships) == 1

    rel = orders.relationships[0]
    assert rel.name == "customers"
    assert rel.type == "many_to_one"
    assert rel.foreign_key == "customer_id"
    assert rel.primary_key == "customer_id"
    assert rel.metadata["osi_name"] == "orders_to_customers"


def test_osi_relationship_metadata_roundtrip():
    """Test relationship-level OSI metadata is preserved."""
    osi_yaml = {
        "version": OSIAdapter.OSI_VERSION,
        "semantic_model": [
            {
                "name": "test",
                "datasets": [
                    {"name": "orders", "source": "public.orders"},
                    {"name": "customers", "source": "public.customers"},
                ],
                "relationships": [
                    {
                        "name": "orders_to_customers",
                        "from": "orders",
                        "to": "customers",
                        "from_columns": ["customer_id"],
                        "to_columns": ["id"],
                        "ai_context": {"synonyms": ["buyer"]},
                        "custom_extensions": [{"vendor_name": "COMMON", "data": '{"join": "customer"}'}],
                    }
                ],
            }
        ],
    }

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        yaml.dump(osi_yaml, f)
        temp_path = Path(f.name)

    try:
        adapter = OSIAdapter()
        graph = adapter.parse(temp_path)
        rel = graph.models["orders"].relationships[0]
        assert rel.metadata["osi_name"] == "orders_to_customers"
        assert rel.metadata["ai_context"]["synonyms"] == ["buyer"]
        assert rel.metadata["custom_extensions"][0]["vendor_name"] == "COMMON"

        adapter.export(graph, temp_path)
        with open(temp_path) as f:
            data = yaml.safe_load(f)

        exported_rel = data["semantic_model"][0]["relationships"][0]
        assert exported_rel["name"] == "orders_to_customers"
        assert exported_rel["ai_context"]["synonyms"] == ["buyer"]
        assert exported_rel["custom_extensions"][0]["vendor_name"] == "COMMON"
        assert isinstance(exported_rel["custom_extensions"][0]["data"], str)
    finally:
        temp_path.unlink()


def test_osi_multiple_relationships():
    """Test parsing multiple relationships from OSI."""
    adapter = OSIAdapter()
    graph = adapter.parse("tests/fixtures/osi/kitchen_sink.yaml")

    store_sales = graph.models["store_sales"]
    assert len(store_sales.relationships) == 3

    # Check each relationship
    customer_rel = next((r for r in store_sales.relationships if r.name == "customer"), None)
    assert customer_rel is not None
    assert customer_rel.foreign_key == "ss_customer_sk"
    assert customer_rel.primary_key == "c_customer_sk"

    date_rel = next((r for r in store_sales.relationships if r.name == "date_dim"), None)
    assert date_rel is not None
    assert date_rel.foreign_key == "ss_sold_date_sk"
    assert date_rel.primary_key == "d_date_sk"


# =============================================================================
# METRIC PARSING TESTS
# =============================================================================


def test_osi_metric_aggregation_detection():
    """Test that aggregation types are detected from OSI metric expressions."""
    adapter = OSIAdapter()
    graph = adapter.parse("tests/fixtures/osi/ecommerce.yaml")

    total_revenue = graph.metrics["total_revenue"]
    assert total_revenue.agg == "sum"

    order_count = graph.metrics["order_count"]
    assert order_count.agg == "count"

    avg_order = graph.metrics["avg_order_value"]
    assert avg_order.agg == "avg"

    customer_count = graph.metrics["customer_count"]
    assert customer_count.agg == "count_distinct"


def test_osi_complex_metric_expressions():
    """Test parsing complex metric expressions like ratios."""
    adapter = OSIAdapter()
    graph = adapter.parse("tests/fixtures/osi/kitchen_sink.yaml")

    # avg_basket_size is a ratio: SUM / COUNT DISTINCT
    avg_basket = graph.metrics["avg_basket_size"]
    # The Metric class should detect this as a derived metric
    assert avg_basket.sql is not None


# =============================================================================
# ERROR HANDLING TESTS
# =============================================================================


def test_osi_parse_empty_file():
    """Test parsing empty YAML file returns empty graph."""
    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        f.write("")
        temp_path = Path(f.name)

    try:
        adapter = OSIAdapter()
        graph = adapter.parse(temp_path)
        assert len(graph.models) == 0
    finally:
        temp_path.unlink()


def test_osi_parse_nonexistent_file():
    """Test parsing nonexistent file raises FileNotFoundError."""
    adapter = OSIAdapter()
    with pytest.raises(FileNotFoundError, match="Path does not exist"):
        adapter.parse(Path("/nonexistent/file.yaml"))


def test_osi_parse_nonexistent_directory():
    """Test parsing nonexistent directory raises FileNotFoundError."""
    adapter = OSIAdapter()
    with pytest.raises(FileNotFoundError, match="Path does not exist"):
        adapter.parse(Path("/nonexistent/path/"))


def test_osi_dataset_without_name():
    """Test dataset without name is skipped."""
    osi_yaml = {
        "semantic_model": [
            {
                "name": "test",
                "datasets": [{"source": "test.data"}],  # Missing name
            }
        ]
    }

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        yaml.dump(osi_yaml, f)
        temp_path = Path(f.name)

    try:
        adapter = OSIAdapter()
        graph = adapter.parse(temp_path)
        assert len(graph.models) == 0
    finally:
        temp_path.unlink()


def test_osi_field_without_name():
    """Test field without name is skipped."""
    osi_yaml = {
        "semantic_model": [
            {
                "name": "test",
                "datasets": [
                    {
                        "name": "data",
                        "source": "test.data",
                        "fields": [
                            {
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "id"}]},
                            }  # Missing name
                        ],
                    }
                ],
            }
        ]
    }

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        yaml.dump(osi_yaml, f)
        temp_path = Path(f.name)

    try:
        adapter = OSIAdapter()
        graph = adapter.parse(temp_path)
        assert "data" in graph.models
        assert len(graph.models["data"].dimensions) == 0
    finally:
        temp_path.unlink()


def test_osi_metric_without_expression():
    """Test metric without expression is skipped."""
    osi_yaml = {
        "semantic_model": [
            {
                "name": "test",
                "datasets": [{"name": "data", "source": "test.data"}],
                "metrics": [{"name": "bad_metric"}],  # Missing expression
            }
        ]
    }

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        yaml.dump(osi_yaml, f)
        temp_path = Path(f.name)

    try:
        adapter = OSIAdapter()
        graph = adapter.parse(temp_path)
        assert len(graph.metrics) == 0
    finally:
        temp_path.unlink()


# =============================================================================
# EXPORT TESTS
# =============================================================================


def test_osi_export_simple_model():
    """Test exporting a simple model to OSI format."""
    model = Model(
        name="orders",
        table="public.orders",
        description="Customer orders",
        primary_key="order_id",
        dimensions=[
            Dimension(name="order_id", type="categorical", sql="order_id"),
            Dimension(name="order_date", type="time", sql="order_date"),
            Dimension(name="status", type="categorical", sql="status"),
        ],
        metrics=[
            Metric(name="order_count", agg="count"),
            Metric(name="total_revenue", agg="sum", sql="amount"),
        ],
    )

    graph = SemanticGraph()
    graph.add_model(model)

    adapter = OSIAdapter()

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        temp_path = Path(f.name)

    try:
        adapter.export(graph, temp_path)

        with open(temp_path) as f:
            data = yaml.safe_load(f)

        # Verify structure
        assert data["version"] == OSIAdapter.OSI_VERSION
        assert "semantic_model" in data
        assert len(data["semantic_model"]) == 1

        sm = data["semantic_model"][0]
        assert "datasets" in sm
        assert "metrics" in sm

        # Verify dataset
        datasets = sm["datasets"]
        assert len(datasets) == 1
        assert datasets[0]["name"] == "orders"
        assert datasets[0]["source"] == "public.orders"
        assert datasets[0]["primary_key"] == ["order_id"]

        # Verify fields
        fields = datasets[0]["fields"]
        assert len(fields) == 3
        field_names = [f["name"] for f in fields]
        assert "order_id" in field_names
        assert "order_date" in field_names
        assert "status" in field_names

        # Verify time dimension has is_time flag
        order_date_field = next(f for f in fields if f["name"] == "order_date")
        assert order_date_field["dimension"]["is_time"] is True

        # Verify metrics
        metrics = sm["metrics"]
        assert len(metrics) == 2
    finally:
        temp_path.unlink()


def test_osi_export_with_relationships():
    """Test exporting models with relationships to OSI format."""
    orders = Model(
        name="orders",
        table="public.orders",
        primary_key="order_id",
        dimensions=[Dimension(name="order_id", type="categorical", sql="order_id")],
    )
    orders.relationships.append(
        Relationship(name="customers", type="many_to_one", foreign_key="customer_id", primary_key="id")
    )

    customers = Model(
        name="customers",
        table="public.customers",
        primary_key="id",
        dimensions=[Dimension(name="id", type="categorical", sql="id")],
    )

    graph = SemanticGraph()
    graph.add_model(orders)
    graph.add_model(customers)

    adapter = OSIAdapter()

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        temp_path = Path(f.name)

    try:
        adapter.export(graph, temp_path)

        with open(temp_path) as f:
            data = yaml.safe_load(f)

        sm = data["semantic_model"][0]

        # Verify relationships
        assert "relationships" in sm
        relationships = sm["relationships"]
        assert len(relationships) == 1

        rel = relationships[0]
        assert rel["from"] == "orders"
        assert rel["to"] == "customers"
        assert rel["from_columns"] == ["customer_id"]
        assert rel["to_columns"] == ["id"]
    finally:
        temp_path.unlink()


# =============================================================================
# ROUNDTRIP TESTS
# =============================================================================


def test_osi_roundtrip():
    """Test export -> import roundtrip preserves model structure."""
    # Create original model
    original = Model(
        name="sales",
        table="public.sales",
        description="Sales data",
        primary_key="sale_id",
        dimensions=[
            Dimension(name="sale_id", type="categorical", sql="sale_id"),
            Dimension(name="sale_date", type="time", sql="sale_date"),
            Dimension(name="region", type="categorical", sql="region"),
        ],
        metrics=[
            Metric(name="total_sales", agg="sum", sql="amount"),
            Metric(name="sale_count", agg="count"),
        ],
    )

    graph = SemanticGraph()
    graph.add_model(original)

    adapter = OSIAdapter()

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        temp_path = Path(f.name)

    try:
        # Export
        adapter.export(graph, temp_path)

        # Import back
        graph2 = adapter.parse(temp_path)

        # Verify
        assert "sales" in graph2.models
        sales = graph2.models["sales"]

        assert sales.table == "public.sales"
        assert sales.primary_key == "sale_id"

        dim_names = [d.name for d in sales.dimensions]
        assert "sale_id" in dim_names
        assert "sale_date" in dim_names
        assert "region" in dim_names

        sale_date = sales.get_dimension("sale_date")
        assert sale_date.type == "time"

        # Verify metrics came back (as graph-level)
        assert "total_sales" in graph2.metrics
        assert "sale_count" in graph2.metrics
    finally:
        temp_path.unlink()


def test_osi_directory_parsing():
    """Test parsing OSI files from a directory with unique models."""
    # Create a temp directory with a single file to test directory parsing
    osi_yaml = {
        "semantic_model": [
            {
                "name": "test",
                "datasets": [
                    {
                        "name": "test_events",
                        "source": "test.events",
                        "fields": [
                            {
                                "name": "id",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "id"}]},
                            },
                        ],
                    },
                    {
                        "name": "test_users",
                        "source": "test.users",
                        "fields": [
                            {
                                "name": "user_id",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "user_id"}]},
                            },
                        ],
                    },
                ],
            }
        ]
    }

    with tempfile.TemporaryDirectory() as tmpdir:
        yaml_path = Path(tmpdir) / "test.yaml"
        with open(yaml_path, "w") as f:
            yaml.dump(osi_yaml, f)

        adapter = OSIAdapter()
        graph = adapter.parse(tmpdir)

        assert "test_events" in graph.models
        assert "test_users" in graph.models


# =============================================================================
# NEW FEATURE TESTS: Multi-column keys, meta, dialects
# =============================================================================


def test_import_composite_primary_key():
    """Test importing dataset with composite primary key."""
    osi_yaml = {
        "semantic_model": [
            {
                "name": "test",
                "datasets": [
                    {
                        "name": "order_items",
                        "source": "public.order_items",
                        "primary_key": ["order_id", "item_id"],
                        "fields": [
                            {
                                "name": "order_id",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "order_id"}]},
                            },
                            {
                                "name": "item_id",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "item_id"}]},
                            },
                        ],
                    }
                ],
            }
        ]
    }

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        yaml.dump(osi_yaml, f)
        temp_path = Path(f.name)

    try:
        adapter = OSIAdapter()
        graph = adapter.parse(temp_path)

        model = graph.models["order_items"]
        assert model.primary_key == ["order_id", "item_id"]
        assert model.primary_key_columns == ["order_id", "item_id"]
    finally:
        temp_path.unlink()


def test_import_unique_keys():
    """Test importing dataset with unique_keys."""
    osi_yaml = {
        "semantic_model": [
            {
                "name": "test",
                "datasets": [
                    {
                        "name": "users",
                        "source": "public.users",
                        "primary_key": ["user_id"],
                        "unique_keys": [["email"], ["tenant_id", "username"]],
                        "fields": [
                            {
                                "name": "user_id",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "user_id"}]},
                            },
                        ],
                    }
                ],
            }
        ]
    }

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        yaml.dump(osi_yaml, f)
        temp_path = Path(f.name)

    try:
        adapter = OSIAdapter()
        graph = adapter.parse(temp_path)

        model = graph.models["users"]
        assert model.unique_keys == [["email"], ["tenant_id", "username"]]
    finally:
        temp_path.unlink()


def test_import_ai_context():
    """Test importing ai_context as meta field."""
    osi_yaml = {
        "semantic_model": [
            {
                "name": "test",
                "datasets": [
                    {
                        "name": "sales",
                        "source": "public.sales",
                        "ai_context": {
                            "synonyms": ["revenue", "orders"],
                            "description_for_ai": "Contains all sales transactions",
                        },
                        "fields": [
                            {
                                "name": "amount",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "amount"}]},
                                "ai_context": {"synonyms": ["revenue", "value"]},
                            },
                        ],
                    }
                ],
                "metrics": [
                    {
                        "name": "total_sales",
                        "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "SUM(amount)"}]},
                        "ai_context": {"synonyms": ["revenue", "total revenue"]},
                    }
                ],
            }
        ]
    }

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        yaml.dump(osi_yaml, f)
        temp_path = Path(f.name)

    try:
        adapter = OSIAdapter()
        graph = adapter.parse(temp_path)

        # Model ai_context
        model = graph.models["sales"]
        assert model.meta is not None
        assert model.meta["ai_context"]["synonyms"] == ["revenue", "orders"]

        # Field ai_context
        amount_dim = model.get_dimension("amount")
        assert amount_dim.meta is not None
        assert amount_dim.meta["ai_context"]["synonyms"] == ["revenue", "value"]

        # Metric ai_context
        metric = graph.metrics["total_sales"]
        assert metric.meta is not None
        assert metric.meta["ai_context"]["synonyms"] == ["revenue", "total revenue"]
    finally:
        temp_path.unlink()


def test_import_multi_column_relationship():
    """Test importing relationship with multi-column foreign key."""
    osi_yaml = {
        "semantic_model": [
            {
                "name": "test",
                "datasets": [
                    {
                        "name": "order_items",
                        "source": "public.order_items",
                        "primary_key": ["order_id", "item_id"],
                        "fields": [
                            {
                                "name": "order_id",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "order_id"}]},
                            },
                            {
                                "name": "item_id",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "item_id"}]},
                            },
                        ],
                    },
                    {
                        "name": "shipments",
                        "source": "public.shipments",
                        "primary_key": ["shipment_id"],
                        "fields": [
                            {
                                "name": "shipment_id",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "shipment_id"}]},
                            },
                            {
                                "name": "order_id",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "order_id"}]},
                            },
                            {
                                "name": "item_id",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "item_id"}]},
                            },
                        ],
                    },
                ],
                "relationships": [
                    {
                        "name": "shipments_to_order_items",
                        "from": "shipments",
                        "to": "order_items",
                        "from_columns": ["order_id", "item_id"],
                        "to_columns": ["order_id", "item_id"],
                    }
                ],
            }
        ]
    }

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        yaml.dump(osi_yaml, f)
        temp_path = Path(f.name)

    try:
        adapter = OSIAdapter()
        graph = adapter.parse(temp_path)

        shipments = graph.models["shipments"]
        assert len(shipments.relationships) == 1

        rel = shipments.relationships[0]
        assert rel.name == "order_items"
        assert rel.foreign_key == ["order_id", "item_id"]
        assert rel.primary_key == ["order_id", "item_id"]
        assert rel.foreign_key_columns == ["order_id", "item_id"]
        assert rel.primary_key_columns == ["order_id", "item_id"]
    finally:
        temp_path.unlink()


def test_export_multi_dialect():
    """Test exporting with multiple SQL dialects."""
    model = Model(
        name="events",
        table="public.events",
        primary_key="event_id",
        dimensions=[
            Dimension(name="event_id", type="categorical", sql="event_id"),
            Dimension(name="created_at", type="time", sql="created_at"),
        ],
        metrics=[
            Metric(name="event_count", agg="count"),
        ],
    )

    graph = SemanticGraph()
    graph.add_model(model)

    adapter = OSIAdapter()

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        temp_path = Path(f.name)

    try:
        # Export with multiple dialects
        adapter.export(graph, temp_path, dialects=["ANSI_SQL", "SNOWFLAKE"])

        with open(temp_path) as f:
            data = yaml.safe_load(f)

        sm = data["semantic_model"][0]
        fields = sm["datasets"][0]["fields"]

        # Verify each field has both dialects
        for field in fields:
            dialects = field["expression"]["dialects"]
            dialect_names = [d["dialect"] for d in dialects]
            assert "ANSI_SQL" in dialect_names
            assert "SNOWFLAKE" in dialect_names

        # Verify metrics also have multiple dialects
        metrics = sm["metrics"]
        for metric in metrics:
            dialects = metric["expression"]["dialects"]
            dialect_names = [d["dialect"] for d in dialects]
            assert "ANSI_SQL" in dialect_names
            assert "SNOWFLAKE" in dialect_names
    finally:
        temp_path.unlink()


def test_export_composite_primary_key():
    """Test exporting model with composite primary key."""
    model = Model(
        name="order_items",
        table="public.order_items",
        primary_key=["order_id", "item_id"],
        dimensions=[
            Dimension(name="order_id", type="categorical", sql="order_id"),
            Dimension(name="item_id", type="categorical", sql="item_id"),
        ],
    )

    graph = SemanticGraph()
    graph.add_model(model)

    adapter = OSIAdapter()

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        temp_path = Path(f.name)

    try:
        adapter.export(graph, temp_path)

        with open(temp_path) as f:
            data = yaml.safe_load(f)

        dataset = data["semantic_model"][0]["datasets"][0]
        assert dataset["primary_key"] == ["order_id", "item_id"]
    finally:
        temp_path.unlink()


def test_export_unique_keys():
    """Test exporting model with unique_keys."""
    model = Model(
        name="users",
        table="public.users",
        primary_key="user_id",
        unique_keys=[["email"], ["tenant_id", "username"]],
        dimensions=[
            Dimension(name="user_id", type="categorical", sql="user_id"),
        ],
    )

    graph = SemanticGraph()
    graph.add_model(model)

    adapter = OSIAdapter()

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        temp_path = Path(f.name)

    try:
        adapter.export(graph, temp_path)

        with open(temp_path) as f:
            data = yaml.safe_load(f)

        dataset = data["semantic_model"][0]["datasets"][0]
        assert dataset["unique_keys"] == [["email"], ["tenant_id", "username"]]
    finally:
        temp_path.unlink()


def test_export_meta_as_ai_context():
    """Test exporting meta fields as ai_context and custom_extensions."""
    model = Model(
        name="sales",
        table="public.sales",
        primary_key="sale_id",
        meta={
            "ai_context": {"synonyms": ["revenue", "transactions"]},
            "custom_extensions": {"vendor_id": "acme123"},
        },
        dimensions=[
            Dimension(
                name="amount",
                type="categorical",
                sql="amount",
                meta={"ai_context": {"synonyms": ["value", "price"]}},
            ),
        ],
        metrics=[
            Metric(
                name="total_sales",
                agg="sum",
                sql="amount",
                meta={"ai_context": {"synonyms": ["revenue"]}},
            ),
        ],
    )

    graph = SemanticGraph()
    graph.add_model(model)

    adapter = OSIAdapter()

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        temp_path = Path(f.name)

    try:
        adapter.export(graph, temp_path)

        with open(temp_path) as f:
            data = yaml.safe_load(f)

        sm = data["semantic_model"][0]
        dataset = sm["datasets"][0]

        # Model meta
        assert dataset["ai_context"] == {"synonyms": ["revenue", "transactions"]}
        assert dataset["custom_extensions"] == [{"vendor_name": "SIDEMANTIC", "data": '{"vendor_id": "acme123"}'}]

        # Field meta
        amount_field = next(f for f in dataset["fields"] if f["name"] == "amount")
        assert amount_field["ai_context"] == {"synonyms": ["value", "price"]}

        # Metric meta
        metric = sm["metrics"][0]
        assert metric["ai_context"] == {"synonyms": ["revenue"]}
    finally:
        temp_path.unlink()


def test_roundtrip_preserves_meta():
    """Test that roundtrip preserves meta fields (ai_context, custom_extensions)."""
    original = Model(
        name="products",
        table="public.products",
        primary_key=["product_id", "variant_id"],
        unique_keys=[["sku"]],
        meta={
            "ai_context": {"synonyms": ["items", "inventory"]},
            "custom_extensions": {"catalog_version": "2.0"},
        },
        dimensions=[
            Dimension(
                name="product_id",
                type="categorical",
                sql="product_id",
                meta={"ai_context": {"synonyms": ["item_id"]}},
            ),
            Dimension(name="variant_id", type="categorical", sql="variant_id"),
        ],
    )

    graph = SemanticGraph()
    graph.add_model(original)

    adapter = OSIAdapter()

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        temp_path = Path(f.name)

    try:
        # Export
        adapter.export(graph, temp_path)

        with open(temp_path) as f:
            exported = yaml.safe_load(f)

        dataset = exported["semantic_model"][0]["datasets"][0]
        assert dataset["custom_extensions"] == [{"vendor_name": "SIDEMANTIC", "data": '{"catalog_version": "2.0"}'}]

        # Import back
        graph2 = adapter.parse(temp_path)

        products = graph2.models["products"]

        # Verify composite primary key preserved
        assert products.primary_key == ["product_id", "variant_id"]

        # Verify unique_keys preserved
        assert products.unique_keys == [["sku"]]

        # Verify model meta preserved
        assert products.meta is not None
        assert products.meta["ai_context"]["synonyms"] == ["items", "inventory"]
        assert products.meta["custom_extensions"]["catalog_version"] == "2.0"

        # Verify dimension meta preserved
        product_id_dim = products.get_dimension("product_id")
        assert product_id_dim.meta is not None
        assert product_id_dim.meta["ai_context"]["synonyms"] == ["item_id"]
    finally:
        temp_path.unlink()


def test_export_multi_column_relationship():
    """Test exporting relationship with multi-column keys."""
    order_items = Model(
        name="order_items",
        table="public.order_items",
        primary_key=["order_id", "item_id"],
        dimensions=[
            Dimension(name="order_id", type="categorical", sql="order_id"),
            Dimension(name="item_id", type="categorical", sql="item_id"),
        ],
    )

    shipments = Model(
        name="shipments",
        table="public.shipments",
        primary_key="shipment_id",
        dimensions=[
            Dimension(name="shipment_id", type="categorical", sql="shipment_id"),
        ],
    )
    shipments.relationships.append(
        Relationship(
            name="order_items",
            type="many_to_one",
            foreign_key=["order_id", "item_id"],
            primary_key=["order_id", "item_id"],
        )
    )

    graph = SemanticGraph()
    graph.add_model(order_items)
    graph.add_model(shipments)

    adapter = OSIAdapter()

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        temp_path = Path(f.name)

    try:
        adapter.export(graph, temp_path)

        with open(temp_path) as f:
            data = yaml.safe_load(f)

        sm = data["semantic_model"][0]
        relationships = sm["relationships"]

        assert len(relationships) == 1
        rel = relationships[0]
        assert rel["from_columns"] == ["order_id", "item_id"]
        assert rel["to_columns"] == ["order_id", "item_id"]
    finally:
        temp_path.unlink()


# =============================================================================
# EDGE CASE TESTS
# =============================================================================


def test_export_empty_dialects_list():
    """Test export with empty dialects list defaults to ANSI_SQL."""
    model = Model(
        name="test",
        table="public.test",
        primary_key="id",
        dimensions=[Dimension(name="id", type="categorical", sql="id")],
    )

    graph = SemanticGraph()
    graph.add_model(model)

    adapter = OSIAdapter()

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        temp_path = Path(f.name)

    try:
        # Empty list should default to ANSI_SQL
        adapter.export(graph, temp_path, dialects=[])

        with open(temp_path) as f:
            data = yaml.safe_load(f)

        field = data["semantic_model"][0]["datasets"][0]["fields"][0]
        assert "expression" in field
        assert field["expression"]["dialects"] == [{"dialect": "ANSI_SQL", "expression": "id"}]
    finally:
        temp_path.unlink()


def test_export_unknown_dialect_raises():
    """Test export rejects dialects outside the current OSI export support."""
    model = Model(
        name="test",
        table="public.test",
        primary_key="id",
        dimensions=[Dimension(name="id", type="categorical", sql="id")],
    )

    graph = SemanticGraph()
    graph.add_model(model)

    adapter = OSIAdapter()

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        temp_path = Path(f.name)

    try:
        with pytest.raises(ValueError, match="Unsupported OSI export dialect"):
            adapter.export(graph, temp_path, dialects=["UNKNOWN_DIALECT"])
    finally:
        temp_path.unlink()


def test_import_empty_ai_context():
    """Test importing empty ai_context dict doesn't create meta."""
    osi_yaml = {
        "semantic_model": [
            {
                "name": "test",
                "datasets": [
                    {
                        "name": "data",
                        "source": "public.data",
                        "ai_context": {},  # Empty dict
                        "fields": [
                            {
                                "name": "id",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "id"}]},
                            },
                        ],
                    }
                ],
            }
        ]
    }

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        yaml.dump(osi_yaml, f)
        temp_path = Path(f.name)

    try:
        adapter = OSIAdapter()
        graph = adapter.parse(temp_path)

        model = graph.models["data"]
        # Empty ai_context is still truthy in Python, so meta gets created
        # This is actually expected behavior - empty dict is still stored
        if model.meta:
            assert model.meta.get("ai_context") == {}
    finally:
        temp_path.unlink()


def test_import_single_column_primary_key_as_string():
    """Test single-column primary key imported as string, not list."""
    osi_yaml = {
        "semantic_model": [
            {
                "name": "test",
                "datasets": [
                    {
                        "name": "users",
                        "source": "public.users",
                        "primary_key": ["user_id"],  # Single item list
                        "fields": [
                            {
                                "name": "user_id",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "user_id"}]},
                            },
                        ],
                    }
                ],
            }
        ]
    }

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        yaml.dump(osi_yaml, f)
        temp_path = Path(f.name)

    try:
        adapter = OSIAdapter()
        graph = adapter.parse(temp_path)

        model = graph.models["users"]
        # Single-column PK should be stored as string for backwards compat
        assert model.primary_key == "user_id"
        # But primary_key_columns should always return list
        assert model.primary_key_columns == ["user_id"]
    finally:
        temp_path.unlink()


def test_sqlglot_transpilation_actually_transpiles():
    """Test that sqlglot actually transpiles expressions for different dialects."""
    model = Model(
        name="test",
        table="public.test",
        primary_key="id",
        dimensions=[
            # Use an expression that differs between dialects
            Dimension(name="concat_col", type="categorical", sql="col1 || col2"),
        ],
    )

    graph = SemanticGraph()
    graph.add_model(model)

    adapter = OSIAdapter()

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        temp_path = Path(f.name)

    try:
        adapter.export(graph, temp_path, dialects=["ANSI_SQL", "SNOWFLAKE"])

        with open(temp_path) as f:
            data = yaml.safe_load(f)

        field = next(f for f in data["semantic_model"][0]["datasets"][0]["fields"] if f["name"] == "concat_col")
        dialects = field["expression"]["dialects"]

        ansi = next(d for d in dialects if d["dialect"] == "ANSI_SQL")
        snowflake = next(d for d in dialects if d["dialect"] == "SNOWFLAKE")

        # ANSI should keep ||
        assert "||" in ansi["expression"] or "col1" in ansi["expression"]
        assert snowflake["expression"] is not None
    finally:
        temp_path.unlink()


def test_nested_custom_extensions():
    """Test importing nested custom_extensions structure."""
    osi_yaml = {
        "semantic_model": [
            {
                "name": "test",
                "datasets": [
                    {
                        "name": "data",
                        "source": "public.data",
                        "custom_extensions": {
                            "vendor": {
                                "name": "acme",
                                "config": {"timeout": 30, "retries": 3},
                            },
                            "tags": ["important", "production"],
                        },
                        "fields": [
                            {
                                "name": "id",
                                "expression": {"dialects": [{"dialect": "ANSI_SQL", "expression": "id"}]},
                            },
                        ],
                    }
                ],
            }
        ]
    }

    with tempfile.NamedTemporaryFile(mode="w", suffix=".yaml", delete=False) as f:
        yaml.dump(osi_yaml, f)
        temp_path = Path(f.name)

    try:
        adapter = OSIAdapter()
        graph = adapter.parse(temp_path)

        model = graph.models["data"]
        assert model.meta is not None
        assert "custom_extensions" in model.meta

        ext = model.meta["custom_extensions"]
        assert ext["vendor"]["name"] == "acme"
        assert ext["vendor"]["config"]["timeout"] == 30
        assert ext["tags"] == ["important", "production"]
    finally:
        temp_path.unlink()


if __name__ == "__main__":
    pytest.main([__file__, "-v"])


# =============================================================================
# TPC-DS RETAIL MODEL (REAL-WORLD OSI FIXTURE) TESTS
# =============================================================================


class TestTpcdsRetailFixture:
    """Tests for the TPC-DS retail model fixture based on OSI spec examples.

    This fixture exercises: composite PKs, multi-dialect expressions, ai_context
    at every level, custom_extensions for Salesforce/DBT/Snowflake, 5 cross-dataset
    metrics, 5 datasets (1 fact + 4 dimensions), and 4 relationships.
    """

    @pytest.fixture
    def graph(self):
        adapter = OSIAdapter()
        return adapter.parse("tests/fixtures/osi/tpcds_retail.yaml")

    def test_parses_without_errors(self, graph):
        """Fixture parses cleanly into a SemanticGraph."""
        assert isinstance(graph, SemanticGraph)

    def test_dataset_count(self, graph):
        """All 5 datasets (store_sales, customer, date_dim, store, item) are loaded."""
        assert len(graph.models) == 5
        expected = {"store_sales", "customer", "date_dim", "store", "item"}
        assert set(graph.models.keys()) == expected

    def test_metric_count(self, graph):
        """All 5 cross-dataset metrics are loaded."""
        assert len(graph.metrics) == 5
        expected = {
            "total_sales",
            "total_profit",
            "customer_lifetime_value",
            "sales_by_brand",
            "store_productivity",
        }
        assert set(graph.metrics.keys()) == expected

    def test_composite_primary_key(self, graph):
        """store_sales has a composite primary key [ss_item_sk, ss_ticket_number]."""
        store_sales = graph.models["store_sales"]
        assert store_sales.primary_key == ["ss_item_sk", "ss_ticket_number"]
        assert store_sales.primary_key_columns == ["ss_item_sk", "ss_ticket_number"]

    def test_unique_keys(self, graph):
        """store_sales has unique_keys defined."""
        store_sales = graph.models["store_sales"]
        assert store_sales.unique_keys == [["ss_item_sk", "ss_ticket_number"]]

    def test_single_column_primary_key(self, graph):
        """Dimension tables have single-column primary keys stored as strings."""
        customer = graph.models["customer"]
        assert customer.primary_key == "c_customer_sk"

        date_dim = graph.models["date_dim"]
        assert date_dim.primary_key == "d_date_sk"

        store = graph.models["store"]
        assert store.primary_key == "s_store_sk"

        item = graph.models["item"]
        assert item.primary_key == "i_item_sk"

    def test_source_tables(self, graph):
        """Each dataset maps to the correct source table."""
        assert graph.models["store_sales"].table == "tpcds.public.store_sales"
        assert graph.models["customer"].table == "tpcds.public.customer"
        assert graph.models["date_dim"].table == "tpcds.public.date_dim"
        assert graph.models["store"].table == "tpcds.public.store"
        assert graph.models["item"].table == "tpcds.public.item"

    def test_store_sales_dimension_count(self, graph):
        """store_sales has 8 field dimensions."""
        store_sales = graph.models["store_sales"]
        assert len(store_sales.dimensions) == 8

    def test_customer_dimension_count(self, graph):
        """customer has 7 field dimensions."""
        customer = graph.models["customer"]
        assert len(customer.dimensions) == 7

    def test_date_dim_dimension_count(self, graph):
        """date_dim has 6 field dimensions."""
        date_dim = graph.models["date_dim"]
        assert len(date_dim.dimensions) == 6

    def test_store_dimension_count(self, graph):
        """store has 6 field dimensions."""
        store = graph.models["store"]
        assert len(store.dimensions) == 6

    def test_item_dimension_count(self, graph):
        """item has 6 field dimensions."""
        item = graph.models["item"]
        assert len(item.dimensions) == 6

    def test_time_dimension_date_dim(self, graph):
        """date_dim.d_date is recognized as a time dimension."""
        date_dim = graph.models["date_dim"]
        d_date = date_dim.get_dimension("d_date")
        assert d_date is not None
        assert d_date.type == "time"

    def test_default_time_dimension(self, graph):
        """date_dim has d_date as default_time_dimension."""
        date_dim = graph.models["date_dim"]
        assert date_dim.default_time_dimension == "d_date"

    def test_computed_field_multi_dialect(self, graph):
        """customer_full_name uses ANSI_SQL dialect (concatenation expression)."""
        customer = graph.models["customer"]
        full_name = customer.get_dimension("customer_full_name")
        assert full_name is not None
        assert full_name.sql == "c_first_name || ' ' || c_last_name"

    def test_computed_field_label(self, graph):
        """customer_full_name preserves the label attribute."""
        customer = graph.models["customer"]
        full_name = customer.get_dimension("customer_full_name")
        assert full_name.label == "Full Name"

    def test_relationships_count(self, graph):
        """store_sales has 4 relationships (to customer, date_dim, store, item)."""
        store_sales = graph.models["store_sales"]
        assert len(store_sales.relationships) == 4

    def test_relationship_targets(self, graph):
        """Relationships point to the correct dimension tables."""
        store_sales = graph.models["store_sales"]
        rel_targets = {r.name for r in store_sales.relationships}
        assert rel_targets == {"customer", "date_dim", "store", "item"}

    def test_relationship_foreign_keys(self, graph):
        """Each relationship has the correct foreign key column."""
        store_sales = graph.models["store_sales"]
        rel_map = {r.name: r for r in store_sales.relationships}

        assert rel_map["customer"].foreign_key == "ss_customer_sk"
        assert rel_map["date_dim"].foreign_key == "ss_sold_date_sk"
        assert rel_map["store"].foreign_key == "ss_store_sk"
        assert rel_map["item"].foreign_key == "ss_item_sk"

    def test_relationship_primary_keys(self, graph):
        """Each relationship has the correct primary key on the target."""
        store_sales = graph.models["store_sales"]
        rel_map = {r.name: r for r in store_sales.relationships}

        assert rel_map["customer"].primary_key == "c_customer_sk"
        assert rel_map["date_dim"].primary_key == "d_date_sk"
        assert rel_map["store"].primary_key == "s_store_sk"
        assert rel_map["item"].primary_key == "i_item_sk"

    def test_relationship_type(self, graph):
        """All relationships are many_to_one (fact to dimension)."""
        store_sales = graph.models["store_sales"]
        for rel in store_sales.relationships:
            assert rel.type == "many_to_one"

    def test_metric_total_sales(self, graph):
        """total_sales metric parses with correct expression."""
        metric = graph.metrics["total_sales"]
        assert metric.name == "total_sales"
        assert metric.description is not None
        assert "Total sales" in metric.description

    def test_metric_total_profit(self, graph):
        """total_profit metric parses correctly."""
        metric = graph.metrics["total_profit"]
        assert metric.name == "total_profit"

    def test_metric_customer_lifetime_value(self, graph):
        """customer_lifetime_value (ratio expression) parses correctly."""
        metric = graph.metrics["customer_lifetime_value"]
        assert metric.name == "customer_lifetime_value"
        assert metric.description is not None

    def test_metric_store_productivity(self, graph):
        """store_productivity (division with NULLIF) parses correctly."""
        metric = graph.metrics["store_productivity"]
        assert metric.name == "store_productivity"

    def test_ai_context_on_dataset(self, graph):
        """store_sales has ai_context with synonyms in meta."""
        store_sales = graph.models["store_sales"]
        assert store_sales.meta is not None
        assert "ai_context" in store_sales.meta
        synonyms = store_sales.meta["ai_context"]["synonyms"]
        assert "sales transactions" in synonyms
        assert "POS data" in synonyms

    def test_ai_context_on_field(self, graph):
        """Field-level ai_context is preserved in dimension meta."""
        store_sales = graph.models["store_sales"]
        ss_item = store_sales.get_dimension("ss_item_sk")
        assert ss_item.meta is not None
        assert "ai_context" in ss_item.meta
        assert "item key" in ss_item.meta["ai_context"]["synonyms"]

    def test_ai_context_on_metric(self, graph):
        """Metric-level ai_context is preserved."""
        total_sales = graph.metrics["total_sales"]
        assert total_sales.meta is not None
        assert "ai_context" in total_sales.meta
        synonyms = total_sales.meta["ai_context"]["synonyms"]
        assert "total revenue" in synonyms

    def test_ai_context_on_multiple_datasets(self, graph):
        """ai_context with synonyms exists on customer and store datasets."""
        customer = graph.models["customer"]
        assert customer.meta is not None
        assert "ai_context" in customer.meta
        assert "buyers" in customer.meta["ai_context"]["synonyms"]

        store = graph.models["store"]
        assert store.meta is not None
        assert "ai_context" in store.meta
        assert "retail location" in store.meta["ai_context"]["synonyms"]

    def test_model_level_custom_extensions(self, graph):
        """custom_extensions at semantic_model level (SALESFORCE, DBT, SNOWFLAKE) are captured.

        Semantic-model-level metadata is stored on the graph because it does not
        belong to any one Sidemantic Model.
        """
        extensions = graph.metadata["osi"]["semantic_models"][0]["custom_extensions"]
        vendors = {extension["vendor_name"] for extension in extensions}
        assert {"SALESFORCE", "DBT", "SNOWFLAKE"} <= vendors

    def test_dimension_tables_have_no_relationships(self, graph):
        """Dimension tables (customer, date_dim, store, item) have no outbound relationships."""
        for name in ["customer", "date_dim", "store", "item"]:
            model = graph.models[name]
            assert len(model.relationships) == 0, f"{name} should have no relationships"


# =============================================================================
# SUPPLY CHAIN MODEL (REAL-WORLD OSI FIXTURE) TESTS
# =============================================================================


class TestSupplyChainFixture:
    """Tests for the supply chain fixture exercising multi-dialect computed fields,
    multiple unique key constraints, composite PKs, and cross-dataset metrics.
    """

    @pytest.fixture
    def graph(self):
        adapter = OSIAdapter()
        return adapter.parse("tests/fixtures/osi/supply_chain.yaml")

    def test_parses_without_errors(self, graph):
        """Fixture parses cleanly into a SemanticGraph."""
        assert isinstance(graph, SemanticGraph)

    def test_dataset_count(self, graph):
        """All 4 datasets are loaded."""
        assert len(graph.models) == 4
        expected = {"shipments", "warehouse", "supplier", "product"}
        assert set(graph.models.keys()) == expected

    def test_metric_count(self, graph):
        """All 5 metrics are loaded."""
        assert len(graph.metrics) == 5
        expected = {
            "total_shipment_cost",
            "total_shipping_cost",
            "avg_transit_days",
            "shipment_count",
            "cost_per_unit_shipped",
        }
        assert set(graph.metrics.keys()) == expected

    def test_composite_primary_key(self, graph):
        """shipments has composite PK [shipment_id, line_item_id]."""
        shipments = graph.models["shipments"]
        assert shipments.primary_key == ["shipment_id", "line_item_id"]

    def test_multiple_unique_keys(self, graph):
        """shipments has two unique key constraints."""
        shipments = graph.models["shipments"]
        assert shipments.unique_keys is not None
        assert len(shipments.unique_keys) == 2
        assert ["shipment_id", "line_item_id"] in shipments.unique_keys
        assert ["tracking_number"] in shipments.unique_keys

    def test_shipments_field_count(self, graph):
        """shipments has 12 fields."""
        shipments = graph.models["shipments"]
        assert len(shipments.dimensions) == 12

    def test_multi_dialect_computed_field(self, graph):
        """transit_days field has ANSI_SQL expression (preferred over SNOWFLAKE)."""
        shipments = graph.models["shipments"]
        transit = shipments.get_dimension("transit_days")
        assert transit is not None
        # Adapter prefers ANSI_SQL
        assert transit.sql == "delivery_date - ship_date"

    def test_time_dimensions(self, graph):
        """shipments has two time dimensions (ship_date, delivery_date)."""
        shipments = graph.models["shipments"]
        time_dims = [d for d in shipments.dimensions if d.type == "time"]
        time_names = {d.name for d in time_dims}
        assert "ship_date" in time_names
        assert "delivery_date" in time_names
        assert len(time_dims) == 2

    def test_default_time_dimension_is_first(self, graph):
        """default_time_dimension is ship_date (first time dim encountered)."""
        shipments = graph.models["shipments"]
        assert shipments.default_time_dimension == "ship_date"

    def test_relationships_count(self, graph):
        """shipments has 3 relationships."""
        shipments = graph.models["shipments"]
        assert len(shipments.relationships) == 3

    def test_relationship_targets(self, graph):
        """Relationships point to warehouse, supplier, product."""
        shipments = graph.models["shipments"]
        targets = {r.name for r in shipments.relationships}
        assert targets == {"warehouse", "supplier", "product"}

    def test_source_tables(self, graph):
        """Each dataset maps to the correct fully qualified source."""
        assert graph.models["shipments"].table == "logistics.public.shipments"
        assert graph.models["warehouse"].table == "logistics.public.warehouses"
        assert graph.models["supplier"].table == "logistics.public.suppliers"
        assert graph.models["product"].table == "logistics.public.products"

    def test_metric_total_shipment_cost(self, graph):
        """total_shipment_cost metric parses correctly."""
        metric = graph.metrics["total_shipment_cost"]
        assert metric.name == "total_shipment_cost"
        assert metric.description is not None

    def test_metric_with_count_distinct(self, graph):
        """shipment_count uses COUNT(DISTINCT ...)."""
        metric = graph.metrics["shipment_count"]
        assert metric.name == "shipment_count"

    def test_metric_ratio_with_nullif(self, graph):
        """cost_per_unit_shipped uses division with NULLIF."""
        metric = graph.metrics["cost_per_unit_shipped"]
        assert metric.name == "cost_per_unit_shipped"

    def test_metric_multi_dialect(self, graph):
        """avg_transit_days has multi-dialect definition (ANSI_SQL preferred)."""
        metric = graph.metrics["avg_transit_days"]
        assert metric.name == "avg_transit_days"

    def test_ai_context_on_shipments(self, graph):
        """shipments dataset has ai_context synonyms."""
        shipments = graph.models["shipments"]
        assert shipments.meta is not None
        assert "ai_context" in shipments.meta
        assert "deliveries" in shipments.meta["ai_context"]["synonyms"]

    def test_ai_context_on_field(self, graph):
        """transit_days field has ai_context synonyms."""
        shipments = graph.models["shipments"]
        transit = shipments.get_dimension("transit_days")
        assert transit.meta is not None
        assert "ai_context" in transit.meta
        assert "lead time" in transit.meta["ai_context"]["synonyms"]

    def test_ai_context_on_metric(self, graph):
        """total_shipment_cost metric has ai_context."""
        metric = graph.metrics["total_shipment_cost"]
        assert metric.meta is not None
        assert "ai_context" in metric.meta
        assert "COGS shipped" in metric.meta["ai_context"]["synonyms"]

    def test_dimension_tables_no_relationships(self, graph):
        """Dimension tables have no outbound relationships."""
        for name in ["warehouse", "supplier", "product"]:
            model = graph.models[name]
            assert len(model.relationships) == 0

    def test_warehouse_dimensions(self, graph):
        """warehouse has expected fields including is_active."""
        warehouse = graph.models["warehouse"]
        dim_names = {d.name for d in warehouse.dimensions}
        assert "warehouse_id" in dim_names
        assert "warehouse_name" in dim_names
        assert "region" in dim_names
        assert "capacity_sqft" in dim_names
        assert "is_active" in dim_names

    def test_supplier_dimensions(self, graph):
        """supplier has expected fields including reliability_rating."""
        supplier = graph.models["supplier"]
        dim_names = {d.name for d in supplier.dimensions}
        assert "supplier_id" in dim_names
        assert "supplier_name" in dim_names
        assert "country" in dim_names
        assert "reliability_rating" in dim_names

    def test_model_level_custom_extensions(self, graph):
        """custom_extensions at semantic_model level (DATABRICKS, COMMON) are captured."""
        extensions = graph.metadata["osi"]["semantic_models"][0]["custom_extensions"]
        vendors = {extension["vendor_name"] for extension in extensions}
        assert {"DATABRICKS", "COMMON"} <= vendors


# =============================================================================
# OSI OFFICIAL TPC-DS MODEL (Apache 2.0 / CC BY)
# Source: https://github.com/open-semantic-interchange/OSI
# =============================================================================


class TestTpcdsOsiOfficialFixture:
    """Tests for the official TPC-DS example from the OSI repository.

    This is the canonical example from open-semantic-interchange/OSI. It exercises:
    5 datasets, 4 relationships, 5 metrics, ai_context with synonyms at every level,
    custom_extensions for SALESFORCE and DBT vendors, composite PKs, unique_keys,
    and a version field.
    """

    @pytest.fixture
    def graph(self):
        adapter = OSIAdapter()
        return adapter.parse("tests/fixtures/osi/tpcds_osi_official.yaml")

    def test_parses_without_errors(self, graph):
        """Official OSI fixture parses cleanly."""
        assert isinstance(graph, SemanticGraph)

    def test_dataset_count(self, graph):
        """All 5 datasets are loaded."""
        assert len(graph.models) == 5
        expected = {"store_sales", "customer", "date_dim", "store", "item"}
        assert set(graph.models.keys()) == expected

    def test_metric_count(self, graph):
        """All 5 metrics are loaded."""
        assert len(graph.metrics) == 5
        expected = {
            "total_sales",
            "total_profit",
            "customer_lifetime_value",
            "sales_by_brand",
            "store_productivity",
        }
        assert set(graph.metrics.keys()) == expected

    def test_composite_primary_key(self, graph):
        """store_sales has composite PK [ss_item_sk, ss_ticket_number]."""
        store_sales = graph.models["store_sales"]
        assert store_sales.primary_key == ["ss_item_sk", "ss_ticket_number"]

    def test_unique_keys(self, graph):
        """store_sales has unique_keys matching its composite PK."""
        store_sales = graph.models["store_sales"]
        assert store_sales.unique_keys == [["ss_item_sk", "ss_ticket_number"]]

    def test_single_column_pks(self, graph):
        """Dimension tables have single-column primary keys stored as strings."""
        assert graph.models["customer"].primary_key == "c_customer_sk"
        assert graph.models["date_dim"].primary_key == "d_date_sk"
        assert graph.models["store"].primary_key == "s_store_sk"
        assert graph.models["item"].primary_key == "i_item_sk"

    def test_source_tables(self, graph):
        """Each dataset maps to the correct tpcds.public.* source."""
        assert graph.models["store_sales"].table == "tpcds.public.store_sales"
        assert graph.models["customer"].table == "tpcds.public.customer"
        assert graph.models["date_dim"].table == "tpcds.public.date_dim"
        assert graph.models["store"].table == "tpcds.public.store"
        assert graph.models["item"].table == "tpcds.public.item"

    def test_store_sales_field_count(self, graph):
        """store_sales has 8 fields."""
        assert len(graph.models["store_sales"].dimensions) == 8

    def test_date_dim_field_count(self, graph):
        """date_dim has 5 fields (d_date_sk, d_date, d_year, d_quarter_name, d_month_name)."""
        assert len(graph.models["date_dim"].dimensions) == 5

    def test_customer_field_count(self, graph):
        """customer has 6 fields (includes computed customer_full_name)."""
        assert len(graph.models["customer"].dimensions) == 6

    def test_item_field_count(self, graph):
        """item has 6 fields."""
        assert len(graph.models["item"].dimensions) == 6

    def test_store_field_count(self, graph):
        """store has 6 fields."""
        assert len(graph.models["store"].dimensions) == 6

    def test_relationships_count(self, graph):
        """store_sales has 4 relationships (date_dim, customer, item, store)."""
        store_sales = graph.models["store_sales"]
        assert len(store_sales.relationships) == 4

    def test_relationship_targets(self, graph):
        """Relationships point to the correct dimension tables."""
        store_sales = graph.models["store_sales"]
        targets = {r.name for r in store_sales.relationships}
        assert targets == {"customer", "date_dim", "store", "item"}

    def test_relationship_foreign_keys(self, graph):
        """Each relationship has the correct foreign key."""
        store_sales = graph.models["store_sales"]
        rel_map = {r.name: r for r in store_sales.relationships}
        assert rel_map["customer"].foreign_key == "ss_customer_sk"
        assert rel_map["date_dim"].foreign_key == "ss_sold_date_sk"
        assert rel_map["store"].foreign_key == "ss_store_sk"
        assert rel_map["item"].foreign_key == "ss_item_sk"

    def test_relationship_primary_keys(self, graph):
        """Each relationship references the correct PK on the target."""
        store_sales = graph.models["store_sales"]
        rel_map = {r.name: r for r in store_sales.relationships}
        assert rel_map["customer"].primary_key == "c_customer_sk"
        assert rel_map["date_dim"].primary_key == "d_date_sk"
        assert rel_map["store"].primary_key == "s_store_sk"
        assert rel_map["item"].primary_key == "i_item_sk"

    def test_all_relationships_many_to_one(self, graph):
        """All relationships are many_to_one (fact -> dimension)."""
        store_sales = graph.models["store_sales"]
        for rel in store_sales.relationships:
            assert rel.type == "many_to_one"

    def test_dimension_tables_have_no_relationships(self, graph):
        """Dimension tables have no outbound relationships."""
        for name in ["customer", "date_dim", "store", "item"]:
            assert len(graph.models[name].relationships) == 0

    def test_time_dimension_d_date(self, graph):
        """date_dim.d_date is recognized as time dimension."""
        d_date = graph.models["date_dim"].get_dimension("d_date")
        assert d_date is not None
        assert d_date.type == "time"

    def test_time_dimensions_d_year_quarter_month(self, graph):
        """d_year, d_quarter_name, d_month_name are also time dimensions."""
        date_dim = graph.models["date_dim"]
        for name in ["d_year", "d_quarter_name", "d_month_name"]:
            dim = date_dim.get_dimension(name)
            assert dim is not None
            assert dim.type == "time", f"{name} should be time"

    def test_computed_field_customer_full_name(self, graph):
        """customer_full_name uses ANSI_SQL concatenation."""
        customer = graph.models["customer"]
        full_name = customer.get_dimension("customer_full_name")
        assert full_name is not None
        assert full_name.sql == "c_first_name || ' ' || c_last_name"

    def test_ai_context_on_datasets(self, graph):
        """All datasets have ai_context with synonyms."""
        for name in ["store_sales", "customer", "date_dim", "store", "item"]:
            model = graph.models[name]
            assert model.meta is not None, f"{name} should have meta"
            assert "ai_context" in model.meta, f"{name} should have ai_context"
            assert "synonyms" in model.meta["ai_context"], f"{name} should have synonyms"

    def test_ai_context_store_sales_synonyms(self, graph):
        """store_sales has expected synonym list."""
        ss = graph.models["store_sales"]
        synonyms = ss.meta["ai_context"]["synonyms"]
        assert "sales transactions" in synonyms
        assert "POS data" in synonyms
        assert "retail sales" in synonyms

    def test_ai_context_on_fields(self, graph):
        """Fields with ai_context have their synonyms preserved."""
        ss = graph.models["store_sales"]
        ss_item = ss.get_dimension("ss_item_sk")
        assert ss_item.meta is not None
        assert "product" in ss_item.meta["ai_context"]["synonyms"]

    def test_ai_context_on_metrics(self, graph):
        """Metrics have ai_context with synonyms."""
        total_sales = graph.metrics["total_sales"]
        assert total_sales.meta is not None
        assert "total revenue" in total_sales.meta["ai_context"]["synonyms"]

    def test_metric_total_sales(self, graph):
        """total_sales metric parses correctly."""
        metric = graph.metrics["total_sales"]
        assert metric.description == "Total sales revenue across all transactions"

    def test_metric_customer_lifetime_value(self, graph):
        """customer_lifetime_value (ratio with COUNT DISTINCT) parses."""
        metric = graph.metrics["customer_lifetime_value"]
        assert metric.description is not None
        assert "lifetime" in metric.description.lower()

    def test_metric_store_productivity(self, graph):
        """store_productivity (division with NULLIF) parses."""
        metric = graph.metrics["store_productivity"]
        assert "employee" in metric.description.lower()

    def test_custom_extensions_salesforce_dbt(self, graph):
        """custom_extensions for SALESFORCE and DBT vendors are captured."""
        extensions = graph.metadata["osi"]["semantic_models"][0]["custom_extensions"]
        vendors = {extension["vendor_name"] for extension in extensions}
        assert {"SALESFORCE", "DBT"} <= vendors


# =============================================================================
# MDB-ENGINE MOVIES MODEL (MIT license)
# Source: https://github.com/ranfysvalle02/mdb-engine
# =============================================================================


class TestMdbMoviesFixture:
    """Tests for the mdb-engine movies fixture.

    This fixture has 5 datasets, 3 metrics, and relationships in non-standard format
    (left_dataset/right_dataset/cardinality instead of from/to/from_columns/to_columns).
    The adapter parses datasets and metrics but silently skips the non-standard relationships.
    """

    @pytest.fixture
    def graph(self):
        adapter = OSIAdapter()
        return adapter.parse("tests/fixtures/osi/mdb_movies.yaml")

    def test_parses_without_errors(self, graph):
        """Fixture parses cleanly despite non-standard relationship format."""
        assert isinstance(graph, SemanticGraph)

    def test_dataset_count(self, graph):
        """All 5 datasets are loaded."""
        assert len(graph.models) == 5
        expected = {"actor", "movie", "director", "genre", "user_preference"}
        assert set(graph.models.keys()) == expected

    def test_metric_count(self, graph):
        """All 3 metrics are loaded."""
        assert len(graph.metrics) == 3
        expected = {"movies_watched", "favorite_count", "genre_diversity"}
        assert set(graph.metrics.keys()) == expected

    def test_nonstandard_relationships_skipped(self, graph):
        """Non-standard relationships (left_dataset/right_dataset) are silently skipped."""
        for model in graph.models.values():
            assert len(model.relationships) == 0

    def test_actor_fields(self, graph):
        """actor has 3 fields: actor_name, notable_roles, birth_year."""
        actor = graph.models["actor"]
        assert len(actor.dimensions) == 3
        dim_names = {d.name for d in actor.dimensions}
        assert dim_names == {"actor_name", "notable_roles", "birth_year"}

    def test_actor_birth_year_is_time(self, graph):
        """birth_year is marked as a time dimension."""
        actor = graph.models["actor"]
        birth_year = actor.get_dimension("birth_year")
        assert birth_year.type == "time"

    def test_movie_fields(self, graph):
        """movie has 4 fields: title, year, rating, plot."""
        movie = graph.models["movie"]
        assert len(movie.dimensions) == 4
        dim_names = {d.name for d in movie.dimensions}
        assert dim_names == {"title", "year", "rating", "plot"}

    def test_movie_year_is_time(self, graph):
        """year is marked as a time dimension."""
        movie = graph.models["movie"]
        year_dim = movie.get_dimension("year")
        assert year_dim.type == "time"

    def test_director_fields(self, graph):
        """director has 2 fields: director_name, style."""
        director = graph.models["director"]
        assert len(director.dimensions) == 2

    def test_genre_fields(self, graph):
        """genre has 1 field: genre_name."""
        genre = graph.models["genre"]
        assert len(genre.dimensions) == 1

    def test_user_preference_fields(self, graph):
        """user_preference has 2 fields: preference_type, target_name."""
        up = graph.models["user_preference"]
        assert len(up.dimensions) == 2

    def test_primary_keys(self, graph):
        """Each dataset has the correct single-column primary key."""
        assert graph.models["actor"].primary_key == "actor_id"
        assert graph.models["movie"].primary_key == "movie_id"
        assert graph.models["director"].primary_key == "director_id"
        assert graph.models["genre"].primary_key == "genre_id"
        assert graph.models["user_preference"].primary_key == "preference_id"

    def test_source_tables(self, graph):
        """Datasets map to mdb_engine.ai_chat.* sources."""
        assert graph.models["actor"].table == "mdb_engine.ai_chat.actor"
        assert graph.models["movie"].table == "mdb_engine.ai_chat.movie"
        assert graph.models["director"].table == "mdb_engine.ai_chat.director"
        assert graph.models["genre"].table == "mdb_engine.ai_chat.genre"
        assert graph.models["user_preference"].table == "mdb_engine.ai_chat.user_preference"

    def test_ai_context_on_all_datasets(self, graph):
        """All datasets have ai_context with synonyms."""
        for name in ["actor", "movie", "director", "genre", "user_preference"]:
            model = graph.models[name]
            assert model.meta is not None, f"{name} should have meta"
            assert "ai_context" in model.meta
            assert "synonyms" in model.meta["ai_context"]

    def test_actor_synonyms_extensive(self, graph):
        """actor has extensive synonym list (19 entries)."""
        actor = graph.models["actor"]
        synonyms = actor.meta["ai_context"]["synonyms"]
        assert len(synonyms) >= 15
        assert "actor" in synonyms
        assert "A-lister" in synonyms

    def test_genre_synonyms_include_genre_names(self, graph):
        """genre synonyms include actual genre names (drama, comedy, etc.)."""
        genre = graph.models["genre"]
        synonyms = genre.meta["ai_context"]["synonyms"]
        assert "drama" in synonyms
        assert "comedy" in synonyms
        assert "sci-fi" in synonyms

    def test_field_level_ai_context(self, graph):
        """Fields have ai_context synonyms preserved."""
        actor = graph.models["actor"]
        actor_name = actor.get_dimension("actor_name")
        assert actor_name.meta is not None
        assert "full name" in actor_name.meta["ai_context"]["synonyms"]

    def test_metric_movies_watched(self, graph):
        """movies_watched metric parses (COUNT DISTINCT)."""
        metric = graph.metrics["movies_watched"]
        assert metric.description is not None

    def test_metric_favorite_count(self, graph):
        """favorite_count metric with FILTER clause parses."""
        metric = graph.metrics["favorite_count"]
        assert metric.description is not None

    def test_metric_genre_diversity(self, graph):
        """genre_diversity metric parses (COUNT DISTINCT)."""
        metric = graph.metrics["genre_diversity"]
        assert metric.description is not None

    def test_metric_ai_context(self, graph):
        """Metrics have ai_context with synonyms."""
        for name in ["movies_watched", "favorite_count", "genre_diversity"]:
            metric = graph.metrics[name]
            assert metric.meta is not None, f"{name} should have meta"
            assert "ai_context" in metric.meta


# =============================================================================
# MDB-ENGINE MEMBER KNOWLEDGE MODEL (MIT license)
# Source: https://github.com/ranfysvalle02/mdb-engine
# =============================================================================


class TestMdbMemberKnowledgeFixture:
    """Tests for the mdb-engine member_knowledge auto-scaffolded fixture.

    This fixture has 8 datasets (actor, movie, director, genre, person, concept,
    event, user_preference), no metrics (empty list), and non-standard relationships
    nested inside the semantic_model entry. Each dataset has a single field
    (except user_preference with 2).
    """

    @pytest.fixture
    def graph(self):
        adapter = OSIAdapter()
        return adapter.parse("tests/fixtures/osi/mdb_member_knowledge.yaml")

    def test_parses_without_errors(self, graph):
        """Fixture parses cleanly despite non-standard relationship format."""
        assert isinstance(graph, SemanticGraph)

    def test_dataset_count(self, graph):
        """All 8 datasets are loaded."""
        assert len(graph.models) == 8
        expected = {
            "actor",
            "movie",
            "director",
            "genre",
            "person",
            "concept",
            "event",
            "user_preference",
        }
        assert set(graph.models.keys()) == expected

    def test_no_metrics(self, graph):
        """Empty metrics list results in no graph-level metrics."""
        assert len(graph.metrics) == 0

    def test_nonstandard_relationships_skipped(self, graph):
        """Non-standard relationships are silently skipped."""
        for model in graph.models.values():
            assert len(model.relationships) == 0

    def test_single_field_datasets(self, graph):
        """Most datasets have exactly 1 field (the *_name field)."""
        single_field_datasets = ["actor", "movie", "director", "genre", "person", "concept", "event"]
        for name in single_field_datasets:
            model = graph.models[name]
            assert len(model.dimensions) == 1, f"{name} should have 1 field, got {len(model.dimensions)}"

    def test_user_preference_has_two_fields(self, graph):
        """user_preference has 2 fields: preference_type, target_name."""
        up = graph.models["user_preference"]
        assert len(up.dimensions) == 2
        dim_names = {d.name for d in up.dimensions}
        assert dim_names == {"preference_type", "target_name"}

    def test_primary_keys(self, graph):
        """Each dataset has the expected primary key."""
        expected_pks = {
            "actor": "actor_id",
            "movie": "movie_id",
            "director": "director_id",
            "genre": "genre_id",
            "person": "person_id",
            "concept": "concept_id",
            "event": "event_id",
            "user_preference": "preference_id",
        }
        for name, expected_pk in expected_pks.items():
            assert graph.models[name].primary_key == expected_pk, f"{name} PK mismatch"

    def test_source_tables(self, graph):
        """Datasets map to mdb_engine.ai_chat.* sources."""
        for name in ["actor", "movie", "director", "genre", "person", "concept", "event"]:
            assert graph.models[name].table == f"mdb_engine.ai_chat.{name}"
        assert graph.models["user_preference"].table == "mdb_engine.ai_chat.user_preference"

    def test_ai_context_on_all_datasets(self, graph):
        """All 8 datasets have ai_context with synonyms."""
        for name in graph.models:
            model = graph.models[name]
            assert model.meta is not None, f"{name} should have meta"
            assert "ai_context" in model.meta
            assert "synonyms" in model.meta["ai_context"]

    def test_field_ai_context(self, graph):
        """Fields have ai_context with synonyms."""
        actor = graph.models["actor"]
        actor_name = actor.get_dimension("actor_name")
        assert actor_name.meta is not None
        assert "name" in actor_name.meta["ai_context"]["synonyms"]

    def test_concept_dataset(self, graph):
        """concept dataset exists with expected structure (unique to this fixture)."""
        concept = graph.models["concept"]
        assert concept.table == "mdb_engine.ai_chat.concept"
        assert concept.primary_key == "concept_id"
        dim = concept.get_dimension("concept_name")
        assert dim is not None
        assert dim.sql == "name"

    def test_person_dataset(self, graph):
        """person dataset exists with expected structure (unique to this fixture)."""
        person = graph.models["person"]
        assert person.table == "mdb_engine.ai_chat.person"
        assert person.primary_key == "person_id"
        dim = person.get_dimension("person_name")
        assert dim is not None
        assert dim.sql == "name"

    def test_event_dataset(self, graph):
        """event dataset exists with expected structure (unique to this fixture)."""
        event = graph.models["event"]
        assert event.table == "mdb_engine.ai_chat.event"
        assert event.primary_key == "event_id"
