"""Tests for MCP server functionality."""

# ruff: noqa: E402

import json
import tempfile
from datetime import date, datetime, time
from decimal import Decimal
from pathlib import Path

import pytest

from tests.optional_dep_stubs import ensure_fake_mcp

ensure_fake_mcp()

from sidemantic import Metric, Model
from sidemantic.core.relationship import Relationship
from sidemantic.mcp_server import (
    _convert_to_json_compatible,
    _format_join_condition,
    catalog_resource,
    create_chart,
    get_models,
    get_semantic_graph,
    initialize_layer,
    run_query,
    run_sql,
    validate_query,
)


@pytest.fixture
def stub_chart_rendering(monkeypatch):
    import sidemantic.mcp_server as mcp_mod

    def fake_make_chart(data, chart_type="auto", title=None, width=600, height=400):
        return {
            "mark": "bar" if chart_type == "auto" else chart_type,
            "title": title,
            "width": width,
            "height": height,
            "data": {"values": data},
        }

    monkeypatch.setattr("sidemantic.charts.create_chart", fake_make_chart)
    monkeypatch.setattr("sidemantic.charts.chart_to_vega", lambda chart: chart)
    monkeypatch.setattr("sidemantic.charts.chart_to_base64_png", lambda _chart: "data:image/png;base64,ZmFrZQ==")
    monkeypatch.setattr(mcp_mod, "_apps_enabled", False)


@pytest.fixture
def demo_layer():
    """Create a demo semantic layer for testing."""
    # Create a temporary directory for testing
    tmpdir = tempfile.mkdtemp()
    tmpdir_path = Path(tmpdir)

    # Create a model file with segments and a second model
    model_yaml = """
models:
  - name: orders
    table: orders_table
    description: "All customer orders"
    dimensions:
      - name: order_id
        sql: order_id
        type: categorical
      - name: customer_name
        sql: customer_name
        type: categorical
      - name: order_date
        sql: order_date
        type: time
        granularity: day
      - name: status
        sql: status
        type: categorical
    metrics:
      - name: total_revenue
        agg: sum
        sql: amount
        description: "Total revenue from orders"
      - name: order_count
        agg: count_distinct
        sql: order_id
    segments:
      - name: completed_orders
        sql: "{model}.status = 'completed'"
        description: "Only completed orders"
      - name: high_value
        sql: "{model}.amount > 100"
        description: "Orders over 100"
"""
    model_file = tmpdir_path / "orders.yml"
    model_file.write_text(model_yaml)

    # Initialize the layer
    layer = initialize_layer(str(tmpdir_path), db_path=":memory:")

    # Create the table with some test data using adapter interface
    layer.adapter.execute("""
        CREATE TABLE orders_table (
            id INTEGER,
            order_id VARCHAR,
            customer_name VARCHAR,
            order_date DATE,
            amount DECIMAL,
            status VARCHAR
        )
    """)
    layer.adapter.execute("""
        INSERT INTO orders_table VALUES
            (1, '1', 'Alice', '2024-01-01', 100, 'completed'),
            (2, '2', 'Bob', '2024-01-02', 200, 'completed'),
            (3, '3', 'Alice', '2024-01-03', 150, 'pending')
    """)

    yield layer

    # Cleanup
    import shutil

    shutil.rmtree(tmpdir)


def test_discover_models_via_semantic_graph(demo_layer):
    """Test discovering models via get_semantic_graph (replaces list_models)."""
    graph = get_semantic_graph()
    models = graph["models"]

    assert len(models) == 1
    assert models[0]["name"] == "orders"
    assert models[0]["table"] == "orders_table"
    assert len(models[0]["dimensions"]) == 4
    assert len(models[0]["metrics"]) == 2
    assert "order_id" in models[0]["dimensions"]
    assert "customer_name" in models[0]["dimensions"]
    assert "order_date" in models[0]["dimensions"]
    assert "status" in models[0]["dimensions"]
    assert "total_revenue" in models[0]["metrics"]
    assert "order_count" in models[0]["metrics"]


