package connector_inbound_routing

import (
	"context"
	"fmt"
	"regexp"
	"testing"

	sqlmock "github.com/DATA-DOG/go-sqlmock"
)

// loadRouteSQL is the exact SQL issued by loadConnectorInboundRouteFlowName.
// Option 3 R6 fix: scoped by InstalledPackId (not Namespace) so the Dispatcher
// can pass the same packInstallID to both route resolution and Enqueue.
// B19 fix (R16): AND IsCurrent = 1 ensures only currently-declared manifest
// routes are returned for live traffic; preserved-for-backlog rows
// (IsCurrent=0, IsDeleted=0) are excluded.
const loadRouteSQL = `SELECT FlowDeveloperName, Version
  FROM _System_ConnectorInboundRoute
 WHERE InstalledPackId = ?
   AND EventKind = ?
   AND COALESCE(IsDeleted, 0) = 0
   AND IsCurrent = 1
 ORDER BY Version DESC
 LIMIT 1`

// loadRouteByVersionSQL is the exact SQL issued by loadConnectorInboundRouteByVersion.
// Note: the query SELECTs the execution spec and COALESCE(IsDeleted, 0) so the
// caller can distinguish "row found but swept" (ErrRouteVersionSwept) from "row absent"
// (ErrRouteNotDeclared).
const loadRouteByVersionSQL = `SELECT FlowDeveloperName,
       COALESCE(InvocationMode, ''), COALESCE(BatchInputName, ''), COALESCE(BatchResultOutputName, ''), COALESCE(MaxBatchSize, 0),
       COALESCE(QueueClass, ''), COALESCE(FlowExecutionFilter, ''),
       COALESCE(IsDeleted, 0)
  FROM _System_ConnectorInboundRoute
 WHERE InstalledPackId = ?
   AND EventKind = ?
   AND Version = ?
 LIMIT 1`

func routeVersionRows() *sqlmock.Rows {
	return sqlmock.NewRows([]string{
		"FlowDeveloperName",
		"InvocationMode",
		"BatchInputName",
		"BatchResultOutputName",
		"MaxBatchSize",
		"QueueClass",
		"FlowExecutionFilter",
		"IsDeleted",
	})
}

// TestLoadConnectorInboundRouteFlowName_HappyPath verifies a well-formed live
// route returns the FlowDeveloperName and Version when scoped by packInstallID.
func TestLoadConnectorInboundRouteFlowName_HappyPath(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(regexp.QuoteMeta(loadRouteSQL)).
		WithArgs("install-abc", "contact.created").
		WillReturnRows(sqlmock.NewRows([]string{"FlowDeveloperName", "Version"}).AddRow("ContactCreatedHandler", int64(1)))

	flow, version, found, err := loadConnectorInboundRouteFlowName(context.Background(), db, "install-abc", "contact.created")
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if !found {
		t.Fatal("expected found=true, got false")
	}
	if flow != "ContactCreatedHandler" {
		t.Errorf("expected FlowDeveloperName=%q, got %q", "ContactCreatedHandler", flow)
	}
	if version != 1 {
		t.Errorf("expected Version=1, got %d", version)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Errorf("unmet expectations: %v", err)
	}
}

// TestLoadConnectorInboundRouteFlowName_LatestVersionSelected verifies that when
// multiple version rows exist the latest (highest Version) is returned.
func TestLoadConnectorInboundRouteFlowName_LatestVersionSelected(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// SQL returns Version=3 (the latest) — ORDER BY Version DESC LIMIT 1.
	mock.ExpectQuery(regexp.QuoteMeta(loadRouteSQL)).
		WithArgs("install-abc", "contact.created").
		WillReturnRows(sqlmock.NewRows([]string{"FlowDeveloperName", "Version"}).AddRow("HandlerV3", int64(3)))

	flow, version, found, err := loadConnectorInboundRouteFlowName(context.Background(), db, "install-abc", "contact.created")
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if !found {
		t.Fatal("expected found=true, got false")
	}
	if flow != "HandlerV3" || version != 3 {
		t.Errorf("expected (HandlerV3, 3), got (%q, %d)", flow, version)
	}
}

