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

-- 1.0.264: 剩余门禁收口
-- 1) S6 报工 stg/std + S6_REPORT / S6_REPORT_API
-- 2) S9 API_OUT 访问日志表
-- 幂等可重复执行

-- ========== mdp_stg_s6_report ==========
CREATE TABLE IF NOT EXISTS mdp_stg_s6_report (
  id bigint NOT NULL AUTO_INCREMENT,
  tenant_id bigint NOT NULL DEFAULT 0,
  source_system varchar(50) DEFAULT NULL,
  source_table varchar(200) DEFAULT NULL,
  source_row_id varchar(200) DEFAULT NULL,
  source_biz_key varchar(300) DEFAULT NULL,
  raw_data json DEFAULT NULL,
  sync_batch_id varchar(100) DEFAULT NULL,
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sync_time datetime DEFAULT CURRENT_TIMESTAMP,
  process_status varchar(20) NOT NULL DEFAULT 'PENDING',
  process_message varchar(500) DEFAULT NULL,
  update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key),
  KEY idx_batch (sync_batch_id),
  KEY idx_src (source_table, source_row_id),
  KEY idx_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S6 报工执行器贴源层（T8 Cj_Bg_Head_Rep / API）';

-- ========== mdp_std_s6_report ==========
CREATE TABLE IF NOT EXISTS mdp_std_s6_report (
  id bigint NOT NULL AUTO_INCREMENT,
  tenant_id bigint NOT NULL DEFAULT 0,
  factory_id bigint DEFAULT 1,
  source_system varchar(50) NOT NULL DEFAULT 'T8',
  work_order_no varchar(100) NOT NULL,
  report_date datetime DEFAULT NULL,
  report_qty decimal(18,6) DEFAULT NULL,
  ztid varchar(50) DEFAULT NULL,
  source_row_id varchar(100) NOT NULL,
  source_biz_key varchar(200) NOT NULL,
  sync_batch_id varchar(100) NOT NULL,
  sync_time datetime NOT NULL,
  update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_mdp_std_s6_report (tenant_id, source_system, source_biz_key),
  KEY idx_mdp_std_s6_report_wo (tenant_id, work_order_no),
  KEY idx_mdp_std_s6_report_batch (sync_batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S6 报工标准层';

-- ========== S6_REPORT DB 实体（T8）==========
INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type,
   source_table_name, source_api_path, target_table_name,
   sync_mode, batch_size, response_data_path, dedup_key_path, biz_key_expr,
   status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='T8_V5_SQLSERVER' LIMIT 1),
       'S6_REPORT', 'S6报工头(T8)', 'TABLE',
       'Cj_Bg_Head_Rep', NULL, 'mdp_stg_s6_report',
       'INCREMENTAL', 1000, NULL, NULL, 'noid,kgdate',
       1, '1.0.264 S6 report DB inbound', NOW(), NOW()
WHERE EXISTS (SELECT 1 FROM mdp_source WHERE source_code='T8_V5_SQLSERVER')
  AND NOT EXISTS (SELECT 1 FROM mdp_entity e WHERE e.entity_code='S6_REPORT');

-- ========== S6_REPORT_API ==========
INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type,
   source_table_name, source_api_path, target_table_name,
   sync_mode, batch_size, response_data_path, dedup_key_path, biz_key_expr,
   status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='WMS_API' LIMIT 1),
       'S6_REPORT_API', 'S6报工头API', 'API',
       'Cj_Bg_Head_Rep', '/api/mes/report', 'mdp_stg_s6_report',
       'INCREMENTAL', 1000, 'data.list', 'bizKey', 'noid,kgdate',
       1, '1.0.264 S6 report API pair', NOW(), NOW()
WHERE EXISTS (SELECT 1 FROM mdp_source WHERE source_code='WMS_API')
  AND NOT EXISTS (SELECT 1 FROM mdp_entity e WHERE e.entity_code='S6_REPORT_API');

-- ========== S9 API_OUT 访问日志 ==========
CREATE TABLE IF NOT EXISTS mdp_api_out_access_log (
  id bigint NOT NULL AUTO_INCREMENT,
  tenant_id bigint NOT NULL DEFAULT 0,
  caller varchar(100) DEFAULT NULL,
  resource_code varchar(100) NOT NULL,
  query_json text,
  row_count int DEFAULT 0,
  success tinyint NOT NULL DEFAULT 1,
  message varchar(500) DEFAULT NULL,
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_mdp_api_out_res (resource_code, create_time),
  KEY idx_mdp_api_out_tenant (tenant_id, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S9/API_OUT 受控只读访问日志';