multi-platform-media-manage/database/schema.sql

-- 智媒通数据库表结构
-- MySQL 8.0+

CREATE DATABASE IF NOT EXISTS media_manager 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

USE media_manager;

-- 用户表（软件登录账号）
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(500),
    nickname VARCHAR(50),
    role ENUM('admin', 'editor', 'operator') DEFAULT 'operator',
    status ENUM('active', 'disabled', 'pending') DEFAULT 'active',
    last_login_at TIMESTAMP NULL,
    last_login_ip VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 用户 Token 表（用于 Token 失效管理）
CREATE TABLE IF NOT EXISTS user_tokens (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    refresh_token VARCHAR(500) NOT NULL,
    device_info VARCHAR(255),
    ip_address VARCHAR(50),
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 系统配置表
CREATE TABLE IF NOT EXISTS system_config (
    id INT PRIMARY KEY AUTO_INCREMENT,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value TEXT,
    description VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 初始化系统配置
INSERT INTO system_config (config_key, config_value, description) VALUES
('allow_registration', 'false', '是否开放用户注册'),
('default_user_role', 'operator', '新注册用户默认角色')
ON DUPLICATE KEY UPDATE config_key=config_key;

-- 创建默认管理员账号 (密码: admin123)
INSERT INTO users (username, email, password_hash, nickname, role, status) VALUES
('admin', 'admin@example.com', '$2a$10$rQnM1YFKBqr6.8t5P7H5/.8mJSYvCXsMaJ6fBVdX5xFXQV8u7sSAu', '管理员', 'admin', 'active')
ON DUPLICATE KEY UPDATE username=username;

-- 平台账号分组表（关联用户）
CREATE TABLE IF NOT EXISTS account_groups (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 平台账号表（关联用户，核心数据表）
CREATE TABLE IF NOT EXISTS platform_accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    platform ENUM('douyin','kuaishou','weixin_video','xiaohongshu','bilibili','toutiao','baijiahao','qie','dayuhao') NOT NULL,
    account_name VARCHAR(100),
    account_id VARCHAR(100),
    avatar_url VARCHAR(500),
    fans_count INT DEFAULT 0,
    works_count INT DEFAULT 0,
    cookie_data TEXT,
    cookie_expire_at TIMESTAMP NULL,
    status ENUM('active','expired','disabled') DEFAULT 'active',
    proxy_config JSON,
    group_id INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES account_groups(id) ON DELETE SET NULL,
    UNIQUE KEY uk_user_platform_account (user_id, platform, account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 视频发布任务表（关联用户）
CREATE TABLE IF NOT EXISTS publish_tasks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    video_path VARCHAR(500),
    video_filename VARCHAR(255),
    title VARCHAR(200),
    description TEXT,
    cover_path VARCHAR(500),
    tags JSON,
    target_accounts JSON,
    platform_configs JSON,
    publish_proxy JSON,
    status ENUM('pending','processing','completed','failed','cancelled') DEFAULT 'pending',
    scheduled_at TIMESTAMP NULL,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 发布结果表
CREATE TABLE IF NOT EXISTS publish_results (
    id INT PRIMARY KEY AUTO_INCREMENT,
    task_id INT NOT NULL,
    account_id INT NOT NULL,
    platform VARCHAR(50),
    status ENUM('success','failed'),
    video_url VARCHAR(500),
    platform_video_id VARCHAR(100),
    error_message TEXT,
    published_at TIMESTAMP NULL,
    FOREIGN KEY (task_id) REFERENCES publish_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (account_id) REFERENCES platform_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 评论表（关联用户）
CREATE TABLE IF NOT EXISTS comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    account_id INT NOT NULL,
    work_id INT NULL COMMENT '作品ID（关联works.id，可为空）',
    platform VARCHAR(50),
    video_id VARCHAR(100),
    platform_video_url VARCHAR(500),
    comment_id VARCHAR(100) UNIQUE,
    parent_comment_id VARCHAR(100),
    author_id VARCHAR(100),
    author_name VARCHAR(100),
    author_avatar VARCHAR(500),
    content TEXT,
    like_count INT DEFAULT 0,
    reply_content TEXT,
    replied_at TIMESTAMP NULL,
    is_read BOOLEAN DEFAULT FALSE,
    is_top BOOLEAN DEFAULT FALSE,
    comment_time TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (account_id) REFERENCES platform_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (work_id) REFERENCES works(id) ON DELETE SET NULL,
    INDEX idx_comment_user_read (user_id, is_read),
    INDEX idx_comment_account (account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 数据统计表（关联用户）
CREATE TABLE IF NOT EXISTS analytics_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    account_id INT NOT NULL,
    date DATE NOT NULL,
    fans_count INT DEFAULT 0,
    fans_increase INT DEFAULT 0,
    views_count INT DEFAULT 0,
    likes_count INT DEFAULT 0,
    comments_count INT DEFAULT 0,
    shares_count INT DEFAULT 0,
    income DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (account_id) REFERENCES platform_accounts(id) ON DELETE CASCADE,
    UNIQUE KEY uk_account_date (account_id, date),
    INDEX idx_analytics_user_date (user_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 操作日志表（审计追踪）
CREATE TABLE IF NOT EXISTS operation_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    action VARCHAR(50) NOT NULL,
    target_type VARCHAR(50),
    target_id INT,
    detail JSON,
    ip_address VARCHAR(50),
    user_agent VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_log_user_time (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 作品表（关联平台账号）
CREATE TABLE IF NOT EXISTS works (
    id INT PRIMARY KEY AUTO_INCREMENT,
    userId INT NOT NULL,
    accountId INT NOT NULL,
    platform VARCHAR(20) NOT NULL,
    platform_video_id VARCHAR(500) NOT NULL,
    title VARCHAR(200) DEFAULT '',
    description TEXT,
    cover_url VARCHAR(500) DEFAULT '',
    video_url TEXT,
    duration VARCHAR(20) DEFAULT '00:00',
    status VARCHAR(20) DEFAULT 'published',
    publish_time DATETIME,
    play_count INT DEFAULT 0,
    like_count INT DEFAULT 0,
    comment_count INT DEFAULT 0,
    share_count INT DEFAULT 0,
    collect_count INT DEFAULT 0,
    -- ===== 昨日数据快照（yesterday_*）=====
    yesterday_play_count INT DEFAULT 0 COMMENT '昨日播放数',
    yesterday_like_count INT DEFAULT 0 COMMENT '昨日点赞数',
    yesterday_comment_count INT DEFAULT 0 COMMENT '昨日评论数',
    yesterday_share_count INT DEFAULT 0 COMMENT '昨日分享数',
    yesterday_collect_count INT DEFAULT 0 COMMENT '昨日收藏数',
    yesterday_recommend_count INT DEFAULT 0 COMMENT '昨日推荐数',
    yesterday_fans_increase INT DEFAULT 0 COMMENT '昨日涨粉数',
    yesterday_follow_count INT DEFAULT 0 COMMENT '昨日关注数',
    yesterday_cover_click_rate VARCHAR(50) DEFAULT '0' COMMENT '昨日封面点击率',
    yesterday_avg_watch_duration VARCHAR(50) DEFAULT '0' COMMENT '昨日平均观看时长',
    yesterday_total_watch_duration VARCHAR(50) DEFAULT '0' COMMENT '昨日总观看时长',
    yesterday_completion_rate VARCHAR(50) DEFAULT '0' COMMENT '昨日完播率',
    yesterday_two_second_exit_rate VARCHAR(50) DEFAULT '0' COMMENT '昨日2秒退出率',
    yesterday_completion_rate_5s VARCHAR(50) DEFAULT '0' COMMENT '昨日5秒完播率',
    yesterday_exposure_count INT DEFAULT 0 COMMENT '昨日曝光数',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_platform (userId, platform),
    UNIQUE KEY uk_account_platform_video (accountId, platform_video_id),
    FOREIGN KEY (accountId) REFERENCES platform_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 作品每日统计表（记录每天的数据快照）
CREATE TABLE IF NOT EXISTS work_day_statistics (
    id INT PRIMARY KEY AUTO_INCREMENT,
    work_id INT NOT NULL,
    record_date DATE NOT NULL,
    play_count INT DEFAULT 0 COMMENT '播放数',
    exposure_count INT DEFAULT 0 COMMENT '曝光数/展现量',
    like_count INT DEFAULT 0 COMMENT '点赞数',
    recommend_count INT DEFAULT 0 COMMENT '推荐量',
    comment_count INT DEFAULT 0 COMMENT '评论数',
    share_count INT DEFAULT 0 COMMENT '分享数',
    collect_count INT DEFAULT 0 COMMENT '收藏数',
    fans_increase INT DEFAULT 0 COMMENT '涨粉数',
    follow_count INT DEFAULT 0 COMMENT '关注数',
    cover_click_rate VARCHAR(50) DEFAULT '0' COMMENT '封面点击率',
    avg_watch_duration VARCHAR(50) DEFAULT '0' COMMENT '平均观看时长（秒）',
    total_watch_duration VARCHAR(50) DEFAULT '0' COMMENT '总观看时长（秒）',
    completion_rate VARCHAR(50) DEFAULT '0' COMMENT '完播率',
    two_second_exit_rate VARCHAR(50) DEFAULT '0' COMMENT '2秒退出率',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_work_date (work_id, record_date),
    INDEX idx_work_id (work_id),
    INDEX idx_record_date (record_date),
    FOREIGN KEY (work_id) REFERENCES works(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='作品每日统计数据';

-- 账号每日统计表（记录每个平台账号的粉丝数和作品数，不关联作品）
CREATE TABLE IF NOT EXISTS user_day_statistics (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL COMMENT '账号ID（关联platform_accounts.id）',
    record_date DATE NOT NULL COMMENT '记录日期',
    fans_count INT DEFAULT 0 COMMENT '粉丝数',
    works_count INT DEFAULT 0 COMMENT '作品数',
    play_count INT DEFAULT 0 COMMENT '播放数',
    exposure_count INT DEFAULT 0 COMMENT '曝光数/展现量',
    comment_count INT DEFAULT 0 COMMENT '评论数',
    fans_increase INT DEFAULT 0 COMMENT '涨粉数',
    like_count INT DEFAULT 0 COMMENT '点赞数',
    share_count INT DEFAULT 0 COMMENT '分享数',
    collect_count INT DEFAULT 0 COMMENT '收藏数',
    recommend_count INT DEFAULT 0 COMMENT '推荐数',
    follow_count INT DEFAULT 0 COMMENT '关注数（来自视频等）',
    cover_click_rate VARCHAR(50) DEFAULT '0' COMMENT '封面点击率',
    avg_watch_duration VARCHAR(50) DEFAULT '0' COMMENT '平均观看时长（秒）',
    total_watch_duration VARCHAR(50) DEFAULT '0' COMMENT '观看总时长（秒）',
    completion_rate VARCHAR(50) DEFAULT '0' COMMENT '视频完播率',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_account_date (account_id, record_date),
    INDEX idx_account_id (account_id),
    INDEX idx_record_date (record_date),
    FOREIGN KEY (account_id) REFERENCES platform_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='账号每日统计数据（按平台账号统计）';