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),
    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);