package persistence

import (
	"context"
	"fmt"
	"strings"

	pkgerrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/kernel/pkg/formula"
	"github.com/nexuscrm/kernel/pkg/ports"
	domainrls "github.com/nexuscrm/kernel/pkg/rls"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/formulastorage"
	"github.com/nexuscrm/shared/pkg/models"
)

func resolveQueryReadTarget(ctx context.Context, schema *models.ObjectMetadata, viewResolver ports.ViewResolver) (string, error) {
	if schema == nil {
		return "", pkgerrors.NewValidationError("object", "object metadata is required")
	}

	baseTable, err := schema.ResolveReadSourceTable()
	if err != nil {
		return "", pkgerrors.NewValidationError("readSource", err.Error())
	}

	if viewResolver == nil {
		return baseTable, nil
	}

	resolved := strings.TrimSpace(viewResolver.ResolveTableName(ctx, schema))
	if resolved == "" || strings.EqualFold(resolved, schema.APIName) {
		return baseTable, nil
	}

	// readSource + formula-view composition remains fail-closed until the full
	// query path can resolve logical field names safely against routed views.
	if !strings.EqualFold(baseTable, schema.APIName) {
		return "", pkgerrors.NewValidationError(
			"readSource",
			fmt.Sprintf("%s readSource cannot be composed with formula view routing yet", schema.APIName),
		)
	}

	return resolved, nil
}

func resolveQueryReadTargetForRequest(ctx context.Context, schema *models.ObjectMetadata, viewResolver ports.ViewResolver, req models.QueryRequest, rawSelectFields []string, selectedFields []string, rlsResult *domainrls.Result) (string, error) {
	return resolveQueryReadTargetForRequestWithMemberships(ctx, schema, viewResolver, req, rawSelectFields, selectedFields, rlsResult, nil)
}

func resolveQueryReadTargetForRequestWithMemberships(ctx context.Context, schema *models.ObjectMetadata, viewResolver ports.ViewResolver, req models.QueryRequest, rawSelectFields []string, selectedFields []string, rlsResult *domainrls.Result, memberships []ports.QueryCorrelatedMembershipPlan) (string, error) {
	baseTable, err := schema.ResolveReadSourceTable()
	if err != nil {
		return "", pkgerrors.NewValidationError("readSource", err.Error())
	}
	if viewResolver == nil || queryReadTargetCanUseBaseTableWithMemberships(schema, req, rawSelectFields, selectedFields, rlsResult, memberships) {
		return baseTable, nil
	}
	return resolveQueryReadTarget(ctx, schema, viewResolver)
}

func queryReadTargetCanUseBaseTableWithMemberships(schema *models.ObjectMetadata, req models.QueryRequest, rawSelectFields []string, selectedFields []string, rlsResult *domainrls.Result, memberships []ports.QueryCorrelatedMembershipPlan) bool {
	if !queryReadTargetCanUseBaseTable(schema, req, rawSelectFields, selectedFields, rlsResult) {
		return false
	}
	for _, membership := range memberships {
		if queryReadTargetBareFieldNeedsLogicalReadTarget(schema, membership.OuterField) {
			return false
		}
	}
	return true
}

func queryReadTargetCanUseBaseTable(schema *models.ObjectMetadata, req models.QueryRequest, rawSelectFields []string, selectedFields []string, rlsResult *domainrls.Result) bool {
	if schema == nil {
		return false
	}
	// Builder-managed projection/criteria/sort fields are emitted as bare
	// columns after logical->physical mapping. They need a real base-table column;
	// expression-backed readers (filterExpr/RLS criteria) can still map compound
	// fields through CONCAT_WS or recompile source expressions for the read target.
	if queryReadTargetNeedsLogicalProjectionField(schema, rawSelectFields) {
		return false
	}
	if queryReadTargetNeedsLogicalProjectionField(schema, selectedFields) {
		return false
	}
	if queryReadTargetNeedsLogicalClauseField(schema, req.Criteria, req.SortField, req.OrderBy, req.FilterExpr, req.ParentFilterPaths) {
		return false
	}
	baseTable, err := schema.ResolveReadSourceTable()
	if err != nil {
		return false
	}
	return rlsCurrentTableCanUseBaseTable(schema, baseTable, rlsResult)
}

