hako/internal/archival/store/archive_file_store.go

package store

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

	"git.nakama.town/fmartingr/hako/internal/model"
	"github.com/huandu/go-sqlbuilder"
)

// ArchiveFileStore handles database operations for archive files
type ArchiveFileStore struct {
	readDB  *sql.DB
	writeDB *sql.DB
}

// NewArchiveFileStore creates a new ArchiveFileStore
func NewArchiveFileStore(readDB, writeDB *sql.DB) *ArchiveFileStore {
	return &ArchiveFileStore{
		readDB:  readDB,
		writeDB: writeDB,
	}
}

// Create creates a new archive file
func (s *ArchiveFileStore) Create(ctx context.Context, file *model.ArchiveFile) error {
	ib := sqlbuilder.NewInsertBuilder()
	ib.InsertInto("archive_files")
	ib.Cols("id", "archive_id", "extractor_key", "filename", "mime_type", "file_size", "storage_path", "hash_sha256", "content", "content_mime_type", "thumbnail_path", "created_at")
	ib.Values(file.ID, file.ArchiveID, file.ArchiverKey, file.Filename, file.MimeType, file.FileSize, file.StoragePath, file.HashSha256, file.Content, file.ContentMimeType, file.ThumbnailPath, file.CreatedAt.Format("2006-01-02 15:04:05"))

	query, args := ib.Build()
	_, err := s.writeDB.ExecContext(ctx, query, args...)
	if err != nil {
		return fmt.Errorf("failed to create archive file: %w", err)
	}

	return nil
}

// GetByID retrieves an archive file by ID
func (s *ArchiveFileStore) GetByID(ctx context.Context, id string) (*model.ArchiveFile, error) {
	sb := sqlbuilder.NewSelectBuilder()
	sb.Select("id", "archive_id", "extractor_key", "filename", "mime_type", "file_size", "storage_path", "hash_sha256", "content", "content_mime_type", "thumbnail_path", "created_at")
	sb.From("archive_files")
	sb.Where(sb.Equal("id", id))

	query, args := sb.Build()
	row := s.readDB.QueryRowContext(ctx, query, args...)

	var file model.ArchiveFile
	var createdAt string

	err := row.Scan(&file.ID, &file.ArchiveID, &file.ArchiverKey, &file.Filename, &file.MimeType, &file.FileSize, &file.StoragePath, &file.HashSha256, &file.Content, &file.ContentMimeType, &file.ThumbnailPath, &createdAt)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("archive file not found")
		}
		return nil, fmt.Errorf("failed to get archive file: %w", err)
	}

	// Parse timestamp
	file.CreatedAt, _ = parseTimestamp(createdAt)

	return &file, nil
}

// ListByArchiveID retrieves all files for an archive
func (s *ArchiveFileStore) ListByArchiveID(ctx context.Context, opts ArchiveFileListOptions) ([]*model.ArchiveFile, error) {
	opts.Defaults()
	if err := opts.IsValid(); err != nil {
		return nil, fmt.Errorf("invalid options: %w", err)
	}

	sb := sqlbuilder.NewSelectBuilder()
	sb.Select("id", "archive_id", "extractor_key", "filename", "mime_type", "file_size", "storage_path", "hash_sha256", "content", "content_mime_type", "thumbnail_path", "created_at")
	sb.From("archive_files")
	sb.Where(sb.Equal("archive_id", opts.ArchiveID))
	sb.OrderBy("created_at ASC")

	query, args := sb.Build()
	rows, err := s.readDB.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to list archive files: %w", err)
	}
	defer func() { _ = rows.Close() }()

	var files []*model.ArchiveFile
	for rows.Next() {
		var file model.ArchiveFile
		var createdAt string

		if err := rows.Scan(&file.ID, &file.ArchiveID, &file.ArchiverKey, &file.Filename, &file.MimeType, &file.FileSize, &file.StoragePath, &file.HashSha256, &file.Content, &file.ContentMimeType, &file.ThumbnailPath, &createdAt); err != nil {
			return nil, fmt.Errorf("failed to scan archive file: %w", err)
		}

		// Parse timestamp
		file.CreatedAt, _ = parseTimestamp(createdAt)

		files = append(files, &file)
	}

	return files, nil
}

