-- Post comments — two-level threads, likes, pinning, reports.
--
-- Threading is deliberately ONE level deep: `parent_id` is NULL for a
-- top-level comment and otherwise points at a TOP-LEVEL comment (the thread
-- root). When a user replies to a reply, the service flattens the new comment
-- onto the same root and records `reply_to_user_id` so the UI can render the
-- "@handle" it was addressed to — so every reply lives under one thread.
-- ON DELETE CASCADE on parent_id means deleting a root removes its whole thread.
CREATE TABLE IF NOT EXISTS post_comments (
    id               UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    post_id          UUID         NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id          UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    parent_id        UUID         REFERENCES post_comments(id) ON DELETE CASCADE,
    -- The user a reply is addressed to (for the "@handle" prefix). NULL on
    -- top-level comments. SET NULL if that user is later deleted.
    reply_to_user_id UUID         REFERENCES users(id) ON DELETE SET NULL,
    body             TEXT         NOT NULL,
    -- Denormalized like counter (the UI shows only the count; comment_likes
    -- keeps the relations). Kept in sync on like/unlike in the same txn — comment
    -- likes are far lower-volume than post likes, so no Redis batching here.
    likes_count      INTEGER      NOT NULL DEFAULT 0,
    pinned_at        TIMESTAMPTZ,                       -- non-null = pinned (top-level only)
    created_at       TIMESTAMPTZ  NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ  NOT NULL DEFAULT now()
);

-- Dedup: a user can't post the identical text twice in the same scope (post +
-- thread). COALESCE folds top-level (NULL parent) onto a fixed sentinel so the
-- index covers it; md5(body) keeps the key small for long comments.
CREATE UNIQUE INDEX IF NOT EXISTS post_comments_dedup
    ON post_comments (post_id, user_id, COALESCE(parent_id, '00000000-0000-0000-0000-000000000000'::uuid), md5(body));

-- Listing a post's top-level comments, and a thread's replies (oldest first).
CREATE INDEX IF NOT EXISTS post_comments_post_idx   ON post_comments (post_id, created_at);
CREATE INDEX IF NOT EXISTS post_comments_parent_idx ON post_comments (parent_id, created_at);

-- At most one pinned comment per post.
CREATE UNIQUE INDEX IF NOT EXISTS post_comments_one_pin
    ON post_comments (post_id) WHERE pinned_at IS NOT NULL;

-- Comment likes — membership rows backing the displayed count. Idempotent via PK.
CREATE TABLE IF NOT EXISTS comment_likes (
    comment_id UUID         NOT NULL REFERENCES post_comments(id) ON DELETE CASCADE,
    user_id    UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT now(),
    PRIMARY KEY (comment_id, user_id)
);
-- "Which comments has this viewer liked?" — viewer-state hydration across a thread.
CREATE INDEX IF NOT EXISTS comment_likes_user_idx ON comment_likes (user_id);

-- Comment reports — mirrors post_reports, keyed to a comment.
CREATE TABLE IF NOT EXISTS comment_reports (
    id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    reporter_id UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    comment_id  UUID         NOT NULL REFERENCES post_comments(id) ON DELETE CASCADE,
    category    VARCHAR(32)  NOT NULL,
    reason      TEXT         NOT NULL DEFAULT '',
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
    resolved_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS comment_reports_unresolved_idx
    ON comment_reports (created_at DESC) WHERE resolved_at IS NULL;