-- ─────────────────────────────────────────────────────────────────────────────
-- 056 — Put the invoice link into UNTOUCHED store copies of "Order Delivered"
--
-- 055 updated the system default, which is what a store with no template rows
-- of its own renders. But a published store override wins over the default
-- (template-render.service.ts findTemplate), so stores that still carry a copy
-- got the delivery email with no invoice link at all — which is what was
-- reported from production.
--
-- Those copies are not customisations. They are leftovers from migration 051,
-- which created a full set of template rows per store before 052 replaced that
-- approach with defaults on TemplateType. On the store checked they are
-- byte-identical to the default, createdBy 'system', updatedBy 'system' —
-- nobody ever edited them.
--
-- So this patches only the rows that are STILL byte-identical to the default.
-- A merchant who has genuinely edited their template has a body that differs,
-- and is left completely alone: appending a button to a design someone chose is
-- not this migration's business. Those stores are listed at the end so they can
-- be handled deliberately.
-- ─────────────────────────────────────────────────────────────────────────────

BEGIN;

WITH block AS (
  SELECT
    '<p style="margin:0 0 12px;color:#475569;font-size:16px;line-height:1.65;text-align:center;">Your tax invoice for this order is ready.</p>' ||
    '<p style="margin:0 0 8px;text-align:center;"><a href="{{invoiceUrl}}" style="display:inline-block;padding:12px 28px;background:#4f46e5;color:#ffffff;font-size:15px;font-weight:600;text-decoration:none;border-radius:8px;">Download invoice (PDF)</a></p>' ||
    '<p style="margin:0 0 18px;color:#94a3b8;font-size:13px;line-height:1.6;text-align:center;">This link works for 30 days. After that, sign in to your account to download it any time.</p>'
    AS html
)
UPDATE platform.templates t
   SET "bodyHtml"  = t."bodyHtml" || (SELECT html FROM block),
       "updatedBy" = 'migration_056',
       "updatedOn" = now()
  FROM platform.template_types tt, block b
 WHERE tt.id = t."templateTypeId"
   AND tt.code = 'order_delivered'
   -- Never twice.
   AND t."bodyHtml" NOT LIKE '%{{invoiceUrl}}%'
   -- Only a copy nobody has touched: identical to the default with 055's block
   -- removed. Comparing against the live default rather than a hard-coded
   -- string means this stays correct if the default is ever revised again.
   AND btrim(t."bodyHtml") = btrim(replace(tt."defaultBodyHtml", b.html, ''));

COMMIT;

-- ── Anything left behind ────────────────────────────────────────────────────
-- Rows this deliberately did NOT touch: real customisations. Each of these
-- stores keeps sending a delivery email with no invoice link until someone adds
-- {{invoiceUrl}} to their template in the admin Templates screen.
SELECT t."storeId",
       t."updatedBy",
       t."updatedOn",
       'customised — add {{invoiceUrl}} by hand' AS action
  FROM platform.templates t
  JOIN platform.template_types tt ON tt.id = t."templateTypeId"
 WHERE tt.code = 'order_delivered'
   AND t."isActive"
   AND t."isPublished"
   AND t."bodyHtml" NOT LIKE '%{{invoiceUrl}}%'
 ORDER BY t."storeId";