-- =====================================================
-- Timeline 数据库迁移脚本
-- 版本: V1.4.0
-- 日期: 2024
-- 描述: 个人用户增强功能 - 相册、反应、用户偏好、个人资料、统计
-- =====================================================

-- -----------------------------------------------------
-- 1. 相册系统表结构
-- -----------------------------------------------------

-- 相册表
CREATE TABLE IF NOT EXISTS album (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    instance_id VARCHAR(64) UNIQUE NOT NULL COMMENT '相册唯一标识',
    user_id VARCHAR(64) NOT NULL COMMENT '用户ID',
    name VARCHAR(200) NOT NULL COMMENT '相册名称',
    description TEXT COMMENT '相册描述',
    cover_photo_id VARCHAR(64) COMMENT '封面照片ID',
    photo_count INT DEFAULT 0 COMMENT '照片数量',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    is_delete TINYINT DEFAULT 0 COMMENT '是否删除 0-否 1-是',
    INDEX idx_album_user (user_id, is_delete, create_time DESC),
    INDEX idx_album_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='相册表';

-- 相册照片关联表
CREATE TABLE IF NOT EXISTS album_photo (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    album_id VARCHAR(64) NOT NULL COMMENT '相册ID',
    photo_id VARCHAR(64) NOT NULL COMMENT '照片ID（story_item_id）',
    sort_order INT DEFAULT 0 COMMENT '排序值，数值越小越靠前',
    added_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
    UNIQUE KEY uk_album_photo (album_id, photo_id),
    INDEX idx_album_photos (album_id, sort_order, added_time DESC),
    INDEX idx_photo_albums (photo_id, album_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='相册照片关联表';

-- -----------------------------------------------------
-- 2. 反应系统表结构（扩展现有点赞系统）
-- -----------------------------------------------------

-- 反应表（替代简单的点赞）
CREATE TABLE IF NOT EXISTS reaction (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    entity_type VARCHAR(20) NOT NULL COMMENT '实体类型: STORY_ITEM/PHOTO',
    entity_id VARCHAR(64) NOT NULL COMMENT '实体ID',
    user_id VARCHAR(64) NOT NULL COMMENT '用户ID',
    reaction_type VARCHAR(20) NOT NULL COMMENT '反应类型: LIKE/LOVE/LAUGH/WOW/SAD',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY uk_entity_user (entity_type, entity_id, user_id),
    INDEX idx_reaction_entity (entity_type, entity_id, reaction_type),
    INDEX idx_reaction_user (user_id, create_time DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='反应表';

-- -----------------------------------------------------
-- 3. 评论系统扩展（支持多实体类型）
-- -----------------------------------------------------

-- 通用评论表（扩展现有 story_comment）
CREATE TABLE IF NOT EXISTS comment (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    instance_id VARCHAR(64) UNIQUE NOT NULL COMMENT '评论唯一标识',
    entity_type VARCHAR(20) NOT NULL COMMENT '实体类型: STORY_ITEM/PHOTO',
    entity_id VARCHAR(64) NOT NULL COMMENT '实体ID',
    user_id VARCHAR(64) NOT NULL COMMENT '评论用户ID',
    parent_id VARCHAR(64) DEFAULT NULL COMMENT '父评论ID',
    reply_to_user_id VARCHAR(64) DEFAULT NULL COMMENT '回复的用户ID',
    content TEXT NOT NULL COMMENT '评论内容',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    is_delete TINYINT DEFAULT 0 COMMENT '是否删除 0-否 1-是',
    INDEX idx_comment_entity (entity_type, entity_id, is_delete, create_time ASC),
    INDEX idx_comment_user (user_id, create_time DESC),
    INDEX idx_comment_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通用评论表';

-- -----------------------------------------------------
-- 4. 用户偏好设置表
-- -----------------------------------------------------

-- 用户偏好表
CREATE TABLE IF NOT EXISTS user_preferences (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    user_id VARCHAR(64) UNIQUE NOT NULL COMMENT '用户ID',
    -- 主题设置
    theme_mode VARCHAR(20) DEFAULT 'auto' COMMENT '主题模式: light/dark/auto',
    color_scheme VARCHAR(50) DEFAULT 'default' COMMENT '配色方案',
    -- 布局设置
    gallery_layout VARCHAR(20) DEFAULT 'grid' COMMENT '画廊布局: grid/list',
    timeline_layout VARCHAR(20) DEFAULT 'grid' COMMENT '时间线布局: grid/list',
    album_layout VARCHAR(20) DEFAULT 'grid' COMMENT '相册布局: grid/list',
    card_size VARCHAR(20) DEFAULT 'medium' COMMENT '卡片大小: small/medium/large',
    -- 时间线显示设置
    timeline_display_mode VARCHAR(20) DEFAULT 'chronological' COMMENT '时间线显示模式: chronological/grouped/masonry',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_preferences_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户偏好设置表';

-- -----------------------------------------------------
-- 5. 用户个人资料扩展表
-- -----------------------------------------------------

-- 用户个人资料表
CREATE TABLE IF NOT EXISTS user_profile (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    user_id VARCHAR(64) UNIQUE NOT NULL COMMENT '用户ID',
    cover_photo_url VARCHAR(500) COMMENT '封面照片URL',
    bio VARCHAR(500) COMMENT '个人简介',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_profile_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户个人资料表';

-- 用户自定义字段表
CREATE TABLE IF NOT EXISTS user_custom_field (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    instance_id VARCHAR(64) UNIQUE NOT NULL COMMENT '字段唯一标识',
    user_id VARCHAR(64) NOT NULL COMMENT '用户ID',
    field_name VARCHAR(100) NOT NULL COMMENT '字段名称',
    field_value VARCHAR(500) NOT NULL COMMENT '字段值',
    visibility VARCHAR(20) DEFAULT 'public' COMMENT '可见性: public/private',
    sort_order INT DEFAULT 0 COMMENT '排序值',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_custom_field_user (user_id, sort_order),
    INDEX idx_custom_field_visibility (user_id, visibility)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户自定义字段表';

-- -----------------------------------------------------
-- 6. 智能收藏集元数据表
-- -----------------------------------------------------

-- 智能收藏集表
CREATE TABLE IF NOT EXISTS smart_collection (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    instance_id VARCHAR(64) UNIQUE NOT NULL COMMENT '收藏集唯一标识',
    user_id VARCHAR(64) NOT NULL COMMENT '用户ID',
    collection_type VARCHAR(20) NOT NULL COMMENT '收藏集类型: DATE/LOCATION/PERSON',
    name VARCHAR(200) NOT NULL COMMENT '收藏集名称',
    criteria_json JSON NOT NULL COMMENT '筛选条件JSON',
    content_count INT DEFAULT 0 COMMENT '内容数量',
    thumbnail_url VARCHAR(500) COMMENT '缩略图URL',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_collection_user (user_id, collection_type, create_time DESC),
    INDEX idx_collection_type (collection_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='智能收藏集表';

-- -----------------------------------------------------
-- 7. 离线同步变更记录表
-- -----------------------------------------------------

-- 离线变更记录表
CREATE TABLE IF NOT EXISTS offline_change_record (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    instance_id VARCHAR(64) UNIQUE NOT NULL COMMENT '变更记录唯一标识',
    user_id VARCHAR(64) NOT NULL COMMENT '用户ID',
    entity_type VARCHAR(20) NOT NULL COMMENT '实体类型: STORY/ALBUM/PHOTO',
    entity_id VARCHAR(64) NOT NULL COMMENT '实体ID',
    operation VARCHAR(20) NOT NULL COMMENT '操作类型: CREATE/UPDATE/DELETE',
    change_data JSON NOT NULL COMMENT '变更数据JSON',
    client_timestamp BIGINT NOT NULL COMMENT '客户端时间戳',
    synced TINYINT DEFAULT 0 COMMENT '是否已同步 0-否 1-是',
    synced_time DATETIME COMMENT '同步时间',
    conflict TINYINT DEFAULT 0 COMMENT '是否有冲突 0-否 1-是',
    error_message TEXT COMMENT '错误信息',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_change_user_sync (user_id, synced, create_time),
    INDEX idx_change_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='离线变更记录表';

-- -----------------------------------------------------
-- 8. 统计数据缓存表（扩展现有 user_stats_cache）
-- -----------------------------------------------------

-- 为现有 user_stats_cache 表添加新字段
ALTER TABLE user_stats_cache 
ADD COLUMN total_albums INT DEFAULT 0 COMMENT '总相册数',
ADD COLUMN total_photos INT DEFAULT 0 COMMENT '总照片数',
ADD COLUMN total_storage_bytes BIGINT DEFAULT 0 COMMENT '总存储字节数',
ADD COLUMN monthly_uploads_json JSON COMMENT '月度上传统计JSON',
ADD COLUMN yearly_uploads_json JSON COMMENT '年度上传统计JSON',
ADD COLUMN storage_breakdown_json JSON COMMENT '存储分类统计JSON';

-- -----------------------------------------------------
-- 9. 索引优化
-- -----------------------------------------------------

-- 为 story_item 表添加照片相关索引
CREATE INDEX IF NOT EXISTS idx_story_item_photo 
ON story_item(user_id, is_delete, create_time DESC);

-- 为 notification 表添加复合索引
CREATE INDEX IF NOT EXISTS idx_notification_entity 
ON notification(related_type, related_id, create_time DESC);

-- -----------------------------------------------------
-- 数据迁移说明
-- -----------------------------------------------------

-- 1. 新表均为空表，无需数据迁移
-- 2. user_stats_cache 新增字段默认值为 0 或 NULL，现有数据无需迁移
-- 3. 建议在低峰期执行此脚本
-- 4. Redis 缓存配置需要单独设置

-- -----------------------------------------------------
-- 回滚脚本 (如需回滚，请执行以下语句)
-- -----------------------------------------------------

/*
-- 删除新增字段
ALTER TABLE user_stats_cache DROP COLUMN total_albums;
ALTER TABLE user_stats_cache DROP COLUMN total_photos;
ALTER TABLE user_stats_cache DROP COLUMN total_storage_bytes;
ALTER TABLE user_stats_cache DROP COLUMN monthly_uploads_json;
ALTER TABLE user_stats_cache DROP COLUMN yearly_uploads_json;
ALTER TABLE user_stats_cache DROP COLUMN storage_breakdown_json;

-- 删除新增索引
DROP INDEX IF EXISTS idx_story_item_photo ON story_item;
DROP INDEX IF EXISTS idx_notification_entity ON notification;

-- 删除新增表
DROP TABLE IF EXISTS offline_change_record;
DROP TABLE IF EXISTS smart_collection;
DROP TABLE IF EXISTS user_custom_field;
DROP TABLE IF EXISTS user_profile;
DROP TABLE IF EXISTS user_preferences;
DROP TABLE IF EXISTS comment;
DROP TABLE IF EXISTS reaction;
DROP TABLE IF EXISTS album_photo;
DROP TABLE IF EXISTS album;
*/