[
  {
    "id": "2006300000000000006",
    "businessName": "SalesOrderList",
    "businessDescription": "历史订单列表：查询所有销售订单，包含客户信息、订单金额、收款状态、交货日期等关键信息",
    "datasourceId": "19",
    "sqlTemplate": "SELECT so.sales_order_id, so.sales_order_number, c.customer_name, so.contract_number, so.order_date_utc, so.delivery_date_utc, so.deal_amount, so.currency_code, so.payment_status, so.stage, so.result, so.transport_mode, so.production_doc_number, so.event_time_utc FROM fact_sales_order so LEFT JOIN dim_customer c ON so.customer_id = c.customer_id AND c.is_current = true ORDER BY so.order_date_utc DESC",
    "parameters": {}
  },
  {
    "id": "2006300000000000007",
    "businessName": "SalesOrderDetail",
    "businessDescription": "订单详情：根据订单编号查询订单详细信息，包含客户信息、销售员、合同、金额、交货日期、收款状态等完整信息",
    "datasourceId": "19",
    "sqlTemplate": "SELECT so.sales_order_id, so.sales_order_number, c.customer_name, c.customer_level, c.phone AS customer_phone, c.email AS customer_email, c.address AS customer_address, p.person_name AS sales_person_name, so.contract_number, ct.contract_name, ct.contract_amount, so.order_date_utc, so.delivery_date_utc, so.deal_amount, so.currency_code, so.fx_rate, so.payment_status, so.transport_mode, so.packaging, so.customs_number, so.production_doc_number, so.stage, so.result, so.event_time_utc FROM fact_sales_order so LEFT JOIN dim_customer c ON so.customer_id = c.customer_id AND c.is_current = true LEFT JOIN dim_person p ON so.sales_person_id = p.person_id AND p.is_current = true LEFT JOIN dim_contract ct ON so.contract_id = ct.contract_id AND ct.is_current = true WHERE so.sales_order_number = '{salesOrderNumber}'",
    "parameters": {
      "salesOrderNumber": {
        "type": "string",
        "description": "销售订单编号",
        "required": true,
        "examples": [
          "SO20251225001"
        ]
      }
    }
  },
  {
    "id": "2006300000000000008",
    "businessName": "DeliveryRiskPrediction",
    "businessDescription": "交付风险预测：基于历史订单的生产周期、物流延误、设备故障等特征，计算延迟概率与红/黄/绿预警等级",
    "datasourceId": "19",
    "sqlTemplate": "WITH global_production AS (SELECT COUNT(*) AS total_wo_count, AVG(CASE WHEN planned_qty > 0 THEN completed_qty / planned_qty ELSE 0 END) AS avg_completion_rate, SUM(CASE WHEN status IN ('OPEN', 'STARTED') THEN 1 ELSE 0 END) AS pending_wo_count, SUM(CASE WHEN status = 'CLOSED' THEN 1 ELSE 0 END) AS closed_wo_count FROM fact_work_order), global_quality AS (SELECT COUNT(*) AS total_inspection_count, SUM(COALESCE(pass_qty, 0)) AS total_pass_qty, SUM(COALESCE(fail_qty, 0)) AS total_fail_qty, CASE WHEN SUM(COALESCE(pass_qty, 0) + COALESCE(fail_qty, 0)) > 0 THEN SUM(COALESCE(pass_qty, 0))::FLOAT / SUM(COALESCE(pass_qty, 0) + COALESCE(fail_qty, 0)) ELSE 1 END AS qc_pass_rate FROM fact_quality_inspection), global_operation AS (SELECT COUNT(*) AS total_task_count, SUM(COALESCE(good_qty, 0)) AS total_good_qty, SUM(COALESCE(bad_qty, 0)) AS total_bad_qty, CASE WHEN SUM(COALESCE(good_qty, 0) + COALESCE(bad_qty, 0)) > 0 THEN SUM(COALESCE(bad_qty, 0))::FLOAT / SUM(COALESCE(good_qty, 0) + COALESCE(bad_qty, 0)) ELSE 0 END AS operation_defect_rate FROM fact_operation_task), customer_shipment AS (SELECT customer_id, COUNT(*) AS shipment_count, SUM(COALESCE(amount, 0)) AS total_shipment_amount FROM fact_sales_shipment GROUP BY customer_id), customer_return AS (SELECT customer_id, COUNT(*) AS return_count, SUM(COALESCE(amount, 0)) AS total_return_amount FROM fact_sales_return GROUP BY customer_id), order_risk AS (SELECT so.sales_order_id, so.sales_order_number, c.customer_name, so.order_date_utc, so.deal_amount, so.payment_status, gp.avg_completion_rate AS production_completion_rate, gp.pending_wo_count, gp.total_wo_count AS work_order_count, gq.qc_pass_rate, gq.total_fail_qty, go.operation_defect_rate, COALESCE(cs.shipment_count, 0) AS shipment_count, COALESCE(cr.return_count, 0) AS return_count, CASE WHEN COALESCE(cs.shipment_count, 0) > 0 THEN COALESCE(cr.return_count, 0)::FLOAT / cs.shipment_count ELSE 0 END AS return_rate FROM fact_sales_order so LEFT JOIN dim_customer c ON so.customer_id = c.customer_id AND c.is_current = true CROSS JOIN global_production gp CROSS JOIN global_quality gq CROSS JOIN global_operation go LEFT JOIN customer_shipment cs ON so.customer_id = cs.customer_id LEFT JOIN customer_return cr ON so.customer_id = cr.customer_id) SELECT sales_order_id, sales_order_number, customer_name, order_date_utc, deal_amount, payment_status, work_order_count, ROUND(production_completion_rate::NUMERIC, 2) AS production_completion_rate, pending_wo_count, ROUND(qc_pass_rate::NUMERIC, 2) AS qc_pass_rate, ROUND(operation_defect_rate::NUMERIC, 4) AS operation_defect_rate, return_count, ROUND(return_rate::NUMERIC, 4) AS return_rate, ROUND((CASE WHEN production_completion_rate < 0.3 THEN 0.30 WHEN production_completion_rate < 0.5 THEN 0.20 WHEN production_completion_rate < 0.8 THEN 0.10 ELSE 0 END + CASE WHEN qc_pass_rate < 0.8 THEN 0.25 WHEN qc_pass_rate < 0.9 THEN 0.15 WHEN qc_pass_rate < 0.95 THEN 0.08 ELSE 0 END + CASE WHEN operation_defect_rate > 0.1 THEN 0.20 WHEN operation_defect_rate > 0.05 THEN 0.12 WHEN operation_defect_rate > 0.02 THEN 0.05 ELSE 0 END + CASE WHEN return_rate > 0.1 THEN 0.15 WHEN return_rate > 0.05 THEN 0.08 WHEN return_rate > 0.02 THEN 0.03 ELSE 0 END + CASE WHEN payment_status = 'UNPAID' THEN 0.10 WHEN payment_status = 'PARTIAL' THEN 0.05 ELSE 0 END)::NUMERIC, 2) AS delay_probability, CASE WHEN (CASE WHEN production_completion_rate < 0.3 THEN 0.30 WHEN production_completion_rate < 0.5 THEN 0.20 WHEN production_completion_rate < 0.8 THEN 0.10 ELSE 0 END + CASE WHEN qc_pass_rate < 0.8 THEN 0.25 WHEN qc_pass_rate < 0.9 THEN 0.15 WHEN qc_pass_rate < 0.95 THEN 0.08 ELSE 0 END + CASE WHEN operation_defect_rate > 0.1 THEN 0.20 WHEN operation_defect_rate > 0.05 THEN 0.12 WHEN operation_defect_rate > 0.02 THEN 0.05 ELSE 0 END + CASE WHEN return_rate > 0.1 THEN 0.15 WHEN return_rate > 0.05 THEN 0.08 WHEN return_rate > 0.02 THEN 0.03 ELSE 0 END + CASE WHEN payment_status = 'UNPAID' THEN 0.10 WHEN payment_status = 'PARTIAL' THEN 0.05 ELSE 0 END) >= 0.50 THEN 'RED' WHEN (CASE WHEN production_completion_rate < 0.3 THEN 0.30 WHEN production_completion_rate < 0.5 THEN 0.20 WHEN production_completion_rate < 0.8 THEN 0.10 ELSE 0 END + CASE WHEN qc_pass_rate < 0.8 THEN 0.25 WHEN qc_pass_rate < 0.9 THEN 0.15 WHEN qc_pass_rate < 0.95 THEN 0.08 ELSE 0 END + CASE WHEN operation_defect_rate > 0.1 THEN 0.20 WHEN operation_defect_rate > 0.05 THEN 0.12 WHEN operation_defect_rate > 0.02 THEN 0.05 ELSE 0 END + CASE WHEN return_rate > 0.1 THEN 0.15 WHEN return_rate > 0.05 THEN 0.08 WHEN return_rate > 0.02 THEN 0.03 ELSE 0 END + CASE WHEN payment_status = 'UNPAID' THEN 0.10 WHEN payment_status = 'PARTIAL' THEN 0.05 ELSE 0 END) >= 0.25 THEN 'YELLOW' ELSE 'GREEN' END AS risk_level, CONCAT_WS(' | ', CASE WHEN production_completion_rate < 0.5 THEN '生产进度滞后' END, CASE WHEN qc_pass_rate < 0.9 THEN '质检通过率低' END, CASE WHEN operation_defect_rate > 0.05 THEN '工序不良率高' END, CASE WHEN return_rate > 0.05 THEN '历史退货率高' END, CASE WHEN payment_status = 'UNPAID' THEN '未付款' END) AS risk_reasons FROM order_risk ORDER BY delay_probability DESC, deal_amount DESC",
    "parameters": {}
  }
]