-- 1 行政区划码表 GB/T2260
CREATE TABLE IF NOT EXISTS sys_area (
    AREAID VARCHAR(6) NOT NULL,
    AREANAME VARCHAR(300) NOT NULL,
    PARENTID VARCHAR(6) NULL,
    LEVEL TINYINT NOT NULL,
    SORT INT NOT NULL DEFAULT 0,
    SFSC TINYINT NOT NULL DEFAULT 0,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(AREAID)
);

-- 2 全局数据字典
CREATE TABLE IF NOT EXISTS sys_dict (
    DICTID VARCHAR(32) NOT NULL,
    DICT_TYPE VARCHAR(50) NOT NULL,
    DICT_LABEL VARCHAR(300) NOT NULL,
    DICT_VALUE VARCHAR(100) NOT NULL,
    SORT INT NOT NULL DEFAULT 0,
    STATUS TINYINT NOT NULL DEFAULT 1,
    SFSC TINYINT NOT NULL DEFAULT 0,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(DICTID)
);

-- 3 司法行政机关 标准表3
CREATE TABLE IF NOT EXISTS sys_org (
    JGBM VARCHAR(18) NOT NULL,
    JGMC VARCHAR(300) NOT NULL,
    JGJC VARCHAR(60) NULL,
    YWMC VARCHAR(60) NULL,
    JGDM VARCHAR(20) NOT NULL,
    FZR VARCHAR(20) NOT NULL,
    JGLB VARCHAR(4) NOT NULL,
    JGXZJB VARCHAR(2) NOT NULL,
    JGLSCJ VARCHAR(2) NOT NULL,
    SJSFXZZGJG VARCHAR(18) NOT NULL,
    LXDH VARCHAR(40) NULL,
    CZHM VARCHAR(40) NULL,
    DZYX VARCHAR(60) NULL,
    XZQHDM VARCHAR(6) NOT NULL,
    DZ VARCHAR(200) NULL,
    JD DECIMAL(12,4) NULL,
    WD DECIMAL(12,4) NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(JGBM)
);

-- 4 司法工作人员 标准表4【修复NOT DEFAULT → NOT NULL DEFAULT】
CREATE TABLE IF NOT EXISTS sys_staff (
    RYBM VARCHAR(20) NOT NULL,
    XM VARCHAR(50) NOT NULL,
    YWM VARCHAR(50) NULL,
    XB VARCHAR(2) NOT NULL,
    CSRQ DATE NOT NULL,
    SFZH VARCHAR(18) NOT NULL,
    MZ VARCHAR(2) NOT NULL,
    ZZMM VARCHAR(2) NOT NULL,
    HYZK VARCHAR(2) NOT NULL,
    ZP BLOB NULL,
    BYYX VARCHAR(60) NOT NULL,
    XL VARCHAR(2) NOT NULL,
    ZGXW VARCHAR(3) NULL,
    ZY VARCHAR(7) NULL,
    SSJG VARCHAR(18) NOT NULL,
    ZWZJ VARCHAR(60) NOT NULL,
    RYBZ VARCHAR(2) NOT NULL,
    SFPZJC TINYINT NULL,
    PZJCQK VARCHAR(200) NULL,
    CJGZSJ DATE NOT NULL,
    SJHM VARCHAR(20) NULL,
    LXDH VARCHAR(40) NULL,
    DZYX VARCHAR(100) NULL,
    ZZ VARCHAR(200) NULL,
    SFZZ TINYINT NOT NULL DEFAULT 1,
    SFSC TINYINT NOT NULL DEFAULT 0,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(RYBM)
);

-- 5 社会工作者 标准表47
CREATE TABLE IF NOT EXISTS sys_social_worker (
    ID VARCHAR(32) NOT NULL,
    XM VARCHAR(20) NOT NULL,
    XB VARCHAR(2) NOT NULL,
    CSRQ DATE NOT NULL,
    SFZH VARCHAR(18) NOT NULL,
    XL VARCHAR(2) NULL,
    ZGXW VARCHAR(3) NULL,
    ZZMM VARCHAR(2) NULL,
    ZY VARCHAR(7) NULL,
    ZC VARCHAR(2) NULL,
    XCSPD VARCHAR(2) NULL,
    SSJG VARCHAR(18) NOT NULL,
    LXDH VARCHAR(20) NULL,
    SJHM VARCHAR(11) NULL,
    JTZZ VARCHAR(50) NULL,
    HTQ VARCHAR(2) NULL,
    QDHTRQ DATE NULL,
    SFZZ TINYINT NOT NULL DEFAULT 1,
    BZ VARCHAR(500) NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(ID)
);

-- 6 社会志愿者 标准表48
CREATE TABLE IF NOT EXISTS sys_volunteer (
    ID VARCHAR(32) NOT NULL,
    SFJZCY TINYINT NOT NULL,
    XZCYLX VARCHAR(20) NULL,
    XZCYLB VARCHAR(20) NULL,
    XM VARCHAR(20) NOT NULL,
    XB VARCHAR(2) NULL,
    CSRQ DATE NULL,
    SFZH VARCHAR(18) NULL,
    XL VARCHAR(2) NULL,
    ZGXW VARCHAR(3) NULL,
    ZZMM VARCHAR(2) NULL,
    ZY VARCHAR(7) NULL,
    ZC VARCHAR(2) NULL,
    GZDW VARCHAR(100) NULL,
    SSJG VARCHAR(18) NOT NULL,
    LXDH VARCHAR(20) NULL,
    SJHM VARCHAR(11) NULL,
    JTZZ VARCHAR(50) NULL,
    KSSJ DATE NULL,
    JSSJ DATE NULL,
    SFZZ TINYINT NOT NULL DEFAULT 1,
    BZ VARCHAR(500) NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(ID)
);

-- 7 系统操作审计日志
CREATE TABLE IF NOT EXISTS sys_oper_log (
    LOGID VARCHAR(32) NOT NULL,
    RYBM VARCHAR(20) NOT NULL,
    OPER_NAME VARCHAR(20) NOT NULL,
    ORGCODE VARCHAR(18) NOT NULL,
    MODULE VARCHAR(50) NULL,
    OPER_TYPE VARCHAR(30) NULL,
    BUSIID VARCHAR(64) NULL,
    REQ_URL VARCHAR(255) NULL,
    REQ_METHOD VARCHAR(20) NULL,
    REQ_PARAM TEXT NULL,
    CLIENT_IP VARCHAR(50) NULL,
    OPER_DT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    COST BIGINT NULL,
    RESULT TINYINT NULL,
    ERROR_TEXT TEXT NULL,
    PRIMARY KEY(LOGID)
);

