// Integration test for DBApprovalActionLogStore against a live TiDB.
// Skipped when TIDB_HOST / TIDB_PORT (default 127.0.0.1:4001) is unreachable.
//
// # Why this exists on top of the unit tests
//
// The unit tests drive a recordingExecer, which accepts any SQL and any bind
// arguments. A double that is more permissive than the wire proves the code
// works against the double, not against the database — so the INSERT's column
// names, column count, and NULL handling are unverified by them.
//
// The schema here is generated FROM the schema catalog rather than hand-copied
// into the test. A hand-copied CREATE TABLE is a second source of truth: it
// would keep passing after someone changes the catalog, which is precisely the
// drift that makes an integration test worthless.
package automation

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"os"
	"path/filepath"
	"strings"
	"testing"

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

const approvalActionLogCatalogPath = "../nexuscrm-kernel/pkg/schemacatalog/system_tables.json"

type catalogTableDef struct {
	TableName string `json:"tableName"`
	Columns   []struct {
		Name       string `json:"name"`
		Type       string `json:"type"`
		Nullable   *bool  `json:"nullable"`
		PrimaryKey bool   `json:"primaryKey"`
		Default    string `json:"default"`
	} `json:"columns"`
}

// createTableFromCatalog renders CREATE TABLE for the named table straight
// from the catalog declaration.
func createTableFromCatalog(t *testing.T, table string) string {
	t.Helper()
	path, err := filepath.Abs(approvalActionLogCatalogPath)
	if err != nil {
		t.Fatalf("resolve catalog path: %v", err)
	}
	raw, err := os.ReadFile(path)
	if err != nil {
		t.Skipf("schema catalog not readable at %s: %v", path, err)
	}
	var defs []catalogTableDef
	if err := json.Unmarshal(raw, &defs); err != nil {
		t.Fatalf("parse schema catalog: %v", err)
	}
	for _, d := range defs {
		if d.TableName != table {
			continue
		}
		cols := make([]string, 0, len(d.Columns))
		pk := ""
		for _, c := range d.Columns {
			piece := fmt.Sprintf("`%s` %s", c.Name, c.Type)
			if c.Nullable != nil && !*c.Nullable {
				piece += " NOT NULL"
			}
			if c.Default != "" {
				piece += " DEFAULT " + c.Default
			}
			cols = append(cols, piece)
			if c.PrimaryKey {
				pk = c.Name
			}
		}
		if pk != "" {
			cols = append(cols, fmt.Sprintf("PRIMARY KEY (`%s`)", pk))
		}
		return fmt.Sprintf("CREATE TABLE `%s` (%s)", table, strings.Join(cols, ", "))
	}
	t.Fatalf("schema catalog does not declare %s", table)
	return ""
}

// TestDBApprovalActionLogStore_WritesAgainstDeclaredSchema proves the INSERT
// matches the catalog declaration and that the three MaterializedActionCount
// states survive a round trip as NULL / 0 / n.
func TestDBApprovalActionLogStore_WritesAgainstDeclaredSchema(t *testing.T) {
	db, cleanup := openTiDB(t)
	defer cleanup()

	table := "_System_ApprovalActionLog"
	if _, err := db.Exec("DROP TABLE IF EXISTS `" + table + "`"); err != nil {
		t.Fatalf("drop table: %v", err)
	}
	ddl := createTableFromCatalog(t, table)
	if _, err := db.Exec(ddl); err != nil {
		t.Fatalf("create table from catalog (%s): %v", ddl, err)
	}
	defer func() { _, _ = db.Exec("DROP TABLE IF EXISTS `" + table + "`") }()

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

	zero := 0
	seven := 7
	cases := []struct {
		id    string
		row   ApprovalActionLogRow
		count *int
	}{
		{"committed", ApprovalActionLogRow{
			ActionEvent: ApprovalActionRequestApprove, WorkItemId: "wi-1",
			ProcessInstanceId: "pi-1", TargetObjectId: "tgt-1",
			ActorId: "user-1", RequestedStatus: "Approved",
			Outcome: ApprovalActionOutcomeCommitted, DurationMs: 42,
		}, &seven},
		{"rolled-back", ApprovalActionLogRow{
			ActionEvent: ApprovalActionRequestApprove, WorkItemId: "wi-2",
			Outcome:      ApprovalActionOutcomeRolledBack,
			FailureClass: ApprovalActionFailureClassLockWait,
			FailureCause: "Error 1205: Lock wait timeout exceeded",
			DurationMs:   50123,
		}, nil},
		{"resolved-none", ApprovalActionLogRow{
			ActionEvent: ApprovalActionRequestRecall, WorkItemId: "wi-3",
			Outcome: ApprovalActionOutcomeCommitted,
		}, &zero},
	}

	for _, tc := range cases {
		row := tc.row
		row.Id = tc.id
		row.MaterializedActionCount = tc.count
		if err := store.RecordApprovalAction(ctx, row); err != nil {
			t.Fatalf("%s: RecordApprovalAction against the declared schema: %v", tc.id, err)
		}
	}

	for _, tc := range cases {
		var (
			outcome   string
			count     sql.NullInt64
			cause     sql.NullString
			class     sql.NullString
			instance  sql.NullString
			durations int
		)
		err := db.QueryRow(
			"SELECT Outcome, MaterializedActionCount, FailureCause, FailureClass, ProcessInstanceId, DurationMs FROM `"+
				table+"` WHERE Id = ?", tc.id).
			Scan(&outcome, &count, &cause, &class, &instance, &durations)
		if err != nil {
			t.Fatalf("%s: read back: %v", tc.id, err)
		}
		if outcome != tc.row.Outcome {
			t.Errorf("%s: Outcome = %q; want %q", tc.id, outcome, tc.row.Outcome)
		}
		if tc.count == nil {
			if count.Valid {
				t.Errorf("%s: MaterializedActionCount = %d; want NULL — 'not reached' must not collapse into 0",
					tc.id, count.Int64)
			}
		} else {
			if !count.Valid || count.Int64 != int64(*tc.count) {
				t.Errorf("%s: MaterializedActionCount = %v; want %d", tc.id, count, *tc.count)
			}
		}
		if tc.row.FailureCause == "" && cause.Valid {
			t.Errorf("%s: FailureCause stored as %q; want NULL", tc.id, cause.String)
		}
		if tc.row.FailureCause != "" && cause.String != tc.row.FailureCause {
			t.Errorf("%s: FailureCause = %q; want %q", tc.id, cause.String, tc.row.FailureCause)
		}
		if tc.row.ProcessInstanceId == "" && instance.Valid {
			t.Errorf("%s: ProcessInstanceId stored as %q; want NULL", tc.id, instance.String)
		}
		if durations != tc.row.DurationMs {
			t.Errorf("%s: DurationMs = %d; want %d", tc.id, durations, tc.row.DurationMs)
		}
		_ = class
	}
}