def test_get_models(demo_layer):
    """Test getting detailed model information."""
    result = get_models(["orders"])
    models = result["models"]

    assert len(models) == 1
    model = models[0]
    assert model["name"] == "orders"
    assert model["table"] == "orders_table"

    # Check dimensions
    assert len(model["dimensions"]) == 4
    dim_names = [d["name"] for d in model["dimensions"]]
    assert "order_id" in dim_names
    assert "customer_name" in dim_names
    assert "order_date" in dim_names
    assert "status" in dim_names

    # Check metrics
    assert len(model["metrics"]) == 2
    metric_names = [m["name"] for m in model["metrics"]]
    assert "total_revenue" in metric_names
    assert "order_count" in metric_names

    # Check metric details
    revenue_metric = next(m for m in model["metrics"] if m["name"] == "total_revenue")
    assert revenue_metric["agg"] == "sum"
    assert revenue_metric["sql"] == "amount"


def test_get_models_nonexistent(demo_layer):
    """Test getting a model that doesn't exist."""
    result = get_models(["nonexistent"])
    assert len(result["models"]) == 0


def test_get_models_multiple(demo_layer):
    """Test getting multiple models (only one exists)."""
    result = get_models(["orders", "nonexistent"])
    assert len(result["models"]) == 1
    assert result["models"][0]["name"] == "orders"


def test_run_query_basic(demo_layer):
    """Test running a basic query."""
    result = run_query(
        dimensions=["orders.customer_name"],
        metrics=["orders.total_revenue"],
    )

    assert result["sql"] is not None
    assert "SELECT" in result["sql"].upper()
    assert "customer_name" in result["sql"]
    assert "SUM" in result["sql"].upper()
    # Should have 2 rows (Alice and Bob)
    assert result["row_count"] == 2
    assert len(result["rows"]) == 2


def test_run_query_with_filter(demo_layer):
    """Test running a query with a WHERE clause."""
    result = run_query(
        dimensions=["orders.customer_name"],
        metrics=["orders.total_revenue"],
        where="orders.customer_name = 'Alice'",
    )

    assert result["sql"] is not None
    assert "WHERE" in result["sql"].upper()
    assert "Alice" in result["sql"]


def test_run_query_with_order_by(demo_layer):
    """Test running a query with ORDER BY."""
    result = run_query(
        dimensions=["orders.customer_name"],
        metrics=["orders.total_revenue"],
        order_by=["orders.total_revenue desc"],
    )

    assert result["sql"] is not None
    assert "ORDER BY" in result["sql"].upper()


def test_run_query_with_limit(demo_layer):
    """Test running a query with LIMIT."""
    result = run_query(
        dimensions=["orders.customer_name"],
        metrics=["orders.total_revenue"],
        limit=10,
    )

    assert result["sql"] is not None
    assert "LIMIT" in result["sql"].upper()
    assert "10" in result["sql"]


def test_run_query_dimensions_only(demo_layer):
    """Test running a query with only dimensions."""
    result = run_query(
        dimensions=["orders.customer_name", "orders.order_date"],
    )

    assert result["sql"] is not None
    assert "customer_name" in result["sql"]
    assert "order_date" in result["sql"]


def test_run_query_metrics_only(demo_layer):
    """Test running a query with only metrics."""
    result = run_query(
        metrics=["orders.total_revenue", "orders.order_count"],
    )

    assert result["sql"] is not None
    assert "SUM" in result["sql"].upper()
    assert "COUNT" in result["sql"].upper()


def test_run_query_decimal_conversion(demo_layer):
    """Test that Decimal values are converted to float for JSON serialization."""
    result = run_query(
        dimensions=["orders.customer_name"],
        metrics=["orders.total_revenue"],
    )

    # Verify we have results
    assert result["row_count"] > 0
    assert len(result["rows"]) > 0

    # Check that no Decimal objects are in the results
    for row in result["rows"]:
        for key, value in row.items():
            assert not isinstance(value, Decimal), f"Found Decimal value for {key}: {value}"
            # Revenue should be a float or int
            if "revenue" in key.lower():
                assert isinstance(value, (int, float)), f"Expected numeric type for {key}, got {type(value)}"

    # Verify the entire result is JSON serializable
    try:
        json.dumps(result)
    except TypeError as e:
        pytest.fail(f"Result is not JSON serializable: {e}")


