-- =============================================================================
-- S1-S4 全局 UAT P0 试导 SQL 草案
-- 批次：S1S4_UAT_20260604_RQ_V1
-- 租户：797403760988229（AIDOP）  |  禁止：1300000000888（Demo）
-- 依据：doc/plan/S1-S4全局UAT场景测试数据生成方案.md
--
-- 【重要】第一阶段：Section 0 为只读检查（可执行）；Section 1-4 为 DML 草案（默认注释）。
--         正式写库前必须：① 从 dopdemorq 灌入 tmp_* 表  ② 人工审阅  ③ 取消注释后执行
-- =============================================================================

-- -----------------------------------------------------------------------------
-- Section 0：只读检查（安全可执行）
-- -----------------------------------------------------------------------------

SET @tenant_aidop := 797403760988229;
SET @tenant_demo  := 1300000000888;
SET @batch_no     := 'S1S4_UAT_20260604_RQ_V1';

-- 0.1 租户隔离：AIDOP vs Demo
SELECT 'crm_seorder' AS tbl, tenant_id, COUNT(*) AS cnt
FROM crm_seorder WHERE tenant_id IN (@tenant_aidop, @tenant_demo)
GROUP BY tenant_id;

SELECT 'mes_morder' AS tbl, tenant_id, COUNT(*) AS cnt
FROM mes_morder WHERE tenant_id IN (@tenant_aidop, @tenant_demo)
GROUP BY tenant_id;

SELECT 'PurOrdMaster' AS tbl, tenant_id, COUNT(*) AS cnt
FROM PurOrdMaster WHERE tenant_id IN (@tenant_aidop, @tenant_demo)
GROUP BY tenant_id;

-- 0.2 P0 单号冲突（应为空）
SELECT bill_no, tenant_id, COUNT(*) AS cnt
FROM crm_seorder
WHERE bill_no IN (
    'MPO482024102300001', 'MPO482025010900002', 'MPO482025012100001',
    'MPO482024120200003', 'MPO482025010800003'
)
GROUP BY bill_no, tenant_id;

-- 0.3 现有 AIDOP 主线（导入后用于对比，不应与 P0 批次混淆）
SELECT bill_no, bill_from, custom_no, date
FROM crm_seorder
WHERE tenant_id = @tenant_aidop;

-- 0.4 全局链路验收探针（导入后执行；当前仅结构预览）
/*
SELECT so.bill_no, COUNT(DISTINCT se.Id) AS line_cnt,
       COUNT(DISTINCT mo.Id) AS morder_cnt,
       COUNT(DISTINCT pm.PurOrd) AS po_cnt
FROM crm_seorder so
LEFT JOIN crm_seorderentry se ON se.seorder_id = so.Id AND se.tenant_id = @tenant_aidop
LEFT JOIN mes_moentry me ON me.soentry_id = se.Id AND me.tenant_id = @tenant_aidop
LEFT JOIN mes_morder mo ON mo.Id = me.moentry_moid AND mo.tenant_id = @tenant_aidop
LEFT JOIN PurOrdMaster pom ON pom.SalesOrd = so.bill_no AND pom.tenant_id = @tenant_aidop
LEFT JOIN PurOrdDetail pm ON pm.PurOrd = pom.PurOrd AND pm.tenant_id = @tenant_aidop
WHERE so.tenant_id = @tenant_aidop
  AND so.bill_from LIKE CONCAT('UAT导入:', @batch_no, '%')
GROUP BY so.bill_no;
*/


-- -----------------------------------------------------------------------------
-- Section 1：全局变量与 ID 映射（DML 草案 — 默认注释）
-- -----------------------------------------------------------------------------