// TestDBApprovalActionLogStore_TruncatedCauseFitsTheColumn proves the byte cap
// is the column's real limit and not merely a constant that happens to agree
// with it. A cause longer than the column would otherwise fail the INSERT and
// lose the row at the moment it matters most.
func TestDBApprovalActionLogStore_TruncatedCauseFitsTheColumn(t *testing.T) {
	db, cleanup := openTiDB(t)
	defer cleanup()

	table := "_System_ApprovalActionLog"
	if _, err := db.Exec("DROP TABLE IF EXISTS `" + table + "`"); err != nil {
		t.Fatalf("drop table: %v", err)
	}
	if _, err := db.Exec(createTableFromCatalog(t, table)); err != nil {
		t.Fatalf("create table: %v", err)
	}
	defer func() { _, _ = db.Exec("DROP TABLE IF EXISTS `" + table + "`") }()

	store := NewDBApprovalActionLogStore(db)
	err := store.RecordApprovalAction(context.Background(), ApprovalActionLogRow{
		Id:           "oversized",
		ActionEvent:  ApprovalActionRequestApprove,
		Outcome:      ApprovalActionOutcomeRolledBack,
		FailureClass: ApprovalActionFailureClassInternal,
		FailureCause: strings.Repeat("e", maxApprovalActionFailureCauseBytes*4),
	})
	if err != nil {
		t.Fatalf("oversized cause was not truncated to fit the declared column: %v", err)
	}
}

// TestDBApprovalActionLogStore_HostileValuesStillProduceARow is the test whose
// absence let two real defects through.
//
// The unit tests drive a recordingExecer that accepts any bind value, so they
// can only prove the store SHORTENS a string — never that the database accepts
// it. Against a real utf8mb4 column both of these reject the entire INSERT:
//
//	oversized value  -> Error 1406 "Data too long for column"
//	invalid UTF-8    -> Error 1366 "Incorrect string value"
//
// Either one destroys the outcome row, which is the exact failure this table
// exists to prevent. An earlier revision deliberately preserved invalid bytes
// "rather than the silence" and would have lost every such row.
func TestDBApprovalActionLogStore_HostileValuesStillProduceARow(t *testing.T) {
	db, cleanup := openTiDB(t)
	defer cleanup()

	table := "_System_ApprovalActionLog"
	if _, err := db.Exec("DROP TABLE IF EXISTS `" + table + "`"); err != nil {
		t.Fatalf("drop: %v", err)
	}
	if _, err := db.Exec(createTableFromCatalog(t, table)); err != nil {
		t.Fatalf("create: %v", err)
	}
	defer func() { _, _ = db.Exec("DROP TABLE IF EXISTS `" + table + "`") }()

	store := NewDBApprovalActionLogStore(db)
	err := store.RecordApprovalAction(context.Background(), ApprovalActionLogRow{
		// The primary key is hostile too: it is the one bound string the first
		// pass at this class fix forgot, and TiDB rejects an oversized Id
		// exactly as it rejects an oversized RequestId.
		Id:           strings.Repeat("h", 400),
		ActionEvent:  ApprovalActionRequestApprove,
		WorkItemId:   strings.Repeat("w", 400),
		Outcome:      ApprovalActionOutcomeRolledBack,
		FailureClass: ApprovalActionFailureClassInternal,
		// Invalid UTF-8 and oversized at once, as a driver error carrying a raw
		// payload fragment would be.
		FailureCause: strings.Repeat("\x80\xff", 3000),
		// Caller-supplied headers: middleware.RequestID applies no length bound.
		RequestId: strings.Repeat("q", 400),
		TraceId:   strings.Repeat("z", 300),
	})
	if err != nil {
		t.Fatalf("hostile values rejected the outcome row: %v", err)
	}

	var cause, req, trace sql.NullString
	var storedID string
	if err := db.QueryRow(
		"SELECT Id, FailureCause, RequestId, TraceId FROM `"+table+"`",
	).Scan(&storedID, &cause, &req, &trace); err != nil {
		t.Fatalf("the row is absent after a successful write: %v", err)
	}
	if len(storedID) > 255 {
		t.Errorf("stored Id is %d bytes; the column accepts 255", len(storedID))
	}
	if !cause.Valid || cause.String == "" {
		t.Error("FailureCause was dropped entirely; a sanitised cause is still evidence")
	}
	// Unusable identifiers must be ABSENT, not shortened: a truncated
	// correlation id can join to a different request that shares its prefix.
	if req.Valid {
		t.Errorf("oversized RequestId stored as %q; it must be NULL", req.String)
	}
	if trace.Valid {
		t.Errorf("oversized TraceId stored as %q; it must be NULL", trace.String)
	}
}

