package packs

import (
	"context"
	"regexp"
	"testing"

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

// TestWebhookSecretSweeper_SoftDeletesOrphans verifies that applyWebhookSecretPackSweeper
// issues an UPDATE to soft-delete a definition row whose Name is NOT in the
// declared set, while leaving declared rows untouched.
func TestWebhookSecretSweeper_SoftDeletesOrphans(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	const ownerPack = "my-connector"

	// Pack declares only "new"; "old" is the orphan in the DB.
	batch := &webhookSecretPackBatch{declaredNames: []string{"new"}}

	// Query for live rows: returns "old-id"/"old" and "new-id"/"new".
	mock.ExpectQuery(regexp.QuoteMeta("SELECT Id, Name\nFROM _System_WebhookSecret\nWHERE OwnerPack =")).
		WithArgs(ownerPack).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name"}).
			AddRow("old-id", "old").
			AddRow("new-id", "new"),
		)

	// Soft-delete definition row for "old".
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_WebhookSecret\nSET IsDeleted = 1")).
		WithArgs("old-id").
		WillReturnResult(sqlmock.NewResult(0, 1))

	// Deactivate value rows for "old".
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_WebhookSecretValue\nSET IsActive = 0")).
		WithArgs("old-id").
		WillReturnResult(sqlmock.NewResult(0, 1))

	if err := applyWebhookSecretPackSweeper(context.Background(), db, batch, ownerPack); err != nil {
		t.Fatalf("applyWebhookSecretPackSweeper: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

// TestWebhookSecretSweeper_DeactivatesOrphanValues verifies that both the
// definition row and its value rows are updated when an orphan is swept.
func TestWebhookSecretSweeper_DeactivatesOrphanValues(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	const ownerPack = "my-connector"

	// Pack declares nothing; "old" is the only live row → it is orphaned.
	batch := &webhookSecretPackBatch{}

	// Query for live rows.
	mock.ExpectQuery(regexp.QuoteMeta("SELECT Id, Name\nFROM _System_WebhookSecret\nWHERE OwnerPack =")).
		WithArgs(ownerPack).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name"}).
			AddRow("old-id", "old"),
		)

	// Soft-delete the definition.
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_WebhookSecret\nSET IsDeleted = 1")).
		WithArgs("old-id").
		WillReturnResult(sqlmock.NewResult(0, 1))

	// Deactivate the value row.
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_WebhookSecretValue\nSET IsActive = 0")).
		WithArgs("old-id").
		WillReturnResult(sqlmock.NewResult(0, 1))

	if err := applyWebhookSecretPackSweeper(context.Background(), db, batch, ownerPack); err != nil {
		t.Fatalf("applyWebhookSecretPackSweeper: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

// TestWebhookSecretSweeper_CaseInsensitiveDeclaredVsLiveName verifies that a
// pack declaring "Events" (mixed case) does NOT treat a live row named "events"
// (lowercase — as stored by an earlier install) as an orphan.  This covers the
// case where TiDB's case-insensitive (OwnerPack, Name) UNIQUE collation means
// the row was matched at upsert time but the stored Name column keeps its
// original casing.
func TestWebhookSecretSweeper_CaseInsensitiveDeclaredVsLiveName(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	const ownerPack = "my-connector"

	// Declared with mixed case; live row has lowercase name from earlier install.
	batch := &webhookSecretPackBatch{declaredNames: []string{"Events"}}

	mock.ExpectQuery(regexp.QuoteMeta("SELECT Id, Name\nFROM _System_WebhookSecret\nWHERE OwnerPack =")).
		WithArgs(ownerPack).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name"}).
			AddRow("ev-id", "events"),
		)

	// No UPDATE expectations — "events" matches "Events" case-insensitively.
	if err := applyWebhookSecretPackSweeper(context.Background(), db, batch, ownerPack); err != nil {
		t.Fatalf("applyWebhookSecretPackSweeper: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

// TestWebhookSecretSweeper_CaseInsensitiveLowerDeclaredUpperLive verifies the
// inverse scenario: pack declares "events" (lowercase) while the stored live
// row has Name "EVENTS" (uppercase).  The sweeper must still treat this as
// declared and issue no orphan deletes.
func TestWebhookSecretSweeper_CaseInsensitiveLowerDeclaredUpperLive(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	const ownerPack = "my-connector"

	// Declared lowercase; live row stored as uppercase.
	batch := &webhookSecretPackBatch{declaredNames: []string{"events"}}

	mock.ExpectQuery(regexp.QuoteMeta("SELECT Id, Name\nFROM _System_WebhookSecret\nWHERE OwnerPack =")).
		WithArgs(ownerPack).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name"}).
			AddRow("ev-id", "EVENTS"),
		)

	// No UPDATE expectations — "EVENTS" matches "events" case-insensitively.
	if err := applyWebhookSecretPackSweeper(context.Background(), db, batch, ownerPack); err != nil {
		t.Fatalf("applyWebhookSecretPackSweeper: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

// TestWebhookSecretSweeper_NoOpWhenAllDeclared verifies that the sweeper issues
// only the SELECT (to check for orphans) and no UPDATE statements when the live
// set fully matches the declared set.
func TestWebhookSecretSweeper_NoOpWhenAllDeclared(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	const ownerPack = "my-connector"

	// Declared and live sets are identical.
	batch := &webhookSecretPackBatch{declaredNames: []string{"events", "orders"}}

	mock.ExpectQuery(regexp.QuoteMeta("SELECT Id, Name\nFROM _System_WebhookSecret\nWHERE OwnerPack =")).
		WithArgs(ownerPack).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name"}).
			AddRow("ev-id", "events").
			AddRow("or-id", "orders"),
		)

	// No UPDATE expectations — the sweeper must be a no-op.
	if err := applyWebhookSecretPackSweeper(context.Background(), db, batch, ownerPack); err != nil {
		t.Fatalf("applyWebhookSecretPackSweeper: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}