-- 8 调查评估主信息 标准表5
CREATE TABLE IF NOT EXISTS survey_main (
    WTBH VARCHAR(20) NOT NULL,
    WTDW VARCHAR(300) NOT NULL,
    WTDCH BLOB NOT NULL,
    BDCPGRDLX VARCHAR(2) NOT NULL,
    BGRXM VARCHAR(20) NOT NULL,
    BGRSFZH VARCHAR(18) NULL,
    BGRXB VARCHAR(2) NOT NULL,
    BGRCSRQ DATE NOT NULL,
    BGRJZDDZ VARCHAR(50) NOT NULL,
    BGRGZDW VARCHAR(50) NULL,
    ZM VARCHAR(50) NULL,
    YPXQ VARCHAR(10) NULL,
    YPXQKSRQ DATE NULL,
    YPXQJSRQ DATE NULL,
    YPXF VARCHAR(2) NULL,
    FJX VARCHAR(20) NULL,
    PJJG VARCHAR(50) NULL,
    PJRQ DATE NULL,
    NSYJZLB VARCHAR(2) NOT NULL,
    DCDWXQJ VARCHAR(18) NOT NULL,
    DCPGYJ TEXT NOT NULL,
    DCYJSHR VARCHAR(20) NOT NULL,
    DCPGYJS BLOB NOT NULL,
    FLOW_STATUS TINYINT NOT NULL,
    OVER_STATUS TINYINT NOT NULL DEFAULT 0,
    LEGAL_LIMIT INT NOT NULL DEFAULT 10,
    LIMIT_START DATETIME NOT NULL,
    LIMIT_END DATETIME NOT NULL,
    PUSH_ID VARCHAR(64) NULL,
    SYNC_STATUS TINYINT NOT NULL DEFAULT 0,
    RECEIVE_DT DATETIME NOT NULL,
    RECEIVE_USER VARCHAR(20) NOT NULL,
    RECEIVE_RYBM VARCHAR(32) NOT NULL,
    ENTRUST_LETTER_NO VARCHAR(50) NOT NULL,
    VICTIM_INFO TEXT NULL,
    OTHER_MATERIAL TEXT NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(WTBH)
);

-- 9 调查走访记录 标准表6
CREATE TABLE IF NOT EXISTS survey_visit (
    ID VARCHAR(32) NOT NULL,
    WTBH VARCHAR(20) NOT NULL,
    BDCRXM VARCHAR(20) NOT NULL,
    YBGRGX VARCHAR(2) NOT NULL,
    DCSX TEXT NULL,
    DCSJ DATETIME NOT NULL,
    DCDD VARCHAR(50) NOT NULL,
    DCDWSFS VARCHAR(18) NULL,
    DCR VARCHAR(50) NOT NULL,
    REC_FILE BLOB NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(ID)
);

-- 10 调查评估9项详情
CREATE TABLE IF NOT EXISTS survey_detail (
    DETAIL_ID VARCHAR(32) NOT NULL,
    WTBH VARCHAR(20) NOT NULL,
    ITEM1_RESIDE TEXT NOT NULL,
    ITEM2_FAMILY TEXT NOT NULL,
    ITEM3_PERFORM TEXT NOT NULL,
    ITEM4_INFLUENCE TEXT NOT NULL,
    ITEM5_COMMUNITY TEXT NOT NULL,
    ITEM6_VICTIM TEXT NULL,
    ITEM7_FORBID TEXT NOT NULL,
    ITEM8_RISK TEXT NOT NULL,
    ITEM9_IMPACT TEXT NOT NULL,
    INVEST_PLACE VARCHAR(200) NOT NULL,
    INVEST_METHOD TINYINT NULL,
    IS_FIELD TINYINT NOT NULL,
    INVEST_TIME DATETIME NOT NULL,
    INVESTOR VARCHAR(20) NOT NULL,
    REMARK TEXT NULL,
    STATUS TINYINT NOT NULL DEFAULT 0,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(DETAIL_ID)
);

-- 11 调查取证笔录音视频附件
CREATE TABLE IF NOT EXISTS survey_record (
    RECORD_ID VARCHAR(32) NOT NULL,
    WTBH VARCHAR(20) NOT NULL,
    RECORD_TYPE TINYINT NULL,
    RECORD_CONTENT TEXT NULL,
    INVESTOR VARCHAR(20) NOT NULL,
    INTERVIEWEE VARCHAR(20) NOT NULL,
    SIGN_STATUS TINYINT NULL,
    REFUSE_REASON TEXT NULL,
    FILE_URL VARCHAR(255) NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(RECORD_ID)
);

-- 12 保证人信息
CREATE TABLE IF NOT EXISTS survey_guarantor (
    GUAR_ID VARCHAR(32) NOT NULL,
    WTBH VARCHAR(20) NOT NULL,
    NAME VARCHAR(20) NOT NULL,
    SFZH VARCHAR(18) NOT NULL,
    PHONE VARCHAR(20) NOT NULL,
    ADDRESS VARCHAR(200) NOT NULL,
    ABILITY TINYINT NULL,
    WILLING TINYINT NULL,
    COMMIT_FILE VARCHAR(255) NOT NULL,
    CHECK_USER VARCHAR(20) NOT NULL,
    CHECK_TIME DATETIME NOT NULL,
    STATUS TINYINT NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(GUAR_ID)
);

-- 13 评估小组主表
CREATE TABLE IF NOT EXISTS survey_group (
    GROUP_ID VARCHAR(32) NOT NULL,
    GROUP_CODE VARCHAR(32) NOT NULL,
    WTBH VARCHAR(20) NOT NULL,
    LEADER_RYBM VARCHAR(32) NOT NULL,
    LEADER_NAME VARCHAR(20) NOT NULL,
    LEADER_ORG VARCHAR(100) NOT NULL,
    LEADER_POST VARCHAR(50) NOT NULL,
    LEADER_PHONE VARCHAR(20) NOT NULL,
    MEMBER_COUNT INT NOT NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CREATE_RYBM VARCHAR(32) NOT NULL,
    CREATE_NAME VARCHAR(20) NOT NULL,
    APPROVE_STATUS TINYINT NOT NULL DEFAULT 0,
    APPROVE_RYBM VARCHAR(32) NULL,
    APPROVE_NAME VARCHAR(20) NULL,
    APPROVE_TIME DATETIME NULL,
    APPROVE_REMARK TEXT NULL,
    GROUP_STATUS TINYINT NOT NULL DEFAULT 0,
    CHANGE_COUNT INT NOT NULL DEFAULT 0,
    REMARK TEXT NULL,
    UPDATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(GROUP_ID)
);

-- 14 评估小组成员
CREATE TABLE IF NOT EXISTS survey_group_member (
    MEMBER_ID VARCHAR(32) NOT NULL,
    GROUP_ID VARCHAR(32) NOT NULL,
    MEMBER_NAME VARCHAR(20) NOT NULL,
    MEMBER_SFZH VARCHAR(18) NULL,
    MEMBER_ORG VARCHAR(100) NOT NULL,
    MEMBER_POST VARCHAR(50) NOT NULL,
    MEMBER_PHONE VARCHAR(20) NOT NULL,
    DUTY TEXT NOT NULL,
    MEMBER_TYPE TINYINT NULL,
    JOIN_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    OPER_RYBM VARCHAR(32) NOT NULL,
    OPER_NAME VARCHAR(20) NOT NULL,
    MEMBER_STATUS TINYINT NOT NULL DEFAULT 0,
    EXIT_TIME DATETIME NULL,
    EXIT_REASON TEXT NULL,
    UPDATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    REMARK TEXT NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(MEMBER_ID)
);

-- 15 评估意见书反馈
CREATE TABLE IF NOT EXISTS survey_feedback (
    FEED_ID VARCHAR(32) NOT NULL,
    WTBH VARCHAR(20) NOT NULL,
    FEED_TIME DATETIME NOT NULL,
    ENTRUST_ORG VARCHAR(100) NOT NULL,
    ENTRUST_ORG_CODE VARCHAR(50) NOT NULL,
    CC_ORG VARCHAR(100) NOT NULL,
    CC_ORG_CODE VARCHAR(50) NOT NULL,
    CC_TIME DATETIME NOT NULL,
    ENTRUST_SIGN DATETIME NULL,
    CC_SIGN DATETIME NULL,
    FEED_TYPE CHAR(1) NULL,
    DELIVER_WAY CHAR(1) NULL,
    SIGN_USER VARCHAR(100) NULL,
    PRIMARY KEY(FEED_ID)
);