/*
SET @tenant_id    := 797403760988229;
SET @batch_no     := 'S1S4_UAT_20260604_RQ_V1';
SET @operator_id  := 1300000000111;
SET @operator_name:= 'UAT数据导入';
SET @now          := NOW();
SET @factory_id   := 797403760988229;
SET @id_base      := 9106000400000001;   -- 新 ID 基址，避开现有 800xxx 段

-- 全局 ID 映射表（会话级）
DROP TEMPORARY TABLE IF EXISTS tmp_s1s4_uat_id_map;
CREATE TEMPORARY TABLE tmp_s1s4_uat_id_map (
    batch_no    VARCHAR(64)  NOT NULL,
    entity_name VARCHAR(64)  NOT NULL,
    source_key  VARCHAR(128) NOT NULL,
    target_id   BIGINT       NOT NULL,
    ref_key     VARCHAR(128) NULL,
    PRIMARY KEY (batch_no, entity_name, source_key)
);

-- P0 五单：销售订单 ID 预分配
INSERT INTO tmp_s1s4_uat_id_map VALUES
(@batch_no,'crm_seorder','MPO482024102300001',@id_base+1,'MPO482024102300001'),
(@batch_no,'crm_seorder','MPO482025010900002',@id_base+2,'MPO482025010900002'),
(@batch_no,'crm_seorder','MPO482025012100001',@id_base+3,'MPO482025012100001'),
(@batch_no,'crm_seorder','MPO482024120200003',@id_base+4,'MPO482024120200003'),
(@batch_no,'crm_seorder','MPO482025010800003',@id_base+5,'MPO482025010800003');

-- 订单明细 ID：每单 2 行 => @id_base+101 .. +110
-- 工单 ID：每明细 1 工单 => @id_base+201 .. +210
-- 采购订单号：PO-UAT-001 .. PO-UAT-005
*/


-- -----------------------------------------------------------------------------
-- Section 2：S1 销售订单（链路起点）
-- -----------------------------------------------------------------------------

/*
-- 2.1 中间表：从 dopdemorq 抽取后灌入（字段为占位，须替换）
DROP TEMPORARY TABLE IF EXISTS tmp_s1_uat_so;
CREATE TEMPORARY TABLE tmp_s1_uat_so (
    bill_no      VARCHAR(64)  NOT NULL PRIMARY KEY,
    target_id    BIGINT       NOT NULL,
    custom_no    VARCHAR(64)  NULL,
    custom_name  VARCHAR(128) NULL,
    order_date   DATETIME     NULL,
    delivery_date DATETIME    NULL,
    urgent       INT          DEFAULT 0,
    status       INT          DEFAULT 1
);

INSERT INTO tmp_s1_uat_so VALUES
('MPO482024102300001', @id_base+1,  'CUST0005', 'ET0005', '2024-10-23', '2024-11-30', 0, 1),
('MPO482025010900002', @id_base+2,  'CUST0005', 'ET0005', '2025-01-09', '2025-02-28', 0, 1),
('MPO482025012100001', @id_base+3,  'CUST0005', 'ET0005', '2025-01-21', '2025-03-15', 1, 1),
('MPO482024120200003', @id_base+4,  'CUST0005', 'ET0005', '2024-12-02', '2025-01-20', 0, 1),
('MPO482025010800003', @id_base+5,  'CUST0005', 'ET0005', '2025-01-08', '2025-02-20', 0, 1);

INSERT INTO crm_seorder (
    Id, bill_no, order_type, custom_no, custom_name,
    date, rdate, urgent, bill_from, closed, IsDeleted,
    create_by_name, create_time, update_by_name, update_time,
    tenant_id, factory_id
)
SELECT
    target_id, bill_no, 1, custom_no, custom_name,
    order_date, delivery_date, urgent,
    CONCAT('UAT导入:', @batch_no),
    0, 0,
    @operator_name, @now, @operator_name, @now,
    @tenant_id, @factory_id
FROM tmp_s1_uat_so;

-- 2.2 订单明细（每单 2 行；item_number/qty 须从旧库替换）
DROP TEMPORARY TABLE IF EXISTS tmp_s1_uat_so_line;
CREATE TEMPORARY TABLE tmp_s1_uat_so_line (
    target_id       BIGINT NOT NULL PRIMARY KEY,
    bill_no         VARCHAR(64) NOT NULL,
    entry_seq       INT NOT NULL,
    item_number     VARCHAR(64) NULL,
    item_name       VARCHAR(128) NULL,
    qty             DECIMAL(18,4) NULL,
    plan_date       DATETIME NULL
);

-- 示例：MPO482024102300001 两行
INSERT INTO tmp_s1_uat_so_line VALUES
(@id_base+101,'MPO482024102300001',1,'ITEM-P0-01','成品A',100,'2024-11-15'),
(@id_base+102,'MPO482024102300001',2,'ITEM-P0-02','成品B', 50,'2024-11-20');
-- ... 其余 8 行按同模式从 dopdemorq 补全

INSERT INTO crm_seorderentry (
    Id, seorder_id, bill_no, entry_seq, item_number, item_name,
    qty, plan_date, date, IsDeleted,
    create_by_name, create_time, update_by_name, update_time,
    tenant_id, factory_id, seorderentry_id
)
SELECT
    l.target_id, m.target_id, l.bill_no, l.entry_seq,
    l.item_number, l.item_name, l.qty, l.plan_date, s.order_date, 0,
    @operator_name, @now, @operator_name, @now,
    @tenant_id, @factory_id, l.target_id
FROM tmp_s1_uat_so_line l
JOIN tmp_s1_uat_id_map m ON m.entity_name='crm_seorder' AND m.ref_key=l.bill_no AND m.batch_no=@batch_no
JOIN tmp_s1_uat_so s ON s.bill_no = l.bill_no;
*/


