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"
	"github.com/nexuscrm/kernel/pkg/query"
	domainrls "github.com/nexuscrm/kernel/pkg/rls"
	"github.com/nexuscrm/shared/pkg/models"
)

func (r *QueryRepository) applyFindBaseLogic(ctx context.Context, b *query.Builder, tableSchema *models.ObjectMetadata, readTable string, req models.QueryRequest, correlatedMemberships []ports.QueryCorrelatedMembershipPlan) error {
	if err := applyParentFilterJoins(b, tableSchema, readTable, req.ParentFilterPaths, req.ScanAll); err != nil {
		return err
	}

	if !req.ScanAll && schemaHasIsDeleted(tableSchema) {
		b.ExcludeDeleted()
	}

	for _, c := range req.Criteria {
		if isValidFieldName(c.Field) && isValidFilterOp(c.Op) {
			opUpper := strings.ToUpper(strings.TrimSpace(c.Op))
			if parentExpression, ok, err := parentFilterStructuredClauseExpression(tableSchema, readTable, req.ParentFilterPaths, c.Field, "Criteria"); err != nil {
				return err
			} else if ok {
				if err := applyParentFilterStructuredCriterion(b, parentExpression, opUpper, c.Val, c.Field, "Criteria"); err != nil {
					return err
				}
				continue
			}
			resolvedField := readTargetColumnName(tableSchema, readTable, c.Field)
			if opUpper == "IN" || opUpper == "NOT IN" {
				vals := structuredCriterionListValues(c.Val)
				if opUpper == "IN" {
					b.WhereIn(resolvedField, vals)
				} else {
					placeholders := make([]string, len(vals))
					for j := range vals {
						placeholders[j] = "?"
					}
					col := fmt.Sprintf("`%s`.`%s`", readTable, resolvedField)
					b.WhereRaw(fmt.Sprintf("%s NOT IN (%s)", col, strings.Join(placeholders, ",")), vals)
				}
			} else {
				b.WhereOp(resolvedField, c.Op, c.Val)
			}
		}
	}

	if req.FilterExpr != "" {
		mapper := readTargetFieldMapperForTable(tableSchema, readTable)
		allowMemberPaths := len(req.ParentFilterPaths) > 0
		var unsupportedParentPath string
		if allowMemberPaths {
			mapper = parentFilterFieldMapperForTable(tableSchema, readTable, req.ParentFilterPaths, func(identifier string) {
				if unsupportedParentPath == "" {
					unsupportedParentPath = identifier
				}
			})
		}
		sqlWhere, args, err := formula.ToSQLWithOptions(req.FilterExpr, formula.SQLOptions{
			AllowMemberPaths: allowMemberPaths,
			Mapper:           mapper,
			FunctionLowerer:  parentFilterNullBlankFunctionLowerer(req.ParentFilterPaths),
		})
		if err != nil {
			return pkgerrors.NewValidationError("filterExpr", fmt.Sprintf("invalid filter expression: %v", err))
		}
		if unsupportedParentPath != "" {
			return pkgerrors.NewValidationError("filterExpr", fmt.Sprintf("unsupported relationship field filter path %q", unsupportedParentPath))
		}
		if err := validateParentFilterNullBlankSQL(sqlWhere, req.ParentFilterPaths); err != nil {
			return err
		}
		if err := validateParentFilterListSQL(sqlWhere, req.ParentFilterPaths); err != nil {
			return err
		}
		sqlWhere = requireMatchedParentForNullSQL(sqlWhere, req.ParentFilterPaths)
		if sqlWhere != "" {
			if req.MRUUserID != "" {
				sqlWhere = replaceIdentifiers(sqlWhere, buildQualifiedFieldReplacements(tableSchema, readTable))
			}
			b.WhereRaw(sqlWhere, args)
		}
	}

	return r.applyCorrelatedMemberships(ctx, b, tableSchema, readTable, correlatedMemberships)
}

