Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

31.6 KB
Raw Blame
package mcp

import (
	"context"
	"encoding/json"
	"fmt"
	"net/http"
	"strings"
	"sync"
	"sync/atomic"
	"testing"

	mcpsdk "github.com/modelcontextprotocol/go-sdk/mcp"
	"github.com/stretchr/testify/require"
)

// --- Fixture builders (mirror the JSON wire format of DatabaseMetadata proto) ---

func makeMetadataResponse(schemas ...map[string]any) map[string]any {
	return map[string]any{
		"name":    "instances/prod-pg/databases/employee_db/metadata",
		"schemas": schemas,
	}
}

func makeSchema(name string, tables ...map[string]any) map[string]any {
	return map[string]any{
		"name":   name,
		"tables": tables,
	}
}

func makeSchemaWithViews(name string, views []string, tables ...map[string]any) map[string]any {
	viewList := make([]map[string]any, 0, len(views))
	for _, v := range views {
		viewList = append(viewList, map[string]any{"name": v})
	}
	return map[string]any{
		"name":   name,
		"tables": tables,
		"views":  viewList,
	}
}

func makeTable(name string, rowCount int64, columns []map[string]any, indexes []map[string]any) map[string]any {
	t := map[string]any{
		"name":     name,
		"rowCount": fmt.Sprintf("%d", rowCount),
		"columns":  columns,
	}
	if len(indexes) > 0 {
		t["indexes"] = indexes
	}
	return t
}

func makeColumn(name, typ string, nullable bool) map[string]any {
	return map[string]any{
		"name":     name,
		"type":     typ,
		"nullable": nullable,
	}
}

func makeColumnWithDefault(name, typ, defaultVal, comment string) map[string]any {
	c := makeColumn(name, typ, false)
	c["default"] = defaultVal
	c["comment"] = comment
	return c
}

func makePrimaryKeyIndex(name string, columns []string) map[string]any {
	return map[string]any{
		"name":        name,
		"expressions": columns,
		"type":        "btree",
		"unique":      true,
		"primary":     true,
	}
}

func makeIndex(name string, unique bool, columns []string) map[string]any {
	return map[string]any{
		"name":        name,
		"expressions": columns,
		"type":        "btree",
		"unique":      unique,
		"primary":     false,
	}
}

// --- Request capture / metadata mock ---

// capturedRequest stores a single GetDatabaseMetadata request body for assertions.
type capturedRequest struct {
	Name   string `json:"name"`
	Filter string `json:"filter"`
	Limit  int32  `json:"limit"`
}

// metadataMock is a stateful mock that records GetDatabaseMetadata calls and
// returns a scripted response (or a response per-call for the candidate refetch case).
type metadataMock struct {
	databases     []map[string]any
	responses     []map[string]any // scripted responses; indexed by call count
	calls         int32
	capturedMu    sync.Mutex
	capturedCalls []capturedRequest
}

func newMetadataMock(databases []map[string]any, responses ...map[string]any) *metadataMock {
	return &metadataMock{databases: databases, responses: responses}
}

func (m *metadataMock) handler() http.Handler {
	listHandler := mockListDatabases(m.databases)
	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
		if strings.Contains(r.URL.Path, "DatabaseService/GetDatabaseMetadata") {
			var captured capturedRequest
			_ = json.NewDecoder(r.Body).Decode(&captured)

			m.capturedMu.Lock()
			m.capturedCalls = append(m.capturedCalls, captured)
			m.capturedMu.Unlock()

			idx := int(atomic.AddInt32(&m.calls, 1)) - 1
			var resp map[string]any
			if idx < len(m.responses) {
				resp = m.responses[idx]
			} else if len(m.responses) > 0 {
				// Reuse last response for additional calls.
				resp = m.responses[len(m.responses)-1]
			}
			if resp == nil {
				resp = map[string]any{"name": "", "schemas": []any{}}
			}

			w.Header().Set("Content-Type", "application/json")
			w.WriteHeader(http.StatusOK)
			_ = json.NewEncoder(w).Encode(resp)
			return
		}
		listHandler.ServeHTTP(w, r)
	})
}

func (m *metadataMock) lastCall() capturedRequest {
	m.capturedMu.Lock()
	defer m.capturedMu.Unlock()
	if len(m.capturedCalls) == 0 {
		return capturedRequest{}
	}
	return m.capturedCalls[len(m.capturedCalls)-1]
}

