package bootstrapimpl

import (
	"database/sql/driver"
	"fmt"
	"regexp"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/kernel/pkg/schema"
)

func TestEnsurePluralLabelsAndNameFieldsLeavesReferenceLabelsToCanonicalReconciler(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })

	tableDefs := []schema.TableDefinition{{
		TableName: "Task",
		Columns: []schema.ColumnDefinition{{
			Name:        "ParentId",
			Label:       "Parent ID",
			LogicalType: "reference",
		}},
	}}
	mock.ExpectExec(regexp.QuoteMeta("UPDATE `_System_Object` SET")).
		WithArgs("task").
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(regexp.QuoteMeta("UPDATE `_System_Field` f JOIN `_System_Object` o")).
		WithArgs("task", "ParentId").
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("(?s)SET f\\.`Label` = TRIM\\(TRAILING '/' FROM f\\.`Label`\\).*WHERE .*`IsCustomized`.*`IsDeleted`").
		WithArgs("task", "ParentId").
		WillReturnResult(sqlmock.NewResult(0, 0))

	if err := ensurePluralLabelsAndNameFieldsScoped(db, tableDefs, true); err != nil {
		t.Fatalf("ensurePluralLabelsAndNameFieldsScoped: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}

func TestReconcileFieldLabelsWritesOnlyLiveUncustomizedDeltasAndWarmRunWritesNothing(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })

	tableDefs := []schema.TableDefinition{{
		TableName: "Task",
		Columns: []schema.ColumnDefinition{
			{Name: "ParentId", Label: "Parent ID", LogicalType: "reference"},
			{Name: "CustomId", Label: "Catalog Custom ID", LogicalType: "reference"},
			{Name: "DeletedId", Label: "Deleted ID", LogicalType: "reference"},
			{Name: "AlreadyId", Label: "Already ID", LogicalType: "reference"},
			{Name: "CreatedById", LogicalType: "reference"},
		},
	}}
	candidateArgs := []driver.Value{
		"task", "ParentId",
		"task", "CustomId",
		"task", "DeletedId",
		"task", "AlreadyId",
		"task", "CreatedById",
	}
	columns := []string{"Id", "ObjectApiName", "FieldApiName", "Label", "Type", "IsCustomized", "IsDeleted"}

	mock.ExpectQuery(regexp.QuoteMeta("SELECT f.`Id`, o.`ApiName`, f.`ApiName`, f.`Label`, f.`Type`")).
		WithArgs(candidateArgs...).
		WillReturnRows(sqlmock.NewRows(columns).
			AddRow("field-parent", "Task", "ParentId", "Parent", "reference", false, false).
			AddRow("field-custom", "Task", "CustomId", "Team Custom ID", "reference", true, false).
			AddRow("field-deleted", "Task", "DeletedId", "Deleted", "reference", false, true).
			AddRow("field-already", "Task", "AlreadyId", "Already ID", "reference", false, false).
			AddRow("field-created-by", "Task", "CreatedById", "CreatedById", "reference", false, false))
	mock.ExpectBegin()
	mock.ExpectExec("(?s)UPDATE `_System_Field` f JOIN .*`DesiredLabel`").
		WithArgs(
			"field-parent", "Parent", "Parent ID",
			"field-created-by", "CreatedById", "Created By",
		).
		WillReturnResult(sqlmock.NewResult(0, 2))
	mock.ExpectCommit()

	if err := reconcileFieldLabelsScoped(db, tableDefs, true); err != nil {
		t.Fatalf("first reconcileFieldLabelsScoped: %v", err)
	}

	mock.ExpectQuery(regexp.QuoteMeta("SELECT f.`Id`, o.`ApiName`, f.`ApiName`, f.`Label`, f.`Type`")).
		WithArgs(candidateArgs...).
		WillReturnRows(sqlmock.NewRows(columns).
			AddRow("field-parent", "Task", "ParentId", "Parent ID", "reference", false, false).
			AddRow("field-custom", "Task", "CustomId", "Team Custom ID", "reference", true, false).
			AddRow("field-deleted", "Task", "DeletedId", "Deleted", "reference", false, true).
			AddRow("field-already", "Task", "AlreadyId", "Already ID", "reference", false, false).
			AddRow("field-created-by", "Task", "CreatedById", "Created By", "reference", false, false))

	if err := reconcileFieldLabelsScoped(db, tableDefs, true); err != nil {
		t.Fatalf("second reconcileFieldLabelsScoped: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}

func TestReconcileFieldLabelsChunksLargeCandidatePrefetch(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })

	columns := make([]schema.ColumnDefinition, 0, 501)
	for i := 0; i < 501; i++ {
		columns = append(columns, schema.ColumnDefinition{Name: fmt.Sprintf("Field%d", i)})
	}
	tableDefs := []schema.TableDefinition{{TableName: "Task", Columns: columns}}
	resultColumns := []string{"Id", "ObjectApiName", "FieldApiName", "Label", "Type", "IsCustomized", "IsDeleted"}
	for range 3 {
		mock.ExpectQuery(regexp.QuoteMeta("SELECT f.`Id`, o.`ApiName`, f.`ApiName`, f.`Label`, f.`Type`")).
			WillReturnRows(sqlmock.NewRows(resultColumns))
	}

	if err := reconcileFieldLabelsScoped(db, tableDefs, true); err != nil {
		t.Fatalf("reconcileFieldLabelsScoped: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}

func TestReconcileFieldLabelsRejectsConflictingCatalogLabels(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })

	tableDefs := []schema.TableDefinition{
		{
			TableName: "Task",
			Columns:   []schema.ColumnDefinition{{Name: "ParentId", Label: "Parent ID"}},
		},
		{
			TableName: "task",
			Columns:   []schema.ColumnDefinition{{Name: "parentid", Label: "Parent"}},
		},
	}

	err = reconcileFieldLabelsScoped(db, tableDefs, true)
	if err == nil || !regexp.MustCompile(`conflicting explicit labels.*Parent ID.*Parent`).MatchString(err.Error()) {
		t.Fatalf("reconcileFieldLabelsScoped error = %v, want conflicting explicit labels", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}

func TestReconcileFieldLabelsFullScopeInitializesDiscoveredFields(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })

	columns := []string{"Id", "ObjectApiName", "FieldApiName", "Label", "Type", "IsCustomized", "IsDeleted"}
	mock.ExpectQuery(regexp.QuoteMeta("SELECT f.`Id`, o.`ApiName`, f.`ApiName`, f.`Label`, f.`Type`")).
		WillReturnRows(sqlmock.NewRows(columns).
			AddRow("field-approver", "PackObject", "ApproverId", "ApproverId", "reference", false, false))
	mock.ExpectBegin()
	mock.ExpectExec("(?s)UPDATE `_System_Field` f JOIN .*`DesiredLabel`").
		WithArgs("field-approver", "ApproverId", "Approver").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectCommit()

	if err := reconcileFieldLabelsScoped(db, nil, false); err != nil {
		t.Fatalf("reconcileFieldLabelsScoped: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}

func TestReconcileFieldLabelsRollsBackWhenPrefetchedDeltaChanges(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })

	tableDefs := []schema.TableDefinition{{
		TableName: "Task",
		Columns:   []schema.ColumnDefinition{{Name: "ParentId", Label: "Parent ID"}},
	}}
	columns := []string{"Id", "ObjectApiName", "FieldApiName", "Label", "Type", "IsCustomized", "IsDeleted"}
	mock.ExpectQuery(regexp.QuoteMeta("SELECT f.`Id`, o.`ApiName`, f.`ApiName`, f.`Label`, f.`Type`")).
		WithArgs("task", "ParentId").
		WillReturnRows(sqlmock.NewRows(columns).
			AddRow("field-parent", "Task", "ParentId", "Parent", "reference", false, false))
	mock.ExpectBegin()
	mock.ExpectExec("(?s)UPDATE `_System_Field` f JOIN .*`DesiredLabel`").
		WithArgs("field-parent", "Parent", "Parent ID").
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectRollback()

	err = reconcileFieldLabelsScoped(db, tableDefs, true)
	if err == nil || !regexp.MustCompile(`matched 0 of 1 prefetched deltas`).MatchString(err.Error()) {
		t.Fatalf("reconcileFieldLabelsScoped error = %v, want stale-prefetch failure", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}