package persistence

import (
	"fmt"
	"strings"

	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/kernel/pkg/query"
	"github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/kernel/pkg/schemacatalog"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// preserveAuthorityOnlyPolicyUpdate renders the ordered upsert decision:
// existing authority wins; otherwise incoming authority wins; otherwise a
// customized row keeps its value when requested; otherwise the incoming value
// lands. Both authority branches precede customization so a stale customized
// canonical row still converges to the embedded catalog declaration.
func preserveAuthorityOnlyPolicyUpdate(preserveCustomized bool) query.Raw {
	incomingPolicy := fmt.Sprintf(
		"VALUES(`%s`)",
		constants.FieldSysField_WriteProtectionPolicy,
	)
	ordinaryUpdate := incomingPolicy
	if preserveCustomized {
		ordinaryUpdate = fmt.Sprintf(
			"IF(COALESCE(%s, '') = '%s', %s, IF(`%s` = 0 OR `%s` IS NULL, %s, `%s`))",
			incomingPolicy,
			constants.FieldWriteProtectionAuthorityOnly,
			incomingPolicy,
			constants.ColSysField_IsCustomized,
			constants.ColSysField_IsCustomized,
			incomingPolicy,
			constants.FieldSysField_WriteProtectionPolicy,
		)
	}
	return query.Raw(fmt.Sprintf(
		"IF(COALESCE(`%s`, '') = '%s', `%s`, %s)",
		constants.FieldSysField_WriteProtectionPolicy,
		constants.FieldWriteProtectionAuthorityOnly,
		constants.FieldSysField_WriteProtectionPolicy,
		ordinaryUpdate,
	))
}

func authorityOnlyCapabilityUpdate(field string) query.Raw {
	return query.Raw(fmt.Sprintf(
		"IF(COALESCE(`%s`, '') = '%s', 0, VALUES(`%s`))",
		constants.FieldSysField_WriteProtectionPolicy,
		constants.FieldWriteProtectionAuthorityOnly,
		field,
	))
}

func validateCatalogWriteProtectionProjection(tableName string, field *models.FieldMetadata) error {
	if field == nil {
		return apperrors.NewValidationError("field", "field metadata is required")
	}

	apiName := strings.TrimSpace(field.APIName)
	physicalName := strings.TrimSpace(field.PhysicalName)
	if physicalName == "" {
		physicalName = apiName
	}

	apiFact, apiFound, err := schemacatalog.SystemTableFieldWriteProtectionFor(tableName, apiName)
	if err != nil {
		return apperrors.NewInternalError("load canonical system-table write-protection policy", err)
	}
	physicalFact, physicalFound, err := schemacatalog.SystemTableFieldWriteProtectionFor(tableName, physicalName)
	if err != nil {
		return apperrors.NewInternalError("load canonical system-table write-protection policy", err)
	}

	proposed := strings.TrimSpace(field.WriteProtectionPolicy)
	var authorityFact schemacatalog.SystemTableFieldWriteProtection
	if apiFound && strings.TrimSpace(apiFact.Policy) == constants.FieldWriteProtectionAuthorityOnly {
		authorityFact = apiFact
	}
	if physicalFound && strings.TrimSpace(physicalFact.Policy) == constants.FieldWriteProtectionAuthorityOnly {
		if authorityFact.FieldName != "" &&
			(authorityFact.FieldName != physicalFact.FieldName ||
				authorityFact.PhysicalName != physicalFact.PhysicalName) {
			return catalogWriteProtectionAliasPairError(tableName, apiName, physicalName)
		}
		authorityFact = physicalFact
	}
	if authorityFact.FieldName != "" {
		if !strings.EqualFold(apiName, authorityFact.FieldName) ||
			!strings.EqualFold(physicalName, authorityFact.PhysicalName) {
			return catalogWriteProtectionAliasPairError(tableName, apiName, physicalName)
		}
		if proposed == constants.FieldWriteProtectionAuthorityOnly {
			return nil
		}
		return apperrors.NewValidationError(
			constants.FieldSysField_WriteProtectionPolicy,
			fmt.Sprintf(
				"field %s.%s must project catalog-owned write protection %q",
				tableName,
				apiName,
				constants.FieldWriteProtectionAuthorityOnly,
			),
		)
	}
	if proposed == constants.FieldWriteProtectionAuthorityOnly {
		return apperrors.NewValidationError(
			constants.FieldSysField_WriteProtectionPolicy,
			fmt.Sprintf(
				"field %s.%s cannot introduce catalog-owned write protection through generic schema persistence",
				tableName,
				apiName,
			),
		)
	}
	return nil
}

func catalogWriteProtectionAliasPairError(tableName, apiName, physicalName string) error {
	return apperrors.NewValidationError(
		constants.FieldSysField_WriteProtectionPolicy,
		fmt.Sprintf(
			"field %s.%s cannot project catalog write protection through mismatched logical/physical aliases %q/%q",
			tableName,
			apiName,
			apiName,
			physicalName,
		),
	)
}

func validateCatalogColumnWriteProtectionProjection(tableName string, column schema.ColumnDefinition) error {
	return validateCatalogWriteProtectionProjection(tableName, &models.FieldMetadata{
		APIName:               column.Name,
		PhysicalName:          column.PhysicalName,
		WriteProtectionPolicy: column.WriteProtectionPolicy,
	})
}

func rejectCatalogAuthorityColumnDrop(tableName, columnName string) error {
	fact, found, err := schemacatalog.SystemTableFieldWriteProtectionFor(tableName, columnName)
	if err != nil {
		return apperrors.NewInternalError("load canonical system-table write-protection policy", err)
	}
	if !found || strings.TrimSpace(fact.Policy) != constants.FieldWriteProtectionAuthorityOnly {
		return nil
	}
	return apperrors.NewValidationError(
		"columnName",
		fmt.Sprintf(
			"field %s.%s is catalog-owned authority metadata and cannot be removed through generic schema persistence",
			tableName,
			fact.FieldName,
		),
	)
}

func rejectCatalogAuthorityTableDrop(tableName string) error {
	facts, err := schemacatalog.SystemTableFieldWriteProtectionsFor(tableName)
	if err != nil {
		return apperrors.NewInternalError("load canonical system-table write-protection policy", err)
	}
	for _, fact := range facts {
		if strings.TrimSpace(fact.Policy) != constants.FieldWriteProtectionAuthorityOnly {
			continue
		}
		return apperrors.NewValidationError(
			"tableName",
			fmt.Sprintf(
				"table %s contains catalog-owned authority metadata and cannot be removed through generic schema persistence",
				tableName,
			),
		)
	}
	return nil
}

func validateCatalogTableWriteProtectionProjection(definition schema.TableDefinition) error {
	for _, column := range definition.Columns {
		if err := validateCatalogColumnWriteProtectionProjection(definition.TableName, column); err != nil {
			return err
		}
	}
	return nil
}

func (r *SchemaRepository) validateCatalogFieldContextWriteProtectionProjection(field schema.FieldWithContext) error {
	effective := field.Field
	if effective == nil {
		effective = r.buildFieldMetadataFromColumn(field.ColumnDefinition)
	} else if strings.TrimSpace(field.ColumnDefinition.Name) != "" &&
		fieldContextCarriesAuthorityOnlyProjection(field) {
		if err := validateFieldContextIdentityPair(field); err != nil {
			return err
		}
	}
	if err := validateCatalogWriteProtectionProjection(field.TableName, effective); err != nil {
		return err
	}
	return validateCatalogColumnWriteProtectionProjection(field.TableName, field.ColumnDefinition)
}

func fieldContextCarriesAuthorityOnlyProjection(field schema.FieldWithContext) bool {
	return strings.TrimSpace(field.Field.WriteProtectionPolicy) == constants.FieldWriteProtectionAuthorityOnly ||
		strings.TrimSpace(field.ColumnDefinition.WriteProtectionPolicy) == constants.FieldWriteProtectionAuthorityOnly
}

func validateFieldContextIdentityPair(field schema.FieldWithContext) error {
	rawFieldAPIName := field.Field.APIName
	rawFieldPhysicalName := field.Field.PhysicalName
	rawColumnName := field.ColumnDefinition.Name
	rawColumnPhysicalName := field.ColumnDefinition.PhysicalName
	fieldAPIName := strings.TrimSpace(rawFieldAPIName)
	columnName := strings.TrimSpace(rawColumnName)
	fieldPhysicalName := strings.TrimSpace(rawFieldPhysicalName)
	if fieldPhysicalName == "" {
		fieldPhysicalName = fieldAPIName
	}
	columnPhysicalName := strings.TrimSpace(rawColumnPhysicalName)
	if columnPhysicalName == "" {
		columnPhysicalName = columnName
	}
	rawIdentityIsCanonical := rawFieldAPIName == fieldAPIName &&
		rawFieldPhysicalName == strings.TrimSpace(rawFieldPhysicalName) &&
		rawColumnName == columnName &&
		rawColumnPhysicalName == strings.TrimSpace(rawColumnPhysicalName)
	if rawIdentityIsCanonical &&
		strings.EqualFold(fieldAPIName, columnName) &&
		strings.EqualFold(fieldPhysicalName, columnPhysicalName) {
		return nil
	}
	return apperrors.NewValidationError(
		constants.FieldSysField_WriteProtectionPolicy,
		fmt.Sprintf(
			"field %s.%s cannot project catalog write protection through mismatched field/column identities %q/%q and %q/%q",
			field.TableName,
			fieldAPIName,
			fieldAPIName,
			fieldPhysicalName,
			columnName,
			columnPhysicalName,
		),
	)
}