package postgres import ( "context" "errors" "fmt" "github.com/jackc/pgx/v5" "gitea.zheromiku.top/Weknora/dobotai-platform/internal/feedback" ) const findFeedbackAnswerSQL = `SELECT m.id,t.id FROM messages m JOIN turns t ON t.id=m.correlation_id AND t.tenant_key=m.tenant_key AND t.channel=m.channel AND t.answer_message_id=m.id WHERE m.tenant_key=$1 AND m.channel=$2 AND m.provider_message_id=$3 AND m.direction='outbound' AND m.role='assistant' AND m.status='sent' AND m.delivery_method='card' AND t.status='succeeded'` const upsertFeedbackActorSQL = `INSERT INTO channel_actors (id,tenant_key,channel,external_actor_id,actor_type,display_name) VALUES($1,$2,$3,$4,'user','') ON CONFLICT (tenant_key,channel,external_actor_id) DO UPDATE SET updated_at=channel_actors.updated_at RETURNING id` const findFeedbackStateSQL = `SELECT id,value,reason_code FROM answer_feedback WHERE tenant_key=$1 AND channel=$2 AND answer_message_id=$3 AND actor_id=$4 FOR UPDATE` const insertFeedbackStateSQL = `INSERT INTO answer_feedback (id,tenant_key,channel,answer_message_id,turn_id,actor_id,value,reason_code,source) VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9)` const updateFeedbackStateSQL = `UPDATE answer_feedback SET value=$1,reason_code=$2,source=$3,updated_at=NOW() WHERE id=$4 AND tenant_key=$5 AND channel=$6 AND answer_message_id=$7 AND actor_id=$8` func (s *Store) RecordFeedback(ctx context.Context, record feedback.Record) (feedback.Result, error) { if err := validateFeedbackRecord(record); err != nil { return feedback.Result{}, err } tx, err := s.pool.BeginTx(ctx, pgx.TxOptions{}) if err != nil { return feedback.Result{}, fmt.Errorf("begin feedback transaction: %w", err) } defer func() { _ = tx.Rollback(ctx) }() var answerMessageID, turnID string err = tx.QueryRow(ctx, findFeedbackAnswerSQL, record.TenantKey, record.Channel, record.OutTrackID).Scan(&answerMessageID, &turnID) if errors.Is(err, pgx.ErrNoRows) { return feedback.Result{}, feedback.ErrTargetNotFound } if err != nil { return feedback.Result{}, fmt.Errorf("find feedback answer: %w", err) } lockKey := feedbackLockKey(record.TenantKey, record.Channel, answerMessageID, record.ExternalActorID) if _, err = tx.Exec(ctx, `SELECT pg_advisory_xact_lock(hashtextextended($1,0))`, lockKey); err != nil { return feedback.Result{}, fmt.Errorf("lock feedback state: %w", err) } if err = tx.QueryRow(ctx, upsertFeedbackActorSQL, record.ActorID, record.TenantKey, record.Channel, record.ExternalActorID, ).Scan(&record.ActorID); err != nil { return feedback.Result{}, fmt.Errorf("resolve feedback actor: %w", err) } var existingID, existingValue, existingReason string err = tx.QueryRow(ctx, findFeedbackStateSQL, record.TenantKey, record.Channel, answerMessageID, record.ActorID, ).Scan(&existingID, &existingValue, &existingReason) if errors.Is(err, pgx.ErrNoRows) { command, insertErr := tx.Exec(ctx, insertFeedbackStateSQL, record.ID, record.TenantKey, record.Channel, answerMessageID, turnID, record.ActorID, record.Value, record.ReasonCode, record.Source, ) if insertErr != nil { return feedback.Result{}, fmt.Errorf("insert feedback state: %w", insertErr) } if command.RowsAffected() != 1 { return feedback.Result{}, errors.New("insert feedback state affected unexpected rows") } if record.Value == feedback.ValueDown { if err = s.ensureFeedbackSupportCase(ctx, tx, record, record.ID, turnID); err != nil { return feedback.Result{}, err } } if err = tx.Commit(ctx); err != nil { return feedback.Result{}, fmt.Errorf("commit feedback create: %w", err) } return feedback.Result{Created: true, Value: record.Value, ReasonCode: record.ReasonCode}, nil } if err != nil { return feedback.Result{}, fmt.Errorf("find feedback state: %w", err) } if sameFeedbackState(existingValue, existingReason, record.Value, record.ReasonCode) { if record.Value == feedback.ValueDown { if err = s.ensureFeedbackSupportCase(ctx, tx, record, existingID, turnID); err != nil { return feedback.Result{}, err } } if err = tx.Commit(ctx); err != nil { return feedback.Result{}, fmt.Errorf("commit feedback replay: %w", err) } return feedback.Result{Replayed: true, Value: existingValue, ReasonCode: existingReason}, nil } command, err := tx.Exec(ctx, updateFeedbackStateSQL, record.Value, record.ReasonCode, record.Source, existingID, record.TenantKey, record.Channel, answerMessageID, record.ActorID, ) if err != nil { return feedback.Result{}, fmt.Errorf("update feedback state: %w", err) } if command.RowsAffected() != 1 { return feedback.Result{}, errors.New("update feedback state affected unexpected rows") } if record.Value == feedback.ValueDown { if err = s.ensureFeedbackSupportCase(ctx, tx, record, existingID, turnID); err != nil { return feedback.Result{}, err } } if err = tx.Commit(ctx); err != nil { return feedback.Result{}, fmt.Errorf("commit feedback change: %w", err) } return feedback.Result{Changed: true, Value: record.Value, ReasonCode: record.ReasonCode}, nil } func (s *Store) ensureFeedbackSupportCase(ctx context.Context, tx pgx.Tx, record feedback.Record, feedbackID, turnID string) error { var decisionID *string if err := tx.QueryRow(ctx, `SELECT (SELECT id FROM coverage_decisions WHERE tenant_key=$1 AND channel=$2 AND turn_id=$3)`, record.TenantKey, record.Channel, turnID, ).Scan(&decisionID); err != nil { return fmt.Errorf("find feedback coverage decision: %w", err) } caseID, err := newUUID() if err != nil { return fmt.Errorf("generate feedback support case ID: %w", err) } _, err = tx.Exec(ctx, `INSERT INTO support_cases (id,tenant_key,channel,turn_id,coverage_decision_id,status,faq_export_status,trigger_type,trigger_feedback_id) VALUES($1,$2,$3,$4,$5,'pending',$6,'feedback_down',$7) ON CONFLICT (tenant_key,channel,turn_id) DO NOTHING`, caseID, record.TenantKey, record.Channel, turnID, decisionID, faqExportStatus(s.faqExportEnabled), feedbackID, ) if err != nil { return fmt.Errorf("insert feedback support case: %w", err) } return nil } func validateFeedbackRecord(record feedback.Record) error { if record.ID == "" || record.ActorID == "" || record.TenantKey == "" || record.Channel == "" || record.ExternalActorID == "" || record.OutTrackID == "" || record.Source != feedback.SourceDingTalkCard { return feedback.ErrInvalidCommand } if record.Value != feedback.ValueUp && record.Value != feedback.ValueDown { return feedback.ErrInvalidCommand } if record.Value == feedback.ValueUp && record.ReasonCode != "" { return feedback.ErrInvalidCommand } return nil } func feedbackLockKey(parts ...string) string { key := "" for _, part := range parts { key += fmt.Sprintf("%d:%s|", len(part), part) } return key } func sameFeedbackState(existingValue, existingReason, value, reason string) bool { return existingValue == value && existingReason == reason } var _ feedback.Repository = (*Store)(nil)