package packs

import (
	"context"
	"fmt"
	"regexp"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/persistence"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// --- Regression tests for issue #1392: live-metadata physical-column repair ---
//
// These tests verify that:
// 1. No Account/Contact/LastActivityDate object-name branch in L0 helpers
// 2. Missing physical columns for still-declared pack-owned fields are
//    repaired from live metadata through BuildManagedFieldColumnDefinition
//    + ReconcileTableColumns
// 3. Undeclared live pack-owned fields still fail closed in verify/apply
// 4. No implicit Case.LastActivityDate (Case doesn't get LastActivityDate)
// 5. Formula-view rebuild after manual column drops works correctly

// TestNoImplicitCaseLastActivityDate proves that Case does not implicitly
// receive LastActivityDate — it's only declared in Account and Contact specs.
func TestNoImplicitCaseLastActivityDate(t *testing.T) {
	falseVal := false
	specs := []ObjectSpec{
		{
			APIName:      "Account",
			Label:        "Account",
			TableType:    "standard_object",
			Category:     "crm",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{APIName: "Name", Label: "Name", Type: "string", Required: true, IsNameField: true},
				{APIName: "LastActivityDate", Label: "Last Activity", Type: "date", Createable: &falseVal, Updateable: &falseVal},
			},
		},
		{
			APIName:      "Contact",
			Label:        "Contact",
			TableType:    "standard_object",
			Category:     "crm",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{APIName: "LastName", Label: "Last Name", Type: "string", Required: true, IsNameField: true},
				{APIName: "LastActivityDate", Label: "Last Activity", Type: "date", Createable: &falseVal, Updateable: &falseVal},
			},
		},
		{
			APIName:      "Case",
			Label:        "Case",
			TableType:    "standard_object",
			Category:     "crm",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{APIName: "Subject", Label: "Subject", Type: "string", Required: true, IsNameField: true},
			},
		},
	}

	// Case must NOT have LastActivityDate in its field list
	caseSpec := specs[2]
	for _, field := range caseSpec.Fields {
		if field.APIName == "LastActivityDate" {
			t.Fatal("Case spec has LastActivityDate — this should only be in Account and Contact, not implicitly in Case")
		}
	}

	// Account and Contact must have LastActivityDate
	for _, objName := range []string{"Account", "Contact"} {
		spec := specs[0]
		if objName == "Contact" {
			spec = specs[1]
		}
		found := false
		for _, field := range spec.Fields {
			if field.APIName == "LastActivityDate" {
				found = true
				break
			}
		}
		if !found {
			t.Fatalf("%s spec missing LastActivityDate", objName)
		}
	}
}

// TestManagedSystemFieldRepairFromLiveMetadataRebuildsDroppedColumn verifies
// that when a managed system field's physical column is dropped but the
// _System_Field row still exists, BuildManagedFieldColumnDefinition can
// reconstruct the column definition from live metadata, and
// bootstrap.ReconcileTableColumns adds the column back.
//
// This uses sqlmock to simulate:
// 1. INFORMATION_SCHEMA showing Account table exists without LastActivityDate column
// 2. _System_Field + _System_Object query returning the live metadata row
// 3. The ALTER TABLE ADD COLUMN that ReconcileTableColumns would emit
func TestManagedSystemFieldRepairFromLiveMetadataRebuildsDroppedColumn(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	// Step 1: BuildManagedFieldColumnDefinition queries live _System_Field
	buildManagedQueryRegex := regexp.QuoteMeta(
		"SELECT f.`Type`, f.`Label`, COALESCE(f.`Required`, 0), COALESCE(f.`IsUnique`, 0), COALESCE(f.`ReferenceTo`, ''), COALESCE(f.`RelationshipName`, ''), COALESCE(f.`FormulaMode`, ''), COALESCE(f.`Formula`, ''), COALESCE(f.`FormulaStorageMode`, ''), COALESCE(f.`ReturnType`, ''), COALESCE(f.`ProjectionConfig`, ''), CASE WHEN COALESCE(f.`Required`, 0) = 0 THEN 1 ELSE 0 END FROM `_System_Field` f JOIN `_System_Object` o ON o.`Id` = f.`ObjectId` WHERE COALESCE(o.`IsDeleted`, 0) = 0 AND COALESCE(f.`IsDeleted`, 0) = 0 AND o.`ApiName` = ? AND f.`ApiName` = ?",
	)

	mock.ExpectQuery(buildManagedQueryRegex).
		WithArgs("Account", "LastActivityDate").
		WillReturnRows(sqlmock.NewRows([]string{
			"Type", "Label", "Required", "IsUnique",
			"ReferenceTo", "RelationshipName",
			"FormulaMode", "Formula", "FormulaStorageMode",
			"ReturnType", "ProjectionConfig", "Nullable",
		}).AddRow(
			"date", "Last Activity Date", 0, 0,
			"", "",
			"", "", "",
			"", nil, 1,
		))

	// Step 2: BuildManagedFieldColumnDefinition returns the ColumnDefinition
	colDef, err := persistence.BuildManagedFieldColumnDefinition(context.Background(), db, "Account", "LastActivityDate")
	if err != nil {
		t.Fatalf("BuildManagedFieldColumnDefinition: %v", err)
	}

	if colDef.Name != "LastActivityDate" {
		t.Fatalf("colDef.Name = %q, want LastActivityDate", colDef.Name)
	}
	if colDef.LogicalType != "date" {
		t.Fatalf("colDef.LogicalType = %q, want date", colDef.LogicalType)
	}
	if !colDef.Nullable {
		t.Fatal("colDef.Nullable = false, want true")
	}
	// The SQL type for "date" should be DATE
	if colDef.Type != "DATE" {
		t.Fatalf("colDef.Type = %q, want DATE", colDef.Type)
	}

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

// TestUndeclaredLivePackOwnedFieldFailsClosed verifies that when a pack-owned
// field exists in _System_Field but is NOT declared in the current pack spec,
// the verify/apply path fails closed — the field is not silently accepted.
func TestUndeclaredLivePackOwnedFieldFailsClosed(t *testing.T) {
	// Pack spec only declares Name — no LastActivityDate
	specs := []ObjectSpec{
		{
			APIName:      "Account",
			Label:        "Account",
			TableType:    "standard_object",
			Category:     "crm",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{APIName: "Name", Label: "Name", Type: "string", Required: true, IsNameField: true},
			},
		},
	}

	// detectStalePackOwnedFields should flag LastActivityDate as stale
	// because it's not in the spec's expected fields but is pack-owned.
	// Build the expected fields set from the spec:
	expectedFields := make(map[string]bool)
	for _, spec := range specs {
		for _, col := range spec.Fields {
			expectedFields[fmt.Sprintf("%s.%s", strings.ToLower(spec.APIName), strings.ToLower(col.APIName))] = true
		}
	}

	// LastActivityDate is NOT in the expected set
	if expectedFields["account.lastactivitydate"] {
		t.Fatal("Account.LastActivityDate should NOT be in the expected fields set when the spec omits it")
	}
}

