package persistence

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

	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/kernel/pkg/schemautil"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// Helper methods to eliminate redundancy and ensuring consistency in metadata persistence

func (r *SchemaRepository) prepareObjectDBMap(obj *models.ObjectMetadata) (map[string]interface{}, error) {
	description := schemautil.ToNullString(obj.Description)
	var icon sql.NullString
	if obj.Icon != "" {
		icon = sql.NullString{String: obj.Icon, Valid: true}
	}
	appID := schemautil.ToNullString(obj.AppID)
	sharingModel := obj.SharingModel
	if sharingModel == "" {
		sharingModel = models.SharingModel(constants.SharingModelPrivate)
	}

	var listFields sql.NullString
	if obj.ListFields != nil {
		b, err := json.Marshal(obj.ListFields)
		if err != nil {
			return nil, apperrors.NewInternalError("failed to marshal list fields", err)
		}
		listFields = sql.NullString{String: string(b), Valid: true}
	}
	var defaultChildRows sql.NullString
	if len(obj.DefaultChildRows) > 0 {
		b, err := json.Marshal(obj.DefaultChildRows)
		if err != nil {
			return nil, apperrors.NewInternalError("failed to marshal default child rows", err)
		}
		defaultChildRows = sql.NullString{String: string(b), Valid: true}
	}
	var childCloneRules sql.NullString
	if len(obj.ChildCloneRules) > 0 {
		b, err := json.Marshal(obj.ChildCloneRules)
		if err != nil {
			return nil, apperrors.NewInternalError("failed to marshal child clone rules", err)
		}
		childCloneRules = sql.NullString{String: string(b), Valid: true}
	}
	var exclusiveSiblingFields sql.NullString
	if len(obj.ExclusiveSiblingFields) > 0 {
		b, err := json.Marshal(obj.ExclusiveSiblingFields)
		if err != nil {
			return nil, apperrors.NewInternalError("failed to marshal exclusive sibling fields", err)
		}
		exclusiveSiblingFields = sql.NullString{String: string(b), Valid: true}
	}
	var readSource sql.NullString
	if obj.ReadSource != nil {
		raw, err := models.CanonicalObjectReadSourceJSON(obj.ReadSource)
		if err != nil {
			return nil, apperrors.NewInternalError("failed to canonicalize read source", err)
		}
		readSource = sql.NullString{String: raw, Valid: true}
	}

	pathField := schemautil.ToNullString(obj.PathField)
	displayNameField := schemautil.ToNullString(obj.DisplayNameField)
	lockRule := schemautil.ToNullString(obj.LockRule)
	keyPrefix := schemautil.ToNullString(obj.KeyPrefix)
	themeColor := schemautil.ToNullString(obj.ThemeColor)
	var requiredWritePermission sql.NullString
	if obj.RequiredWritePermission != "" {
		requiredWritePermission = sql.NullString{String: obj.RequiredWritePermission, Valid: true}
	}
	var requiredReadPermission sql.NullString
	if obj.RequiredReadPermission != "" {
		requiredReadPermission = sql.NullString{String: obj.RequiredReadPermission, Valid: true}
	}
	var accessPolicy sql.NullString
	if obj.AccessPolicy != nil {
		b, err := json.Marshal(obj.AccessPolicy)
		if err != nil {
			return nil, apperrors.NewInternalError("failed to marshal access policy", err)
		}
		accessPolicy = sql.NullString{String: string(b), Valid: true}
	}

	tableType := constants.TableTypeStandardObject
	if obj.IsSystem || constants.IsSystemTable(obj.APIName) {
		tableType = constants.TableTypeSystemMetadata
	} else if obj.IsCustom {
		tableType = constants.TableTypeCustomObject
	}

	return map[string]interface{}{
		constants.FieldID:                                         obj.ID,
		constants.FieldSysObject_APIName:                          obj.APIName,
		constants.FieldSysObject_KeyPrefix:                        keyPrefix,
		constants.FieldSysObject_Label:                            obj.Label,
		constants.FieldSysObject_PluralLabel:                      obj.PluralLabel,
		constants.FieldSysObject_Icon:                             icon,
		constants.FieldSysObject_Description:                      description,
		constants.FieldSysObject_IsCustom:                         obj.IsCustom,
		constants.FieldSysObject_CustomSetting:                    obj.CustomSetting,
		constants.FieldSysObject_Searchable:                       obj.Searchable,
		constants.FieldSysObject_SharingModel:                     sharingModel,
		constants.FieldSysObject_IsCustomized:                     obj.IsCustomized,
		constants.FieldSysObject_EnableHierarchySharing:           obj.EnableHierarchySharing,
		constants.FieldSysObject_RequiredWritePermission:          requiredWritePermission,
		constants.FieldSysObject_RequiredReadPermission:           requiredReadPermission,
		constants.FieldSysObject_AccessPolicy:                     accessPolicy,
		constants.FieldSysObject_DeleteMode:                       obj.DeleteMode,
		constants.FieldSysObject_AppID:                            appID,
		constants.FieldSysObject_ListFields:                       listFields,
		constants.FieldSysObject_ListFieldsCustomized:             obj.ListFieldsCustomized,
		constants.FieldSysObject_DefaultChildRows:                 defaultChildRows,
		constants.FieldSysObject_DefaultChildRowsCustomized:       obj.DefaultChildRowsCustomized,
		constants.FieldSysObject_ChildCloneRules:                  childCloneRules,
		constants.FieldSysObject_ChildCloneRulesCustomized:        obj.ChildCloneRulesCustomized,
		constants.FieldSysObject_ExclusiveSiblingFields:           exclusiveSiblingFields,
		constants.FieldSysObject_ExclusiveSiblingFieldsCustomized: obj.ExclusiveSiblingFieldsCustomized,
		constants.FieldSysObject_PathField:                        pathField,
		constants.FieldSysObject_DisplayNameField:                 displayNameField,
		constants.FieldSysObject_LockRule:                         lockRule,
		constants.FieldSysObject_ReadSource:                       readSource,
		constants.FieldSysObject_LockRuleCustomized:               obj.LockRuleCustomized,
		constants.FieldSysObject_ThemeColor:                       themeColor,
		constants.FieldSysObject_TableType:                        tableType,
		constants.FieldCreatedDate:                                time.Now(),
		constants.FieldLastModifiedDate:                           time.Now(),
	}, nil
}

