teecup/059_post_reactions_and_comments.sql

-- =====================================================================
--  TeeCup — Reaksjoner (emoji) + trådede kommentarer på innlegg,
--  migrasjon 059 (ADR-046).
--
--  To tabellpar, ett per eksisterende "innlegg"-tabell -- IKKE én
--  polymorf tabell på tvers av tenant-grensen (ADR-044 Beslutning C sin
--  begrunnelse gjelder identisk her: round_message har ingen
--  organization_id/RLS (ADR-033 Beslutning A), message har begge
--  (ADR-025) -- CLAUDE.md sin invariant "organization_id på alle
--  domenetabeller, håndhevet av RLS" krever egen RLS-håndtering for
--  org-siden uansett).
--
--  Én reaksjon per bruker per innlegg (UPSERT ved bytte av emoji, ikke
--  en stabel). Kommentarer lagres FLATT med parent_comment_id --
--  frontend bygger tre-strukturen. Sletting av en kommentar kaskade-
--  sletter undertråden (samme "kun slett, aldri rediger"-filosofi som
--  round_message/message selv).
-- =====================================================================
\set ON_ERROR_STOP on

-- ---------------------------------------------------------------------
-- round_message -- ingen RLS, speiler round_message selv (ADR-033)
-- ---------------------------------------------------------------------

CREATE TABLE round_message_reaction (
    id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    round_message_id uuid NOT NULL REFERENCES round_message(id) ON DELETE CASCADE,
    user_id          uuid NOT NULL REFERENCES app_user(id),
    emoji            text NOT NULL,
    created_at       timestamptz NOT NULL DEFAULT now(),

    UNIQUE (round_message_id, user_id)
);

CREATE INDEX round_message_reaction_message_idx ON round_message_reaction (round_message_id);

-- INGEN RLS -- samme plain_connection()-mønster som round_message selv.
GRANT SELECT, INSERT, UPDATE, DELETE ON round_message_reaction TO teecup_app;

CREATE TABLE round_message_comment (
    id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    round_message_id     uuid NOT NULL REFERENCES round_message(id) ON DELETE CASCADE,
    parent_comment_id    uuid REFERENCES round_message_comment(id) ON DELETE CASCADE,
    author_user_id       uuid NOT NULL REFERENCES app_user(id),
    -- Frosset ved skrivetidspunkt, samme prinsipp som round_message.author_display_name.
    author_display_name  text NOT NULL,
    body                 text NOT NULL,
    created_at           timestamptz NOT NULL DEFAULT now(),

    CHECK (length(btrim(body)) > 0)
);

CREATE INDEX round_message_comment_message_idx ON round_message_comment (round_message_id, created_at);
CREATE INDEX round_message_comment_parent_idx ON round_message_comment (parent_comment_id);

GRANT SELECT, INSERT, DELETE ON round_message_comment TO teecup_app;

-- ---------------------------------------------------------------------
-- message (lag-chat + org-oppslagstavle) -- RLS'et, org-tenant-invariant
-- ---------------------------------------------------------------------

CREATE TABLE message_reaction (
    id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id  uuid NOT NULL REFERENCES organization(id) ON DELETE CASCADE,
    message_id       uuid NOT NULL REFERENCES message(id) ON DELETE CASCADE,
    user_id          uuid NOT NULL REFERENCES app_user(id),
    emoji            text NOT NULL,
    created_at       timestamptz NOT NULL DEFAULT now(),

    UNIQUE (message_id, user_id)
);

CREATE INDEX message_reaction_message_idx ON message_reaction (message_id);

ALTER TABLE message_reaction ENABLE ROW LEVEL SECURITY;
ALTER TABLE message_reaction FORCE ROW LEVEL SECURITY;

CREATE POLICY org_isolation ON message_reaction
    USING (organization_id = app_current_org())
    WITH CHECK (organization_id = app_current_org());

GRANT SELECT, INSERT, UPDATE, DELETE ON message_reaction TO teecup_app;

CREATE TABLE message_comment (
    id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id      uuid NOT NULL REFERENCES organization(id) ON DELETE CASCADE,
    message_id           uuid NOT NULL REFERENCES message(id) ON DELETE CASCADE,
    parent_comment_id    uuid REFERENCES message_comment(id) ON DELETE CASCADE,
    author_user_id       uuid NOT NULL REFERENCES app_user(id),
    author_display_name  text NOT NULL,
    body                 text NOT NULL,
    created_at           timestamptz NOT NULL DEFAULT now(),

    CHECK (length(btrim(body)) > 0)
);

CREATE INDEX message_comment_message_idx ON message_comment (message_id, created_at);
CREATE INDEX message_comment_parent_idx ON message_comment (parent_comment_id);

ALTER TABLE message_comment ENABLE ROW LEVEL SECURITY;
ALTER TABLE message_comment FORCE ROW LEVEL SECURITY;

CREATE POLICY org_isolation ON message_comment
    USING (organization_id = app_current_org())
    WITH CHECK (organization_id = app_current_org());

GRANT SELECT, INSERT, DELETE ON message_comment TO teecup_app;