-- The public in-app feedback board: feature requests and bug reports, with a
-- threaded discussion and emoji reactions. Unlike `tweak_requests`, which is a
-- private queue between one install and the operators, everything here is
-- readable by every FTStore user, hence the moderation columns.

-- What an install calls itself. One row per install, and the only place a
-- display name lives, so a rename shows up on everything they ever wrote.
CREATE TABLE "feedback_authors" (
    "install_id" TEXT NOT NULL,
    "display_name" TEXT,
    "blocked" BOOLEAN NOT NULL DEFAULT false,
    "blocked_note" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "feedback_authors_pkey" PRIMARY KEY ("install_id")
);

CREATE TABLE "feedback_tickets" (
    "id" SERIAL NOT NULL,
    "install_id" TEXT NOT NULL,
    "kind" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    "body" TEXT NOT NULL,
    "status" TEXT NOT NULL DEFAULT 'open',
    "hidden" BOOLEAN NOT NULL DEFAULT false,
    "hidden_reason" TEXT,
    "edited_at" TIMESTAMP(3),
    "client_version" TEXT,
    "system_version" TEXT,
    "device_model" TEXT,
    "upvotes" INTEGER NOT NULL DEFAULT 0,
    "comment_count" INTEGER NOT NULL DEFAULT 0,
    "last_activity_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

CREATE TABLE "feedback_comments" (
    "id" SERIAL NOT NULL,
    "ticket_id" INTEGER NOT NULL,
    "install_id" TEXT,
    "author_user_id" INTEGER,
    "author_role" TEXT NOT NULL DEFAULT 'user',
    "body" TEXT,
    "status_from" TEXT,
    "status_to" TEXT,
    "hidden" BOOLEAN NOT NULL DEFAULT false,
    "hidden_reason" TEXT,
    "edited_at" TIMESTAMP(3),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- No unique index here on purpose: `comment_id` is nullable and Postgres treats
-- NULLs in a unique index as distinct, so the constraint that matters cannot be
-- expressed. `lib/feedback.ts` toggles inside a transaction instead, and every
-- count is DISTINCT install_id so a lost race still reads as one reaction.
CREATE TABLE "feedback_reactions" (
    "id" SERIAL NOT NULL,
    "ticket_id" INTEGER NOT NULL,
    "comment_id" INTEGER,
    "install_id" TEXT NOT NULL,
    "emoji" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

CREATE INDEX "feedback_tickets_hidden_status_last_activity_at_idx" ON "feedback_tickets"("hidden", "status", "last_activity_at");
CREATE INDEX "feedback_tickets_hidden_upvotes_idx" ON "feedback_tickets"("hidden", "upvotes");
CREATE INDEX "feedback_tickets_install_id_created_at_idx" ON "feedback_tickets"("install_id", "created_at");
CREATE INDEX "feedback_comments_ticket_id_created_at_idx" ON "feedback_comments"("ticket_id", "created_at");
CREATE INDEX "feedback_reactions_ticket_id_comment_id_idx" ON "feedback_reactions"("ticket_id", "comment_id");
CREATE INDEX "feedback_reactions_install_id_idx" ON "feedback_reactions"("install_id");

ALTER TABLE "feedback_comments" ADD CONSTRAINT "feedback_comments_ticket_id_fkey" FOREIGN KEY ("ticket_id") REFERENCES "feedback_tickets"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "feedback_reactions" ADD CONSTRAINT "feedback_reactions_ticket_id_fkey" FOREIGN KEY ("ticket_id") REFERENCES "feedback_tickets"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "feedback_reactions" ADD CONSTRAINT "feedback_reactions_comment_id_fkey" FOREIGN KEY ("comment_id") REFERENCES "feedback_comments"("id") ON DELETE CASCADE ON UPDATE CASCADE;