package bootstrap

import (
	"encoding/json"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/backend/internal/domain/schema"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/stretchr/testify/require"
)

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

	tableDefs := []schema.TableDefinition{
		{TableName: "Account", TableType: string(constants.TableTypeStandardObject), SharingModel: string(constants.SharingModelPrivate)},
	}

	mock.ExpectExec("UPDATE `_System_Object` SET `SharingModel` = \\?, `LastModifiedDate` = NOW\\(\\) WHERE LOWER\\(`ApiName`\\) = \\? AND `TableType` = \\? AND \\(\\(`IsCustomized` = 0 OR `IsCustomized` IS NULL\\) OR `SharingModel` = \\?\\) AND `SharingModel` != \\?").
		WithArgs(string(constants.SharingModelPrivate), "account", string(constants.TableTypeStandardObject), string(constants.SharingModelPublicRead), string(constants.SharingModelPrivate)).
		WillReturnResult(sqlmock.NewResult(0, 1))

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

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

	tableDefs := []schema.TableDefinition{
		{
			TableName:              "Contract",
			TableType:              string(constants.TableTypeStandardObject),
			SharingModel:           string(constants.SharingModelControlledByParent),
			EnableHierarchySharing: true,
			AccessPolicy: &models.ObjectAccessPolicy{
				Mode:       "derived",
				ObjectRead: &models.AccessPolicyExpr{Kind: "objectPermission", Object: "Account", Operation: "Read"},
			},
		},
	}
	accessPolicyJSON, err := json.Marshal(tableDefs[0].AccessPolicy)
	if err != nil {
		t.Fatalf("marshal AccessPolicy: %v", err)
	}

	mock.ExpectExec("UPDATE `_System_Object` SET `SharingModel` = \\?, `LastModifiedDate` = NOW\\(\\) WHERE LOWER\\(`ApiName`\\) = \\? AND `TableType` = \\? AND \\(\\(`IsCustomized` = 0 OR `IsCustomized` IS NULL\\) OR `SharingModel` = \\?\\) AND `SharingModel` != \\?").
		WithArgs(string(constants.SharingModelControlledByParent), "contract", string(constants.TableTypeStandardObject), string(constants.SharingModelPrivate), string(constants.SharingModelControlledByParent)).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec("UPDATE `_System_Object` SET `EnableHierarchySharing` = \\?, `LastModifiedDate` = NOW\\(\\) WHERE LOWER\\(`ApiName`\\) = \\? AND `TableType` = \\? AND COALESCE\\(`EnableHierarchySharing`, 0\\) != \\?").
		WithArgs(true, "contract", string(constants.TableTypeStandardObject), true).
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("UPDATE `_System_Object` SET `AccessPolicy` = CAST\\(\\? AS JSON\\), `LastModifiedDate` = NOW\\(\\) WHERE LOWER\\(`ApiName`\\) = \\? AND `TableType` = \\? AND \\(`AccessPolicy` IS NULL OR CAST\\(`AccessPolicy` AS CHAR\\) != \\?\\)").
		WithArgs(string(accessPolicyJSON), "contract", string(constants.TableTypeStandardObject), string(accessPolicyJSON)).
		WillReturnResult(sqlmock.NewResult(0, 1))

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

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

	tableDefs := []schema.TableDefinition{
		{TableName: "_System_Session", TableType: string(constants.TableTypeSystemCore), SharingModel: string(constants.SharingModelPublicReadWrite)},
		{TableName: "MyObject__c", TableType: string(constants.TableTypeCustomObject), SharingModel: string(constants.SharingModelPrivate)},
	}

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

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

	tableDefs := []schema.TableDefinition{
		{TableName: constants.TableFile},
		{TableName: constants.TableFileUploadOperation},
	}

	mock.ExpectExec("UPDATE `_System_Object` SET `IsDeleted` = 0, `LastModifiedDate` = NOW\\(\\) WHERE LOWER\\(`ApiName`\\) = \\? AND COALESCE\\(`IsDeleted`, 0\\) != 0").
		WithArgs(strings.ToLower(constants.TableFile)).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec("UPDATE `_System_Object` SET `IsDeleted` = 0, `LastModifiedDate` = NOW\\(\\) WHERE LOWER\\(`ApiName`\\) = \\? AND COALESCE\\(`IsDeleted`, 0\\) != 0").
		WithArgs(strings.ToLower(constants.TableFileUploadOperation)).
		WillReturnResult(sqlmock.NewResult(0, 0))

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

