-- Voting replaces reacting on the feedback board.
--
-- A reaction was eight emoji of which exactly one meant anything: `+1` was what
-- ranked the board. A vote is one value per install per target, `1` or `-1`, so
-- "how much does anyone want this?" is a sum rather than a tally of glyphs, and
-- it fits on a board row next to the title instead of behind a tap.
--
-- The two emoji that were already votes carry over; the other six were opinions
-- with no ranking behind them and are dropped with the table. The counters are
-- renamed rather than reused because their old values counted rows that are
-- about to be gone, and `vote_score` is stored because Prisma cannot order by an
-- expression.

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

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

CREATE INDEX "feedback_votes_ticket_id_comment_id_idx" ON "feedback_votes"("ticket_id", "comment_id");
CREATE INDEX "feedback_votes_install_id_idx" ON "feedback_votes"("install_id");

ALTER TABLE "feedback_votes" ADD CONSTRAINT "feedback_votes_ticket_id_fkey"
  FOREIGN KEY ("ticket_id") REFERENCES "feedback_tickets"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "feedback_votes" ADD CONSTRAINT "feedback_votes_comment_id_fkey"
  FOREIGN KEY ("comment_id") REFERENCES "feedback_comments"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- One row per install per target: an install that managed to file two `+1`s on
-- one ticket was always counted once, and `DISTINCT ON` keeps that true. An
-- install that reacted both 👍 and 👎 to the same thing kept both rows; the
-- newest one is the vote it ends up with.
INSERT INTO "feedback_votes" ("ticket_id", "comment_id", "install_id", "value", "created_at", "updated_at")
SELECT DISTINCT ON ("ticket_id", "comment_id", "install_id")
  "ticket_id",
  "comment_id",
  "install_id",
  CASE WHEN "emoji" = '+1' THEN 1 ELSE -1 END,
  "created_at",
  "created_at"
FROM "feedback_reactions"
WHERE "emoji" IN ('+1', '-1')
ORDER BY "ticket_id", "comment_id", "install_id", "created_at" DESC;

DROP TABLE "feedback_reactions";

ALTER TABLE "feedback_tickets"
  DROP COLUMN "upvotes",
  ADD COLUMN "vote_up" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN "vote_down" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN "vote_score" INTEGER NOT NULL DEFAULT 0;

UPDATE "feedback_tickets" t
SET "vote_up" = v."up", "vote_down" = v."down", "vote_score" = v."up" - v."down"
FROM (
  SELECT
    "ticket_id",
    count(*) FILTER (WHERE "value" > 0) AS "up",
    count(*) FILTER (WHERE "value" < 0) AS "down"
  FROM "feedback_votes"
  WHERE "comment_id" IS NULL
  GROUP BY "ticket_id"
) v
WHERE t."id" = v."ticket_id";

-- `feedback_tickets_hidden_upvotes_idx` went with the column it was on.
CREATE INDEX "feedback_tickets_hidden_vote_score_idx" ON "feedback_tickets"("hidden", "vote_score");