zsjz-ai/sql/ai_clean.sql

-- ============================================================================
-- AI 清洗链路（module/aiclean）元数据表
--
-- 落在 **master PG**（与 table_info / table_field 同库），但**一张都不碰模板库**：
-- AI 生成的模板、字段、清洗函数链、任务轨迹全部存这四张表。
-- 之所以不写 table_info：一旦写行并打开 table_field.matched，既有匹配器就会命中该模板，
-- 进而走到 StrategyType.fromCode(main_id) 抛异常（StrategyType.java:82）。
-- 换存储而不是换参数，是这个方案不侵入既有链路的根本保障。
--
-- 设计文档：docs/design/ai-clean-parallel.md
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 1. AI 模板（等价于 table_info 的角色，但完全独立）
--
--    header_md5 唯一键是「零模型重放」的收口：同一张表头签名第二次上传时直接命中，
--    不再调用大模型 —— 长期成本能不能降下来全看这一列。
-- ----------------------------------------------------------------------------
create table if not exists ai_clean_template
(
    id                   bigint primary key,
    table_name_cn        varchar(200)  not null,          -- 中文名，模型语义命名
    table_name_en        varchar(200)  not null,          -- 物理表名 ai_clean_{id}（2026-10-18 起；历史表为 ai_t{id}）
    headers              varchar(4000),                   -- 结构归一后的列头，逗号分隔
    header_md5           varchar(64)   not null,          -- 归一化表头指纹（唯一）
    header_line_no       integer,                         -- 表头所在行（多级表头压平后的那一行）
    func_regex           text,                            -- 表头外提姓名/卡号正则，沿用 table_info.func_regex 的语义
    block_no             integer,                         -- 原 sheet 内第几个表块（单 sheet 多套表头）
    struct_ops           text,                            -- 结构指令快照 JSON（重放时免模型再判一次）
    needs_struct         integer      default 0,          -- 1=必须结构归一才能看懂表头，此类模板永不参与既有匹配
    category             varchar(50),                     -- 业务分类，取值 FileCategoryEnum#name()
    category_l2          varchar(50),
    main_ref_template_id integer,                          -- 判定参考的既有模板 id（溯源用，可空）
    match_tier           varchar(20),                      -- 定档时的档位：STRONG/WEAK/NONE
    status               varchar(20)  default 'DRAFT',     -- DRAFT/ONLINE/OFFLINE；OFFLINE = 从治理树摘除
    ver                  integer      default 1,
    confidence           numeric(6, 4),
    create_by            bigint,
    create_time          timestamp    default current_timestamp,
    update_time          timestamp
);
create unique index if not exists uk_ai_clean_tpl_md5 on ai_clean_template (header_md5);
create index if not exists idx_ai_clean_tpl_status on ai_clean_template (status);
create index if not exists idx_ai_clean_tpl_category on ai_clean_template (category);

-- ----------------------------------------------------------------------------
-- 2. AI 模板字段（等价于 table_field 的角色）
--
--    既是动态表 DDL 的真源（field_type/field_len → DuckDB 类型），
--    也是前端列表头的真源（field_name_cn + field_sort），
--    因此不需要借用 GlobalCache.TABLE_HEAD —— 那套缓存只服务 table_field。
-- ----------------------------------------------------------------------------
create table if not exists ai_clean_field
(
    id             bigint primary key,
    ai_template_id bigint      not null,
    column_name    varchar(5)  not null,                  -- 动态表里的实际列名，固定 c0/c1/…（长度 ≤5）
    field_name_cn  varchar(200),                          -- 沿用文件原列头，前端展示用
    field_name_en  varchar(5),                            -- 与 column_name 完全一致（2026-10-18 需求）
    field_type     varchar(50),                           -- FieldTypeEnum#name()
    required       integer   default 0,
    field_len      integer   default 0,
    field_sort     integer   default 0,
    direction_conf text,                                   -- 借贷方向字典 JSON，沿用既有格式
    col_index      integer,                                -- 原文件里的列序号（重放时定位用）
    matched        integer   default 1,                    -- AI 内部证据位：0=两采样不一致，不作为打分证据
    create_time    timestamp default current_timestamp
);
create index if not exists idx_ai_clean_field_tpl on ai_clean_field (ai_template_id);

