-- Tracks first time a listing was set to active (never cleared). Used to distinguish
-- "new submission awaiting review" from "was live, now draft".
SET NAMES utf8mb4;

ALTER TABLE listings
  ADD COLUMN first_activated_at TIMESTAMP NULL DEFAULT NULL AFTER status,
  ADD KEY idx_listing_first_activation (first_activated_at);

-- Existing live listings: treat as already published.
UPDATE listings
SET first_activated_at = COALESCE(created_at, updated_at)
WHERE status = 'active' AND first_activated_at IS NULL;