func (m *metadataMock) callAt(idx int) capturedRequest {
	m.capturedMu.Lock()
	defer m.capturedMu.Unlock()
	if idx >= len(m.capturedCalls) {
		return capturedRequest{}
	}
	return m.capturedCalls[idx]
}

func (m *metadataMock) callCount() int {
	return int(atomic.LoadInt32(&m.calls))
}

// mockMetadataServer returns a ready-to-use mock server that handles both
// ListDatabases and GetDatabaseMetadata calls.
func mockMetadataServer(t *testing.T, databases []map[string]any, responses ...map[string]any) (*Server, *metadataMock) {
	t.Helper()
	mock := newMetadataMock(databases, responses...)
	return newTestServerWithMock(t, mock.handler()), mock
}

// employeeDB returns a standard single-database fixture (POSTGRES, employee_db).
func employeeDB() []map[string]any {
	return []map[string]any{
		makeDatabase("instances/prod-pg/databases/employee_db", "instances/prod-pg", "projects/hr-system", "POSTGRES", "ds-admin-1"),
	}
}

// --- Tests ---

func TestGetSchema_Summary(t *testing.T) {
	usersTable := makeTable("users", 10432,
		[]map[string]any{
			makeColumn("id", "int4", false),
			makeColumn("email", "varchar(255)", false),
		},
		[]map[string]any{makePrimaryKeyIndex("users_pkey", []string{"id"})},
	)
	ordersTable := makeTable("orders", 182394,
		[]map[string]any{
			makeColumn("id", "int8", false),
			makeColumn("user_id", "int4", false),
		},
		[]map[string]any{makePrimaryKeyIndex("orders_pkey", []string{"id"})},
	)
	// Intentionally out of order to verify sorting.
	resp := makeMetadataResponse(makeSchemaWithViews("public", []string{"active_users", "recent_orders"}, ordersTable, usersTable))

	s, _ := mockMetadataServer(t, employeeDB(), resp)

	result, structured, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
	})
	require.NoError(t, err)
	require.False(t, result.IsError)

	output, ok := structured.(*SchemaOutput)
	require.True(t, ok)
	require.Equal(t, "POSTGRES", output.Engine)
	require.Len(t, output.Schemas, 1)
	section := output.Schemas[0]
	require.Equal(t, "public", section.Name)
	require.Len(t, section.Tables, 2)
	// Tables sorted alphabetically.
	require.Equal(t, "orders", section.Tables[0].Name)
	require.Equal(t, "users", section.Tables[1].Name)
	// Summary mode has no column details but includes column count.
	require.Equal(t, 2, section.Tables[0].ColumnCount)
	require.Equal(t, int64(182394), section.Tables[0].RowCountEstimate)
	// Claude Code reads the structured result alone, so the key says it is an estimate.
	serialized, err := json.Marshal(structured)
	require.NoError(t, err)
	require.Contains(t, string(serialized), `"rowCountEstimate":182394`)
	require.NotContains(t, string(serialized), `"rowCount"`)
	require.Empty(t, section.Tables[0].Columns)
	// Views sorted alphabetically.
	require.Equal(t, []string{"active_users", "recent_orders"}, section.Views)

	// Text header mentions schema and tables.
	text := result.Content[0].(*mcpsdk.TextContent).Text
	require.Contains(t, text, "Database: instances/prod-pg/databases/employee_db (POSTGRES)")
	require.Contains(t, text, "Schemas: 1 (public)")
	require.Contains(t, text, "Tables: 2")
}

func TestGetSchema_IncludeColumns(t *testing.T) {
	usersTable := makeTable("users", 10432,
		[]map[string]any{
			makeColumn("id", "int4", false),
			makeColumn("email", "varchar(255)", false),
			makeColumn("created_at", "timestamptz", true),
		},
		[]map[string]any{makePrimaryKeyIndex("users_pkey", []string{"id"})},
	)
	resp := makeMetadataResponse(makeSchema("public", usersTable))

	s, mock := mockMetadataServer(t, employeeDB(), resp)

	result, structured, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
		Include:  "columns",
	})
	require.NoError(t, err)
	require.False(t, result.IsError)

	output, ok := structured.(*SchemaOutput)
	require.True(t, ok)
	users := output.Schemas[0].Tables[0]
	require.Len(t, users.Columns, 3)
	// Proto order preserved (id, email, created_at) — NOT alphabetized.
	require.Equal(t, "id", users.Columns[0].Name)
	require.Equal(t, "int4", users.Columns[0].Type)
	require.False(t, users.Columns[0].Nullable)
	require.True(t, users.Columns[0].PrimaryKey)
	require.Equal(t, "email", users.Columns[1].Name)
	require.False(t, users.Columns[1].PrimaryKey)
	require.Equal(t, "created_at", users.Columns[2].Name)
	require.True(t, users.Columns[2].Nullable)
	// columns mode has no defaults/comments populated.
	require.Empty(t, users.Columns[0].Default)
	require.Empty(t, users.Columns[0].Comment)
	// No indexes or FKs in columns mode.
	require.Empty(t, users.Indexes)
	require.Empty(t, users.ForeignKeys)

	// Request includes limit=201.
	require.Equal(t, int32(201), mock.lastCall().Limit)
}