-- ----------------------------------------------------------------------------
-- 3. AI 清洗规则（函数链快照）
--
--    只存**扁平枚举 hint**，绝不存带 @class 的多态 JSON：
--    TableRuleDTO/Fun 用的是 Jackson @JsonTypeInfo(use=CLASS)，让模型输出它等于
--    开放「任意类名反序列化」攻击面（Fun.java:10）。加载时由 AiRuleCompiler
--    在内存里把 hint 还原成 Fun 对象，再交给 FunProcess 执行。
-- ----------------------------------------------------------------------------
create table if not exists ai_clean_rule
(
    id             bigint primary key,
    ai_template_id bigint,                                -- 出口 B 挂模板；出口 A 的快照可空（挂 job）
    col_index      integer    not null,
    field_name_en  varchar(200),
    field_name_cn  varchar(200),                          -- 列头中文名；表头为空/没有中文名时为 null
    hints          text,                                   -- 枚举 hint 的 JSON 数组，如 ["TRIM","UNIT_SCALE:WAN"]
    ref_template_id integer,                              -- 出口 A：命中的既有 table_info.id
    ver            integer   default 1,
    create_time    timestamp default current_timestamp
);
create index if not exists idx_ai_clean_rule_tpl on ai_clean_rule (ai_template_id);

-- ----------------------------------------------------------------------------
-- 4. AI 清洗任务（幂等游标 + 状态线 + 成本埋点）
--
--    唯一键 (case_id,file_id,sheet_id,block_no,ver) 就是「同一块只被处理一次」的保证：
--    轮询器靠它决定要不要接管，重跑靠它做 UPSERT，不会累积重复行。
--    状态线独立于 FileSheetStateEnum —— 与 AiParseStatusEnum 同一套设计理由。
-- ----------------------------------------------------------------------------
create table if not exists ai_clean_job
(
    id              bigint primary key,
    case_id         bigint    not null,
    file_id         bigint    not null,
    sheet_id        bigint    not null,
    block_no        integer   default 0,
    ver             integer   default 1,
    batch_id        bigint,
    file_name       varchar(500),
    sheet_name      varchar(200),
    status          varchar(30)  not null,                -- AiCleanStatusEnum#name()
    exit_code       varchar(10),                          -- 出口：A/A2/A3/B
    matched_by      varchar(20),                          -- HEADER_CACHE/SCORE/LLM/NONE
    match_tier      varchar(20),                          -- STRONG/WEAK/NONE
    ref_template_id integer,                              -- 出口 A：既有模板 id
    ai_template_id  bigint,                               -- 出口 B：AI 模板 id
    header_md5      varchar(64),
    header_row      integer,
    confidence      numeric(6, 4),
    detail          text,                                  -- VerifyReport JSON（逐列错误率、抽样预览摘要）
    draft           text,                                  -- 模型返回的原始草稿 JSON，便于回溯 prompt 版本
    model_name      varchar(200),
    token_in        integer,
    token_out       integer,
    llm_calls       integer    default 0,                  -- 本 job 调了几次模型（直通应为 0）
    cost_ms         bigint,
    row_total       integer,
    row_loaded      integer,
    row_rejected    integer,                               -- 必填空值/类型错误/函数异常/超长导致的整行拒绝
    row_warned      integer,                               -- 已入库但含非必填错误的行（单元格置空）
    row_unprocessed integer,                               -- 失败任务里未处理到的行
    fail_reason     varchar(1000),
    create_time     timestamp default current_timestamp,
    update_time     timestamp
);
create unique index if not exists uk_ai_clean_job on ai_clean_job (case_id, file_id, sheet_id, block_no, ver);
create index if not exists idx_ai_clean_job_status on ai_clean_job (status);
create index if not exists idx_ai_clean_job_file on ai_clean_job (file_id);