func queryReadTargetNeedsLogicalClauseField(schema *models.ObjectMetadata, criteria []models.QueryCriterion, sortField string, orderBy []models.SortCriterion, filterExpr string, parentFilterPaths []models.QueryParentFilterPath) bool {
	for _, criterion := range criteria {
		if queryReadTargetBareFieldNeedsLogicalReadTarget(schema, criterion.Field) {
			return true
		}
	}
	if sortField != "" && sortField != "xMruAccessedAt" && queryReadTargetBareFieldNeedsLogicalReadTarget(schema, sortField) {
		return true
	}
	for _, sort := range orderBy {
		if queryReadTargetBareFieldNeedsLogicalReadTarget(schema, sort.Field) {
			return true
		}
	}
	if len(parentFilterPaths) > 0 {
		return queryReadTargetParentFilterExprNeedsView(schema, filterExpr)
	}
	return queryReadTargetFilterExprNeedsView(schema, filterExpr)
}

func queryReadTargetNeedsLogicalProjectionField(schema *models.ObjectMetadata, fields []string) bool {
	for _, field := range fields {
		if queryReadTargetBareFieldNeedsLogicalReadTarget(schema, field) {
			return true
		}
	}
	return false
}

func queryReadTargetFilterExprNeedsView(schema *models.ObjectMetadata, expr string) bool {
	if strings.TrimSpace(expr) == "" {
		return false
	}
	needsView := false
	_, _, err := formula.ToSQLWithMapper(expr, func(identifier string) string {
		if needsView {
			return identifier
		}
		if field := queryReadTargetField(schema, identifier); field != nil && !formulastorage.FieldRequiresPhysicalColumn(field) {
			needsView = true
		}
		return identifier
	})
	if err != nil {
		return true
	}
	return needsView
}

func queryReadTargetParentFilterExprNeedsView(schema *models.ObjectMetadata, expr string) bool {
	if strings.TrimSpace(expr) == "" {
		return false
	}
	fields, err := formula.CollectCurrentObjectFieldReferences(expr)
	if err != nil {
		return true
	}
	return queryReadTargetNeedsLogicalProjectionField(schema, fields)
}

func queryReadTargetBareFieldNeedsLogicalReadTarget(schema *models.ObjectMetadata, rawField string) bool {
	if strings.TrimSpace(rawField) == "*" {
		return schemaHasBareColumnUnsafeField(schema)
	}
	if strings.HasSuffix(strings.TrimSpace(rawField), "_Name") {
		baseField := strings.TrimSuffix(strings.TrimSpace(rawField), "_Name")
		if field := queryReadTargetField(schema, baseField); field != nil && field.Type == constants.FieldTypeReference {
			return true
		}
	}
	field := queryReadTargetField(schema, rawField)
	return field != nil && !fieldHasBarePhysicalColumn(field)
}

func schemaHasBareColumnUnsafeField(schema *models.ObjectMetadata) bool {
	if schema == nil {
		return false
	}
	for i := range schema.Fields {
		if !fieldHasBarePhysicalColumn(&schema.Fields[i]) {
			return true
		}
	}
	return false
}

func queryReadTargetField(schema *models.ObjectMetadata, rawField string) *models.FieldMetadata {
	if schema == nil {
		return nil
	}
	fieldName := strings.Trim(strings.TrimSpace(rawField), "`")
	if fieldName == "" {
		return nil
	}
	if idx := strings.LastIndex(fieldName, "."); idx >= 0 {
		fieldName = strings.Trim(fieldName[idx+1:], "`")
	}
	if strings.HasSuffix(fieldName, "_Name") {
		fieldName = strings.TrimSuffix(fieldName, "_Name")
	}
	return models.FindField(schema, fieldName)
}

func countSelectExpression(schema *models.ObjectMetadata, readTarget string) string {
	if schema == nil {
		return FuncCount
	}
	if models.FindField(schema, constants.FieldID) == nil {
		return FuncCount
	}
	return fmt.Sprintf("COUNT(DISTINCT `%s`.`%s`)", readTarget, readTargetColumnName(schema, readTarget, constants.FieldID))
}