//go:build integration

// Package bootstrap — autonumber_seed_integration_test.go exercises
// SeedAutoNumberCounters + VerifyAutoNumberInvariants against a real
// TiDB/MySQL-compatible DB. Skips unless TIDB_TEST_DSN is set (project
// memory: 4Qbn3F3nyaU9DLk.root — new-prod-essential branch).
//
// These tests run under the `integration` build tag to keep CI's fast
// path from paying their network cost. Invoke with:
//
//	TIDB_TEST_DSN=... go test -tags integration -count=1 \
//	    ./internal/bootstrap/... -run TestSeedAutoNumberCounters_
package bootstrap

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

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

	"github.com/nexuscrm/backend/internal/features/automation/autonumber"
)

// openSeedIntegrationDB mirrors the autonumber engine test's DSN dance.
func openSeedIntegrationDB(t *testing.T) *sql.DB {
	t.Helper()
	dsn := os.Getenv("TIDB_TEST_DSN")
	if dsn == "" {
		t.Skip("TIDB_TEST_DSN not set; skipping autonumber seed integration test")
	}
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		t.Fatalf("sql.Open: %v", err)
	}
	if err := db.Ping(); err != nil {
		_ = db.Close()
		t.Skipf("TIDB_TEST_DSN set but DB unreachable (%v); skipping", err)
	}
	db.SetMaxOpenConns(10)
	t.Cleanup(func() { _ = db.Close() })
	return db
}

// seedIntegrationNamespace returns a unique suffix per test run so
// multiple integration runs don't race on the same (field_id,
// scope_key) counter rows. The suffix is applied to the synthetic
// field Id we insert into `_System_Field`.
func seedIntegrationNamespace(t *testing.T) string {
	t.Helper()
	return fmt.Sprintf("seedtest_%d_%s",
		time.Now().UnixNano(),
		strings.ReplaceAll(t.Name(), "/", "_"),
	)
}

// ensureCounterTableForSeedTest matches the defensive DDL pattern used
// by the engine's reserve_integration_test — the branch DB may not
// have the v2 counter table yet. Also runs ReconcileTableColumns so
// _System_Field has the v2 autonumber columns (Wave 1 Lane B DDL) on
// first contact with a branch DB that hasn't been bootstrapped.
func ensureCounterTableForSeedTest(t *testing.T, db *sql.DB) {
	t.Helper()

	// Reconcile schema — brings _System_Field's v2 columns into
	// existence if the branch DB hasn't been bootstrapped yet. The
	// same path is exercised by Wave 1 Lane B's integration test
	// (schema_autonumber_v2_test.go:48).
	defs := GetSystemTableDefinitions()
	if err := ReconcileTableColumns(db, defs); err != nil {
		t.Fatalf("ReconcileTableColumns: %v", err)
	}

	// _System_AutoNumberCounter (same DDL as engine test). Kept
	// defensively for DBs where ReconcileTableColumns does not
	// create new tables (only columns).
	const counterDDL = `CREATE TABLE IF NOT EXISTS _System_AutoNumberCounter (
	  Id               VARCHAR(255) NOT NULL PRIMARY KEY,
	  FieldId          VARCHAR(255) NOT NULL,
	  ScopeKey         VARCHAR(50)  NOT NULL DEFAULT '',
	  NextValue        BIGINT       NOT NULL DEFAULT 1,
	  CreatedDate      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
	  LastModifiedDate DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
	  UNIQUE KEY uk_field_scope (FieldId, ScopeKey),
	  KEY idx_field (FieldId)
	)`
	if _, err := db.Exec(counterDDL); err != nil {
		t.Fatalf("ensureCounterTable: %v", err)
	}

	// Legacy _System_AutoNumber (integration test populates rows in
	// it so readLegacyAutoNumberCurrent has data). Skip-if-exists.
	const legacyDDL = `CREATE TABLE IF NOT EXISTS _System_AutoNumber (
	  Id               VARCHAR(255) NOT NULL PRIMARY KEY,
	  ObjectApiName    VARCHAR(255) NOT NULL,
	  FieldApiName     VARCHAR(255) NOT NULL,
	  DisplayFormat    VARCHAR(255) NOT NULL,
	  StartingNumber   INT          NOT NULL DEFAULT 1,
	  CurrentNumber    INT          NOT NULL DEFAULT 0,
	  CreatedDate      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
	  LastModifiedDate DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
	  UNIQUE KEY uk_obj_field (ObjectApiName, FieldApiName)
	)`
	if _, err := db.Exec(legacyDDL); err != nil {
		t.Fatalf("ensure _System_AutoNumber: %v", err)
	}
}

