-- DM chat: reply threads, read receipts, reactions

ALTER TABLE direct_chat_messages
  ADD COLUMN reply_to_id CHAR(36) NULL AFTER body,
  ADD COLUMN edited_at DATETIME NULL AFTER reply_to_id;

ALTER TABLE direct_chat_messages
  ADD CONSTRAINT fk_dcm_msg_reply FOREIGN KEY (reply_to_id) REFERENCES direct_chat_messages (id);

CREATE TABLE IF NOT EXISTS direct_chat_message_status (
  message_id CHAR(36) NOT NULL,
  user_id INT NOT NULL,
  status ENUM('sent', 'delivered', 'read', 'failed') NOT NULL DEFAULT 'sent',
  delivered_at DATETIME NULL,
  read_at DATETIME NULL,
  seen_at DATETIME NULL,
  PRIMARY KEY (message_id, user_id),
  CONSTRAINT fk_dcms_msg FOREIGN KEY (message_id) REFERENCES direct_chat_messages (id) ON DELETE CASCADE,
  CONSTRAINT fk_dcms_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS direct_chat_message_reactions (
  message_id CHAR(36) NOT NULL,
  user_id INT NOT NULL,
  emoji VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (message_id, user_id, emoji),
  CONSTRAINT fk_dcr_msg FOREIGN KEY (message_id) REFERENCES direct_chat_messages (id) ON DELETE CASCADE,
  CONSTRAINT fk_dcr_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);