-- -----------------------------------------------------------------------------
-- Section 3：S2 工单（必须来源于 S1 订单明细）
-- -----------------------------------------------------------------------------

/*
-- 3.1 mes_morder + mes_moentry：soentry_id 关联 crm_seorderentry.Id
DROP TEMPORARY TABLE IF EXISTS tmp_s2_uat_morder;
CREATE TEMPORARY TABLE tmp_s2_uat_morder (
    target_mo_id    BIGINT NOT NULL PRIMARY KEY,
    target_me_id    BIGINT NOT NULL,
    soentry_id      BIGINT NOT NULL,
    bill_no         VARCHAR(64) NOT NULL,
    morder_no       VARCHAR(128) NOT NULL,
    product_code    VARCHAR(128) NULL,
    need_number     DECIMAL(23,10) NULL
);

INSERT INTO tmp_s2_uat_morder VALUES
(@id_base+201, @id_base+301, @id_base+101, 'MPO482024102300001', 'WO-UAT-2024-001', 'ITEM-P0-01', 100),
(@id_base+202, @id_base+302, @id_base+102, 'MPO482024102300001', 'WO-UAT-2024-002', 'ITEM-P0-02',  50);
-- ... 其余 8 工单

INSERT INTO mes_morder (
    Id, morder_no, morder_date, morder_state, product_code, product_name,
    need_number, morder_production_number, IsDeleted, urgent,
    create_by_name, create_time, update_by_name, update_time,
    tenant_id, factory_id
)
SELECT
    target_mo_id, morder_no, @now, 'Released', product_code, product_code,
    need_number, need_number, 0, 0,
    @operator_name, @now, @operator_name, @now,
    @tenant_id, @factory_id
FROM tmp_s2_uat_morder;

INSERT INTO mes_moentry (
    Id, moentry_moid, moentry_mono, soentry_id, fbill_no,
    need_number, morder_production_number, IsDeleted,
    create_by_name, create_time, tenant_id, factory_id
)
SELECT
    target_me_id, target_mo_id, morder_no, soentry_id, bill_no,
    need_number, need_number, 0,
    @operator_name, @now, @tenant_id, @factory_id
FROM tmp_s2_uat_morder;

-- 3.2 WorkOrdMaster：供 S2 排程/日计划页（WorkOrd = morder_no）
INSERT INTO WorkOrdMaster (
    Domain, WorkOrd, ItemNum, Status, QtyOrded, QtyCompleted, LbrVar,
    OrdDate, DueDate, SalesJob, tenant_id, CreateTime, UpdateTime
)
SELECT
    CAST(@factory_id AS CHAR), morder_no, product_code, 'R',
    need_number, 0, 0,
    @now, DATE_ADD(@now, INTERVAL 14 DAY), bill_no,
    @tenant_id, @now, @now
FROM tmp_s2_uat_morder;
*/


-- -----------------------------------------------------------------------------
-- Section 4：S3 物料需求 / 采购（必须来源于 S2 工单）
-- -----------------------------------------------------------------------------