func (r *SchemaRepository) prepareFieldDBMap(field *models.FieldMetadata, objectID, fieldID string) (map[string]interface{}, error) {
	defaultValue := schemautil.ToNullString(field.DefaultValue)
	siblingMaxParentField := schemautil.ToNullString(field.SiblingMaxParentField)
	helpText := schemautil.ToNullString(field.HelpText)
	minLength := schemautil.ToNullInt64(field.MinLength)
	maxLength := schemautil.ToNullInt64(field.MaxLength)
	validator := schemautil.ToNullString(field.Validator)
	controllingField := schemautil.ToNullString(field.ControllingField)

	// ROOT FIX REMOVED: Legacy "Lookup (target)" parsing removed for fresh project.
	// We rely strictly on the `ReferenceTo` field being populated.
	referenceTo := field.ReferenceTo

	// DESIGN FIX REMOVED: Type normalization removed for fresh project.
	// Input type should already be in the correct format.
	normalizedType := field.Type
	referenceToJSON := schemautil.SliceToNullJSON(referenceTo)
	writableReferenceToJSON := schemautil.SliceToNullJSON(field.WritableReferenceTo)
	formula := schemautil.ToNullString(field.Formula)

	var returnTypeVal *string
	if field.ReturnType != nil {
		s := string(*field.ReturnType)
		returnTypeVal = &s
	}
	returnType := schemautil.ToNullString(returnTypeVal)

	var optionsJSON interface{}
	if field.Options != nil {
		b, err := json.Marshal(field.Options)
		if err != nil {
			return nil, apperrors.NewInternalError("failed to marshal options", err)
		}
		optionsJSON = string(b)
	}

	var rollupConfigJSON interface{}
	if field.RollupConfig != nil {
		b, err := json.Marshal(field.RollupConfig)
		if err != nil {
			return nil, apperrors.NewInternalError("failed to marshal rollup config", err)
		}
		rollupConfigJSON = string(b)
	}

	var projectionConfigJSON interface{}
	if field.ProjectionConfig != nil {
		b, err := json.Marshal(field.ProjectionConfig)
		if err != nil {
			return nil, apperrors.NewInternalError("failed to marshal projection config", err)
		}
		projectionConfigJSON = string(b)
	}

	var picklistDependencyJSON interface{}
	if field.PicklistDependency != nil {
		b, err := json.Marshal(field.PicklistDependency)
		if err != nil {
			return nil, apperrors.NewInternalError("failed to marshal picklist dependency", err)
		}
		picklistDependencyJSON = string(b)
	}

	var deleteRuleVal *string
	if field.DeleteRule != nil {
		s := string(*field.DeleteRule)
		deleteRuleVal = &s
	}
	deleteRule := schemautil.ToNullString(deleteRuleVal)
	relationshipName := schemautil.ToNullString(field.RelationshipName)
	childRelationshipName := schemautil.ToNullString(field.ChildRelationshipName)
	validatorConfig := schemautil.ToNullString(field.ValidatorConfig)

	// Source / IsCompound / ComposedFrom are no longer stored on _System_Field;
	// provenance is derived at read time by fieldprovenance.ClassifySource /
	// ClassifyCompound from the L1 manifests. Issue #1582 RFC v1.6.
	ownerKind := strings.TrimSpace(field.OwnerKind)
	if ownerKind == "" {
		ownerKind = constants.FieldOwnerKindRuntime
	}
	ownerPack := schemautil.ToNullString(field.OwnerPack)
	writeProtectionPolicy := schemautil.ToNullString(nil)
	if strings.TrimSpace(field.WriteProtectionPolicy) != "" {
		policy := strings.TrimSpace(field.WriteProtectionPolicy)
		writeProtectionPolicy = schemautil.ToNullString(&policy)
	}
	readProtectionPolicy := schemautil.ToNullString(nil)
	if strings.TrimSpace(field.ReadProtectionPolicy) != "" {
		policy := strings.TrimSpace(field.ReadProtectionPolicy)
		readProtectionPolicy = schemautil.ToNullString(&policy)
	}

	return map[string]interface{}{
		constants.FieldID:                             fieldID,
		constants.FieldObjectID:                       objectID,
		constants.FieldAPIName:                        field.APIName,
		constants.FieldSysField_Label:                 field.Label,
		constants.FieldType:                           normalizedType,
		constants.FieldSysField_Required:              field.Required,
		constants.FieldSysField_IsUnique:              field.IsUnique,
		constants.FieldSysField_DefaultValue:          defaultValue,
		constants.FieldSysField_HelpText:              helpText,
		constants.FieldSysField_IsSystem:              field.IsSystem,
		constants.FieldSysField_IsNameField:           field.IsNameField,
		constants.FieldSysField_Createable:            field.Createable,
		constants.FieldSysField_Updateable:            field.Updateable,
		constants.FieldSysField_Options:               optionsJSON,
		constants.FieldSysField_MinLength:             minLength,
		constants.FieldSysField_MaxLength:             maxLength,
		constants.FieldReferenceTo:                    referenceToJSON,
		constants.FieldSysField_WritableReferenceTo:   writableReferenceToJSON,
		constants.FieldSysField_Formula:               formula,
		constants.FieldSysField_ReturnType:            returnType,
		constants.FieldSysField_FormulaMode:           schemautil.ToNullString(field.FormulaMode),
		constants.FieldSysField_FormulaStorageMode:    schemautil.ToNullString(field.FormulaStorageMode),
		constants.FieldSysField_ProjectionConfig:      projectionConfigJSON,
		constants.FieldSysField_Validator:             validator,
		constants.FieldSysField_SiblingMaxParentField: siblingMaxParentField,
		constants.FieldSysField_ValidatorConfig:       validatorConfig,
		constants.FieldSysField_ControllingField:      controllingField,
		constants.FieldSysField_PicklistDependency:    picklistDependencyJSON,
		constants.FieldSysField_RollupConfig:          rollupConfigJSON,
		constants.FieldSysField_IsMasterDetail:        field.IsMasterDetail,
		constants.FieldSysField_IsPolymorphic:         field.IsPolymorphic,
		constants.FieldSysField_DeleteRule:            deleteRule,
		constants.FieldSysField_RelationshipName:      relationshipName,
		constants.FieldSysField_ChildRelationshipName: childRelationshipName,
		constants.ColSysField_PhysicalName:            schemautil.ToNullString(&field.PhysicalName),
		constants.FieldCreatedDate:                    time.Now(),
		constants.FieldLastModifiedDate:               time.Now(),
		constants.FieldSysField_OwnerKind:             ownerKind,
		constants.FieldSysField_OwnerPack:             ownerPack,
		constants.FieldSysField_TrackHistory:          field.TrackHistory,
		constants.FieldSysField_WriteProtectionPolicy: writeProtectionPolicy,
		constants.FieldSysField_ReadProtectionPolicy:  readProtectionPolicy,
	}, nil
}