def test_create_chart_basic(demo_layer, stub_chart_rendering):
    """Test creating a basic chart."""
    result = create_chart(
        dimensions=["orders.customer_name"],
        metrics=["orders.total_revenue"],
        chart_type="bar",
        title="Revenue by Customer",
    )

    # Check all expected keys are present
    assert "sql" in result
    assert "vega_spec" in result
    assert "png_base64" in result
    assert "row_count" in result

    # Verify SQL was generated
    assert result["sql"] is not None
    assert "SELECT" in result["sql"].upper()

    # Verify vega spec is a dict
    assert isinstance(result["vega_spec"], dict)
    assert "data" in result["vega_spec"]

    # Verify PNG is base64 encoded
    assert result["png_base64"].startswith("data:image/png;base64,")

    # Verify row count
    assert result["row_count"] == 2  # Alice and Bob


def test_create_chart_decimal_conversion(demo_layer, stub_chart_rendering):
    """Test that create_chart converts Decimals to floats for JSON serialization."""
    result = create_chart(
        dimensions=["orders.customer_name"],
        metrics=["orders.total_revenue"],
        chart_type="bar",
    )

    # The vega_spec contains the data embedded, so it must be JSON serializable
    vega_spec = result["vega_spec"]

    # Check that the embedded data has no Decimal values
    if "data" in vega_spec and "values" in vega_spec["data"]:
        for row in vega_spec["data"]["values"]:
            for key, value in row.items():
                assert not isinstance(value, Decimal), f"Found Decimal in vega_spec data for {key}: {value}"

    # Verify the entire result is JSON serializable
    try:
        json.dumps(result)
    except TypeError as e:
        pytest.fail(f"create_chart result is not JSON serializable: {e}")


def test_create_chart_with_filter(demo_layer, stub_chart_rendering):
    """Test creating a chart with a filter."""
    result = create_chart(
        dimensions=["orders.customer_name"],
        metrics=["orders.total_revenue"],
        where="orders.customer_name = 'Alice'",
        chart_type="bar",
    )

    assert result["sql"] is not None
    assert "WHERE" in result["sql"].upper()
    assert "Alice" in result["sql"]


def test_create_chart_time_series(demo_layer, stub_chart_rendering):
    """Test creating a time series chart."""
    result = create_chart(
        dimensions=["orders.order_date"],
        metrics=["orders.total_revenue"],
        chart_type="line",
        title="Revenue Over Time",
    )

    assert result["sql"] is not None
    assert result["row_count"] > 0

    # Verify it's JSON serializable
    try:
        json.dumps(result)
    except TypeError as e:
        pytest.fail(f"Time series chart result is not JSON serializable: {e}")


# --- Tests for new tools ---


def test_run_query_dry_run(demo_layer):
    """Test run_query with dry_run returns SQL without executing."""
    result = run_query(
        dimensions=["orders.customer_name"],
        metrics=["orders.total_revenue"],
        limit=5,
        dry_run=True,
    )

    assert "sql" in result
    assert "SELECT" in result["sql"].upper()
    assert "SUM" in result["sql"].upper()
    assert "LIMIT" in result["sql"].upper()
    # Should NOT have rows or row_count
    assert "rows" not in result
    assert "row_count" not in result


def test_run_query_dry_run_with_offset(demo_layer):
    """Test run_query dry_run with offset for pagination."""
    result = run_query(
        dimensions=["orders.customer_name"],
        metrics=["orders.total_revenue"],
        limit=10,
        offset=20,
        dry_run=True,
    )

    assert "sql" in result
    assert "OFFSET" in result["sql"].upper()


