-- Ranggefüge: Rollen, die aufeinander aufbauen.
--
-- Bisher trug jede Rolle ihre Rechte vollständig selbst. Bei achtzehn Rollen
-- und zweiundzwanzig Rechten heisst das, dass beim Anlegen einer höheren
-- Rangrolle die ganze Liste der darunterliegenden noch einmal abgehakt werden
-- muss - fehleranfällig und bei jeder späteren Änderung doppelt zu pflegen.
--
-- Eine Leiter ist eine benannte, geordnete Folge von Rollen. Jede Sprosse erbt
-- alles von jeder Sprosse unter ihr. Verwaltet wird nur noch das, was oben
-- hinzukommt.
--
-- Die Reihenfolge steht in `ladderRank`, 0 ist unten. Bewusst **ohne**
-- eindeutigen Index über (ladderId, ladderRank): das Umsortieren schreibt alle
-- Sprossen einer Leiter in einem Zug neu, und ein sofort geprüfter Index
-- verböte die Zwischenzustände. Doppelte Ränge sind harmlos, sie erben dann
-- gegenseitig voneinander.
--
-- Zyklen sind ausgeschlossen, weil eine Ordnung über ganzen Zahlen keine
-- zulässt. Das ist der Grund für die Leiter statt eines Zeigers je Rolle.

CREATE TABLE "RoleLadder" (
    "id"          TEXT         NOT NULL,
    "name"        TEXT         NOT NULL,
    "note"        TEXT,
    "createdById" TEXT,
    "createdAt"   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt"   TIMESTAMP(3) NOT NULL,

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

CREATE UNIQUE INDEX "RoleLadder_name_key" ON "RoleLadder"("name");

ALTER TABLE "RoleMapping" ADD COLUMN "ladderId"   TEXT;
ALTER TABLE "RoleMapping" ADD COLUMN "ladderRank" INTEGER;

-- Verschwindet eine Leiter, stehen ihre Rollen wieder für sich. Ihre eigenen
-- Rechte behalten sie; nur das Geerbte fällt weg. Ein Rollenverlust durch
-- Löschen einer Leiter ist damit sichtbar und keine stille Rechteerweiterung.
ALTER TABLE "RoleMapping"
    ADD CONSTRAINT "RoleMapping_ladderId_fkey"
    FOREIGN KEY ("ladderId") REFERENCES "RoleLadder"("id")
    ON DELETE SET NULL ON UPDATE CASCADE;

CREATE INDEX "RoleMapping_ladderId_ladderRank_idx" ON "RoleMapping"("ladderId", "ladderRank");

-- Eine Sprosse ohne Leiter ist sinnlos, eine Leiter ohne Sprosse nicht.
ALTER TABLE "RoleMapping"
    ADD CONSTRAINT "RoleMapping_ladder_vollstaendig"
    CHECK (("ladderId" IS NULL) = ("ladderRank" IS NULL));

-- Eine Änderung an der Leiter ändert die Rechte jedes Trägers. Derselbe Kanal
-- wie bei RoleMapping selbst, damit die Web-Anwendung ihre zwischengespeicherten
-- Rechte sofort verwirft und nicht erst nach der Verfallszeit.
-- Eigene Funktion, weil `notify_role_mapping_changed` die Spalte
-- "discordRoleId" liest, die es auf dieser Tabelle nicht gibt. Der Kanal ist
-- derselbe: die Gegenstelle unterscheidet nicht, wodurch die Zuordnung
-- ungueltig wurde, sie verwirft ohnehin alles.
CREATE OR REPLACE FUNCTION notify_role_ladder_changed() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('role_mapping_changed', COALESCE(NEW."id", OLD."id"));
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER role_ladder_notify
    AFTER INSERT OR UPDATE OR DELETE ON "RoleLadder"
    FOR EACH ROW EXECUTE FUNCTION notify_role_ladder_changed();