-- =============================================================================
-- 背景：
--   AssistiveRent.Admin.WebApi.OrderController.CreateOther（建立其他費用-運費）
--   原本用 GetOrderAddress (FirstOrDefault) 取單筆 OrderAddress 後軟刪，
--   若同 OrderId + EN_Type 已存在 >1 筆未刪紀錄，只會刪到其中一筆，
--   接著又新增一筆 → 累積 orphan 紀錄。
--
--   VStoreRentalReport view 在 LEFT JOIN OrderAddress (DeleteDatetime IS NULL)
--   時會把每個 OrderDetail 乘以未刪 OrderAddress 筆數，
--   造成 StoreReportService.GetRentalItems 的 g.Sum(PeriodQty) 重複計算
--   （門市對帳報表-租賃 顯示「期數 2 月」實際只有 1 期的問題）。
--
-- 對應程式修正：
--   AssistiveRent.Admin/WebApi/OrderController.cs CreateOther
--   - 改用 MarkDeleteRangeAsync 把同 OrderId + EN_Type 未刪紀錄「全部」軟刪
--
-- 目前已知影響訂單（執行前可用 _check_duplicate_address.sql 驗證）：
--   2025050700001#001
--   2026012700004
--   2026042400002
--
-- Idempotent：
--   只軟刪「同 (OrderId, EN_Type) 中存在更新一筆（CreateDatetime 更大）」的舊紀錄；
--   重跑不會誤殺。
--
-- 執行紀錄：
--   [ ] Dev DB
--   [ ] Prod DB
-- =============================================================================

BEGIN;

WITH targets AS (
    SELECT oa."Id", oa."OrderId"
    FROM "OrderAddress" oa
    WHERE oa."DeleteDatetime" IS NULL
      AND EXISTS (
          SELECT 1
          FROM "OrderAddress" oa2
          WHERE oa2."DeleteDatetime" IS NULL
            AND oa2."OrderId" = oa."OrderId"
            AND oa2."EN_Type" = oa."EN_Type"
            AND oa2."CreateDatetime" > oa."CreateDatetime"
      )
)
UPDATE "OrderAddress"
   SET "DeleteDatetime" = NOW()
 WHERE "Id" IN (SELECT "Id" FROM targets);

-- 驗證 1：影響訂單範圍應已清乾淨（同 OrderId+EN_Type 只剩 1 筆 active）
SELECT om."OrderNum",
       SUM(CASE WHEN oa."EN_Type" = 100 THEN 1 ELSE 0 END) AS delivery_count,
       SUM(CASE WHEN oa."EN_Type" = 200 THEN 1 ELSE 0 END) AS takeback_count
FROM "OrderMain" om
JOIN "OrderAddress" oa ON oa."OrderId" = om."Id" AND oa."DeleteDatetime" IS NULL
WHERE om."DeleteDatetime" IS NULL
  AND om."OrderNum" IN ('2025050700001#001', '2026012700004', '2026042400002')
GROUP BY om."Id", om."OrderNum"
ORDER BY om."OrderNum";

-- 驗證 2：再全表掃一次，應無 >1 筆同型 active
SELECT om."OrderNum",
       SUM(CASE WHEN oa."EN_Type" = 100 THEN 1 ELSE 0 END) AS delivery_count,
       SUM(CASE WHEN oa."EN_Type" = 200 THEN 1 ELSE 0 END) AS takeback_count
FROM "OrderMain" om
JOIN "OrderAddress" oa ON oa."OrderId" = om."Id" AND oa."DeleteDatetime" IS NULL
WHERE om."DeleteDatetime" IS NULL
GROUP BY om."Id", om."OrderNum"
HAVING SUM(CASE WHEN oa."EN_Type" = 100 THEN 1 ELSE 0 END) > 1
    OR SUM(CASE WHEN oa."EN_Type" = 200 THEN 1 ELSE 0 END) > 1;

COMMIT;
