package persistence

import (
	"context"
	"database/sql"
	"errors"
	database "github.com/nexuscrm/db"
	"testing"

	"github.com/nexuscrm/kernel/pkg/schemacatalog"
	"github.com/nexuscrm/shared/pkg/constants"
)

func TestMetadataRepositoryReadOutcomesOnTiDB(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()

	createCanonicalMetadataReadTables(t, db)
	seedMetadataReadOutcomes(t, db)
	repository := NewMetadataRepository(db)

	t.Run("schema and fields share caller transaction", func(t *testing.T) {
		manager := NewTransactionManager(database.NewConnectionFromDB(db))
		rollback := errors.New("rollback transaction-only metadata")
		err := manager.WithTransaction(t.Context(), func(ctx context.Context) error {
			tx := manager.ExtractTx(ctx).(Executor)
			if _, err := tx.ExecContext(ctx, `UPDATE _System_Object SET Label='Transactional object' WHERE Id='obj-account'`); err != nil {
				return err
			}
			if _, err := tx.ExecContext(ctx, `UPDATE _System_Field SET Label='Transactional field' WHERE Id='fld-account-code'`); err != nil {
				return err
			}
			inside, err := repository.GetSchemaByAPIName(ctx, "Account")
			if err != nil {
				return err
			}
			if inside == nil || inside.Label != "Transactional object" || len(inside.Fields) != 1 || inside.Fields[0].Label != "Transactional field" {
				t.Fatalf("schema hydration escaped transaction: %+v", inside)
			}
			outside, err := repository.GetSchemaByAPIName(t.Context(), "Account")
			if err != nil {
				return err
			}
			if outside.Label == inside.Label || outside.Fields[0].Label == inside.Fields[0].Label {
				t.Fatal("uncommitted metadata escaped to another read")
			}
			return rollback
		})
		if !errors.Is(err, rollback) {
			t.Fatalf("transaction result: %v", err)
		}
	})

	t.Run("object and field hydration", func(t *testing.T) {
		objects, err := repository.GetAllSchemas(t.Context())
		if err != nil {
			t.Fatalf("GetAllSchemas: %v", err)
		}
		byName := make(map[string]int, len(objects))
		for i, object := range objects {
			byName[object.APIName] = i
		}

		accountIndex, ok := byName["Account"]
		if !ok {
			t.Fatalf("GetAllSchemas omitted Account: %#v", objects)
		}
		account := objects[accountIndex]
		if len(account.Fields) != 1 {
			t.Fatalf("Account fields = %#v, want one field", account.Fields)
		}
		const fieldID = "fld-account-code"
		if got := account.Fields[0].ID; got != fieldID {
			t.Fatalf("persisted field identity = %q, want %q", got, fieldID)
		}
		mappedField, ok := account.FieldsMap["account_code__c"]
		if !ok {
			t.Fatalf("Account FieldsMap omitted account_code__c: %#v", account.FieldsMap)
		}
		if got := mappedField.ID; got != fieldID {
			t.Fatalf("FieldsMap identity = %q, want %q", got, fieldID)
		}

		settingsIndex, ok := byName["RegionalSetting__c"]
		if !ok {
			t.Fatalf("GetAllSchemas omitted RegionalSetting__c: %#v", objects)
		}
		settings := objects[settingsIndex]
		if !settings.CustomSetting {
			t.Fatal("RegionalSetting__c CustomSetting = false, want true")
		}
		if settings.ReadSource != nil {
			t.Fatalf("RegionalSetting__c ReadSource = %+v, want nil", settings.ReadSource)
		}

		recordType, err := repository.GetSchemaByAPIName(t.Context(), "RecordType")
		if err != nil {
			t.Fatalf("GetSchemaByAPIName(RecordType): %v", err)
		}
		if recordType == nil {
			t.Fatal("GetSchemaByAPIName(RecordType) = nil, want object")
		}
		if recordType.ReadSource == nil ||
			recordType.ReadSource.Kind != "physical_table" ||
			recordType.ReadSource.PhysicalTable != constants.TableRecordType {
			t.Fatalf("RecordType ReadSource = %+v, want canonical physical table", recordType.ReadSource)
		}
	})

	t.Run("record type scope and soft deletion", func(t *testing.T) {
		recordTypes, err := repository.GetRecordTypes(t.Context(), "aCcOuNt")
		if err != nil {
			t.Fatalf("GetRecordTypes: %v", err)
		}
		if len(recordTypes) != 2 {
			t.Fatalf("Account record types = %#v, want two live rows", recordTypes)
		}
		byID := make(map[string]string, len(recordTypes))
		for _, recordType := range recordTypes {
			byID[recordType.ID] = recordType.DeveloperName
			if recordType.ObjectAPIName != "Account" {
				t.Fatalf("record type %s object = %q, want Account", recordType.ID, recordType.ObjectAPIName)
			}
		}
		if developerName, exists := byID["rt-account"]; !exists || developerName != "Business" {
			t.Fatalf("rt-account DeveloperName = %q, want Business", byID["rt-account"])
		}
		if developerName, exists := byID["rt-account-legacy"]; !exists || developerName != "" {
			t.Fatalf("NULL DeveloperName = %q, want empty string", byID["rt-account-legacy"])
		}
		if _, exists := byID["rt-account-deleted"]; exists {
			t.Fatal("soft-deleted record type was returned")
		}

		all, err := repository.GetAllRecordTypes(t.Context())
		if err != nil {
			t.Fatalf("GetAllRecordTypes: %v", err)
		}
		if len(all) != 3 {
			t.Fatalf("all record types = %#v, want three live rows across objects", all)
		}
		allIDs := make(map[string]bool, len(all))
		for _, recordType := range all {
			allIDs[recordType.ID] = true
		}
		for _, id := range []string{"rt-account", "rt-account-legacy", "rt-contact"} {
			if !allIDs[id] {
				t.Fatalf("GetAllRecordTypes omitted %s: %#v", id, all)
			}
		}
		if allIDs["rt-account-deleted"] {
			t.Fatalf("GetAllRecordTypes returned soft-deleted row: %#v", all)
		}
		empty, err := repository.GetRecordTypes(t.Context(), "")
		if err != nil {
			t.Fatalf("GetRecordTypes(empty): %v", err)
		}
		if len(empty) != 0 {
			t.Fatalf("empty-object record types = %#v, want none", empty)
		}
	})

	t.Run("sharing rule scope and soft deletion", func(t *testing.T) {
		rules, err := repository.GetSharingRules(t.Context(), "ACCOUNT")
		if err != nil {
			t.Fatalf("GetSharingRules: %v", err)
		}
		if len(rules) != 1 || rules[0].ID != "sharing-account" || rules[0].AccessLevel != "Read" {
			t.Fatalf("Account sharing rules = %#v, want live Account rule", rules)
		}
		all, err := repository.GetAllSharingRules(t.Context())
		if err != nil {
			t.Fatalf("GetAllSharingRules: %v", err)
		}
		if len(all) != 2 {
			t.Fatalf("all sharing rules = %#v, want two live rows", all)
		}
		allIDs := make(map[string]bool, len(all))
		for _, rule := range all {
			allIDs[rule.ID] = true
		}
		if !allIDs["sharing-account"] || !allIDs["sharing-contact"] || allIDs["sharing-deleted"] {
			t.Fatalf("GetAllSharingRules returned wrong live set: %#v", all)
		}
		empty, err := repository.GetSharingRules(t.Context(), "")
		if err != nil {
			t.Fatalf("GetSharingRules(empty): %v", err)
		}
		if len(empty) != 0 {
			t.Fatalf("empty-object sharing rules = %#v, want none", empty)
		}
	})

	t.Run("scoped picklist tuple and order", func(t *testing.T) {
		values, err := repository.GetScopedPicklistValues(t.Context(), "rt-account", "fld-account-code")
		if err != nil {
			t.Fatalf("GetScopedPicklistValues: %v", err)
		}
		if len(values) != 2 {
			t.Fatalf("scoped values = %#v, want two live tuple rows", values)
		}
		if values[0].Value != "Alpha" || values[0].SortOrder != 10 || values[0].OwnerPack != "" || !values[0].IsCustomized {
			t.Fatalf("values[0] = %+v, want Alpha/10/no owner/customized", values[0])
		}
		if values[1].Value != "Done" || values[1].SortOrder != 20 || values[1].OwnerPack != "crm" || values[1].IsCustomized {
			t.Fatalf("values[1] = %+v, want Done/20/crm/not customized", values[1])
		}
		empty, err := repository.GetScopedPicklistValues(t.Context(), "rt-account", "missing-field")
		if err != nil {
			t.Fatalf("GetScopedPicklistValues(empty): %v", err)
		}
		if empty == nil || len(empty) != 0 {
			t.Fatalf("empty scoped values = %#v, want non-nil empty slice", empty)
		}
	})
}

