AiDOPWarehouse/doc/db/rule/01_ado_rule_config.sql

-- 全模块规则配置表草案
-- 状态：评审草案；P29 已在 aidopdev 研发库验证执行
-- 试点：S3 / DELIVERY_GENERATE

CREATE TABLE IF NOT EXISTS ado_rule_profile (
    id BIGINT PRIMARY KEY,
    module_code VARCHAR(32) NOT NULL COMMENT '模块编码，如 S3/S4/S8/S9',
    scenario_code VARCHAR(64) NOT NULL COMMENT '场景编码，如 DELIVERY_GENERATE',
    profile_code VARCHAR(64) NOT NULL COMMENT '规则方案编码',
    profile_name VARCHAR(128) NOT NULL COMMENT '规则方案名称',
    tenant_id BIGINT NOT NULL DEFAULT 0 COMMENT '0 表示全局默认',
    factory_id BIGINT NOT NULL DEFAULT 0 COMMENT '0 表示租户级或全局',
    version INT NOT NULL DEFAULT 1 COMMENT '版本号',
    is_enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用',
    effective_from DATETIME NULL COMMENT '生效开始时间',
    effective_to DATETIME NULL COMMENT '生效结束时间',
    remark VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(64) NULL,
    updated_at DATETIME NULL,
    updated_by VARCHAR(64) NULL,
    UNIQUE KEY uk_rule_profile_code (module_code, scenario_code, profile_code, tenant_id, factory_id, version),
    KEY idx_rule_profile_effective (module_code, scenario_code, tenant_id, factory_id, is_enabled, effective_from, effective_to)
) COMMENT='全模块规则配置方案';

CREATE TABLE IF NOT EXISTS ado_rule_item (
    id BIGINT PRIMARY KEY,
    profile_id BIGINT NOT NULL COMMENT '规则方案 ID',
    rule_code VARCHAR(64) NOT NULL COMMENT '规则编码',
    rule_name VARCHAR(128) NOT NULL COMMENT '规则名称',
    value_type VARCHAR(32) NOT NULL COMMENT 'string/number/boolean/string_array/number_array/json',
    rule_value TEXT NULL COMMENT '规则值，数组和对象使用 JSON',
    is_enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用',
    sort_no INT NOT NULL DEFAULT 0,
    remark VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(64) NULL,
    updated_at DATETIME NULL,
    updated_by VARCHAR(64) NULL,
    UNIQUE KEY uk_rule_item_code (profile_id, rule_code),
    KEY idx_rule_item_profile (profile_id, is_enabled)
) COMMENT='全模块规则配置项';

CREATE TABLE IF NOT EXISTS ado_rule_schema (
    id BIGINT PRIMARY KEY,
    module_code VARCHAR(32) NOT NULL,
    scenario_code VARCHAR(64) NOT NULL,
    rule_code VARCHAR(64) NOT NULL,
    rule_name VARCHAR(128) NOT NULL,
    value_type VARCHAR(32) NOT NULL,
    default_value TEXT NULL COMMENT '默认值，数组和对象使用 JSON',
    required TINYINT NOT NULL DEFAULT 0,
    validation_rule TEXT NULL COMMENT 'min/max/enum/regex 等 JSON 描述',
    ui_control VARCHAR(32) NULL COMMENT 'input/number/switch/multi_select/json',
    option_source TEXT NULL COMMENT '静态枚举或数据源说明',
    is_enabled TINYINT NOT NULL DEFAULT 1,
    sort_no INT NOT NULL DEFAULT 0,
    remark VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(64) NULL,
    updated_at DATETIME NULL,
    updated_by VARCHAR(64) NULL,
    UNIQUE KEY uk_rule_schema_code (module_code, scenario_code, rule_code),
    KEY idx_rule_schema_scene (module_code, scenario_code, is_enabled)
) COMMENT='全模块规则定义';

-- S3 / DELIVERY_GENERATE 默认规则方案
INSERT INTO ado_rule_profile
(id, module_code, scenario_code, profile_code, profile_name, tenant_id, factory_id, version, is_enabled, remark)
VALUES
(1003000000001, 'S3', 'DELIVERY_GENERATE', 'S3_DELIVERY_GENERATE_DEFAULT', 'S3 交货单生成默认规则', 0, 0, 1, 1, 'P10 规则配置试点默认方案')
AS new
ON DUPLICATE KEY UPDATE
    profile_name=new.profile_name,
    is_enabled=new.is_enabled,
    remark=new.remark,
    updated_at=CURRENT_TIMESTAMP;

