package repository

import (
	"bugulma/backend/internal/domain"
	"bugulma/backend/internal/localization"
	"context"
	"fmt"
	"strings"

	"gorm.io/gorm"
)

// LocalizationRepository implements the domain.LocalizationRepository interface
type LocalizationRepository struct {
	db *gorm.DB
}

// NewLocalizationRepository creates a new localization repository
func NewLocalizationRepository(db *gorm.DB) domain.LocalizationRepository {
	return &LocalizationRepository{db: db}
}

// Create creates a new localization record
func (r *LocalizationRepository) Create(ctx context.Context, loc *domain.Localization) error {
	if loc == nil {
		return fmt.Errorf("localization cannot be nil")
	}

	// Validate required fields
	if loc.EntityType == "" {
		return fmt.Errorf("entity_type is required")
	}
	if loc.EntityID == "" {
		return fmt.Errorf("entity_id is required")
	}
	if loc.Field == "" {
		return fmt.Errorf("field is required")
	}
	if loc.Locale == "" {
		return fmt.Errorf("locale is required")
	}
	if loc.Value == "" {
		return fmt.Errorf("value cannot be empty")
	}

	// Generate composite ID if not provided
	if loc.ID == "" {
		loc.ID = fmt.Sprintf("%s_%s_%s_%s", loc.EntityType, loc.EntityID, loc.Field, loc.Locale)
	}

	return r.db.WithContext(ctx).Create(loc).Error
}

// GetByEntityAndField retrieves a localization by entity type, entity ID, field, and locale
func (r *LocalizationRepository) GetByEntityAndField(ctx context.Context, entityType, entityID, field, locale string) (*domain.Localization, error) {
	if entityType == "" || entityID == "" || field == "" || locale == "" {
		return nil, fmt.Errorf("all parameters (entityType, entityID, field, locale) are required")
	}

	var loc domain.Localization
	err := r.db.WithContext(ctx).Where("entity_type = ? AND entity_id = ? AND field = ? AND locale = ?",
		entityType, entityID, field, locale).First(&loc).Error

	if err == gorm.ErrRecordNotFound {
		return nil, nil // Return nil instead of error for not found
	}

	return &loc, err
}

// GetAllByEntity retrieves all localizations for a specific entity
func (r *LocalizationRepository) GetAllByEntity(ctx context.Context, entityType, entityID string) ([]*domain.Localization, error) {
	if entityType == "" || entityID == "" {
		return nil, fmt.Errorf("entityType and entityID are required")
	}

	var localizations []*domain.Localization
	err := r.db.WithContext(ctx).Where("entity_type = ? AND entity_id = ?",
		entityType, entityID).Order("field, locale").Find(&localizations).Error

	return localizations, err
}

// Update updates an existing localization record
func (r *LocalizationRepository) Update(ctx context.Context, loc *domain.Localization) error {
	if loc == nil {
		return fmt.Errorf("localization cannot be nil")
	}
	if loc.ID == "" {
		return fmt.Errorf("localization ID is required for update")
	}

	// Validate required fields
	if loc.EntityType == "" {
		return fmt.Errorf("entity_type is required")
	}
	if loc.EntityID == "" {
		return fmt.Errorf("entity_id is required")
	}
	if loc.Field == "" {
		return fmt.Errorf("field is required")
	}
	if loc.Locale == "" {
		return fmt.Errorf("locale is required")
	}

	return r.db.WithContext(ctx).Save(loc).Error
}

// Delete deletes a localization record by ID
func (r *LocalizationRepository) Delete(ctx context.Context, id string) error {
	if id == "" {
		return fmt.Errorf("localization ID is required")
	}

	return r.db.WithContext(ctx).Delete(&domain.Localization{}, "id = ?", id).Error
}

// GetByEntityTypeAndLocale retrieves all localizations for entities of a specific type and locale
func (r *LocalizationRepository) GetByEntityTypeAndLocale(ctx context.Context, entityType, locale string) ([]*domain.Localization, error) {
	if entityType == "" || locale == "" {
		return nil, fmt.Errorf("entityType and locale are required")
	}

	var localizations []*domain.Localization
	err := r.db.WithContext(ctx).Where("entity_type = ? AND locale = ?",
		entityType, locale).Order("entity_id, field").Find(&localizations).Error

	return localizations, err
}

// GetAllLocales returns all available locales in the system
func (r *LocalizationRepository) GetAllLocales(ctx context.Context) ([]string, error) {
	var locales []string
	err := r.db.WithContext(ctx).Model(&domain.Localization{}).
		Distinct("locale").
		Pluck("locale", &locales).Error

	return locales, err
}

