443 lines
17 KiB
Go
443 lines
17 KiB
Go
package store
|
|
|
|
import (
|
|
"context"
|
|
"crypto/rand"
|
|
"crypto/sha256"
|
|
"crypto/subtle"
|
|
"database/sql"
|
|
"encoding/base64"
|
|
"encoding/hex"
|
|
"errors"
|
|
"strings"
|
|
"time"
|
|
|
|
"lektorenplan/domain"
|
|
)
|
|
|
|
// Swap request states.
|
|
const (
|
|
SwapPending = "pending"
|
|
SwapAccepted = "accepted"
|
|
SwapDeclined = "declined"
|
|
SwapCancelled = "cancelled"
|
|
)
|
|
|
|
// SwapRequest is one proposed exchange of two assignments.
|
|
type SwapRequest struct {
|
|
ID string
|
|
TenantID string
|
|
RequesterUserID string
|
|
RequesterPersonID string
|
|
TargetUserID string
|
|
TargetPersonID string
|
|
OfferedAssignmentID string
|
|
WantedAssignmentID string
|
|
Status string
|
|
TokenHash string
|
|
CreatedAt time.Time
|
|
RespondedAt time.Time
|
|
}
|
|
|
|
// SwapAssignmentInfo bundles an assignment with its service.
|
|
type SwapAssignmentInfo struct {
|
|
Assignment domain.Assignment
|
|
Service domain.ServiceOccurrence
|
|
}
|
|
|
|
func swapTokenHash(token string) string {
|
|
sum := sha256.Sum256([]byte(token))
|
|
return hex.EncodeToString(sum[:])
|
|
}
|
|
|
|
// SwapTokenMatches reports whether a plaintext e-mail token belongs to the
|
|
// stored request.
|
|
func SwapTokenMatches(request *SwapRequest, token string) bool {
|
|
if request == nil || request.TokenHash == "" || token == "" {
|
|
return false
|
|
}
|
|
return subtle.ConstantTimeCompare([]byte(request.TokenHash), []byte(swapTokenHash(token))) == 1
|
|
}
|
|
|
|
func newSwapToken() (string, error) {
|
|
raw := make([]byte, 32)
|
|
if _, err := rand.Read(raw); err != nil {
|
|
return "", err
|
|
}
|
|
return base64.RawURLEncoding.EncodeToString(raw), nil
|
|
}
|
|
|
|
func scanSwapRequest(row rowScanner) (*SwapRequest, error) {
|
|
var request SwapRequest
|
|
var created, responded string
|
|
if err := row.Scan(&request.ID, &request.TenantID, &request.RequesterUserID,
|
|
&request.RequesterPersonID, &request.TargetUserID, &request.TargetPersonID,
|
|
&request.OfferedAssignmentID, &request.WantedAssignmentID, &request.Status,
|
|
&request.TokenHash, &created, &responded); err != nil {
|
|
return nil, err
|
|
}
|
|
request.CreatedAt = parseTime(created)
|
|
request.RespondedAt = parseTime(responded)
|
|
return &request, nil
|
|
}
|
|
|
|
const swapRequestColumns = `id, tenant_id, requester_user_id, requester_person_id,
|
|
target_user_id, target_person_id, offered_assignment_id, wanted_assignment_id,
|
|
status, token_hash, created_at, responded_at`
|
|
|
|
// SwapRequestByToken loads a request by its unhashed e-mail token.
|
|
func (s *Store) SwapRequestByToken(ctx context.Context, tenantID, token string) (*SwapRequest, error) {
|
|
row := s.db.QueryRowContext(ctx, `SELECT `+swapRequestColumns+`
|
|
FROM swap_requests WHERE tenant_id=? AND token_hash=?`,
|
|
tenantID, swapTokenHash(strings.TrimSpace(token)))
|
|
request, err := scanSwapRequest(row)
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
return nil, ErrNotFound
|
|
}
|
|
return request, err
|
|
}
|
|
|
|
// SwapRequestByID loads one request of a tenant.
|
|
func (s *Store) SwapRequestByID(ctx context.Context, tenantID, id string) (*SwapRequest, error) {
|
|
row := s.db.QueryRowContext(ctx, `SELECT `+swapRequestColumns+`
|
|
FROM swap_requests WHERE tenant_id=? AND id=?`, tenantID, id)
|
|
request, err := scanSwapRequest(row)
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
return nil, ErrNotFound
|
|
}
|
|
return request, err
|
|
}
|
|
|
|
// swapAssignmentInfo loads one assignment together with its service.
|
|
func (s *Store) SwapAssignmentInfo(ctx context.Context, tenantID, assignmentID string) (*SwapAssignmentInfo, error) {
|
|
row := s.db.QueryRowContext(ctx, `
|
|
SELECT a.id, a.service_id, a.person_id, a.role, a.locked, a.note, a.created_at,
|
|
s.id, s.church_id, s.location, s.template_id, s.date, s.time_minutes,
|
|
s.title, s.lectors_needed, s.helpers_needed, s.kind, s.notes,
|
|
s.locked, s.created_at
|
|
FROM assignments a
|
|
JOIN services s ON s.tenant_id=a.tenant_id AND s.id=a.service_id
|
|
WHERE a.tenant_id=? AND a.id=?`, tenantID, assignmentID)
|
|
var info SwapAssignmentInfo
|
|
var role, aCreated, date, kind, sCreated, location string
|
|
var templateID sql.NullString
|
|
var aLocked, sLocked int
|
|
if err := row.Scan(&info.Assignment.ID, &info.Assignment.ServiceID,
|
|
&info.Assignment.PersonID, &role, &aLocked, &info.Assignment.Note, &aCreated,
|
|
&info.Service.ID, &info.Service.ChurchID, &location, &templateID, &date,
|
|
&info.Service.TimeMinutes, &info.Service.Title, &info.Service.LectorsNeeded,
|
|
&info.Service.HelpersNeeded, &kind, &info.Service.Notes, &sLocked, &sCreated); err != nil {
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
return nil, ErrNotFound
|
|
}
|
|
return nil, err
|
|
}
|
|
info.Assignment.Role = domain.Role(role)
|
|
info.Assignment.Locked = aLocked != 0
|
|
info.Assignment.CreatedAt = parseTime(aCreated)
|
|
if location != "" {
|
|
info.Service.Location = &location
|
|
}
|
|
if templateID.Valid && templateID.String != "" {
|
|
value := templateID.String
|
|
info.Service.TemplateID = &value
|
|
}
|
|
info.Service.Date = domain.MustDate(date)
|
|
info.Service.Kind = domain.ServiceKind(kind)
|
|
info.Service.Locked = sLocked != 0
|
|
info.Service.CreatedAt = parseTime(sCreated)
|
|
return &info, nil
|
|
}
|
|
|
|
func (s *Store) personAbsentOn(ctx context.Context, tenantID, personID string, date time.Time) bool {
|
|
var count int
|
|
err := s.db.QueryRowContext(ctx, `
|
|
SELECT COUNT(*) FROM absences
|
|
WHERE tenant_id=? AND person_id=? AND start_date<=? AND end_date>=?`,
|
|
tenantID, personID, domain.Key(date), domain.Key(date)).Scan(&count)
|
|
return err == nil && count > 0
|
|
}
|
|
|
|
func (s *Store) assignmentConflict(ctx context.Context, tenantID, serviceID, personID string, role domain.Role, excludeID string) bool {
|
|
var count int
|
|
err := s.db.QueryRowContext(ctx, `
|
|
SELECT COUNT(*) FROM assignments
|
|
WHERE tenant_id=? AND service_id=? AND person_id=? AND role=? AND id<>?`,
|
|
tenantID, serviceID, personID, string(role), excludeID).Scan(&count)
|
|
return err == nil && count > 0
|
|
}
|
|
|
|
func (s *Store) pendingSwapForAssignment(ctx context.Context, tenantID, assignmentID, excludeRequestID string) bool {
|
|
var count int
|
|
err := s.db.QueryRowContext(ctx, `
|
|
SELECT COUNT(*) FROM swap_requests
|
|
WHERE tenant_id=? AND status=? AND id<>?
|
|
AND (offered_assignment_id=? OR wanted_assignment_id=?)`,
|
|
tenantID, SwapPending, excludeRequestID, assignmentID, assignmentID).Scan(&count)
|
|
return err == nil && count > 0
|
|
}
|
|
|
|
// swapConsentExists verifies the mutual address release that makes both the
|
|
// request and the response e-mail possible.
|
|
func (s *Store) swapConsentExists(ctx context.Context, tenantID, requesterUserID, requesterPersonID, targetUserID, targetPersonID string) bool {
|
|
return s.ContactShareExists(ctx, tenantID, targetPersonID, requesterUserID) &&
|
|
s.ContactShareExists(ctx, tenantID, requesterPersonID, targetUserID)
|
|
}
|
|
|
|
// validateSwapPair checks every planning and privacy precondition for a swap.
|
|
// excludeRequestID is the request currently being answered, if any.
|
|
func (s *Store) validateSwapPair(ctx context.Context, tenantID, requesterPersonID, targetPersonID, offeredID, wantedID, excludeRequestID string) (*SwapAssignmentInfo, *SwapAssignmentInfo, error) {
|
|
if requesterPersonID == "" || targetPersonID == "" || offeredID == "" || wantedID == "" {
|
|
return nil, nil, errors.New("Für den Tausch fehlen Angaben")
|
|
}
|
|
if requesterPersonID == targetPersonID {
|
|
return nil, nil, errors.New("Ein Tausch mit sich selbst ist nicht möglich")
|
|
}
|
|
offered, err := s.SwapAssignmentInfo(ctx, tenantID, offeredID)
|
|
if err != nil {
|
|
return nil, nil, errors.New("Der angebotene Einsatz wurde nicht gefunden")
|
|
}
|
|
wanted, err := s.SwapAssignmentInfo(ctx, tenantID, wantedID)
|
|
if err != nil {
|
|
return nil, nil, errors.New("Der gewünschte Einsatz wurde nicht gefunden")
|
|
}
|
|
if offered.Assignment.PersonID != requesterPersonID {
|
|
return nil, nil, errors.New("Der angebotene Einsatz gehört nicht mehr zu Ihnen")
|
|
}
|
|
if wanted.Assignment.PersonID != targetPersonID {
|
|
return nil, nil, errors.New("Der gewünschte Einsatz gehört nicht mehr zur angefragten Person")
|
|
}
|
|
if offered.Assignment.Role != wanted.Assignment.Role {
|
|
return nil, nil, errors.New("Es können nur Einsätze derselben Rolle getauscht werden")
|
|
}
|
|
today := domain.Today()
|
|
if offered.Service.Date.Before(today) || wanted.Service.Date.Before(today) {
|
|
return nil, nil, errors.New("Vergangene Einsätze können nicht getauscht werden")
|
|
}
|
|
if offered.Service.Locked || offered.Assignment.Locked || wanted.Service.Locked || wanted.Assignment.Locked {
|
|
return nil, nil, errors.New("Gesperrte Einsätze können nicht getauscht werden")
|
|
}
|
|
if s.personAbsentOn(ctx, tenantID, targetPersonID, offered.Service.Date) {
|
|
return nil, nil, errors.New("Die angefragte Person ist am angebotenen Termin als abwesend eingetragen")
|
|
}
|
|
if s.personAbsentOn(ctx, tenantID, requesterPersonID, wanted.Service.Date) {
|
|
return nil, nil, errors.New("Sie sind am gewünschten Termin als abwesend eingetragen")
|
|
}
|
|
if s.assignmentConflict(ctx, tenantID, wanted.Service.ID, requesterPersonID, wanted.Assignment.Role, wanted.Assignment.ID) {
|
|
return nil, nil, errors.New("Sie sind für diesen Gottesdienst bereits in derselben Rolle eingeteilt")
|
|
}
|
|
if s.assignmentConflict(ctx, tenantID, offered.Service.ID, targetPersonID, offered.Assignment.Role, offered.Assignment.ID) {
|
|
return nil, nil, errors.New("Die angefragte Person ist für diesen Gottesdienst bereits in derselben Rolle eingeteilt")
|
|
}
|
|
if s.pendingSwapForAssignment(ctx, tenantID, offered.Assignment.ID, excludeRequestID) ||
|
|
s.pendingSwapForAssignment(ctx, tenantID, wanted.Assignment.ID, excludeRequestID) {
|
|
return nil, nil, errors.New("Für einen der beiden Einsätze liegt bereits eine offene Tauschanfrage vor")
|
|
}
|
|
return offered, wanted, nil
|
|
}
|
|
|
|
// SwapOptions lists the requester's future assignments that can be offered for
|
|
// the wanted assignment of the target person.
|
|
func (s *Store) SwapOptions(ctx context.Context, tenantID, requesterPersonID, targetPersonID, wantedAssignmentID string) ([]SwapAssignmentInfo, error) {
|
|
wanted, err := s.SwapAssignmentInfo(ctx, tenantID, wantedAssignmentID)
|
|
if err != nil {
|
|
return nil, errors.New("Der gewünschte Einsatz wurde nicht gefunden")
|
|
}
|
|
if wanted.Assignment.PersonID != targetPersonID {
|
|
return nil, errors.New("Der gewünschte Einsatz gehört nicht mehr zur angefragten Person")
|
|
}
|
|
if wanted.Service.Date.Before(domain.Today()) {
|
|
return nil, errors.New("Vergangene Einsätze können nicht getauscht werden")
|
|
}
|
|
if wanted.Service.Locked || wanted.Assignment.Locked {
|
|
return nil, errors.New("Gesperrte Einsätze können nicht getauscht werden")
|
|
}
|
|
assignments, err := s.AssignmentsForPerson(ctx, tenantID, requesterPersonID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
out := make([]SwapAssignmentInfo, 0, len(assignments))
|
|
for _, assignment := range assignments {
|
|
if assignment.Role != wanted.Assignment.Role || assignment.ID == wanted.Assignment.ID {
|
|
continue
|
|
}
|
|
service, err := s.Service(ctx, tenantID, assignment.ServiceID)
|
|
if err != nil || service.Date.Before(domain.Today()) {
|
|
continue
|
|
}
|
|
offered, _, err := s.validateSwapPair(ctx, tenantID, requesterPersonID,
|
|
targetPersonID, assignment.ID, wantedAssignmentID, "")
|
|
if err != nil {
|
|
continue
|
|
}
|
|
out = append(out, *offered)
|
|
}
|
|
return out, nil
|
|
}
|
|
|
|
// CreateSwapRequest validates a proposed exchange, stores it and returns the
|
|
// plaintext e-mail token. Only the token hash is persisted.
|
|
func (s *Store) CreateSwapRequest(ctx context.Context, tenantID, requesterUserID, requesterPersonID, targetUserID, targetPersonID, offeredID, wantedID string) (*SwapRequest, string, error) {
|
|
if !s.swapConsentExists(ctx, tenantID, requesterUserID, requesterPersonID, targetUserID, targetPersonID) {
|
|
return nil, "", errors.New("Für eine Tauschanfrage müssen beide Seiten ihre E-Mail-Adresse gegenseitig freigegeben haben")
|
|
}
|
|
if _, _, err := s.validateSwapPair(ctx, tenantID, requesterPersonID, targetPersonID, offeredID, wantedID, ""); err != nil {
|
|
return nil, "", err
|
|
}
|
|
token, err := newSwapToken()
|
|
if err != nil {
|
|
return nil, "", err
|
|
}
|
|
now := time.Now().UTC()
|
|
request := &SwapRequest{
|
|
ID: domain.NewID(), TenantID: tenantID,
|
|
RequesterUserID: requesterUserID, RequesterPersonID: requesterPersonID,
|
|
TargetUserID: targetUserID, TargetPersonID: targetPersonID,
|
|
OfferedAssignmentID: offeredID, WantedAssignmentID: wantedID,
|
|
Status: SwapPending, TokenHash: swapTokenHash(token), CreatedAt: now,
|
|
}
|
|
_, err = s.db.ExecContext(ctx, `
|
|
INSERT INTO swap_requests(id, tenant_id, requester_user_id, requester_person_id,
|
|
target_user_id, target_person_id, offered_assignment_id, wanted_assignment_id,
|
|
status, token_hash, created_at, responded_at)
|
|
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '')`,
|
|
request.ID, request.TenantID, request.RequesterUserID, request.RequesterPersonID,
|
|
request.TargetUserID, request.TargetPersonID, request.OfferedAssignmentID,
|
|
request.WantedAssignmentID, request.Status, request.TokenHash,
|
|
request.CreatedAt.Format(time.RFC3339))
|
|
if err != nil {
|
|
return nil, "", err
|
|
}
|
|
return request, token, nil
|
|
}
|
|
|
|
// RespondSwapRequest accepts or declines a request on behalf of the target
|
|
// account. Accepting swaps both assignments atomically and revalidates every
|
|
// planning rule first.
|
|
func (s *Store) RespondSwapRequest(ctx context.Context, tenantID, token, targetUserID string, accept bool) (*SwapRequest, error) {
|
|
request, err := s.SwapRequestByToken(ctx, tenantID, token)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
if request.TargetUserID != targetUserID {
|
|
return nil, errors.New("Diese Tauschanfrage richtet sich an ein anderes Konto")
|
|
}
|
|
if request.Status != SwapPending {
|
|
return nil, errors.New("Diese Tauschanfrage wurde bereits beantwortet")
|
|
}
|
|
now := time.Now().UTC()
|
|
if !accept {
|
|
result, err := s.db.ExecContext(ctx, `
|
|
UPDATE swap_requests SET status=?, responded_at=?
|
|
WHERE tenant_id=? AND id=? AND status=?`,
|
|
SwapDeclined, now.Format(time.RFC3339), tenantID, request.ID, SwapPending)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
if affected, _ := result.RowsAffected(); affected == 0 {
|
|
return nil, errors.New("Diese Tauschanfrage wurde bereits beantwortet")
|
|
}
|
|
request.Status = SwapDeclined
|
|
request.RespondedAt = now
|
|
return request, nil
|
|
}
|
|
|
|
offered, wanted, err := s.validateSwapPair(ctx, tenantID, request.RequesterPersonID,
|
|
request.TargetPersonID, request.OfferedAssignmentID, request.WantedAssignmentID, request.ID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
if !s.swapConsentExists(ctx, tenantID, request.RequesterUserID, request.RequesterPersonID,
|
|
request.TargetUserID, request.TargetPersonID) {
|
|
return nil, errors.New("Die gegenseitige E-Mail-Freigabe wurde inzwischen entzogen; der Tausch kann nicht angenommen werden")
|
|
}
|
|
tempPerson := "swap-tmp-" + request.ID
|
|
err = s.transaction(ctx, func(tx *sql.Tx) error {
|
|
update := func(personID, assignmentID, expected string) error {
|
|
result, err := tx.ExecContext(ctx, `
|
|
UPDATE assignments SET person_id=?
|
|
WHERE tenant_id=? AND id=? AND person_id=?`,
|
|
personID, tenantID, assignmentID, expected)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if affected, _ := result.RowsAffected(); affected != 1 {
|
|
return errors.New("Die Einteilung hat sich inzwischen geändert")
|
|
}
|
|
return nil
|
|
}
|
|
if err := update(tempPerson, offered.Assignment.ID, request.RequesterPersonID); err != nil {
|
|
return err
|
|
}
|
|
if err := update(request.RequesterPersonID, wanted.Assignment.ID, request.TargetPersonID); err != nil {
|
|
return err
|
|
}
|
|
if err := update(request.TargetPersonID, offered.Assignment.ID, tempPerson); err != nil {
|
|
return err
|
|
}
|
|
result, err := tx.ExecContext(ctx, `
|
|
UPDATE swap_requests SET status=?, responded_at=?
|
|
WHERE tenant_id=? AND id=? AND status=?`,
|
|
SwapAccepted, now.Format(time.RFC3339), tenantID, request.ID, SwapPending)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if affected, _ := result.RowsAffected(); affected != 1 {
|
|
return errors.New("Diese Tauschanfrage wurde bereits beantwortet")
|
|
}
|
|
// Competing requests for the same assignments can no longer be
|
|
// fulfilled after this swap.
|
|
_, err = tx.ExecContext(ctx, `
|
|
UPDATE swap_requests SET status=?, responded_at=?
|
|
WHERE tenant_id=? AND status=? AND id<>?
|
|
AND (offered_assignment_id IN (?, ?) OR wanted_assignment_id IN (?, ?))`,
|
|
SwapCancelled, now.Format(time.RFC3339), tenantID, SwapPending, request.ID,
|
|
offered.Assignment.ID, wanted.Assignment.ID,
|
|
offered.Assignment.ID, wanted.Assignment.ID)
|
|
return err
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
request.Status = SwapAccepted
|
|
request.RespondedAt = now
|
|
return request, nil
|
|
}
|
|
|
|
// PendingSwapAssignmentIDs maps assignment IDs of open swap requests to the
|
|
// request ID so the plan and service views can mark them.
|
|
func (s *Store) PendingSwapAssignmentIDs(ctx context.Context, tenantID string) (map[string]string, error) {
|
|
rows, err := s.db.QueryContext(ctx, `
|
|
SELECT id, offered_assignment_id, wanted_assignment_id
|
|
FROM swap_requests WHERE tenant_id=? AND status=?`,
|
|
tenantID, SwapPending)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
out := map[string]string{}
|
|
for rows.Next() {
|
|
var id, offered, wanted string
|
|
if err := rows.Scan(&id, &offered, &wanted); err != nil {
|
|
return nil, err
|
|
}
|
|
out[offered] = id
|
|
out[wanted] = id
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// SwapStatusLabel returns the German label of a request state.
|
|
func SwapStatusLabel(status string) string {
|
|
switch status {
|
|
case SwapAccepted:
|
|
return "angenommen"
|
|
case SwapDeclined:
|
|
return "abgelehnt"
|
|
case SwapCancelled:
|
|
return "abgebrochen"
|
|
default:
|
|
return "offen"
|
|
}
|
|
}
|