// GetTotalSizeByLinkID calculates the total size of all archive files for a link
func (s *ArchiveFileStore) GetTotalSizeByLinkID(ctx context.Context, linkID string) (int64, error) {
	// Join archive_files with archives to get all files for a link
	sb := sqlbuilder.NewSelectBuilder()
	sb.Select("COALESCE(SUM(af.file_size), 0)")
	sb.From("archive_files af")
	sb.JoinWithOption(sqlbuilder.InnerJoin, "archives a", "af.archive_id = a.id")
	sb.Where(sb.Equal("a.link_id", linkID))

	query, args := sb.Build()
	row := s.readDB.QueryRowContext(ctx, query, args...)

	var totalSize int64
	err := row.Scan(&totalSize)
	if err != nil {
		return 0, fmt.Errorf("failed to calculate total size: %w", err)
	}

	return totalSize, nil
}

// DeleteByArchiveID deletes all archive files for an archive
func (s *ArchiveFileStore) DeleteByArchiveID(ctx context.Context, archiveID string) error {
	db := sqlbuilder.NewDeleteBuilder()
	db.DeleteFrom("archive_files")
	db.Where(db.Equal("archive_id", archiveID))

	query, args := db.Build()
	_, err := s.writeDB.ExecContext(ctx, query, args...)
	if err != nil {
		return fmt.Errorf("failed to delete archive files by archive ID: %w", err)
	}

	return nil
}

// ListByLinkID retrieves all archive files for a link (across all archives)
func (s *ArchiveFileStore) ListByLinkID(ctx context.Context, linkID string) ([]*model.ArchiveFile, error) {
	sb := sqlbuilder.NewSelectBuilder()
	sb.Select("af.id", "af.archive_id", "af.extractor_key", "af.filename", "af.mime_type", "af.file_size", "af.storage_path", "af.hash_sha256", "af.content", "af.content_mime_type", "af.thumbnail_path", "af.created_at")
	sb.From("archive_files af")
	sb.JoinWithOption(sqlbuilder.InnerJoin, "archives a", "af.archive_id = a.id")
	sb.Where(sb.Equal("a.link_id", linkID))
	sb.OrderBy("af.created_at ASC")

	query, args := sb.Build()
	rows, err := s.readDB.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to list archive files by link ID: %w", err)
	}
	defer func() { _ = rows.Close() }()

	var files []*model.ArchiveFile
	for rows.Next() {
		var file model.ArchiveFile
		var createdAt string

		if err := rows.Scan(&file.ID, &file.ArchiveID, &file.ArchiverKey, &file.Filename, &file.MimeType, &file.FileSize, &file.StoragePath, &file.HashSha256, &file.Content, &file.ContentMimeType, &file.ThumbnailPath, &createdAt); err != nil {
			return nil, fmt.Errorf("failed to scan archive file: %w", err)
		}

		// Parse timestamp
		file.CreatedAt, _ = parseTimestamp(createdAt)

		files = append(files, &file)
	}

	return files, nil
}

// UpdateContent updates the content and content_mime_type fields of an archive file
func (s *ArchiveFileStore) UpdateContent(ctx context.Context, fileID string, content string, contentMimeType string) error {
	ub := sqlbuilder.NewUpdateBuilder()
	ub.Update("archive_files")

	// Build the assignments array to ensure both fields are set
	assignments := []string{
		ub.Assign("content", content),
		ub.Assign("content_mime_type", contentMimeType),
	}

	ub.Set(assignments...)
	ub.Where(ub.Equal("id", fileID))

	query, args := ub.Build()
	result, err := s.writeDB.ExecContext(ctx, query, args...)
	if err != nil {
		return fmt.Errorf("failed to update archive file content: %w", err)
	}

	// Verify that a row was actually updated
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %w", err)
	}
	if rowsAffected == 0 {
		return fmt.Errorf("no rows updated for file ID: %s", fileID)
	}

	return nil
}

