package persistence

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

	"github.com/google/uuid"
	"github.com/nexuscrm/backend/internal/domain/ports"
	"github.com/nexuscrm/backend/internal/features/mru"
	pkgerrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/rs/zerolog/log"
)

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

// NewMRURepository creates a new MRU repository
func NewMRURepository(db *sql.DB) *MRURepository {
	return &MRURepository{db: 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 {
	// 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 (?, ?, ?, 1, CURRENT_TIMESTAMP, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
		ON DUPLICATE KEY UPDATE 
			InteractionCount = InteractionCount + 1,
			LastAccessedAt = CURRENT_TIMESTAMP,
			LastModifiedDate = CURRENT_TIMESTAMP`

	id := uuid.New().String()
	_, err := r.db.ExecContext(ctx, query, id, userID, objectAPIName)
	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.db.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.enforceLRULimit(ctx, userID, objectAPIName, mru.MaxRecentItemsPerObject)
	}

	return nil
}

// 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
		FROM ` + "`_System_Object`" + `
		WHERE ApiName = ? AND IsDeleted = 0`

	var meta models.ObjectMetadata
	var pluralLabel sql.NullString
	err := r.db.QueryRowContext(ctx, query, objectAPIName).Scan(&meta.ID, &meta.APIName, &meta.Label, &pluralLabel)
	if err != nil {
		return nil, err
	}
	if pluralLabel.Valid {
		meta.PluralLabel = pluralLabel.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
		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
		if err := rows.Scan(&meta.ID, &meta.APIName, &meta.Label, &pluralLabel); err != nil {
			return nil, pkgerrors.NewInternalError("failed to scan object metadata", err)
		}
		if pluralLabel.Valid {
			meta.PluralLabel = pluralLabel.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
}

// InitializeKeyPrefixes loads custom object prefixes from _System_Object table
// This should be called during application startup after database is initialized
// Returns the number of prefixes registered
func (r *MRURepository) InitializeKeyPrefixes(ctx context.Context) (int, error) {
	if r.db == nil {
		return 0, nil
	}

	// Query ALL objects (both standard and custom) that have a keyPrefix
	// Standard objects use their defined KeyPrefix from Salesforce spec
	// Custom objects generate sequential prefixes (a0X pattern)
	query := `
		SELECT ApiName, KeyPrefix, IsCustom 
		FROM _System_Object 
		WHERE IsDeleted = 0`

	rows, err := r.db.QueryContext(ctx, query)
	if err != nil {
		// If table doesn't exist yet (during initial bootstrap), return silently
		return 0, nil
	}
	defer rows.Close()

	count := 0
	customObjIndex := 0
	for rows.Next() {
		var apiName string
		var keyPrefix *string
		var isCustom bool
		if err := rows.Scan(&apiName, &keyPrefix, &isCustom); err != nil {
			continue
		}

		var prefix string
		if keyPrefix != nil && *keyPrefix != "" {
			// Use KeyPrefix from metadata if defined
			prefix = *keyPrefix
		} else if isCustom {
			// Generate a prefix for custom objects: a0X pattern
			prefix = mru.GenerateCustomPrefix(customObjIndex)
			customObjIndex++
		} else {
			// Standard object without KeyPrefix in metadata - skip
			// (Core system objects like User are already in the initial map)
			continue
		}

		mru.RegisterKeyPrefix(prefix, apiName)
		count++
	}

	return count, rows.Err()
}