package persistence

import (
	"context"
	"database/sql"
	"fmt"
	"strings"

	"github.com/nexuscrm/backend/internal/domain/ports"
	domainrls "github.com/nexuscrm/backend/internal/domain/rls"
	pkgerrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/backend/pkg/formula"
	"github.com/nexuscrm/backend/pkg/query"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/rs/zerolog/log"
)

// QueryRepository handles complex read operations (filtering, searching, analytics).
// Method implementations are split across domain-specific files:
//   - query_analytics.go — RunAnalytics, applyAnalyticsFilters, applyAnalyticsOperation, executeAndFormatAnalytics
//   - query_helpers.go   — shared helpers (resolvePhysicalName, newFieldMapper, schemaHasIsDeleted, isValidFieldName, etc.)
type QueryRepository struct {
	db           *sql.DB
	viewResolver ports.ViewResolver
}

// NewQueryRepository creates a new QueryRepository
func NewQueryRepository(db *sql.DB) *QueryRepository {
	return &QueryRepository{db: db}
}

// SetViewResolver injects the ViewResolver for formula view routing.
// When set, read queries will be routed to v_{object} views when available.
func (r *QueryRepository) SetViewResolver(vr ports.ViewResolver) {
	r.viewResolver = vr
}

// resolveReadTable returns the table name to use for SELECT queries.
// If a ViewResolver is set and a view exists, returns the view name.
func (r *QueryRepository) resolveReadTable(ctx context.Context, schema *models.ObjectMetadata) string {
	if r.viewResolver != nil {
		return r.viewResolver.ResolveTableName(ctx, schema)
	}
	return schema.APIName
}

// GetExecutor returns the DB connection or active transaction from context
func (r *QueryRepository) GetExecutor(ctx context.Context) Executor {
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		return tx
	}
	return r.db
}

