CREATE TYPE message_status AS ENUM ('pending', 'sent', 'canceled');

CREATE TABLE messages (
    message_id    uuid            NOT NULL PRIMARY KEY,
    created_at    timestamptz     NOT NULL,
    scheduled_at  timestamptz,
    status        message_status  NOT NULL,
    title         text            NOT NULL,
    content       text            NOT NULL,
    channel       bigint          NOT NULL,
    thumbnail_url text
);

CREATE TABLE messages_users (
    message_id  uuid    NOT NULL REFERENCES users (user_id) ON UPDATE cascade ON DELETE cascade,
    user_id     uuid    NOT NULL REFERENCES users (user_id) ON UPDATE cascade ON DELETE cascade,
    is_read     boolean NOT NULL,

    CONSTRAINT messages_users_pkey PRIMARY KEY (message_id, user_id)
);