func (r *SchemaRepository) prepareObjectDBValues(obj *models.ObjectMetadata) ([]interface{}, error) {
	description := schemautil.ToNullString(obj.Description)
	var icon sql.NullString
	if obj.Icon != "" {
		icon = sql.NullString{String: obj.Icon, Valid: true}
	}
	appID := schemautil.ToNullString(obj.AppID)
	sharingModel := obj.SharingModel
	if sharingModel == "" {
		sharingModel = models.SharingModel(constants.SharingModelPrivate)
	}

	var listFields sql.NullString
	if obj.ListFields != nil {
		b, err := json.Marshal(obj.ListFields)
		if err != nil {
			return nil, apperrors.NewInternalError("failed to marshal list fields", err)
		}
		listFields = sql.NullString{String: string(b), Valid: true}
	}

	pathField := schemautil.ToNullString(obj.PathField)
	displayNameField := schemautil.ToNullString(obj.DisplayNameField)
	lockRule := schemautil.ToNullString(obj.LockRule)
	readSource := schemautil.ToNullString(nil)
	if obj.ReadSource != nil {
		raw, err := models.CanonicalObjectReadSourceJSON(obj.ReadSource)
		if err != nil {
			return nil, apperrors.NewInternalError("failed to canonicalize read source", err)
		}
		readSource = sql.NullString{String: raw, Valid: true}
	}
	themeColor := schemautil.ToNullString(obj.ThemeColor)
	var requiredWritePermission sql.NullString
	if obj.RequiredWritePermission != "" {
		requiredWritePermission = sql.NullString{String: obj.RequiredWritePermission, Valid: true}
	}
	var requiredReadPermission sql.NullString
	if obj.RequiredReadPermission != "" {
		requiredReadPermission = sql.NullString{String: obj.RequiredReadPermission, Valid: true}
	}
	var accessPolicy sql.NullString
	if obj.AccessPolicy != nil {
		b, err := json.Marshal(obj.AccessPolicy)
		if err != nil {
			return nil, apperrors.NewInternalError("failed to marshal access policy", err)
		}
		accessPolicy = sql.NullString{String: string(b), Valid: true}
	}

	tableType := constants.TableTypeStandardObject
	if obj.IsSystem || constants.IsSystemTable(obj.APIName) {
		tableType = constants.TableTypeSystemMetadata
	} else if obj.IsCustom {
		tableType = constants.TableTypeCustomObject
	}
	// For core objects bootstrapped via this path, we default to metadata/custom.
	// Real system core objects are often inserted via dedicated bootstrap scripts, but if using this, defaulting to metadata is safer than null.

	// Order matches ObjectInsertQuery
	return []interface{}{
		obj.ID, obj.APIName, obj.Label, obj.PluralLabel, icon, description,
		obj.IsCustom, obj.CustomSetting, obj.Searchable, sharingModel, obj.EnableHierarchySharing, requiredWritePermission, requiredReadPermission,
		accessPolicy, appID, listFields, obj.ListFieldsCustomized, pathField, displayNameField, lockRule, readSource, themeColor, tableType,
	}, nil
}

