-- @database: work
--
-- `media_to_describe` is a queue SHARED across environments (dev / preprod /
-- prod all point at the same `work` database). A queued row previously carried
-- only `media_id`, so a consuming worker could not tell which environment owns
-- the media — and therefore which Hydrogen API / storage / `hxa` to route it to.
--
-- Add an `environment` discriminator, stamped at enqueue time from the app's
-- `WORK_QUEUE_ENV` key (unique per environment). Each environment runs its own
-- out-of-band worker (`bin/describe_worker.py`) that claims ONLY its own rows
-- (`WHERE environment = :env`).
--
-- Existing rows (enqueued before this column existed) default to ''. Do NOT
-- blindly attribute them all to one environment: because `work` is shared, a
-- pre-existing row may belong to ANY instance. Attribute by provenance — a
-- media_id resolves to exactly the environment whose `hxa.media` holds it.
-- Rows left at '' are claimed by no env-specific worker (safe), pending manual
-- assignment once the owning environment's `WORK_QUEUE_ENV` is known.

ALTER TABLE `media_to_describe`
    ADD COLUMN `environment` VARCHAR(32) NOT NULL DEFAULT '' AFTER `media_id`,
    ADD INDEX `idx_env_created` (`environment`, `created_at`);

-- Backfill is provenance-based and therefore done out-of-band (per environment),
-- not with a single UPDATE here. For reference, the pattern used on the shared
-- `work` DB at rollout: rows whose media_id exists in an environment's
-- `hxa.media` were set to that environment's WORK_QUEUE_ENV; the rest were left
-- at '' until their owning instance is configured. Example (run against the env
-- that owns them, with its own hxa reachable):
--   UPDATE media_to_describe q
--     SET q.environment = '<WORK_QUEUE_ENV>'
--     WHERE q.environment = ''
--       AND EXISTS (SELECT 1 FROM <hxa>.media m WHERE m.id = q.media_id);