// SearchContent performs a full-text search on archive file content using FTS5
// Returns a list of archive file IDs that match the search query, filtered by userID
func (s *ArchiveFileStore) SearchContent(ctx context.Context, query string, userID string) ([]string, error) {
	// Use raw SQL for FTS5 MATCH query since sqlbuilder doesn't support FTS5 directly
	// The query parameter needs to be properly formatted for FTS5
	// FTS5 uses a special syntax: "term1 term2" for AND, "term1 OR term2" for OR
	sqlQuery := `
		SELECT DISTINCT af.id
		FROM archive_files_fts fts
		INNER JOIN archive_files af ON fts.id = af.id
		INNER JOIN archives a ON af.archive_id = a.id
		WHERE fts.content MATCH ? AND a.user_id = ?
		ORDER BY af.created_at DESC
	`

	rows, err := s.readDB.QueryContext(ctx, sqlQuery, query, userID)
	if err != nil {
		return nil, fmt.Errorf("failed to search archive file content: %w", err)
	}
	defer func() { _ = rows.Close() }()

	var fileIDs []string
	for rows.Next() {
		var fileID string
		if err := rows.Scan(&fileID); err != nil {
			return nil, fmt.Errorf("failed to scan search result: %w", err)
		}
		fileIDs = append(fileIDs, fileID)
	}

	return fileIDs, nil
}

// GetThumbnailByArchiveID retrieves the thumbnail file for an archive
// Returns the first archive file with MIME type matching image/*+thumbnail
func (s *ArchiveFileStore) GetThumbnailByArchiveID(ctx context.Context, archiveID string) (*model.ArchiveFile, error) {
	sb := sqlbuilder.NewSelectBuilder()
	sb.Select("id", "archive_id", "extractor_key", "filename", "mime_type", "file_size", "storage_path", "hash_sha256", "content", "content_mime_type", "thumbnail_path", "created_at")
	sb.From("archive_files")
	sb.Where(sb.Equal("archive_id", archiveID))
	sb.Where(sb.Like("mime_type", "image/%+thumbnail"))
	sb.OrderBy("created_at ASC")
	sb.Limit(1)

	query, args := sb.Build()
	row := s.readDB.QueryRowContext(ctx, query, args...)

	var file model.ArchiveFile
	var createdAt string

	err := row.Scan(&file.ID, &file.ArchiveID, &file.ArchiverKey, &file.Filename, &file.MimeType, &file.FileSize, &file.StoragePath, &file.HashSha256, &file.Content, &file.ContentMimeType, &file.ThumbnailPath, &createdAt)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil // No thumbnail found is not an error
		}
		return nil, fmt.Errorf("failed to get thumbnail: %w", err)
	}

	// Parse timestamp
	file.CreatedAt, _ = parseTimestamp(createdAt)

	return &file, nil
}

// GetThumbnailByLinkID retrieves the thumbnail file for a link's latest archive
// Returns the first archive file with MIME type matching image/*+thumbnail from the latest archive
func (s *ArchiveFileStore) GetThumbnailByLinkID(ctx context.Context, linkID string) (*model.ArchiveFile, error) {
	sb := sqlbuilder.NewSelectBuilder()
	sb.Select("af.id", "af.archive_id", "af.extractor_key", "af.filename", "af.mime_type", "af.file_size", "af.storage_path", "af.hash_sha256", "af.content", "af.content_mime_type", "af.thumbnail_path", "af.created_at")
	sb.From("archive_files af")
	sb.JoinWithOption(sqlbuilder.InnerJoin, "archives a", "af.archive_id = a.id")
	sb.Where(sb.Equal("a.link_id", linkID))
	sb.Where(sb.Like("af.mime_type", "image/%+thumbnail"))
	sb.OrderBy("a.created_at DESC", "af.created_at ASC")
	sb.Limit(1)

	query, args := sb.Build()
	row := s.readDB.QueryRowContext(ctx, query, args...)

	var file model.ArchiveFile
	var createdAt string

	err := row.Scan(&file.ID, &file.ArchiveID, &file.ArchiverKey, &file.Filename, &file.MimeType, &file.FileSize, &file.StoragePath, &file.HashSha256, &file.Content, &file.ContentMimeType, &file.ThumbnailPath, &createdAt)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil // No thumbnail found is not an error
		}
		return nil, fmt.Errorf("failed to get thumbnail: %w", err)
	}

	// Parse timestamp
	file.CreatedAt, _ = parseTimestamp(createdAt)

	return &file, nil
}