ALTER TABLE "ShopeeOrderItem" ADD COLUMN "hppAwalSnapshot" REAL;
ALTER TABLE "ShopeeOrderItem" ADD COLUMN "hppAwalSnapshotAt" DATETIME;
ALTER TABLE "ShopeeOrderItem" ADD COLUMN "hppFinalSnapshot" REAL;
ALTER TABLE "ShopeeOrderItem" ADD COLUMN "hppFinalSnapshotAt" DATETIME;

-- Freeze known current costs for historical rows. Missing costs remain
-- unsnapshotted so entering an HPP for the first time can fill them later.
UPDATE "ShopeeOrderItem"
SET "hppAwalSnapshot" = COALESCE(
      (SELECT p."hppAwal" FROM "ShopeeProductSku" p WHERE p."shopId" = "ShopeeOrderItem"."shopId" AND p."itemId" = "ShopeeOrderItem"."itemId" AND p."modelId" = COALESCE("ShopeeOrderItem"."modelId", '0')),
      (SELECT p."hpp" FROM "ShopeeProductSku" p WHERE p."shopId" = "ShopeeOrderItem"."shopId" AND p."itemId" = "ShopeeOrderItem"."itemId" AND p."modelId" = COALESCE("ShopeeOrderItem"."modelId", '0'))
    ),
    "hppAwalSnapshotAt" = CURRENT_TIMESTAMP
WHERE EXISTS (
  SELECT 1 FROM "ShopeeProductSku" p
  WHERE p."shopId" = "ShopeeOrderItem"."shopId"
    AND p."itemId" = "ShopeeOrderItem"."itemId"
    AND p."modelId" = COALESCE("ShopeeOrderItem"."modelId", '0')
    AND (p."hppAwal" IS NOT NULL OR p."hpp" IS NOT NULL)
);

UPDATE "ShopeeOrderItem"
SET "hppFinalSnapshot" = COALESCE(
      (SELECT p."hppFinal" FROM "ShopeeProductSku" p WHERE p."shopId" = "ShopeeOrderItem"."shopId" AND p."itemId" = "ShopeeOrderItem"."itemId" AND p."modelId" = COALESCE("ShopeeOrderItem"."modelId", '0')),
      (SELECT p."hpp" FROM "ShopeeProductSku" p WHERE p."shopId" = "ShopeeOrderItem"."shopId" AND p."itemId" = "ShopeeOrderItem"."itemId" AND p."modelId" = COALESCE("ShopeeOrderItem"."modelId", '0'))
    ),
    "hppFinalSnapshotAt" = CURRENT_TIMESTAMP
WHERE EXISTS (
  SELECT 1 FROM "ShopeeProductSku" p
  WHERE p."shopId" = "ShopeeOrderItem"."shopId"
    AND p."itemId" = "ShopeeOrderItem"."itemId"
    AND p."modelId" = COALESCE("ShopeeOrderItem"."modelId", '0')
    AND (p."hppFinal" IS NOT NULL OR p."hpp" IS NOT NULL)
);