// Find executes a structured query request
func (r *QueryRepository) Find(ctx context.Context, tableSchema *models.ObjectMetadata, req models.QueryRequest, rlsResult *domainrls.Result, visibleFields []string) (*models.QueryResult, error) {
	selectedFields := visibleFields
	if len(req.SelectFields) > 0 {
		visible := make(map[string]bool, len(visibleFields))
		for _, f := range visibleFields {
			visible[f] = true
		}
		selectedFields = make([]string, 0, len(req.SelectFields)+4)
		added := make(map[string]bool)
		addField := func(field string) {
			if field == "" || added[field] || !visible[field] {
				return
			}
			selectedFields = append(selectedFields, field)
			added[field] = true
		}
		addField(constants.FieldID)
		for _, f := range req.SelectFields {
			if strings.HasSuffix(f, "_Name") {
				addField(strings.TrimSuffix(f, "_Name"))
				continue
			}
			addField(f)
		}
	}

	readTable := r.resolveReadTable(ctx, tableSchema)

	// 1. Build Base Builder (Common for Data and Count)
	applyBaseLogic := func(b *query.Builder) error {
		// Exclude deleted (unless ScanAll is true)
		if !req.ScanAll && schemaHasIsDeleted(tableSchema) {
			b.ExcludeDeleted()
		}

		// Apply criteria
		for _, c := range req.Criteria {
			if isValidFieldName(c.Field) && isValidFilterOp(c.Op) {
				opUpper := strings.ToUpper(strings.TrimSpace(c.Op))
				if opUpper == "IN" || opUpper == "NOT IN" {
					// Handle both []interface{} (from main's typed approach) and
					// comma-separated string (from report filter builder)
					var vals []interface{}
					if typedVals, ok := c.Val.([]interface{}); ok {
						vals = typedVals
					} else {
						valStr := fmt.Sprintf("%v", c.Val)
						parts := strings.Split(valStr, ",")
						vals = make([]interface{}, len(parts))
						for j, p := range parts {
							vals[j] = strings.TrimSpace(p)
						}
					}
					if opUpper == "IN" {
						b.WhereIn(c.Field, vals)
					} else {
						placeholders := make([]string, len(vals))
						for j := range vals {
							placeholders[j] = "?"
						}
						col := fmt.Sprintf("`%s`.`%s`", tableSchema.APIName, c.Field)
						b.WhereRaw(fmt.Sprintf("%s NOT IN (%s)", col, strings.Join(placeholders, ",")), vals)
					}
				} else {
					b.WhereOp(c.Field, c.Op, c.Val)
				}
			}
		}

		// Apply formula expression filter
		if req.FilterExpr != "" {
			sqlWhere, args, err := formula.ToSQLWithMapper(req.FilterExpr, newFieldMapper(tableSchema))
			if err != nil {
				return pkgerrors.NewValidationError("filterExpr", fmt.Sprintf("invalid filter expression: %v", err))
			}
			if sqlWhere != "" {
				if req.MRUUserID != "" {
					sqlWhere = replaceIdentifiers(sqlWhere, buildQualifiedFieldReplacements(tableSchema, readTable))
				}
				b.WhereRaw(sqlWhere, args)
			}
		}
		return nil
	}

	// 2. Execute Data Query (route to formula view if available)
	// Apply RLS with the resolved physical table name so that manual-share
	// and team-member predicates reference the actual FROM target, not the
	// logical object name (which may differ when routed to a formula view).
	applyRLS := func(b *query.Builder) {
		if rlsWhere := domainrls.ToSQLFilter(rlsResult, readTable); rlsWhere != "" {
			b.WhereRaw(rlsWhere, nil)
		}
	}

	builder := query.From(readTable).WithMetadata(tableSchema)
	builder.Select(selectedFields)
	if err := applyBaseLogic(builder); err != nil {
		return nil, err
	}
	applyRLS(builder)

	// Apply sorting — MRU JOIN takes precedence over standard field sort
	if req.MRUUserID != "" {
		builder.JoinWithParams("INNER JOIN", constants.TableUserRecentItem, "__mru",
			fmt.Sprintf("`__mru`.`RecordId` = `%s`.`Id` AND `__mru`.`UserId` = ? AND `__mru`.`ObjectApiName` = ?", readTable),
			[]interface{}{req.MRUUserID, req.ObjectAPIName})
		builder.OrderByRaw("`__mru`.`AccessedAt` DESC", nil)
	} else if req.SortField != "" {
		builder.OrderBy(req.SortField, req.SortDirection)
	}

	// Apply limit & offset
	limit := req.Limit
	if limit <= 0 {
		limit = constants.DefaultSOSLLimit
	}
	// Fetch one extra to detect "Has Next"
	builder.Limit(limit + 1)

	if req.Offset > 0 {
		builder.Offset(req.Offset)
	}

	q := builder.Build()
	log.Debug().Str("sql", q.SQL).Any("params", q.Params).Msg("DEBUG_FIND_SQL")
	exec := r.GetExecutor(ctx)
	rows, err := exec.QueryContext(ctx, q.SQL, q.Params...)
	if err != nil {
		if IsUnknownColumnError(err) {
			colName := extractUnknownColumnName(err)
			return nil, pkgerrors.NewColumnNotFoundError(colName)
		}
		return nil, pkgerrors.NewInternalError("query execution error", err)
	}
	defer rows.Close()

	records, err := query.ScanRowsToSObjects(rows)
	if err != nil {
		return nil, err
	}

	// 3. Determine Pagination Status
	done := true
	if len(records) > limit {
		done = false
		records = records[:limit] // Trim extra record
	}

	// 4. Calculate Total Size
	// Optimization: if done=true and offset=0, total = len(records)
	totalSize := len(records)
	if (!done || req.Offset > 0) && !(done && req.Offset == 0) {
		// Need count query
		countBuilder := query.From(readTable).WithMetadata(tableSchema).SkipID()
		countBuilder.AddSelectRaw("COUNT(*)")
		if err := applyBaseLogic(countBuilder); err != nil {
			return nil, err
		}
		applyRLS(countBuilder)
		qCount := countBuilder.Build()

		var count int
		err := exec.QueryRowContext(ctx, qCount.SQL, qCount.Params...).Scan(&count)
		if err != nil {
			return nil, fmt.Errorf("count query error: %w", err)
		}
		totalSize = count
	}

	return &models.QueryResult{
		TotalSize: totalSize,
		Done:      done,
		Records:   records,
		// NextRecordsUrl is left empty for Service/Handler to populate
	}, nil
}