// TestDBApprovalActionLogStore_EveryValueLandsInItsOwnColumn catches
// column/bind-argument misalignment, which no other test here can see.
//
// Every existing fixture leaves most identifier columns NULL, so swapping two
// same-typed columns in the INSERT list without swapping their arguments is
// invisible: NULL lands on NULL either way. The consequence would be silent
// corruption of a forensic row — the actor recorded as the request id, the
// target as the work item — with every test still green.
//
// Giving each column a distinct marker and reading them back by name is the
// only assertion that distinguishes those cases.
func TestDBApprovalActionLogStore_EveryValueLandsInItsOwnColumn(t *testing.T) {
	db, cleanup := openTiDB(t)
	defer cleanup()

	table := "_System_ApprovalActionLog"
	if _, err := db.Exec("DROP TABLE IF EXISTS `" + table + "`"); err != nil {
		t.Fatalf("drop: %v", err)
	}
	if _, err := db.Exec(createTableFromCatalog(t, table)); err != nil {
		t.Fatalf("create: %v", err)
	}
	defer func() { _, _ = db.Exec("DROP TABLE IF EXISTS `" + table + "`") }()

	count := 7
	row := ApprovalActionLogRow{
		Id:                      "marker-id",
		ActionEvent:             "MarkerEvent",
		WorkItemId:              "marker-workitem",
		ProcessInstanceId:       "marker-instance",
		ProcessDefinitionId:     "marker-definition",
		TargetObjectId:          "marker-target",
		ActorId:                 "marker-actor",
		RequestedStatus:         "MarkerStatus",
		Outcome:                 ApprovalActionOutcomeRolledBack,
		FailureClass:            ApprovalActionFailureClassDeadlock,
		FailureCause:            "marker-cause",
		MaterializedActionCount: &count,
		DurationMs:              4242,
		CreatedById:             "marker-createdby",
		RequestId:               "marker-request",
		TraceId:                 "marker-trace",
	}
	if err := NewDBApprovalActionLogStore(db).RecordApprovalAction(context.Background(), row); err != nil {
		t.Fatalf("RecordApprovalAction: %v", err)
	}

	want := map[string]string{
		"Id":                  row.Id,
		"ActionEvent":         row.ActionEvent,
		"WorkItemId":          row.WorkItemId,
		"ProcessInstanceId":   row.ProcessInstanceId,
		"ProcessDefinitionId": row.ProcessDefinitionId,
		"TargetObjectId":      row.TargetObjectId,
		"ActorId":             row.ActorId,
		"RequestedStatus":     row.RequestedStatus,
		"Outcome":             row.Outcome,
		"FailureClass":        row.FailureClass,
		"FailureCause":        row.FailureCause,
		"CreatedById":         row.CreatedById,
		"RequestId":           row.RequestId,
		"TraceId":             row.TraceId,
	}
	for col, expect := range want {
		var got sql.NullString
		if err := db.QueryRow("SELECT `"+col+"` FROM `"+table+"` WHERE Id = ?", row.Id).Scan(&got); err != nil {
			t.Fatalf("read %s: %v", col, err)
		}
		if got.String != expect {
			t.Errorf("column %s holds %q; want %q - the bind argument is attached to the wrong column",
				col, got.String, expect)
		}
	}

	var gotCount sql.NullInt64
	var gotDuration int
	if err := db.QueryRow("SELECT MaterializedActionCount, DurationMs FROM `"+table+"` WHERE Id = ?", row.Id).
		Scan(&gotCount, &gotDuration); err != nil {
		t.Fatalf("read numerics: %v", err)
	}
	if !gotCount.Valid || gotCount.Int64 != 7 || gotDuration != 4242 {
		t.Errorf("numeric columns misaligned: count=%v duration=%d", gotCount, gotDuration)
	}
}