// insertSyntheticField inserts a row into _System_Field that the seed
// runner can resolve. Returns the synthetic fieldID. Fills the
// NOT-NULL-without-default columns (Description) with sentinel values
// so TiDB's strict mode accepts the row.
func insertSyntheticField(
	t *testing.T, db *sql.DB,
	objectID, objectApiName, fieldName, format string,
) string {
	t.Helper()
	fieldID := fmt.Sprintf("fld_%s_%d", strings.ReplaceAll(objectApiName, "_", ""), time.Now().UnixNano())
	// Type='autonumber' aligns with existing legacy convention; all
	// other columns carry test-sentinel values. Description is
	// nullable on _System_Field but tabletop caution keeps it ''.
	_, err := db.Exec(
		`INSERT INTO `+"`_System_Field`"+` (
			Id, ObjectId, ApiName, Label, Type,
			AutoNumberFormat, AutoNumberScope, AutoNumberStart,
			Description, CreatedDate, LastModifiedDate
		 ) VALUES (?, ?, ?, ?, 'autonumber', ?, 'global', 1, '', NOW(), NOW())`,
		fieldID, objectID, fieldName, fieldName, format,
	)
	if err != nil {
		t.Fatalf("insert synthetic field: %v", err)
	}
	t.Cleanup(func() {
		_, _ = db.Exec("DELETE FROM `_System_Field` WHERE Id = ?", fieldID)
	})
	return fieldID
}

// ensureSyntheticObject inserts a _System_Object row and returns its
// Id. The synthetic name embeds the test's namespace so parallel test
// runs don't collide. Fills NOT NULL columns without defaults
// (Description, Label) so TiDB strict mode accepts the INSERT.
func ensureSyntheticObject(
	t *testing.T, db *sql.DB,
	apiName string,
) string {
	t.Helper()
	objectID := "obj_" + apiName + "_" + fmt.Sprintf("%d", time.Now().UnixNano())
	_, err := db.Exec(
		`INSERT INTO `+"`_System_Object`"+` (
			Id, ApiName, TableType, Label, Description,
			IsCustom, Searchable, SharingModel, IsDeleted, IsCustomized,
			CreatedDate, LastModifiedDate
		 ) VALUES (?, ?, 'custom_object', ?, '', 1, 1, 'Private', 0, 0, NOW(), NOW())
		 ON DUPLICATE KEY UPDATE LastModifiedDate = VALUES(LastModifiedDate)`,
		objectID, apiName, apiName,
	)
	if err != nil {
		t.Fatalf("insert synthetic object: %v", err)
	}
	t.Cleanup(func() {
		_, _ = db.Exec("DELETE FROM `_System_Object` WHERE Id = ?", objectID)
	})
	return objectID
}

// ensureSyntheticPhysicalTable creates `<apiName>` with an Id PK and
// a <fieldName> VARCHAR column so scanLiveMaxCounter has something to
// iterate. Returns a cleanup that drops the table.
func ensureSyntheticPhysicalTable(
	t *testing.T, db *sql.DB,
	apiName, fieldName string,
) {
	t.Helper()
	ddl := fmt.Sprintf(
		"CREATE TABLE IF NOT EXISTS `%s` (Id VARCHAR(255) NOT NULL PRIMARY KEY, `%s` VARCHAR(255) NULL)",
		apiName, fieldName,
	)
	if _, err := db.Exec(ddl); err != nil {
		t.Fatalf("create physical table %s: %v", apiName, err)
	}
	t.Cleanup(func() {
		_, _ = db.Exec(fmt.Sprintf("DROP TABLE IF EXISTS `%s`", apiName))
	})
}

// clearCounterForField — test helper to wipe seed output for a
// given fieldID so a re-run starts from a known state.
func clearCounterForField(t *testing.T, db *sql.DB, fieldID string) {
	t.Helper()
	if _, err := db.Exec(
		"DELETE FROM `_System_AutoNumberCounter` WHERE FieldId = ?",
		fieldID,
	); err != nil {
		t.Fatalf("clear counter: %v", err)
	}
}