// GetSupportedLocalesForEntity returns locales that have translations for a specific entity
func (r *LocalizationRepository) GetSupportedLocalesForEntity(ctx context.Context, entityType, entityID string) ([]string, error) {
	if entityType == "" || entityID == "" {
		return nil, fmt.Errorf("entityType and entityID are required")
	}

	var locales []string
	err := r.db.WithContext(ctx).Model(&domain.Localization{}).
		Where("entity_type = ? AND entity_id = ?", entityType, entityID).
		Distinct("locale").
		Pluck("locale", &locales).Error

	return locales, err
}

// BulkCreate creates multiple localization records in a transaction
func (r *LocalizationRepository) BulkCreate(ctx context.Context, localizations []*domain.Localization) error {
	if len(localizations) == 0 {
		return nil
	}

	// Validate all localizations before starting transaction
	for i, loc := range localizations {
		if loc == nil {
			return fmt.Errorf("localization at index %d cannot be nil", i)
		}
		if loc.EntityType == "" || loc.EntityID == "" || loc.Field == "" || loc.Locale == "" {
			return fmt.Errorf("localization at index %d has missing required fields", i)
		}
		// Generate composite ID if not provided
		if loc.ID == "" {
			loc.ID = fmt.Sprintf("%s_%s_%s_%s", loc.EntityType, loc.EntityID, loc.Field, loc.Locale)
		}
	}

	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		for _, loc := range localizations {
			if err := tx.Create(loc).Error; err != nil {
				return err
			}
		}
		return nil
	})
}

// BulkDelete deletes multiple localization records by their IDs in a transaction
func (r *LocalizationRepository) BulkDelete(ctx context.Context, ids []string) error {
	if len(ids) == 0 {
		return nil
	}

	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		return tx.Where("id IN ?", ids).Delete(&domain.Localization{}).Error
	})
}

// SearchLocalizations searches localizations by value content
func (r *LocalizationRepository) SearchLocalizations(ctx context.Context, query string, locale string, limit int) ([]*domain.Localization, error) {
	if query == "" {
		return nil, fmt.Errorf("search query cannot be empty")
	}

	var localizations []*domain.Localization
	db := r.db.WithContext(ctx)

	if locale != "" {
		db = db.Where("locale = ?", locale)
	}

	err := db.Where("value ILIKE ?", "%"+query+"%").
		Order("entity_type, entity_id, field").
		Limit(limit).
		Find(&localizations).Error

	return localizations, err
}

// GetTranslationCountsByEntity returns translation counts grouped by entity type and locale
func (r *LocalizationRepository) GetTranslationCountsByEntity(ctx context.Context) (map[string]map[string]int, error) {
	var results []struct {
		EntityType string
		Locale     string
		Count      int
	}

	err := r.db.WithContext(ctx).Model(&domain.Localization{}).
		Select("entity_type, locale, COUNT(*) as count").
		Group("entity_type, locale").
		Order("entity_type, locale").
		Scan(&results).Error

	if err != nil {
		return nil, err
	}

	counts := make(map[string]map[string]int)
	for _, result := range results {
		if counts[result.EntityType] == nil {
			counts[result.EntityType] = make(map[string]int)
		}
		counts[result.EntityType][result.Locale] = result.Count
	}

	return counts, nil
}

// GetUntranslatedFields returns fields that exist in entities but don't have translations
func (r *LocalizationRepository) GetUntranslatedFields(ctx context.Context, entityType, targetLocale string, entityIDs []string) ([]UntranslatedFieldInfo, error) {
	// This would require joining with the actual entity tables
	// For now, return empty - this is a complex query that would need to be implemented
	// based on the specific entity schema
	return []UntranslatedFieldInfo{}, nil
}

// UntranslatedFieldInfo represents information about a field that needs translation
type UntranslatedFieldInfo struct {
	EntityID     string
	Field        string
	RussianValue string
}

// GetTranslationReuseCandidates finds Russian text that appears in multiple entities
// This can help identify good candidates for caching
func (r *LocalizationRepository) GetTranslationReuseCandidates(ctx context.Context, entityType, field, locale string) ([]domain.ReuseCandidate, error) {
	var results []struct {
		RussianValue string
		Count        int
	}

	// Find Russian values that appear in multiple entities of the same type
	err := r.db.WithContext(ctx).Model(&domain.Localization{}).
		Select("value as russian_value, COUNT(DISTINCT entity_id) as count").
		Where("entity_type = ? AND field = ? AND locale = 'ru'", entityType, field).
		Group("value").
		Having("COUNT(DISTINCT entity_id) > 1").
		Order("count DESC").
		Limit(50).
		Scan(&results).Error

	if err != nil {
		return nil, err
	}

	candidates := make([]domain.ReuseCandidate, len(results))
	for i, result := range results {
		candidates[i] = domain.ReuseCandidate{
			RussianValue: result.RussianValue,
			EntityCount:  result.Count,
		}
	}

	return candidates, nil
}

