-- Create message_thread before migrations that reference it.
-- Safe for existing databases because the table may already exist from 004.

BEGIN;

CREATE TABLE IF NOT EXISTS message_thread (
    id                     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    channel_id             UUID NOT NULL,
    root_message_id        UUID NOT NULL REFERENCES message(id) ON DELETE CASCADE,
    created_by             UUID NOT NULL,
    replies_count          BIGINT NOT NULL DEFAULT 0,
    participants_count     BIGINT NOT NULL DEFAULT 0,
    last_reply_message_id  UUID NULL REFERENCES message(id) ON DELETE SET NULL,
    last_reply_at          TIMESTAMPTZ NULL,
    resolved               BOOLEAN NOT NULL DEFAULT FALSE,
    resolved_by            UUID NULL,
    resolved_at            TIMESTAMPTZ NULL,
    created_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT uq_message_thread_root UNIQUE (root_message_id)
);

COMMIT;