package persistence

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

	"github.com/google/uuid"
	pkgerrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/kernel/pkg/ports"
	"github.com/nexuscrm/persistence/mru"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/rs/zerolog/log"
)

// MRURepository implements ports.MRURepository for database operations
type MRURepository struct {
	db           *sql.DB
	cleanup      *mruCleanupCoalescer
	cleanupSlots chan struct{}
}

const (
	defaultMRUCleanupMinInterval = 30 * time.Second
	defaultMRUCleanupConcurrency = 1
	defaultMRUCleanupTimeout     = 5 * time.Second
)

type mruCleanupState struct {
	lastQueued time.Time
	inFlight   bool
}

type mruCleanupCoalescer struct {
	mu          sync.Mutex
	minInterval time.Duration
	entries     map[string]mruCleanupState
}

func newMRUCleanupCoalescer(minInterval time.Duration) *mruCleanupCoalescer {
	if minInterval <= 0 {
		minInterval = defaultMRUCleanupMinInterval
	}
	return &mruCleanupCoalescer{
		minInterval: minInterval,
		entries:     make(map[string]mruCleanupState),
	}
}

func (c *mruCleanupCoalescer) shouldQueue(key string, now time.Time) bool {
	if c == nil || key == "" {
		return false
	}
	c.mu.Lock()
	defer c.mu.Unlock()
	state := c.entries[key]
	if state.inFlight || (!state.lastQueued.IsZero() && now.Sub(state.lastQueued) < c.minInterval) {
		return false
	}
	state.inFlight = true
	state.lastQueued = now
	c.entries[key] = state
	return true
}

func (c *mruCleanupCoalescer) finish(key string) {
	if c == nil || key == "" {
		return
	}
	c.mu.Lock()
	state := c.entries[key]
	state.inFlight = false
	c.entries[key] = state
	c.mu.Unlock()
}

// NewMRURepository creates a new MRU repository
func NewMRURepository(db *sql.DB) *MRURepository {
	return &MRURepository{
		db:           db,
		cleanup:      newMRUCleanupCoalescer(defaultMRUCleanupMinInterval),
		cleanupSlots: make(chan struct{}, defaultMRUCleanupConcurrency),
	}
}

func (r *MRURepository) getExecutor(ctx context.Context) Executor {
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		return tx
	}
	return r.db
}

// Compile-time interface check
var _ ports.MRURepository = (*MRURepository)(nil)

// TrackObjectInteraction upserts an object interaction record
// Uses INSERT ON DUPLICATE KEY UPDATE for atomic upsert (no race conditions)
func (r *MRURepository) TrackObjectInteraction(ctx context.Context, userID, objectAPIName string) error {
	return r.TrackObjectInteractionCount(ctx, userID, objectAPIName, 1)
}

// TrackObjectInteractionCount upserts an object interaction record and increments
// it by count. Async MRU coalescing uses this to preserve the interaction count
// while avoiding a hot-row write per request in a burst.
func (r *MRURepository) TrackObjectInteractionCount(ctx context.Context, userID, objectAPIName string, count int) error {
	if count <= 0 {
		return nil
	}
	// Validate objectAPIName to prevent injection
	if !mru.ValidateObjectAPIName(objectAPIName) {
		return pkgerrors.NewValidationError("objectAPIName", fmt.Sprintf("invalid object API name: %s", objectAPIName))
	}

	// Atomic upsert using INSERT ON DUPLICATE KEY UPDATE
	// This avoids race conditions where two concurrent requests could both try to INSERT
	query := `
		INSERT INTO ` + "`_System_UserObjectInteraction`" + ` 
		(Id, UserId, ObjectApiName, InteractionCount, LastAccessedAt, IsPinned, CreatedDate, LastModifiedDate)
		VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
		ON DUPLICATE KEY UPDATE 
			InteractionCount = InteractionCount + VALUES(InteractionCount),
			LastAccessedAt = CURRENT_TIMESTAMP,
			LastModifiedDate = CURRENT_TIMESTAMP`

	id := uuid.New().String()
	_, err := r.db.ExecContext(ctx, query, id, userID, objectAPIName, count)
	if err != nil {
		return pkgerrors.NewInternalError("failed to upsert object interaction", err)
	}

	return nil
}

