CREATE TABLE IF NOT EXISTS team_message_threads (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  review_due_at DATETIME NULL,
  subject VARCHAR(255) NOT NULL,
  topic_type VARCHAR(30) NOT NULL DEFAULT 'general',
  status_code VARCHAR(30) NOT NULL DEFAULT 'open',
  participant_a_user_id CHAR(36) NOT NULL,
  participant_b_user_id CHAR(36) NOT NULL,
  created_by_user_id CHAR(36) NULL,
  closed_by_user_id CHAR(36) NULL,
  closed_at DATETIME NULL,
  last_message_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_team_message_threads_team_last (team_id, last_message_at),
  KEY idx_team_message_threads_team_status_last (team_id, status_code, last_message_at),
  KEY idx_team_message_threads_participant_a (participant_a_user_id, created_at),
  KEY idx_team_message_threads_participant_b (participant_b_user_id, created_at),
  KEY idx_team_message_threads_topic (topic_type),
  KEY idx_team_message_threads_case (case_id),
  KEY idx_team_message_threads_review_due (review_due_at),
  CONSTRAINT fk_team_message_threads_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_message_threads_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_message_threads_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_team_message_threads_participant_a
    FOREIGN KEY (participant_a_user_id) REFERENCES users (id),
  CONSTRAINT fk_team_message_threads_participant_b
    FOREIGN KEY (participant_b_user_id) REFERENCES users (id),
  CONSTRAINT fk_team_message_threads_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id),
  CONSTRAINT fk_team_message_threads_closed_by
    FOREIGN KEY (closed_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_message_thread_participants (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  thread_id CHAR(36) NOT NULL,
  user_id CHAR(36) NOT NULL,
  added_by_user_id CHAR(36) NULL,
  joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_team_message_thread_participants_thread_user (thread_id, user_id),
  KEY idx_team_message_thread_participants_user_joined (user_id, joined_at),
  KEY idx_team_message_thread_participants_team_thread (team_id, thread_id),
  KEY idx_team_message_thread_participants_added_by (added_by_user_id),
  CONSTRAINT fk_team_message_thread_participants_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_message_thread_participants_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_message_thread_participants_thread
    FOREIGN KEY (thread_id) REFERENCES team_message_threads (id),
  CONSTRAINT fk_team_message_thread_participants_user
    FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT fk_team_message_thread_participants_added_by
    FOREIGN KEY (added_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_messages (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  sequence_number BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  thread_id CHAR(36) NOT NULL,
  sender_user_id CHAR(36) NOT NULL,
  body_text LONGTEXT NOT NULL,
  replied_message_id CHAR(36) NULL,
  attachment_file_id CHAR(36) NULL,
  review_source_file_id CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_team_messages_sequence (sequence_number),
  KEY idx_team_messages_thread_created (thread_id, created_at),
  KEY idx_team_messages_sender_created (sender_user_id, created_at),
  KEY idx_team_messages_replied_message (replied_message_id),
  KEY idx_team_messages_attachment_file (attachment_file_id),
  KEY idx_team_messages_review_source_file (review_source_file_id),
  CONSTRAINT fk_team_messages_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_messages_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_messages_thread
    FOREIGN KEY (thread_id) REFERENCES team_message_threads (id),
  CONSTRAINT fk_team_messages_sender
    FOREIGN KEY (sender_user_id) REFERENCES users (id),
  CONSTRAINT fk_team_messages_replied_message
    FOREIGN KEY (replied_message_id) REFERENCES team_messages (id)
    ON DELETE SET NULL,
  CONSTRAINT fk_team_messages_attachment_file
    FOREIGN KEY (attachment_file_id) REFERENCES files (id)
    ON DELETE SET NULL,
  CONSTRAINT fk_team_messages_review_source_file
    FOREIGN KEY (review_source_file_id) REFERENCES files (id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_message_thread_reads (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  thread_id CHAR(36) NOT NULL,
  user_id CHAR(36) NOT NULL,
  last_read_message_id CHAR(36) NULL,
  last_read_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_team_message_thread_reads_thread_user (thread_id, user_id),
  KEY idx_team_message_thread_reads_user_team_read (user_id, team_id, last_read_at),
  KEY idx_team_message_thread_reads_team_thread (team_id, thread_id),
  CONSTRAINT fk_team_message_thread_reads_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_message_thread_reads_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_message_thread_reads_thread
    FOREIGN KEY (thread_id) REFERENCES team_message_threads (id),
  CONSTRAINT fk_team_message_thread_reads_user
    FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_message_attachment_comments (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  thread_id CHAR(36) NOT NULL,
  message_id CHAR(36) NOT NULL,
  attachment_file_id CHAR(36) NOT NULL,
  page_number INT NULL,
  anchor_x_ratio DECIMAL(8, 6) NULL,
  anchor_y_ratio DECIMAL(8, 6) NULL,
  selection_rects_json JSON NULL,
  selection_start_offset INT NULL,
  selection_end_offset INT NULL,
  selected_text TEXT NOT NULL,
  comment_text TEXT NOT NULL,
  created_by_user_id CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_team_message_attachment_comments_attachment_created (attachment_file_id, created_at),
  KEY idx_team_message_attachment_comments_thread_created (thread_id, created_at),
  KEY idx_team_message_attachment_comments_message (message_id),
  KEY idx_team_message_attachment_comments_created_by (created_by_user_id),
  CONSTRAINT fk_team_message_attachment_comments_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_message_attachment_comments_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_message_attachment_comments_thread
    FOREIGN KEY (thread_id) REFERENCES team_message_threads (id),
  CONSTRAINT fk_team_message_attachment_comments_message
    FOREIGN KEY (message_id) REFERENCES team_messages (id),
  CONSTRAINT fk_team_message_attachment_comments_attachment
    FOREIGN KEY (attachment_file_id) REFERENCES files (id),
  CONSTRAINT fk_team_message_attachment_comments_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
