pipu-music/services/api/migrations/00022_chat_features.sql

-- +goose Up
ALTER TABLE direct_messages ADD COLUMN reply_to uuid REFERENCES direct_messages(id) ON DELETE SET NULL,
 ADD COLUMN edited_at timestamptz, ADD COLUMN deleted_at timestamptz;
CREATE TABLE message_reactions (
 message_id uuid NOT NULL REFERENCES direct_messages(id) ON DELETE CASCADE,
 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 emoji text NOT NULL CHECK(emoji IN ('❤️','👍','🔥','😂','🎵','👏')),
 PRIMARY KEY(message_id,user_id,emoji)
);
CREATE TABLE chat_typing (
 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 peer_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 expires_at timestamptz NOT NULL,
 PRIMARY KEY(user_id,peer_id), CHECK(user_id<>peer_id)
);

-- +goose Down
DROP TABLE chat_typing;
DROP TABLE message_reactions;
ALTER TABLE direct_messages DROP COLUMN reply_to, DROP COLUMN edited_at, DROP COLUMN deleted_at;