-- Add the new column as NULLable so the backfill can populate it.
ALTER TABLE "issue_statuses"
ADD COLUMN "code" VARCHAR(50);

-- Backfill the five known seed rows by their human-readable name.
UPDATE "issue_statuses" SET "code" = 'DRAFT'           WHERE "name" = 'Draft';
UPDATE "issue_statuses" SET "code" = 'NEW_SUBMISSION'  WHERE "name" = 'New Submission';
UPDATE "issue_statuses" SET "code" = 'IN_PROGRESS'    WHERE "name" = 'In Progress';
UPDATE "issue_statuses" SET "code" = 'SOLVED'          WHERE "name" = 'Solved';
UPDATE "issue_statuses" SET "code" = 'NOT_ADDRESSED'  WHERE "name" = 'Not Addressed';

-- Fallback for any custom rows the explicit list didn't cover: uppercase,
-- replace any non-alphanumeric run with a single underscore, trim leading/
-- trailing underscores.
UPDATE "issue_statuses"
SET "code" = TRIM(BOTH '_' FROM REGEXP_REPLACE(UPPER(TRIM("name")), '[^A-Z0-9]+', '_', 'g'))
WHERE "code" IS NULL;

-- Lock the column down now that every row has a value.
ALTER TABLE "issue_statuses"
ALTER COLUMN "code" SET NOT NULL;

-- Index to keep code lookups fast.
CREATE INDEX "issue_statuses_code_idx" ON "issue_statuses" ("code");