def test_run_sql_simple(demo_layer):
    """Test run_sql with a simple semantic query."""
    result = run_sql("SELECT total_revenue, customer_name FROM orders")

    assert "sql" in result
    assert "original_sql" in result
    assert "rows" in result
    assert "row_count" in result
    assert result["row_count"] == 2  # Alice and Bob
    assert result["original_sql"] == "SELECT total_revenue, customer_name FROM orders"


def test_run_sql_with_filter(demo_layer):
    """Test run_sql with WHERE clause."""
    result = run_sql("SELECT total_revenue, customer_name FROM orders WHERE customer_name = 'Alice'")

    assert result["row_count"] == 1
    assert "WHERE" in result["sql"].upper()


def test_run_sql_with_time_granularity(demo_layer):
    """Test run_sql with time granularity suffix."""
    result = run_sql("SELECT order_date__month, total_revenue FROM orders")

    assert result["row_count"] > 0
    assert "DATE_TRUNC" in result["sql"].upper()


def test_validate_query_valid(demo_layer):
    """Test validate_query with valid references."""
    result = validate_query(
        dimensions=["orders.customer_name"],
        metrics=["orders.total_revenue"],
    )

    assert result["valid"] is True
    assert result["errors"] == []


def test_validate_query_invalid_model(demo_layer):
    """Test validate_query with nonexistent model."""
    result = validate_query(
        dimensions=["nonexistent.foo"],
        metrics=["orders.total_revenue"],
    )

    assert result["valid"] is False
    assert len(result["errors"]) > 0
    assert any("nonexistent" in e for e in result["errors"])


def test_validate_query_invalid_dimension(demo_layer):
    """Test validate_query with nonexistent dimension."""
    result = validate_query(
        dimensions=["orders.nonexistent_dim"],
        metrics=["orders.total_revenue"],
    )

    assert result["valid"] is False
    assert len(result["errors"]) > 0


def test_validate_query_invalid_metric(demo_layer):
    """Test validate_query with nonexistent metric."""
    result = validate_query(
        dimensions=["orders.customer_name"],
        metrics=["orders.nonexistent_metric"],
    )

    assert result["valid"] is False
    assert len(result["errors"]) > 0


def test_segments_via_get_models(demo_layer):
    """Test that segments are accessible via get_models (replaces list_segments)."""
    result = get_models(["orders"])
    model = result["models"][0]

    assert "segments" in model
    assert len(model["segments"]) == 2

    seg_names = [s["name"] for s in model["segments"]]
    assert "completed_orders" in seg_names
    assert "high_value" in seg_names

    completed = next(s for s in model["segments"] if s["name"] == "completed_orders")
    assert "sql" in completed
    assert "description" in completed


def test_get_semantic_graph(demo_layer):
    """Test get_semantic_graph returns full graph overview."""
    result = get_semantic_graph()

    assert "models" in result
    assert "joinable_pairs" in result
    assert len(result["models"]) == 1

    model = result["models"][0]
    assert model["name"] == "orders"
    assert "dimensions" in model
    assert "metrics" in model
    assert "segments" in model
    assert "completed_orders" in model["segments"]
    assert model["primary_key"] == "id"


def test_get_models_enriched(demo_layer):
    """Test that get_models returns enriched model data."""
    result = get_models(["orders"])
    model = result["models"][0]

    # Check new fields
    assert model["primary_key"] == "id"
    assert model["description"] == "All customer orders"

    # Check segments are included
    assert "segments" in model
    assert len(model["segments"]) == 2

    # Check metric description is included
    revenue = next(m for m in model["metrics"] if m["name"] == "total_revenue")
    assert revenue["description"] == "Total revenue from orders"


def test_run_query_with_offset(demo_layer):
    """Test run_query with offset for pagination."""
    result = run_query(
        dimensions=["orders.customer_name"],
        metrics=["orders.total_revenue"],
        order_by=["orders.customer_name"],
        limit=1,
        offset=1,
    )

    assert result["row_count"] == 1
    assert "OFFSET" in result["sql"].upper()


