Gardomatic/internal/storage/postgres/plant_locations.go

package postgres

import (
	"database/sql"

	"gardomatic.kleiax.de/internal/storage"
)

// PlantLocationModel stores plant-to-location assignments in PostgreSQL.
// PlantLocationModel implements storage.PlantLocationModelInterface for
// PostgreSQL and verifies both plant and location membership in the garden.
type PlantLocationModel struct{ DB *sql.DB }

const plantLocationQualifiedColumns = `pl.id, pl.plant_id, pl.location_id, pl.quantity, pl.planted_at,
    pl.removed_at, pl.notes, pl.created_at, pl.version`

func scanPlantLocation(s scanner) (storage.PlantLocation, error) {
	var plantLocation storage.PlantLocation
	err := s.Scan(
		&plantLocation.ID, &plantLocation.PlantID, &plantLocation.LocationID,
		&plantLocation.Quantity, &plantLocation.PlantedAt, &plantLocation.RemovedAt,
		&plantLocation.Notes, &plantLocation.CreatedAt, &plantLocation.Version,
	)
	return plantLocation, err
}

// Insert assigns a plant to a location in the same garden.
func (m PlantLocationModel) Insert(gardenID int, plantLocation storage.PlantLocation) (storage.PlantLocation, error) {
	ctx, cancel := contextWithTimeout()
	defer cancel()
	err := m.DB.QueryRowContext(ctx, `
        INSERT INTO plant_locations
            (plant_id, location_id, quantity, planted_at, removed_at, notes)
        SELECT $2, $3, $4, $5, $6, $7
        FROM plants p, locations l
        WHERE p.id = $2 AND p.garden_id = $1 AND l.id = $3 AND l.garden_id = $1
        RETURNING id, created_at, version`,
		gardenID, plantLocation.PlantID, plantLocation.LocationID, plantLocation.Quantity,
		plantLocation.PlantedAt, plantLocation.RemovedAt, plantLocation.Notes,
	).Scan(&plantLocation.ID, &plantLocation.CreatedAt, &plantLocation.Version)
	if err != nil {
		return storage.PlantLocation{}, recordError(err)
	}
	return plantLocation, nil
}

// Get returns a plant assignment within its garden.
func (m PlantLocationModel) Get(gardenID, id int) (storage.PlantLocation, error) {
	ctx, cancel := contextWithTimeout()
	defer cancel()
	plantLocation, err := scanPlantLocation(m.DB.QueryRowContext(ctx,
		`SELECT `+plantLocationQualifiedColumns+` FROM plant_locations pl
        JOIN plants p ON p.id = pl.plant_id WHERE p.garden_id = $1 AND pl.id = $2`, gardenID, id))
	if err != nil {
		return storage.PlantLocation{}, recordError(err)
	}
	return plantLocation, nil
}

func (m PlantLocationModel) getAll(query string, gardenID, id int) ([]storage.PlantLocation, error) {
	ctx, cancel := contextWithTimeout()
	defer cancel()
	rows, err := m.DB.QueryContext(ctx, query, gardenID, id)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	plantLocations := []storage.PlantLocation{}
	for rows.Next() {
		plantLocation, err := scanPlantLocation(rows)
		if err != nil {
			return nil, err
		}
		plantLocations = append(plantLocations, plantLocation)
	}
	return plantLocations, rows.Err()
}

// GetAllForPlant lists a plant's current and historical placements.
func (m PlantLocationModel) GetAllForPlant(gardenID, plantID int) ([]storage.PlantLocation, error) {
	return m.getAll(`SELECT `+plantLocationQualifiedColumns+`
        FROM plant_locations pl JOIN plants p ON p.id = pl.plant_id
        WHERE p.garden_id = $1 AND pl.plant_id = $2 ORDER BY pl.created_at, pl.id`, gardenID, plantID)
}

// GetAllForLocation lists current and historical plant placements at a location.
func (m PlantLocationModel) GetAllForLocation(gardenID, locationID int) ([]storage.PlantLocation, error) {
	return m.getAll(`SELECT `+plantLocationQualifiedColumns+`
        FROM plant_locations pl JOIN locations l ON l.id = pl.location_id
        WHERE l.garden_id = $1 AND pl.location_id = $2 ORDER BY pl.created_at, pl.id`, gardenID, locationID)
}

// Update changes a plant assignment using optimistic locking.
func (m PlantLocationModel) Update(gardenID int, plantLocation storage.PlantLocation) (storage.PlantLocation, error) {
	ctx, cancel := contextWithTimeout()
	defer cancel()
	result, err := m.DB.ExecContext(ctx, `
        UPDATE plant_locations SET plant_id = $1, location_id = $2, quantity = $3,
            planted_at = $4, removed_at = $5, notes = $6, version = version + 1
		WHERE id = $7 AND version = $8
		  AND EXISTS (SELECT 1 FROM plants p WHERE p.id = plant_locations.plant_id AND p.garden_id = $9)
		  AND EXISTS (SELECT 1 FROM plants p WHERE p.id = $1 AND p.garden_id = $9)
		  AND EXISTS (SELECT 1 FROM locations l WHERE l.id = $2 AND l.garden_id = $9)`,
		plantLocation.PlantID, plantLocation.LocationID, plantLocation.Quantity,
		plantLocation.PlantedAt, plantLocation.RemovedAt, plantLocation.Notes,
		plantLocation.ID, plantLocation.Version, gardenID)
	if err != nil {
		return storage.PlantLocation{}, err
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return storage.PlantLocation{}, err
	}
	if rowsAffected == 0 {
		return storage.PlantLocation{}, storage.ErrEditConflict
	}
	plantLocation.Version++
	return plantLocation, nil
}

// Delete removes a plant assignment within its garden.
func (m PlantLocationModel) Delete(gardenID, id int) error {
	ctx, cancel := contextWithTimeout()
	defer cancel()
	result, err := m.DB.ExecContext(ctx, `DELETE FROM plant_locations pl USING plants p
		WHERE pl.id = $2 AND p.id = pl.plant_id AND p.garden_id = $1`, gardenID, id)
	if err != nil {
		return err
	}
	rows, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if rows == 0 {
		return storage.ErrRecordNotFound
	}
	return nil
}