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,
  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;