func TestGetSchema_IncludeDetails(t *testing.T) {
	ordersTable := map[string]any{
		"name":     "orders",
		"rowCount": "182394",
		"comment":  "Customer orders",
		"columns": []map[string]any{
			makeColumnWithDefault("id", "int8", "", "Order ID"),
			makeColumnWithDefault("user_id", "int4", "", "Customer ref"),
			makeColumnWithDefault("status", "varchar(32)", "'pending'", "Status flag"),
		},
		"indexes": []map[string]any{
			// Intentionally out of order.
			makeIndex("orders_user_id_idx", false, []string{"user_id"}),
			makePrimaryKeyIndex("orders_pkey", []string{"id"}),
		},
		"foreignKeys": []map[string]any{
			{
				"name":              "orders_user_id_fkey",
				"columns":           []string{"user_id"},
				"referencedTable":   "users",
				"referencedColumns": []string{"id"},
			},
		},
	}
	resp := makeMetadataResponse(makeSchema("public", ordersTable))

	s, _ := mockMetadataServer(t, employeeDB(), resp)

	result, structured, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
		Include:  "details",
	})
	require.NoError(t, err)
	require.False(t, result.IsError)

	output, ok := structured.(*SchemaOutput)
	require.True(t, ok)
	orders := output.Schemas[0].Tables[0]
	require.Equal(t, "Customer orders", orders.Comment)
	// Column defaults and comments populated.
	require.Equal(t, "'pending'", orders.Columns[2].Default)
	require.Equal(t, "Status flag", orders.Columns[2].Comment)
	// Indexes sorted alphabetically.
	require.Len(t, orders.Indexes, 2)
	require.Equal(t, "orders_pkey", orders.Indexes[0].Name)
	require.Equal(t, "orders_user_id_idx", orders.Indexes[1].Name)
	// FKs present.
	require.Len(t, orders.ForeignKeys, 1)
	require.Equal(t, "orders_user_id_fkey", orders.ForeignKeys[0].Name)
	require.Equal(t, "users", orders.ForeignKeys[0].ReferencedTable)
}

func TestGetSchema_TableFilter_ServerSideFilter(t *testing.T) {
	ordersTable := makeTable("orders", 182394,
		[]map[string]any{
			makeColumn("id", "int8", false),
			makeColumn("user_id", "int4", false),
		},
		[]map[string]any{makePrimaryKeyIndex("orders_pkey", []string{"id"})},
	)
	resp := makeMetadataResponse(makeSchema("public", ordersTable))

	s, mock := mockMetadataServer(t, employeeDB(), resp)

	_, _, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
		Table:    "orders",
	})
	require.NoError(t, err)

	require.Equal(t, `table == "orders"`, mock.lastCall().Filter)
	// table= mode should NOT include a limit.
	require.Equal(t, int32(0), mock.lastCall().Limit)
}

func TestGetSchema_TableImpliesDetails(t *testing.T) {
	ordersTable := map[string]any{
		"name":     "orders",
		"rowCount": "182394",
		"comment":  "Customer orders",
		"columns": []map[string]any{
			makeColumnWithDefault("id", "int8", "", "Order ID"),
		},
		"indexes": []map[string]any{makePrimaryKeyIndex("orders_pkey", []string{"id"})},
		"foreignKeys": []map[string]any{
			{
				"name":              "orders_user_id_fkey",
				"columns":           []string{"user_id"},
				"referencedTable":   "users",
				"referencedColumns": []string{"id"},
			},
		},
	}
	resp := makeMetadataResponse(makeSchema("public", ordersTable))

	s, _ := mockMetadataServer(t, employeeDB(), resp)

	result, structured, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
		Table:    "orders",
		// Include not set → should default to details.
	})
	require.NoError(t, err)
	require.False(t, result.IsError)
	require.Contains(t, result.Content[0].(*mcpsdk.TextContent).Text, "Table: orders (row count estimate 182394, ")

	output, ok := structured.(*SchemaOutput)
	require.True(t, ok)
	require.NotNil(t, output.Table)
	// Details level: indexes and FKs are populated.
	require.NotEmpty(t, output.Table.Indexes)
	require.NotEmpty(t, output.Table.ForeignKeys)
	// Column comment (details-only field) populated.
	require.Equal(t, "Order ID", output.Table.Columns[0].Comment)
}

