-- Direct messages between portal users (employees/admins)

CREATE TABLE IF NOT EXISTS direct_chat_channels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS direct_chat_members (
  channel_id INT NOT NULL,
  user_id INT NOT NULL,
  last_read_at DATETIME NULL,
  joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (channel_id, user_id),
  KEY idx_dcm_user (user_id),
  CONSTRAINT fk_dcm_channel FOREIGN KEY (channel_id) REFERENCES direct_chat_channels (id) ON DELETE CASCADE,
  CONSTRAINT fk_dcm_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS direct_chat_messages (
  id CHAR(36) NOT NULL PRIMARY KEY,
  channel_id INT NOT NULL,
  sender_user_id INT NOT NULL,
  body TEXT NOT NULL,
  deleted_for_all_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_dcm_channel_created (channel_id, created_at),
  CONSTRAINT fk_dcm_msg_channel FOREIGN KEY (channel_id) REFERENCES direct_chat_channels (id) ON DELETE CASCADE,
  CONSTRAINT fk_dcm_msg_sender FOREIGN KEY (sender_user_id) REFERENCES users (id)
);