-- ============================================================
-- 湛江市智慧人社运营运维（2025-2027年）项目
-- 就业一湛通服务平台 - 任务派发模块 达梦数据库建表脚本
-- 
-- 依据：《需求规格说明书》第3.12节 - 任务派单
--   任务派发信息表（任务派发页面）
--   县区级接收情况表
--   镇区接收情况表
-- 数据库：达梦数据库 (DM8)
-- 创建日期：2025-06-10
-- ============================================================

-- ============================================================
-- 表1：任务派发信息表
-- 说明：实现市→县区→镇的三级任务派发主表，记录任务基本信息
-- ============================================================
CREATE TABLE TASK_DISPATCH (
    ID                              VARCHAR(36)     NOT NULL,
    TASK_NAME                       VARCHAR(200)    NOT NULL,
    PRIORITY                        VARCHAR(10)     NOT NULL,
    ISSUE_TIME                      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    DEADLINE                        DATETIME        NOT NULL,
    TASK_CONTENT                    TEXT            NOT NULL,
    INITIATOR_ID                    VARCHAR(50)     NOT NULL,
    ATTACHMENT                      VARCHAR(500),
    TASK_STATUS                     VARCHAR(20),
    CREATE_BY                       VARCHAR(50),
    CREATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY                       VARCHAR(50),
    UPDATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE                    VARCHAR(50),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE TASK_DISPATCH IS '任务派发信息表';
COMMENT ON COLUMN TASK_DISPATCH.ID IS '主键ID';
COMMENT ON COLUMN TASK_DISPATCH.TASK_NAME IS '任务名称';
COMMENT ON COLUMN TASK_DISPATCH.PRIORITY IS '任务优先级（高/中/低）';
COMMENT ON COLUMN TASK_DISPATCH.ISSUE_TIME IS '下达时间（年月日时分，默认系统当前时间）';
COMMENT ON COLUMN TASK_DISPATCH.DEADLINE IS '截止时间（年月日时分）';
COMMENT ON COLUMN TASK_DISPATCH.TASK_CONTENT IS '任务内容（富文本）';
COMMENT ON COLUMN TASK_DISPATCH.INITIATOR_ID IS '发起人ID';
COMMENT ON COLUMN TASK_DISPATCH.ATTACHMENT IS '附件';
COMMENT ON COLUMN TASK_DISPATCH.TASK_STATUS IS '任务状态（草稿/进行中/已结束）';
COMMENT ON COLUMN TASK_DISPATCH.CREATE_BY IS '创建人';
COMMENT ON COLUMN TASK_DISPATCH.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN TASK_DISPATCH.UPDATE_BY IS '修改人';
COMMENT ON COLUMN TASK_DISPATCH.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN TASK_DISPATCH.SYS_ORG_CODE IS '组织机构编号';

CREATE INDEX IDX_TASK_DISPATCH_STATUS ON TASK_DISPATCH(TASK_STATUS);
CREATE INDEX IDX_TASK_DISPATCH_PRIORITY ON TASK_DISPATCH(PRIORITY);
CREATE INDEX IDX_TASK_DISPATCH_INITIATOR_ID ON TASK_DISPATCH(INITIATOR_ID);


-- ============================================================
-- 表2：县区级接收情况表
-- 说明：记录县区级接收市级下派任务的响应情况，可转发至辖区内镇
-- ============================================================
CREATE TABLE TASK_DISTRICT_RECEIVE (
    ID                              VARCHAR(36)     NOT NULL,
    TASK_ID                         VARCHAR(36)     NOT NULL,
    RECEIVER_ID                     VARCHAR(100)    NOT NULL,
    IS_RECEIVED                     VARCHAR(10)     NOT NULL,
    RECEIVE_TIME                    DATETIME,
    FORWARD_TIME                    DATETIME,
    REPLY_CONTENT                   TEXT            NOT NULL,
    REPLY_TIME                      DATETIME,
    ATTACHMENT                      VARCHAR(500),
    CREATE_BY                       VARCHAR(50),
    CREATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY                       VARCHAR(50),
    UPDATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE                    VARCHAR(50),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE TASK_DISTRICT_RECEIVE IS '县区级接收情况表';
COMMENT ON COLUMN TASK_DISTRICT_RECEIVE.ID IS '主键ID';
COMMENT ON COLUMN TASK_DISTRICT_RECEIVE.TASK_ID IS '关联任务派发信息ID';
COMMENT ON COLUMN TASK_DISTRICT_RECEIVE.RECEIVER_ID IS '接收人';
COMMENT ON COLUMN TASK_DISTRICT_RECEIVE.IS_RECEIVED IS '是否已经接收（是/否）';
COMMENT ON COLUMN TASK_DISTRICT_RECEIVE.RECEIVE_TIME IS '接收时间';
COMMENT ON COLUMN TASK_DISTRICT_RECEIVE.FORWARD_TIME IS '转发下达时间';
COMMENT ON COLUMN TASK_DISTRICT_RECEIVE.REPLY_CONTENT IS '回复内容（富文本）';
COMMENT ON COLUMN TASK_DISTRICT_RECEIVE.REPLY_TIME IS '回复时间';
COMMENT ON COLUMN TASK_DISTRICT_RECEIVE.ATTACHMENT IS '任务附件';
COMMENT ON COLUMN TASK_DISTRICT_RECEIVE.CREATE_BY IS '创建人';
COMMENT ON COLUMN TASK_DISTRICT_RECEIVE.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN TASK_DISTRICT_RECEIVE.UPDATE_BY IS '修改人';
COMMENT ON COLUMN TASK_DISTRICT_RECEIVE.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN TASK_DISTRICT_RECEIVE.SYS_ORG_CODE IS '组织机构编号';

CREATE INDEX IDX_TASK_DISTRICT_RECEIVE_TASK ON TASK_DISTRICT_RECEIVE(TASK_ID);
CREATE INDEX IDX_TASK_DISTRICT_RECEIVE_RECEIVED ON TASK_DISTRICT_RECEIVE(IS_RECEIVED);


-- ============================================================
-- 表3：镇区接收情况表
-- 说明：记录镇区接收市级/县区级下派任务的响应情况
-- ============================================================
CREATE TABLE TASK_TOWN_RECEIVE (
    ID                              VARCHAR(36)     NOT NULL,
    TASK_ID                         VARCHAR(36)     NOT NULL,
    PARENT_ID                       VARCHAR(36),
    RECEIVER_ID                     VARCHAR(100)    NOT NULL,
    IS_RECEIVED                     VARCHAR(10)     NOT NULL,
    RECEIVE_TIME                    DATETIME,
    REPLY_CONTENT                   TEXT            NOT NULL,
    REPLY_TIME                      DATETIME,
    ATTACHMENT                      VARCHAR(500),
    CREATE_BY                       VARCHAR(50),
    CREATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY                       VARCHAR(50),
    UPDATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE                    VARCHAR(50),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE TASK_TOWN_RECEIVE IS '镇区接收情况表';
COMMENT ON COLUMN TASK_TOWN_RECEIVE.ID IS '主键ID';
COMMENT ON COLUMN TASK_TOWN_RECEIVE.TASK_ID IS '关联任务派发信息ID';
COMMENT ON COLUMN TASK_TOWN_RECEIVE.PARENT_ID IS '上级接收记录ID（关联县区级接收情况表ID，市级直派则为空）';
COMMENT ON COLUMN TASK_TOWN_RECEIVE.RECEIVER_ID IS '接收人';
COMMENT ON COLUMN TASK_TOWN_RECEIVE.IS_RECEIVED IS '是否已经接收（是/否）';
COMMENT ON COLUMN TASK_TOWN_RECEIVE.RECEIVE_TIME IS '接收时间';
COMMENT ON COLUMN TASK_TOWN_RECEIVE.REPLY_CONTENT IS '回复内容（富文本）';
COMMENT ON COLUMN TASK_TOWN_RECEIVE.REPLY_TIME IS '回复时间';
COMMENT ON COLUMN TASK_TOWN_RECEIVE.ATTACHMENT IS '任务附件';
COMMENT ON COLUMN TASK_TOWN_RECEIVE.CREATE_BY IS '创建人';
COMMENT ON COLUMN TASK_TOWN_RECEIVE.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN TASK_TOWN_RECEIVE.UPDATE_BY IS '修改人';
COMMENT ON COLUMN TASK_TOWN_RECEIVE.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN TASK_TOWN_RECEIVE.SYS_ORG_CODE IS '组织机构编号';

CREATE INDEX IDX_TASK_TOWN_RECEIVE_TASK ON TASK_TOWN_RECEIVE(TASK_ID);
CREATE INDEX IDX_TASK_TOWN_RECEIVE_PARENT ON TASK_TOWN_RECEIVE(PARENT_ID);
CREATE INDEX IDX_TASK_TOWN_RECEIVE_RECEIVED ON TASK_TOWN_RECEIVE(IS_RECEIVED);


-- ============================================================
-- 表4：任务附件表
-- 说明：统一管理任务派发各环节（任务主表、县区级/镇区级接收）的多附件上传
--       通过 BIZ_TYPE + BIZ_ID 关联不同的业务记录
-- ============================================================
CREATE TABLE TASK_ATTACHMENT (
    ID                              VARCHAR(36)     NOT NULL,
    BIZ_TYPE                        VARCHAR(30)     NOT NULL,
    BIZ_ID                          VARCHAR(36)     NOT NULL,
    FILE_NAME                       VARCHAR(200)    NOT NULL,
    FILE_PATH                       VARCHAR(500)    NOT NULL,
    FILE_SIZE                       INTEGER,
    FILE_TYPE                       VARCHAR(50),
    SORT_ORDER                      INTEGER,
    CREATE_BY                       VARCHAR(50),
    CREATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY                       VARCHAR(50),
    UPDATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE                    VARCHAR(50),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE TASK_ATTACHMENT IS '任务附件表';
COMMENT ON COLUMN TASK_ATTACHMENT.ID IS '主键ID';
COMMENT ON COLUMN TASK_ATTACHMENT.BIZ_TYPE IS '业务类型（DISPATCH-任务派发/DISTRICT_RECEIVE-县区级接收/TOWN_RECEIVE-镇区级接收）';
COMMENT ON COLUMN TASK_ATTACHMENT.BIZ_ID IS '关联业务记录ID（对应TASK_DISPATCH/TASK_DISTRICT_RECEIVE/TASK_TOWN_RECEIVE的主键）';
COMMENT ON COLUMN TASK_ATTACHMENT.FILE_NAME IS '文件名称';
COMMENT ON COLUMN TASK_ATTACHMENT.FILE_PATH IS '文件存储路径';
COMMENT ON COLUMN TASK_ATTACHMENT.FILE_SIZE IS '文件大小（字节）';
COMMENT ON COLUMN TASK_ATTACHMENT.FILE_TYPE IS '文件类型（如pdf/docx/jpg/png等）';
COMMENT ON COLUMN TASK_ATTACHMENT.SORT_ORDER IS '排序序号';
COMMENT ON COLUMN TASK_ATTACHMENT.CREATE_BY IS '创建人';
COMMENT ON COLUMN TASK_ATTACHMENT.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN TASK_ATTACHMENT.UPDATE_BY IS '修改人';
COMMENT ON COLUMN TASK_ATTACHMENT.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN TASK_ATTACHMENT.SYS_ORG_CODE IS '组织机构编号';

CREATE INDEX IDX_TASK_ATTACHMENT_BIZ ON TASK_ATTACHMENT(BIZ_TYPE, BIZ_ID);
CREATE INDEX IDX_TASK_ATTACHMENT_BIZ_ID ON TASK_ATTACHMENT(BIZ_ID);

-- 补增上传人ID字段
ALTER TABLE TASK_ATTACHMENT ADD UPLOAD_BY VARCHAR(36) NULL;
COMMENT ON COLUMN TASK_ATTACHMENT.UPLOAD_BY IS '上传人ID（记录是谁上传的附件）';


-- ============================================================
-- 表5：跨层级协作记录表
-- 说明：记录下级（县区/镇）向上级（市级/县区）发起的协作请求，
--       包括请求内容、处理回复等。原需求：县区级可发起跨层级协作；
--       镇级可反馈任务难点请求支持；市级可查看协作记录。
-- ============================================================
CREATE TABLE TASK_COLLABORATION (
    ID                              VARCHAR(36)     NOT NULL,
    TASK_ID                         VARCHAR(36)     NOT NULL,
    REQUESTER_ID                    VARCHAR(50)     NOT NULL,
    REQUESTER_TYPE                  VARCHAR(20)     NOT NULL,
    REQUEST_CONTENT                 TEXT            NOT NULL,
    TARGET_LEVEL                    VARCHAR(20)     NOT NULL,
    STATUS                          VARCHAR(10)     DEFAULT '0',
    REPLY_CONTENT                   TEXT,
    REPLY_TIME                      DATETIME,
    REPLY_BY                        VARCHAR(50),
    CREATE_BY                       VARCHAR(50),
    CREATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY                       VARCHAR(50),
    UPDATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE                    VARCHAR(50),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE TASK_COLLABORATION IS '跨层级协作记录表';
COMMENT ON COLUMN TASK_COLLABORATION.ID IS '主键ID';
COMMENT ON COLUMN TASK_COLLABORATION.TASK_ID IS '关联任务派发ID';
COMMENT ON COLUMN TASK_COLLABORATION.REQUESTER_ID IS '发起人ID（当前登录用户）';
COMMENT ON COLUMN TASK_COLLABORATION.REQUESTER_TYPE IS '发起方类型（DISTRICT-县区级/TOWN-镇街级）';
COMMENT ON COLUMN TASK_COLLABORATION.REQUEST_CONTENT IS '协作请求内容';
COMMENT ON COLUMN TASK_COLLABORATION.TARGET_LEVEL IS '请求目标层级（CITY-市级/DISTRICT-县区级）';
COMMENT ON COLUMN TASK_COLLABORATION.STATUS IS '状态（0-待处理/1-已处理/2-已关闭）';
COMMENT ON COLUMN TASK_COLLABORATION.REPLY_CONTENT IS '回复内容';
COMMENT ON COLUMN TASK_COLLABORATION.REPLY_TIME IS '回复时间';
COMMENT ON COLUMN TASK_COLLABORATION.REPLY_BY IS '回复人';
COMMENT ON COLUMN TASK_COLLABORATION.CREATE_BY IS '创建人';
COMMENT ON COLUMN TASK_COLLABORATION.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN TASK_COLLABORATION.UPDATE_BY IS '修改人';
COMMENT ON COLUMN TASK_COLLABORATION.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN TASK_COLLABORATION.SYS_ORG_CODE IS '组织机构编号';

CREATE INDEX IDX_TASK_COLLABORATION_TASK ON TASK_COLLABORATION(TASK_ID);
CREATE INDEX IDX_TASK_COLLABORATION_REQUESTER ON TASK_COLLABORATION(REQUESTER_ID);
CREATE INDEX IDX_TASK_COLLABORATION_STATUS ON TASK_COLLABORATION(STATUS);