risk_control_platform/sql/risk_inspection_task_scan_interval.sql

-- =============================================================
-- 风控平台 数据库增量脚本 2026-07-21
-- 巡检任务扫码字段 3 列
-- 对应 commit f6614ac7 (feat(inspection): 扫码任务补全 4 字段 + verifyQrCode 时间窗口校验)
-- 全部 SQL 幂等(基于 INFORMATION_SCHEMA 判断)
-- =============================================================

USE risk_control_platform;

-- =============================================================
-- 1. 巡检任务表 risk_inspection_task 补 3 列
-- =============================================================

-- punch_device_name: 扫码设备名称(展示用)
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_inspection_task'
       AND COLUMN_NAME  = 'punch_device_name') = 0,
  'ALTER TABLE `risk_inspection_task` ADD COLUMN `punch_device_name` varchar(100) DEFAULT NULL COMMENT ''扫码设备名称(展示用)'' AFTER `qr_code`',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- scan_start_time: 扫码开启时间(二维码生效起始)
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_inspection_task'
       AND COLUMN_NAME  = 'scan_start_time') = 0,
  'ALTER TABLE `risk_inspection_task` ADD COLUMN `scan_start_time` datetime DEFAULT NULL COMMENT ''扫码开启时间(二维码生效起始)'' AFTER `punch_device_name`',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- scan_interval_sec: 扫码间隔(秒),默认 1800 秒(30 分钟)
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_inspection_task'
       AND COLUMN_NAME  = 'scan_interval_sec') = 0,
  'ALTER TABLE `risk_inspection_task` ADD COLUMN `scan_interval_sec` int DEFAULT 1800 COMMENT ''扫码间隔(秒),默认 1800'' AFTER `scan_start_time`',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =============================================================
-- 验证
-- =============================================================
SELECT '--- 巡检任务扫码相关字段 ---' AS info;
SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME   = 'risk_inspection_task'
  AND COLUMN_NAME IN ('punch_device_name','scan_start_time','scan_interval_sec','qr_code')
ORDER BY ORDINAL_POSITION;