-- S3 / DELIVERY_GENERATE 规则定义
INSERT INTO ado_rule_schema
(id, module_code, scenario_code, rule_code, rule_name, value_type, default_value, required, validation_rule, ui_control, option_source, is_enabled, sort_no, remark)
VALUES
(1003000000101, 'S3', 'DELIVERY_GENERATE', 'quotaRequiredTotal', '货源配额要求合计', 'number', '100', 1, '{"min":0,"max":100}', 'number', NULL, 1, 10, '货源配额合计校验目标值'),
(1003000000102, 'S3', 'DELIVERY_GENERATE', 'quotaTolerance', '配额误差容忍', 'number', '0.0001', 1, '{"min":0}', 'number', NULL, 1, 20, '配额合计误差容忍'),
(1003000000103, 'S3', 'DELIVERY_GENERATE', 'poBuyerCodes', '普通 PO 可用采购组', 'string_array', '["110","160","170"]', 1, NULL, 'multi_select', 'static:110,160,170', 1, 30, '普通 PO 采购组白名单'),
(1003000000104, 'S3', 'DELIVERY_GENERATE', 'doUsages', 'DO 可用供应类别', 'string_array', '["标准","VMI","委外加工"]', 1, NULL, 'multi_select', 'static:标准,VMI,委外加工', 1, 40, 'DO 可用用途白名单'),
(1003000000105, 'S3', 'DELIVERY_GENERATE', 'defaultLeadDays', '默认提前期天数', 'number', '1', 1, '{"min":0}', 'number', NULL, 1, 50, '货源提前期缺失或小于等于 0 时使用'),
(1003000000106, 'S3', 'DELIVERY_GENERATE', 'enablePackagingRoundUp', '启用包装量向上取整', 'boolean', 'true', 1, NULL, 'switch', NULL, 1, 60, '是否按 packaging_qty 圆整'),
(1003000000107, 'S3', 'DELIVERY_GENERATE', 'shortageCreatePr', 'PO 不足自动生成 PR', 'boolean', 'true', 1, NULL, 'switch', NULL, 1, 70, '采购单不足时是否生成 PR'),
(1003000000108, 'S3', 'DELIVERY_GENERATE', 'enablePrMerge', '启用 PR 合并', 'boolean', 'true', 1, NULL, 'switch', NULL, 1, 80, '是否启用本次生成 PR 合并'),
(1003000000109, 'S3', 'DELIVERY_GENERATE', 'enableRequireGoodsToPo', '启用要货令转 DO/PO', 'boolean', 'true', 1, NULL, 'switch', NULL, 1, 90, '是否将 IsRequireGoods=1 的 PR 转 DO/PO'),
(1003000000110, 'S3', 'DELIVERY_GENERATE', 'enableExternalPushTracking', '启用外部事务写入', 'boolean', 'true', 1, NULL, 'switch', NULL, 1, 100, '是否写 QadTracking；P22 为保持现有 P5 行为默认开启')
AS new
ON DUPLICATE KEY UPDATE
    rule_name=new.rule_name,
    value_type=new.value_type,
    default_value=new.default_value,
    required=new.required,
    validation_rule=new.validation_rule,
    ui_control=new.ui_control,
    option_source=new.option_source,
    is_enabled=new.is_enabled,
    sort_no=new.sort_no,
    remark=new.remark,
    updated_at=CURRENT_TIMESTAMP;

-- S3 / DELIVERY_GENERATE 默认规则项
INSERT INTO ado_rule_item
(id, profile_id, rule_code, rule_name, value_type, rule_value, is_enabled, sort_no, remark)
VALUES
(1003000000201, 1003000000001, 'quotaRequiredTotal', '货源配额要求合计', 'number', '100', 1, 10, NULL),
(1003000000202, 1003000000001, 'quotaTolerance', '配额误差容忍', 'number', '0.0001', 1, 20, NULL),
(1003000000203, 1003000000001, 'poBuyerCodes', '普通 PO 可用采购组', 'string_array', '["110","160","170"]', 1, 30, NULL),
(1003000000204, 1003000000001, 'doUsages', 'DO 可用供应类别', 'string_array', '["标准","VMI","委外加工"]', 1, 40, NULL),
(1003000000205, 1003000000001, 'defaultLeadDays', '默认提前期天数', 'number', '1', 1, 50, NULL),
(1003000000206, 1003000000001, 'enablePackagingRoundUp', '启用包装量向上取整', 'boolean', 'true', 1, 60, NULL),
(1003000000207, 1003000000001, 'shortageCreatePr', 'PO 不足自动生成 PR', 'boolean', 'true', 1, 70, NULL),
(1003000000208, 1003000000001, 'enablePrMerge', '启用 PR 合并', 'boolean', 'true', 1, 80, NULL),
(1003000000209, 1003000000001, 'enableRequireGoodsToPo', '启用要货令转 DO/PO', 'boolean', 'true', 1, 90, NULL),
(1003000000210, 1003000000001, 'enableExternalPushTracking', '启用外部事务写入', 'boolean', 'true', 1, 100, 'P22 为保持现有 P5 行为默认开启；生产切换前仍需确认 QadTracking 消费机制')
AS new
ON DUPLICATE KEY UPDATE
    rule_name=new.rule_name,
    value_type=new.value_type,
    rule_value=new.rule_value,
    is_enabled=new.is_enabled,
    sort_no=new.sort_no,
    remark=new.remark,
    updated_at=CURRENT_TIMESTAMP;