-- ----------------------------------------------------------------------------
-- 5. 智能清洗批次（融合入口的状态机与幂等锚点）
--
--    一个上传批次一行：点「智能」后接口立即返回 batchId，后台按
--    JUDGING → CLEANING_EXISTING → LOADING_AI → DONE/PARTIAL 推进，前端轮询这张表。
--    唯一键 (case_id, batch_id) 让重复点按钮返回同一批次而不是重跑一遍。
-- ----------------------------------------------------------------------------
create table if not exists ai_clean_batch
(
    id                  bigint primary key,
    case_id             bigint      not null,
    batch_id            bigint      not null,
    stage               varchar(20) not null,                 -- AiCleanStageEnum#name()
    file_count          integer   default 0,
    sheet_total         integer   default 0,
    matched_count       integer   default 0,                  -- 原本就匹配上模板的 sheet
    ai_judged_count     integer   default 0,                  -- AI 已判定完成的 sheet
    existing_hit_count  integer   default 0,                  -- 其中判为命中既有模板
    ai_load_count       integer   default 0,                  -- 其中走出口 B 成功入库
    loaded_rows         bigint    default 0,                  -- 出口 B 实际入库行数
    rejected_rows       bigint    default 0,                  -- 出口 B 逐行拒绝行数
    unprocessed_rows    bigint    default 0,                  -- 待人工/跳过/失败任务剩余行
    self_built_count    integer   default 0,                  -- 无模板自建表的 sheet 数（不计入匹配成功）
    need_review_count   integer   default 0,
    failed_count        integer   default 0,
    need_review_detail  text,                                 -- [{sheetId,fileName,sheetName,reason}]
    last_error          varchar(1000),
    create_time         timestamp default current_timestamp,
    update_time         timestamp
);
create unique index if not exists uk_ai_clean_batch on ai_clean_batch (case_id, batch_id);
create index if not exists idx_ai_clean_batch_stage on ai_clean_batch (stage);

-- ----------------------------------------------------------------------------
-- 6. 表头同义词库（评分归一 + 绑定域，2026-10-25）
--
--    星型词组：alias_word → std_word（一个标准词挂任意多别名）。
--    只进 HeaderScorer 评分与 filterBinds 绑定域，绝不进 HeaderFingerprint 指纹 ——
--    同义词一旦折叠指纹，存量模板 header_md5 会随词条配置漂移。
--    约束：同一归一化词全表只允许出现一次（Service 校验，防归一歧义）；不做链式传递。
--    命中类型：EXACT（表内映射）优先；SIMILAR（包含/编辑距离，见 HeaderScorer.matchWord）
--    为代码内模糊规则，不落库。
-- ----------------------------------------------------------------------------
create table if not exists ai_clean_synonym
(
    id          bigint primary key,
    std_word    varchar(100) not null,          -- 标准词（组内规范形，如 交易金额）
    alias_word  varchar(100) not null,          -- 别名（如 发生额）
    remark      varchar(200),
    status      smallint default 1,             -- 1 启用 / 0 停用
    create_by   bigint,
    create_time timestamp default current_timestamp,
    update_time timestamp
);
create unique index if not exists uk_ai_clean_synonym on ai_clean_synonym (std_word, alias_word);

-- 内置词条（与 2026-10-22 起硬编码在 HeaderScorer.SYNONYMS 的 24 条一致，去掉 6 条自映射）
insert into ai_clean_synonym (id, std_word, alias_word, remark) values
    (1,  '户名',     '账户名称', '内置：户名系'),
    (2,  '户名',     '客户名称', '内置：户名系'),
    (3,  '户名',     '姓名',     '内置：户名系'),
    (4,  '交易金额', '发生额',   '内置：金额系'),
    (5,  '交易金额', '交易额',   '内置：金额系'),
    (6,  '交易金额', '金额',     '内置：金额系'),
    (7,  '日期',     '交易日期', '内置：日期系'),
    (8,  '时间',     '交易时间', '内置：时间系'),
    (9,  '账号',     '卡号',     '内置：账号系'),
    (10, '账号',     '银行账号', '内置：账号系'),
    (11, '账号',     '账户',     '内置：账号系'),
    (12, '电话',     '手机号',   '内置：电话系'),
    (13, '电话',     '手机',     '内置：电话系'),
    (14, '电话',     '联系电话', '内置：电话系'),
    (15, '身份证号', '身份证',   '内置：证件系'),
    (16, '身份证号', '身份证号码', '内置：证件系'),
    (17, '身份证号', '证件号码', '内置：证件系')
on conflict (std_word, alias_word) do nothing;