// Integration test for the connector sync-state SQL repository against a live
// TiDB. Skipped automatically when TIDB_HOST / TIDB_PORT (default 127.0.0.1:4001)
// is unreachable — it runs in CI where TiDB is provisioned.
//
// Regression guard for #2026: the `Cursor` column is a reserved word in
// TiDB/MySQL. Before the fix the repository referenced it unquoted, so every
// statement that touched the column failed to parse with Error 1064, which made
// GET /api/connectors/:namespace/health return HTTP 500. The existing
// service_test.go uses a mock repository and never executes SQL, so it could not
// catch this; this test executes the real read + cursor-upsert SQL so a
// reserved-word regression fails the build.
package connector_syncstate

import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"testing"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

// openTiDB opens a temporary TiDB database for a single test run and returns a
// cleanup func that drops the database and closes both connections. The pattern
// mirrors connector_events/concurrent_claim_integration_test.go:openTiDB.
func openTiDB(t *testing.T) (*sql.DB, func()) {
	t.Helper()
	host := os.Getenv("TIDB_HOST")
	if host == "" {
		host = "127.0.0.1"
	}
	port := os.Getenv("TIDB_PORT")
	if port == "" {
		port = "4001"
	}
	user := os.Getenv("TIDB_USER")
	if user == "" {
		user = "root"
	}
	pwd := os.Getenv("TIDB_PASSWORD")

	adminDSN := fmt.Sprintf("%s:%s@tcp(%s:%s)/?parseTime=true&loc=Local", user, pwd, host, port)
	adminDB, err := sql.Open("mysql", adminDSN)
	if err != nil {
		t.Skipf("skipping integration test: open admin TiDB: %v", err)
	}
	if err := adminDB.Ping(); err != nil {
		_ = adminDB.Close()
		t.Skipf("skipping integration test: TiDB not reachable at %s:%s: %v", host, port, err)
	}

	dbName := fmt.Sprintf("nexuscrm_connector_syncstate_%d", time.Now().UnixNano())
	if _, err := adminDB.Exec("CREATE DATABASE `" + dbName + "`"); err != nil {
		_ = adminDB.Close()
		t.Skipf("skipping integration test: cannot create temporary database: %v", err)
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true&loc=Local", user, pwd, host, port, dbName)
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		_, _ = adminDB.Exec("DROP DATABASE IF EXISTS `" + dbName + "`")
		_ = adminDB.Close()
		t.Fatalf("open test TiDB connection: %v", err)
	}
	if err := db.Ping(); err != nil {
		_ = db.Close()
		_, _ = adminDB.Exec("DROP DATABASE IF EXISTS `" + dbName + "`")
		_ = adminDB.Close()
		t.Fatalf("ping test TiDB connection: %v", err)
	}

	cleanup := func() {
		_ = db.Close()
		_, _ = adminDB.Exec("DROP DATABASE IF EXISTS `" + dbName + "`")
		_ = adminDB.Close()
	}
	return db, cleanup
}

// createSyncStateTable creates _System_SyncState in the temporary test database.
// DDL matches bootstrap/system_tables.json (the `Cursor` reserved word is quoted
// in DDL too, otherwise the CREATE itself fails to parse).
func createSyncStateTable(t *testing.T, db *sql.DB) {
	t.Helper()
	_, err := db.Exec("CREATE TABLE _System_SyncState (\n" +
		"  Id                VARCHAR(255) PRIMARY KEY,\n" +
		"  InstalledPackId   VARCHAR(255) NOT NULL,\n" +
		"  Namespace         VARCHAR(255) NOT NULL,\n" +
		"  Object            VARCHAR(255) NOT NULL,\n" +
		"  Direction         VARCHAR(32)  NOT NULL,\n" +
		"  `Cursor`          TEXT,\n" +
		"  LastSyncAt        DATETIME,\n" +
		"  LastErrorMessage  TEXT,\n" +
		"  LastErrorAt       DATETIME,\n" +
		"  RunningErrorCount INT          NOT NULL DEFAULT 0,\n" +
		"  IsActive          BOOLEAN      NOT NULL DEFAULT TRUE,\n" +
		"  CreatedDate       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,\n" +
		"  LastModifiedDate  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,\n" +
		"  UNIQUE KEY uniq_sync_state (InstalledPackId, Object, Direction)\n" +
		")")
	if err != nil {
		t.Fatalf("create _System_SyncState: %v", err)
	}
}

// TestRepository_CursorColumnSQLParses exercises every repository statement that
// references the reserved `Cursor` column against a live TiDB. On unfixed code
// each call fails with Error 1064; with the column quoted they succeed and the
// cursor value round-trips through both the Get (single-row) and ListByInstallID
// (health-endpoint) read paths.
func TestRepository_CursorColumnSQLParses(t *testing.T) {
	db, cleanup := openTiDB(t)
	defer cleanup()
	createSyncStateTable(t, db)

	ctx := context.Background()
	r := NewRepository(db)

	const (
		installID = "pkginstall-2026"
		namespace = "demo-connector"
		object    = "Lead"
		direction = DirectionInbound
	)

	// INSERT path (lines 59/63): exercises the unquoted-column INSERT column list
	// and ON DUPLICATE KEY UPDATE that fail with 1064 before the fix.
	if err := r.UpsertCursor(ctx, installID, namespace, object, direction, "page-token-1"); err != nil {
		t.Fatalf("UpsertCursor insert: %v", err)
	}

	// Single-row SELECT path (line 30).
	got, err := r.Get(ctx, installID, object, direction)
	if err != nil {
		t.Fatalf("Get after first upsert: %v", err)
	}
	if got == nil {
		t.Fatal("Get returned nil after UpsertCursor")
	}
	if got.Cursor != "page-token-1" {
		t.Fatalf("Cursor round-trip mismatch: got %q want %q", got.Cursor, "page-token-1")
	}

	// ON DUPLICATE KEY UPDATE path (line 63): second upsert on the same
	// (InstalledPackId, Object, Direction) unique key must update the cursor.
	if err := r.UpsertCursor(ctx, installID, namespace, object, direction, "page-token-2"); err != nil {
		t.Fatalf("UpsertCursor update: %v", err)
	}
	got, err = r.Get(ctx, installID, object, direction)
	if err != nil {
		t.Fatalf("Get after second upsert: %v", err)
	}
	if got == nil || got.Cursor != "page-token-2" {
		t.Fatalf("Cursor not updated by ON DUPLICATE KEY UPDATE: got %+v", got)
	}

	// List SELECT path (line 107): this is the query GetHealth reaches via
	// ListByNamespace -> ListByInstallID, the one that returned 500 before the fix.
	states, err := r.ListByInstallID(ctx, installID)
	if err != nil {
		t.Fatalf("ListByInstallID (health read path): %v", err)
	}
	if len(states) != 1 {
		t.Fatalf("ListByInstallID returned %d rows, want 1", len(states))
	}
	if states[0].Cursor != "page-token-2" {
		t.Fatalf("ListByInstallID cursor mismatch: got %q want %q", states[0].Cursor, "page-token-2")
	}
}