zsjz-ai/sql/case_table_1.sql

-- CREATE SEQUENCE global_seq START 1000;
-- CREATE SEQUENCE time_series_id_seq START 1;
-- CREATE SEQUENCE use_call_seq START 1;
-- CREATE SEQUENCE call_seq START 1;

DROP TABLE IF EXISTS file_info;
CREATE TABLE file_info
(
    id             BIGINT PRIMARY KEY,
    pid            BIGINT              NOT NULL,
    fileName       VARCHAR,
    sheetName      VARCHAR,
    batchId        BIGINT,
    templateId     BIGINT,
    tableName      VARCHAR,
    lineNo         INTEGER,
    header         VARCHAR,
    password       VARCHAR,
    charSet        VARCHAR,
    delimiter      VARCHAR,
    fileType       VARCHAR,
    filePath       VARCHAR,
    fileSize       BIGINT    DEFAULT 0 NOT NULL,
    dataNum        INTEGER   DEFAULT 0 NOT NULL,
    successNum     INTEGER   DEFAULT 0 NOT NULL,
    failNum        INTEGER   DEFAULT 0 NOT NULL,
    fileStatus     VARCHAR,
    md5            VARCHAR,
    createDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 非表格文件（txt / doc / docx / pdf / ppt / 压缩包 …）的 AI 识别档案。
-- 这些文件无法解析成表格数据，file_info 里只留一条 FILE_UNSUPPORTED_FAIL 记录；
-- 本表存 Tika 抽取的文本样本 + 大模型识别出的分类/摘要/关键词，供后续数据分析工具使用。
-- 注意：业务代码（CaseDataSourceRegistry#ensureAiProfileTable）会用 CREATE TABLE IF NOT EXISTS
-- 做幂等补建、用 AI_PROFILE_MIGRATIONS 给老库补列，老案件库开案时自动补上，无需手工迁移。
-- 改这里的列时，必须同步改 CaseDataSourceRegistry#AI_PROFILE_DDL / #AI_PROFILE_MIGRATIONS
-- 与实体 FileAiProfile（FileAiProfileSchemaTest 会比对三者的列名）。
DROP TABLE IF EXISTS file_ai_profile;
CREATE TABLE file_ai_profile
(
    id             BIGINT PRIMARY KEY,
    caseId         BIGINT,
    fileId         BIGINT,
    batchId        BIGINT,
    fileName       VARCHAR,
    fileType       VARCHAR,
    mimeType       VARCHAR,
    category       VARCHAR,
    summary        VARCHAR,
    keywords       VARCHAR,
    sampleText     VARCHAR,
    sampleLength   INTEGER,
    parseStatus    VARCHAR,
    failReason     VARCHAR,
    modelName      VARCHAR,
    costMillis     BIGINT,
    createDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS person_basic_info;
create table person_basic_info
(
    personName          VARCHAR PRIMARY KEY,
    personCertNo        VARCHAR,
    personBirth         DATE,
    personGender        VARCHAR,
    personAge           INTEGER,
    personAddress       VARCHAR,
    photograph          VARCHAR,
    marry               INTEGER,
    nation              VARCHAR,
    nativePlace         VARCHAR,
    householdRegistered VARCHAR,
    orderSort           VARCHAR,
    personTag           VARCHAR,
    sysLabel            VARCHAR
);

DROP TABLE IF EXISTS person_group;
create table person_group
(
    id        BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
    groupName VARCHAR,
    remark    VARCHAR
);

DROP TABLE IF EXISTS person_group_rel;
create table person_group_rel
(
    id         BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
    groupId    BIGINT,
    personName VARCHAR,
    hasMain    VARCHAR,
    mainDesc   VARCHAR
);

DROP TABLE IF EXISTS person_lib_no;
create table person_lib_no
(
    md5        VARCHAR PRIMARY KEY,
    personName VARCHAR,
    personNo   VARCHAR,
    personNoCn VARCHAR,
    libType    INTEGER,
    startTime  TIMESTAMP,
    endTime    TIMESTAMP,
    hasAuto    INTEGER
);

DROP TABLE IF EXISTS rel_edge;
create table rel_edge
(
    id      BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
    node1   VARCHAR,
    node2   VARCHAR,
    node1cn VARCHAR,
    node2cn VARCHAR,
    mc      VARCHAR,
    clazz   VARCHAR,
    num     VARCHAR,
    sj      TIMESTAMP,
    fx      INTEGER,
    lx      INTEGER,
    cs      INTEGER
);

DROP TABLE IF EXISTS rel_node;
create table rel_node
(
    node_id   VARCHAR,
    node_type VARCHAR
);

DROP TABLE IF EXISTS edge_conf;
create table edge_conf
(
    id        BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
    clazz     VARCHAR,
    tableName VARCHAR,
    node1     VARCHAR,
    node2     VARCHAR,
    mc        VARCHAR,
    sj        VARCHAR
);

DROP TABLE IF EXISTS rel_node_conf;
create table rel_node_conf
(
    id        BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
    clazz     VARCHAR,
    tableName VARCHAR,
    node      VARCHAR,
    fd        VARCHAR
);

DROP TABLE IF EXISTS special_date;
create table special_date
(
    batchId   BIGINT,
    name      VARCHAR,
    startDate DATE,
    endDate   DATE
);

DROP TABLE IF EXISTS special_date_reord;
create table special_date_reord
(
    id     BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
    name   VARCHAR,
    flag   INTEGER,
    type   INTEGER,
    gap    INTEGER,
    spDate VARCHAR
);


DROP TABLE IF EXISTS govern_tree;
CREATE TABLE govern_tree
(
    id          BIGINT PRIMARY KEY,
    pid         BIGINT,
    type        VARCHAR,
    tableNameEn VARCHAR,
    tableNameCn VARCHAR,
    dataCount   INTEGER
);

DROP TABLE IF EXISTS govern_conf;
CREATE TABLE govern_conf
(
    id       BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
    confStr  VARCHAR,
    confType VARCHAR
);

DROP TABLE IF EXISTS cash_key;
CREATE TABLE cash_key
(
    id      BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
    cashKey VARCHAR
);

DROP TABLE IF EXISTS trans_financial_key;
CREATE TABLE trans_financial_key
(
    id           BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
    financialKey VARCHAR,
    tag          VARCHAR
);


DROP TABLE IF EXISTS fixed_deposit_key;
CREATE TABLE fixed_deposit_key
(
    id              BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
    fixedDepositKey VARCHAR
);

DROP TABLE IF EXISTS clean_error_log;
CREATE TABLE clean_error_log
(
    id             BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
    fileId         BIGINT,
    batchId        BIGINT,
    sheetId        BIGINT,
    fileName       VARCHAR,
    sheetName      VARCHAR,
    content        VARCHAR,
    createDateTime timestamp
);


DROP TABLE IF EXISTS intimacy;
CREATE TABLE intimacy
(
    id     BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
    scores VARCHAR,
    weight VARCHAR,
    tags   VARCHAR
);

DROP TABLE IF EXISTS graph_his;
CREATE TABLE graph_his
(
    id        BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
    graphName VARCHAR,
    path      VARCHAR
);