mini-prog/backend/internal/store/postgres/asset_store.go

package postgres

import (
	"context"

	"github.com/jackc/pgx/v5"
)

type ManagedAssetRecord struct {
	ID              string
	PublicID        string
	AssetType       string
	AssetCode       string
	Version         string
	Title           *string
	SourceMode      string
	StorageProvider string
	ObjectKey       *string
	PublicURL       string
	FileName        *string
	ContentType     *string
	FileSizeBytes   *int64
	ChecksumSHA256  *string
	Status          string
	MetadataJSONB   map[string]any
}

type CreateManagedAssetParams struct {
	PublicID        string
	AssetType       string
	AssetCode       string
	Version         string
	Title           *string
	SourceMode      string
	StorageProvider string
	ObjectKey       *string
	PublicURL       string
	FileName        *string
	ContentType     *string
	FileSizeBytes   *int64
	ChecksumSHA256  *string
	Status          string
	MetadataJSONB   map[string]any
}

func (s *Store) CreateManagedAsset(ctx context.Context, tx pgx.Tx, params CreateManagedAssetParams) (*ManagedAssetRecord, error) {
	row := tx.QueryRow(ctx, `
		INSERT INTO managed_assets (
			asset_public_id, asset_type, asset_code, version, title, source_mode, storage_provider,
			object_key, public_url, file_name, content_type, file_size_bytes, checksum_sha256, status, metadata_jsonb
		) VALUES (
			$1, $2, $3, $4, $5, $6, $7,
			$8, $9, $10, $11, $12, $13, $14, COALESCE($15, '{}'::jsonb)
		)
		RETURNING id, asset_public_id, asset_type, asset_code, version, title, source_mode, storage_provider,
			object_key, public_url, file_name, content_type, file_size_bytes, checksum_sha256, status, metadata_jsonb
	`,
		params.PublicID, params.AssetType, params.AssetCode, params.Version, params.Title, params.SourceMode, params.StorageProvider,
		params.ObjectKey, params.PublicURL, params.FileName, params.ContentType, params.FileSizeBytes, params.ChecksumSHA256, params.Status, params.MetadataJSONB,
	)
	return scanManagedAsset(row)
}

func (s *Store) ListManagedAssets(ctx context.Context, limit int) ([]ManagedAssetRecord, error) {
	if limit <= 0 {
		limit = 20
	}
	rows, err := s.pool.Query(ctx, `
		SELECT id, asset_public_id, asset_type, asset_code, version, title, source_mode, storage_provider,
			object_key, public_url, file_name, content_type, file_size_bytes, checksum_sha256, status, metadata_jsonb
		FROM managed_assets
		ORDER BY created_at DESC
		LIMIT $1
	`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var items []ManagedAssetRecord
	for rows.Next() {
		record, err := scanManagedAsset(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, *record)
	}
	return items, rows.Err()
}

func (s *Store) GetManagedAssetByPublicID(ctx context.Context, publicID string) (*ManagedAssetRecord, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT id, asset_public_id, asset_type, asset_code, version, title, source_mode, storage_provider,
			object_key, public_url, file_name, content_type, file_size_bytes, checksum_sha256, status, metadata_jsonb
		FROM managed_assets
		WHERE asset_public_id = $1
	`, publicID)
	return scanManagedAsset(row)
}

type managedAssetScanner interface {
	Scan(dest ...any) error
}

func scanManagedAsset(scanner managedAssetScanner) (*ManagedAssetRecord, error) {
	var record ManagedAssetRecord
	err := scanner.Scan(
		&record.ID,
		&record.PublicID,
		&record.AssetType,
		&record.AssetCode,
		&record.Version,
		&record.Title,
		&record.SourceMode,
		&record.StorageProvider,
		&record.ObjectKey,
		&record.PublicURL,
		&record.FileName,
		&record.ContentType,
		&record.FileSizeBytes,
		&record.ChecksumSHA256,
		&record.Status,
		&record.MetadataJSONB,
	)
	if err != nil {
		if err == pgx.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	if record.MetadataJSONB == nil {
		record.MetadataJSONB = map[string]any{}
	}
	return &record, nil
}