// GetEntitiesNeedingTranslation returns entity IDs that need translation for specific fields
// It queries entity tables to find entities with Russian content that don't have translations
func (r *LocalizationRepository) GetEntitiesNeedingTranslation(ctx context.Context, entityType, field, targetLocale string, limit int) ([]string, error) {
	if entityType == "" || field == "" || targetLocale == "" {
		return nil, fmt.Errorf("entityType, field, and targetLocale are required")
	}

	if limit <= 0 {
		limit = 100
	}
	if limit > 1000 {
		limit = 1000
	}

	// Get entity descriptor from registry to know table name
	desc, exists := localization.GetEntityDescriptor(entityType)
	if !exists {
		return nil, fmt.Errorf("unknown entity type: %s", entityType)
	}

	// Build query to find entities that:
	// 1. Have non-empty Russian content in the specified field
	// 2. Don't have a translation in the target locale
	// This uses a LEFT JOIN to find missing translations
	// We need to use GORM's Raw with proper parameterization
	// The field name needs to be validated against the entity descriptor
	fieldValid := false
	for _, f := range desc.Fields {
		if f == field {
			fieldValid = true
			break
		}
	}
	if !fieldValid {
		return nil, fmt.Errorf("field '%s' is not valid for entity type '%s'", field, entityType)
	}

	// Use parameterized query with proper escaping for table/column names
	// Note: Table and column names cannot be parameterized, so we validate them above
	// GORM uses ? placeholders for parameters
	query := fmt.Sprintf(`
		SELECT DISTINCT e.%s as entity_id
		FROM %s e
		LEFT JOIN localizations l
			ON l.entity_type = ?
			AND l.entity_id = e.%s
			AND l.field = ?
			AND l.locale = ?
		WHERE e.%s IS NOT NULL
			AND e.%s != ''
			AND l.id IS NULL
		LIMIT ?
	`, desc.IDField, desc.TableName, desc.IDField, field, field)

	var entityIDs []string
	err := r.db.WithContext(ctx).Raw(query, entityType, field, targetLocale, limit).Pluck("entity_id", &entityIDs).Error
	if err != nil {
		return nil, fmt.Errorf("failed to query entities needing translation: %w", err)
	}

	return entityIDs, nil
}

// GetEntityFieldValue retrieves the raw value of a field from the entity table for a given entity ID
func (r *LocalizationRepository) GetEntityFieldValue(ctx context.Context, entityType, entityID, field string) (string, error) {
	if entityType == "" || entityID == "" || field == "" {
		return "", fmt.Errorf("entityType, entityID and field are required")
	}

	// Get entity descriptor to know table and id field
	desc, exists := localization.GetEntityDescriptor(entityType)
	if !exists {
		return "", fmt.Errorf("unknown entity type: %s", entityType)
	}

	// Validate field
	fieldValid := false
	for _, f := range desc.Fields {
		if f == field {
			fieldValid = true
			break
		}
	}
	if !fieldValid {
		return "", fmt.Errorf("field '%s' is not valid for entity type '%s'", field, entityType)
	}

	query := fmt.Sprintf(`SELECT e.%s FROM %s e WHERE e.%s = ? LIMIT 1`, field, desc.TableName, desc.IDField)
	var value string
	if err := r.db.WithContext(ctx).Raw(query, entityID).Scan(&value).Error; err != nil {
		return "", fmt.Errorf("failed to get field value: %w", err)
	}

	return value, nil
}

// FindExistingTranslationByRussianText finds an existing translation by matching Russian source text
// It looks for entities with the same Russian text in the same field that already have a translation
// Returns the translation value if found, empty string if not found
func (r *LocalizationRepository) FindExistingTranslationByRussianText(ctx context.Context, entityType, field, targetLocale, russianText string) (string, error) {
	if entityType == "" || field == "" || targetLocale == "" || russianText == "" {
		return "", nil
	}

	// Normalize the Russian text for matching
	normalized := strings.TrimSpace(russianText)
	if normalized == "" {
		return "", nil
	}

	// Query: Find a localization where:
	// 1. The entity has a Russian localization with matching text
	// 2. The same entity has a translation in the target locale
	var translation string
	err := r.db.WithContext(ctx).Raw(`
		SELECT l_target.value
		FROM localizations l_ru
		INNER JOIN localizations l_target
			ON l_ru.entity_type = l_target.entity_type
			AND l_ru.entity_id = l_target.entity_id
			AND l_ru.field = l_target.field
		WHERE l_ru.entity_type = ?
			AND l_ru.field = ?
			AND l_ru.locale = 'ru'
			AND l_ru.value = ?
			AND l_target.locale = ?
		LIMIT 1
	`, entityType, field, normalized, targetLocale).Scan(&translation).Error

	if err != nil {
		return "", fmt.Errorf("failed to find existing translation: %w", err)
	}

	return translation, nil
}