-- Phase 4: moderation primitives.
--
-- thread_settings — per-root-comment reply restrictions written by xyz.effem.feed.threadgate.
-- labels          — moderation labels keyed (src_did, subject_uri, val). Effem's own labeler
--                   plus any external labelers the AppView imports from.
-- comments.self_labels        — author-applied content warnings.
-- profiles.labeler_subscriptions — DIDs whose labels this user trusts.

CREATE TABLE "thread_settings" (
    "root_uri"   VARCHAR(1024) PRIMARY KEY,
    "root_did"   VARCHAR(255)  NOT NULL,
    "rkey"       VARCHAR(512)  NOT NULL,
    "allow"      JSONB,
    "created_at" VARCHAR(64)   NOT NULL,
    "indexed_at" TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);
CREATE INDEX "idx_thread_settings_root_did" ON "thread_settings" ("root_did");

CREATE TABLE "labels" (
    "id"          BIGSERIAL    PRIMARY KEY,
    "src_did"     VARCHAR(255) NOT NULL,
    "subject_uri" VARCHAR(1024) NOT NULL,
    "val"         VARCHAR(128) NOT NULL,
    "neg"         BOOLEAN      NOT NULL DEFAULT FALSE,
    "created_at"  VARCHAR(64)  NOT NULL,
    "indexed_at"  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
-- One row per (src, subject, val) — re-applying a label idempotently updates
-- the existing row (incl. flipping `neg`) instead of producing duplicates.
CREATE UNIQUE INDEX "idx_labels_src_subject_val" ON "labels" ("src_did", "subject_uri", "val");
CREATE INDEX "idx_labels_subject" ON "labels" ("subject_uri");

ALTER TABLE "comments"
    ADD COLUMN "self_labels" JSONB;

ALTER TABLE "profiles"
    ADD COLUMN "labeler_subscriptions" JSONB;