// GetScopeOrder returns the user's search scope order
// Pinned objects come first, then sorted by interaction count (descending)
func (r *MRURepository) GetScopeOrder(ctx context.Context, userID string) ([]ports.MRUObjectInteraction, error) {
	query := `
		SELECT Id, UserId, ObjectApiName, InteractionCount, LastAccessedAt, IsPinned
		FROM ` + "`_System_UserObjectInteraction`" + `
		WHERE UserId = ?
		ORDER BY IsPinned DESC, InteractionCount DESC
		LIMIT ?`

	rows, err := r.getExecutor(ctx).QueryContext(
		ctx, query, userID, mru.MaxScopeOrderResults,
	)
	if err != nil {
		return nil, pkgerrors.NewInternalError("failed to query scope order", err)
	}
	defer rows.Close()

	var result []ports.MRUObjectInteraction
	for rows.Next() {
		var oi ports.MRUObjectInteraction
		var isPinned int
		if err := rows.Scan(&oi.ID, &oi.UserID, &oi.ObjectAPIName, &oi.InteractionCount, &oi.LastAccessedAt, &isPinned); err != nil {
			return nil, pkgerrors.NewInternalError("failed to scan object interaction", err)
		}
		oi.IsPinned = isPinned == 1
		result = append(result, oi)
	}

	return result, rows.Err()
}

// SetObjectPinned pins or unpins an object for a user
func (r *MRURepository) SetObjectPinned(ctx context.Context, userID, objectAPIName string, pinned bool) error {
	// Validate objectAPIName to prevent injection
	if !mru.ValidateObjectAPIName(objectAPIName) {
		return pkgerrors.NewValidationError("objectAPIName", fmt.Sprintf("invalid object API name: %s", objectAPIName))
	}

	pinnedVal := 0
	if pinned {
		pinnedVal = 1
	}

	query := `
		UPDATE ` + "`_System_UserObjectInteraction`" + `
		SET IsPinned = ?, LastModifiedDate = CURRENT_TIMESTAMP
		WHERE UserId = ? AND ObjectApiName = ?`

	_, err := r.db.ExecContext(ctx, query, pinnedVal, userID, objectAPIName)
	return err
}

// TrackRecentItem upserts a recent item record
// Uses INSERT ON DUPLICATE KEY UPDATE for atomic upsert (no race conditions)
func (r *MRURepository) TrackRecentItem(ctx context.Context, userID, objectAPIName, recordID string) error {
	// Validate objectAPIName to prevent injection
	if !mru.ValidateObjectAPIName(objectAPIName) {
		return pkgerrors.NewValidationError("objectAPIName", fmt.Sprintf("invalid object API name: %s", objectAPIName))
	}

	// Atomic upsert using INSERT ON DUPLICATE KEY UPDATE
	// This avoids race conditions where two concurrent requests could both try to INSERT
	query := `
		INSERT INTO ` + "`_System_UserRecentItem`" + `
		(Id, UserId, ObjectApiName, RecordId, AccessedAt, UpdateCounter, CreatedDate, LastModifiedDate)
		VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
		ON DUPLICATE KEY UPDATE 
			AccessedAt = CURRENT_TIMESTAMP,
			UpdateCounter = UpdateCounter + 1,
			LastModifiedDate = CURRENT_TIMESTAMP`

	id := uuid.New().String()
	result, err := r.db.ExecContext(ctx, query, id, userID, objectAPIName, recordID)
	if err != nil {
		return pkgerrors.NewInternalError("failed to upsert recent item", err)
	}

	// Check if this was an INSERT (RowsAffected=1) vs UPDATE (RowsAffected=2 for ON DUPLICATE KEY UPDATE)
	// For INSERT, enforce LRU limit to clean up old records
	rowsAffected, _ := result.RowsAffected()
	if rowsAffected == 1 {
		// New record was inserted, enforce LRU limit
		r.scheduleLRULimitCleanup(userID, objectAPIName, mru.MaxRecentItemsPerObject)
	}

	return nil
}