-- 16 矫正对象主表 标准表7
CREATE TABLE IF NOT EXISTS jz_object (
    SQJZDXBH VARCHAR(20) NOT NULL,
    JZJGBM VARCHAR(18) NOT NULL,
    SWTSFSBM VARCHAR(18) NULL,
    SFDCPG TINYINT NOT NULL,
    DCPGYJ VARCHAR(50) NULL,
    DCYJCXQK VARCHAR(2) NOT NULL,
    JZLB VARCHAR(2) NOT NULL,
    SFCN TINYINT NOT NULL,
    WCN VARCHAR(2) NULL,
    XM VARCHAR(20) NOT NULL,
    CYM VARCHAR(20) NULL,
    XB VARCHAR(2) NOT NULL,
    MZ VARCHAR(20) NOT NULL,
    GJ VARCHAR(2) NOT NULL,
    HJLX VARCHAR(2) NULL,
    SFZH VARCHAR(18) NULL,
    CSRQ DATE NOT NULL,
    YWGATSFZJ TINYINT NOT NULL,
    GATSFZJLX TINYINT NULL,
    GATSFZJHM VARCHAR(20) NULL,
    YWHZ TINYINT NOT NULL,
    HZHM VARCHAR(20) NULL,
    HZBCZT VARCHAR(2) NULL,
    YWGATXZ TINYINT NOT NULL,
    GATXZHM VARCHAR(20) NULL,
    GATXZBCZT VARCHAR(2) NULL,
    YWTWTXZ TINYINT NOT NULL,
    TWTXZHM VARCHAR(20) NULL,
    TWTXZBCZT VARCHAR(2) NULL,
    YWGAJMWLNDTXZ TINYINT NOT NULL,
    GAJMWLNDTXZ VARCHAR(20) NULL,
    GAJMWLNDTXZBCZT VARCHAR(2) NULL,
    YWTBZ TINYINT NOT NULL,
    TBZHM VARCHAR(20) NULL,
    TBZBCZT VARCHAR(2) NULL,
    ZYJWSTZK VARCHAR(20) NULL,
    ZHJZYY VARCHAR(50) NULL,
    SFYJSB TINYINT NOT NULL,
    JDJG VARCHAR(50) NULL,
    SFYCRB TINYINT NOT NULL,
    JTCRB VARCHAR(100) NULL,
    WHCD VARCHAR(20) NOT NULL,
    HYZK VARCHAR(20) NOT NULL,
    BQZY VARCHAR(50) NOT NULL,
    JYJXQK VARCHAR(20) NOT NULL,
    XZZMM VARCHAR(20) NOT NULL,
    YZZMM VARCHAR(20) NULL,
    YGZDW VARCHAR(100) NULL,
    XGZDW VARCHAR(100) NULL,
    DWLXDH VARCHAR(40) NULL,
    GRLXDH VARCHAR(18) NOT NULL,
    YWJTCYJZYSHGX TINYINT NOT NULL,
    ZP BLOB NOT NULL,
    HJDSFYJZD TINYINT NOT NULL,
    GDJZDSZS VARCHAR(20) NOT NULL,
    GDJZDSZDS VARCHAR(20) NOT NULL,
    GDJZDSZXQ VARCHAR(20) NOT NULL,
    GDJZD VARCHAR(20) NOT NULL,
    GDJZDMX VARCHAR(100) NOT NULL,
    HJSZS VARCHAR(20) NULL,
    HJSZDS VARCHAR(20) NULL,
    HJSZXQ VARCHAR(20) NULL,
    HJSZD VARCHAR(20) NULL,
    HJSZDMX VARCHAR(100) NULL,
    SFSWRY TINYINT NOT NULL,
    SWRYLX VARCHAR(2) NULL,
    SQJZQX VARCHAR(20) NOT NULL,
    SQJZKSRQ DATE NOT NULL,
    SQJJSRQ DATE NULL,
    FZLX VARCHAR(20) NULL,
    ZM VARCHAR(50) NOT NULL,
    YPXF VARCHAR(2) NULL,
    YPXQ VARCHAR(20) NULL,
    YPXQKSRQ DATE NULL,
    YPXQJSRQ DATE NULL,
    GZQX VARCHAR(2) NULL,
    HXKYQX VARCHAR(2) NULL,
    SFSZBF TINYINT NOT NULL,
    FJX VARCHAR(20) NULL,
    SFWD TINYINT NULL,
    SFWS TINYINT NULL,
    SFYSS VARCHAR(20) NULL,
    SFBXGJZL TINYINT NOT NULL,
    JZLNR VARCHAR(500) NULL,
    JZLKSRQ DATE NULL,
    JZLJSRQ DATE NULL,
    SQJZDXJSRQ DATE NOT NULL,
    SQJZDXJSFS VARCHAR(2) NOT NULL,
    BDQK VARCHAR(2) NOT NULL,
    WASBDQKSM VARCHAR(200) NULL,
    SFJLJZXZ TINYINT NOT NULL,
    JZXZRYZCQK VARCHAR(50) NULL,
    SFXXHHC TINYINT NOT NULL,
    XXHHCFS VARCHAR(2) NULL,
    DWHM VARCHAR(50) NULL,
    SFSYDZDW TINYINT NOT NULL,
    SFTG TINYINT NOT NULL,
    JCQK TEXT NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(SQJZDXBH)
);

-- 17 个人简历
CREATE TABLE IF NOT EXISTS jz_resume (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    QS DATE NOT NULL,
    ZR DATE NOT NULL,
    SZDW VARCHAR(50) NOT NULL,
    ZW VARCHAR(20) NOT NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(ID)
);

-- 18 家庭社会关系
CREATE TABLE IF NOT EXISTS jz_relation (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    GX VARCHAR(2) NOT NULL,
    XM VARCHAR(20) NOT NULL,
    SZDW VARCHAR(50) NULL,
    JTZZ VARCHAR(50) NULL,
    LXDH VARCHAR(20) NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(ID)
);

-- 19 法律文书
CREATE TABLE IF NOT EXISTS jz_law_doc (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    SQJZJDJG VARCHAR(2) NOT NULL,
    SQJZJDJGMC VARCHAR(50) NOT NULL,
    ZXTZSWH VARCHAR(100) NOT NULL,
    ZXTZSRQ DATE NOT NULL,
    JFZXRQ DATE NOT NULL,
    YJZFJG VARCHAR(2) NULL,
    YJZFJGMC VARCHAR(50) NULL,
    SFYQK TINYINT NOT NULL,
    SFLF TINYINT NOT NULL,
    QKLJLX VARCHAR(20) NULL,
    ZYFZSS TEXT NOT NULL,
    FILE_BB BLOB NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(ID)
);

-- 20 入矫文书接收台账
CREATE TABLE IF NOT EXISTS receive_document (
    REC_ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    PENALTY_TYPE TINYINT NULL,
    JUDGE_ORG VARCHAR(100) NOT NULL,
    EFFECT_DATE DATE NOT NULL,
    DEADLINE_DATE DATE NOT NULL,
    RECEIVE_DT DATETIME NOT NULL,
    IS_OVERDUE TINYINT NOT NULL DEFAULT 0,
    OVERDUE_REASON TEXT NULL,
    SOURCE_TYPE TINYINT NULL,
    TRANSFER_ORG VARCHAR(100) NULL,
    TRANSFER_USER VARCHAR(20) NULL,
    RECEIVE_USER VARCHAR(20) NOT NULL,
    RECEIVE_DEPT VARCHAR(50) NOT NULL,
    DOC_LIST TEXT NOT NULL,
    DOC_FILES TEXT NOT NULL,
    ENFORCE_ADDR VARCHAR(200) NOT NULL,
    TERM_DESC VARCHAR(100) NOT NULL,
    STATUS TINYINT NOT NULL DEFAULT 0,
    CASE_CODE VARCHAR(32) NOT NULL,
    DELIVER_STATUS TINYINT NOT NULL DEFAULT 0,
    JUDGE_ORG_CODE VARCHAR(50) NOT NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(REC_ID)
);

