119 lines
4.2 KiB
Go
119 lines
4.2 KiB
Go
package postgres
|
|
|
|
import (
|
|
"database/sql"
|
|
"time"
|
|
|
|
"gardomatic.kleiax.de/internal/storage"
|
|
"github.com/lib/pq"
|
|
)
|
|
|
|
// ApplicationSettingsModel stores global automation configuration.
|
|
// ApplicationSettingsModel persists the singleton application configuration
|
|
// and performs lifecycle cleanup transactionally.
|
|
type ApplicationSettingsModel struct{ DB *sql.DB }
|
|
|
|
// Get returns the singleton application settings.
|
|
func (m ApplicationSettingsModel) Get() (storage.ApplicationSettings, error) {
|
|
ctx, cancel := contextWithTimeout()
|
|
defer cancel()
|
|
var settings storage.ApplicationSettings
|
|
err := m.DB.QueryRowContext(ctx, `
|
|
SELECT lifecycle_status_enabled, lifecycle_removal_month, lifecycle_removal_day,
|
|
timezone, updated_at, version
|
|
FROM application_settings WHERE singleton = true`).Scan(
|
|
&settings.LifecycleStatusEnabled, &settings.LifecycleRemovalMonth,
|
|
&settings.LifecycleRemovalDay, &settings.Timezone, &settings.UpdatedAt, &settings.Version,
|
|
)
|
|
if err != nil {
|
|
return storage.ApplicationSettings{}, recordError(err)
|
|
}
|
|
return settings, nil
|
|
}
|
|
|
|
// Update replaces the singleton application settings using optimistic locking.
|
|
func (m ApplicationSettingsModel) Update(settings storage.ApplicationSettings) (storage.ApplicationSettings, error) {
|
|
ctx, cancel := contextWithTimeout()
|
|
defer cancel()
|
|
err := m.DB.QueryRowContext(ctx, `
|
|
UPDATE application_settings
|
|
SET lifecycle_status_enabled = $1, lifecycle_removal_month = $2,
|
|
lifecycle_removal_day = $3, timezone = $4,
|
|
updated_at = CURRENT_TIMESTAMP, version = version + 1
|
|
WHERE singleton = true AND version = $5
|
|
RETURNING updated_at, version`,
|
|
settings.LifecycleStatusEnabled, settings.LifecycleRemovalMonth,
|
|
settings.LifecycleRemovalDay, settings.Timezone, settings.Version,
|
|
).Scan(&settings.UpdatedAt, &settings.Version)
|
|
if err == sql.ErrNoRows {
|
|
return storage.ApplicationSettings{}, storage.ErrEditConflict
|
|
}
|
|
if err != nil {
|
|
return storage.ApplicationSettings{}, recordError(err)
|
|
}
|
|
return settings, nil
|
|
}
|
|
|
|
// RemoveExpiredPlants closes expired annual and biennial plants together with
|
|
// their active placements and records each status transition atomically.
|
|
func (m ApplicationSettingsModel) RemoveExpiredPlants(asOf time.Time, removalMonth, removalDay int) (int, error) {
|
|
ctx, cancel := contextWithTimeout()
|
|
defer cancel()
|
|
tx, err := m.DB.BeginTx(ctx, nil)
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
defer tx.Rollback()
|
|
|
|
rows, err := tx.QueryContext(ctx, `
|
|
UPDATE plants p
|
|
SET status = 'removed', removed_at = $1, updated_at = CURRENT_TIMESTAMP, version = p.version + 1
|
|
FROM species s
|
|
JOIN species_categories c ON c.id = s.category_id
|
|
WHERE p.species_id = s.id
|
|
AND p.status = 'alive'
|
|
AND p.acquired_at IS NOT NULL
|
|
AND c.lifecycle IN ('annual', 'biennial')
|
|
AND EXTRACT(YEAR FROM $1::date)::int >=
|
|
EXTRACT(YEAR FROM p.acquired_at)::int
|
|
+ CASE WHEN (EXTRACT(MONTH FROM p.acquired_at)::int, EXTRACT(DAY FROM p.acquired_at)::int) >= ($2, $3) THEN 1 ELSE 0 END
|
|
+ CASE c.lifecycle WHEN 'biennial' THEN 1 ELSE 0 END
|
|
RETURNING p.id`, asOf, removalMonth, removalDay)
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
ids := []int64{}
|
|
for rows.Next() {
|
|
var id int64
|
|
if err = rows.Scan(&id); err != nil {
|
|
rows.Close()
|
|
return 0, err
|
|
}
|
|
ids = append(ids, id)
|
|
}
|
|
if err = rows.Close(); err != nil {
|
|
return 0, err
|
|
}
|
|
if len(ids) == 0 {
|
|
if err = tx.Commit(); err != nil {
|
|
return 0, err
|
|
}
|
|
return 0, nil
|
|
}
|
|
if _, err = tx.ExecContext(ctx, `
|
|
INSERT INTO plant_status_history (plant_id, from_status, to_status, reason, effective_at)
|
|
SELECT unnest($2::bigint[]), 'alive', 'removed', 'lifecycle_reached', $1`, asOf, pq.Array(ids)); err != nil {
|
|
return 0, err
|
|
}
|
|
if _, err = tx.ExecContext(ctx, `UPDATE plant_locations SET removed_at = $1, version = version + 1 WHERE plant_id = ANY($2) AND removed_at IS NULL`, asOf, pq.Array(ids)); err != nil {
|
|
return 0, err
|
|
}
|
|
if _, err = tx.ExecContext(ctx, `UPDATE tasks SET active = false, updated_at = CURRENT_TIMESTAMP, version = version + 1 WHERE plant_id = ANY($1) AND template_id IS NOT NULL AND completed_at IS NULL AND active = true`, pq.Array(ids)); err != nil {
|
|
return 0, err
|
|
}
|
|
if err = tx.Commit(); err != nil {
|
|
return 0, err
|
|
}
|
|
return len(ids), nil
|
|
}
|