func createCanonicalMetadataReadTables(t *testing.T, db *sql.DB) {
	createCanonicalMetadataTables(t, db,
		constants.TableObject,
		constants.TableField,
		constants.TableRecordType,
		constants.TableRecordTypePicklistValue,
		constants.TableSharingRule,
	)
}

func createCanonicalMetadataTables(t *testing.T, db *sql.DB, tableNames ...string) {
	t.Helper()
	definitions, err := schemacatalog.SystemTableDefinitions()
	if err != nil {
		t.Fatalf("SystemTableDefinitions: %v", err)
	}
	byName := make(map[string]int, len(definitions))
	for i := range definitions {
		byName[definitions[i].TableName] = i
	}
	repository := NewSchemaRepository(db, nil)
	for _, tableName := range tableNames {
		index, ok := byName[tableName]
		if !ok {
			t.Fatalf("canonical schema omits %s", tableName)
		}
		if err := repository.CreatePhysicalTable(t.Context(), definitions[index]); err != nil {
			t.Fatalf("create canonical %s: %v", tableName, err)
		}
	}
}

func seedMetadataReadOutcomes(t *testing.T, db *sql.DB) {
	t.Helper()
	for _, row := range []struct {
		id            string
		apiName       string
		customSetting bool
		readSource    any
	}{
		{id: "obj-account", apiName: "Account"},
		{id: "obj-contact", apiName: "Contact"},
		{id: "obj-settings", apiName: "RegionalSetting__c", customSetting: true},
		{id: "obj-recordtype", apiName: "RecordType", readSource: `{"kind":"physical_table","physicalTable":"_System_RecordType"}`},
	} {
		metadataReadExec(t, db, `INSERT INTO _System_Object
			(Id, ApiName, TableType, Label, IsCustom, CustomSetting, Searchable, SharingModel,
			 IsCustomized, LockRuleCustomized, ListFieldsCustomized, ChildCloneRulesCustomized,
			 ExclusiveSiblingFieldsCustomized, DefaultChildRowsCustomized, IsDeleted, CreatedDate,
			 LastModifiedDate, ReadSource)
			VALUES (?, ?, ?, ?, 0, ?, 1, ?, 0, 0, 0, 0, 0, 0, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)`,
			row.id, row.apiName, string(constants.TableTypeStandardObject), row.apiName,
			row.customSetting, string(constants.SharingModelPrivate), row.readSource)
	}

	for _, row := range []struct {
		id       string
		objectID string
		apiName  string
	}{
		{id: "fld-account-code", objectID: "obj-account", apiName: "Account_Code__c"},
		{id: "fld-contact-code", objectID: "obj-contact", apiName: "Contact_Code__c"},
	} {
		metadataReadExec(t, db, `INSERT INTO _System_Field
			(Id, ObjectId, ApiName, Label, Type, Required, IsUnique, IsSystem, IsNameField,
			 Createable, Updateable, Indexed, TrackHistory, HistoryTrackingRevision, IsMasterDetail,
			 IsPolymorphic, IsDeleted, CreatedDate, LastModifiedDate, IsCustomized, OwnerKind)
			VALUES (?, ?, ?, ?, 'text', 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0,
			 CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, 'admin')`,
			row.id, row.objectID, row.apiName, row.apiName)
	}

	for _, row := range []struct {
		id            string
		objectID      string
		name          string
		developerName any
		sobjectType   string
		deleted       bool
	}{
		{id: "rt-account", objectID: "obj-account", name: "Business", developerName: "Business", sobjectType: "Account"},
		{id: "rt-account-legacy", objectID: "obj-account", name: "Legacy", developerName: nil, sobjectType: "Account"},
		{id: "rt-account-deleted", objectID: "obj-account", name: "Deleted", developerName: "Deleted", sobjectType: "Account", deleted: true},
		{id: "rt-contact", objectID: "obj-contact", name: "External", developerName: "External", sobjectType: "Contact"},
	} {
		metadataReadExec(t, db, `INSERT INTO _System_RecordType
			(Id, ObjectId, Name, DeveloperName, IsActive, IsMaster, SobjectType, IsDeleted,
			 OwnerPack, CreatedDate, LastModifiedDate)
			VALUES (?, ?, ?, ?, 1, 0, ?, ?, '', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)`,
			row.id, row.objectID, row.name, row.developerName, row.sobjectType, row.deleted)
	}

	for _, row := range []struct {
		id         string
		objectName string
		access     string
		deleted    bool
	}{
		{id: "sharing-account", objectName: "Account", access: "Read"},
		{id: "sharing-contact", objectName: "Contact", access: "Edit"},
		{id: "sharing-deleted", objectName: "Account", access: "Edit", deleted: true},
	} {
		metadataReadExec(t, db, `INSERT INTO SharingRule
			(Id, ObjectApiName, Name, AccessLevel, IsDeleted, CreatedDate, LastModifiedDate)
			VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)`,
			row.id, row.objectName, row.id, row.access, row.deleted)
	}

	for _, row := range []struct {
		id           string
		recordTypeID string
		fieldID      string
		value        string
		sortOrder    int
		ownerPack    string
		customized   bool
		deleted      bool
	}{
		{id: "value-done", recordTypeID: "rt-account", fieldID: "fld-account-code", value: "Done", sortOrder: 20, ownerPack: "crm"},
		{id: "value-alpha", recordTypeID: "rt-account", fieldID: "fld-account-code", value: "Alpha", sortOrder: 10, customized: true},
		{id: "value-deleted", recordTypeID: "rt-account", fieldID: "fld-account-code", value: "Deleted", sortOrder: 1, deleted: true},
		{id: "value-other", recordTypeID: "rt-account", fieldID: "fld-contact-code", value: "Other", sortOrder: 1},
	} {
		metadataReadExec(t, db, `INSERT INTO _System_RecordTypePicklistValue
			(Id, RecordTypeId, FieldId, Value, IsActive, IsDefault, SortOrder, OwnerPack,
			 IsCustomized, IsDeleted, CreatedDate, LastModifiedDate)
			VALUES (?, ?, ?, ?, 1, 0, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)`,
			row.id, row.recordTypeID, row.fieldID, row.value, row.sortOrder,
			row.ownerPack, row.customized, row.deleted)
	}
}

func metadataReadExec(t *testing.T, db *sql.DB, statement string, args ...any) {
	t.Helper()
	if _, err := db.ExecContext(t.Context(), statement, args...); err != nil {
		t.Fatalf("seed metadata read fixture: %v", err)
	}
}