Tolan/backend/migrations/create_inbox_tables.sql

-- 站内信相关表
CREATE TABLE IF NOT EXISTS inbox_messages (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '站内信ID',
    message_type VARCHAR(32) NOT NULL COMMENT '消息类型: energy_gift/news_notice',
    title VARCHAR(200) NOT NULL COMMENT '中文标题',
    title_en VARCHAR(200) NOT NULL DEFAULT '' COMMENT '英文标题',
    content TEXT NOT NULL COMMENT '中文正文',
    content_en TEXT NULL COMMENT '英文正文',
    target_type VARCHAR(32) NOT NULL COMMENT '发送范围: users/all_users',
    energy_stone_amount INT NOT NULL DEFAULT 0 COMMENT '赠送能量石数量，新闻固定为0',
    created_by VARCHAR(64) NOT NULL DEFAULT 'web-admin' COMMENT '创建来源',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_inbox_type_created (message_type, created_at),
    INDEX idx_inbox_target_created (target_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='站内信主表';

CREATE TABLE IF NOT EXISTS inbox_message_targets (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '目标记录ID',
    message_id BIGINT NOT NULL COMMENT '站内信ID',
    user_id VARCHAR(64) NOT NULL COMMENT '目标用户ID',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    UNIQUE KEY uk_inbox_target_message_user (message_id, user_id),
    INDEX idx_inbox_target_user_created (user_id, created_at),
    CONSTRAINT fk_inbox_target_message_id
        FOREIGN KEY (message_id) REFERENCES inbox_messages(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='站内信目标用户表（仅定向消息使用）';

CREATE TABLE IF NOT EXISTS inbox_user_message_states (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户消息状态ID',
    message_id BIGINT NOT NULL COMMENT '站内信ID',
    user_id VARCHAR(64) NOT NULL COMMENT '用户ID',
    read_status TINYINT NOT NULL DEFAULT 0 COMMENT '是否已读:0未读,1已读',
    deleted_status TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除:0未删,1已删',
    claim_status TINYINT NOT NULL DEFAULT 0 COMMENT '是否领取:0未领,1已领',
    claimed_energy_stones INT NOT NULL DEFAULT 0 COMMENT '已领取能量石数量',
    read_at TIMESTAMP NULL DEFAULT NULL COMMENT '阅读时间',
    deleted_at TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
    claimed_at TIMESTAMP NULL DEFAULT NULL COMMENT '领取时间',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY uk_inbox_state_message_user (message_id, user_id),
    INDEX idx_inbox_state_user_visible (user_id, deleted_status, created_at),
    INDEX idx_inbox_state_user_claim (user_id, claim_status, created_at),
    CONSTRAINT fk_inbox_state_message_id
        FOREIGN KEY (message_id) REFERENCES inbox_messages(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='用户维度站内信状态表';