-- 21 入矫宣告
CREATE TABLE IF NOT EXISTS jz_declare (
    DEC_ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    DEC_TIME DATETIME NOT NULL,
    DEC_ADDR VARCHAR(200) NOT NULL,
    HOST_USER VARCHAR(20) NOT NULL,
    ATTEND TEXT NULL,
    DEC_CONTENT TEXT NOT NULL,
    PHOTO_FILES TEXT NULL,
    VIDEO_FILE VARCHAR(255) NULL,
    OBJ_SIGN TINYINT NULL,
    REFUSE_REASON TEXT NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(DEC_ID)
);

-- 22 矫正方案
CREATE TABLE IF NOT EXISTS jz_plan (
    PLAN_ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    PLAN_CODE VARCHAR(32) NOT NULL,
    RISK_LEVEL TINYINT NULL,
    MANAGE_TYPE VARCHAR(50) NOT NULL,
    SUPERVISE_MEAS TEXT NOT NULL,
    EDU_MEAS TEXT NOT NULL,
    HELP_MEAS TEXT NULL,
    PLAN_START DATE NOT NULL,
    PLAN_END DATE NOT NULL,
    MAKER VARCHAR(20) NOT NULL,
    AUDIT_USER VARCHAR(20) NULL,
    AUDIT_TIME DATETIME NULL,
    AUDIT_REMARK TEXT NULL,
    STATUS TINYINT NOT NULL DEFAULT 0,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(PLAN_ID)
);

-- 23 矫正执行档案
CREATE TABLE IF NOT EXISTS jz_execute (
    ZXID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    YPXF VARCHAR(50) NOT NULL,
    YPXQ VARCHAR(20) NOT NULL,
    XQQZRQ VARCHAR(50) NOT NULL,
    JZLB CHAR(2) NOT NULL,
    JZQXQZ VARCHAR(50) NOT NULL,
    RJDJRQ DATE NOT NULL,
    RJZXJG VARCHAR(50) NOT NULL,
    JJRQ DATE NULL,
    JJZXJG VARCHAR(50) NULL,
    JZXZZZ VARCHAR(20) NOT NULL,
    JZXZCY VARCHAR(200) NOT NULL,
    LJBY INT NOT NULL DEFAULT 0,
    LJJG INT NOT NULL DEFAULT 0,
    LJXJ INT NOT NULL DEFAULT 0,
    SJZX TEXT NULL,
    JQ TEXT NULL,
    ZXZT CHAR(2) NULL,
    LRR VARCHAR(20) NOT NULL,
    LRSJ DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    XGR VARCHAR(20) NULL,
    XGSJ DATETIME NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(ZXID)
);

-- 24 矫正小组主表
CREATE TABLE IF NOT EXISTS jz_correct_group (
    GROUP_ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    GROUP_NAME VARCHAR(50) NOT NULL,
    BUILD_DATE DATE NOT NULL,
    LEADER_RYBM VARCHAR(32) NOT NULL,
    LEADER_NAME VARCHAR(20) NOT NULL,
    LEADER_DEPT VARCHAR(100) NOT NULL,
    TOTAL_MEMBER INT NOT NULL,
    IS_FEMALE TINYINT NOT NULL DEFAULT 0,
    SIGN_STATUS TINYINT NULL,
    CREATE_USER VARCHAR(20) NOT NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    GROUP_STATUS TINYINT NOT NULL DEFAULT 0,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(GROUP_ID)
);

-- 25 矫正小组成员
CREATE TABLE IF NOT EXISTS jz_correct_member (
    MEMBER_ID VARCHAR(32) NOT NULL,
    GROUP_ID VARCHAR(32) NOT NULL,
    MEMBER_NAME VARCHAR(20) NOT NULL,
    MEMBER_TYPE TINYINT NULL,
    DEPT VARCHAR(100) NOT NULL,
    POST VARCHAR(50) NOT NULL,
    PHONE VARCHAR(20) NOT NULL,
    DUTY TEXT NOT NULL,
    IS_LEADER TINYINT NOT NULL DEFAULT 0,
    IS_FEMALE TINYINT NOT NULL DEFAULT 0,
    SIGN_TIME DATETIME NULL,
    SIGN_STATUS TINYINT NOT NULL DEFAULT 0,
    EXIT_TIME DATETIME NULL,
    EXIT_REASON TEXT NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(MEMBER_ID)
);

-- 26 矫正小组责任书
CREATE TABLE IF NOT EXISTS jz_group_book (
    BOOK_ID VARCHAR(32) NOT NULL,
    GROUP_ID VARCHAR(32) NOT NULL,
    PRINT_CONTENT TEXT NOT NULL,
    SIGN_FILE VARCHAR(255) NULL,
    ALL_SIGN TINYINT NOT NULL DEFAULT 0,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRINT_COUNT INT NOT NULL DEFAULT 0,
    EXPORT_COUNT INT NOT NULL DEFAULT 0,
    ARCHIVE_STATUS TINYINT NOT NULL DEFAULT 0,
    ARCHIVE_TIME DATETIME NULL,
    ARCHIVE_USER VARCHAR(20) NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(BOOK_ID)
);

-- 27 会客审批
CREATE TABLE IF NOT EXISTS jz_visit_apply (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    HKSJ DATETIME NOT NULL,
    HJRXM VARCHAR(20) NOT NULL,
    HKDD VARCHAR(50) NOT NULL,
    HKYY TEXT NOT NULL,
    SQSJ DATE NOT NULL,
    SFSSHR VARCHAR(20) NULL,
    SFSSHSJ DATETIME NULL,
    SFSSHYJ TEXT NULL,
    XJSQSPR VARCHAR(20) NULL,
    XJSPSJ DATETIME NULL,
    XJSPYJ TEXT NULL,
    XJSPJG VARCHAR(2) NOT NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(ID)
);

-- 28 外出审批
CREATE TABLE IF NOT EXISTS jz_leave_apply (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    SQSJ DATETIME NOT NULL,
    WCMDDSZS VARCHAR(20) NOT NULL,
    WCMDDSZD VARCHAR(20) NOT NULL,
    WCMDDSZX VARCHAR(20) NOT NULL,
    WCMDDXZ VARCHAR(20) NOT NULL,
    WCMDDMX VARCHAR(50) NOT NULL,
    WCLY TEXT NOT NULL,
    WCTS INT NOT NULL,
    KSQR DATE NOT NULL,
    JSRQ DATE NOT NULL,
    SFSSHR VARCHAR(20) NULL,
    SFSSHSJ DATETIME NULL,
    SFSSHYJ TEXT NULL,
    SFSSHJG VARCHAR(2) NULL,
    XJSQSPR VARCHAR(20) NULL,
    XJSPSJ DATETIME NULL,
    XJSPYJ TEXT NULL,
    XJSPJG VARCHAR(2) NULL,
    DSQSHR VARCHAR(20) NULL,
    DSQSJ DATETIME NULL,
    DSQYJ TEXT NULL,
    DSQJG VARCHAR(2) NULL,
    SSQSHR VARCHAR(20) NULL,
    SSQSJ DATETIME NULL,
    SSQYJ TEXT NULL,
    SSQJG VARCHAR(2) NULL,
    XJRQ DATE NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(ID)
);

