markedit / backend / internal / database / queries.go

package database

import (
	"database/sql"
	"fmt"
	"time"
)

// CreateUser creates a new user or updates if exists
func (db *DB) CreateUser(user *User) error {
	query := `
		INSERT INTO users (github_id, username, email, avatar_url, last_repo)
		VALUES (?, ?, ?, ?, ?)
		ON CONFLICT(github_id) DO UPDATE SET
			username = excluded.username,
			email = excluded.email,
			avatar_url = excluded.avatar_url,
			last_repo = excluded.last_repo,
			updated_at = CURRENT_TIMESTAMP
		RETURNING id, created_at, updated_at
	`

	return db.QueryRow(query, user.GithubID, user.Username, user.Email, user.AvatarURL, user.LastRepo).
		Scan(&user.ID, &user.CreatedAt, &user.UpdatedAt)
}

// GetUserByGithubID retrieves a user by their GitHub ID
func (db *DB) GetUserByGithubID(githubID int) (*User, error) {
	user := &User{}
	query := `
		SELECT id, github_id, username, email, avatar_url, last_repo, created_at, updated_at
		FROM users
		WHERE github_id = ?
	`

	err := db.QueryRow(query, githubID).Scan(
		&user.ID,
		&user.GithubID,
		&user.Username,
		&user.Email,
		&user.AvatarURL,
		&user.LastRepo,
		&user.CreatedAt,
		&user.UpdatedAt,
	)

	if err == sql.ErrNoRows {
		return nil, fmt.Errorf("user not found")
	}
	if err != nil {
		return nil, fmt.Errorf("failed to get user: %w", err)
	}

	return user, nil
}

// GetUserByID retrieves a user by their ID
func (db *DB) GetUserByID(id int) (*User, error) {
	user := &User{}
	query := `
		SELECT id, github_id, username, email, avatar_url, last_repo, created_at, updated_at
		FROM users
		WHERE id = ?
	`

	err := db.QueryRow(query, id).Scan(
		&user.ID,
		&user.GithubID,
		&user.Username,
		&user.Email,
		&user.AvatarURL,
		&user.LastRepo,
		&user.CreatedAt,
		&user.UpdatedAt,
	)

	if err == sql.ErrNoRows {
		return nil, fmt.Errorf("user not found")
	}
	if err != nil {
		return nil, fmt.Errorf("failed to get user: %w", err)
	}

	return user, nil
}

// SaveAuthToken saves or updates an auth token
func (db *DB) SaveAuthToken(token *AuthToken) error {
	query := `
		INSERT INTO auth_tokens (user_id, provider, access_token, refresh_token, expires_at)
		VALUES (?, ?, ?, ?, ?)
		ON CONFLICT(user_id, provider) DO UPDATE SET
			access_token = excluded.access_token,
			refresh_token = excluded.refresh_token,
			expires_at = excluded.expires_at,
			created_at = CURRENT_TIMESTAMP
		RETURNING id, created_at
	`

	// Note: In production, these tokens should be encrypted before storage
	return db.QueryRow(query,
		token.UserID,
		token.Provider,
		token.AccessToken,
		token.RefreshToken,
		token.ExpiresAt,
	).Scan(&token.ID, &token.CreatedAt)
}

// GetAuthToken retrieves an auth token for a user and provider
func (db *DB) GetAuthToken(userID int, provider string) (*AuthToken, error) {
	token := &AuthToken{}
	query := `
		SELECT id, user_id, provider, access_token, refresh_token, expires_at, created_at
		FROM auth_tokens
		WHERE user_id = ? AND provider = ?
	`

	err := db.QueryRow(query, userID, provider).Scan(
		&token.ID,
		&token.UserID,
		&token.Provider,
		&token.AccessToken,
		&token.RefreshToken,
		&token.ExpiresAt,
		&token.CreatedAt,
	)

	if err == sql.ErrNoRows {
		return nil, fmt.Errorf("token not found")
	}
	if err != nil {
		return nil, fmt.Errorf("failed to get token: %w", err)
	}

	// Note: In production, these tokens should be decrypted after retrieval
	return token, nil
}

// SaveDraftContent saves or updates draft content for a file
func (db *DB) SaveDraftContent(draft *DraftContent) error {
	query := `
		INSERT INTO draft_content (user_id, repo_full_name, file_path, content, change_type, original_path, last_saved_at)
		VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
		ON CONFLICT(user_id, repo_full_name, file_path) DO UPDATE SET
			content = excluded.content,
			change_type = excluded.change_type,
			original_path = excluded.original_path,
			last_saved_at = CURRENT_TIMESTAMP
		RETURNING id, last_saved_at
	`

	return db.QueryRow(query,
		draft.UserID,
		draft.RepoFullName,
		draft.FilePath,
		draft.Content,
		draft.ChangeType,
		draft.OriginalPath,
	).Scan(&draft.ID, &draft.LastSavedAt)
}