def test_run_query_ungrouped(demo_layer):
    """Test run_query with ungrouped=True returns raw rows."""
    result = run_query(
        dimensions=["orders.customer_name", "orders.order_date"],
        ungrouped=True,
    )

    # Should return 3 raw rows (no aggregation)
    assert result["row_count"] == 3


def test_run_query_temporal_json_serialization(demo_layer):
    """Test that date/datetime values in query results are converted to ISO strings."""
    result = run_query(
        dimensions=["orders.order_date"],
        metrics=["orders.total_revenue"],
    )

    assert result["row_count"] > 0
    for row in result["rows"]:
        for key, value in row.items():
            assert not isinstance(value, (Decimal,)), f"Found Decimal value for {key}"

    # The entire result must be JSON serializable
    try:
        json.dumps(result)
    except TypeError as e:
        pytest.fail(f"Result with date values is not JSON serializable: {e}")

    # Date values should be ISO format strings
    for row in result["rows"]:
        if "order_date" in row:
            assert isinstance(row["order_date"], str), f"Expected ISO string, got {type(row['order_date'])}"


def test_run_sql_temporal_json_serialization(demo_layer):
    """Test that run_sql converts temporal values to ISO strings."""
    result = run_sql("SELECT order_date__month, total_revenue FROM orders")

    assert result["row_count"] > 0
    try:
        json.dumps(result)
    except TypeError as e:
        pytest.fail(f"run_sql result with temporal values is not JSON serializable: {e}")


def test_catalog_resource(demo_layer):
    """Test catalog_resource returns valid JSON catalog metadata."""
    result = catalog_resource()

    catalog = json.loads(result)
    assert "tables" in catalog
    assert "columns" in catalog
    assert "constraints" in catalog
    assert "key_column_usage" in catalog

    # Check that orders table is present
    table_names = [t["table_name"] for t in catalog["tables"]]
    assert "orders" in table_names

    # Check that columns include dimensions and metrics
    order_cols = [c for c in catalog["columns"] if c["table_name"] == "orders"]
    col_names = [c["column_name"] for c in order_cols]
    assert "customer_name" in col_names
    assert "total_revenue" in col_names


def test_convert_to_json_compatible_handles_common_scalar_types():
    assert _convert_to_json_compatible(Decimal("12.5")) == 12.5
    assert _convert_to_json_compatible(date(2024, 1, 2)) == "2024-01-02"
    assert _convert_to_json_compatible(time(12, 30, 45)) == "12:30:45"
    assert _convert_to_json_compatible(datetime(2024, 1, 2, 3, 4, 5)) == "2024-01-02T03:04:05"
    assert _convert_to_json_compatible("plain") == "plain"


def test_initialize_layer_builds_connection_from_db_path(tmp_path):
    model_file = tmp_path / "orders.yml"
    model_file.write_text(
        """
models:
  - name: orders
    table: orders_table
    primary_key: id
    dimensions:
      - name: status
        sql: status
        type: categorical
"""
    )

    layer = initialize_layer(str(tmp_path), db_path=":memory:")

    assert layer.connection_string == "duckdb:///:memory:"
    assert "orders" in layer.graph.models


def test_format_join_condition_handles_all_relationship_shapes():
    models = {
        "orders": Model(name="orders", table="orders", primary_key="id"),
        "customers": Model(name="customers", table="customers", primary_key="id"),
        "line_items": Model(name="line_items", table="line_items", primary_key="id"),
        "products": Model(name="products", table="products", primary_key="id"),
        "order_products": Model(name="order_products", table="order_products", primary_key="id"),
    }

    assert (
        _format_join_condition(
            "orders",
            Relationship(name="customers", type="many_to_one", foreign_key="customer_id"),
            models,
        )
        == "orders.customer_id = customers.id"
    )
    assert (
        _format_join_condition(
            "orders",
            Relationship(name="line_items", type="one_to_many", foreign_key="order_id"),
            models,
        )
        == "line_items.order_id = orders.id"
    )
    assert (
        _format_join_condition(
            "orders",
            Relationship(
                name="line_items",
                type="one_to_many",
                foreign_key="account_id",
                primary_key="account_id",
            ),
            models,
        )
        == "line_items.account_id = orders.account_id"
    )
    assert (
        _format_join_condition(
            "orders",
            Relationship(
                name="products",
                type="many_to_many",
                through="order_products",
                through_foreign_key="order_id",
                related_foreign_key="product_id",
            ),
            models,
        )
        == "orders.id = order_products.order_id AND order_products.product_id = products.id"
    )
    assert _format_join_condition("orders", Relationship(name="missing", type="many_to_one"), models) is None


