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

INSERT INTO team_message_thread_reads (
  id,
  law_firm_id,
  team_id,
  thread_id,
  user_id,
  last_read_at,
  created_at,
  updated_at
)
SELECT
  UUID(),
  tmtp.law_firm_id,
  tmtp.team_id,
  tmtp.thread_id,
  tmtp.user_id,
  COALESCE(tmt.last_message_at, tmt.created_at),
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP
FROM team_message_thread_participants tmtp
INNER JOIN team_message_threads tmt ON tmt.id = tmtp.thread_id
LEFT JOIN team_message_thread_reads existing
  ON existing.thread_id = tmtp.thread_id
 AND existing.user_id = tmtp.user_id
WHERE existing.id IS NULL;
