AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.536.sql

-- ============================================================================
-- 1.0.536 · API_INBOUND WP-A：入站 6 表 + mdp_entity 扩列 + 联调种子（号段避让远端已占用的 1.0.527）
--
-- 幂等：CREATE TABLE IF NOT EXISTS；扩列 information_schema + PREPARE 自包含；
-- 种子 INSERT ... WHERE NOT EXISTS。AutoVersionUpdate 按 ; 拆句、无事务。
-- ============================================================================

CREATE TABLE IF NOT EXISTS mdp_inbound_grant (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL COMMENT '凭证绑定租户（冗余自 SysOpenAccess.BindTenantId）',
    access_key VARCHAR(64) NOT NULL COMMENT 'SysOpenAccess.AccessKey',
    entity_code VARCHAR(100) NOT NULL COMMENT 'mdp_entity.entity_code 白名单',
    factory_id BIGINT NULL COMMENT '可选绑定工厂',
    source_code VARCHAR(50) NOT NULL COMMENT 'mdp_source.source_code（source_type=API_INBOUND）',
    rate_limit_per_min INT NOT NULL DEFAULT 60,
    batch_row_limit INT NOT NULL DEFAULT 500 COMMENT '主数据快照类可放宽到 2000',
    ip_allowlist VARCHAR(1000) NULL COMMENT '逗号分隔；空=不限制',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '1启用 0停用',
    remark VARCHAR(500) NULL,
    create_time DATETIME NOT NULL,
    update_time DATETIME NOT NULL,
    UNIQUE KEY uk_inbound_grant (access_key, entity_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='第三方推数入站授权';

CREATE TABLE IF NOT EXISTS mdp_inbound_request (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    access_key VARCHAR(64) NOT NULL,
    entity_code VARCHAR(100) NOT NULL,
    idempotency_key VARCHAR(128) NOT NULL,
    request_hash CHAR(64) NOT NULL COMMENT 'sha256(原始请求体字节) hex',
    sync_batch_id VARCHAR(64) NOT NULL COMMENT 'INB_yyyyMMdd_{id 零填充}',
    snapshot_id VARCHAR(64) NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'RECEIVED' COMMENT 'RECEIVED/COMMITTED/FAILED',
    http_status INT NOT NULL DEFAULT 0,
    result_code INT NOT NULL DEFAULT 0 COMMENT '回执 code：0 ok / 2 partial',
    accepted INT NOT NULL DEFAULT 0,
    rejected INT NOT NULL DEFAULT 0,
    stale_rejected INT NOT NULL DEFAULT 0,
    equal_ts_override INT NOT NULL DEFAULT 0,
    receipt_json MEDIUMTEXT NULL,
    error_msg VARCHAR(1000) NULL,
    client_ip VARCHAR(64) NULL,
    elapsed_ms INT NOT NULL DEFAULT 0,
    create_time DATETIME NOT NULL,
    update_time DATETIME NOT NULL,
    UNIQUE KEY uk_inbound_idem (tenant_id, access_key, entity_code, idempotency_key),
    KEY idx_inbound_req_batch (sync_batch_id),
    KEY idx_inbound_req_entity_time (entity_code, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='第三方推数入站请求（幂等状态机+审计）';

CREATE TABLE IF NOT EXISTS mdp_inbound_envelope (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    request_id BIGINT NOT NULL COMMENT 'mdp_inbound_request.id',
    tenant_id BIGINT NOT NULL,
    access_key VARCHAR(64) NOT NULL,
    entity_code VARCHAR(100) NOT NULL,
    path VARCHAR(300) NOT NULL,
    headers_json TEXT NULL COMMENT '脱敏后头集合',
    body MEDIUMTEXT NOT NULL,
    body_sha256 CHAR(64) NOT NULL,
    create_time DATETIME NOT NULL,
    UNIQUE KEY uk_inbound_envelope_req (request_id),
    KEY idx_inbound_envelope_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='入站原始信封归档（重放用）';

CREATE TABLE IF NOT EXISTS mdp_inbound_snapshot (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    snapshot_id VARCHAR(64) NOT NULL,
    tenant_id BIGINT NOT NULL,
    access_key VARCHAR(64) NOT NULL,
    entity_code VARCHAR(100) NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'OPEN' COMMENT 'OPEN/COMMITTED/EXPIRED/DIFF_BLOCKED',
    last_seq INT NOT NULL DEFAULT 0,
    row_count INT NOT NULL DEFAULT 0,
    diff_missing INT NULL,
    diff_ratio DECIMAL(6,4) NULL,
    opened_at DATETIME NOT NULL,
    expire_at DATETIME NOT NULL,
    committed_at DATETIME NULL,
    create_time DATETIME NOT NULL,
    update_time DATETIME NOT NULL,
    UNIQUE KEY uk_inbound_snapshot (snapshot_id),
    KEY idx_inbound_snap_entity (tenant_id, entity_code, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='入站全量快照批次';

CREATE TABLE IF NOT EXISTS mdp_inbound_conflict (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    entity_code VARCHAR(100) NOT NULL,
    biz_key VARCHAR(300) NOT NULL,
    source_system VARCHAR(50) NOT NULL,
    incoming_raw MEDIUMTEXT NOT NULL,
    mirror_table VARCHAR(64) NOT NULL,
    conflict_type VARCHAR(40) NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/RESOLVED',
    resolution VARCHAR(500) NULL,
    create_time DATETIME NOT NULL,
    update_time DATETIME NOT NULL,
    KEY idx_inbound_conflict (tenant_id, entity_code, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='主数据镜像键冲突待裁决';

CREATE TABLE IF NOT EXISTS mdp_field_map (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    entity_code VARCHAR(100) NOT NULL,
    access_key VARCHAR(64) NOT NULL DEFAULT '*' COMMENT '精确对接方优先；*=该实体默认映射',
    contract_field VARCHAR(200) NOT NULL,
    raw_key VARCHAR(200) NOT NULL,
    is_required TINYINT NOT NULL DEFAULT 0,
    status TINYINT NOT NULL DEFAULT 1,
    create_time DATETIME NOT NULL,
    update_time DATETIME NOT NULL,
    UNIQUE KEY uk_field_map (tenant_id, entity_code, access_key, contract_field)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='入站对外契约字段两层映射';

SET @exist := (SELECT COUNT(1) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='mdp_entity' AND COLUMN_NAME='inbound_enabled');
SET @sql := IF(@exist=0, "ALTER TABLE mdp_entity ADD COLUMN inbound_enabled TINYINT NOT NULL DEFAULT 0 COMMENT '入站开通开关'", 'SELECT 1');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

SET @exist := (SELECT COUNT(1) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='mdp_entity' AND COLUMN_NAME='inbound_max_rows');
SET @sql := IF(@exist=0, "ALTER TABLE mdp_entity ADD COLUMN inbound_max_rows INT NOT NULL DEFAULT 500 COMMENT '单请求行数上限（快照类 2000）'", 'SELECT 1');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

SET @exist := (SELECT COUNT(1) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='mdp_entity' AND COLUMN_NAME='stale_guard_enabled');
SET @sql := IF(@exist=0, "ALTER TABLE mdp_entity ADD COLUMN stale_guard_enabled TINYINT NOT NULL DEFAULT 1 COMMENT '乱序 stale 守卫'", 'SELECT 1');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

SET @exist := (SELECT COUNT(1) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='mdp_entity' AND COLUMN_NAME='silence_alert_hours');
SET @sql := IF(@exist=0, "ALTER TABLE mdp_entity ADD COLUMN silence_alert_hours INT NULL COMMENT '沉默告警阈值（小时）；NULL=不告警'", 'SELECT 1');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

SET @exist := (SELECT COUNT(1) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='mdp_entity' AND COLUMN_NAME='silence_calendar');
SET @sql := IF(@exist=0, "ALTER TABLE mdp_entity ADD COLUMN silence_calendar VARCHAR(20) NULL COMMENT '逗号分隔星期 1-7；空=每天'", 'SELECT 1');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

INSERT INTO mdp_source
  (tenant_id, source_code, source_name, source_type, status, remark, create_time, update_time)
SELECT 797403760988229, 'TEST_INBOUND_ERP', '联调入站源 API_INBOUND', 'API_INBOUND', 1,
       'WP-A 联调种子；正式对接方另签发', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_source WHERE source_code='TEST_INBOUND_ERP');

INSERT INTO SysOpenAccess (Id, AccessKey, AccessSecret, BindTenantId, BindUserId, CreateTime, UpdateTime)
SELECT 910000000000001, 'TESTKPARTNER', 'TESTKPARTNER_SECRET_DO_NOT_USE_IN_PROD',
       797403760988229, 797403762421829, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM SysOpenAccess WHERE AccessKey='TESTKPARTNER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, batch_size, biz_key_expr, status, inbound_enabled, inbound_max_rows, stale_guard_enabled,
   remark, create_time, update_time)
SELECT e.tenant_id, s.id, 'S1_SALES_ORDER_ENTRY', '销售订单行(入站草案)', e.entity_type, e.source_table_name, e.target_table_name,
       e.sync_mode, e.batch_size, e.biz_key_expr, 1, 1, 500, 1,
       'v1-draft 对外码，贴源/转换复用 S1_SEORDER_ENTRY', NOW(), NOW()
FROM mdp_entity e
JOIN mdp_source s ON s.source_code='TEST_INBOUND_ERP'
WHERE e.entity_code='S1_SEORDER_ENTRY'
  AND NOT EXISTS (SELECT 1 FROM mdp_entity x WHERE x.entity_code='S1_SALES_ORDER_ENTRY');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, batch_size, biz_key_expr, status, inbound_enabled, inbound_max_rows, stale_guard_enabled,
   remark, create_time, update_time)
SELECT e.tenant_id, s.id, 'MDM_ITEM', '物料主数据(入站草案)', e.entity_type, e.source_table_name, e.target_table_name,
       e.sync_mode, e.batch_size, e.biz_key_expr, 1, 1, 500, 1,
       'v1-draft 对外码，贴源/转换复用 S3_ITEM_ERP', NOW(), NOW()
FROM mdp_entity e
JOIN mdp_source s ON s.source_code='TEST_INBOUND_ERP'
WHERE e.entity_code='S3_ITEM_ERP'
  AND NOT EXISTS (SELECT 1 FROM mdp_entity x WHERE x.entity_code='MDM_ITEM');

UPDATE mdp_entity
   SET inbound_enabled=1, inbound_max_rows=500, stale_guard_enabled=1, update_time=NOW()
 WHERE entity_code IN ('S1_SALES_ORDER_ENTRY', 'S1_SEORDER_ENTRY', 'MDM_ITEM');

INSERT INTO mdp_inbound_grant
  (tenant_id, access_key, entity_code, factory_id, source_code, rate_limit_per_min, batch_row_limit, status, remark, create_time, update_time)
SELECT 797403760988229, 'TESTKPARTNER', 'S1_SALES_ORDER_ENTRY', NULL, 'TEST_INBOUND_ERP', 60, 500, 1, 'WP-A 联调', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_inbound_grant WHERE access_key='TESTKPARTNER' AND entity_code='S1_SALES_ORDER_ENTRY');

INSERT INTO mdp_inbound_grant
  (tenant_id, access_key, entity_code, factory_id, source_code, rate_limit_per_min, batch_row_limit, status, remark, create_time, update_time)
SELECT 797403760988229, 'TESTKPARTNER', 'MDM_ITEM', NULL, 'TEST_INBOUND_ERP', 60, 500, 1, 'WP-A 联调', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_inbound_grant WHERE access_key='TESTKPARTNER' AND entity_code='MDM_ITEM');