-- 29 迁居审批
CREATE TABLE IF NOT EXISTS jz_move_house (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    SQSJ DATETIME NOT NULL,
    YJZDXZ VARCHAR(20) NOT NULL,
    YJZDMX VARCHAR(50) NOT NULL,
    QRDXZ VARCHAR(20) NOT NULL,
    QRDMX VARCHAR(50) NOT NULL,
    QRDSWTDSFS VARCHAR(18) NOT NULL,
    BGLY TEXT NOT NULL,
    SFSSHR VARCHAR(20) NULL,
    SFSSHSJ DATETIME NULL,
    SFSSHYJ TEXT NULL,
    XJSQSPR VARCHAR(20) NULL,
    XJSPSJ DATETIME NULL,
    XJSPYJ TEXT NULL,
    XJSPJG VARCHAR(2) NOT NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(ID)
);

-- 30 执行地变更
CREATE TABLE IF NOT EXISTS jz_change_area (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    SQSJ DATETIME NULL,
    QRDSZS VARCHAR(20) NULL,
    QRDSZD VARCHAR(20) NULL,
    QRDSZX VARCHAR(20) NULL,
    QRDXZ VARCHAR(20) NULL,
    QRDMX VARCHAR(50) NULL,
    BGLY TEXT NULL,
    SFSSHR VARCHAR(20) NULL,
    SFSSHSJ DATETIME NULL,
    SFSSHYJ TEXT NULL,
    XJSQSPR VARCHAR(20) NULL,
    XJSPSJ DATETIME NULL,
    XJSPYJ TEXT NULL,
    XJSPJG VARCHAR(2) NULL,
    XZXDXJSQJZJGMC VARCHAR(50) NULL,
    XZXDXJSPYJ TEXT NULL,
    XZXDXJSPJG VARCHAR(2) NULL,
    XZXDXJSPSJ DATETIME NULL,
    SFSC TINYINT NULL,
    PRIMARY KEY(ID)
);

-- 31 奖惩审批（去重SFSSHSJ）
CREATE TABLE IF NOT EXISTS jz_reward_punish (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    JCLB VARCHAR(2) NOT NULL,
    JCSJ DATETIME NOT NULL,
    JCYY TEXT NOT NULL,
    EVIDENCE BLOB NULL,
    SFSSQR VARCHAR(20) NULL,
    SFSSQSJ DATETIME NULL,
    SFSSHR VARCHAR(20) NULL,
    SFSSHSJ DATETIME NULL,
    SFSSHYJ TEXT NULL,
    XJSQSHR VARCHAR(20) NULL,
    XJSPSJ DATETIME NULL,
    XJSPYJ TEXT NULL,
    XJSPJG VARCHAR(2) NULL,
    DSQSHR VARCHAR(20) NULL,
    DSQSJ DATETIME NULL,
    DSQY TEXT NULL,
    FILE_BB BLOB NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(ID)
);

-- 32 电子定位审批
CREATE TABLE IF NOT EXISTS jz_loc_apply (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    JGLY TEXT NOT NULL,
    SFSSHR VARCHAR(20) NULL,
    SFSSHSJ DATETIME NULL,
    SFSSHYJ TEXT NULL,
    XJSQSPR VARCHAR(20) NULL,
    XJSPSJ DATETIME NULL,
    XJSPYJ TEXT NULL,
    XJSPJG VARCHAR(2) NULL,
    XSFXZBMFZR VARCHAR(20) NOT NULL,
    XSFSPJSJ DATETIME NOT NULL,
    XSFSPYJ TEXT NOT NULL,
    DEV_CODE VARCHAR(50) NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(ID)
);

-- 33 日常报到
CREATE TABLE IF NOT EXISTS jz_report (
    REPORT_ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    REPORT_TYPE TINYINT NULL,
    REPORT_TIME DATETIME NOT NULL,
    REPORT_ADDR VARCHAR(200) NULL,
    VERIFY_TYPE TINYINT NULL,
    VERIFY_RESULT TINYINT NULL,
    REMARK TEXT NULL,
    OPERATOR VARCHAR(20) NOT NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(REPORT_ID)
);

-- 34 定位设备
CREATE TABLE IF NOT EXISTS dev_location (
    ID VARCHAR(32) NOT NULL,
    SBBM VARCHAR(32) NOT NULL,
    SBXH VARCHAR(50) NULL,
    SIM VARCHAR(30) NULL,
    SSJG VARCHAR(18) NOT NULL,
    SQJZDXBH VARCHAR(20) NULL,
    BIND_DT DATETIME NULL,
    UNBIND_DT DATETIME NULL,
    STATUS TINYINT NOT NULL,
    SFSC TINYINT NULL,
    PRIMARY KEY(ID)
);

-- 35 定位轨迹
CREATE TABLE IF NOT EXISTS dw_track (
    TRACK_ID VARCHAR(32) NOT NULL,
    DEV_ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    LNG DECIMAL(12,6) NOT NULL,
    LAT DECIMAL(12,6) NOT NULL,
    ADDR VARCHAR(200) NULL,
    COLLECT_DT DATETIME NOT NULL,
    POWER INT NULL,
    SIGNAL INT NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(TRACK_ID)
);

-- 36 电子围栏
CREATE TABLE IF NOT EXISTS dw_fence (
    FENCE_ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    FENCE_NAME VARCHAR(100) NOT NULL,
    FENCE_TYPE TINYINT NULL,
    GEO_DATA TEXT NOT NULL,
    VALID_START DATETIME NOT NULL,
    VALID_END DATETIME NULL,
    STATUS TINYINT NOT NULL DEFAULT 1,
    CREATE_RYBM VARCHAR(32) NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(FENCE_ID)
);

-- 37 报警记录
CREATE TABLE IF NOT EXISTS dw_alarm (
    ALARM_ID VARCHAR(32) NOT NULL,
    DEV_ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    ALARM_TYPE TINYINT NULL,
    ALARM_LEVEL TINYINT NULL,
    ALARM_DT DATETIME NOT NULL,
    LNG DECIMAL(12,6) NULL,
    LAT DECIMAL(12,6) NULL,
    ADDR VARCHAR(200) NULL,
    DEAL_RYBM VARCHAR(32) NULL,
    DEAL_DT DATETIME NULL,
    DEAL_RESULT TEXT NULL,
    DEAL_STATUS TINYINT NOT NULL DEFAULT 0,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(ALARM_ID)
);

-- 38 集中教育活动
CREATE TABLE IF NOT EXISTS edu_activity (
    ACT_ID VARCHAR(32) NOT NULL,
    JGBM VARCHAR(18) NOT NULL,
    TITLE VARCHAR(200) NOT NULL,
    EDU_TYPE TINYINT NULL,
    ACT_DT DATETIME NOT NULL,
    ADDR VARCHAR(200) NOT NULL,
    LECTURER VARCHAR(50) NULL,
    CONTENT TEXT NOT NULL,
    FILE_LIST TEXT NULL,
    CREATE_RYBM VARCHAR(32) NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(ACT_ID)
);

-- 39 签到
CREATE TABLE IF NOT EXISTS edu_sign (
    SIGN_ID VARCHAR(32) NOT NULL,
    ACT_ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    SIGN_DT DATETIME NOT NULL,
    SIGN_WAY TINYINT NULL,
    REMARK TEXT NULL,
    PRIMARY KEY(SIGN_ID)
);