func TestGetSchema_TableNotFound_RefetchesForCandidates(t *testing.T) {
	// First call: server-side filter returns nothing.
	emptyResp := makeMetadataResponse(makeSchema("public"))
	// Second call: no filter, returns candidates.
	ordersTable := makeTable("orders", 0, []map[string]any{makeColumn("id", "int4", false)}, nil)
	orderItemsTable := makeTable("order_items", 0, []map[string]any{makeColumn("id", "int4", false)}, nil)
	unrelatedTable := makeTable("invoices", 0, []map[string]any{makeColumn("id", "int4", false)}, nil)
	fullResp := makeMetadataResponse(makeSchema("public", ordersTable, orderItemsTable, unrelatedTable))

	s, mock := mockMetadataServer(t, employeeDB(), emptyResp, fullResp)

	result, _, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
		Table:    "orderz",
	})
	require.NoError(t, err)
	require.True(t, result.IsError)

	// Two metadata calls: filtered + candidate lookup.
	require.Equal(t, 2, mock.callCount())
	require.Equal(t, `table == "orderz"`, mock.callAt(0).Filter)
	require.Equal(t, "", mock.callAt(1).Filter)
	require.Equal(t, int32(schemaCandidateFetchLimit), mock.callAt(1).Limit)

	// Error body has candidates.
	text := result.Content[0].(*mcpsdk.TextContent).Text
	require.Contains(t, text, "TABLE_NOT_FOUND")
	require.Contains(t, text, "orders")
	require.Contains(t, text, "order_items")
	require.NotContains(t, text, "invoices")
}

func TestGetSchema_SchemaFilter_Postgres_ServerSideFilter(t *testing.T) {
	resp := makeMetadataResponse(makeSchema("public",
		makeTable("users", 10, []map[string]any{makeColumn("id", "int4", false)}, nil),
	))
	s, mock := mockMetadataServer(t, employeeDB(), resp)

	_, _, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
		Schema:   "public",
	})
	require.NoError(t, err)
	require.Equal(t, `schema == "public"`, mock.lastCall().Filter)
}

func TestGetSchema_SchemaAndTableFilter(t *testing.T) {
	resp := makeMetadataResponse(makeSchema("public",
		makeTable("orders", 10, []map[string]any{makeColumn("id", "int4", false)}, nil),
	))
	s, mock := mockMetadataServer(t, employeeDB(), resp)

	_, _, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
		Schema:   "public",
		Table:    "orders",
	})
	require.NoError(t, err)
	require.Equal(t, `schema == "public" && table == "orders"`, mock.lastCall().Filter)
}

func TestGetSchema_SchemaFilter_MySQL_DroppedClientSide(t *testing.T) {
	// MySQL has an empty schema name. The backend applies `schema == "X"` as an
	// exact match, which would filter out every table. The MCP layer must drop
	// the schema filter before calling the backend on single-schema engines.
	databases := []map[string]any{
		makeDatabase("instances/prod-mysql/databases/employee_db", "instances/prod-mysql", "projects/hr-system", "MYSQL", "ds-admin-1"),
	}
	resp := makeMetadataResponse(makeSchema("",
		makeTable("users", 10, []map[string]any{makeColumn("id", "int4", false)}, nil),
	))
	s, mock := mockMetadataServer(t, databases, resp)

	_, structured, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
		Schema:   "anything",
	})
	require.NoError(t, err)
	// The schema filter is dropped client-side — the backend sees an empty filter.
	require.Equal(t, "", mock.lastCall().Filter)

	output, ok := structured.(*SchemaOutput)
	require.True(t, ok)
	require.Len(t, output.Schemas, 1)
	require.Len(t, output.Schemas[0].Tables, 1)
}

