package postgres import ( "context" "errors" "fmt" "time" "github.com/jackc/pgx/v5" "gitea.privcloud.tech/dizi/mtusi2026lab7/internal/model" ) const attachmentColumns = ` id, chat_id, owner_id, filename, content_type, size_bytes, sha256, object_key, status, created_at, updated_at, ready_at, pipeline_version, error_code, staging_key` type rowScanner interface { Scan(...any) error } func (s *Store) CreatePendingAttachment(ctx context.Context, attachment model.Attachment) (model.Attachment, error) { query := ` INSERT INTO attachments ( id, chat_id, owner_id, filename, content_type, size_bytes, sha256, object_key, status, ready_at ) SELECT $1, $2, $3, $4, $5, $6, $7, $8, 'pending', NULL WHERE EXISTS ( SELECT 1 FROM chat_members WHERE chat_id = $2 AND user_id = $3 ) RETURNING ` + attachmentColumns err := scanAttachment(s.pool.QueryRow( ctx, query, attachment.ID, attachment.ChatID, attachment.OwnerID, attachment.Filename, attachment.ContentType, attachment.SizeBytes, attachment.SHA256, attachment.ObjectKey, ), &attachment) if errors.Is(err, pgx.ErrNoRows) { return model.Attachment{}, ErrNotFound } if err != nil { return model.Attachment{}, fmt.Errorf("create pending attachment: %w", err) } return publicAttachment(attachment), nil } func (s *Store) MarkAttachmentReady(ctx context.Context, attachmentID, objectKey string) (model.Attachment, error) { query := ` UPDATE attachments SET status = 'ready', ready_at = clock_timestamp(), updated_at = clock_timestamp() WHERE id = $1 AND object_key = $2 AND status = 'pending' RETURNING ` + attachmentColumns var attachment model.Attachment err := scanAttachment(s.pool.QueryRow(ctx, query, attachmentID, objectKey), &attachment) if errors.Is(err, pgx.ErrNoRows) { return model.Attachment{}, ErrNotFound } if err != nil { return model.Attachment{}, fmt.Errorf("mark attachment ready: %w", err) } return publicAttachment(attachment), nil } func (s *Store) DeletePendingAttachment(ctx context.Context, attachmentID, objectKey string) error { const query = `DELETE FROM attachments WHERE id = $1 AND object_key = $2 AND status = 'pending'` if _, err := s.pool.Exec(ctx, query, attachmentID, objectKey); err != nil { return fmt.Errorf("delete pending attachment: %w", err) } return nil } func (s *Store) AttachmentForUser(ctx context.Context, attachmentID, userID string) (model.Attachment, error) { query := ` SELECT ` + attachmentColumnsWithAlias("a") + ` FROM attachments a JOIN chat_members member ON member.chat_id = a.chat_id AND member.user_id = $2 WHERE a.id = $1 AND a.status IN ('queued', 'processing', 'ready', 'failed')` var attachment model.Attachment err := scanAttachment(s.pool.QueryRow(ctx, query, attachmentID, userID), &attachment) if errors.Is(err, pgx.ErrNoRows) { return model.Attachment{}, ErrNotFound } if err != nil { return model.Attachment{}, fmt.Errorf("get attachment for user: %w", err) } if attachment.Status == model.AttachmentReady { attachment.Variants, err = s.attachmentVariants(ctx, attachment.ID, attachment.PipelineVersion) if err != nil { return model.Attachment{}, err } } return publicAttachment(attachment), nil } func (s *Store) AttachmentVariantForUser( ctx context.Context, attachmentID string, userID string, variantName string, ) (model.Attachment, model.Variant, error) { attachment, err := s.AttachmentForUser(ctx, attachmentID, userID) if err != nil { return model.Attachment{}, model.Variant{}, err } if attachment.Status != model.AttachmentReady { return model.Attachment{}, model.Variant{}, ErrConflict } for _, variant := range attachment.Variants { if variant.Name == variantName { return attachment, variant, nil } } if variantName == "original" && attachment.ObjectKey != "" { return attachment, model.Variant{ Name: "original", ContentType: attachment.ContentType, SizeBytes: attachment.SizeBytes, SHA256: attachment.SHA256, ObjectKey: attachment.ObjectKey, PipelineVersion: attachment.PipelineVersion, }, nil } return model.Attachment{}, model.Variant{}, ErrNotFound } func (s *Store) attachmentVariants(ctx context.Context, attachmentID string, pipelineVersion int) ([]model.Variant, error) { const query = ` SELECT name, content_type, size_bytes, sha256, width, height, object_key, pipeline_version FROM attachment_variants WHERE attachment_id = $1 AND pipeline_version = $2 ORDER BY CASE name WHEN 'original' THEN 0 WHEN 'thumbnail' THEN 1 WHEN 'crop' THEN 2 WHEN 'optimized' THEN 3 ELSE 4 END` rows, err := s.pool.Query(ctx, query, attachmentID, pipelineVersion) if err != nil { return nil, fmt.Errorf("list attachment variants: %w", err) } defer rows.Close() variants, err := pgx.CollectRows(rows, pgx.RowToStructByPos[model.Variant]) if err != nil { return nil, fmt.Errorf("scan attachment variants: %w", err) } return variants, nil } func (s *Store) AttachmentByStorage(ctx context.Context, attachmentID, objectKey string) (model.Attachment, error) { const query = `SELECT ` + attachmentColumns + ` FROM attachments WHERE id = $1 AND object_key = $2` var attachment model.Attachment err := scanAttachment(s.pool.QueryRow(ctx, query, attachmentID, objectKey), &attachment) if errors.Is(err, pgx.ErrNoRows) { return model.Attachment{}, ErrNotFound } if err != nil { return model.Attachment{}, fmt.Errorf("get attachment by storage: %w", err) } return publicAttachment(attachment), nil } func (s *Store) AttachmentExistsByObjectKey(ctx context.Context, objectKey string) (bool, error) { const query = ` SELECT EXISTS ( SELECT 1 FROM attachments WHERE object_key = $1 OR staging_key = $1 UNION ALL SELECT 1 FROM attachment_variants WHERE object_key = $1 )` var exists bool if err := s.pool.QueryRow(ctx, query, objectKey).Scan(&exists); err != nil { return false, fmt.Errorf("check attachment by object key: %w", err) } return exists, nil } func (s *Store) AttachmentObjectKeys(ctx context.Context, attachmentID string) ([]string, error) { const query = ` SELECT object_key FROM ( SELECT object_key FROM attachments WHERE id = $1 AND object_key <> '' UNION SELECT object_key FROM attachment_variants WHERE attachment_id = $1 ) objects ORDER BY object_key` rows, err := s.pool.Query(ctx, query, attachmentID) if err != nil { return nil, fmt.Errorf("list attachment object keys: %w", err) } defer rows.Close() keys, err := pgx.CollectRows(rows, pgx.RowTo[string]) if err != nil { return nil, fmt.Errorf("scan attachment object keys: %w", err) } return keys, nil } func (s *Store) ListAttachments(ctx context.Context, afterObjectKey string, limit int) ([]model.Attachment, string, error) { if limit < 1 || limit > 1000 { return nil, "", fmt.Errorf("attachment list limit must be between 1 and 1000") } query := ` SELECT ` + attachmentColumnsWithAlias("a") + `, EXISTS (SELECT 1 FROM message_attachments ma WHERE ma.attachment_id = a.id), objects.object_key FROM ( SELECT id AS attachment_id, object_key FROM attachments WHERE object_key <> '' UNION SELECT attachment_id, object_key FROM attachment_variants ) objects JOIN attachments a ON a.id = objects.attachment_id WHERE objects.object_key > $1 ORDER BY objects.object_key LIMIT $2` rows, err := s.pool.Query(ctx, query, afterObjectKey, limit+1) if err != nil { return nil, "", fmt.Errorf("list attachments: %w", err) } defer rows.Close() attachments, err := pgx.CollectRows(rows, pgx.RowToFunc[model.Attachment](func(row pgx.CollectableRow) (model.Attachment, error) { var attachment model.Attachment var objectKey string err := scanAttachmentWithReferenceAndObjectKey(row, &attachment, &objectKey) attachment.ObjectKey = objectKey return publicAttachment(attachment), err })) if err != nil { return nil, "", fmt.Errorf("scan attachments: %w", err) } var next string if len(attachments) > limit { attachments = attachments[:limit] next = attachments[len(attachments)-1].ObjectKey } return attachments, next, nil } func scanAttachmentWithReferenceAndObjectKey( row rowScanner, attachment *model.Attachment, objectKey *string, ) error { var stagingKey *string err := row.Scan( &attachment.ID, &attachment.ChatID, &attachment.OwnerID, &attachment.Filename, &attachment.ContentType, &attachment.SizeBytes, &attachment.SHA256, &attachment.ObjectKey, &attachment.Status, &attachment.CreatedAt, &attachment.UpdatedAt, &attachment.ReadyAt, &attachment.PipelineVersion, &attachment.ErrorCode, &stagingKey, &attachment.Referenced, objectKey, ) if stagingKey != nil { attachment.StagingKey = *stagingKey } return err } func (s *Store) AttachmentReferenced(ctx context.Context, attachmentID string) (bool, error) { const query = `SELECT EXISTS (SELECT 1 FROM message_attachments WHERE attachment_id = $1)` var referenced bool if err := s.pool.QueryRow(ctx, query, attachmentID).Scan(&referenced); err != nil { return false, fmt.Errorf("check attachment references: %w", err) } return referenced, nil } func (s *Store) ClaimAttachmentForDeletion(ctx context.Context, candidate model.Attachment, createdBefore time.Time) (bool, error) { tx, err := s.pool.BeginTx(ctx, pgx.TxOptions{}) if err != nil { return false, fmt.Errorf("begin attachment deletion claim: %w", err) } defer func() { _ = tx.Rollback(context.WithoutCancel(ctx)) }() var status string var createdAt time.Time var readyAt *time.Time err = tx.QueryRow( ctx, `SELECT status, created_at, ready_at FROM attachments WHERE id = $1 FOR UPDATE`, candidate.ID, ).Scan(&status, &createdAt, &readyAt) if errors.Is(err, pgx.ErrNoRows) { return false, nil } if err != nil { return false, fmt.Errorf("lock attachment deletion candidate: %w", err) } if status != candidate.Status { return false, nil } var age time.Time switch status { case model.AttachmentPending: age = createdAt case model.AttachmentReady: if readyAt == nil { return false, nil } age = *readyAt default: return false, nil } if age.After(createdBefore) { return false, nil } var referenced bool if err := tx.QueryRow(ctx, `SELECT EXISTS (SELECT 1 FROM message_attachments WHERE attachment_id = $1)`, candidate.ID).Scan(&referenced); err != nil { return false, fmt.Errorf("recheck attachment references: %w", err) } if referenced { return false, nil } if _, err := tx.Exec( ctx, `UPDATE attachments SET status = 'deleting', updated_at = clock_timestamp() WHERE id = $1 AND status = $2`, candidate.ID, candidate.Status, ); err != nil { return false, fmt.Errorf("mark attachment deleting: %w", err) } if err := tx.Commit(ctx); err != nil { return false, fmt.Errorf("commit attachment deletion claim: %w", err) } return true, nil } func (s *Store) DeleteAttachmentIfDeleting(ctx context.Context, attachmentID string) (bool, error) { const query = ` DELETE FROM attachments a WHERE a.id = $1 AND a.status = 'deleting' AND NOT EXISTS (SELECT 1 FROM message_attachments ma WHERE ma.attachment_id = a.id)` result, err := s.pool.Exec(ctx, query, attachmentID) if err != nil { return false, fmt.Errorf("delete claimed attachment: %w", err) } return result.RowsAffected() == 1, nil } func scanAttachment(row rowScanner, attachment *model.Attachment) error { var stagingKey *string err := row.Scan( &attachment.ID, &attachment.ChatID, &attachment.OwnerID, &attachment.Filename, &attachment.ContentType, &attachment.SizeBytes, &attachment.SHA256, &attachment.ObjectKey, &attachment.Status, &attachment.CreatedAt, &attachment.UpdatedAt, &attachment.ReadyAt, &attachment.PipelineVersion, &attachment.ErrorCode, &stagingKey, ) if stagingKey != nil { attachment.StagingKey = *stagingKey } return err } func attachmentColumnsWithAlias(alias string) string { return fmt.Sprintf( "%[1]s.id, %[1]s.chat_id, %[1]s.owner_id, %[1]s.filename, %[1]s.content_type, "+ "%[1]s.size_bytes, %[1]s.sha256, %[1]s.object_key, %[1]s.status, "+ "%[1]s.created_at, %[1]s.updated_at, %[1]s.ready_at, %[1]s.pipeline_version, "+ "%[1]s.error_code, %[1]s.staging_key", alias, ) } func publicAttachment(attachment model.Attachment) model.Attachment { variants := make([]model.Variant, 0, len(attachment.Variants)) for _, variant := range attachment.Variants { if variant.Name != "original" { variants = append(variants, variant) } } attachment.Variants = variants return attachment }