// TestLoadConnectorInboundRouteFlowName_MissingRoute verifies ErrNoRows results
// in found=false (not an error).
func TestLoadConnectorInboundRouteFlowName_MissingRoute(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(regexp.QuoteMeta(loadRouteSQL)).
		WithArgs("install-missing", "some.event").
		WillReturnRows(sqlmock.NewRows([]string{"FlowDeveloperName", "Version"}))

	flow, version, found, err := loadConnectorInboundRouteFlowName(context.Background(), db, "install-missing", "some.event")
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if found {
		t.Fatal("expected found=false for missing route, got true")
	}
	if flow != "" || version != 0 {
		t.Errorf("expected empty flow/version for missing route, got (%q, %d)", flow, version)
	}
}

// TestLoadConnectorInboundRouteFlowName_SoftDeletedRoute verifies a route row with
// IsDeleted=1 is not returned (SQL predicate filters it).
func TestLoadConnectorInboundRouteFlowName_SoftDeletedRoute(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(regexp.QuoteMeta(loadRouteSQL)).
		WithArgs("install-abc", "deleted.event").
		WillReturnRows(sqlmock.NewRows([]string{"FlowDeveloperName", "Version"}))

	_, _, found, err := loadConnectorInboundRouteFlowName(context.Background(), db, "install-abc", "deleted.event")
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if found {
		t.Fatal("expected found=false for soft-deleted route, got true")
	}
}

// TestLoadConnectorInboundRouteFlowName_DBError verifies DB errors propagate.
func TestLoadConnectorInboundRouteFlowName_DBError(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(regexp.QuoteMeta(loadRouteSQL)).
		WithArgs("install-abc", "contact.created").
		WillReturnError(fmt.Errorf("connection refused"))

	_, _, _, err = loadConnectorInboundRouteFlowName(context.Background(), db, "install-abc", "contact.created")
	if err == nil {
		t.Fatal("expected DB error, got nil")
	}
}

// TestLoadConnectorInboundRouteFlowName_EmptyInputs verifies that empty
// packInstallID or eventKind returns (false, nil) without querying the DB.
func TestLoadConnectorInboundRouteFlowName_EmptyInputs(t *testing.T) {
	db, _, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	cases := []struct{ packID, ek string }{
		{"", "contact.created"},
		{"install-abc", ""},
		{"", ""},
	}
	for _, tc := range cases {
		flow, version, found, err := loadConnectorInboundRouteFlowName(context.Background(), db, tc.packID, tc.ek)
		if err != nil {
			t.Errorf("packID=%q ek=%q: unexpected error: %v", tc.packID, tc.ek, err)
		}
		if found || flow != "" || version != 0 {
			t.Errorf("packID=%q ek=%q: expected (false, nil) for empty inputs, got (%v, %q, %d)", tc.packID, tc.ek, found, flow, version)
		}
	}
}

// TestLoadConnectorInboundRouteFlowName_EventKindCanonicalized verifies that
// the eventKind is lowercased before the query.
func TestLoadConnectorInboundRouteFlowName_EventKindCanonicalized(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// Caller passes mixed-case; helper must canonicalize to lower before query.
	mock.ExpectQuery(regexp.QuoteMeta(loadRouteSQL)).
		WithArgs("install-abc", "contact.created"). // lowercase expected
		WillReturnRows(sqlmock.NewRows([]string{"FlowDeveloperName", "Version"}).AddRow("ContactCreatedHandler", int64(1)))

	flow, _, found, err := loadConnectorInboundRouteFlowName(context.Background(), db, "install-abc", "Contact.Created")
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if !found || flow != "ContactCreatedHandler" {
		t.Errorf("mixed-case eventKind: expected (found=true, flow=ContactCreatedHandler), got (%v, %q)", found, flow)
	}
}

