AiDOPWarehouse/doc/db/mdp/contract_tests/e2e_api_to_std_s5.py

"""S5 端到端：Mock API → stg（Writer 同构 upsert）→ std → 校验 source_biz_key。

不依赖后端进程；直连 Mock + MySQL（环境变量同 _db.py）。
用法：
    set MOCK_TOKEN=uat-mock-token
    python e2e_api_to_std_s5.py
"""
from __future__ import annotations

import json
import sys
from datetime import datetime

import requests

from _db import get_conn

MOCK = "http://127.0.0.1:8018"
TOKEN = "uat-mock-token"
BATCH = f"CT_API_E2E_{datetime.now():%Y%m%d%H%M%S}"


def fetch(path: str) -> list[dict]:
    r = requests.get(
        MOCK.rstrip("/") + path,
        headers={"Authorization": f"Bearer {TOKEN}"},
        timeout=30,
    )
    r.raise_for_status()
    rows = r.json().get("data", {}).get("list")
    if not isinstance(rows, list):
        raise RuntimeError(f"{path} data.list invalid")
    return rows


def upsert_stg(cur, source_table: str, row: dict, biz_expr: list[str]):
    vals = []
    for f in biz_expr:
        if f not in row or row[f] is None or row[f] == "":
            vals = None
            break
        vals.append(str(row[f]))
    biz = "#".join(vals) if vals else str(row.get("bizKey") or row.get("RecID") or row.get("id"))
    rid = str(row.get("bizKey") or row.get("RecID") or row.get("Line") or biz)
    raw = json.dumps(row, ensure_ascii=False)
    cur.execute(
        """
        INSERT INTO mdp_stg_purchase_receipt
          (tenant_id, source_system, source_table, source_row_id, source_biz_key,
           raw_data, sync_batch_id, sync_time, process_status, create_time)
        VALUES (0, 'WMS_API', %s, %s, %s, %s, %s, NOW(), 'PENDING', NOW())
        ON DUPLICATE KEY UPDATE
          source_row_id=VALUES(source_row_id), raw_data=VALUES(raw_data),
          sync_batch_id=VALUES(sync_batch_id), sync_time=VALUES(sync_time),
          process_status='PENDING', update_time=NOW()
        """,
        (source_table, rid, biz, raw, BATCH),
    )


def transform_std(cur):
    cur.execute(
        """
        INSERT INTO mdp_std_purchase_receipt
        (tenant_id, factory_id, source_system, domain, receiver, line, rct_date, supp, sort_name,
         item_num, item_name, item_spec, um, qty_ordered, qty_received, lot_serial, location,
         ord_nbr, ord_line, blanket_line, pur_ord, pur_line, sales_job, address1,
         req, req_line, dop_req, source_biz_key, sync_batch_id, sync_time)
        SELECT
            IFNULL(p.tenant_id, 0), 1, IFNULL(NULLIF(p.source_system,''), 'WMS_API'),
            JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.Domain')),
            JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.Receiver')),
            CAST(JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.Line')) AS SIGNED),
            STR_TO_DATE(NULLIF(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(d.raw_data,'$.RctDate')),'null'),''), '%%Y-%%m-%%d %%H:%%i:%%s'),
            JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.Supp')),
            JSON_UNQUOTE(JSON_EXTRACT(d.raw_data,'$.Supp')),
            JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.ItemNum')), NULL, NULL,
            JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.UM')),
            CAST(JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.QtyOrded')) AS DECIMAL(18,6)),
            CAST(JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.QtyReceived')) AS DECIMAL(18,6)),
            JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.LotSerial')),
            JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.Location')),
            JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.OrdNbr')),
            CAST(JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.OrdLine')) AS SIGNED),
            NULL, NULL, NULL, NULL, NULL,
            NULL, NULL, NULL,
            IFNULL(NULLIF(p.source_biz_key,''), CONCAT(
                JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.Domain')), '#',
                JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.Receiver')), '#',
                JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.Line')))),
            %s, NOW()
        FROM mdp_stg_purchase_receipt p
        INNER JOIN mdp_stg_purchase_receipt d
          ON d.source_table='PurOrdRctMaster'
         AND JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.Domain')) = JSON_UNQUOTE(JSON_EXTRACT(d.raw_data,'$.Domain'))
         AND JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.Receiver')) = JSON_UNQUOTE(JSON_EXTRACT(d.raw_data,'$.Receiver'))
         AND d.source_system='WMS_API'
        WHERE p.source_table='PurOrdRctDetail' AND p.source_system='WMS_API'
          AND JSON_UNQUOTE(JSON_EXTRACT(p.raw_data,'$.RctType'))='rc'
          AND p.sync_batch_id=%s
        ON DUPLICATE KEY UPDATE
            qty_received=VALUES(qty_received), sync_batch_id=VALUES(sync_batch_id),
            sync_time=VALUES(sync_time), update_time=CURRENT_TIMESTAMP
        """,
        (BATCH, BATCH),
    )


def main() -> int:
    details = fetch("/api/receipt")
    masters = fetch("/api/receipt-master")
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            for r in masters:
                upsert_stg(cur, "PurOrdRctMaster", r, ["Domain", "Receiver"])
            for r in details:
                upsert_stg(cur, "PurOrdRctDetail", r, ["Domain", "Receiver", "Line"])
            transform_std(cur)
            cur.execute(
                "SELECT COUNT(*) AS c, COUNT(DISTINCT source_biz_key) AS k "
                "FROM mdp_std_purchase_receipt WHERE sync_batch_id=%s",
                (BATCH,),
            )
            row = cur.fetchone()
        print(f"[PASS] batch={BATCH} std rows={row['c']} keys={row['k']}")
        return 0 if row["c"] > 0 else 1
    except Exception as e:
        print(f"[FAIL] {e}", file=sys.stderr)
        return 1
    finally:
        conn.close()


if __name__ == "__main__":
    sys.exit(main())