func TestGetSchema_SchemaFilter_PassedThroughOnMultiSchemaEngines(t *testing.T) {
	// Spot-check a few known multi-schema engines to confirm the allowlist.
	for _, engine := range []string{"POSTGRES", "MSSQL", "ORACLE", "SNOWFLAKE", "REDSHIFT", "COCKROACHDB", "DATABRICKS", "TRINO", "SPANNER", "HIVE"} {
		t.Run(engine, func(t *testing.T) {
			databases := []map[string]any{
				makeDatabase("instances/prod/databases/employee_db", "instances/prod", "projects/hr-system", engine, "ds-1"),
			}
			resp := makeMetadataResponse(makeSchema("public",
				makeTable("users", 10, []map[string]any{makeColumn("id", "int4", false)}, nil),
			))
			s, mock := mockMetadataServer(t, databases, resp)

			_, _, err := s.handleGetSchema(testContext(), nil, SchemaInput{
				Database: "employee_db",
				Schema:   "public",
			})
			require.NoError(t, err)
			require.Equal(t, `schema == "public"`, mock.lastCall().Filter)
		})
	}
}

func TestGetSchema_SchemaFilter_DroppedOnSingleSchemaEngines(t *testing.T) {
	// Spot-check a few known single-schema engines.
	for _, engine := range []string{"MYSQL", "TIDB", "MARIADB", "CLICKHOUSE", "STARROCKS"} {
		t.Run(engine, func(t *testing.T) {
			databases := []map[string]any{
				makeDatabase("instances/prod/databases/employee_db", "instances/prod", "projects/hr-system", engine, "ds-1"),
			}
			resp := makeMetadataResponse(makeSchema("",
				makeTable("users", 10, []map[string]any{makeColumn("id", "int4", false)}, nil),
			))
			s, mock := mockMetadataServer(t, databases, resp)

			result, _, err := s.handleGetSchema(testContext(), nil, SchemaInput{
				Database: "employee_db",
				Schema:   "something",
			})
			require.NoError(t, err)
			require.Equal(t, "", mock.lastCall().Filter, "engine %s should drop the schema filter", engine)

			// The response should include a note about the dropped parameter.
			text := result.Content[0].(*mcpsdk.TextContent).Text
			require.Contains(t, text, "schema parameter ignored", "engine %s should annotate the dropped schema filter", engine)
			require.Contains(t, text, "does not use named schemas", "engine %s note should explain why", engine)
		})
	}
}

func TestGetSchema_PrimaryKeyDerivation(t *testing.T) {
	usersTable := makeTable("users", 10,
		[]map[string]any{
			makeColumn("id", "int4", false),
			makeColumn("email", "varchar(255)", false),
			makeColumn("created_at", "timestamptz", false),
		},
		[]map[string]any{
			makePrimaryKeyIndex("users_pkey", []string{"id"}),
			makeIndex("users_email_idx", true, []string{"email"}), // unique but NOT primary
		},
	)
	resp := makeMetadataResponse(makeSchema("public", usersTable))
	s, _ := mockMetadataServer(t, employeeDB(), resp)

	_, structured, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
		Include:  "columns",
	})
	require.NoError(t, err)
	output, ok := structured.(*SchemaOutput)
	require.True(t, ok)
	cols := output.Schemas[0].Tables[0].Columns
	require.True(t, cols[0].PrimaryKey, "id should be a PK (primary index)")
	require.False(t, cols[1].PrimaryKey, "email should NOT be a PK (unique only)")
	require.False(t, cols[2].PrimaryKey, "created_at should NOT be a PK")
}

func TestGetSchema_LimitForBulkModes(t *testing.T) {
	resp := makeMetadataResponse(makeSchema("public",
		makeTable("users", 10, []map[string]any{makeColumn("id", "int4", false)}, nil),
	))

	// columns → limit=201
	s, mock := mockMetadataServer(t, employeeDB(), resp)
	_, _, err := s.handleGetSchema(testContext(), nil, SchemaInput{Database: "employee_db", Include: "columns"})
	require.NoError(t, err)
	require.Equal(t, int32(201), mock.lastCall().Limit)

	// details → limit=201
	s2, mock2 := mockMetadataServer(t, employeeDB(), resp)
	_, _, err = s2.handleGetSchema(testContext(), nil, SchemaInput{Database: "employee_db", Include: "details"})
	require.NoError(t, err)
	require.Equal(t, int32(201), mock2.lastCall().Limit)

	// summary → no limit
	s3, mock3 := mockMetadataServer(t, employeeDB(), resp)
	_, _, err = s3.handleGetSchema(testContext(), nil, SchemaInput{Database: "employee_db"})
	require.NoError(t, err)
	require.Equal(t, int32(0), mock3.lastCall().Limit)

	// table= → no limit
	s4, mock4 := mockMetadataServer(t, employeeDB(), resp)
	_, _, err = s4.handleGetSchema(testContext(), nil, SchemaInput{Database: "employee_db", Table: "users"})
	require.NoError(t, err)
	require.Equal(t, int32(0), mock4.lastCall().Limit)
}

