-- Repurposes the existing (previously unused, always 0) `media.status` INT
-- column as the processing-lifecycle the OWNER polls to track an upload:
--
--   0  pending     — stored + enqueued, waiting for the AI worker
--   1  processing  — the worker claimed it (POST /admin/media/{hex}/claim)
--   2  published   — describe verdict: clean, went live
--   3  rejected    — describe verdict: flagged (moderation refusal)
--   4  failed      — worker gave up (POST /admin/media/{hex}/fail), retryable
--
-- NO schema change is needed (the column already exists). This migration only
-- backfills the legacy rows from the verdict columns written so far:
--   - is_rejected = 1            → rejected (3)
--   - is_published = 1           → published (2)
--   - everything else            → pending (0), left as-is
--
-- `status` carries PIPELINE progress; `is_published` stays the VISIBILITY flag
-- (toggled independently by PUT /admin/media/{hex}/published). They agree on
-- the terminal states but are not redundant — processing / failed have no
-- is_published equivalent.

-- Rejected first: is_rejected = 1 already implies is_published = 0, so the two
-- UPDATEs never fight over the same row, but ordering keeps intent explicit.
UPDATE `media` SET `status` = 3 WHERE `is_rejected` = 1;
UPDATE `media` SET `status` = 2 WHERE `is_published` = 1 AND `is_rejected` = 0;
-- Remaining rows (not published, not rejected) stay at 0 = pending.

-- Speeds up the back-office "stuck in pending/processing/failed" listings and
-- the owner's "?status=" filter when it lands. Cheap, low-cardinality column.
CREATE INDEX `idx_media_status` ON `media` (`status`);
