// Integration test for DBauditStore against a live TiDB.
// Skipped when TIDB_HOST / TIDB_PORT (default 127.0.0.1:4001) is unreachable,
// unless NEXUSCRM_TIDB_REQUIRED=1 makes availability a fail-closed CI contract.
//
// Regression coverage for Codex Review HIGH finding (PR #265 round-3): a
// row-count-cap retention pass must remove exactly the requested number of
// rows even when many rows share the same CreatedDate second. The earlier
// implementation matched on CreatedDate and therefore deleted every row in the
// oldest second, potentially wiping the entire table on a surplus of 1.
package automation

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

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

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 {
		automationTiDBUnavailable(t, "open admin TiDB: %v", err)
	}
	if err := adminDB.Ping(); err != nil {
		_ = adminDB.Close()
		automationTiDBUnavailable(t, "TiDB not reachable at %s:%s: %v", host, port, err)
	}

	dbName := fmt.Sprintf("nexuscrm_audit_store_%d", time.Now().UnixNano())
	if _, err := adminDB.Exec("CREATE DATABASE `" + dbName + "`"); err != nil {
		_ = adminDB.Close()
		automationTiDBUnavailable(t, "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
}

func automationTiDBUnavailable(t *testing.T, format string, args ...any) {
	t.Helper()
	if os.Getenv("NEXUSCRM_TIDB_REQUIRED") == "1" {
		t.Fatalf("required TiDB integration environment unavailable: "+format, args...)
	}
	t.Skipf("skipping integration test: "+format, args...)
}

func createAuditTable(t *testing.T, db *sql.DB) {
	t.Helper()
	_, err := db.Exec(`CREATE TABLE _System_FlowAuditLog (
		Id VARCHAR(64) PRIMARY KEY,
		FlowExecutionId VARCHAR(64),
		FlowDefinitionId VARCHAR(64),
		FlowVersionId VARCHAR(64),
		ElementId VARCHAR(64),
		ElementType VARCHAR(64),
		Status VARCHAR(32),
		DurationMs BIGINT,
		InputJSON LONGTEXT,
		OutputJSON LONGTEXT,
		ErrorPayload LONGTEXT,
		ObjectMetadataId VARCHAR(64),
		RecordId VARCHAR(64),
		CreatedDate DATETIME
	)`)
	if err != nil {
		t.Fatalf("create _System_FlowAuditLog: %v", err)
	}
}

// TestDBauditStore_DeleteOldest_SameSecondCollision is the regression test for
// the Codex Review HIGH finding on PR #265. With CreatedDate at second precision,
// many rows can share the same timestamp; DeleteOldest(n) must still remove
// exactly n rows (not every row in the oldest second).
func TestDBauditStore_DeleteOldest_SameSecondCollision(t *testing.T) {
	db, cleanup := openTiDB(t)
	defer cleanup()
	createAuditTable(t, db)

	store := NewDBAuditStore(db)
	ctx := context.Background()

	// Five rows, all in the same wall-clock second.
	sameSec := time.Date(2026, 5, 6, 10, 0, 0, 0, time.Local)
	rows := []Row{
		{Id: "a", CreatedDate: sameSec},
		{Id: "b", CreatedDate: sameSec},
		{Id: "c", CreatedDate: sameSec},
		{Id: "d", CreatedDate: sameSec},
		{Id: "e", CreatedDate: sameSec},
	}
	if err := store.InsertRows(ctx, rows); err != nil {
		t.Fatalf("InsertRows: %v", err)
	}
	if c, err := store.CountRows(ctx); err != nil || c != 5 {
		t.Fatalf("seed count = %d err=%v, want 5", c, err)
	}

	deleted, err := store.DeleteOldest(ctx, 1)
	if err != nil {
		t.Fatalf("DeleteOldest(1): %v", err)
	}
	if deleted != 1 {
		t.Fatalf("DeleteOldest(1) reported %d deleted, want 1", deleted)
	}
	after, err := store.CountRows(ctx)
	if err != nil {
		t.Fatalf("CountRows after DeleteOldest(1): %v", err)
	}
	if after != 4 {
		t.Fatalf("table count after DeleteOldest(1) = %d, want 4 (would be 0 with the timestamp-IN bug)", after)
	}

	// DeleteOldest(0) is a no-op.
	if d, err := store.DeleteOldest(ctx, 0); err != nil || d != 0 {
		t.Fatalf("DeleteOldest(0) = %d err=%v, want 0/nil", d, err)
	}

	// DeleteOldest(N > remaining) caps at remaining and clears the table.
	if d, err := store.DeleteOldest(ctx, 100); err != nil || d != 4 {
		t.Fatalf("DeleteOldest(100) = %d err=%v, want 4/nil", d, err)
	}
	if c, err := store.CountRows(ctx); err != nil || c != 0 {
		t.Fatalf("final count = %d err=%v, want 0", c, err)
	}
}

// TestDBauditStore_DeleteOldest_OrdersByCreatedDate verifies the oldest-first
// ordering is honored when timestamps differ.
func TestDBauditStore_DeleteOldest_OrdersByCreatedDate(t *testing.T) {
	db, cleanup := openTiDB(t)
	defer cleanup()
	createAuditTable(t, db)

	store := NewDBAuditStore(db)
	ctx := context.Background()

	base := time.Date(2026, 5, 6, 10, 0, 0, 0, time.Local)
	rows := []Row{
		{Id: "old1", CreatedDate: base},
		{Id: "old2", CreatedDate: base.Add(1 * time.Second)},
		{Id: "new1", CreatedDate: base.Add(10 * time.Second)},
		{Id: "new2", CreatedDate: base.Add(20 * time.Second)},
	}
	if err := store.InsertRows(ctx, rows); err != nil {
		t.Fatalf("InsertRows: %v", err)
	}

	if _, err := store.DeleteOldest(ctx, 2); err != nil {
		t.Fatalf("DeleteOldest(2): %v", err)
	}

	remaining, err := store.QueryRows(ctx, QueryFilter{Limit: 100})
	if err != nil {
		t.Fatalf("QueryRows: %v", err)
	}
	if len(remaining) != 2 {
		t.Fatalf("remaining count = %d, want 2", len(remaining))
	}
	for _, r := range remaining {
		if r.Id == "old1" || r.Id == "old2" {
			t.Fatalf("oldest row %q survived DeleteOldest(2)", r.Id)
		}
	}
}