WITH Combined_JSON_Details AS (
    SELECT 
        t.id AS register_id,
        -- 關鍵點：不管 pay_mode 是什麼，功德人代號與姓名一律優先認 JSON 明細裡面的真正主人
        -- 如果 JSON 內沒有（格式B），才自動 fallback 回主表的付款人
        COALESCE(items.[f_num], CASE WHEN t.pay_mode = '個人' THEN t.f_num END) AS final_f_num,
        COALESCE(items.[follower_name], CASE WHEN t.pay_mode = '個人' THEN t.name END) AS final_follower_name,
        items.[pro_order_detail_num],
        items.[reconcile],
        items.[actitem_name],
        items.[activity_name],
        items.[register_date],
        items.[order_no],
        items.[price],
        t.check_date
    FROM [transfer_register] AS t
    -- 動態判定外殼：有 pro_order_detail_items 就走特定路徑，沒有就直接解析陣列根目錄
    CROSS APPLY OPENJSON(t.[draft], CASE WHEN t.[draft] LIKE '%pro_order_detail_items%' THEN '$.pro_order_detail_items' ELSE '$' END)
    WITH (
        [f_num] INT '$.f_num',
        [follower_name] NVARCHAR(50) '$.follower_name',
        [pro_order_detail_num] INT '$.pro_order_detail_num',
        [reconcile] DECIMAL(18,2) '$.reconcile',
        [actitem_name] NVARCHAR(100) '$.actitem_name',
        [activity_name] NVARCHAR(100) '$.activity_name',
        [register_date] DATETIME2 '$.register_date',
        [order_no] VARCHAR(50) '$.order_no',
        [price] DECIMAL(18,2) '$.price'
    ) AS items
),
Unique_Order_Activity AS (
    SELECT pd.num AS pro_order_detail_num, MAX(p.activity_num) AS activity_num
    FROM pro_order_detail pd
    INNER JOIN pro_order p ON pd.order_no = p.order_no
    GROUP BY pd.num
)
SELECT 
    t.id AS [轉帳序號], 
    t.pay_mode AS [沖帳型態],
    convert(varchar(10),t.check_date,120) AS [沖帳日期],
    t.name AS [主要付款人],
    t.phone AS [付款人電話],
    t.amount AS [主單總金額],
    t.status AS [主單狀態],
    t.check_amount AS [已核對金額],
    t.f_num AS [付款人代號],
    
    -- 【這是關聯實體表】
    a.activity_num AS [法會編號],

    -- 【這是明細】：共同沖帳已完美歸回「原來功德人」的資訊，不會被付款人蓋過
    j.final_f_num AS [實際功德人代號],
    j.final_follower_name AS [實際功德人姓名],
    j.pro_order_detail_num AS [訂單明細編號],
    j.order_no AS [訂單編號],
    j.actitem_name AS [牌位/點燈項目],
    j.activity_name AS [法會名稱],
    j.price AS [項目原價],
    j.reconcile AS [本次實際沖帳金額], -- 金額不重複，可安全進行 SUM 統計
    j.register_date AS [登記時間],
    [總金額] =(select sum(price)  from pro_order_detail pd where pd.order_no in (
    select order_no from pro_order p where activity_num=a.activity_num and p.f_num=j.final_f_num
    ) )
FROM [transfer_register] AS t
INNER JOIN Combined_JSON_Details j ON t.id = j.register_id
LEFT JOIN Unique_Order_Activity a ON a.pro_order_detail_num = j.pro_order_detail_num
ORDER BY a.activity_num,j.order_no --t.id, j.pro_order_detail_num; -- 依主單序號排序，讓結構「頭是頭、明細是明細」一目了然