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;

INSERT INTO team_message_thread_participants (
  id,
  law_firm_id,
  team_id,
  thread_id,
  user_id,
  added_by_user_id,
  joined_at
)
SELECT
  UUID(),
  tmt.law_firm_id,
  tmt.team_id,
  tmt.id,
  tmt.participant_a_user_id,
  tmt.created_by_user_id,
  tmt.created_at
FROM team_message_threads tmt
LEFT JOIN team_message_thread_participants existing
  ON existing.thread_id = tmt.id
 AND existing.user_id = tmt.participant_a_user_id
WHERE existing.id IS NULL;

INSERT INTO team_message_thread_participants (
  id,
  law_firm_id,
  team_id,
  thread_id,
  user_id,
  added_by_user_id,
  joined_at
)
SELECT
  UUID(),
  tmt.law_firm_id,
  tmt.team_id,
  tmt.id,
  tmt.participant_b_user_id,
  tmt.created_by_user_id,
  tmt.created_at
FROM team_message_threads tmt
LEFT JOIN team_message_thread_participants existing
  ON existing.thread_id = tmt.id
 AND existing.user_id = tmt.participant_b_user_id
WHERE existing.id IS NULL;
