package bootstrap

import (
	"strings"
	"testing"

	"github.com/nexuscrm/backend/internal/domain/schema"
)

// TestAuditLogAdditiveColumnsC1 asserts the 6 NULL-able columns added in C1
// (design doc §2.1) are present on _System_AuditLog with the expected types
// AND match the authoritative names from design §2.1 + the C4 Go struct
// (pre-C5 reconcile: `Action` not `ActionType`, `SessionId` not `SessionKey`).
// All 6 MUST be nullable; FieldName MUST remain VARCHAR(255) NOT NULL
// (additive-only contract — no existing column narrowed or altered).
func TestAuditLogAdditiveColumnsC1(t *testing.T) {
	defs := GetSystemTableDefinitions()
	var auditLog *struct {
		columns map[string]struct {
			colType  string
			nullable bool
		}
	}

	for i := range defs {
		if defs[i].TableName != "_System_AuditLog" {
			continue
		}
		auditLog = &struct {
			columns map[string]struct {
				colType  string
				nullable bool
			}
		}{columns: map[string]struct {
			colType  string
			nullable bool
		}{}}
		for _, c := range defs[i].Columns {
			auditLog.columns[c.Name] = struct {
				colType  string
				nullable bool
			}{colType: c.Type, nullable: c.Nullable}
		}
		break
	}
	if auditLog == nil {
		t.Fatalf("_System_AuditLog not found in system_tables.json")
	}

	// C1 additive columns (all NULL-able), post pre-C5 reconcile.
	expected := []struct {
		name    string
		colType string
	}{
		{"Entrypoint", "VARCHAR(32)"},
		{"Action", "VARCHAR(32)"},
		{"ChangesJSON", "LONGTEXT"},
		{"TraceId", "CHAR(32)"},
		{"ClientIp", "VARCHAR(45)"},
		{"SessionId", "VARCHAR(64)"},
	}

	for _, e := range expected {
		got, ok := auditLog.columns[e.name]
		if !ok {
			t.Errorf("_System_AuditLog missing column %q (§2.1 canonical name)", e.name)
			continue
		}
		if got.colType != e.colType {
			t.Errorf("_System_AuditLog.%s: type = %q, want %q", e.name, got.colType, e.colType)
		}
		if !got.nullable {
			t.Errorf("_System_AuditLog.%s: nullable = false, want true (C1 additive-only contract)", e.name)
		}
	}

	// Pre-C5 rename invariant: old names MUST be absent.
	for _, old := range []string{"ActionType", "SessionKey"} {
		if _, ok := auditLog.columns[old]; ok {
			t.Errorf("_System_AuditLog.%s must be renamed (pre-C5 reconcile: Action/SessionId)", old)
		}
	}

	// Invariant: FieldName must remain VARCHAR(255) NOT NULL.
	fn, ok := auditLog.columns["FieldName"]
	if !ok {
		t.Fatalf("_System_AuditLog.FieldName missing — regression")
	}
	if fn.colType != "VARCHAR(255)" {
		t.Errorf("_System_AuditLog.FieldName type = %q, want VARCHAR(255) (must not narrow)", fn.colType)
	}
	if fn.nullable {
		t.Errorf("_System_AuditLog.FieldName became nullable — additive-only contract violated")
	}
}