/*
-- 4.1 采购申请 srm_pr_main（每条工单至少 1 条缺料申请）
DROP TEMPORARY TABLE IF EXISTS tmp_s3_uat_pr;
CREATE TEMPORARY TABLE tmp_s3_uat_pr (
    voucher         BIGINT NOT NULL PRIMARY KEY,
    pr_billno       VARCHAR(64) NOT NULL,
    morder_no       VARCHAR(128) NOT NULL,
    icitem_name     VARCHAR(255) NULL,
    pr_rqty         DECIMAL(18,4) NULL
);

INSERT INTO tmp_s3_uat_pr VALUES
(@id_base+401, 'PR-UAT-001', 'WO-UAT-2024-001', '原料M1', 200),
(@id_base+402, 'PR-UAT-002', 'WO-UAT-2024-001', '原料M2', 100);
-- ... 按工单扩展

INSERT INTO srm_pr_main (
    voucher, Id, pr_billno, pr_purchaseid, pr_purchasenumber, pr_purchasename,
    icitem_id, icitem_name, pr_rqty, pr_aqty, state, pr_type, currencytype,
    create_by_name, create_time, update_by_name, update_time,
    tenant_id, factory_id, IsDeleted, Remark
)
SELECT
    voucher, voucher, pr_billno, 0, morder_no, morder_no,
    0, icitem_name, pr_rqty, 0, 1, 1, 0,
    @operator_name, @now, @operator_name, @now,
    @tenant_id, @factory_id, 0, CONCAT('UAT:', @batch_no)
FROM tmp_s3_uat_pr;

-- 4.2 采购订单 PurOrdMaster / PurOrdDetail（SalesOrd + WorkOrd 回指 S1/S2）
-- PO-UAT-001 关联 MPO482024102300001 + WO-UAT-2024-001
INSERT INTO PurOrdMaster (
    PurOrd, OrdDate, DueDate, Supp, Potype, Status, SalesOrd, WorkOrd,
    Remark, Domain, tenant_id, CreateTime, UpdateTime,
    Confirming, Consignment, CommentIndex, CreditTermsInt, Disc, ExchRate,
    Sequence, ExchRate1, ExchRate2, FixedPrice, FixedRate, Frt, EMTPO,
    MaxAgingDays, PartialOK, AmtPrepaid, PrintPO, ERSPriceListOption,
    PST, Recurr, ReleaseFlag, Rel, Revision, Scheduled, Taxable,
    BusinessID, IsActive, IsConfirm, IsChanged, TaxIn, Rev
)
VALUES (
    'PO-UAT-001', @now, DATE_ADD(@now, INTERVAL 21 DAY), 'VEN00060', 'PT', 'O',
    'MPO482024102300001', 'WO-UAT-2024-001',
    CONCAT('UAT:', @batch_no), CAST(@factory_id AS CHAR), @tenant_id, @now, @now,
    0, 0, 0, 0, 0, 1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
);

INSERT INTO PurOrdDetail (
    PurOrd, Line, ItemNum, QtyOrded, DueDate, PurCost, Status,
    Domain, tenant_id, CreateTime, UpdateTime,
    QtyBO, CommentIndex, Consignment, CreditTermsInt, Disc, Op, ProjectLine,
    ERSPriceListOption, ReqLine, Rel, NextReleaseID, PrimarySOLine,
    Scheduled, ScheduledChanged, SchedMRPReq, ShipSchedWeeks, StdCost,
    Taxable, TaxIn, MaxTaxableAmt, TransportHours, UMConversion, VAT,
    BusinessID, IsActive, IsConfirm, Potype, IsChanged, TaxRate,
    IsRounding, ReceiptQty, BarCodeQty, IsClosed, QtyReturnedRefund, CumQtyBO,
    PurOrdRecID
)
VALUES
('PO-UAT-001', 1, 'RAW-M1', 200, DATE_ADD(@now, INTERVAL 14 DAY), 10, 'O',
 CAST(@factory_id AS CHAR), @tenant_id, @now, @now,
 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'PT',0,0,0,0,0,0,0,0,0,0),
('PO-UAT-001', 2, 'RAW-M2', 100, DATE_ADD(@now, INTERVAL 14 DAY), 10, 'O',
 CAST(@factory_id AS CHAR), @tenant_id, @now, @now,
 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'PT',0,0,0,0,0,0,0,0,0,0);

-- 4.3 交货计划 srm_polist_ds（ponumber 指向 PurOrd）
INSERT INTO srm_polist_ds (
    Id, domain, icdsid, dsnum, status, itemnum, um, purgroup,
    suppliercode, supplier, ponumber, poline, schedqty, needdate, tenant_id
)
VALUES
(@id_base+501, CAST(@factory_id AS CHAR), @id_base+501, 'DS-UAT-001', 'P',
 'RAW-M1', 'EA', 'PG01', 'VEN00060', '供应商A', 'PO-UAT-001', 1, 200, DATE_ADD(@now, INTERVAL 14 DAY), @tenant_id),
(@id_base+502, CAST(@factory_id AS CHAR), @id_base+502, 'DS-UAT-002', 'P',
 'RAW-M2', 'EA', 'PG01', 'VEN00060', '供应商A', 'PO-UAT-001', 2, 100, DATE_ADD(@now, INTERVAL 14 DAY), @tenant_id);
*/