// GetDraftContent retrieves draft content for a file
func (db *DB) GetDraftContent(userID int, repoFullName, filePath string) (*DraftContent, error) {
	draft := &DraftContent{}
	query := `
		SELECT id, user_id, repo_full_name, file_path, content, change_type, original_path, last_saved_at
		FROM draft_content
		WHERE user_id = ? AND repo_full_name = ? AND file_path = ?
	`

	err := db.QueryRow(query, userID, repoFullName, filePath).Scan(
		&draft.ID,
		&draft.UserID,
		&draft.RepoFullName,
		&draft.FilePath,
		&draft.Content,
		&draft.ChangeType,
		&draft.OriginalPath,
		&draft.LastSavedAt,
	)

	if err == sql.ErrNoRows {
		return nil, nil // No draft found, return nil without error
	}
	if err != nil {
		return nil, fmt.Errorf("failed to get draft: %w", err)
	}

	return draft, nil
}

// DeleteDraftContent deletes draft content for a file
func (db *DB) DeleteDraftContent(userID int, repoFullName, filePath string) error {
	query := `
		DELETE FROM draft_content
		WHERE user_id = ? AND repo_full_name = ? AND file_path = ?
	`

	_, err := db.Exec(query, userID, repoFullName, filePath)
	if err != nil {
		return fmt.Errorf("failed to delete draft: %w", err)
	}

	return nil
}

// GetAllDraftContent retrieves all draft content for a repository
func (db *DB) GetAllDraftContent(userID int, repoFullName string) ([]*DraftContent, error) {
	query := `
		SELECT id, user_id, repo_full_name, file_path, content, change_type, original_path, last_saved_at
		FROM draft_content
		WHERE user_id = ? AND repo_full_name = ?
		ORDER BY last_saved_at DESC
	`

	rows, err := db.Query(query, userID, repoFullName)
	if err != nil {
		return nil, fmt.Errorf("failed to get all drafts: %w", err)
	}
	defer rows.Close()

	var drafts []*DraftContent
	for rows.Next() {
		draft := &DraftContent{}
		err := rows.Scan(
			&draft.ID,
			&draft.UserID,
			&draft.RepoFullName,
			&draft.FilePath,
			&draft.Content,
			&draft.ChangeType,
			&draft.OriginalPath,
			&draft.LastSavedAt,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan draft row: %w", err)
		}
		drafts = append(drafts, draft)
	}

	return drafts, nil
}

// DeleteAllDraftContent deletes all draft content for a repository
func (db *DB) DeleteAllDraftContent(userID int, repoFullName string) error {
	query := `
		DELETE FROM draft_content
		WHERE user_id = ? AND repo_full_name = ?
	`

	_, err := db.Exec(query, userID, repoFullName)
	if err != nil {
		return fmt.Errorf("failed to delete all drafts: %w", err)
	}

	return nil
}

// SaveBranchState saves or updates branch state for a repository
func (db *DB) SaveBranchState(state *BranchState) error {
	query := `
		INSERT INTO branch_states (user_id, repo_full_name, branch_name, base_branch, last_push_at, has_uncommitted_changes, file_paths, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
		ON CONFLICT(user_id, repo_full_name) DO UPDATE SET
			branch_name = excluded.branch_name,
			base_branch = excluded.base_branch,
			last_push_at = excluded.last_push_at,
			has_uncommitted_changes = excluded.has_uncommitted_changes,
			file_paths = excluded.file_paths,
			updated_at = CURRENT_TIMESTAMP
		RETURNING id, created_at, updated_at
	`

	return db.QueryRow(query,
		state.UserID,
		state.RepoFullName,
		state.BranchName,
		state.BaseBranch,
		state.LastPushAt,
		state.HasUncommittedChanges,
		state.FilePaths,
	).Scan(&state.ID, &state.CreatedAt, &state.UpdatedAt)
}

// GetBranchState retrieves branch state for a repository
func (db *DB) GetBranchState(userID int, repoFullName string) (*BranchState, error) {
	state := &BranchState{}
	query := `
		SELECT id, user_id, repo_full_name, branch_name, base_branch, last_push_at, has_uncommitted_changes, file_paths, created_at, updated_at
		FROM branch_states
		WHERE user_id = ? AND repo_full_name = ?
	`

	err := db.QueryRow(query, userID, repoFullName).Scan(
		&state.ID,
		&state.UserID,
		&state.RepoFullName,
		&state.BranchName,
		&state.BaseBranch,
		&state.LastPushAt,
		&state.HasUncommittedChanges,
		&state.FilePaths,
		&state.CreatedAt,
		&state.UpdatedAt,
	)

	if err == sql.ErrNoRows {
		return nil, nil // No branch state found, return nil without error
	}
	if err != nil {
		return nil, fmt.Errorf("failed to get branch state: %w", err)
	}

	return state, nil
}

// DeleteBranchState deletes branch state for a repository
// UpdateUserRepo updates the last repository selected by a user
func (db *DB) UpdateUserRepo(userID int, lastRepo string) error {
	query := `
		UPDATE users
		SET last_repo = ?, updated_at = CURRENT_TIMESTAMP
		WHERE id = ?
	`

	_, err := db.Exec(query, lastRepo, userID)
	if err != nil {
		return fmt.Errorf("failed to update user's last repository: %w", err)
	}

	return nil
}

