package persistence

import (
	"database/sql"
	"encoding/json"
	"fmt"

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/fieldcapabilities"
	"github.com/nexuscrm/shared/pkg/models"
)

func systemObjectColumns() []string {
	cols := append([]string(nil), constants.ColsSystemObject...)
	return append(cols,
		constants.FieldSysObject_KeyPrefix,
		constants.FieldSysObject_DefaultChildRows,
		constants.FieldSysObject_DefaultChildRowsCustomized,
		constants.FieldSysObject_ChildCloneRulesCustomized,
		constants.FieldSysObject_ExclusiveSiblingFields,
		constants.FieldSysObject_ExclusiveSiblingFieldsCustomized,
		constants.FieldSysObject_ReadSource,
	)
}

func (r *MetadataRepository) scanObject(row Scannable) (*models.ObjectMetadata, error) {
	var obj models.ObjectMetadata
	var description, icon, pathField, displayNameField, lockRule, keyPrefix, listFieldsJSON, childCloneRulesJSON, exclusiveSiblingFieldsJSON, defaultChildRowsJSON, accessPolicyJSON, readSourceJSON, appID, pluralLabel, themeColor, sharingModel, tableType, requiredWritePermission, requiredReadPermission, deleteMode, ownerID, createdByID, lastModifiedByID sql.NullString
	var isCustom, searchable, isDeleted bool
	var customSetting sql.NullBool
	var isCustomized sql.NullBool
	var listFieldsCustomized sql.NullBool
	var lockRuleCustomized sql.NullBool
	var defaultChildRowsCustomized sql.NullBool
	var childCloneRulesCustomized sql.NullBool
	var exclusiveSiblingFieldsCustomized sql.NullBool
	var enableHierarchySharing sql.NullBool
	var createdDateVal, lastModifiedDateVal interface{}

	// systemObjectColumns(): generated _System_Object columns plus KeyPrefix
	// from system_tables.json. The public repo currently lacks cmd/codegen, so
	// KeyPrefix is appended in a non-generated helper rather than editing the
	// generated slice by hand.
	err := row.Scan(
		&obj.ID, &obj.APIName, &tableType, &obj.Label, &pluralLabel, &appID, &icon, &description,
		&themeColor, &isCustom, &customSetting, &searchable, &sharingModel, &isCustomized, &enableHierarchySharing, &requiredWritePermission, &requiredReadPermission, &accessPolicyJSON, &deleteMode, &pathField, &displayNameField, &lockRule, &lockRuleCustomized, &listFieldsJSON, &listFieldsCustomized, &childCloneRulesJSON, &isDeleted,
		&ownerID, &createdByID, &lastModifiedByID, &createdDateVal, &lastModifiedDateVal,
		&keyPrefix, &defaultChildRowsJSON, &defaultChildRowsCustomized, &childCloneRulesCustomized, &exclusiveSiblingFieldsJSON, &exclusiveSiblingFieldsCustomized, &readSourceJSON,
	)
	if err != nil {
		return nil, err
	}

	if pluralLabel.Valid {
		obj.PluralLabel = pluralLabel.String
	}
	if description.Valid {
		val := description.String
		obj.Description = &val
	}
	if icon.Valid {
		obj.Icon = icon.String
	}
	if themeColor.Valid {
		val := themeColor.String
		obj.ThemeColor = &val
	}
	if appID.Valid {
		val := appID.String
		obj.AppID = &val
	}
	if keyPrefix.Valid && keyPrefix.String != "" {
		val := keyPrefix.String
		obj.KeyPrefix = &val
	}
	if pathField.Valid {
		val := pathField.String
		obj.PathField = &val
	}
	if displayNameField.Valid {
		val := displayNameField.String
		obj.DisplayNameField = &val
	}
	if lockRule.Valid {
		val := lockRule.String
		obj.LockRule = &val
	}
	if sharingModel.Valid {
		obj.SharingModel = constants.SharingModel(sharingModel.String)
	} else {
		obj.SharingModel = constants.SharingModelPrivate
	}
	if isCustomized.Valid {
		obj.IsCustomized = isCustomized.Bool
	}
	if listFieldsCustomized.Valid {
		obj.ListFieldsCustomized = listFieldsCustomized.Bool
	}
	if lockRuleCustomized.Valid {
		obj.LockRuleCustomized = lockRuleCustomized.Bool
	}
	if defaultChildRowsCustomized.Valid {
		obj.DefaultChildRowsCustomized = defaultChildRowsCustomized.Bool
	}
	if childCloneRulesCustomized.Valid {
		obj.ChildCloneRulesCustomized = childCloneRulesCustomized.Bool
	}
	if exclusiveSiblingFieldsCustomized.Valid {
		obj.ExclusiveSiblingFieldsCustomized = exclusiveSiblingFieldsCustomized.Bool
	}

	obj.IsCustom = isCustom
	obj.CustomSetting = customSetting.Valid && customSetting.Bool
	obj.Searchable = searchable
	if tableType.Valid {
		obj.TableType = constants.TableType(tableType.String)
	}
	switch constants.TableType(tableType.String) {
	case constants.TableTypeCustomObject, constants.TableTypeStandardObject:
		obj.IsSystem = false
	case constants.TableTypeSystemMetadata, constants.TableTypeSystemCore, constants.TableTypeSystemData, constants.TableTypeSystemJunction:
		obj.IsSystem = true
	default:
		obj.IsSystem = constants.IsSystemTable(obj.APIName)
	}

	// Unmarshal ListFields
	if listFieldsJSON.Valid {
		r.unmarshalJSON(listFieldsJSON.String, &obj.ListFields)
	}

	// Unmarshal ChildCloneRules (retained compatibility post-insert side
	// effects). Greenfield rows carry NULL here and obj.ChildCloneRules stays
	// nil, which is exactly what the persistence post-insert hook needs to
	// no-op. New create-from-source authoring is Flow-owned under issue #979.
	if childCloneRulesJSON.Valid && childCloneRulesJSON.String != "" {
		r.unmarshalJSON(childCloneRulesJSON.String, &obj.ChildCloneRules)
	}
	if defaultChildRowsJSON.Valid && defaultChildRowsJSON.String != "" {
		r.unmarshalJSON(defaultChildRowsJSON.String, &obj.DefaultChildRows)
	}
	if exclusiveSiblingFieldsJSON.Valid && exclusiveSiblingFieldsJSON.String != "" {
		r.unmarshalJSON(exclusiveSiblingFieldsJSON.String, &obj.ExclusiveSiblingFields)
	}

	if enableHierarchySharing.Valid {
		obj.EnableHierarchySharing = enableHierarchySharing.Bool
	}
	if requiredWritePermission.Valid {
		obj.RequiredWritePermission = requiredWritePermission.String
	}
	if requiredReadPermission.Valid {
		obj.RequiredReadPermission = requiredReadPermission.String
	}
	if accessPolicyJSON.Valid && accessPolicyJSON.String != "" {
		r.unmarshalJSON(accessPolicyJSON.String, &obj.AccessPolicy)
	}
	if readSourceJSON.Valid && readSourceJSON.String != "" {
		readSource, err := unmarshalObjectReadSourceConfig(readSourceJSON.String)
		if err != nil {
			return nil, fmt.Errorf("%s invalid readSource metadata: %w", obj.APIName, err)
		}
		obj.ReadSource = readSource
	}
	if deleteMode.Valid {
		obj.DeleteMode = deleteMode.String
	}
	obj.Fields = make([]models.FieldMetadata, 0)

	return &obj, nil
}