func (r *MRURepository) scheduleLRULimitCleanup(userID, objectAPIName string, limit int) {
	if r == nil || r.db == nil || userID == "" || objectAPIName == "" || limit <= 0 {
		return
	}
	cleanup := r.cleanup
	cleanupSlots := r.cleanupSlots
	if cleanup == nil || cleanupSlots == nil {
		return
	}
	key := userID + "\x00" + objectAPIName
	if !cleanup.shouldQueue(key, time.Now()) {
		return
	}
	select {
	case cleanupSlots <- struct{}{}:
	default:
		cleanup.finish(key)
		return
	}
	go func() {
		defer func() {
			<-cleanupSlots
			cleanup.finish(key)
		}()
		cleanupCtx, cancel := context.WithTimeout(context.Background(), defaultMRUCleanupTimeout)
		defer cancel()
		r.enforceLRULimit(cleanupCtx, userID, objectAPIName, limit)
	}()
}

// enforceLRULimit removes old records to maintain the limit per object
func (r *MRURepository) enforceLRULimit(ctx context.Context, userID, objectAPIName string, limit int) {
	// Delete records beyond the limit
	query := `
		DELETE FROM ` + "`_System_UserRecentItem`" + `
		WHERE UserId = ? AND ObjectApiName = ? AND Id NOT IN (
			SELECT Id FROM (
				SELECT Id FROM ` + "`_System_UserRecentItem`" + `
				WHERE UserId = ? AND ObjectApiName = ?
				ORDER BY AccessedAt DESC
				LIMIT ?
			) AS keep_ids
		)`

	// Execute but ignore errors - this is a cleanup operation
	_, _ = r.db.ExecContext(ctx, query, userID, objectAPIName, userID, objectAPIName, limit)
}

// GetRelevantItems returns the user's recently accessed records
// If sobjects is empty, returns all objects in user's scope
// If lastUpdatedId is provided, returns items with UpdateCounter > lastUpdatedId
func (r *MRURepository) GetRelevantItems(ctx context.Context, userID string, sobjects []string, lastUpdatedId int64) ([]ports.MRURecentItem, int64, error) {
	var args []interface{}
	args = append(args, userID)

	whereClause := "WHERE UserId = ?"

	if len(sobjects) > 0 {
		placeholders := make([]string, len(sobjects))
		for i, obj := range sobjects {
			placeholders[i] = "?"
			args = append(args, obj)
		}
		whereClause += " AND ObjectApiName IN (" + strings.Join(placeholders, ",") + ")"
	}

	if lastUpdatedId > 0 {
		whereClause += " AND UpdateCounter > ?"
		args = append(args, lastUpdatedId)
	}

	query := `
		SELECT Id, UserId, ObjectApiName, RecordId, AccessedAt, UpdateCounter
		FROM ` + "`_System_UserRecentItem`" + `
		` + whereClause + `
		ORDER BY AccessedAt DESC
		LIMIT ?`

	args = append(args, mru.MaxRelevantItemsTotal)
	rows, err := r.db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, 0, pkgerrors.NewInternalError("failed to query recent items", err)
	}
	defer rows.Close()

	var result []ports.MRURecentItem
	var maxCounter int64
	for rows.Next() {
		var ri ports.MRURecentItem
		if err := rows.Scan(&ri.ID, &ri.UserID, &ri.ObjectAPIName, &ri.RecordID, &ri.AccessedAt, &ri.UpdateCounter); err != nil {
			return nil, 0, pkgerrors.NewInternalError("failed to scan recent item", err)
		}
		result = append(result, ri)
		if ri.UpdateCounter > maxCounter {
			maxCounter = ri.UpdateCounter
		}
	}

	return result, maxCounter, rows.Err()
}

// GetObjectMeta retrieves object metadata for building response
func (r *MRURepository) GetObjectMeta(ctx context.Context, objectAPIName string) (*models.ObjectMetadata, error) {
	query := `
		SELECT Id, ApiName, Label, PluralLabel, KeyPrefix
		FROM ` + "`_System_Object`" + `
		WHERE ApiName = ? AND IsDeleted = 0`

	var meta models.ObjectMetadata
	var pluralLabel sql.NullString
	var keyPrefix sql.NullString
	err := r.db.QueryRowContext(ctx, query, objectAPIName).Scan(&meta.ID, &meta.APIName, &meta.Label, &pluralLabel, &keyPrefix)
	if err != nil {
		return nil, err
	}
	if pluralLabel.Valid {
		meta.PluralLabel = pluralLabel.String
	}
	if keyPrefix.Valid {
		meta.KeyPrefix = &keyPrefix.String
	}
	return &meta, nil
}