// readSeededNext reads the seeded NextValue for (fieldID, ''). Fails
// if the row is missing.
func readSeededNext(t *testing.T, db *sql.DB, fieldID string) int64 {
	t.Helper()
	var n int64
	err := db.QueryRow(
		"SELECT NextValue FROM `_System_AutoNumberCounter` WHERE FieldId = ? AND ScopeKey = ''",
		fieldID,
	).Scan(&n)
	if err != nil {
		t.Fatalf("readSeededNext: %v", err)
	}
	return n
}

// countCounterRows returns the total row count in
// `_System_AutoNumberCounter` — used by the idempotency test.
func countCounterRows(t *testing.T, db *sql.DB) int64 {
	t.Helper()
	var n int64
	err := db.QueryRow("SELECT COUNT(*) FROM `_System_AutoNumberCounter`").Scan(&n)
	if err != nil {
		t.Fatalf("countCounterRows: %v", err)
	}
	return n
}

// --- Idempotency (PRD §8.4, Lane C2 task spec Step 3) ------------

// TestSeedAutoNumberCounters_Idempotent_3x runs the seed 3 times
// against the same DB + synthetic field. Row counts must be identical
// across runs and NextValue must not decrease.
func TestSeedAutoNumberCounters_Idempotent_3x(t *testing.T) {
	db := openSeedIntegrationDB(t)
	ensureCounterTableForSeedTest(t, db)

	apiName := fmt.Sprintf("SeedTestObj%d", time.Now().UnixNano()%1_000_000_000)
	fieldName := "CounterField__c"
	objectID := ensureSyntheticObject(t, db, apiName)
	ensureSyntheticPhysicalTable(t, db, apiName, fieldName)
	fieldID := insertSyntheticField(t, db, objectID, apiName, fieldName, "{00000}")

	// Seed live data: two rows with counters 7 and 42.
	for _, v := range []string{"00007", "00042"} {
		_, err := db.Exec(
			fmt.Sprintf("INSERT INTO `%s` (Id, `%s`) VALUES (?, ?)", apiName, fieldName),
			fmt.Sprintf("row_%s", v), v,
		)
		if err != nil {
			t.Fatalf("insert live row %s: %v", v, err)
		}
	}

	clearCounterForField(t, db, fieldID)

	seeds := []AutoNumberFormatSeed{{
		ObjectName:   apiName,
		FieldApiName: fieldName,
		Format:       "{00000}",
		Scope:        autonumber.ScopeGlobal,
		Start:        1,
		LegacySource: LegacySourceNone,
	}}
	scopeSrc := BuildScopeSourceMapFromSeeds(seeds)

	var (
		rowCountAfterRun [3]int64
		nextAfterRun     [3]int64
	)
	for i := 0; i < 3; i++ {
		if err := SeedAutoNumberCounters(context.Background(), db, seeds, scopeSrc); err != nil {
			t.Fatalf("run %d: SeedAutoNumberCounters: %v", i, err)
		}
		rowCountAfterRun[i] = countCounterRows(t, db)
		nextAfterRun[i] = readSeededNext(t, db, fieldID)
	}

	// live_max = 42 → next = 43. All three runs must agree.
	if nextAfterRun[0] != 43 {
		t.Errorf("run 0: got NextValue=%d, want 43", nextAfterRun[0])
	}
	for i := 1; i < 3; i++ {
		if rowCountAfterRun[i] != rowCountAfterRun[0] {
			t.Errorf("run %d: row count drifted (first=%d, this=%d)",
				i, rowCountAfterRun[0], rowCountAfterRun[i])
		}
		if nextAfterRun[i] < nextAfterRun[i-1] {
			t.Errorf("run %d: NextValue regressed (prev=%d, this=%d)",
				i, nextAfterRun[i-1], nextAfterRun[i])
		}
	}
}

// --- Scope-aware GREATEST (F8 — PRD §10.2 test 25/26) -------------

