-- ============================================================
-- 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);

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