-- 40 线上课程
CREATE TABLE IF NOT EXISTS edu_online (
    COURSE_ID VARCHAR(32) NOT NULL,
    COURSE_NAME VARCHAR(200) NOT NULL,
    COURSE_TYPE TINYINT NULL,
    DURATION INT NULL,
    VIDEO_URL VARCHAR(255) NULL,
    CONTENT TEXT NULL,
    STATUS TINYINT NOT NULL DEFAULT 1,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(COURSE_ID)
);

-- 41 线上学习记录（修复END_DT DATETIME NULL）
CREATE TABLE IF NOT EXISTS edu_study (
    STUDY_ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    COURSE_ID VARCHAR(32) NOT NULL,
    START_DT DATETIME NOT NULL,
    END_DT DATETIME NULL,
    STUDY_DUR INT NOT NULL DEFAULT 0,
    FINISH TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(STUDY_ID)
);

-- 42 公益活动
CREATE TABLE IF NOT EXISTS jz_service_record (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    SQFWKSSJ DATETIME NULL,
    SQFWJSSJ DATETIME NULL,
    SQFWSC INT NULL,
    SQFWDD VARCHAR(50) NULL,
    SQFWNR TEXT NULL,
    SQFWBX TEXT NULL,
    JLR VARCHAR(20) NULL,
    JLSJ DATETIME NULL,
    BZ VARCHAR(500) NULL,
    SFSC TINYINT NULL,
    PRIMARY KEY(ID)
);

-- 43 心理辅导
CREATE TABLE IF NOT EXISTS jz_psych_record (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    XLFDKSSJ DATETIME NULL,
    XLFDJSSJ DATETIME NULL,
    XLFDSC INT NULL,
    XLFDFS VARCHAR(50) NULL,
    XLFDNR TEXT NULL,
    XLFDQKFX TEXT NULL,
    JLR VARCHAR(20) NULL,
    JLSJ DATETIME NULL,
    SFSC TINYINT NULL,
    PRIMARY KEY(ID)
);

-- 44 帮扶记录
CREATE TABLE IF NOT EXISTS jz_help_record (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    BFSQLX VARCHAR(20) NULL,
    BFSQLY TEXT NULL,
    BFSJ DATETIME NULL,
    BFDD VARCHAR(50) NULL,
    BFNR TEXT NULL,
    JLR VARCHAR(20) NULL,
    JLSJ DATETIME NULL,
    FJ BLOB NULL,
    BZ VARCHAR(500) NULL,
    SFSC TINYINT NULL,
    PRIMARY KEY(ID)
);

-- 45 期满解矫
CREATE TABLE IF NOT EXISTS jz_finish (
    FIN_ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    FIN_DATE DATE NOT NULL,
    FIN_TYPE TINYINT NULL,
    FIN_CONTENT TEXT NOT NULL,
    CERT_FILE VARCHAR(255) NULL,
    DECLARE_STATUS TINYINT NULL,
    OPERATOR VARCHAR(32) NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(FIN_ID)
);

-- 46 脱管漏管（修复FIND_DATE DATE NULL）
CREATE TABLE IF NOT EXISTS jz_absent (
    ABS_ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    ABS_TYPE TINYINT NULL,
    START_DATE DATE NOT NULL,
    FIND_DATE DATE NULL,
    REASON TEXT NOT NULL,
    DISPOSE TEXT NOT NULL,
    POLICE_NOTICE TINYINT NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(ABS_ID)
);

-- 47 收监执行
CREATE TABLE IF NOT EXISTS jz_prison (
    PRI_ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    PRI_TYPE TINYINT NULL,
    COURT_NO VARCHAR(50) NOT NULL,
    PRI_DATE DATE NOT NULL,
    REASON TEXT NOT NULL,
    FILE_URL VARCHAR(255) NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(PRI_ID)
);

-- 48 人脸特征
CREATE TABLE IF NOT EXISTS bio_face (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    SWLY VARCHAR(20) NULL,
    SWTZZ BLOB NOT NULL,
    CJSJ DATETIME NOT NULL,
    BZ VARCHAR(500) NULL,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(ID)
);

-- 49 声纹
CREATE TABLE IF NOT EXISTS bio_voice (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    SWTZZ BLOB NOT NULL,
    CJSJ DATETIME NULL,
    BZ VARCHAR(500) NULL,
    SFSC TINYINT NULL,
    PRIMARY KEY(ID)
);

-- 50 指纹
CREATE TABLE IF NOT EXISTS bio_finger (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    ZWTZZ1 BLOB NULL,
    ZWTZZ2 BLOB NULL,
    ZWTZZ3 BLOB NULL,
    ZWTZZ4 BLOB NULL,
    ZWTZZ5 BLOB NULL,
    ZWTZZ6 BLOB NULL,
    CJSJ DATETIME NULL,
    SFSC TINYINT NULL,
    PRIMARY KEY(ID)
);

-- 51 矫务通
CREATE TABLE IF NOT EXISTS dev_jiaowutong (
    ID VARCHAR(32) NOT NULL,
    RYBM VARCHAR(20) NOT NULL,
    IMEIHM VARCHAR(40) NULL,
    SJHM VARCHAR(11) NOT NULL,
    SBXH VARCHAR(20) NULL,
    SBBH VARCHAR(20) NULL,
    CZXTBB VARCHAR(50) NULL,
    STATUS TINYINT NULL,
    PRIMARY KEY(ID)
);

-- 52 在矫通
CREATE TABLE IF NOT EXISTS dev_zaijiaotong (
    ID VARCHAR(32) NOT NULL,
    SQJZDXBH VARCHAR(20) NOT NULL,
    IMEIHM VARCHAR(40) NULL,
    SJHM VARCHAR(11) NOT NULL,
    SBXH VARCHAR(20) NULL,
    CZXTBB VARCHAR(50) NULL,
    STATUS TINYINT NOT NULL DEFAULT 1,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SFSC TINYINT NULL,
    PRIMARY KEY(ID)
);

-- 53 APP消息
CREATE TABLE IF NOT EXISTS app_msg (
    MSG_ID VARCHAR(32) NOT NULL,
    TARGET_TYPE TINYINT NULL,
    TARGET_ID VARCHAR(32) NOT NULL,
    TITLE VARCHAR(100) NOT NULL,
    CONTENT TEXT NOT NULL,
    MSG_TYPE TINYINT NULL,
    READ TINYINT NOT NULL DEFAULT 0,
    SEND_DT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(MSG_ID)
);

-- 54 统计日报
CREATE TABLE IF NOT EXISTS stat_day (
    STAT_ID VARCHAR(32) NOT NULL,
    STAT_DATE DATE NOT NULL,
    JGBM VARCHAR(18) NOT NULL,
    TOTAL_COL INT NULL,
    NEW_IN INT NULL,
    FINISH INT NULL,
    ALARM_NUM INT NULL,
    PRAISE INT NULL,
    WARN INT NULL,
    ABSENT INT NULL,
    ONLINE_EDU INT NULL,
    SERVICE_HOUR INT NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(STAT_ID)
);

-- 55 一网通办
CREATE TABLE IF NOT EXISTS work_net (
    NET_ID VARCHAR(32) NOT NULL,
    BUSI_TYPE VARCHAR(50) NOT NULL,
    APPLY_NAME VARCHAR(20) NOT NULL,
    APPLY_SFZH VARCHAR(18) NOT NULL,
    APPLY_DT DATETIME NULL,
    JGBM VARCHAR(18) NULL,
    BUSI_DATA TEXT NULL,
    AUDIT_STATUS TINYINT NULL,
    RESULT_FILE VARCHAR(255) NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(NET_ID)
);

