TappoCloud/store/schema.sql

-- ============================================================
-- TappoCloud App Store - MySQL Schema
-- Version: 1.0.0
-- Table Prefix: tpc_
-- ============================================================

-- 开发者表
CREATE TABLE IF NOT EXISTS tpc_developers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(128) NOT NULL COMMENT '开发者名称',
    email VARCHAR(255) COMMENT '联系邮箱',
    website VARCHAR(512) COMMENT '官网地址',
    avatar_url VARCHAR(512) COMMENT '头像 URL',
    verified TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否官方认证',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='开发者信息表';

-- 分类表
CREATE TABLE IF NOT EXISTS tpc_categories (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(64) NOT NULL UNIQUE COMMENT '分类标识符 (URL 友好)',
    name VARCHAR(128) NOT NULL COMMENT '分类显示名称',
    description VARCHAR(255) COMMENT '分类描述',
    icon VARCHAR(64) COMMENT '分类图标 (可选)',
    sort_order SMALLINT NOT NULL DEFAULT 0 COMMENT '排序权重',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应用分类表';

-- 应用主表
CREATE TABLE IF NOT EXISTS tpc_apps (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    app_uid VARCHAR(64) NOT NULL UNIQUE COMMENT '应用唯一标识 (如 mfa-authenticator)',
    name VARCHAR(255) NOT NULL COMMENT '应用名称',
    type ENUM('system', 'community', 'external') NOT NULL COMMENT '应用类型',
    developer_id BIGINT UNSIGNED COMMENT '开发者 ID',
    icon_url VARCHAR(512) COMMENT '应用图标 URL',
    short_description VARCHAR(255) COMMENT '简短描述',
    description TEXT COMMENT '详细描述 (支持 Markdown)',
    homepage_url VARCHAR(512) COMMENT '应用主页',
    current_version_id BIGINT UNSIGNED COMMENT '当前版本 ID',
    status ENUM('draft', 'published', 'deprecated', 'suspended') NOT NULL DEFAULT 'draft' COMMENT '应用状态',
    is_featured TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否推荐',
    download_count INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '下载次数',
    publish_time DATETIME COMMENT '首次发布时间',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (developer_id) REFERENCES tpc_developers(id) ON DELETE SET NULL,
    INDEX idx_type (type),
    INDEX idx_status (status),
    INDEX idx_featured (is_featured),
    INDEX idx_publish_time (publish_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应用主表';

-- 应用-分类关联表 (多对多)
CREATE TABLE IF NOT EXISTS tpc_app_category_map (
    app_id BIGINT UNSIGNED NOT NULL,
    category_id SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (app_id, category_id),
    FOREIGN KEY (app_id) REFERENCES tpc_apps(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES tpc_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应用分类关联表';

-- 应用版本表
CREATE TABLE IF NOT EXISTS tpc_app_versions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    app_id BIGINT UNSIGNED NOT NULL,
    version VARCHAR(64) NOT NULL COMMENT '版本号 (SemVer)',
    storage_url VARCHAR(512) NOT NULL COMMENT 'ZIP 包存储 URL (OSS/S3)',
    checksum_sha256 CHAR(64) COMMENT 'SHA256 校验值',
    size_bytes BIGINT UNSIGNED COMMENT '文件大小 (字节)',
    manifest JSON COMMENT 'app.yml 解析后的 JSON (pages, commands, permissions)',
    release_notes TEXT COMMENT '版本更新说明',
    min_client_version VARCHAR(32) COMMENT '最低客户端版本要求',
    status ENUM('draft', 'published', 'yanked') NOT NULL DEFAULT 'draft' COMMENT '版本状态',
    published_at DATETIME COMMENT '发布时间',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_app_version (app_id, version),
    FOREIGN KEY (app_id) REFERENCES tpc_apps(id) ON DELETE CASCADE,
    INDEX idx_status (status),
    INDEX idx_published_at (published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应用版本表';

-- 添加 tpc_apps.current_version_id 外键 (延迟添加避免循环依赖)
ALTER TABLE tpc_apps
ADD CONSTRAINT fk_current_version
FOREIGN KEY (current_version_id) REFERENCES tpc_app_versions(id) ON DELETE SET NULL;

-- ============================================================
-- 初始数据
-- ============================================================

-- 预置分类
INSERT INTO tpc_categories (slug, name, description, sort_order) VALUES
('security', '安全工具', '密码管理、认证、加密等安全相关应用', 10),
('productivity', '效率工具', '提升工作效率的自动化工具', 20),
('tool', '实用工具', '通用工具类应用', 30),
('ai', 'AI 应用', '人工智能、机器学习相关应用', 40),
('data', '数据处理', '数据转换、分析、导入导出工具', 50),
('integration', '集成服务', '第三方服务集成 (SaaS)', 60);

-- 管理员表
CREATE TABLE IF NOT EXISTS tpc_admins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL UNIQUE COMMENT '用户名',
    password_hash VARCHAR(128) NOT NULL COMMENT 'bcrypt 密码哈希',
    is_active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login_at DATETIME COMMENT '最后登录时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='管理员表';

-- ============================================================
-- 初始数据
-- ============================================================

-- 预置管理员 (密码: admin123, 使用 bcrypt 加密)
-- 生产环境请修改密码！
INSERT INTO tpc_admins (username, password_hash) VALUES
('admin', '$2b$12$5cPnxzXZH7iroq8sgUKFYuL61lxreoW5782uCSL/SgoB6aBmNRySq')
ON DUPLICATE KEY UPDATE username=username;

-- 预置分类
INSERT INTO tpc_categories (slug, name, description, sort_order) VALUES
('security', '安全工具', '密码管理、认证、加密等安全相关应用', 10),
('productivity', '效率工具', '提升工作效率的自动化工具', 20),
('tool', '实用工具', '通用工具类应用', 30),
('ai', 'AI 应用', '人工智能、机器学习相关应用', 40),
('data', '数据处理', '数据转换、分析、导入导出工具', 50),
('integration', '集成服务', '第三方服务集成 (SaaS)', 60)
ON DUPLICATE KEY UPDATE slug=slug;

-- 预置官方开发者
INSERT INTO tpc_developers (name, email, website, verified) VALUES
('Tappo Team', 'dev@tappo.dev', 'https://tappo.dev', 1)
ON DUPLICATE KEY UPDATE name=name;