func (r *SchemaRepository) getObjectInsertQuery() string {
	columns := []string{
		constants.ColID, constants.FieldSysObject_APIName, constants.FieldSysObject_Label, constants.FieldSysObject_PluralLabel,
		constants.FieldSysObject_Icon, constants.FieldSysObject_Description, constants.FieldSysObject_IsCustom,
		constants.FieldSysObject_CustomSetting,
		constants.FieldSysObject_Searchable,
		constants.FieldSysObject_SharingModel, constants.FieldSysObject_EnableHierarchySharing, constants.FieldSysObject_RequiredWritePermission, constants.FieldSysObject_RequiredReadPermission,
		constants.FieldSysObject_AccessPolicy,
		constants.FieldSysObject_AppID, constants.FieldSysObject_ListFields, constants.FieldSysObject_ListFieldsCustomized,
		constants.FieldSysObject_PathField, constants.FieldSysObject_DisplayNameField, constants.FieldSysObject_LockRule, constants.FieldSysObject_ReadSource, constants.FieldSysObject_ThemeColor, constants.FieldSysObject_TableType,
		constants.ColCreatedDate, constants.ColLastModifiedDate,
	}
	cols := strings.Join(columns, ", ")
	valuePlaceholders := strings.TrimSuffix(strings.Repeat("?, ", len(columns)-2), ", ")

	updates := strings.Join([]string{
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_Label, constants.FieldSysObject_Label),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_PluralLabel, constants.FieldSysObject_PluralLabel),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_Icon, constants.FieldSysObject_Icon),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_Description, constants.FieldSysObject_Description),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_CustomSetting, constants.FieldSysObject_CustomSetting),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_Searchable, constants.FieldSysObject_Searchable),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_SharingModel, constants.FieldSysObject_SharingModel),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_EnableHierarchySharing, constants.FieldSysObject_EnableHierarchySharing),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_RequiredWritePermission, constants.FieldSysObject_RequiredWritePermission),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_RequiredReadPermission, constants.FieldSysObject_RequiredReadPermission),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_AccessPolicy, constants.FieldSysObject_AccessPolicy),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_AppID, constants.FieldSysObject_AppID),
		fmt.Sprintf("%s = IF(%s = 0 OR %s IS NULL, VALUES(%s), %s)", constants.FieldSysObject_ListFields, constants.FieldSysObject_ListFieldsCustomized, constants.FieldSysObject_ListFieldsCustomized, constants.FieldSysObject_ListFields, constants.FieldSysObject_ListFields),
		fmt.Sprintf("%s = IF(%s = 0 OR %s IS NULL, VALUES(%s), %s)", constants.FieldSysObject_ListFieldsCustomized, constants.FieldSysObject_ListFieldsCustomized, constants.FieldSysObject_ListFieldsCustomized, constants.FieldSysObject_ListFieldsCustomized, constants.FieldSysObject_ListFieldsCustomized),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_PathField, constants.FieldSysObject_PathField),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_DisplayNameField, constants.FieldSysObject_DisplayNameField),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_LockRule, constants.FieldSysObject_LockRule),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_ReadSource, constants.FieldSysObject_ReadSource),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_ThemeColor, constants.FieldSysObject_ThemeColor),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_TableType, constants.FieldSysObject_TableType),
		fmt.Sprintf("%s = NOW()", constants.ColLastModifiedDate),
	}, ", ")

	return fmt.Sprintf(`%s %s (%s) %s (%s, %s, %s)
	%s %s`, KeywordInsertInto, constants.TableObject, cols, KeywordValues, valuePlaceholders, FuncNow,
		FuncNow, KeywordOnDuplicate, updates)
}