func (r *QueryRepository) applyCorrelatedMemberships(ctx context.Context, b *query.Builder, outerSchema *models.ObjectMetadata, outerReadTable string, memberships []ports.QueryCorrelatedMembershipPlan) error {
	for _, membership := range memberships {
		sqlWhere, args, err := r.correlatedMembershipSQL(ctx, outerSchema, outerReadTable, membership)
		if err != nil {
			return err
		}
		b.WhereRaw(sqlWhere, args)
	}
	return nil
}

func (r *QueryRepository) correlatedMembershipSQL(ctx context.Context, outerSchema *models.ObjectMetadata, outerReadTable string, membership ports.QueryCorrelatedMembershipPlan) (string, []interface{}, error) {
	outerExpr, err := correlatedMembershipColumnExpression(outerSchema, outerReadTable, membership.OuterField, "outer field")
	if err != nil {
		return "", nil, err
	}
	innerSQL, innerParams, err := r.correlatedMembershipInnerSQL(ctx, membership)
	if err != nil {
		return "", nil, err
	}
	if membership.Negate {
		return fmt.Sprintf("(%s IS NULL OR %s NOT IN (%s))", outerExpr, outerExpr, innerSQL), innerParams, nil
	}
	return fmt.Sprintf("%s IN (%s)", outerExpr, innerSQL), innerParams, nil
}

func (r *QueryRepository) correlatedMembershipInnerSQL(ctx context.Context, membership ports.QueryCorrelatedMembershipPlan) (string, []interface{}, error) {
	if membership.InnerSchema == nil {
		return "", nil, pkgerrors.NewValidationError("filterExpr", "correlated membership requires inner metadata")
	}
	innerReq := membership.InnerRequest
	innerReadRLSResult, err := r.prepareRLSForRead(ctx, membership.InnerRLSResult, innerReq.ScanAll)
	if err != nil {
		return "", nil, err
	}
	innerReadTable, err := resolveQueryReadTargetForRequest(ctx, membership.InnerSchema, r.viewResolver, innerReq, innerReq.SelectFields, innerReq.SelectFields, innerReadRLSResult)
	if err != nil {
		return "", nil, err
	}
	if err := validateParentFilterReadTarget(membership.InnerSchema, innerReadTable, innerReq.ParentFilterPaths); err != nil {
		return "", nil, err
	}
	innerReadRLSResult, err = prepareRLSForReadTarget(innerReadRLSResult, membership.InnerSchema, innerReadTable)
	if err != nil {
		return "", nil, err
	}

	innerSelectExpr, err := correlatedMembershipColumnExpression(membership.InnerSchema, innerReadTable, membership.InnerSelectField, "inner selected field")
	if err != nil {
		return "", nil, err
	}
	innerBuilder := queryBuilderForReadTarget(membership.InnerSchema, innerReadTable).SkipID()
	innerBuilder.AddSelectRaw(innerSelectExpr)
	if err := r.applyFindBaseLogic(ctx, innerBuilder, membership.InnerSchema, innerReadTable, innerReq, nil); err != nil {
		return "", nil, err
	}
	if membership.Negate {
		innerBuilder.WhereRaw(fmt.Sprintf("%s IS NOT NULL", innerSelectExpr), nil)
	}
	if rlsWhere := domainrls.ToSQLFilter(innerReadRLSResult, innerReadTable); rlsWhere != "" {
		rlsWhere = qualifyParentFilterRootRLSFilter(rlsWhere, membership.InnerSchema, innerReadTable, innerReq.ParentFilterPaths)
		innerBuilder.WhereRaw(rlsWhere, nil)
	}
	if innerReq.Limit > 0 {
		innerBuilder.Limit(innerReq.Limit)
	}
	query := innerBuilder.Build()
	return query.SQL, query.Params, nil
}

func correlatedMembershipColumnExpression(schema *models.ObjectMetadata, readTable string, field string, label string) (string, error) {
	column := strings.Trim(readTargetColumnName(schema, readTable, field), "`")
	if !isSafeParentFilterIdentifier(readTable) || !isSafeParentFilterIdentifier(column) {
		return "", pkgerrors.NewValidationError("filterExpr", fmt.Sprintf("correlated membership requires a safe %s: %s", label, field))
	}
	return fmt.Sprintf("`%s`.`%s`", readTable, column), nil
}