// TestLoadConnectorInboundRouteFlowName_ScopesQueryByPackInstallID verifies that
// two packs with the same EventKind but different InstalledPackIds are resolved
// independently.  Pack-A's route must not be returned when querying pack-B.
// This is the key incarnation-scope invariant introduced in R6.
func TestLoadConnectorInboundRouteFlowName_ScopesQueryByPackInstallID(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// Query for pack-A returns HandlerA (version 1).
	mock.ExpectQuery(regexp.QuoteMeta(loadRouteSQL)).
		WithArgs("install-pack-a", "contact.created").
		WillReturnRows(sqlmock.NewRows([]string{"FlowDeveloperName", "Version"}).AddRow("HandlerA", int64(1)))

	// Query for pack-B returns HandlerB (version 2) — different handler, same event.
	mock.ExpectQuery(regexp.QuoteMeta(loadRouteSQL)).
		WithArgs("install-pack-b", "contact.created").
		WillReturnRows(sqlmock.NewRows([]string{"FlowDeveloperName", "Version"}).AddRow("HandlerB", int64(2)))

	flowA, versionA, foundA, errA := loadConnectorInboundRouteFlowName(context.Background(), db, "install-pack-a", "contact.created")
	if errA != nil || !foundA || flowA != "HandlerA" || versionA != 1 {
		t.Errorf("pack-A: expected (HandlerA, 1, true, nil), got (%q, %d, %v, %v)", flowA, versionA, foundA, errA)
	}

	flowB, versionB, foundB, errB := loadConnectorInboundRouteFlowName(context.Background(), db, "install-pack-b", "contact.created")
	if errB != nil || !foundB || flowB != "HandlerB" || versionB != 2 {
		t.Errorf("pack-B: expected (HandlerB, 2, true, nil), got (%q, %d, %v, %v)", flowB, versionB, foundB, errB)
	}

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Errorf("unmet expectations: %v", err)
	}
}

// TestLoadConnectorInboundRouteByVersion_HappyPath verifies exact version lookup
// for a live (IsDeleted=0) row.
func TestLoadConnectorInboundRouteByVersion_HappyPath(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(regexp.QuoteMeta(loadRouteByVersionSQL)).
		WithArgs("install-123", "contact.created", int64(2)).
		WillReturnRows(routeVersionRows().AddRow("ContactCreatedV2", "", "", "", 0, "", "", int64(0)))

	spec, isDeleted, found, err := loadConnectorInboundRouteByVersion(context.Background(), db, "install-123", "contact.created", 2)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if !found {
		t.Fatal("expected found=true")
	}
	if isDeleted {
		t.Fatal("expected isDeleted=false for live row")
	}
	if spec.FlowDeveloperName != "ContactCreatedV2" {
		t.Errorf("expected flow=%q, got %q", "ContactCreatedV2", spec.FlowDeveloperName)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Errorf("unmet expectations: %v", err)
	}
}

// TestLoadConnectorInboundRouteByVersion_SweptRow verifies that a soft-deleted row
// (IsDeleted=1) is returned as (flow, isDeleted=true, found=true, nil) so the caller
// can distinguish "swept" from "absent".
func TestLoadConnectorInboundRouteByVersion_SweptRow(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(regexp.QuoteMeta(loadRouteByVersionSQL)).
		WithArgs("install-123", "contact.created", int64(1)).
		WillReturnRows(routeVersionRows().AddRow("ContactCreatedV1", "", "", "", 0, "", "", int64(1)))

	spec, isDeleted, found, err := loadConnectorInboundRouteByVersion(context.Background(), db, "install-123", "contact.created", 1)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if !found {
		t.Fatal("expected found=true for a soft-deleted row (row exists in DB)")
	}
	if !isDeleted {
		t.Fatal("expected isDeleted=true for soft-deleted row")
	}
	if spec.FlowDeveloperName != "ContactCreatedV1" {
		t.Errorf("expected flow=%q even for swept row, got %q", "ContactCreatedV1", spec.FlowDeveloperName)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Errorf("unmet expectations: %v", err)
	}
}

// TestLoadConnectorInboundRouteByVersion_NotFound verifies ("", false, false, nil) on no row.
func TestLoadConnectorInboundRouteByVersion_NotFound(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(regexp.QuoteMeta(loadRouteByVersionSQL)).
		WithArgs("install-123", "contact.created", int64(99)).
		WillReturnRows(routeVersionRows())

	spec, isDeleted, found, err := loadConnectorInboundRouteByVersion(context.Background(), db, "install-123", "contact.created", 99)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if found {
		t.Fatal("expected found=false for missing version")
	}
	if isDeleted {
		t.Fatal("expected isDeleted=false when row absent")
	}
	if spec.FlowDeveloperName != "" {
		t.Errorf("expected empty flow, got %q", spec.FlowDeveloperName)
	}
}