-- -----------------------------------------------------------------------------
-- Section 5：S4 交货 / 发货 / 退货（必须来源于 S3 采购订单）
-- -----------------------------------------------------------------------------

/*
-- 5.1 供应商发货 scm_shd / scm_shdzb（po_billno = PurOrd）
-- 注：scm_shd 表结构以 UAT 实库为准（可能无 tenant_id 列）
INSERT INTO scm_shd (
    id, po_billno, shddh, sh_purchase_num, sh_purchase_name,
    estimated_delivery_date, tjrxm, tjrq
)
VALUES (
    @id_base+601, 'PO-UAT-001', 'SH-UAT-001', 'VEN00060', '供应商A',
    DATE_ADD(@now, INTERVAL 7 DAY), @operator_name, DATE_FORMAT(@now,'%Y-%m-%d')
);

INSERT INTO scm_shdzb (
    id, glid, sh_material_code, sh_material_name,
    sh_delivery_quantity, po_bill, po_line
)
VALUES
(@id_base+701, CAST(@id_base+601 AS CHAR), 'RAW-M1', '原料M1', 200, 'PO-UAT-001', '1'),
(@id_base+702, CAST(@id_base+601 AS CHAR), 'RAW-M2', '原料M2', 100, 'PO-UAT-001', '2');

-- 5.2 采购退货 PurOrdRctMaster（OrdNbr 指向 PurOrd；IQC 不合格样例）
INSERT INTO PurOrdRctMaster (
    Receiver, OrdNbr, Supp, RctDate, Status, Remark, tenant_id, CreateTime
)
VALUES (
    'RT-UAT-001', 'PO-UAT-001', 'VEN00060', @now, 'O',
    CONCAT('UAT IQC退货:', @batch_no), @tenant_id, @now
);
*/


-- -----------------------------------------------------------------------------
-- Section 6：导入后串联验收 SQL（只读，导入完成后执行）
-- -----------------------------------------------------------------------------

-- 6.1 租户合规：不得出现 Demo 租户新数据
SELECT 'crm_seorder_demo' AS chk, COUNT(*) AS cnt
FROM crm_seorder
WHERE tenant_id = @tenant_demo
  AND bill_from LIKE CONCAT('UAT导入:', @batch_no, '%');

-- 6.2 S1→S2：每单明细至少 1 工单
SELECT so.bill_no,
       COUNT(DISTINCT se.Id)  AS line_cnt,
       COUNT(DISTINCT mo.Id)  AS morder_cnt
FROM crm_seorder so
JOIN crm_seorderentry se
  ON se.seorder_id = so.Id AND se.tenant_id = @tenant_aidop AND se.IsDeleted = 0
LEFT JOIN mes_moentry me
  ON me.soentry_id = se.Id AND me.tenant_id = @tenant_aidop AND me.IsDeleted = 0
LEFT JOIN mes_morder mo
  ON mo.Id = me.moentry_moid AND mo.tenant_id = @tenant_aidop AND mo.IsDeleted = 0
WHERE so.tenant_id = @tenant_aidop
  AND so.bill_from LIKE CONCAT('UAT导入:', @batch_no, '%')
GROUP BY so.bill_no;

-- 6.3 S2→S3：工单能找到采购申请
SELECT mo.morder_no, COUNT(pr.voucher) AS pr_cnt
FROM mes_morder mo
LEFT JOIN srm_pr_main pr
  ON pr.pr_purchasenumber = mo.morder_no AND pr.tenant_id = @tenant_aidop
WHERE mo.tenant_id = @tenant_aidop
  AND mo.morder_no LIKE 'WO-UAT-%'
GROUP BY mo.morder_no;

-- 6.4 S3→S4：采购订单能找到发货单（scm_shd 当前无 tenant_id，按 po_billno 关联）
SELECT pom.PurOrd, COUNT(sh.id) AS ship_cnt
FROM PurOrdMaster pom
LEFT JOIN scm_shd sh ON sh.po_billno = pom.PurOrd
WHERE pom.tenant_id = @tenant_aidop
  AND pom.Remark LIKE CONCAT('%', @batch_no, '%')
GROUP BY pom.PurOrd;

-- 6.5 KPI 探针（MDP 跑批后）
SELECT module_code, metric_code, COUNT(*) AS rows_count, MAX(biz_date) AS latest_biz_date
FROM ado_s9_kpi_value_l1_day
WHERE module_code IN ('S1','S2','S3','S4')
  AND is_deleted = 0
GROUP BY module_code, metric_code
ORDER BY module_code, metric_code;