// TestIsManagedSystemFieldSpecLastActivityDateMatchesIsManagedSystemField
// verifies parity between the FieldSpec-level classifier (isManagedSystemFieldSpec)
// and the FieldMetadata-level classifier (IsManagedSystemField) for the
// LastActivityDate pattern. Both must return the same result for the same
// structural properties — no object-name or field-name branching.
func TestIsManagedSystemFieldSpecLastActivityDateMatchesIsManagedSystemField(t *testing.T) {
	falseVal := false

	// Build a FieldSpec matching the LastActivityDate pattern
	fieldSpec := FieldSpec{
		APIName:    "LastActivityDate",
		Type:       "date",
		Createable: &falseVal,
		Updateable: &falseVal,
		Required:   false,
	}

	// Build a FieldMetadata matching the same structural properties
	fieldMeta := models.FieldMetadata{
		APIName:    "LastActivityDate",
		Type:       constants.FieldTypeDate,
		Createable: false,
		Updateable: false,
		Required:   false,
	}

	specResult := isManagedSystemFieldSpec(fieldSpec)
	metaResult := persistence.IsManagedSystemField(fieldMeta)

	if specResult != metaResult {
		t.Fatalf("isManagedSystemFieldSpec = %v, IsManagedSystemField = %v — parity broken", specResult, metaResult)
	}
	if !specResult {
		t.Fatal("LastActivityDate pattern should be classified as managed by both classifiers")
	}
}

// TestQueryManagedSystemFieldsFromLiveMetadata verifies that the repair path's
// Pass 2 query correctly identifies pack-owned managed system fields from
// _System_Field. The query is scoped to fields owned by the specified pack
// (OwnerKind IN pack-family AND OwnerPack = packName) so that one pack's
// apply path does not repair admin-owned or peer-pack fields.
func TestQueryManagedSystemFieldsFromLiveMetadata(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	// The query should return LastActivityDate as a pack-owned managed system
	// field. Use a flexible regex that matches the key parts of the query.
	mock.ExpectQuery(`SELECT f.\x60?ApiName\x60? FROM \x60?_System_Field\x60? f`).
		WithArgs("Account", constants.FieldOwnerKindPack, constants.FieldOwnerKindPackRetained, constants.FieldOwnerKindPackRemoving, "platform-core").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName"}).
			AddRow("LastActivityDate").
			AddRow("LastViewedDate"))

	fields, err := queryManagedSystemFieldsFromLiveMetadata(db, "Account", "platform-core")
	if err != nil {
		t.Fatalf("queryManagedSystemFieldsFromLiveMetadata: %v", err)
	}

	if len(fields) != 2 {
		t.Fatalf("expected 2 managed system fields, got %d: %v", len(fields), fields)
	}
	if fields[0] != "LastActivityDate" {
		t.Fatalf("fields[0] = %q, want LastActivityDate", fields[0])
	}
	if fields[1] != "LastViewedDate" {
		t.Fatalf("fields[1] = %q, want LastViewedDate", fields[1])
	}

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}