func unmarshalObjectReadSourceConfig(data string) (*models.ObjectReadSourceConfig, error) {
	var cfg models.ObjectReadSourceConfig
	if err := json.Unmarshal([]byte(data), &cfg); err != nil {
		return nil, err
	}
	return models.NormalizeObjectReadSourceConfig(&cfg)
}

func (r *MetadataRepository) scanField(row Scannable) (*models.FieldMetadata, string, error) {
	var field models.FieldMetadata
	var id, objectAPIName string
	var required, unique, isSystem, trackHistory, isNameField, isMasterDetail, isPolymorphic, createable, updateable sql.NullBool
	var options, referenceTo, writableReferenceTo, formula, returnType, formulaMode, formulaStorageMode, projectionConfig, defaultValue, siblingMaxParentField, helpText, controllingField, picklistDependency, rollupConfig, deleteRule, relationshipName, childRelationshipName, regex, regexMessage, validator, description, physicalName sql.NullString
	var minValue, maxValue sql.NullFloat64
	var minLength, maxLength, historyTrackingRevision sql.NullInt64

	// ColsSystemField: ID, ObjectID, APIName, Label, PhysicalName, Type, Required, IsUnique, IsSystem, IsNameField, Createable, Updateable, Indexed, Options, ReferenceTo, WritableReferenceTo, DeleteRule, Formula, ReturnType, FormulaMode, FormulaStorageMode, ProjectionConfig, DefaultValue, sibling_max_parent_field, HelpText, Description, TrackHistory, HistoryTrackingRevision, MinValue, MaxValue, MinLength, MaxLength, Regex, RegexMessage, Validator, ValidatorConfig, ControllingField, PicklistDependency, RollupConfig, IsMasterDetail, IsPolymorphic, RelationshipName, ChildRelationshipName, IsDeleted, OwnerID, CreatedByID, LastModifiedByID, CreatedDate, LastModifiedDate, IsCustomized, OwnerKind, OwnerPack, WriteProtectionPolicy, ReadProtectionPolicy, AutoNumberFormat, AutoNumberScope, AutoNumberStart, AutoNumberTokenRefs, AutoNumberCanonicalHash, AutoNumberCounterResetPolicy
	//
	// Issue #1582 RFC v1.6: Source / IsCompound / ComposedFrom columns are
	// no longer part of ColsSystemField — provenance is derived at read
	// time by fieldprovenance.ClassifySource / ClassifyCompound.
	var isDeleted bool
	var ownerID, createdByID, lastModifiedByID sql.NullString
	var createdDateVal, lastModifiedDateVal interface{}
	var indexed sql.NullBool
	var isCustomized sql.NullBool
	var ownerKind, ownerPack, writeProtectionPolicy, readProtectionPolicy sql.NullString
	var validatorConfig sql.NullString
	// AutoNumber* columns are scanned here for column-parity with ColsSystemField
	// (Wave 1 Lane B added 6 new columns). FieldMetadata does not carry them —
	// autonumber configuration is consumed by the dedicated
	// autonumber.MetadataSource which runs its own SELECT. These destinations
	// exist solely to keep scanField's Scan() argument count aligned with the
	// SELECT produced by query.From(TableField).Select(ColsSystemField).
	var autoNumberFormat, autoNumberScope, autoNumberTokenRefs, autoNumberCanonicalHash, autoNumberCounterResetPolicy sql.NullString
	var autoNumberStart sql.NullInt64

	err := row.Scan(
		&id, &objectAPIName, &field.APIName, &field.Label, &physicalName, &field.Type,
		&required, &unique, &isSystem, &isNameField,
		&createable, &updateable, &indexed, &options,
		&referenceTo, &writableReferenceTo, &deleteRule, &formula, &returnType, &formulaMode, &formulaStorageMode, &projectionConfig, &defaultValue, &siblingMaxParentField, &helpText, &description,
		&trackHistory, &historyTrackingRevision, &minValue, &maxValue, &minLength, &maxLength,
		&regex, &regexMessage, &validator, &validatorConfig, &controllingField,
		&picklistDependency, &rollupConfig, &isMasterDetail, &isPolymorphic, &relationshipName, &childRelationshipName,
		&isDeleted, &ownerID, &createdByID, &lastModifiedByID, &createdDateVal, &lastModifiedDateVal,
		&isCustomized, &ownerKind, &ownerPack,
		&writeProtectionPolicy, &readProtectionPolicy,
		&autoNumberFormat, &autoNumberScope, &autoNumberStart,
		&autoNumberTokenRefs, &autoNumberCanonicalHash, &autoNumberCounterResetPolicy,
	)
	if err != nil {
		return nil, "", err
	}

	// Id is the durable identity of the field row. Keep it on every hydrated
	// FieldMetadata value so consumers that join independently loaded metadata
	// facts (for example Catalog autonumber rules) observe the same identity.
	field.ID = id

	// Set Physical Name if present
	if physicalName.Valid {
		field.PhysicalName = physicalName.String
	}

	// Note: Field Aliasing is obsolete as Physical Names match API Names under Salesforce specifications

	field.Required = required.Bool
	field.IsUnique = unique.Bool
	field.IsSystem = isSystem.Bool
	field.TrackHistory = trackHistory.Bool
	field.IsNameField = isNameField.Bool
	field.Createable = !createable.Valid || createable.Bool // default true if NULL
	field.Updateable = !updateable.Valid || updateable.Bool // default true if NULL
	field.IsMasterDetail = isMasterDetail.Bool
	field.IsPolymorphic = isPolymorphic.Bool
	// Source / IsCompound / ComposedFrom are no longer stored — see scanField
	// header comment. Derived at read time by fieldprovenance.
	_ = isCustomized            // scanned but not mapped to FieldMetadata
	_ = historyTrackingRevision // internal CAS state, not part of FieldMetadata
	if ownerKind.Valid {
		field.OwnerKind = ownerKind.String
	}
	if ownerPack.Valid {
		field.OwnerPack = &ownerPack.String
	}
	if writeProtectionPolicy.Valid {
		field.WriteProtectionPolicy = writeProtectionPolicy.String
	}
	// Catalog snapshots are not the only metadata read path. The legacy schema
	// reader feeds PermissionService directly, so it must apply the same generic
	// narrowing rule as the mutation projection and never advertise a capability
	// wider than the write guard. Existing persisted capabilities remain the
	// authority; the policy can only remove access and unknown values fail closed.
	field.Createable, field.Updateable = fieldcapabilities.ApplyWriteProtectionPolicy(
		field.Createable,
		field.Updateable,
		field.WriteProtectionPolicy,
	)
	if readProtectionPolicy.Valid {
		field.ReadProtectionPolicy = readProtectionPolicy.String
	}
	// AutoNumber* values are scanned for column-parity but consumed elsewhere
	// (autonumber.MetadataSource). Blank here keeps the compiler quiet while
	// documenting that these columns intentionally do not populate FieldMetadata.
	_ = autoNumberFormat
	_ = autoNumberScope
	_ = autoNumberStart
	_ = autoNumberTokenRefs
	_ = autoNumberCanonicalHash
	_ = autoNumberCounterResetPolicy

	if formula.Valid {
		field.Formula = &formula.String
	}
	if formulaMode.Valid {
		field.FormulaMode = &formulaMode.String
	}
	if formulaStorageMode.Valid {
		field.FormulaStorageMode = &formulaStorageMode.String
	}
	if projectionConfig.Valid {
		var pc models.FieldProjectionConfig
		r.unmarshalJSON(projectionConfig.String, &pc)
		field.ProjectionConfig = &pc
	}
	if defaultValue.Valid {
		field.DefaultValue = &defaultValue.String
	}
	if siblingMaxParentField.Valid && siblingMaxParentField.String != "" {
		parentField := siblingMaxParentField.String
		field.SiblingMaxParentField = &parentField
	}
	if helpText.Valid {
		field.HelpText = &helpText.String
	}
	if controllingField.Valid {
		field.ControllingField = &controllingField.String
	}
	if relationshipName.Valid {
		field.RelationshipName = &relationshipName.String
	}
	if childRelationshipName.Valid {
		field.ChildRelationshipName = &childRelationshipName.String
	}
	if regex.Valid {
		field.Regex = &regex.String
	}
	if regexMessage.Valid {
		field.RegexMessage = &regexMessage.String
	}
	if validator.Valid {
		field.Validator = &validator.String
	}
	if validatorConfig.Valid {
		field.ValidatorConfig = &validatorConfig.String
	}
	if returnType.Valid {
		rt := models.FieldType(returnType.String)
		field.ReturnType = &rt
	}
	if deleteRule.Valid {
		dr := models.DeleteRule(deleteRule.String)
		field.DeleteRule = &dr
	}

	if minValue.Valid {
		field.MinValue = &minValue.Float64
	}
	if maxValue.Valid {
		field.MaxValue = &maxValue.Float64
	}
	if minLength.Valid {
		val := int(minLength.Int64)
		field.MinLength = &val
	}
	if maxLength.Valid {
		val := int(maxLength.Int64)
		field.MaxLength = &val
	}

	// Unmarshal JSON fields
	if referenceTo.Valid {
		r.unmarshalJSON(referenceTo.String, &field.ReferenceTo)
	}
	if writableReferenceTo.Valid {
		r.unmarshalJSON(writableReferenceTo.String, &field.WritableReferenceTo)
	}
	// Re-derive IsPolymorphic through the resolver with LifecyclePersistedRead.
	// Legacy Type=lookup multi-target rows do not infer polymorphic ownership
	// unless IsPolymorphic is already true (stamped by the bootstrap backfill
	// MigrateIsPolymorphicMultiTargetBackfill or explicitly set by admin).
	// Type=reference multi-target rows continue to infer ownership.
	field.IsPolymorphic = models.ResolveFieldRelationshipSemantics(&field, models.LifecyclePersistedRead).IsPolymorphic
	if options.Valid {
		r.unmarshalJSON(options.String, &field.Options)
	}
	if picklistDependency.Valid {
		r.unmarshalJSON(picklistDependency.String, &field.PicklistDependency)
	}
	if rollupConfig.Valid {
		var rc models.RollupConfig
		r.unmarshalJSON(rollupConfig.String, &rc)
		field.RollupConfig = &rc
		hydrateRollupResultMetadata(&field)
	}

	return &field, objectAPIName, nil
}

// hydrateRollupResultMetadata restores resolver-owned output facets from the
// canonical RollupConfig. _System_Field has no precision/scale columns, so
// the config is the durable source of those public API semantics.
func hydrateRollupResultMetadata(field *models.FieldMetadata) {
	if field == nil || field.RollupConfig == nil {
		return
	}
	if field.RollupConfig.ResultType != nil {
		resultType := *field.RollupConfig.ResultType
		field.ReturnType = &resultType
	}
	field.Precision = cloneRollupResultInt(field.RollupConfig.ResultPrecision)
	field.Scale = cloneRollupResultInt(field.RollupConfig.ResultScale)
}

func cloneRollupResultInt(value *int) *int {
	if value == nil {
		return nil
	}
	cloned := *value
	return &cloned
}