forecast/release_note/20260924/20260924_data_file_sync.sql

-- 20260917 增加气象、功率文件数据同步任务 (aicoding/2026091706)
-- 可重复执行

-- -----------------------------------------------------
-- 1. 待处理文件表 data_file_sync_files
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS data_file_sync_files (
    id SERIAL NOT NULL,
    type VARCHAR(64),
    file VARCHAR(1024) NOT NULL,
    status INTEGER NOT NULL DEFAULT 0,
    handler VARCHAR(255),
    create_time TIMESTAMP NOT NULL DEFAULT now(),
    update_time TIMESTAMP NOT NULL DEFAULT now(),
    fail_reason VARCHAR(1024),
    process_times INTEGER NOT NULL DEFAULT 0,
    priority INTEGER NOT NULL DEFAULT 3,
    CONSTRAINT pk_data_file_sync_files PRIMARY KEY (id)
);

-- 文件路径查重保险(扫描任务按 file 去重插入)
CREATE UNIQUE INDEX IF NOT EXISTS uk_data_file_sync_files_file ON data_file_sync_files (file);

COMMENT ON TABLE data_file_sync_files IS '待处理数据文件同步';
COMMENT ON COLUMN data_file_sync_files.id IS '唯一标识';
COMMENT ON COLUMN data_file_sync_files.type IS '类型';
COMMENT ON COLUMN data_file_sync_files.file IS '文件(完整路径)';
COMMENT ON COLUMN data_file_sync_files.status IS '状态(0待处理,1处理中,2完成,99失败,-1取消)';
COMMENT ON COLUMN data_file_sync_files.handler IS '处理器bean名称';
COMMENT ON COLUMN data_file_sync_files.create_time IS '创建时间';
COMMENT ON COLUMN data_file_sync_files.update_time IS '更新时间';
COMMENT ON COLUMN data_file_sync_files.fail_reason IS '失败原因';
COMMENT ON COLUMN data_file_sync_files.process_times IS '处理次数';
COMMENT ON COLUMN data_file_sync_files.priority IS '优先级';

-- -----------------------------------------------------
-- 2. 字典配置项 data_file_sync_config
--    dict_value 存整个 JSON 数组: [{scanFolder, updateInterval, handler, type?}]
--    例: [{"scanFolder":"/data/weather","updateInterval":12,"handler":"weatherFileSyncHandler","type":"weather"}]
-- -----------------------------------------------------
-- JSON 数组配置可能超过 dict_value 原长度(VARCHAR(100)), 扩容(可重复执行)
ALTER TABLE sys_dict_data ALTER COLUMN dict_value TYPE VARCHAR(2000);

INSERT INTO sys_dict_type (dict_id, dict_name, dict_type, status, create_by, create_time, remark)
SELECT
nextval(pg_get_serial_sequence('sys_dict_type', 'dict_id')),
'数据文件同步配置', 'data_file_sync_config', '0', 'admin', now(), '气象、功率数据文件同步扫描目录配置'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type t WHERE t.dict_type = 'data_file_sync_config');

INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT
nextval(pg_get_serial_sequence('sys_dict_data', 'dict_code')),
1, '文件同步配置', '[]', 'data_file_sync_config', '', '', 'Y', '0', 'admin', now(), 'JSON数组, 每项含 scanFolder/updateInterval/handler, type可选'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data t WHERE t.dict_type = 'data_file_sync_config');