// TestLoadConnectorInboundRouteByVersion_InvalidInputs verifies ("", false, false, nil) for
// empty or zero inputs without querying the DB.
func TestLoadConnectorInboundRouteByVersion_InvalidInputs(t *testing.T) {
	db, _, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	cases := []struct {
		packID  string
		ek      string
		version int64
	}{
		{"", "contact.created", 1},
		{"install-123", "", 1},
		{"install-123", "contact.created", 0}, // version=0 is the sentinel; no DB query
		{"install-123", "contact.created", -1},
	}
	for _, tc := range cases {
		spec, isDeleted, found, err := loadConnectorInboundRouteByVersion(context.Background(), db, tc.packID, tc.ek, tc.version)
		if err != nil {
			t.Errorf("packID=%q ek=%q v=%d: unexpected error: %v", tc.packID, tc.ek, tc.version, err)
		}
		if found || isDeleted || spec.FlowDeveloperName != "" {
			t.Errorf("packID=%q ek=%q v=%d: expected (false, false, nil) for invalid inputs", tc.packID, tc.ek, tc.version)
		}
	}
}

// TestResolveFlow_FiltersOutPreservedForBacklogRows verifies that
// loadConnectorInboundRouteFlowName (the live-traffic resolution path) does NOT
// return a row that is preserved for backlog drain (IsCurrent=0, IsDeleted=0).
//
// B19 fix (codex R16 BLOCK): the sweeper marks dropped-eventKind rows with
// IsCurrent=0 instead of immediately soft-deleting them, so the backlog worker
// can still find the row via loadConnectorInboundRouteByVersion. Without the
// AND IsCurrent=1 predicate in loadConnectorInboundRouteFlowName, new webhook
// traffic would continue routing to the preserved row even after the eventKind
// was removed from the manifest.
//
// The mock returns no rows for the loadRouteSQL query (which now includes
// AND IsCurrent=1); this simulates the DB correctly honouring the filter and
// returning no result for the IsCurrent=0 preserved row.
func TestResolveFlow_FiltersOutPreservedForBacklogRows(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// Simulate: DB has a row with IsCurrent=0 (preserved for backlog).
	// loadRouteSQL filters AND IsCurrent=1 → DB returns no rows for this query.
	mock.ExpectQuery(regexp.QuoteMeta(loadRouteSQL)).
		WithArgs("install-abc", "dropped.event").
		WillReturnRows(sqlmock.NewRows([]string{"FlowDeveloperName", "Version"}))

	flow, version, found, err := loadConnectorInboundRouteFlowName(context.Background(), db, "install-abc", "dropped.event")
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if found {
		t.Fatalf("expected found=false for IsCurrent=0 (preserved-for-backlog) row, got found=true flow=%q version=%d", flow, version)
	}
	if flow != "" || version != 0 {
		t.Errorf("expected empty (flow=%q, version=%d) when preserved row excluded", flow, version)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Errorf("unmet expectations: %v", err)
	}
}

// TestLoadConnectorInboundRouteByVersion_PreservedRowStillAccessible verifies that
// loadConnectorInboundRouteByVersion CAN still return a preserved-for-backlog row
// (IsCurrent=0, IsDeleted=0) — i.e., the backlog worker is not blocked by the
// IsCurrent filter.
//
// loadConnectorInboundRouteByVersion does NOT filter by IsCurrent; it fetches by
// exact (InstalledPackId, EventKind, Version) triple. A preserved row (IsCurrent=0,
// IsDeleted=0) must be visible here so the worker can drain the backlog.
func TestLoadConnectorInboundRouteByVersion_PreservedRowStillAccessible(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// The row is not soft-deleted (IsDeleted=0) but IsCurrent=0 (preserved for backlog).
	// loadConnectorInboundRouteByVersion does not filter by IsCurrent → row is returned.
	mock.ExpectQuery(regexp.QuoteMeta(loadRouteByVersionSQL)).
		WithArgs("install-abc", "dropped.event", int64(1)).
		WillReturnRows(routeVersionRows().AddRow("DroppedHandler", "", "", "", 0, "", "", int64(0)))

	spec, isDeleted, found, err := loadConnectorInboundRouteByVersion(context.Background(), db, "install-abc", "dropped.event", 1)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if !found {
		t.Fatal("expected found=true: preserved-for-backlog row (IsCurrent=0, IsDeleted=0) must be accessible by exact version")
	}
	if isDeleted {
		t.Fatal("expected isDeleted=false: preserved row has IsDeleted=0")
	}
	if spec.FlowDeveloperName != "DroppedHandler" {
		t.Errorf("expected flow=%q, got %q", "DroppedHandler", spec.FlowDeveloperName)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Errorf("unmet expectations: %v", err)
	}
}