// Search performs a text search on specific fields
func (r *QueryRepository) Search(ctx context.Context, schema *models.ObjectMetadata, term string, searchFields []string, selectFields []string, limit int, offset int, orderBy string, orderDir string, filterExpr string, rlsResult *domainrls.Result) ([]models.SObject, error) {
	readTable := r.resolveReadTable(ctx, schema)
	builder := query.From(readTable).WithMetadata(schema).Select(selectFields).ExcludeDeleted()

	// Apply Sorting
	if orderBy != "" {
		builder.OrderBy(orderBy, orderDir)
	}

	// Apply Offset
	if offset > 0 {
		builder.Offset(offset)
	}

	// Apply Filter Expression
	if filterExpr != "" {
		sqlWhere, args, err := formula.ToSQLWithMapper(filterExpr, newFieldMapper(schema))
		if err != nil {
			return nil, pkgerrors.NewValidationError("filterExpr", fmt.Sprintf("invalid filter expression: %v", err))
		}
		if sqlWhere != "" {
			builder.WhereRaw(sqlWhere, args)
		}
	}
	if rlsWhere := domainrls.ToSQLFilter(rlsResult, readTable); rlsWhere != "" {
		builder.WhereRaw(rlsWhere, nil)
	}

	// Support for listing all records with "*" wildcard
	if term != "*" && term != "" {
		// Try to parse as formula expression (field op value)
		if formulaCondition, formulaParams, ok := query.ParseFormulaQuery(term, readTable); ok {
			builder.WhereRaw(formulaCondition, formulaParams)
		} else {
			// Fallback to text search
			searchConditions := make([]string, 0)
			searchParams := make([]interface{}, 0)
			for _, field := range searchFields {
				if !isValidFieldName(field) {
					return nil, pkgerrors.NewValidationError("searchField", fmt.Sprintf("invalid search field name: %s", field))
				}
				colName := resolvePhysicalName(schema, field)
				searchConditions = append(searchConditions, fmt.Sprintf("LOWER(`%s`.`%s`) LIKE ?", readTable, colName))
				searchParams = append(searchParams, fmt.Sprintf("%%%s%%", strings.ToLower(term)))
			}
			builder.WhereRaw(fmt.Sprintf("(%s)", strings.Join(searchConditions, " OR ")), searchParams)
		}
	}

	if limit <= 0 {
		limit = constants.DefaultSOSLLimit
	}
	builder.Limit(limit)

	q := builder.Build()
	log.Debug().Str("sql", q.SQL).Any("params", q.Params).Msg("DEBUG_SEARCH_SQL")

	exec := r.GetExecutor(ctx)
	rows, err := exec.QueryContext(ctx, q.SQL, q.Params...)
	if err != nil {
		return nil, pkgerrors.NewInternalError("search execution error", err)
	}
	defer rows.Close()

	results, err := query.ScanRowsToSObjects(rows)
	if err != nil {
		return nil, err
	}
	log.Debug().Int("count", len(results)).Str("table", schema.APIName).Msg("DEBUG_SEARCH_RESULTS_COUNT")
	return results, nil
}

// ExecuteRawSQL executes a raw SQL string (Validated by Service Layer)
func (r *QueryRepository) ExecuteRawSQL(ctx context.Context, sql string, params []interface{}) ([]models.SObject, error) {
	exec := r.GetExecutor(ctx)
	rows, err := exec.QueryContext(ctx, sql, params...)
	if err != nil {
		return nil, pkgerrors.NewInternalError("raw query error", err)
	}
	defer rows.Close()

	return query.ScanRowsToSObjects(rows)
}

// GetLookupNames retrieves specific ID and Name fields for a list of IDs
// idField: the field to use for ID matching (determined by caller from metadata)
// nameField: the field to use for display name
func (r *QueryRepository) GetLookupNames(ctx context.Context, tableSchema *models.ObjectMetadata, ids []string, idField string, nameField string) ([]models.SObject, error) {
	if len(ids) == 0 {
		return []models.SObject{}, nil
	}

	// Use Builder for schema-aware query construction
	if !isValidFieldName(idField) {
		return nil, pkgerrors.NewValidationError("idField", fmt.Sprintf("invalid id field name: %s", idField))
	}
	if !isValidFieldName(nameField) {
		return nil, pkgerrors.NewValidationError("nameField", fmt.Sprintf("invalid name field name: %s", nameField))
	}

	readTable := r.resolveReadTable(ctx, tableSchema)
	builder := query.From(readTable).WithMetadata(tableSchema)
	builder.Select([]string{idField, nameField})

	// Convert string IDs to interface{} for WhereIn
	vals := make([]interface{}, len(ids))
	for i, v := range ids {
		vals[i] = v
	}
	builder.WhereIn(idField, vals)

	q := builder.Build()

	exec := r.GetExecutor(ctx)
	rows, err := exec.QueryContext(ctx, q.SQL, q.Params...)
	if err != nil {
		return nil, pkgerrors.NewInternalError("lookup hydration error", err)
	}
	defer rows.Close()

	return query.ScanRowsToSObjects(rows)
}

// GetRecordsByIDs retrieves selected fields for a list of IDs.
func (r *QueryRepository) GetRecordsByIDs(ctx context.Context, tableSchema *models.ObjectMetadata, ids []string, fields []string) ([]models.SObject, error) {
	if len(ids) == 0 {
		return []models.SObject{}, nil
	}

	if len(fields) == 0 {
		fields = []string{constants.FieldID}
	}

	hasID := false
	for _, field := range fields {
		if !isValidFieldName(field) {
			return nil, fmt.Errorf("invalid field name: %s", field)
		}
		if strings.EqualFold(field, constants.FieldID) {
			hasID = true
		}
	}
	if !hasID {
		fields = append([]string{constants.FieldID}, fields...)
	}

	readTable := r.resolveReadTable(ctx, tableSchema)
	builder := query.From(readTable).WithMetadata(tableSchema)
	builder.Select(fields)

	vals := make([]interface{}, len(ids))
	for i, v := range ids {
		vals[i] = v
	}
	builder.WhereIn(constants.FieldID, vals)

	q := builder.Build()
	exec := r.GetExecutor(ctx)
	rows, err := exec.QueryContext(ctx, q.SQL, q.Params...)
	if err != nil {
		return nil, fmt.Errorf("get records by ids error: %w", err)
	}
	defer rows.Close()

	return query.ScanRowsToSObjects(rows)
}