SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS team_threads (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 workspace_id BIGINT UNSIGNED NOT NULL,
 entity_type ENUM('task','campaign','scheduled_post','experiment') NOT NULL,
 entity_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(255) NOT NULL,
 created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_tt_entity(workspace_id,entity_type,entity_id),
 INDEX idx_tt_updated(workspace_id,updated_at),
 CONSTRAINT fk_tt_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
 CONSTRAINT fk_tt_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS team_thread_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 workspace_id BIGINT UNSIGNED NOT NULL,
 thread_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 body TEXT NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_ttm_thread(thread_id,id),
 CONSTRAINT fk_ttm_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
 CONSTRAINT fk_ttm_thread FOREIGN KEY(thread_id) REFERENCES team_threads(id) ON DELETE CASCADE,
 CONSTRAINT fk_ttm_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS team_mentions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 workspace_id BIGINT UNSIGNED NOT NULL,
 thread_id BIGINT UNSIGNED NOT NULL,
 message_id BIGINT UNSIGNED NOT NULL,
 mentioned_user_id BIGINT UNSIGNED NOT NULL,
 author_user_id BIGINT UNSIGNED NOT NULL,
 read_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_tm_message_user(message_id,mentioned_user_id),
 INDEX idx_tm_user_read(workspace_id,mentioned_user_id,read_at),
 CONSTRAINT fk_tm_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
 CONSTRAINT fk_tm_thread FOREIGN KEY(thread_id) REFERENCES team_threads(id) ON DELETE CASCADE,
 CONSTRAINT fk_tm_message FOREIGN KEY(message_id) REFERENCES team_thread_messages(id) ON DELETE CASCADE,
 CONSTRAINT fk_tm_mentioned FOREIGN KEY(mentioned_user_id) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_tm_author FOREIGN KEY(author_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS team_activity_feed (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 workspace_id BIGINT UNSIGNED NOT NULL,
 entity_type VARCHAR(80) NOT NULL,
 entity_id BIGINT UNSIGNED NOT NULL,
 event_type VARCHAR(80) NOT NULL,
 user_id BIGINT UNSIGNED NULL,
 metadata LONGTEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_taf_entity(workspace_id,entity_type,entity_id,created_at),
 INDEX idx_taf_time(workspace_id,created_at),
 CONSTRAINT fk_taf_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
 CONSTRAINT fk_taf_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
