amcs/internal/store/files.go

package store

import (
	"context"
	"encoding/json"
	"fmt"
	"strings"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"

	thoughttypes "git.warky.dev/wdevs/amcs/internal/types"
)

func (db *DB) InsertStoredFile(ctx context.Context, file thoughttypes.StoredFile) (thoughttypes.StoredFile, error) {
	row := db.pool.QueryRow(ctx, `
		insert into stored_files (thought_id, project_id, name, media_type, kind, encoding, size_bytes, sha256, content)
		values ($1, $2, $3, $4, $5, $6, $7, $8, $9)
		returning guid, thought_id, project_id, name, media_type, kind, encoding, size_bytes, sha256, created_at, updated_at
	`, file.ThoughtID, file.ProjectID, file.Name, file.MediaType, file.Kind, file.Encoding, file.SizeBytes, file.SHA256, file.Content)

	var created thoughttypes.StoredFile
	if err := row.Scan(
		&created.ID,
		&created.ThoughtID,
		&created.ProjectID,
		&created.Name,
		&created.MediaType,
		&created.Kind,
		&created.Encoding,
		&created.SizeBytes,
		&created.SHA256,
		&created.CreatedAt,
		&created.UpdatedAt,
	); err != nil {
		return thoughttypes.StoredFile{}, fmt.Errorf("insert stored file: %w", err)
	}

	return created, nil
}

func (db *DB) GetStoredFile(ctx context.Context, id uuid.UUID) (thoughttypes.StoredFile, error) {
	row := db.pool.QueryRow(ctx, `
		select guid, thought_id, project_id, name, media_type, kind, encoding, size_bytes, sha256, content, created_at, updated_at
		from stored_files
		where guid = $1
	`, id)

	var file thoughttypes.StoredFile
	if err := row.Scan(
		&file.ID,
		&file.ThoughtID,
		&file.ProjectID,
		&file.Name,
		&file.MediaType,
		&file.Kind,
		&file.Encoding,
		&file.SizeBytes,
		&file.SHA256,
		&file.Content,
		&file.CreatedAt,
		&file.UpdatedAt,
	); err != nil {
		if err == pgx.ErrNoRows {
			return thoughttypes.StoredFile{}, err
		}
		return thoughttypes.StoredFile{}, fmt.Errorf("get stored file: %w", err)
	}

	return file, nil
}

func (db *DB) ListStoredFiles(ctx context.Context, filter thoughttypes.StoredFileFilter) ([]thoughttypes.StoredFile, error) {
	args := make([]any, 0, 4)
	conditions := make([]string, 0, 3)

	if filter.ThoughtID != nil {
		args = append(args, *filter.ThoughtID)
		conditions = append(conditions, fmt.Sprintf("thought_id = $%d", len(args)))
	}
	if filter.ProjectID != nil {
		args = append(args, *filter.ProjectID)
		conditions = append(conditions, fmt.Sprintf("project_id = $%d", len(args)))
	}
	if kind := strings.TrimSpace(filter.Kind); kind != "" {
		args = append(args, kind)
		conditions = append(conditions, fmt.Sprintf("kind = $%d", len(args)))
	}

	query := `
		select guid, thought_id, project_id, name, media_type, kind, encoding, size_bytes, sha256, created_at, updated_at
		from stored_files
	`
	if len(conditions) > 0 {
		query += " where " + strings.Join(conditions, " and ")
	}

	args = append(args, filter.Limit)
	query += fmt.Sprintf(" order by created_at desc limit $%d", len(args))

	rows, err := db.pool.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list stored files: %w", err)
	}
	defer rows.Close()

	files := make([]thoughttypes.StoredFile, 0, filter.Limit)
	for rows.Next() {
		var file thoughttypes.StoredFile
		if err := rows.Scan(
			&file.ID,
			&file.ThoughtID,
			&file.ProjectID,
			&file.Name,
			&file.MediaType,
			&file.Kind,
			&file.Encoding,
			&file.SizeBytes,
			&file.SHA256,
			&file.CreatedAt,
			&file.UpdatedAt,
		); err != nil {
			return nil, fmt.Errorf("scan stored file: %w", err)
		}
		files = append(files, file)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate stored files: %w", err)
	}

	return files, nil
}

func (db *DB) AddThoughtAttachment(ctx context.Context, thoughtID uuid.UUID, attachment thoughttypes.ThoughtAttachment) error {
	tx, err := db.pool.Begin(ctx)
	if err != nil {
		return fmt.Errorf("begin transaction: %w", err)
	}
	defer func() {
		_ = tx.Rollback(ctx)
	}()

	var metadataBytes []byte
	if err := tx.QueryRow(ctx, `select metadata from thoughts where guid = $1 for update`, thoughtID).Scan(&metadataBytes); err != nil {
		if err == pgx.ErrNoRows {
			return err
		}
		return fmt.Errorf("load thought metadata: %w", err)
	}

	var metadata thoughttypes.ThoughtMetadata
	if len(metadataBytes) > 0 {
		if err := json.Unmarshal(metadataBytes, &metadata); err != nil {
			return fmt.Errorf("decode thought metadata: %w", err)
		}
	}

	replaced := false
	for i := range metadata.Attachments {
		if metadata.Attachments[i].FileID == attachment.FileID {
			metadata.Attachments[i] = attachment
			replaced = true
			break
		}
	}
	if !replaced {
		metadata.Attachments = append(metadata.Attachments, attachment)
	}

	updatedMetadata, err := json.Marshal(metadata)
	if err != nil {
		return fmt.Errorf("encode thought metadata: %w", err)
	}

	tag, err := tx.Exec(ctx, `
		update thoughts
		set metadata = $2::jsonb,
		    updated_at = now()
		where guid = $1
	`, thoughtID, updatedMetadata)
	if err != nil {
		return fmt.Errorf("update thought attachments: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return pgx.ErrNoRows
	}

	if err := tx.Commit(ctx); err != nil {
		return fmt.Errorf("commit attachment update: %w", err)
	}

	return nil
}