func TestGetSchema_NotTruncatedAtExactly200(t *testing.T) {
	tables := make([]map[string]any, schemaTableLimit)
	for i := range tables {
		tables[i] = makeTable(fmt.Sprintf("t%03d", i), 0, []map[string]any{makeColumn("id", "int4", false)}, nil)
	}
	resp := makeMetadataResponse(makeSchema("public", tables...))
	s, _ := mockMetadataServer(t, employeeDB(), resp)

	_, structured, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
		Include:  "columns",
	})
	require.NoError(t, err)
	output, ok := structured.(*SchemaOutput)
	require.True(t, ok)
	section := output.Schemas[0]
	require.Len(t, section.Tables, schemaTableLimit)
	require.False(t, section.Truncated)
	require.Equal(t, 0, section.TablesShown)
}

func TestGetSchema_TruncatedAt201(t *testing.T) {
	// Build 201 tables; the 201st sorted entry should be dropped.
	tables := make([]map[string]any, schemaTableLimit+1)
	for i := range tables {
		tables[i] = makeTable(fmt.Sprintf("t%03d", i), 0, []map[string]any{makeColumn("id", "int4", false)}, nil)
	}
	resp := makeMetadataResponse(makeSchema("public", tables...))
	s, _ := mockMetadataServer(t, employeeDB(), resp)

	_, structured, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
		Include:  "columns",
	})
	require.NoError(t, err)
	output, ok := structured.(*SchemaOutput)
	require.True(t, ok)
	section := output.Schemas[0]
	require.Len(t, section.Tables, schemaTableLimit)
	require.True(t, section.Truncated)
	require.Equal(t, schemaTableLimit, section.TablesShown)
	// The names are t000..t200; sorted alphabetically the 201st (last) is t200,
	// which should be dropped. Remaining last table is t199.
	require.Equal(t, "t199", section.Tables[schemaTableLimit-1].Name)
}

func TestGetSchema_PerSchemaLimitSemantics(t *testing.T) {
	// Build 3 schemas with 201 tables each (enough to trigger truncation in each).
	makeBigSchema := func(name string) map[string]any {
		tables := make([]map[string]any, schemaTableLimit+1)
		for i := range tables {
			tables[i] = makeTable(fmt.Sprintf("t%03d", i), 0, []map[string]any{makeColumn("id", "int4", false)}, nil)
		}
		return makeSchema(name, tables...)
	}
	resp := makeMetadataResponse(makeBigSchema("schema_a"), makeBigSchema("schema_b"), makeBigSchema("schema_c"))
	s, _ := mockMetadataServer(t, employeeDB(), resp)

	_, structured, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
		Include:  "columns",
	})
	require.NoError(t, err)
	output, ok := structured.(*SchemaOutput)
	require.True(t, ok)
	require.Len(t, output.Schemas, 3)
	totalTables := 0
	for _, section := range output.Schemas {
		require.True(t, section.Truncated)
		require.Len(t, section.Tables, schemaTableLimit)
		totalTables += len(section.Tables)
	}
	require.Equal(t, 3*schemaTableLimit, totalTables)
}

func TestGetSchema_DatabaseNotFound(t *testing.T) {
	s := newTestServerWithMock(t, mockListDatabases(employeeDB()))

	result, _, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "nonexistent",
	})
	require.NoError(t, err)
	require.True(t, result.IsError)

	text := result.Content[0].(*mcpsdk.TextContent).Text
	require.Contains(t, text, "DATABASE_NOT_FOUND")
}

func TestGetSchema_AmbiguousDatabase(t *testing.T) {
	databases := []map[string]any{
		makeDatabase("instances/prod-pg/databases/employee_db", "instances/prod-pg", "projects/hr-system", "POSTGRES", "ds-admin-1"),
		makeDatabase("instances/staging-mysql/databases/employee_db", "instances/staging-mysql", "projects/hr-system", "MYSQL", "ds-admin-2"),
	}
	s := newTestServerWithMock(t, mockListDatabases(databases))

	result, _, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
	})
	require.NoError(t, err)
	require.True(t, result.IsError)

	text := result.Content[0].(*mcpsdk.TextContent).Text
	require.Contains(t, text, "AMBIGUOUS_TARGET")
}