// InsertUserRepo adds a new repository to the user_repos table
func (db *DB) InsertUserRepo(userID int, repoName, repoLink string, lastUsedAt time.Time) error {
	query := `
		INSERT INTO user_repos (user_id, repo_name, repo_link, last_used_at)
		VALUES (?, ?, ?, ?)
	`

	_, err := db.Exec(query, userID, repoName, repoLink, lastUsedAt)
	if err != nil {
		return fmt.Errorf("failed to insert user repo: %w", err)
	}

	return nil
}

// UpdateLastUsedAt updates the last_used_at for a specific repo
func (db *DB) UpdateLastUsedAt(userID int, repoName string, lastUsedAt time.Time) error {
	query := `
		UPDATE user_repos
		SET last_used_at = ?, updated_at = CURRENT_TIMESTAMP
		WHERE user_id = ? AND repo_name = ?
	`

	_, err := db.Exec(query, lastUsedAt, userID, repoName)
	if err != nil {
		return fmt.Errorf("failed to update last_used_at: %w", err)
	}

	return nil
}

// GetUserRepos retrieves all repositories for a user
func (db *DB) GetUserRepos(userID int) ([]UserRepo, error) {
	query := `
		SELECT id, user_id, repo_name, repo_link, last_used_at, created_at, updated_at
		FROM user_repos
		WHERE user_id = ?
	`

	rows, err := db.Query(query, userID)
	if err != nil {
		return nil, fmt.Errorf("failed to get user repos: %w", err)
	}
	defer rows.Close()

	var repos []UserRepo
	for rows.Next() {
		var repo UserRepo
		if err := rows.Scan(
			&repo.ID, &repo.UserID, &repo.RepoName, &repo.RepoLink,
			&repo.LastUsedAt, &repo.CreatedAt, &repo.UpdatedAt,
		); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		repos = append(repos, repo)
	}

	return repos, nil
}

// GetMostRecentUserRepo gets the most recently used repo from user_repos table
func (db *DB) GetMostRecentUserRepo(userID int) (*UserRepo, error) {
	query := `
		SELECT id, user_id, repo_name, repo_link, last_used_at, created_at, updated_at
		FROM user_repos
		WHERE user_id = ?
		ORDER BY last_used_at DESC
		LIMIT 1
	`

	var repo UserRepo
	err := db.QueryRow(query, userID).Scan(
		&repo.ID, &repo.UserID, &repo.RepoName, &repo.RepoLink,
		&repo.LastUsedAt, &repo.CreatedAt, &repo.UpdatedAt,
	)

	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("failed to get most recent user repo: %w", err)
	}

	return &repo, nil
}

func (db *DB) DeleteBranchState(userID int, repoFullName string) error {
	query := `
		DELETE FROM branch_states
		WHERE user_id = ? AND repo_full_name = ?
	`

	_, err := db.Exec(query, userID, repoFullName)
	if err != nil {
		return fmt.Errorf("failed to delete branch state: %w", err)
	}

	return nil
}

// LogCacheEvent logs a cache event to the database
func (db *DB) LogCacheEvent(cacheKey, cacheType, eventType string, userID *int, responseTimeMs int) error {
	query := `
		INSERT INTO cache_stats (cache_key, cache_type, event_type, user_id, response_time_ms)
		VALUES (?, ?, ?, ?, ?)
	`
	_, err := db.Exec(query, cacheKey, cacheType, eventType, userID, responseTimeMs)
	return err
}

// GetCacheStats retrieves cache statistics since a given time
func (db *DB) GetCacheStats(since time.Time) ([]CacheStat, error) {
	query := `
		SELECT id, cache_key, cache_type, event_type, user_id, response_time_ms, created_at
		FROM cache_stats
		WHERE created_at >= ?
		ORDER BY created_at DESC
		LIMIT 1000
	`
	rows, err := db.Query(query, since)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var stats []CacheStat
	for rows.Next() {
		var s CacheStat
		err := rows.Scan(&s.ID, &s.CacheKey, &s.CacheType, &s.EventType, &s.UserID, &s.ResponseTimeMs, &s.CreatedAt)
		if err != nil {
			return nil, err
		}
		stats = append(stats, s)
	}
	return stats, nil
}

// GetCacheHitRate calculates the cache hit rate since a given time
func (db *DB) GetCacheHitRate(since time.Time) (float64, error) {
	query := `
		SELECT 
			SUM(CASE WHEN event_type = 'hit' THEN 1 ELSE 0 END) as hits,
			COUNT(*) as total
		FROM cache_stats
		WHERE created_at >= ? AND event_type IN ('hit', 'miss')
	`

	var hits, total int
	err := db.QueryRow(query, since).Scan(&hits, &total)
	if err != nil || total == 0 {
		return 0, err
	}

	return float64(hits) / float64(total) * 100, nil
}