-- Giving every feedback author a name that resolves.
--
-- Three things at once, because they are one idea. An author who never picked a
-- name still gets `handle_number`, so the board can call them `Anonymous_12`
-- instead of merging every silent install into one "Anonymous". A name is now
-- registered rather than typed: `name_key` is unique, so it belongs to one
-- install, and `pin_hash` plus the two throttle columns are what let the person
-- behind it claim it again on a new device without letting anyone else.
--
-- `feedback_mentions` is the resolved form of an `@handle`: written at post
-- time, so a rename cannot rewrite who was pinged, and cascaded away with the
-- ticket or the reply it was written in.

ALTER TABLE "feedback_authors"
  ADD COLUMN "handle_number" SERIAL,
  ADD COLUMN "name_key" TEXT,
  ADD COLUMN "pin_hash" TEXT,
  ADD COLUMN "pin_failures" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN "pin_locked_until" TIMESTAMP(3);

-- Names that existed before registration did. They keep the name they are
-- posting under and hold it against anyone else claiming it; the first time
-- their owner opens the name screen they are asked to set a PIN.
UPDATE "feedback_authors"
SET "name_key" = trim(regexp_replace(lower("display_name"), '[^a-z0-9]+', ' ', 'g'))
WHERE "display_name" IS NOT NULL
  AND trim(regexp_replace(lower("display_name"), '[^a-z0-9]+', ' ', 'g')) <> '';

-- Two installs that picked the same name before it was unique: the older row
-- keeps it, the rest go back to their anonymous handle rather than the
-- migration failing on the index below.
UPDATE "feedback_authors" a
SET "display_name" = NULL, "name_key" = NULL
WHERE a."name_key" IS NOT NULL
  AND EXISTS (
    SELECT 1 FROM "feedback_authors" b
    WHERE b."name_key" = a."name_key"
      AND (b."created_at" < a."created_at"
        OR (b."created_at" = a."created_at" AND b."install_id" < a."install_id"))
  );

CREATE UNIQUE INDEX "feedback_authors_handle_number_key" ON "feedback_authors"("handle_number");
CREATE UNIQUE INDEX "feedback_authors_name_key_key" ON "feedback_authors"("name_key");

CREATE TABLE "feedback_mentions" (
  "id" SERIAL NOT NULL,
  "ticket_id" INTEGER NOT NULL,
  "comment_id" INTEGER,
  "install_id" TEXT NOT NULL,
  "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT "feedback_mentions_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "feedback_mentions_install_id_ticket_id_idx" ON "feedback_mentions"("install_id", "ticket_id");
CREATE INDEX "feedback_mentions_ticket_id_comment_id_idx" ON "feedback_mentions"("ticket_id", "comment_id");

ALTER TABLE "feedback_mentions" ADD CONSTRAINT "feedback_mentions_ticket_id_fkey"
  FOREIGN KEY ("ticket_id") REFERENCES "feedback_tickets"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "feedback_mentions" ADD CONSTRAINT "feedback_mentions_comment_id_fkey"
  FOREIGN KEY ("comment_id") REFERENCES "feedback_comments"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "feedback_mentions" ADD CONSTRAINT "feedback_mentions_install_id_fkey"
  FOREIGN KEY ("install_id") REFERENCES "feedback_authors"("install_id") ON DELETE CASCADE ON UPDATE CASCADE;