func TestGetSchema_SchemaSyncFailed(t *testing.T) {
	handler := http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
		if strings.Contains(r.URL.Path, "DatabaseService/GetDatabaseMetadata") {
			w.Header().Set("Content-Type", "application/json")
			w.WriteHeader(http.StatusInternalServerError)
			_ = json.NewEncoder(w).Encode(map[string]any{
				"message": "sync timeout after 30s",
				"code":    "INTERNAL",
			})
			return
		}
		mockListDatabases(employeeDB()).ServeHTTP(w, r)
	})
	s := newTestServerWithMock(t, handler)

	result, _, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
	})
	require.NoError(t, err)
	require.True(t, result.IsError)

	text := result.Content[0].(*mcpsdk.TextContent).Text
	require.Contains(t, text, "SCHEMA_SYNC_FAILED")
	require.Contains(t, text, "sync timeout after 30s")
}

func TestGetSchema_PermissionDenied(t *testing.T) {
	handler := http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
		if strings.Contains(r.URL.Path, "DatabaseService/GetDatabaseMetadata") {
			w.Header().Set("Content-Type", "application/json")
			w.WriteHeader(http.StatusForbidden)
			_ = json.NewEncoder(w).Encode(map[string]any{
				"message": `user does not have permission "bb.databases.getSchema"`,
				"code":    "PERMISSION_DENIED",
			})
			return
		}
		mockListDatabases(employeeDB()).ServeHTTP(w, r)
	})
	s := newTestServerWithMock(t, handler)

	result, _, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
	})
	require.NoError(t, err)
	require.True(t, result.IsError)

	text := result.Content[0].(*mcpsdk.TextContent).Text
	require.Contains(t, text, "PERMISSION_DENIED")
	require.Contains(t, text, "bb.databases.getSchema")
	require.NotContains(t, text, "suggestion", "the server's message is the whole answer")
}

func TestGetSchema_InvalidIncludeValue(t *testing.T) {
	s := newTestServerWithMock(t, http.NotFoundHandler())

	_, _, err := s.handleGetSchema(context.Background(), nil, SchemaInput{
		Database: "employee_db",
		Include:  "everything",
	})
	require.Error(t, err)
	require.Contains(t, err.Error(), "summary|columns|details")
}

func TestGetSchema_MissingDatabase(t *testing.T) {
	s := newTestServerWithMock(t, http.NotFoundHandler())
	_, _, err := s.handleGetSchema(context.Background(), nil, SchemaInput{})
	require.Error(t, err)
	require.Contains(t, err.Error(), "database is required")
}

func TestGetSchema_StableSortOrder(t *testing.T) {
	// Two schemas (out of order), each with a few tables (out of order).
	schemaB := makeSchema("schema_b",
		makeTable("zz", 0, []map[string]any{makeColumn("id", "int4", false)}, nil),
		makeTable("aa", 0, []map[string]any{makeColumn("id", "int4", false)}, nil),
	)
	schemaA := makeSchema("schema_a",
		makeTable("yy", 0, []map[string]any{makeColumn("id", "int4", false)}, nil),
		makeTable("bb", 0, []map[string]any{makeColumn("id", "int4", false)}, nil),
	)
	resp := makeMetadataResponse(schemaB, schemaA)

	s1, _ := mockMetadataServer(t, employeeDB(), resp)
	_, structured1, err := s1.handleGetSchema(testContext(), nil, SchemaInput{Database: "employee_db"})
	require.NoError(t, err)

	s2, _ := mockMetadataServer(t, employeeDB(), resp)
	_, structured2, err := s2.handleGetSchema(testContext(), nil, SchemaInput{Database: "employee_db"})
	require.NoError(t, err)

	json1, _ := json.Marshal(structured1)
	json2, _ := json.Marshal(structured2)
	require.Equal(t, string(json1), string(json2))

	// Explicit ordering assertions.
	output, ok := structured1.(*SchemaOutput)
	require.True(t, ok)
	require.Equal(t, "schema_a", output.Schemas[0].Name)
	require.Equal(t, "schema_b", output.Schemas[1].Name)
	require.Equal(t, "bb", output.Schemas[0].Tables[0].Name)
	require.Equal(t, "yy", output.Schemas[0].Tables[1].Name)
	require.Equal(t, "aa", output.Schemas[1].Tables[0].Name)
	require.Equal(t, "zz", output.Schemas[1].Tables[1].Name)
}