-- 56 协同日志
CREATE TABLE IF NOT EXISTS sync_business_log (
    ID VARCHAR(32) NOT NULL,
    BUSI_TYPE VARCHAR(50) NOT NULL,
    TARGET_DEPT TINYINT NOT NULL,
    SEND_DATA TEXT NULL,
    RECV_DATA TEXT NULL,
    SEND_DT DATETIME NOT NULL,
    RESULT TINYINT NOT NULL,
    ERROR_MSG TEXT NULL,
    PRIMARY KEY(ID)
);

-- 57 公告
CREATE TABLE IF NOT EXISTS public_notice (
    NOTICE_ID VARCHAR(32) NOT NULL,
    TITLE VARCHAR(200) NOT NULL,
    CONTENT TEXT NOT NULL,
    PUBLISH_ORG VARCHAR(100) NULL,
    PUBLISH_DT DATETIME NULL,
    VALID_START DATE NULL,
    VALID_END DATE NULL,
    STATUS TINYINT NOT NULL DEFAULT 1,
    CREATE_RYBM VARCHAR(32) NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SFSC TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(NOTICE_ID)
);

-- 58 视频设备
CREATE TABLE IF NOT EXISTS video_dev (
    VID VARCHAR(32) NOT NULL,
    DEV_NAME VARCHAR(100) NOT NULL,
    DEV_IP VARCHAR(50) NOT NULL,
    CHANNEL INT NOT NULL,
    JGBM VARCHAR(18) NULL,
    LOCATION VARCHAR(200) NULL,
    DEV_STATUS TINYINT NULL,
    CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(VID)
);

-- 59 督察记录
CREATE TABLE IF NOT EXISTS supervise_record (
    SUP_ID VARCHAR(32) NOT NULL,
    JGBM VARCHAR(32) NOT NULL,
    SUP_DT DATETIME NOT NULL,
    SUP_TYPE TINYINT NULL,
    CONTENT TEXT NOT NULL,
    PROBLEM TEXT NULL,
    RECTIFY_REQ TEXT NULL,
    RECTIFY_DATE DATE NULL,
    CREATE_RYBM VARCHAR(32) NULL,
    PRIMARY KEY(SUP_ID)
);

COMMENT ON TABLE sys_area IS '行政区划代码表(GB/T2260)';
COMMENT ON COLUMN sys_area.AREAID IS '行政区划编码 C6，县级及以上6位，乡镇扩展';
COMMENT ON COLUMN sys_area.AREANAME IS '行政区名称 C300';
COMMENT ON COLUMN sys_area.PARENTID IS '上级编码 C6';
COMMENT ON COLUMN sys_area.LEVEL IS '层级1省2市3县4乡镇';
COMMENT ON COLUMN sys_area.SORT IS '排序';
COMMENT ON COLUMN sys_area.SFSC IS '逻辑删除TN 0未删1已删';
COMMENT ON COLUMN sys_area.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN sys_area.UPDATE_TIME IS '更新时间';

COMMENT ON TABLE sys_dict IS '标准代码字典表（SF/T0015第11章全部编码）';
COMMENT ON COLUMN sys_dict.DICTID IS '主键C32';
COMMENT ON COLUMN sys_dict.DICT_TYPE IS '字典类型C50';
COMMENT ON COLUMN sys_dict.DICT_LABEL IS '显示名称C300';
COMMENT ON COLUMN sys_dict.DICT_VALUE IS '存储编码C100';
COMMENT ON COLUMN sys_dict.SORT IS '排序号';
COMMENT ON COLUMN sys_dict.STATUS IS '1启用0停用TN';
COMMENT ON COLUMN sys_dict.SFSC IS '逻辑删除';
COMMENT ON COLUMN sys_dict.CREATE_TIME IS '创建时间';

COMMENT ON TABLE sys_org IS '司法行政机关基础信息（标准表3）';
COMMENT ON TABLE sys_staff IS '司法行政工作人员（标准表4）';
COMMENT ON TABLE sys_social_worker IS '社会工作者信息（标准表47）';
COMMENT ON TABLE sys_volunteer IS '社会志愿者信息（标准表48）';
COMMENT ON TABLE sys_oper_log IS '系统操作审计日志';
COMMENT ON TABLE survey_main IS '调查评估主信息（标准表5+需求扩展）';
COMMENT ON TABLE survey_visit IS '调查评估走访记录（标准表6）';
COMMENT ON TABLE survey_detail IS '调查评估九项详情(需求扩展)';
COMMENT ON TABLE survey_record IS '调查取证笔录附件(需求扩展)';
COMMENT ON TABLE survey_guarantor IS '调查评估保证人信息(需求扩展)';
COMMENT ON TABLE survey_group IS '调查评估小组主表(需求扩展)';
COMMENT ON TABLE survey_group_member IS '评估小组成员(需求扩展)';
COMMENT ON TABLE survey_feedback IS '评估意见书反馈台账(需求扩展)';
COMMENT ON TABLE jz_object IS '社区矫正对象基础信息(标准表7)';
COMMENT ON TABLE jz_resume IS '矫正对象个人简历（标准表8）';
COMMENT ON TABLE jz_relation IS '家庭及社会关系（标准表9）';
COMMENT ON TABLE jz_law_doc IS '矫正对象法律文书（标准表10）';
COMMENT ON TABLE receive_document IS '入矫文书接收台账(需求扩展)';
COMMENT ON TABLE jz_declare IS '入矫宣告记录表(需求扩展)';
COMMENT ON TABLE jz_plan IS '个性化矫正方案(标准+需求融合)';
COMMENT ON TABLE jz_execute IS '矫正执行总档案(需求扩展)';
COMMENT ON TABLE jz_correct_group IS '矫正小组主表(需求扩展)';
COMMENT ON TABLE jz_correct_member IS '矫正小组成员(需求扩展)';
COMMENT ON TABLE jz_group_book IS '矫正小组责任书(需求扩展)';
COMMENT ON TABLE jz_visit_apply IS '会客审批信息（标准表19）';
COMMENT ON TABLE jz_leave_apply IS '外出申请审批（标准表21）';
COMMENT ON TABLE jz_move_house IS '迁居审批（标准表22）';
COMMENT ON TABLE jz_change_area IS '执行地变更审批（标准表23）';
COMMENT ON TABLE jz_reward_punish IS '表扬/训诫/警告/收监（标准表41）';
COMMENT ON TABLE jz_loc_apply IS '电子定位装置使用审批（表26）';
COMMENT ON TABLE jz_report IS '日常报到记录(需求扩展)';
COMMENT ON TABLE dev_location IS '电子定位终端台账';
COMMENT ON TABLE dw_track IS '定位实时轨迹(需求扩展)';
COMMENT ON TABLE dw_fence IS '电子围栏(需求扩展)';
COMMENT ON TABLE dw_alarm IS '定位报警台账(需求扩展)';
COMMENT ON TABLE edu_activity IS '集中教育活动(需求扩展)';
COMMENT ON TABLE edu_sign IS '教育活动签到(需求扩展)';
COMMENT ON TABLE edu_online IS '远程教育课程(需求扩展)';
COMMENT ON TABLE edu_study IS '线上学习记录(标准+需求)';
COMMENT ON TABLE jz_service_record IS '公益活动记录（标准表38）';
COMMENT ON TABLE jz_psych_record IS '心理辅导记录（标准表49）';
COMMENT ON TABLE jz_help_record IS '社会适应性帮扶（标准表39）';
COMMENT ON TABLE jz_finish IS '期满解除矫正(标准表44)';
COMMENT ON TABLE jz_absent IS '脱管漏管处置(标准表17)';
COMMENT ON TABLE jz_prison IS '收监执行档案(标准表43)';
COMMENT ON TABLE bio_face IS '人脸生物特征库（表50）';
COMMENT ON TABLE bio_voice IS '声纹特征库（表51）';
COMMENT ON TABLE bio_finger IS '指纹特征库（表52）';
COMMENT ON TABLE dev_jiaowutong IS '矫务通移动终端（标准表56）';
COMMENT ON TABLE dev_zaijiaotong IS '在矫通设备信息（标准表55）';
COMMENT ON TABLE app_msg IS '移动端消息推送(需求扩展)';
COMMENT ON TABLE stat_day IS '矫正每日统计报表(需求扩展)';
COMMENT ON TABLE work_net IS '一网通办工单(需求扩展)';
COMMENT ON TABLE sync_business_log IS '政法协同交换日志(标准9.2)';
COMMENT ON TABLE public_notice IS '矫务公开公告(需求扩展)';
COMMENT ON TABLE video_dev IS '司法所视频设备(需求扩展)';
COMMENT ON TABLE supervise_record IS '执法督察台账(需求扩展)';


