buff3.0/backend-client/migrations/20260317_create_client_app_versions.sql

-- 客户端版本发布表
CREATE TABLE IF NOT EXISTS client_app_versions (
  id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
  platform VARCHAR(20) NOT NULL DEFAULT 'windows' COMMENT '平台: windows/macos/linux',
  channel VARCHAR(20) NOT NULL DEFAULT 'stable' COMMENT '发布通道: stable/beta',
  version VARCHAR(50) NOT NULL COMMENT '版本号，如 1.2.0',
  build_number INT NULL COMMENT '构建号',
  release_title VARCHAR(120) NULL COMMENT '发布标题',
  release_notes_html TEXT NULL COMMENT '富文本更新日志',
  download_url VARCHAR(500) NULL COMMENT '下载地址',
  download_hash VARCHAR(128) NULL COMMENT '下载包hash',
  is_force_update TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否强制更新',
  min_supported_version VARCHAR(50) NULL COMMENT '最低支持版本',
  status VARCHAR(20) NOT NULL DEFAULT 'draft' COMMENT '状态: draft/published/offline',
  published_at DATETIME NULL COMMENT '发布时间',
  created_by VARCHAR(64) NULL COMMENT '创建人',
  updated_by VARCHAR(64) NULL COMMENT '更新人',
  is_deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否删除: 0-否, 1-是',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  UNIQUE KEY uk_client_app_version (platform, channel, version),
  KEY idx_client_app_version_query (platform, channel, status, is_deleted, published_at),
  KEY idx_client_app_version_min_supported (platform, channel, min_supported_version, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户端版本发布表';

-- 默认轮询间隔配置（登录页版本检查）
INSERT INTO system_config (
  config_key, config_value, config_type, app_name, description, is_system, is_editable, is_deleted
)
SELECT
  'client_version_check_interval_seconds',
  '60',
  'int',
  'backend-client',
  '客户端版本检查轮询间隔（秒）',
  1,
  1,
  0
WHERE NOT EXISTS (
  SELECT 1 FROM system_config
  WHERE config_key = 'client_version_check_interval_seconds'
    AND app_name LIKE '%backend-client%'
    AND is_deleted = 0
);