// TestGetSchema_SummaryDoesNotTruncate verifies the P1 fix: summary mode must
// return every table in a schema even when there are more than schemaTableLimit
// of them, because summary is designed to fit large catalogs (per-table payload
// is tiny) and the server isn't asked to limit in summary mode.
func TestGetSchema_SummaryDoesNotTruncate(t *testing.T) {
	// Build 250 tables — well over schemaTableLimit (200).
	tableCount := schemaTableLimit + 50
	tables := make([]map[string]any, tableCount)
	for i := range tables {
		tables[i] = makeTable(fmt.Sprintf("t%03d", i), 0, []map[string]any{makeColumn("id", "int4", false)}, nil)
	}
	resp := makeMetadataResponse(makeSchema("public", tables...))
	s, mock := mockMetadataServer(t, employeeDB(), resp)

	_, structured, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
		// No Include set → defaults to summary.
	})
	require.NoError(t, err)
	// Summary mode does not send a server-side limit.
	require.Equal(t, int32(0), mock.lastCall().Limit)

	output, ok := structured.(*SchemaOutput)
	require.True(t, ok)
	require.Len(t, output.Schemas, 1)
	section := output.Schemas[0]
	require.Len(t, section.Tables, tableCount, "summary mode must not truncate")
	require.False(t, section.Truncated)
	require.Equal(t, 0, section.TablesShown)
}

// TestGetSchema_AmbiguousTableAcrossSchemas verifies the P2 fix: when the same
// table name exists in multiple schemas and the caller did not specify schema=,
// the tool must return an AMBIGUOUS_TABLE error instead of silently picking the
// first match.
func TestGetSchema_AmbiguousTableAcrossSchemas(t *testing.T) {
	usersPublic := makeTable("users", 10,
		[]map[string]any{makeColumn("id", "int4", false)},
		[]map[string]any{makePrimaryKeyIndex("users_pkey", []string{"id"})},
	)
	usersAnalytics := makeTable("users", 20,
		[]map[string]any{makeColumn("uid", "int4", false)},
		[]map[string]any{makePrimaryKeyIndex("users_pkey", []string{"uid"})},
	)
	resp := makeMetadataResponse(
		makeSchema("analytics", usersAnalytics),
		makeSchema("public", usersPublic),
	)
	s, _ := mockMetadataServer(t, employeeDB(), resp)

	result, _, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
		Table:    "users",
		// No Schema set → both matches are visible.
	})
	require.NoError(t, err)
	require.True(t, result.IsError)

	text := result.Content[0].(*mcpsdk.TextContent).Text
	require.Contains(t, text, "AMBIGUOUS_TABLE")
	require.Contains(t, text, "analytics")
	require.Contains(t, text, "public")
	require.Contains(t, text, "schema=")
}

// TestGetSchema_TableInSpecificSchema verifies the happy path for the P2 fix:
// with a schema= hint, a single match proceeds normally.
func TestGetSchema_TableInSpecificSchema(t *testing.T) {
	usersPublic := makeTable("users", 10,
		[]map[string]any{makeColumn("id", "int4", false)},
		[]map[string]any{makePrimaryKeyIndex("users_pkey", []string{"id"})},
	)
	// Simulate a server that honors the schema filter: only `public` is returned.
	resp := makeMetadataResponse(makeSchema("public", usersPublic))
	s, mock := mockMetadataServer(t, employeeDB(), resp)

	_, structured, err := s.handleGetSchema(testContext(), nil, SchemaInput{
		Database: "employee_db",
		Schema:   "public",
		Table:    "users",
	})
	require.NoError(t, err)
	require.Equal(t, `schema == "public" && table == "users"`, mock.lastCall().Filter)

	output, ok := structured.(*SchemaOutput)
	require.True(t, ok)
	require.NotNil(t, output.Table)
	require.Equal(t, "users", output.Table.Name)
}

func TestBuildMetadataFilter(t *testing.T) {
	tests := []struct {
		name   string
		schema string
		table  string
		want   string
	}{
		{"empty", "", "", ""},
		{"schema only", "public", "", `schema == "public"`},
		{"table only", "", "orders", `table == "orders"`},
		{"both", "public", "orders", `schema == "public" && table == "orders"`},
	}
	for _, tc := range tests {
		t.Run(tc.name, func(t *testing.T) {
			require.Equal(t, tc.want, buildMetadataFilter(tc.schema, tc.table))
		})
	}
}