// TestSeedAutoNumberCounters_ScopeAwareGreatest exercises the test
// 25/26 scenario from PRD §10.2 + fixpack F8: legacy
// `_System_AutoNumber.CurrentNumber` > live_today_max. Seed must pick
// the legacy value, not the live max. Second run must not regress.
func TestSeedAutoNumberCounters_ScopeAwareGreatest(t *testing.T) {
	db := openSeedIntegrationDB(t)
	ensureCounterTableForSeedTest(t, db)

	apiName := fmt.Sprintf("SeedGreatest%d", time.Now().UnixNano()%1_000_000_000)
	fieldName := "CounterField__c"
	objectID := ensureSyntheticObject(t, db, apiName)
	ensureSyntheticPhysicalTable(t, db, apiName, fieldName)
	fieldID := insertSyntheticField(t, db, objectID, apiName, fieldName, "{00000}")

	// live_today_max = 003968 (simulates rollover — today's data
	// capped at ~4K). Legacy says we already hit 4100.
	_, err := db.Exec(
		fmt.Sprintf("INSERT INTO `%s` (Id, `%s`) VALUES (?, ?), (?, ?)", apiName, fieldName),
		"row_low", "00100",
		"row_hi", "03968",
	)
	if err != nil {
		t.Fatalf("insert live rows: %v", err)
	}

	// Legacy state: 4100 via _System_AutoNumber.
	_, err = db.Exec(
		`INSERT INTO `+"`_System_AutoNumber`"+` (Id, ObjectApiName, FieldApiName, DisplayFormat, CurrentNumber, CreatedDate, LastModifiedDate)
		 VALUES (?, ?, ?, ?, 4100, NOW(), NOW())
		 ON DUPLICATE KEY UPDATE CurrentNumber = 4100`,
		fmt.Sprintf("legacy_%s_%s", apiName, fieldName),
		apiName, fieldName, "{00000}",
	)
	if err != nil {
		t.Fatalf("seed legacy _System_AutoNumber: %v", err)
	}
	t.Cleanup(func() {
		_, _ = db.Exec(
			"DELETE FROM `_System_AutoNumber` WHERE ObjectApiName = ? AND FieldApiName = ?",
			apiName, fieldName,
		)
	})

	clearCounterForField(t, db, fieldID)

	seeds := []AutoNumberFormatSeed{{
		ObjectName:   apiName,
		FieldApiName: fieldName,
		Format:       "{00000}",
		Scope:        autonumber.ScopeGlobal,
		Start:        1,
		LegacySource: LegacySourceAutoNumber,
	}}
	scopeSrc := BuildScopeSourceMapFromSeeds(seeds)

	if err := SeedAutoNumberCounters(context.Background(), db, seeds, scopeSrc); err != nil {
		t.Fatalf("SeedAutoNumberCounters (run 1): %v", err)
	}
	// GREATEST(live=3968+1, legacy=4100+1, start=1) = 4101
	got := readSeededNext(t, db, fieldID)
	const want = 4101
	if got != want {
		t.Errorf("scope-aware GREATEST: got NextValue=%d, want %d", got, want)
	}

	// Second run — must not regress.
	if err := SeedAutoNumberCounters(context.Background(), db, seeds, scopeSrc); err != nil {
		t.Fatalf("SeedAutoNumberCounters (run 2): %v", err)
	}
	got2 := readSeededNext(t, db, fieldID)
	if got2 < got {
		t.Errorf("second run regressed: got=%d, first=%d", got2, got)
	}
}

// --- Partial-fail recovery (F14, Lane C2 task spec Step 3) ------