// reconcileBooleanDefaultValues must normalize Required booleans with
// NULL/empty DefaultValue, guarded by IsCustomized and IsDeleted. The SQL
// shape is the invariant under test — same pattern as reconcileFieldLabels.
func TestReconcileBooleanDefaultValues_UpdatesRequiredBooleansWithGuards(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectExec(
		"UPDATE `_System_Field` SET `DefaultValue` = 'false', `LastModifiedDate` = NOW\\(\\) " +
			"WHERE `Type` = 'boolean' AND `Required` = 1 " +
			"AND \\(`DefaultValue` IS NULL OR `DefaultValue` = ''\\) " +
			"AND \\(`IsCustomized` = 0 OR `IsCustomized` IS NULL\\) " +
			"AND `IsDeleted` = 0",
	).WillReturnResult(sqlmock.NewResult(0, 173))

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

// Idempotency: second invocation matches zero rows and returns nil.
func TestReconcileBooleanDefaultValues_IdempotentNoop(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectExec("UPDATE `_System_Field` SET `DefaultValue` = 'false'").
		WillReturnResult(sqlmock.NewResult(0, 0))

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

// Phase ordering invariant: reconcileStandardFieldDefaultValues (phase7)
// must run BEFORE reconcileBooleanDefaultValues (phase7b). If the boolean
// fallback runs first on a row where the spec declares a non-'false' default
// (e.g. Campaign.IsActive spec default '0'), the fallback writes 'false',
// the row no longer matches the NULL/empty filter on the spec pass, and the
// spec value is permanently shadowed. The ordering is a load-bearing
// correctness invariant — pin it so a future phase-map reshuffle fails
// loudly rather than silently reintroducing the shadowing bug.
func TestPhaseOrdering_SpecDefaultsBeforeBooleanFallback(t *testing.T) {
	spec, specOK := bootstrapPhaseOrder["phase7_spec_defaults"]
	boolFallback, boolOK := bootstrapPhaseOrder["phase7b_boolean_fallback"]
	if !specOK {
		t.Fatalf("phase7_spec_defaults missing from bootstrapPhaseOrder — ordering invariant cannot be enforced")
	}
	if !boolOK {
		t.Fatalf("phase7b_boolean_fallback missing from bootstrapPhaseOrder — ordering invariant cannot be enforced")
	}
	if spec >= boolFallback {
		t.Fatalf("phase ordering violation: phase7_spec_defaults (%d) must run before phase7b_boolean_fallback (%d); reordering permanently shadows spec-declared non-'false' defaults like Campaign.IsActive='0'",
			spec, boolFallback)
	}
}

// reconcileStandardFieldDefaultValues must issue a single cross-table UPDATE
// filtered by NULL/empty DefaultValue + IsCustomized + IsDeleted guards, and
// restrict rows to the (object,field) tuples declared in the spec. We assert
// on the invariant SQL shape, not a specific row count — the spec embeds at
// build time and content may drift.
func TestReconcileStandardFieldDefaultValues_SQLShape(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectExec(
		"UPDATE `_System_Field` f JOIN `_System_Object` o ON f.`ObjectId` = o.`Id` " +
			"SET f.`DefaultValue` = CASE CONCAT\\(LOWER\\(o.`ApiName`\\), '.', f.`ApiName`\\)" +
			".+" +
			" ELSE f.`DefaultValue` END, f.`LastModifiedDate` = NOW\\(\\) " +
			"WHERE \\(LOWER\\(o.`ApiName`\\), f.`ApiName`\\) IN \\(" +
			".+" +
			"\\) " +
			"AND o.`TableType` = \\? " +
			"AND o.`IsDeleted` = 0 " +
			"AND \\(f.`DefaultValue` IS NULL OR f.`DefaultValue` = ''\\) " +
			"AND \\(f.`IsCustomized` = 0 OR f.`IsCustomized` IS NULL\\) " +
			"AND f.`IsDeleted` = 0",
	).WillReturnResult(sqlmock.NewResult(0, 0))

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

func TestBackfillActionInputGuardKeysBeforeIndex_DerivesGuardKey(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	mock.ExpectQuery(`SELECT COLUMN_NAME FROM INFORMATION_SCHEMA\.COLUMNS WHERE TABLE_SCHEMA = DATABASE\(\) AND TABLE_NAME = \? AND COLUMN_NAME IN \(\?, \?, \?, \?, \?, \?\)`).
		WithArgs(
			constants.TableActionInputGuard,
			constants.FieldSysActionInputGuard_ID,
			constants.FieldSysActionInputGuard_GuardKey,
			constants.FieldSysActionInputGuard_RequiredPermission,
			constants.FieldSysActionInputGuard_RequiredObject,
			constants.FieldSysActionInputGuard_RequiredOperation,
			constants.FieldSysActionInputGuard_IsDeleted,
		).
		WillReturnRows(sqlmock.NewRows([]string{"COLUMN_NAME"}).
			AddRow(constants.FieldSysActionInputGuard_ID).
			AddRow(constants.FieldSysActionInputGuard_GuardKey).
			AddRow(constants.FieldSysActionInputGuard_RequiredPermission).
			AddRow(constants.FieldSysActionInputGuard_RequiredObject).
			AddRow(constants.FieldSysActionInputGuard_RequiredOperation).
			AddRow(constants.FieldSysActionInputGuard_IsDeleted))
	expectActionGuardKeyBackfillExec(mock, 2)

	require.NoError(t, backfillActionInputGuardKeysBeforeIndex(db))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestRunSchemaReconciliationUnderLock_BackfillsActionGuardKeyBeforeIndexReconcile(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	mock.ExpectQuery("SELECT GET_LOCK\\('nexuscrm_schema_reconcile', 600\\)").
		WillReturnRows(sqlmock.NewRows([]string{"GET_LOCK"}).AddRow(1))
	mock.ExpectQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE\\(\\)").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME"}).
			AddRow(constants.TableActionInputGuard))
	mock.ExpectQuery(`SELECT COLUMN_NAME FROM INFORMATION_SCHEMA\.COLUMNS WHERE TABLE_SCHEMA = DATABASE\(\) AND TABLE_NAME = \? AND COLUMN_NAME IN \(\?, \?, \?, \?, \?, \?\)`).
		WithArgs(
			constants.TableActionInputGuard,
			constants.FieldSysActionInputGuard_ID,
			constants.FieldSysActionInputGuard_GuardKey,
			constants.FieldSysActionInputGuard_RequiredPermission,
			constants.FieldSysActionInputGuard_RequiredObject,
			constants.FieldSysActionInputGuard_RequiredOperation,
			constants.FieldSysActionInputGuard_IsDeleted,
		).
		WillReturnRows(sqlmock.NewRows([]string{"COLUMN_NAME"}).
			AddRow(constants.FieldSysActionInputGuard_ID).
			AddRow(constants.FieldSysActionInputGuard_GuardKey).
			AddRow(constants.FieldSysActionInputGuard_RequiredPermission).
			AddRow(constants.FieldSysActionInputGuard_RequiredObject).
			AddRow(constants.FieldSysActionInputGuard_RequiredOperation).
			AddRow(constants.FieldSysActionInputGuard_IsDeleted))
	expectActionGuardKeyBackfillExec(mock, 1)
	mock.ExpectQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE\\(\\)").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME"}).
			AddRow(constants.TableActionInputGuard))
	mock.ExpectQuery(`SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, GROUP_CONCAT\(COLUMN_NAME ORDER BY SEQ_IN_INDEX\)\s+FROM INFORMATION_SCHEMA\.STATISTICS\s+WHERE TABLE_SCHEMA = DATABASE\(\)\s+GROUP BY TABLE_NAME, INDEX_NAME, NON_UNIQUE`).
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME", "INDEX_NAME", "NON_UNIQUE", "GROUP_CONCAT"}))
	mock.ExpectExec("SELECT RELEASE_LOCK\\('nexuscrm_schema_reconcile'\\)").
		WillReturnResult(sqlmock.NewResult(0, 1))

	require.NoError(t, runSchemaReconciliationUnderLock(db, nil))
	require.NoError(t, mock.ExpectationsWereMet())
}

