package persistence

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

	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"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 := ToNullString(obj.Description)
	var icon sql.NullString
	if obj.Icon != "" {
		icon = sql.NullString{String: obj.Icon, Valid: true}
	}
	appID := ToNullString(obj.AppID)
	sharingModel := obj.SharingModel
	if sharingModel == "" {
		sharingModel = models.SharingModel(constants.SharingModelPrivate)
	}

	var listFields sql.NullString
	if len(obj.ListFields) > 0 {
		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 := ToNullString(obj.PathField)
	themeColor := 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_Label:                   obj.Label,
		constants.FieldSysObject_PluralLabel:             obj.PluralLabel,
		constants.FieldSysObject_Icon:                    icon,
		constants.FieldSysObject_Description:             description,
		constants.FieldSysObject_IsCustom:                obj.IsCustom,
		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_PathField:               pathField,
		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 := ToNullString(field.DefaultValue)
	helpText := ToNullString(field.HelpText)
	minLength := ToNullInt64(field.MinLength)
	maxLength := ToNullInt64(field.MaxLength)
	validator := ToNullString(field.Validator)
	controllingField := 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 := SliceToNullJSON(referenceTo)
	formula := ToNullString(field.Formula)

	var returnTypeVal *string
	if field.ReturnType != nil {
		s := string(*field.ReturnType)
		returnTypeVal = &s
	}
	returnType := 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 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 := ToNullString(deleteRuleVal)
	relationshipName := ToNullString(field.RelationshipName)

	// 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.

	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_Formula:            formula,
		constants.FieldSysField_ReturnType:         returnType,
		constants.FieldSysField_FormulaMode:        ToNullString(field.FormulaMode),
		constants.FieldSysField_Validator:          validator,
		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.ColSysField_PhysicalName:         ToNullString(&field.PhysicalName),
		constants.FieldCreatedDate:                 time.Now(),
		constants.FieldLastModifiedDate:            time.Now(),
	}, nil
}

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

	var listFields sql.NullString
	if len(obj.ListFields) > 0 {
		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 := ToNullString(obj.PathField)
	themeColor := 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.APIName, obj.Label, obj.PluralLabel, icon, description,
		obj.IsCustom, obj.Searchable, sharingModel, obj.EnableHierarchySharing, requiredWritePermission, requiredReadPermission,
		accessPolicy, appID, listFields, pathField, themeColor, tableType,
	}, nil
}

func (r *SchemaRepository) prepareFieldDBValues(field *models.FieldMetadata) ([]interface{}, error) {
	defaultValue := ToNullString(field.DefaultValue)
	helpText := ToNullString(field.HelpText)
	minLength := ToNullInt64(field.MinLength)
	maxLength := ToNullInt64(field.MaxLength)
	referenceTo := SliceToNullJSON(field.ReferenceTo)
	formula := ToNullString(field.Formula)

	var returnTypeVal *string
	if field.ReturnType != nil {
		s := string(*field.ReturnType)
		returnTypeVal = &s
	}
	returnType := 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 deleteRuleVal *string
	if field.DeleteRule != nil {
		s := string(*field.DeleteRule)
		deleteRuleVal = &s
	}
	deleteRule := ToNullString(deleteRuleVal)
	relationshipName := ToNullString(field.RelationshipName)

	// Convert bools to ints for compatibility
	isSystem := 0
	if field.IsSystem {
		isSystem = 1
	}
	isNameField := 0
	if field.IsNameField {
		isNameField = 1
	}
	required := 0
	if field.Required {
		required = 1
	}
	unique := 0
	if field.IsUnique {
		unique = 1
	}
	isMasterDetail := 0
	if field.IsMasterDetail {
		isMasterDetail = 1
	}
	isPolymorphic := 0
	if field.IsPolymorphic {
		isPolymorphic = 1
	}

	// Order matches FieldInsertQuery
	return []interface{}{
		field.APIName, field.Label, field.Type, required, unique,
		defaultValue, helpText, isSystem, isNameField, optionsJSON,
		minLength, maxLength, referenceTo, formula, returnType, rollupConfigJSON,
		isMasterDetail, isPolymorphic, deleteRule, relationshipName,
		ToNullString(&field.PhysicalName),
	}, nil
}

