-- 审批流程系统数据库迁移
-- 版本: V22
-- 描述: 创建审批流程相关表
-- 创建时间: 2026-03-04

-- 1. 审批流程配置表
CREATE TABLE sys_approval_flow (
    id                  BIGINT          PRIMARY KEY AUTO_INCREMENT COMMENT '流程ID',
    flow_code           VARCHAR(50)     NOT NULL UNIQUE COMMENT '流程代码',
    flow_name           VARCHAR(100)    NOT NULL COMMENT '流程名称',
    module_code         VARCHAR(50)     NOT NULL COMMENT '所属模块',
    business_type       VARCHAR(50)     NOT NULL COMMENT '业务类型',
    approval_type       VARCHAR(20)     NOT NULL COMMENT '审批类型：SERIAL(串行)/PARALLEL(并行)/CONDITIONAL(条件)',
    approval_mode       VARCHAR(20)     NOT NULL DEFAULT 'ANY' COMMENT '审批模式：ANY(或签)/ALL(会签)',
    timeout_hours       INT             DEFAULT 24 COMMENT '审批超时小时数',
    auto_approve        TINYINT(1)     DEFAULT 0 COMMENT '是否支持自动审批',
    auto_approve_rule   VARCHAR(500)    COMMENT '自动审批规则表达式',
    allow_transfer      TINYINT(1)     DEFAULT 1 COMMENT '是否允许转审',
    allow_cc            TINYINT(1)     DEFAULT 1 COMMENT '是否允许抄送',
    notify_type         VARCHAR(50)     DEFAULT 'EMAIL,IN_APP' COMMENT '通知类型：EMAIL/IN_APP/SMS/WECHAT',
    form_template       TEXT            COMMENT '审批表单模板(JSON)',
    description         VARCHAR(500)    COMMENT '流程描述',
    is_active           TINYINT(1)      DEFAULT 1 COMMENT '是否启用',
    priority            INT             DEFAULT 0 COMMENT '优先级',
    created_by         BIGINT          COMMENT '创建人',
    created_at         DATETIME        DEFAULT CURRENT_TIMESTAMP,
    updated_at         DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted            TINYINT(1)      DEFAULT 0 COMMENT '删除标志',
    INDEX idx_flow_code (flow_code),
    INDEX idx_module_code (module_code),
    INDEX idx_business_type (business_type),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='审批流程配置表';

-- 2. 审批节点配置表
CREATE TABLE sys_approval_node (
    id                  BIGINT          PRIMARY KEY AUTO_INCREMENT COMMENT '节点ID',
    flow_id             BIGINT          NOT NULL COMMENT '流程ID',
    node_code           VARCHAR(50)     NOT NULL COMMENT '节点代码',
    node_name           VARCHAR(100)    NOT NULL COMMENT '节点名称',
    node_type           VARCHAR(20)     NOT NULL COMMENT '节点类型：START/APPROVAL/CONDITION/CC/END',
    node_order          INT             NOT NULL DEFAULT 0 COMMENT '节点顺序',
    approver_type       VARCHAR(20)     NOT NULL COMMENT '审批人类型：USER/ROLE/DEPARTMENT/MANAGER/DYNAMIC',
    approver_value      VARCHAR(500)    COMMENT '审批人值(用户ID/角色ID/部门ID/表达式)',
    approver_count      INT             DEFAULT 1 COMMENT '审批人数(会签时>1)',
    condition_expression VARCHAR(1000)  COMMENT '条件表达式(CONDITION节点使用)',
    timeout_action      VARCHAR(20)     COMMENT '超时动作：AUTO_PASS/AUTO_REJECT/NOTIFY/ESCALATE',
    timeout_hours       INT             DEFAULT 24 COMMENT '节点超时小时数',
    allow_transfer      TINYINT(1)     DEFAULT 1 COMMENT '是否允许转审',
    allow_reject        TINYINT(1)     DEFAULT 1 COMMENT '是否允许驳回',
    reject_to_node      VARCHAR(50)     COMMENT '驳回到节点代码',
    allow_callback      TINYINT(1)     DEFAULT 1 COMMENT '是否允许撤回',
    notify_before       INT             COMMENT '提前通知分钟数',
    created_at         DATETIME        DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_flow_id (flow_id),
    INDEX idx_node_code (node_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='审批节点配置表';

-- 3. 审批实例表
CREATE TABLE sys_approval_instance (
    id                  BIGINT          PRIMARY KEY AUTO_INCREMENT COMMENT '实例ID',
    instance_no         VARCHAR(50)     NOT NULL UNIQUE COMMENT '实例编号',
    flow_id             BIGINT          NOT NULL COMMENT '流程ID',
    flow_code           VARCHAR(50)     NOT NULL COMMENT '流程代码',
    flow_name           VARCHAR(100)    NOT NULL COMMENT '流程名称',
    module_code         VARCHAR(50)     NOT NULL COMMENT '模块代码',
    business_type       VARCHAR(50)     NOT NULL COMMENT '业务类型',
    business_id         BIGINT          NOT NULL COMMENT '业务ID',
    business_key        VARCHAR(100)    COMMENT '业务键',
    title               VARCHAR(200)    NOT NULL COMMENT '审批标题',
    applicant_id        BIGINT          NOT NULL COMMENT '申请人ID',
    applicant_name      VARCHAR(100)    COMMENT '申请人姓名',
    applicant_dept_id   BIGINT          COMMENT '申请人部门ID',
    current_node_code   VARCHAR(50)     COMMENT '当前节点代码',
    current_node_name   VARCHAR(100)    COMMENT '当前节点名称',
    approval_status     VARCHAR(20)     NOT NULL DEFAULT 'PENDING' COMMENT '审批状态：PENDING/APPROVING/APPROVED/REJECTED/CANCELLED/TRANSFERRED',
    form_data           JSON            COMMENT '表单数据',
    total_amount        DECIMAL(15,2)   COMMENT '涉及金额',
    level               INT             DEFAULT 1 COMMENT '审批级别(多级审批)',
    related_instance_id BIGINT          COMMENT '关联实例ID',
    external_no         VARCHAR(100)    COMMENT '外部单据编号',
    source_system       VARCHAR(50)     COMMENT '来源系统',
    callback_url        VARCHAR(500)    COMMENT '回调URL',
    callback_status     VARCHAR(20)     COMMENT '回调状态',
    started_at          DATETIME        COMMENT '开始时间',
    ended_at            DATETIME        COMMENT '结束时间',
    duration_hours      INT             COMMENT '处理时长(小时)',
    created_at         DATETIME        DEFAULT CURRENT_TIMESTAMP,
    updated_at         DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_instance_no (instance_no),
    INDEX idx_flow_id (flow_id),
    INDEX idx_business (business_type, business_id),
    INDEX idx_applicant_id (applicant_id),
    INDEX idx_approval_status (approval_status),
    INDEX idx_current_node (current_node_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='审批实例表';

-- 4. 审批记录表
CREATE TABLE sys_approval_record (
    id                  BIGINT          PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID',
    instance_id         BIGINT          NOT NULL COMMENT '实例ID',
    node_code           VARCHAR(50)     NOT NULL COMMENT '节点代码',
    node_name           VARCHAR(100)    COMMENT '节点名称',
    approver_id         BIGINT          NOT NULL COMMENT '审批人ID',
    approver_name       VARCHAR(100)    COMMENT '审批人姓名',
    approver_dept_id    BIGINT          COMMENT '审批人部门ID',
    action_type         VARCHAR(20)     NOT NULL COMMENT '操作类型：APPROVE/REJECT/TRANSFER/CC/ESCALATE',
    action_result       VARCHAR(20)     NOT NULL COMMENT '操作结果：PASS/REJECT/TRANSFERRED/SKIPPED',
    comment             TEXT            COMMENT '审批意见',
    attachment_urls     VARCHAR(2000)   COMMENT '附件URL(逗号分隔)',
    suggest_next_node   VARCHAR(50)     COMMENT '建议下一节点',
    is_last             TINYINT(1)      DEFAULT 0 COMMENT '是否最后一级',
    level               INT             DEFAULT 1 COMMENT '审批级别',
    sequence            INT             NOT NULL DEFAULT 0 COMMENT '审批顺序',
    delegate_from_id    BIGINT          COMMENT '委托来源用户ID',
    delegate_from_name  VARCHAR(100)    COMMENT '委托来源用户名',
    timeout_action      VARCHAR(20)     COMMENT '超时自动处理',
    action_at           DATETIME        COMMENT '审批时间',
    created_at         DATETIME        DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_instance_id (instance_id),
    INDEX idx_approver_id (approver_id),
    INDEX idx_action_type (action_type),
    INDEX idx_action_result (action_result)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='审批记录表';

-- 5. 审批历史表
CREATE TABLE sys_approval_history (
    id                  BIGINT          PRIMARY KEY AUTO_INCREMENT COMMENT '历史ID',
    instance_id         BIGINT          NOT NULL COMMENT '实例ID',
    instance_no         VARCHAR(50)     NOT NULL COMMENT '实例编号',
    node_code           VARCHAR(50)     NOT NULL COMMENT '节点代码',
    node_name           VARCHAR(100)    COMMENT '节点名称',
    operator_id         BIGINT          NOT NULL COMMENT '操作人ID',
    operator_name       VARCHAR(100)    COMMENT '操作人姓名',
    operator_type       VARCHAR(20)     NOT NULL COMMENT '操作人类型：APPROVER/APPLICANT/SYSTEM',
    action_type         VARCHAR(20)     NOT NULL COMMENT '操作类型',
    action_detail       TEXT            COMMENT '操作详情',
    form_snapshot       JSON            COMMENT '表单快照',
    comment             TEXT            COMMENT '备注',
    attachment_urls     VARCHAR(2000)   COMMENT '附件',
    created_at         DATETIME        DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_instance_id (instance_id),
    INDEX idx_instance_no (instance_no),
    INDEX idx_operator_id (operator_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='审批历史表';