// TestSeedAutoNumberCounters_PartialFailRecovery verifies fixpack F14:
// an unparseable live row aborts the seed; once the offending row is
// removed, the seed completes; V1-V6 verify returns nil after recovery.
func TestSeedAutoNumberCounters_PartialFailRecovery(t *testing.T) {
	db := openSeedIntegrationDB(t)
	ensureCounterTableForSeedTest(t, db)

	apiName := fmt.Sprintf("SeedPartial%d", time.Now().UnixNano()%1_000_000_000)
	fieldName := "Account_ID__c"
	objectID := ensureSyntheticObject(t, db, apiName)
	ensureSyntheticPhysicalTable(t, db, apiName, fieldName)
	fieldID := insertSyntheticField(t, db, objectID, apiName, fieldName,
		"IBG-B-{YYYY}{MM}{DD}-{000000}")

	// 2 valid rows + 1 unparseable row in the middle.
	for id, v := range map[string]string{
		"row_valid_1": "IBG-B-20260418-000001",
		"row_bad":     "IBG-B-2021-custom-override",
		"row_valid_2": "IBG-B-20260418-000042",
	} {
		_, err := db.Exec(
			fmt.Sprintf("INSERT INTO `%s` (Id, `%s`) VALUES (?, ?)", apiName, fieldName),
			id, v,
		)
		if err != nil {
			t.Fatalf("insert %s=%q: %v", id, v, err)
		}
	}

	clearCounterForField(t, db, fieldID)

	seeds := []AutoNumberFormatSeed{{
		ObjectName:   apiName,
		FieldApiName: fieldName,
		Format:       "IBG-B-{YYYY}{MM}{DD}-{000000}",
		Scope:        autonumber.ScopeGlobal,
		Start:        1,
		LegacySource: LegacySourceNone,
	}}
	scopeSrc := BuildScopeSourceMapFromSeeds(seeds)

	// Run 1: MUST abort (fail-closed) naming the bad row.
	err := SeedAutoNumberCounters(context.Background(), db, seeds, scopeSrc)
	if err == nil {
		t.Fatal("expected fail-closed error on unparseable row, got nil")
	}
	if !strings.Contains(err.Error(), "row_bad") {
		t.Errorf("error missing bad row id: %v", err)
	}
	// Seed should NOT have written any counter row (flush happens
	// only on batch size reached or completion; we have 1 field so
	// no batch flushed yet, but defensive check:
	var n int64
	_ = db.QueryRow("SELECT COUNT(*) FROM `_System_AutoNumberCounter` WHERE FieldId = ?", fieldID).
		Scan(&n)
	if n != 0 {
		t.Errorf("after fail-closed abort, expected 0 counter rows for %s, got %d", fieldID, n)
	}

	// Remove the bad row.
	_, err = db.Exec(
		fmt.Sprintf("DELETE FROM `%s` WHERE Id = 'row_bad'", apiName),
	)
	if err != nil {
		t.Fatalf("delete bad row: %v", err)
	}

	// Run 2: MUST succeed. live_max=42 → NextValue=43.
	if err := SeedAutoNumberCounters(context.Background(), db, seeds, scopeSrc); err != nil {
		t.Fatalf("SeedAutoNumberCounters (post-recovery): %v", err)
	}
	got := readSeededNext(t, db, fieldID)
	if got != 43 {
		t.Errorf("post-recovery NextValue: got=%d, want=43", got)
	}

	// V1-V6 verify must report no violations against this field
	// (other autonumber fields on this DB may exist; we only
	// assert our synthetic field is clean). To avoid flakes from
	// preexisting non-compliant data, we run verify and only
	// inspect the returned error for our field's signature.
	verifyErr := VerifyAutoNumberInvariants(context.Background(), db)
	if verifyErr != nil {
		// Pass only if the verify failure does NOT mention our
		// synthetic field. The branch DB may have pre-existing
		// autonumber fields with their own V1-V6 state.
		if strings.Contains(verifyErr.Error(), apiName) ||
			strings.Contains(verifyErr.Error(), fieldID) {
			t.Errorf("verify reported violation for our synthetic field: %v", verifyErr)
		}
	}
}

// TestVerifyAutoNumberInvariants_V3_FailsOnMissingCounter exercises
// the V3 case — an autonumber field exists but no counter row was
// seeded.
func TestVerifyAutoNumberInvariants_V3_FailsOnMissingCounter(t *testing.T) {
	db := openSeedIntegrationDB(t)
	ensureCounterTableForSeedTest(t, db)

	apiName := fmt.Sprintf("V3Missing%d", time.Now().UnixNano()%1_000_000_000)
	fieldName := "CounterField__c"
	objectID := ensureSyntheticObject(t, db, apiName)
	// Intentionally DO NOT create a physical table and DO NOT seed
	// any counter row. Insert only the _System_Field row.
	fieldID := insertSyntheticField(t, db, objectID, apiName, fieldName, "{00000}")

	err := VerifyAutoNumberInvariants(context.Background(), db)
	if err == nil {
		t.Fatal("expected V3 violation for missing counter, got nil")
	}
	if !strings.Contains(err.Error(), fieldID) {
		t.Errorf("V3 error missing fieldID: %v", err)
	}
	if !strings.Contains(err.Error(), "V3") {
		t.Errorf("V3 error missing V3 marker: %v", err)
	}
}