-- 基础表索引
CREATE INDEX IF NOT EXISTS idx_parent ON sys_area(PARENTID, SFSC);
CREATE INDEX IF NOT EXISTS idx_type ON sys_dict(DICT_TYPE, SFSC);
CREATE INDEX IF NOT EXISTS idx_parent ON sys_org(SJSFXZZGJG, SFSC);
CREATE INDEX IF NOT EXISTS idx_area ON sys_org(XZQHDM);
CREATE INDEX IF NOT EXISTS idx_org ON sys_staff(SSJG, SFSC);
CREATE UNIQUE INDEX IF NOT EXISTS uk_idcard ON sys_staff(SFZH, SFSC);
CREATE INDEX IF NOT EXISTS idx_org ON sys_social_worker(SSJG);
CREATE INDEX IF NOT EXISTS idx_vol_org ON sys_volunteer(SSJG);
CREATE INDEX IF NOT EXISTS idx_user ON sys_oper_log(RYBM, OPER_DT);

-- 调查评估索引
CREATE INDEX IF NOT EXISTS idx_org ON survey_main(DCDWXQJ, SFSC);
CREATE INDEX IF NOT EXISTS idx_name ON survey_main(BGRXM);
CREATE INDEX IF NOT EXISTS idx_flow ON survey_main(FLOW_STATUS, SFSC);
CREATE INDEX IF NOT EXISTS idx_wt ON survey_visit(WTBH, SFSC);
CREATE INDEX IF NOT EXISTS idx_wt ON survey_detail(WTBH, SFSC);
CREATE INDEX IF NOT EXISTS idx_wt ON survey_record(WTBH);
CREATE INDEX IF NOT EXISTS idx_wt ON survey_guarantor(WTBH);
CREATE UNIQUE INDEX IF NOT EXISTS uk_wt ON survey_group(WTBH, SFSC);
CREATE INDEX IF NOT EXISTS idx_leader ON survey_group(LEADER_RYBM);
CREATE INDEX IF NOT EXISTS idx_group ON survey_group_member(GROUP_ID, SFSC);
CREATE INDEX IF NOT EXISTS idx_wt ON survey_feedback(WTBH);

-- 矫正对象核心索引
CREATE UNIQUE INDEX IF NOT EXISTS uk_idcard ON jz_object(SFZH, SFSC);
CREATE INDEX IF NOT EXISTS idx_org ON jz_object(JZJGBM);
CREATE INDEX IF NOT EXISTS idx_jzlb ON jz_object(JZLB, SFSC);
CREATE INDEX IF NOT EXISTS idx_jz ON jz_resume(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jz ON jz_relation(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jz ON jz_law_doc(SQJZDXBH);
CREATE UNIQUE INDEX IF NOT EXISTS uk_case ON receive_document(CASE_CODE, SFSC);
CREATE INDEX IF NOT EXISTS idx_jz ON receive_document(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jz ON jz_declare(SQJZDXBH);
CREATE UNIQUE INDEX IF NOT EXISTS uk_jzplan ON jz_plan(SQJZDXBH, STATUS, SFSC);
CREATE INDEX IF NOT EXISTS idx_risk ON jz_plan(RISK_LEVEL);
CREATE UNIQUE INDEX IF NOT EXISTS uk_jz ON jz_execute(SQJZDXBH, SFSC);
CREATE INDEX IF NOT EXISTS idx_zt ON jz_execute(ZXZT);

-- 矫正小组索引
CREATE UNIQUE INDEX IF NOT EXISTS uk_jz ON jz_correct_group(SQJZDXBH, SFSC);
CREATE INDEX IF NOT EXISTS idx_leader ON jz_correct_group(LEADER_RYBM);
CREATE INDEX IF NOT EXISTS idx_group ON jz_correct_member(GROUP_ID, SFSC);
CREATE INDEX IF NOT EXISTS idx_group ON jz_group_book(GROUP_ID);

-- 审批表索引
CREATE INDEX IF NOT EXISTS idx_jz ON jz_visit_apply(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jz ON jz_leave_apply(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jz ON jz_move_house(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jz ON jz_change_area(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jz ON jz_reward_punish(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jclb ON jz_reward_punish(JCLB);
CREATE INDEX IF NOT EXISTS idx_jz ON jz_loc_apply(SQJZDXBH);

-- 定位监督索引
CREATE INDEX IF NOT EXISTS idx_jz ON jz_report(SQJZDXBH, REPORT_TIME);
CREATE UNIQUE INDEX IF NOT EXISTS uk_devcode ON dev_location(SBBM, SFSC);
CREATE INDEX IF NOT EXISTS idx_jz ON dev_location(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jz ON dw_track(SQJZDXBH, COLLECT_DT);
CREATE INDEX IF NOT EXISTS idx_dev ON dw_track(DEV_ID);
CREATE INDEX IF NOT EXISTS idx_jz ON dw_fence(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jz ON dw_alarm(SQJZDXBH, ALARM_DT);
CREATE INDEX IF NOT EXISTS idx_status ON dw_alarm(DEAL_STATUS);

-- 教育业务索引
CREATE INDEX IF NOT EXISTS idx_org ON edu_activity(JGBM);
CREATE INDEX IF NOT EXISTS idx_act ON edu_sign(ACT_ID);
CREATE INDEX IF NOT EXISTS idx_jz ON edu_sign(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jz ON edu_study(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jz ON jz_service_record(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jz ON jz_psych_record(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jz ON jz_help_record(SQJZDXBH);

-- 解矫收监
CREATE UNIQUE INDEX IF NOT EXISTS uk_jz ON jz_finish(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jz ON jz_absent(SQJZDXBH);
CREATE UNIQUE INDEX IF NOT EXISTS uk_jz ON jz_prison(SQJZDXBH);

-- 生物特征
CREATE INDEX IF NOT EXISTS idx_jz ON bio_face(SQJZDXBH, SFSC);
CREATE INDEX IF NOT EXISTS idx_jz ON bio_voice(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_jz ON bio_finger(SQJZDXBH);

-- APP、统计、协同
CREATE INDEX IF NOT EXISTS idx_jz ON dev_zaijiaotong(SQJZDXBH);
CREATE INDEX IF NOT EXISTS idx_target ON app_msg(TARGET_ID, READ);
CREATE UNIQUE INDEX IF NOT EXISTS uk_orgdate ON stat_day(JGBM, STAT_DATE);
CREATE INDEX IF NOT EXISTS idx_busi ON sync_business_log(BUSI_TYPE);
CREATE INDEX IF NOT EXISTS idx_dt ON sync_business_log(SEND_DT);