func expectActionGuardKeyBackfillExec(mock sqlmock.Sqlmock, rowsAffected int64) {
	mock.ExpectExec("(?s)UPDATE `_System_ActionInputGuard`\\s+SET `GuardKey` = CASE.*WHEN `IsDeleted` = 1.*':deleted:'.*`Id`.*CONCAT\\('permission:', TRIM\\(`RequiredPermission`\\)\\).*CONCAT\\('object:', TRIM\\(`RequiredObject`\\), ':', TRIM\\(`RequiredOperation`\\)\\).*WHERE \\(`GuardKey` IS NULL OR `GuardKey` = ''\\)\\s+AND \\(\\s+`IsDeleted` = 1").
		WillReturnResult(sqlmock.NewResult(0, rowsAffected))
}

func TestReconcileTableColumns_ModifiesVarcharNullabilityDrift(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	mock.ExpectQuery(`SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA\.COLUMNS WHERE TABLE_SCHEMA = DATABASE\(\)`).
		WillReturnRows(sqlmock.NewRows([]string{
			"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT",
		}).AddRow("_System_ActionInputGuard", "RequiredPermission", "varchar(128)", "NO", nil))
	mock.ExpectExec("ALTER TABLE `_System_ActionInputGuard` MODIFY COLUMN `RequiredPermission` VARCHAR\\(128\\)").
		WillReturnResult(sqlmock.NewResult(0, 1))

	defs := []schema.TableDefinition{{
		TableName: "_System_ActionInputGuard",
		Columns: []schema.ColumnDefinition{{
			Name:     "RequiredPermission",
			Type:     "VARCHAR(128)",
			Nullable: true,
		}},
	}}

	require.NoError(t, ReconcileTableColumns(db, defs))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestReconcileTableColumns_BooleanDefaultsMatchTinyintDefaults(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	mock.ExpectQuery(`SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA\.COLUMNS WHERE TABLE_SCHEMA = DATABASE\(\)`).
		WillReturnRows(sqlmock.NewRows([]string{
			"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT",
		}).
			AddRow("_System_Test", "DefaultTrue", "tinyint(1)", "NO", "1").
			AddRow("_System_Test", "DefaultFalse", "tinyint(1)", "NO", "0"))

	defs := []schema.TableDefinition{{
		TableName: "_System_Test",
		Columns: []schema.ColumnDefinition{
			{
				Name:     "DefaultTrue",
				Type:     "BOOLEAN",
				Nullable: false,
				Default:  "true",
			},
			{
				Name:     "DefaultFalse",
				Type:     "BOOLEAN",
				Nullable: false,
				Default:  "false",
			},
		},
	}}

	require.NoError(t, ReconcileTableColumns(db, defs))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestReconcileTableColumns_TimestampDefaultsMatchTiDBDefaults(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	mock.ExpectQuery(`SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA\.COLUMNS WHERE TABLE_SCHEMA = DATABASE\(\)`).
		WillReturnRows(sqlmock.NewRows([]string{
			"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT",
		}).
			AddRow("_System_Test", "CreatedDate", "datetime", "NO", "current_timestamp()"))

	defs := []schema.TableDefinition{{
		TableName: "_System_Test",
		Columns: []schema.ColumnDefinition{{
			Name:     "CreatedDate",
			Type:     "DATETIME",
			Nullable: false,
			Default:  "CURRENT_TIMESTAMP",
		}},
	}}

	require.NoError(t, ReconcileTableColumns(db, defs))
	require.NoError(t, mock.ExpectationsWereMet())
}