// GetObjectMetaBatch retrieves metadata for multiple objects in a single query
// Returns a map of objectAPIName -> ObjectMetadata
func (r *MRURepository) GetObjectMetaBatch(ctx context.Context, objectAPINames []string) (map[string]*models.ObjectMetadata, error) {
	if len(objectAPINames) == 0 {
		return make(map[string]*models.ObjectMetadata), nil
	}

	// Build placeholders for IN clause
	placeholders := make([]string, len(objectAPINames))
	args := make([]interface{}, len(objectAPINames))
	for i, name := range objectAPINames {
		placeholders[i] = "?"
		args[i] = name
	}

	query := `
		SELECT Id, ApiName, Label, PluralLabel, KeyPrefix
		FROM ` + "`_System_Object`" + `
		WHERE ApiName IN (` + strings.Join(placeholders, ",") + `) AND IsDeleted = 0`

	rows, err := r.db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, pkgerrors.NewInternalError("failed to query object metadata batch", err)
	}
	defer rows.Close()

	result := make(map[string]*models.ObjectMetadata)
	for rows.Next() {
		var meta models.ObjectMetadata
		var pluralLabel sql.NullString
		var keyPrefix sql.NullString
		if err := rows.Scan(&meta.ID, &meta.APIName, &meta.Label, &pluralLabel, &keyPrefix); err != nil {
			return nil, pkgerrors.NewInternalError("failed to scan object metadata", err)
		}
		if pluralLabel.Valid {
			meta.PluralLabel = pluralLabel.String
		}
		if keyPrefix.Valid {
			meta.KeyPrefix = &keyPrefix.String
		}
		result[meta.APIName] = &meta
	}

	return result, rows.Err()
}

// GetRecordNamesBatch retrieves record Names for multiple objects/records in batch
// byObject is a map of objectAPIName -> []recordIDs
// Returns a map of recordID -> Name
func (r *MRURepository) GetRecordNamesBatch(ctx context.Context, byObject map[string][]string) (map[string]string, error) {
	result := make(map[string]string)

	// Query each object's table to get Name fields
	// We use dynamic table names which requires careful validation (already done in TrackRecentItem)
	for objName, recordIDs := range byObject {
		if len(recordIDs) == 0 {
			continue
		}

		// Validate object name to prevent SQL injection
		if !mru.ValidateObjectAPIName(objName) {
			continue
		}

		// Build IN clause placeholders
		placeholders := make([]string, len(recordIDs))
		args := make([]interface{}, len(recordIDs))
		for i, id := range recordIDs {
			placeholders[i] = "?"
			args[i] = id
		}

		// Query the object's table for Id and Name
		// Note: Not all objects have a "Name" field, some use different name fields
		// We try Name first, then fall back to common alternatives
		query := fmt.Sprintf(
			"SELECT Id, COALESCE(Name, '') as Name FROM `%s` WHERE Id IN (%s)",
			objName,
			strings.Join(placeholders, ","),
		)

		rows, err := r.db.QueryContext(ctx, query, args...)
		if err != nil {
			// Table might not exist or Name column might not exist - log at debug level and skip
			// This is expected for some system objects that don't have a Name field
			log.Debug().Str("object", objName).Err(err).Msg("Skipping object in GetRecordNamesBatch - table or Name column may not exist")
			continue
		}

		for rows.Next() {
			var id, name string
			if err := rows.Scan(&id, &name); err != nil {
				continue
			}
			result[id] = name
		}
		rows.Close()
	}

	return result, nil
}

func nextAvailableCustomPrefix(used map[string]bool, next *int) string {
	for {
		prefix := generateCustomKeyPrefix(*next)
		*next = *next + 1
		if !used[prefix] {
			return prefix
		}
	}
}

func generateCustomKeyPrefix(index int) string {
	const chars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
	series := index / 36
	offset := index % 36

	seriesChar := chars[series%36]
	offsetChar := chars[offset]

	return "a" + string(seriesChar) + string(offsetChar)
}