-- migrations/0009_discussion.sql
-- Phase 12: Native Tablo Chat

-- +goose Up

CREATE TABLE discussion_messages (
    id                 uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    tablo_id           uuid        NOT NULL REFERENCES tablos(id) ON DELETE CASCADE,
    author_user_id     uuid        NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    body               text        NOT NULL,
    created_at         timestamptz NOT NULL DEFAULT now(),
    updated_at         timestamptz NOT NULL DEFAULT now(),
    edited_at          timestamptz,
    edited_by_user_id  uuid        REFERENCES users(id) ON DELETE SET NULL,
    deleted_at         timestamptz,
    deleted_by_user_id uuid        REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT discussion_messages_body_not_blank CHECK (length(trim(body)) > 0),
    CONSTRAINT discussion_messages_body_max_length CHECK (char_length(body) <= 10000)
);

CREATE INDEX discussion_messages_tablo_created_idx ON discussion_messages(tablo_id, created_at, id);
CREATE INDEX discussion_messages_author_idx ON discussion_messages(author_user_id);

CREATE TABLE discussion_read_states (
    tablo_id             uuid        NOT NULL REFERENCES tablos(id) ON DELETE CASCADE,
    user_id              uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    last_read_message_id uuid        REFERENCES discussion_messages(id) ON DELETE SET NULL,
    last_read_at         timestamptz NOT NULL DEFAULT now(),
    created_at           timestamptz NOT NULL DEFAULT now(),
    updated_at           timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (tablo_id, user_id)
);

CREATE INDEX discussion_read_states_user_idx ON discussion_read_states(user_id, tablo_id);

-- +goose Down

DROP INDEX IF EXISTS discussion_read_states_user_idx;
DROP TABLE IF EXISTS discussion_read_states;
DROP INDEX IF EXISTS discussion_messages_author_idx;
DROP INDEX IF EXISTS discussion_messages_tablo_created_idx;
DROP TABLE IF EXISTS discussion_messages;