Unified_Authentication_Platform/sql/V5__add_client_distribution.sql

-- V5__add_client_distribution.sql
-- 客户端 App 版本分发平台：分发表 + 版本表

-- ==========================================
-- Step 1: 创建客户端分发表
-- ==========================================
CREATE TABLE IF NOT EXISTS client_distributions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '分发名称',
    description TEXT NULL COMMENT '分发描述',
    icon_url VARCHAR(255) NULL COMMENT '图标URL',
    owner_id INT NOT NULL COMMENT '创建者 user_id',
    is_deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除 0:否 1:是',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_owner_id (owner_id),
    INDEX idx_is_deleted (is_deleted),
    CONSTRAINT fk_client_distributions_owner FOREIGN KEY (owner_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户端分发表';

-- ==========================================
-- Step 2: 创建客户端版本表
-- ==========================================
CREATE TABLE IF NOT EXISTS client_versions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    distribution_id INT NOT NULL COMMENT '分发ID',
    version_code VARCHAR(32) NOT NULL COMMENT '版本号如 1.2.3',
    version_name VARCHAR(100) NULL COMMENT '显示名称',
    release_notes TEXT NULL COMMENT '更新内容',
    object_key VARCHAR(512) NOT NULL COMMENT 'MinIO 对象键',
    file_size BIGINT NULL COMMENT '文件大小(字节)',
    platform VARCHAR(32) NULL COMMENT '平台 ios/android/windows',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_distribution_id (distribution_id),
    UNIQUE KEY uq_dist_version (distribution_id, version_code),
    CONSTRAINT fk_client_versions_distribution FOREIGN KEY (distribution_id) REFERENCES client_distributions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户端版本表';