func (r *SchemaRepository) getObjectStrictInsertQuery() string {
	columns := []string{
		constants.ColID, constants.FieldSysObject_APIName, constants.FieldSysObject_Label, constants.FieldSysObject_PluralLabel,
		constants.FieldSysObject_Icon, constants.FieldSysObject_Description, constants.FieldSysObject_IsCustom,
		constants.FieldSysObject_CustomSetting,
		constants.FieldSysObject_Searchable,
		constants.FieldSysObject_SharingModel, constants.FieldSysObject_EnableHierarchySharing, constants.FieldSysObject_RequiredWritePermission, constants.FieldSysObject_RequiredReadPermission,
		constants.FieldSysObject_AccessPolicy,
		constants.FieldSysObject_AppID, constants.FieldSysObject_ListFields, constants.FieldSysObject_ListFieldsCustomized,
		constants.FieldSysObject_PathField, constants.FieldSysObject_DisplayNameField, constants.FieldSysObject_LockRule, constants.FieldSysObject_ReadSource, constants.FieldSysObject_ThemeColor, constants.FieldSysObject_TableType,
		constants.ColCreatedDate, constants.ColLastModifiedDate,
	}
	cols := strings.Join(columns, ", ")
	valuePlaceholders := strings.TrimSuffix(strings.Repeat("?, ", len(columns)-2), ", ")
	return fmt.Sprintf(`%s %s (%s) %s (%s, %s, %s)`,
		KeywordInsertInto, constants.TableObject, cols, KeywordValues, valuePlaceholders, FuncNow, FuncNow)
}