CREATE TABLE IF NOT EXISTS project_chat_channels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_chat_project (project_id),
  CONSTRAINT fk_chat_project FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS project_chat_members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  channel_id INT NOT NULL,
  user_id INT NULL,
  employee_id INT NULL,
  role ENUM('admin', 'owner', 'collaborator') NOT NULL DEFAULT 'collaborator',
  joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_read_at DATETIME NULL,
  UNIQUE KEY uk_pcm_user (channel_id, user_id),
  UNIQUE KEY uk_pcm_employee (channel_id, employee_id),
  CONSTRAINT fk_pcm_channel FOREIGN KEY (channel_id) REFERENCES project_chat_channels (id) ON DELETE CASCADE,
  CONSTRAINT fk_pcm_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT fk_pcm_employee FOREIGN KEY (employee_id) REFERENCES employees (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS project_chat_messages (
  id CHAR(36) NOT NULL PRIMARY KEY,
  channel_id INT NOT NULL,
  sender_user_id INT NOT NULL,
  message_type ENUM('text', 'image', 'video', 'audio', 'voice_note', 'document', 'system') NOT NULL DEFAULT 'text',
  body TEXT NULL,
  reply_to_id CHAR(36) NULL,
  edited_at DATETIME NULL,
  deleted_for_all_at DATETIME NULL,
  deleted_for_sender_at DATETIME NULL,
  pinned_at DATETIME NULL,
  metadata JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_pcm_channel_created (channel_id, created_at),
  CONSTRAINT fk_pcm_msg_channel FOREIGN KEY (channel_id) REFERENCES project_chat_channels (id) ON DELETE CASCADE,
  CONSTRAINT fk_pcm_msg_sender FOREIGN KEY (sender_user_id) REFERENCES users (id),
  CONSTRAINT fk_pcm_msg_reply FOREIGN KEY (reply_to_id) REFERENCES project_chat_messages (id)
);

CREATE TABLE IF NOT EXISTS project_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_pcms_msg FOREIGN KEY (message_id) REFERENCES project_chat_messages (id) ON DELETE CASCADE,
  CONSTRAINT fk_pcms_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS project_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_pcr_msg FOREIGN KEY (message_id) REFERENCES project_chat_messages (id) ON DELETE CASCADE,
  CONSTRAINT fk_pcr_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS project_chat_attachments (
  id CHAR(36) NOT NULL PRIMARY KEY,
  message_id CHAR(36) NOT NULL,
  storage_key VARCHAR(512) NOT NULL,
  thumbnail_key VARCHAR(512) NULL,
  mime_type VARCHAR(128) NOT NULL,
  size_bytes BIGINT NOT NULL DEFAULT 0,
  original_name VARCHAR(512) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pca_msg FOREIGN KEY (message_id) REFERENCES project_chat_messages (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS project_chat_presence (
  user_id INT NOT NULL PRIMARY KEY,
  status ENUM('online', 'offline') NOT NULL DEFAULT 'offline',
  last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  typing_channel_id INT NULL,
  recording_channel_id INT NULL,
  CONSTRAINT fk_pcp_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT fk_pcp_typing FOREIGN KEY (typing_channel_id) REFERENCES project_chat_channels (id) ON DELETE SET NULL,
  CONSTRAINT fk_pcp_rec FOREIGN KEY (recording_channel_id) REFERENCES project_chat_channels (id) ON DELETE SET NULL
);