def test_format_join_condition_handles_composite_one_to_many_keys():
    models = {
        "orders": Model(name="orders", table="orders", primary_key=["tenant_id", "order_id"]),
        "line_items": Model(name="line_items", table="line_items", primary_key="id"),
    }

    assert (
        _format_join_condition(
            "orders",
            Relationship(
                name="line_items",
                type="one_to_many",
                foreign_key=["tenant_id", "order_id"],
                primary_key=["tenant_id", "order_id"],
            ),
            models,
        )
        == "line_items.tenant_id = orders.tenant_id AND line_items.order_id = orders.order_id"
    )


def test_get_models_includes_join_conditions_and_source_metadata():
    tmpdir = Path(tempfile.mkdtemp())
    try:
        (tmpdir / "models.yml").write_text(
            """
models:
  - name: customers
    table: customers
    primary_key: id
    dimensions:
      - name: name
        sql: name
        type: categorical
  - name: orders
    table: orders
    primary_key: id
    relationships:
      - name: customers
        type: many_to_one
        foreign_key: customer_id
    dimensions:
      - name: customer_id
        sql: customer_id
        type: categorical
"""
        )

        layer = initialize_layer(str(tmpdir), db_path=":memory:")
        layer.graph.models["orders"]._source_format = "yaml"
        layer.graph.models["orders"]._source_file = str(tmpdir / "models.yml")

        result = get_models(["orders"])
        model = result["models"][0]
        relationship = model["relationships"][0]

        assert relationship["join_condition"] == "orders.customer_id = customers.id"
        assert model["source_format"] == "yaml"
        assert model["source_file"].endswith("models.yml")
    finally:
        import shutil

        shutil.rmtree(tmpdir)


def test_get_semantic_graph_includes_joinable_pairs_and_graph_metrics():
    tmpdir = Path(tempfile.mkdtemp())
    try:
        (tmpdir / "models.yml").write_text(
            """
models:
  - name: customers
    table: customers
    primary_key: id
    dimensions:
      - name: name
        sql: name
        type: categorical
  - name: orders
    table: orders
    primary_key: id
    relationships:
      - name: customers
        type: many_to_one
        foreign_key: customer_id
    dimensions:
      - name: customer_id
        sql: customer_id
        type: categorical
    metrics:
      - name: order_count
        agg: count
"""
        )
        layer = initialize_layer(str(tmpdir), db_path=":memory:")
        layer.add_metric(Metric(name="global_ratio", type="derived", sql="1"))

        result = get_semantic_graph()

        assert any(pair["from"] == "customers" and pair["to"] == "orders" for pair in result["joinable_pairs"])
        assert any(metric["name"] == "global_ratio" for metric in result["graph_metrics"])
    finally:
        import shutil

        shutil.rmtree(tmpdir)


def test_create_chart_returns_ui_resource_when_apps_enabled(monkeypatch, demo_layer, stub_chart_rendering):
    import sidemantic.mcp_server as mcp_mod

    def fake_resource(spec):
        return {"resource": spec["mark"]}

    monkeypatch.setattr("sidemantic.apps.create_chart_resource", fake_resource)

    old_value = mcp_mod._apps_enabled
    mcp_mod._apps_enabled = True
    try:
        result = create_chart(
            dimensions=["orders.customer_name"],
            metrics=["orders.total_revenue"],
            chart_type="bar",
        )
    finally:
        mcp_mod._apps_enabled = old_value

    assert isinstance(result, list)
    assert result[0]["row_count"] == 2
    assert result[1] == {"resource": "bar"}
