-- ============================================================================
-- OARMS - CW-4 线索转办管理 DDL
-- Database: DM8 (达梦)
-- Schema: OARMS
-- Version: V10.0.0
-- Date: 2026-05-18
-- Description: 线索转办记录表 + 转办操作日志表
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 1. cw_clue_transfer_record - 线索转办记录
-- ----------------------------------------------------------------------------
CREATE TABLE OARMS.CW_CLUE_TRANSFER_RECORD (
    id                          VARCHAR(50)    NOT NULL,
    clue_id                     VARCHAR(50)    NOT NULL,
    transfer_target_district    VARCHAR(20)    NOT NULL,
    transfer_target_department  VARCHAR(100)   NOT NULL,
    transfer_target_person      VARCHAR(50),
    transfer_description        VARCHAR(500)   NOT NULL,
    transfer_person             VARCHAR(100)   NOT NULL,
    transferred_at              TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    transfer_status             VARCHAR(20)    NOT NULL DEFAULT 'transferred',
    external_clue_id            VARCHAR(100),
    disposal_result             VARCHAR(2000),
    disposal_completed_at       TIMESTAMP,
    create_by                   VARCHAR(50),
    create_time                 TIMESTAMP,
    create_name                 VARCHAR(50),
    update_by                   VARCHAR(50),
    update_time                 TIMESTAMP,
    update_name                 VARCHAR(50),
    PRIMARY KEY (id)
);

-- 普通索引
CREATE INDEX IDX_CW_TRANS_CLUE ON OARMS.CW_CLUE_TRANSFER_RECORD (CLUE_ID);
CREATE INDEX IDX_CW_TRANS_DIST ON OARMS.CW_CLUE_TRANSFER_RECORD (TRANSFER_TARGET_DISTRICT);
CREATE INDEX IDX_CW_TRANS_STATUS ON OARMS.CW_CLUE_TRANSFER_RECORD (TRANSFER_STATUS);
CREATE INDEX IDX_CW_TRANS_AT ON OARMS.CW_CLUE_TRANSFER_RECORD (TRANSFERRED_AT);

-- 表注释
COMMENT ON TABLE OARMS.CW_CLUE_TRANSFER_RECORD IS '线索转办记录';

-- 列注释
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.ID IS '主键ID';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.CLUE_ID IS '线索ID';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.TRANSFER_TARGET_DISTRICT IS '转办目标区域';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.TRANSFER_TARGET_DEPARTMENT IS '转办目标部门';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.TRANSFER_TARGET_PERSON IS '转办目标联系人';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.TRANSFER_DESCRIPTION IS '转办说明';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.TRANSFER_PERSON IS '转办人';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.TRANSFERRED_AT IS '转办时间';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.TRANSFER_STATUS IS '转办状态(transferred/processing/completed/failed)';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.EXTERNAL_CLUE_ID IS '外部线索ID';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.DISPOSAL_RESULT IS '处置结果';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.DISPOSAL_COMPLETED_AT IS '处置完成时间';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.CREATE_BY IS '创建人ID';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.CREATE_NAME IS '创建人姓名';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.UPDATE_BY IS '更新人ID';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN OARMS.CW_CLUE_TRANSFER_RECORD.UPDATE_NAME IS '更新人姓名';

-- ----------------------------------------------------------------------------
-- 2. cw_transfer_operation_log - 转办操作日志
-- ----------------------------------------------------------------------------
CREATE TABLE OARMS.CW_TRANSFER_OPERATION_LOG (
    id                        VARCHAR(50)    NOT NULL,
    clue_transfer_record_id   VARCHAR(50)    NOT NULL,
    operation_type            VARCHAR(20)    NOT NULL,
    operation_detail          VARCHAR(500)   NOT NULL,
    from_status               VARCHAR(20),
    to_status                 VARCHAR(20),
    operator                  VARCHAR(100)   NOT NULL,
    operated_at               TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- 普通索引
CREATE INDEX IDX_CW_TRANS_LOG_REC ON OARMS.CW_TRANSFER_OPERATION_LOG (CLUE_TRANSFER_RECORD_ID);
CREATE INDEX IDX_CW_TRANS_LOG_TYPE ON OARMS.CW_TRANSFER_OPERATION_LOG (OPERATION_TYPE);
CREATE INDEX IDX_CW_TRANS_LOG_TIME ON OARMS.CW_TRANSFER_OPERATION_LOG (OPERATED_AT);

-- 表注释
COMMENT ON TABLE OARMS.CW_TRANSFER_OPERATION_LOG IS '转办操作日志';

-- 列注释
COMMENT ON COLUMN OARMS.CW_TRANSFER_OPERATION_LOG.ID IS '主键ID';
COMMENT ON COLUMN OARMS.CW_TRANSFER_OPERATION_LOG.CLUE_TRANSFER_RECORD_ID IS '转办记录ID';
COMMENT ON COLUMN OARMS.CW_TRANSFER_OPERATION_LOG.OPERATION_TYPE IS '操作类型(submit/push_success/push_fail/status_sync/result_feedback)';
COMMENT ON COLUMN OARMS.CW_TRANSFER_OPERATION_LOG.OPERATION_DETAIL IS '操作详情';
COMMENT ON COLUMN OARMS.CW_TRANSFER_OPERATION_LOG.FROM_STATUS IS '变更前状态';
COMMENT ON COLUMN OARMS.CW_TRANSFER_OPERATION_LOG.TO_STATUS IS '变更后状态';
COMMENT ON COLUMN OARMS.CW_TRANSFER_OPERATION_LOG.OPERATOR IS '操作人';
COMMENT ON COLUMN OARMS.CW_TRANSFER_OPERATION_LOG.OPERATED_AT IS '操作时间';