func (r *SchemaRepository) getObjectInsertQuery() string {
	cols := strings.Join([]string{
		constants.ColID, constants.FieldSysObject_APIName, constants.FieldSysObject_Label, constants.FieldSysObject_PluralLabel,
		constants.FieldSysObject_Icon, constants.FieldSysObject_Description, constants.FieldSysObject_IsCustom,
		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_PathField, constants.FieldSysObject_ThemeColor, constants.FieldSysObject_TableType,
		constants.ColCreatedDate, constants.ColLastModifiedDate,
	}, ", ")

	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_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 = VALUES(%s)", constants.FieldSysObject_ListFields, constants.FieldSysObject_ListFields),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysObject_PathField, constants.FieldSysObject_PathField),
		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`, KeywordInsertInto, constants.TableObject, cols, KeywordValues, FuncNow, FuncNow,
		KeywordOnDuplicate, updates)
}

func (r *SchemaRepository) getObjectStrictInsertQuery() string {
	cols := strings.Join([]string{
		constants.ColID, constants.FieldSysObject_APIName, constants.FieldSysObject_Label, constants.FieldSysObject_PluralLabel,
		constants.FieldSysObject_Icon, constants.FieldSysObject_Description, constants.FieldSysObject_IsCustom,
		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_PathField, constants.FieldSysObject_ThemeColor, constants.FieldSysObject_TableType,
		constants.ColCreatedDate, constants.ColLastModifiedDate,
	}, ", ")
	return fmt.Sprintf(`%s %s (%s) %s (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, %s, %s)`,
		KeywordInsertInto, constants.TableObject, cols, KeywordValues, FuncNow, FuncNow)
}

func (r *SchemaRepository) getFieldInsertQuery() string {
	cols := strings.Join([]string{
		constants.ColID, constants.FieldObjectID, constants.FieldAPIName, constants.FieldSysField_Label, constants.FieldType,
		constants.FieldSysField_Required, constants.FieldSysField_IsUnique, constants.FieldSysField_DefaultValue, constants.FieldSysField_HelpText,
		constants.FieldSysField_IsSystem, constants.FieldSysField_IsNameField, constants.FieldSysField_Options,
		constants.FieldSysField_MinLength, constants.FieldSysField_MaxLength, constants.FieldReferenceTo,
		constants.FieldSysField_Formula, constants.FieldSysField_ReturnType, constants.FieldSysField_FormulaMode, constants.FieldSysField_RollupConfig,
		constants.FieldSysField_IsMasterDetail, constants.FieldSysField_IsPolymorphic, constants.FieldSysField_DeleteRule,
		constants.FieldSysField_RelationshipName, constants.ColSysField_PhysicalName, constants.ColCreatedDate, constants.ColLastModifiedDate,
	}, ", ")

	updates := strings.Join([]string{
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_Label, constants.FieldSysField_Label),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldType, constants.FieldType),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_Required, constants.FieldSysField_Required),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_IsUnique, constants.FieldSysField_IsUnique),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_DefaultValue, constants.FieldSysField_DefaultValue),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_HelpText, constants.FieldSysField_HelpText),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_IsSystem, constants.FieldSysField_IsSystem),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_IsNameField, constants.FieldSysField_IsNameField),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_Options, constants.FieldSysField_Options),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_MinLength, constants.FieldSysField_MinLength),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_MaxLength, constants.FieldSysField_MaxLength),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldReferenceTo, constants.FieldReferenceTo),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_Formula, constants.FieldSysField_Formula),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_ReturnType, constants.FieldSysField_ReturnType),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_FormulaMode, constants.FieldSysField_FormulaMode),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_RollupConfig, constants.FieldSysField_RollupConfig),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_IsMasterDetail, constants.FieldSysField_IsMasterDetail),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_IsPolymorphic, constants.FieldSysField_IsPolymorphic),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_DeleteRule, constants.FieldSysField_DeleteRule),
		fmt.Sprintf("%s = VALUES(%s)", constants.FieldSysField_RelationshipName, constants.FieldSysField_RelationshipName),
		fmt.Sprintf("%s = NOW()", constants.ColLastModifiedDate),
	}, ", ")

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