lockers.sql

-- Locker CRUD (FR-2, FR-3). Ordering is by label everywhere: labels are what
-- staff and customers read, and UUIDv4 ids carry no meaningful order.
--
-- Every read joins locker_types, because a locker without its interior cannot
-- answer the only question allocation asks. The join is an inner one: migration
-- 000002 backfilled every existing row, and the writes below set the type on
-- every new one, so a locker with no type is a bug rather than a case to carry.
--
-- updated_at uses clock_timestamp(), not now(): now() is the transaction
-- timestamp, so a locker touched twice in one transaction (store: claim, then
-- occupy) would carry the same stamp for both changes.
--
-- The writes take a locker_type_id. Migration 000003 dropped the size column
-- they used to derive it from, so the catalog is now the only place a locker's
-- interior is stated.

-- name: CreateLocker :one
INSERT INTO lockers (station_id, label, locker_type_id)
VALUES ($1, $2, $3)
RETURNING *;

-- name: GetLocker :one
SELECT sqlc.embed(l), sqlc.embed(t)
  FROM lockers l
  JOIN locker_types t ON t.id = l.locker_type_id
 WHERE l.id = $1;

-- name: GetLockerByLabel :one
SELECT sqlc.embed(l), sqlc.embed(t)
  FROM lockers l
  JOIN locker_types t ON t.id = l.locker_type_id
 WHERE l.station_id = $1 AND l.label = $2;

-- name: ListLockersByStation :many
SELECT sqlc.embed(l), sqlc.embed(t)
  FROM lockers l
  JOIN locker_types t ON t.id = l.locker_type_id
 WHERE l.station_id = $1
 ORDER BY l.label;

-- name: UpdateLocker :one
UPDATE lockers
   SET label = sqlc.arg(label),
       locker_type_id = sqlc.arg(locker_type_id),
       updated_at = clock_timestamp()
 WHERE lockers.id = sqlc.arg(id)
RETURNING *;

-- name: SetLockerStatus :one
UPDATE lockers
   SET status = $2, updated_at = clock_timestamp()
 WHERE id = $1
RETURNING *;

-- name: CountLockersByStation :one
SELECT count(*) FROM lockers
WHERE station_id = $1;

-- name: DeleteLocker :execrows
DELETE FROM lockers
WHERE id = $1;

-- CreateLockerIfAbsent is the seed's idempotency lever: the (station_id, label)
-- unique constraint makes a re-run a no-op instead of a duplicate grid.
--
-- It takes a type *name* rather than an id, so the seed config can stay a
-- declarative list of labels and cell names. A name with no row yields NULL and
-- the NOT NULL constraint refuses it — a typo in the grid fails the boot rather
-- than seeding a typeless locker.
-- name: CreateLockerIfAbsent :execrows
INSERT INTO lockers (station_id, label, locker_type_id)
SELECT sqlc.arg(station_id), sqlc.arg(label), t.id
  FROM locker_types t
 WHERE t.name = sqlc.arg(type_name)
ON CONFLICT (station_id, label) DO NOTHING;

-- ClaimSmallestFittingLocker is the Level 4 mechanism (TDD §7). Inside the store
-- transaction it claims one available locker at the station that can hold the
-- package:
--
--   FOR UPDATE OF l  locks the claimed locker row for this transaction
--   SKIP LOCKED      makes concurrent stores skip rows already claimed instead
--                    of queueing behind them, so N simultaneous requests take N
--                    different lockers with no lock waits and no deadlocks
--
-- **OF l is load-bearing, not style.** A plain FOR UPDATE on this join would
-- lock the matched locker_types row as well, and every locker of a type shares
-- one type row — so under SKIP LOCKED a second transaction would skip every
-- locker of that type because one of them is mid-claim. Fifty concurrent stores
-- against ten same-type lockers would then return far fewer than ten successes:
-- a correctness bug wearing the costume of a performance detail.
-- TestClaimDoesNotLockTheLockerTypeRow is what holds it.
--
-- The three package arguments arrive sorted descending and clearance-added,
-- computed by domain.Dimensions.RequiredInteriorMM. Sorting lives in one place
-- and this query only compares — which is also why locker_types keeps its own
-- sides pre-sorted rather than sorting per row inside the locking SELECT.
--
-- Ordering by volume then label is the same rule domain.SelectLocker states in
-- Go; it has to be expressed here as well because the choice must happen inside
-- the locking SELECT. TestClaimAgreesWithTheDomainRule keeps the two honest.
-- name: ClaimSmallestFittingLocker :one
SELECT sqlc.embed(l), sqlc.embed(t)
  FROM lockers l
  JOIN locker_types t ON t.id = l.locker_type_id
 WHERE l.station_id = $1
   AND l.status = 'available'
   AND t.dim_max_mm >= sqlc.arg(pkg_max)::int
   AND t.dim_mid_mm >= sqlc.arg(pkg_mid)::int
   AND t.dim_min_mm >= sqlc.arg(pkg_min)::int
 ORDER BY t.volume_mm3, l.label
   FOR UPDATE OF l SKIP LOCKED
 LIMIT 1;