Files

650 lines
22 KiB
Go

package store
import (
"context"
"database/sql"
"encoding/json"
"errors"
"strings"
"time"
"lektorenplan/domain"
)
func encodeStrings(values []string) string {
if values == nil {
values = []string{}
}
data, err := json.Marshal(values)
if err != nil {
return "[]"
}
return string(data)
}
func decodeStrings(value string) []string {
var out []string
if err := json.Unmarshal([]byte(value), &out); err != nil {
return nil
}
return out
}
func decodeInts(value string) []int {
var out []int
if err := json.Unmarshal([]byte(value), &out); err != nil {
return nil
}
return out
}
func encodeInts(values []int) string {
if values == nil {
values = []int{}
}
data, err := json.Marshal(values)
if err != nil {
return "[]"
}
return string(data)
}
func encodeWeekdayPeriods(values []domain.WeekdayPeriodPreference) string {
if values == nil {
values = []domain.WeekdayPeriodPreference{}
}
data, err := json.Marshal(values)
if err != nil {
return "[]"
}
return string(data)
}
func decodeWeekdayPeriods(value string) []domain.WeekdayPeriodPreference {
var out []domain.WeekdayPeriodPreference
if err := json.Unmarshal([]byte(value), &out); err != nil {
return nil
}
return out
}
// Churches lists a tenant's churches.
func (s *Store) Churches(ctx context.Context, tenantID string) ([]domain.Church, error) {
rows, err := s.db.QueryContext(ctx, `
SELECT id, name, short_name, symbol, color_hex, address, sort_index, notes
FROM churches WHERE tenant_id=? ORDER BY sort_index, name COLLATE NOCASE`, tenantID)
if err != nil {
return nil, err
}
defer rows.Close()
var out []domain.Church
for rows.Next() {
var church domain.Church
var symbol string
if err := rows.Scan(&church.ID, &church.Name, &church.ShortName, &symbol,
&church.ColorHex, &church.Address, &church.SortIndex, &church.Notes); err != nil {
return nil, err
}
church.Symbol = domain.ChurchSymbol(symbol)
out = append(out, church)
}
return out, rows.Err()
}
// Church loads one church inside a tenant.
func (s *Store) Church(ctx context.Context, tenantID, id string) (*domain.Church, error) {
var church domain.Church
var symbol string
err := s.db.QueryRowContext(ctx, `
SELECT id, name, short_name, symbol, color_hex, address, sort_index, notes
FROM churches WHERE tenant_id=? AND id=?`, tenantID, id).
Scan(&church.ID, &church.Name, &church.ShortName, &symbol,
&church.ColorHex, &church.Address, &church.SortIndex, &church.Notes)
if errors.Is(err, sql.ErrNoRows) {
return nil, ErrNotFound
}
if err != nil {
return nil, err
}
church.Symbol = domain.ChurchSymbol(symbol)
return &church, nil
}
// CountChurches returns the number of churches in a tenant.
func (s *Store) CountChurches(ctx context.Context, tenantID string) (int, error) {
var count int
err := s.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM churches WHERE tenant_id=?", tenantID).Scan(&count)
return count, err
}
// UpsertChurch creates or updates a church.
func (s *Store) UpsertChurch(ctx context.Context, tenantID string, church domain.Church) error {
if strings.TrimSpace(church.ID) == "" {
church.ID = domain.NewID()
}
if strings.TrimSpace(church.Name) == "" {
return errors.New("Der Kirchenname ist erforderlich")
}
if church.Symbol == "" {
church.Symbol = domain.SymbolChapel
}
_, err := s.db.ExecContext(ctx, `
INSERT INTO churches(tenant_id, id, name, short_name, symbol, color_hex, address, sort_index, notes)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(tenant_id, id) DO UPDATE SET
name=excluded.name, short_name=excluded.short_name, symbol=excluded.symbol,
color_hex=excluded.color_hex, address=excluded.address,
sort_index=excluded.sort_index, notes=excluded.notes`,
tenantID, church.ID, church.Name, church.ShortName, string(church.Symbol),
church.ColorHex, church.Address, church.SortIndex, church.Notes)
return err
}
// DeleteChurch removes a church and all planning data that depends on it.
func (s *Store) DeleteChurch(ctx context.Context, tenantID, id string) error {
return s.transaction(ctx, func(tx *sql.Tx) error {
if _, err := tx.ExecContext(ctx, `
DELETE FROM assignments WHERE tenant_id=? AND service_id IN (
SELECT id FROM services WHERE tenant_id=? AND church_id=?)`,
tenantID, tenantID, id); err != nil {
return err
}
if _, err := tx.ExecContext(ctx,
"DELETE FROM services WHERE tenant_id=? AND church_id=?", tenantID, id); err != nil {
return err
}
if _, err := tx.ExecContext(ctx, `
DELETE FROM service_exceptions WHERE tenant_id=? AND template_id IN (
SELECT id FROM service_templates WHERE tenant_id=? AND church_id=?)`,
tenantID, tenantID, id); err != nil {
return err
}
if _, err := tx.ExecContext(ctx,
"DELETE FROM service_templates WHERE tenant_id=? AND church_id=?", tenantID, id); err != nil {
return err
}
_, err := tx.ExecContext(ctx, "DELETE FROM churches WHERE tenant_id=? AND id=?", tenantID, id)
return err
})
}
// People lists a tenant's volunteers.
func (s *Store) People(ctx context.Context, tenantID string) ([]domain.Person, error) {
rows, err := s.db.QueryContext(ctx, `
SELECT id, short_code, first_name, last_name, roles, email, preferred_church_ids,
preferred_weekdays, preferred_weekday_periods, preferred_service_titles,
max_per_month, allow_beyond_preferences, active, notes
FROM people WHERE tenant_id=? ORDER BY last_name COLLATE NOCASE, first_name COLLATE NOCASE`, tenantID)
if err != nil {
return nil, err
}
var out []domain.Person
for rows.Next() {
person, err := scanPerson(rows)
if err != nil {
rows.Close()
return nil, err
}
out = append(out, *person)
}
if err := rows.Err(); err != nil {
rows.Close()
return nil, err
}
rows.Close()
// The connection pool has a single connection, so the rows must be closed
// before the encrypted contacts are loaded.
for index := range out {
s.hydratePersonContacts(ctx, tenantID, &out[index])
}
return out, nil
}
// Person loads one volunteer.
func (s *Store) Person(ctx context.Context, tenantID, id string) (*domain.Person, error) {
row := s.db.QueryRowContext(ctx, `
SELECT id, short_code, first_name, last_name, roles, email, preferred_church_ids,
preferred_weekdays, preferred_weekday_periods, preferred_service_titles,
max_per_month, allow_beyond_preferences, active, notes
FROM people WHERE tenant_id=? AND id=?`, tenantID, id)
person, err := scanPerson(row)
if errors.Is(err, sql.ErrNoRows) {
return nil, ErrNotFound
}
if err != nil {
return nil, err
}
s.hydratePersonContacts(ctx, tenantID, person)
return person, nil
}
// hydratePersonContacts fills the e-mail address from the encrypted store.
// Values that exist but cannot be decrypted stay empty and are marked as
// locked so the UI can explain the situation. Before a tenant has been
// migrated, plaintext contacts are only exposed to key-holding editors.
func (s *Store) hydratePersonContacts(ctx context.Context, tenantID string, person *domain.Person) {
if !s.SecretIsInitialized(ctx, tenantID) {
if KeyringFrom(ctx) == nil && strings.TrimSpace(person.Email) != "" {
person.Email = ""
person.EmailLocked = true
}
return
}
email, emailLocked := s.PersonContactSecrets(ctx, tenantID, person.ID)
if person.Email == "" {
person.Email = email
person.EmailLocked = emailLocked
}
}
func scanPerson(row rowScanner) (*domain.Person, error) {
var person domain.Person
var roles, churches, weekdays, periods, titles string
var allowBeyond, active int
if err := row.Scan(&person.ID, &person.ShortCode, &person.FirstName, &person.LastName, &roles,
&person.Email, &churches, &weekdays, &periods, &titles,
&person.MaxPerMonth, &allowBeyond, &active, &person.Notes); err != nil {
return nil, err
}
for _, raw := range decodeStrings(roles) {
role := domain.Role(raw)
if role.IsValid() {
person.Roles = append(person.Roles, role)
}
}
person.PreferredChurchIDs = decodeStrings(churches)
person.PreferredWeekdays = decodeInts(weekdays)
person.PreferredWeekdayPeriods = decodeWeekdayPeriods(periods)
person.PreferredServiceTitles = decodeStrings(titles)
person.AllowBeyondPreferences = allowBeyond != 0
person.Active = active != 0
return &person, nil
}
// UpsertPerson creates or updates a volunteer.
func (s *Store) UpsertPerson(ctx context.Context, tenantID string, person domain.Person) error {
if strings.TrimSpace(person.ID) == "" {
person.ID = domain.NewID()
}
if strings.TrimSpace(person.FirstName) == "" && strings.TrimSpace(person.LastName) == "" {
return errors.New("Der Name ist erforderlich")
}
roleStrings := make([]string, 0, len(person.Roles))
for _, role := range person.Roles {
if role.IsValid() {
roleStrings = append(roleStrings, string(role))
}
}
if person.MaxPerMonth < 0 {
person.MaxPerMonth = 0
}
initialized := s.SecretIsInitialized(ctx, tenantID)
keyring := KeyringFrom(ctx)
// Plaintext contact writes are only a migration fallback for callers
// without key material. A reader who is logged in never stores an address
// in the legacy column.
manageContacts := !initialized && keyring == nil
// A locked field that was left empty must keep its existing value. This
// also lets readers without their own data key update their profile and
// prevents an administrator who cannot see plaintext contacts from
// clearing them before the tenant has been migrated.
preserveContact := person.EmailLocked && strings.TrimSpace(person.Email) == ""
updateLegacyContact := manageContacts && !preserveContact
var dek []byte
if initialized && keyring != nil && !preserveContact {
if err := s.EnsurePersonScope(ctx, keyring, tenantID, person.ID); err != nil {
return err
}
var err error
dek, err = s.scopeDEK(ctx, keyring, KeyScope{Type: ScopePerson, TenantID: tenantID, ID: person.ID})
if err != nil {
return err
}
}
return s.transaction(ctx, func(tx *sql.Tx) error {
email := person.Email
if initialized || !manageContacts {
// Contact details only live in the encrypted secrets table.
email = ""
}
_, err := tx.ExecContext(ctx, `
INSERT INTO people(tenant_id, id, short_code, first_name, last_name, roles, email,
preferred_church_ids, preferred_weekdays, preferred_weekday_periods,
preferred_service_titles, max_per_month, allow_beyond_preferences, active, notes)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(tenant_id, id) DO UPDATE SET
short_code=excluded.short_code,
first_name=excluded.first_name, last_name=excluded.last_name, roles=excluded.roles,
email=CASE WHEN ? THEN excluded.email ELSE people.email END,
preferred_church_ids=excluded.preferred_church_ids,
preferred_weekdays=excluded.preferred_weekdays,
preferred_weekday_periods=excluded.preferred_weekday_periods,
preferred_service_titles=excluded.preferred_service_titles,
max_per_month=excluded.max_per_month,
allow_beyond_preferences=excluded.allow_beyond_preferences,
active=excluded.active, notes=excluded.notes`,
tenantID, person.ID, strings.TrimSpace(person.ShortCode),
person.FirstName, person.LastName, encodeStrings(roleStrings),
email, encodeStrings(person.PreferredChurchIDs),
encodeInts(person.PreferredWeekdays),
encodeWeekdayPeriods(person.PreferredWeekdayPeriods),
encodeStrings(person.PreferredServiceTitles), person.MaxPerMonth,
boolInt(person.AllowBeyondPreferences),
boolInt(person.Active), person.Notes, boolInt(updateLegacyContact))
if err != nil {
return err
}
if !initialized || keyring == nil {
return nil
}
setSecret := func(key, value string) error {
if strings.TrimSpace(value) == "" {
_, err := tx.ExecContext(ctx, `
DELETE FROM secrets
WHERE tenant_id=? AND scope_type=? AND scope_id=? AND secret_key=?`,
tenantID, ScopePerson, person.ID, key)
return err
}
sealed, err := sealAESGCM(dek, []byte(value),
keyAAD(ScopePerson, tenantID, person.ID, key))
if err != nil {
return err
}
_, err = tx.ExecContext(ctx, `
INSERT INTO secrets(tenant_id, scope_type, scope_id, secret_key, ciphertext, updated_at)
VALUES(?, ?, ?, ?, ?, ?)
ON CONFLICT(tenant_id, scope_type, scope_id, secret_key) DO UPDATE SET
ciphertext=excluded.ciphertext, updated_at=excluded.updated_at`,
tenantID, ScopePerson, person.ID, key, sealed,
time.Now().UTC().Format(time.RFC3339))
return err
}
// A locked field that was left empty must keep its existing secret.
if !(person.EmailLocked && strings.TrimSpace(person.Email) == "") {
if err := setSecret("email", person.Email); err != nil {
return err
}
}
return nil
})
}
// DeletePerson removes a volunteer with assignments and absences.
func (s *Store) DeletePerson(ctx context.Context, tenantID, id string) error {
return s.transaction(ctx, func(tx *sql.Tx) error {
if _, err := tx.ExecContext(ctx,
"DELETE FROM assignments WHERE tenant_id=? AND person_id=?", tenantID, id); err != nil {
return err
}
if _, err := tx.ExecContext(ctx,
"DELETE FROM absences WHERE tenant_id=? AND person_id=?", tenantID, id); err != nil {
return err
}
if _, err := tx.ExecContext(ctx,
"DELETE FROM secrets WHERE tenant_id=? AND scope_type=? AND scope_id=?",
tenantID, ScopePerson, id); err != nil {
return err
}
if _, err := tx.ExecContext(ctx,
"DELETE FROM key_wrappers WHERE scope_type=? AND tenant_id=? AND scope_id=?",
ScopePerson, tenantID, id); err != nil {
return err
}
if _, err := tx.ExecContext(ctx,
"DELETE FROM contact_shares WHERE tenant_id=? AND (owner_person_id=? OR viewer_user_id IN ("+
"SELECT id FROM users WHERE linked_tenant_id=? AND linked_person_id=?))",
tenantID, id, tenantID, id); err != nil {
return err
}
if _, err := tx.ExecContext(ctx,
"DELETE FROM contact_reviews WHERE tenant_id=? AND owner_person_id=?", tenantID, id); err != nil {
return err
}
if _, err := tx.ExecContext(ctx,
"DELETE FROM swap_requests WHERE tenant_id=? AND (requester_person_id=? OR target_person_id=?)",
tenantID, id, id); err != nil {
return err
}
_, err := tx.ExecContext(ctx, "DELETE FROM people WHERE tenant_id=? AND id=?", tenantID, id)
return err
})
}
// Absences lists all absences in a tenant.
func (s *Store) Absences(ctx context.Context, tenantID string) ([]domain.Absence, error) {
rows, err := s.db.QueryContext(ctx, `
SELECT id, person_id, start_date, end_date, reason FROM absences
WHERE tenant_id=? ORDER BY start_date`, tenantID)
if err != nil {
return nil, err
}
defer rows.Close()
var out []domain.Absence
for rows.Next() {
var absence domain.Absence
var start, end string
if err := rows.Scan(&absence.ID, &absence.PersonID, &start, &end, &absence.Reason); err != nil {
return nil, err
}
absence.Start = domain.MustDate(start)
absence.End = domain.MustDate(end)
out = append(out, absence)
}
return out, rows.Err()
}
// AbsencesForPerson lists one person's absences.
func (s *Store) AbsencesForPerson(ctx context.Context, tenantID, personID string) ([]domain.Absence, error) {
all, err := s.Absences(ctx, tenantID)
if err != nil {
return nil, err
}
var out []domain.Absence
for _, absence := range all {
if absence.PersonID == personID {
out = append(out, absence)
}
}
return out, nil
}
// UpsertAbsence creates or updates an absence.
func (s *Store) UpsertAbsence(ctx context.Context, tenantID string, absence domain.Absence) error {
if strings.TrimSpace(absence.ID) == "" {
absence.ID = domain.NewID()
}
if absence.PersonID == "" {
return errors.New("Es muss eine Person gewählt werden")
}
_, err := s.db.ExecContext(ctx, `
INSERT INTO absences(tenant_id, id, person_id, start_date, end_date, reason)
VALUES(?, ?, ?, ?, ?, ?)
ON CONFLICT(tenant_id, id) DO UPDATE SET
person_id=excluded.person_id, start_date=excluded.start_date,
end_date=excluded.end_date, reason=excluded.reason`,
tenantID, absence.ID, absence.PersonID,
domain.Key(absence.Start), domain.Key(absence.End), absence.Reason)
return err
}
// DeleteAbsence removes one absence.
func (s *Store) DeleteAbsence(ctx context.Context, tenantID, id string) error {
_, err := s.db.ExecContext(ctx, "DELETE FROM absences WHERE tenant_id=? AND id=?", tenantID, id)
return err
}
// Templates lists service templates.
func (s *Store) Templates(ctx context.Context, tenantID string) ([]domain.ServiceTemplate, error) {
rows, err := s.db.QueryContext(ctx, `
SELECT id, church_id, weekday, time_minutes, title, lectors_needed, helpers_needed,
valid_from, valid_to, active, notes
FROM service_templates WHERE tenant_id=? ORDER BY weekday, time_minutes`, tenantID)
if err != nil {
return nil, err
}
defer rows.Close()
var out []domain.ServiceTemplate
for rows.Next() {
var item domain.ServiceTemplate
var validFrom string
var validTo sql.NullString
var active int
if err := rows.Scan(&item.ID, &item.ChurchID, &item.Weekday, &item.TimeMinutes,
&item.Title, &item.LectorsNeeded, &item.HelpersNeeded, &validFrom,
&validTo, &active, &item.Notes); err != nil {
return nil, err
}
item.ValidFrom = domain.MustDate(validFrom)
if validTo.Valid && validTo.String != "" {
date := domain.MustDate(validTo.String)
item.ValidTo = &date
}
item.Active = active != 0
out = append(out, item)
}
return out, rows.Err()
}
// Template loads one template.
func (s *Store) Template(ctx context.Context, tenantID, id string) (*domain.ServiceTemplate, error) {
all, err := s.Templates(ctx, tenantID)
if err != nil {
return nil, err
}
for i := range all {
if all[i].ID == id {
return &all[i], nil
}
}
return nil, ErrNotFound
}
// UpsertTemplate creates or updates a service template.
func (s *Store) UpsertTemplate(ctx context.Context, tenantID string, template domain.ServiceTemplate) error {
if strings.TrimSpace(template.ID) == "" {
template.ID = domain.NewID()
}
if template.ChurchID == "" || strings.TrimSpace(template.Title) == "" {
return errors.New("Kirche und Titel sind erforderlich")
}
var validTo any
if template.ValidTo != nil {
validTo = domain.Key(*template.ValidTo)
}
_, err := s.db.ExecContext(ctx, `
INSERT INTO service_templates(tenant_id, id, church_id, weekday, time_minutes, title,
lectors_needed, helpers_needed, valid_from, valid_to, active, notes)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(tenant_id, id) DO UPDATE SET
church_id=excluded.church_id, weekday=excluded.weekday,
time_minutes=excluded.time_minutes, title=excluded.title,
lectors_needed=excluded.lectors_needed, helpers_needed=excluded.helpers_needed,
valid_from=excluded.valid_from, valid_to=excluded.valid_to,
active=excluded.active, notes=excluded.notes`,
tenantID, template.ID, template.ChurchID, template.Weekday, template.TimeMinutes,
template.Title, template.LectorsNeeded, template.HelpersNeeded,
domain.Key(template.ValidFrom), validTo, boolInt(template.Active), template.Notes)
return err
}
// DeleteTemplate removes a template, its exceptions, and detaches services.
func (s *Store) DeleteTemplate(ctx context.Context, tenantID, id string) error {
return s.transaction(ctx, func(tx *sql.Tx) error {
if _, err := tx.ExecContext(ctx,
"UPDATE services SET template_id=NULL WHERE tenant_id=? AND template_id=?", tenantID, id); err != nil {
return err
}
if _, err := tx.ExecContext(ctx,
"DELETE FROM service_exceptions WHERE tenant_id=? AND template_id=?", tenantID, id); err != nil {
return err
}
_, err := tx.ExecContext(ctx,
"DELETE FROM service_templates WHERE tenant_id=? AND id=?", tenantID, id)
return err
})
}
// Exceptions lists template exceptions.
func (s *Store) Exceptions(ctx context.Context, tenantID string) ([]domain.ServiceException, error) {
rows, err := s.db.QueryContext(ctx, `
SELECT id, template_id, date, reason FROM service_exceptions
WHERE tenant_id=? ORDER BY date`, tenantID)
if err != nil {
return nil, err
}
defer rows.Close()
var out []domain.ServiceException
for rows.Next() {
var item domain.ServiceException
var date string
if err := rows.Scan(&item.ID, &item.TemplateID, &date, &item.Reason); err != nil {
return nil, err
}
item.Date = domain.MustDate(date)
out = append(out, item)
}
return out, rows.Err()
}
// UpsertException creates or updates an exception. The unique key is
// (tenant, template, date), matching the macOS app.
func (s *Store) UpsertException(ctx context.Context, tenantID string, item domain.ServiceException) error {
if strings.TrimSpace(item.ID) == "" {
item.ID = domain.NewID()
}
if item.TemplateID == "" {
return errors.New("Es muss eine Vorlage gewählt werden")
}
return s.transaction(ctx, func(tx *sql.Tx) error {
// An explicit ID means the caller is editing an existing exception in
// place. Remove a conflicting row first so moving an exception to a new
// template or date cannot violate the unique key.
if strings.TrimSpace(item.ID) != "" {
var count int
if err := tx.QueryRowContext(ctx,
"SELECT COUNT(*) FROM service_exceptions WHERE tenant_id=? AND id=?",
tenantID, item.ID).Scan(&count); err != nil {
return err
}
if count > 0 {
if _, err := tx.ExecContext(ctx, `
DELETE FROM service_exceptions
WHERE tenant_id=? AND template_id=? AND date=? AND id<>?`,
tenantID, item.TemplateID, domain.Key(item.Date), item.ID); err != nil {
return err
}
_, err := tx.ExecContext(ctx, `
UPDATE service_exceptions SET template_id=?, date=?, reason=?
WHERE tenant_id=? AND id=?`,
item.TemplateID, domain.Key(item.Date), item.Reason, tenantID, item.ID)
return err
}
}
var existingID string
err := tx.QueryRowContext(ctx, `
SELECT id FROM service_exceptions WHERE tenant_id=? AND template_id=? AND date=?`,
tenantID, item.TemplateID, domain.Key(item.Date)).Scan(&existingID)
if err != nil && !errors.Is(err, sql.ErrNoRows) {
return err
}
if existingID != "" {
_, err = tx.ExecContext(ctx,
"UPDATE service_exceptions SET reason=? WHERE tenant_id=? AND id=?",
item.Reason, tenantID, existingID)
return err
}
_, err = tx.ExecContext(ctx, `
INSERT INTO service_exceptions(tenant_id, id, template_id, date, reason)
VALUES(?, ?, ?, ?, ?)`,
tenantID, item.ID, item.TemplateID, domain.Key(item.Date), item.Reason)
return err
})
}
// DeleteException removes one exception.
func (s *Store) DeleteException(ctx context.Context, tenantID, id string) error {
_, err := s.db.ExecContext(ctx, "DELETE FROM service_exceptions WHERE tenant_id=? AND id=?", tenantID, id)
return err
}