uzdb/internal/services/query.go

package services

import (
	"context"
	"fmt"
	"time"

	"go.uber.org/zap"
	"gorm.io/gorm"

	"uzdb/internal/config"
	"uzdb/internal/models"
)

// QueryService handles query-related operations
type QueryService struct {
	db *gorm.DB
}

// NewQueryService creates a new query service
func NewQueryService(db *gorm.DB) *QueryService {
	return &QueryService{
		db: db,
	}
}

// GetQueryHistory returns query history with pagination
func (s *QueryService) GetQueryHistory(
	ctx context.Context,
	connectionID string,
	page, pageSize int,
) ([]models.QueryHistory, int64, error) {
	if page <= 0 {
		page = 1
	}
	if pageSize <= 0 || pageSize > 100 {
		pageSize = 20
	}

	var total int64
	var history []models.QueryHistory

	query := s.db.WithContext(ctx).Model(&models.QueryHistory{})

	if connectionID != "" {
		query = query.Where("connection_id = ?", connectionID)
	}

	// Get total count
	if err := query.Count(&total).Error; err != nil {
		return nil, 0, fmt.Errorf("failed to count history: %w", err)
	}

	// Get paginated results
	offset := (page - 1) * pageSize
	if err := query.Order("executed_at DESC").
		Offset(offset).
		Limit(pageSize).
		Find(&history).Error; err != nil {
		return nil, 0, fmt.Errorf("failed to get history: %w", err)
	}

	config.GetLogger().Debug("retrieved query history",
		zap.String("connection_id", connectionID),
		zap.Int("page", page),
		zap.Int("page_size", pageSize),
		zap.Int64("total", total))

	return history, total, nil
}

// GetSavedQueries returns all saved queries
func (s *QueryService) GetSavedQueries(
	ctx context.Context,
	connectionID string,
) ([]models.SavedQuery, error) {
	var queries []models.SavedQuery

	query := s.db.WithContext(ctx)
	if connectionID != "" {
		query = query.Where("connection_id = ?", connectionID)
	}

	result := query.Order("name ASC").Find(&queries)
	if result.Error != nil {
		return nil, fmt.Errorf("failed to get saved queries: %w", result.Error)
	}

	return queries, nil
}

// GetSavedQueryByID returns a saved query by ID
func (s *QueryService) GetSavedQueryByID(ctx context.Context, id uint) (*models.SavedQuery, error) {
	var query models.SavedQuery

	result := s.db.WithContext(ctx).First(&query, "id = ?", id)
	if result.Error != nil {
		if result.Error == gorm.ErrRecordNotFound {
			return nil, models.ErrNotFound
		}
		return nil, fmt.Errorf("failed to get saved query: %w", result.Error)
	}

	return &query, nil
}

// CreateSavedQuery creates a new saved query
func (s *QueryService) CreateSavedQuery(
	ctx context.Context,
	req *models.CreateSavedQueryRequest,
) (*models.SavedQuery, error) {
	query := &models.SavedQuery{
		Name:        req.Name,
		Description: req.Description,
		SQL:         req.SQL,
		ConnectionID: req.ConnectionID,
		Tags:        req.Tags,
	}

	result := s.db.WithContext(ctx).Create(query)
	if result.Error != nil {
		return nil, fmt.Errorf("failed to create saved query: %w", result.Error)
	}

	config.GetLogger().Info("saved query created",
		zap.Uint("id", query.ID),
		zap.String("name", query.Name))

	return query, nil
}

// UpdateSavedQuery updates an existing saved query
func (s *QueryService) UpdateSavedQuery(
	ctx context.Context,
	id uint,
	req *models.UpdateSavedQueryRequest,
) (*models.SavedQuery, error) {
	// Get existing query
	existing, err := s.GetSavedQueryByID(ctx, id)
	if err != nil {
		return nil, err
	}

	// Update fields
	if req.Name != "" {
		existing.Name = req.Name
	}
	if req.Description != "" {
		existing.Description = req.Description
	}
	if req.SQL != "" {
		existing.SQL = req.SQL
	}
	if req.ConnectionID != "" {
		existing.ConnectionID = req.ConnectionID
	}
	if req.Tags != "" {
		existing.Tags = req.Tags
	}

	result := s.db.WithContext(ctx).Save(existing)
	if result.Error != nil {
		return nil, fmt.Errorf("failed to update saved query: %w", result.Error)
	}

	config.GetLogger().Info("saved query updated",
		zap.Uint("id", id),
		zap.String("name", existing.Name))

	return existing, nil
}

// DeleteSavedQuery deletes a saved query
func (s *QueryService) DeleteSavedQuery(ctx context.Context, id uint) error {
	// Check if exists
	if _, err := s.GetSavedQueryByID(ctx, id); err != nil {
		return err
	}

	result := s.db.WithContext(ctx).Delete(&models.SavedQuery{}, "id = ?", id)
	if result.Error != nil {
		return fmt.Errorf("failed to delete saved query: %w", result.Error)
	}

	config.GetLogger().Info("saved query deleted", zap.Uint("id", id))

	return nil
}

// ClearOldHistory clears query history older than specified days
func (s *QueryService) ClearOldHistory(ctx context.Context, days int) (int64, error) {
	if days <= 0 {
		days = 30 // Default to 30 days
	}

	cutoffTime := time.Now().AddDate(0, 0, -days)

	result := s.db.WithContext(ctx).
		Where("executed_at < ?", cutoffTime).
		Delete(&models.QueryHistory{})

	if result.Error != nil {
		return 0, fmt.Errorf("failed to clear old history: %w", result.Error)
	}

	config.GetLogger().Info("cleared old query history",
		zap.Int64("deleted_count", result.RowsAffected),
		zap.Int("days", days))

	return result.RowsAffected, nil
}

// GetRecentQueries returns recent queries for quick access
func (s *QueryService) GetRecentQueries(
	ctx context.Context,
	connectionID string,
	limit int,
) ([]models.QueryHistory, error) {
	if limit <= 0 || limit > 50 {
		limit = 10
	}

	var queries []models.QueryHistory

	query := s.db.WithContext(ctx).
		Where("connection_id = ? AND success = ?", connectionID, true).
		Order("executed_at DESC").
		Limit(limit).
		Find(&queries)

	if query.Error != nil {
		return nil, fmt.Errorf("failed to get recent queries: %w", query.Error)
	}

	return queries, nil
}