-- ============================================================
-- MIGRATION: Comment Read Tracking
-- Adds TTM_TASK_COMMENT_READ table to track when users last
-- read comments for a task. Used to calculate unread counts.
--
-- Safe to re-run (uses IF NOT EXISTS).
-- ============================================================

CREATE TABLE IF NOT EXISTS `TTM_TASK_COMMENT_READ` (
  `ID`            bigint    NOT NULL AUTO_INCREMENT,
  `USER_ID`       bigint    NOT NULL COMMENT 'FK -> users.ID',
  `TASK_ID`       bigint    NOT NULL COMMENT 'FK -> TTM_TASK.ID',
  `LAST_READ_AT`  datetime  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UQ_USER_TASK` (`USER_ID`, `TASK_ID`),
  KEY `IDX_READ_TASK` (`TASK_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