// TestAuditLogEmitterColumnsC15 asserts the columns the DB emitter INSERTs
// (emitter_db.go head-row INSERT) are all present and nullable in the seed.
// Missing columns here crash the emitter at runtime and break the
// capture->audit replay linkage (C15 sub-agent flag). All additive, all NULL.
// The RequestId column here is the logical join partner of
// _System_RequestCapture.RequestId (§14.9 one-to-many join used by the
// replay reader at backend/internal/features/audit/replay/reader.go).
func TestAuditLogEmitterColumnsC15(t *testing.T) {
	defs := GetSystemTableDefinitions()
	var td *schema.TableDefinition
	for i := range defs {
		if defs[i].TableName == "_System_AuditLog" {
			td = &defs[i]
			break
		}
	}
	if td == nil {
		t.Fatalf("_System_AuditLog not found")
	}
	cols := map[string]schema.ColumnDefinition{}
	for _, c := range td.Columns {
		cols[c.Name] = c
	}

	// Columns referenced by emitter_db.go INSERT — all must exist, all NULL.
	expected := []struct {
		name    string
		colType string
	}{
		{"EntrypointDetail", "VARCHAR(255)"},
		{"RequestId", "VARCHAR(64)"},
		{"ParentRequestId", "VARCHAR(64)"},
		{"ActualUserId", "VARCHAR(255)"},
		{"UserAgent", "VARCHAR(512)"},
		{"Outcome", "VARCHAR(16)"},
		{"ErrorCode", "VARCHAR(64)"},
	}
	for _, e := range expected {
		got, ok := cols[e.name]
		if !ok {
			t.Errorf("_System_AuditLog missing emitter column %q (seed drift — emitter INSERT would crash)", e.name)
			continue
		}
		if !strings.EqualFold(got.Type, e.colType) {
			t.Errorf("_System_AuditLog.%s type = %q, want %q", e.name, got.Type, e.colType)
		}
		if !got.Nullable {
			t.Errorf("_System_AuditLog.%s must be nullable (additive-only contract)", e.name)
		}
	}

	// Capture<->Audit linkage: RequestId must exist on both sides with compatible type.
	captureReqID := findColumn(t, defs, "_System_RequestCapture", "RequestId")
	auditReqID, ok := cols["RequestId"]
	if !ok {
		t.Fatalf("_System_AuditLog.RequestId missing — capture<->audit linkage broken")
	}
	if !strings.EqualFold(captureReqID.Type, auditReqID.Type) {
		t.Errorf("RequestId type mismatch: capture=%q, audit=%q — replay join needs compatible types",
			captureReqID.Type, auditReqID.Type)
	}
}

// findColumn is a small helper for cross-table linkage assertions.
func findColumn(t *testing.T, defs []schema.TableDefinition, table, col string) schema.ColumnDefinition {
	t.Helper()
	for _, d := range defs {
		if d.TableName != table {
			continue
		}
		for _, c := range d.Columns {
			if c.Name == col {
				return c
			}
		}
	}
	t.Fatalf("%s.%s not found", table, col)
	return schema.ColumnDefinition{}
}

// TestAuditLogOutboxDedupSchema asserts §2.4/§3.4 outbox dedup prerequisites:
// - `OutboxRowId BIGINT NULL` column present (nullable — historical rows have no outbox link).
// - `uk_outbox(OutboxRowId, FieldName, ChangedAt)` UNIQUE KEY present with columns
//   in the declared order, matching the INSERT IGNORE dedup key in §3.4.
func TestAuditLogOutboxDedupSchema(t *testing.T) {
	defs := GetSystemTableDefinitions()
	var td *schema.TableDefinition
	for i := range defs {
		if defs[i].TableName == "_System_AuditLog" {
			td = &defs[i]
			break
		}
	}
	if td == nil {
		t.Fatalf("_System_AuditLog not found")
	}

	// OutboxRowId column check.
	var outboxCol *schema.ColumnDefinition
	for i := range td.Columns {
		if td.Columns[i].Name == "OutboxRowId" {
			outboxCol = &td.Columns[i]
			break
		}
	}
	if outboxCol == nil {
		t.Fatalf("_System_AuditLog.OutboxRowId missing (§2.4 outbox link)")
	}
	if !strings.EqualFold(outboxCol.Type, "BIGINT") {
		t.Errorf("_System_AuditLog.OutboxRowId type = %q, want BIGINT", outboxCol.Type)
	}
	if !outboxCol.Nullable {
		t.Errorf("_System_AuditLog.OutboxRowId must be nullable (legacy rows have no outbox row)")
	}

	// uk_outbox index check — name + unique + column order.
	var uk *schema.IndexDefinition
	for i := range td.Indices {
		if td.Indices[i].Name == "uk_outbox" {
			uk = &td.Indices[i]
			break
		}
	}
	if uk == nil {
		t.Fatalf("_System_AuditLog index uk_outbox missing (§3.4 INSERT IGNORE dedup)")
	}
	if !uk.Unique {
		t.Errorf("uk_outbox must be UNIQUE")
	}
	wantCols := []string{"OutboxRowId", "FieldName", "ChangedAt"}
	if len(uk.Columns) != len(wantCols) {
		t.Fatalf("uk_outbox columns = %v, want %v", uk.Columns, wantCols)
	}
	for i, want := range wantCols {
		if uk.Columns[i] != want {
			t.Errorf("uk_outbox.Columns[%d] = %q, want %q", i, uk.Columns[i], want)
		}
	}
}