-- 008_publish_safety.sql
--
-- Columns that 007 gained AFTER it had already been applied somewhere.
--
-- The migrator records a file by name and never looks at it again, so editing
-- an applied migration changes nothing on an existing database — it only
-- changes what a fresh install gets. Every column below is therefore added
-- here as well, guarded, so it exists on both.
--
-- Without this the first click on Publish, Analyze, Draft or Image on an
-- upgraded install raises "Unknown column 'dedupe_key'" and the whole feature
-- is dead.
--
-- Guarded ALTERs rather than "ADD COLUMN IF NOT EXISTS", which is MariaDB-only.
-- Each block is four statements sharing one connection, which is how the
-- session variables survive between them.

-- --------------------------------------------------------------- job.dedupe_key
-- Idempotency key for work that costs money on its first tick. UNIQUE, not just
-- indexed: pushUnique() is otherwise a check-then-insert, and two simultaneous
-- clicks both pass the check before either inserts. The index is what actually
-- prevents the second job; the SELECT only makes the common case quiet.
--
-- The key is nullable and MySQL does not consider two NULLs equal, so the
-- unique constraint applies only to jobs that opt into deduplication.
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'job' AND COLUMN_NAME = 'dedupe_key');
SET @ddl := IF(@c = 0, 'ALTER TABLE job ADD COLUMN dedupe_key VARCHAR(120) NULL AFTER type', 'DO 0');
PREPARE s FROM @ddl;
EXECUTE s;
DEALLOCATE PREPARE s;

-- Release the key on every job that has already finished, BEFORE the unique
-- index goes on.
--
-- The first cut of this feature never cleared dedupe_key, so an install that
-- ran it has finished rows still holding keys — and any key used twice
-- (`image:article:7` regenerated, `analyze:1:AE:en` run on two days) is a
-- duplicate the moment the index is created. That ALTER would fail, the
-- migrator would abandon the file, and the two column blocks further down
-- would never run — leaving Publish broken on exactly the install this
-- migration exists to repair.
--
-- A finished job's key means nothing: the claim it represents is over. Only
-- pending and reserved rows keep theirs.
UPDATE job SET dedupe_key = NULL WHERE dedupe_key IS NOT NULL AND status IN ('done', 'failed');

-- Anything still duplicated among live rows is a genuine double-queue from
-- before the index existed. Keep the oldest, release the rest — the work is
-- already queued once, which is the whole point.
UPDATE job j
  JOIN (
    SELECT dedupe_key, MIN(id) AS keep
      FROM job
     WHERE dedupe_key IS NOT NULL
     GROUP BY dedupe_key
    HAVING COUNT(*) > 1
  ) d ON d.dedupe_key = j.dedupe_key AND j.id <> d.keep
   SET j.dedupe_key = NULL;

SET @i := (SELECT COUNT(*) FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'job' AND INDEX_NAME = 'uq_job_dedupe');
SET @ddl := IF(@i = 0, 'ALTER TABLE job ADD UNIQUE KEY uq_job_dedupe (dedupe_key)', 'DO 0');
PREPARE s FROM @ddl;
EXECUTE s;
DEALLOCATE PREPARE s;

-- The non-unique index added by the first cut of 007 is redundant once the
-- unique key exists. Dropped so the table does not carry two indexes on the
-- same leading column.
SET @i := (SELECT COUNT(*) FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'job' AND INDEX_NAME = 'idx_job_dedupe');
SET @ddl := IF(@i > 0, 'ALTER TABLE job DROP INDEX idx_job_dedupe', 'DO 0');
PREPARE s FROM @ddl;
EXECUTE s;
DEALLOCATE PREPARE s;

-- ------------------------------------------------- publish_attempt.unresolved
-- Set on a row written BEFORE the POST goes out, and cleared when a clean
-- answer comes back. A row left set means the request was delivered and the
-- outcome is unknown: the post may be live. Nothing may publish that article
-- again automatically while such a row exists.
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'publish_attempt' AND COLUMN_NAME = 'unresolved');
SET @ddl := IF(@c = 0, 'ALTER TABLE publish_attempt ADD COLUMN unresolved TINYINT(1) NOT NULL DEFAULT 0 AFTER ok', 'DO 0');
PREPARE s FROM @ddl;
EXECUTE s;
DEALLOCATE PREPARE s;

-- ------------------------------------------------- publish_attempt.released
-- Set when an operator confirms the post is not on the live site. Kept apart
-- from ok/remote_id so releasing a block never destroys the record of what the
-- CMS actually replied.
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'publish_attempt' AND COLUMN_NAME = 'released');
SET @ddl := IF(@c = 0, 'ALTER TABLE publish_attempt ADD COLUMN released TINYINT(1) NOT NULL DEFAULT 0 AFTER unresolved', 'DO 0');
PREPARE s FROM @ddl;
EXECUTE s;
DEALLOCATE PREPARE s;

-- ------------------------------------------------------- article.image_error
-- Kept apart from error_message: an image that failed to generate says nothing
-- about the text, and writing "OpenAI rate limit" into the field that explains
-- a failed draft would say the wrong thing about the article.
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'article' AND COLUMN_NAME = 'image_error');
SET @ddl := IF(@c = 0, 'ALTER TABLE article ADD COLUMN image_error VARCHAR(1000) NULL AFTER error_message', 'DO 0');
PREPARE s FROM @ddl;
EXECUTE s;
DEALLOCATE PREPARE s;