-- 052_system_default_templates.sql
--
-- One system default per template TYPE; per-store rows become overrides only.
--
-- Supersedes the approach in 051, which copied all 21 templates onto every
-- store. That worked, but it is the wrong shape: at 1,000 merchants it is
-- 21,000 near-identical rows of HTML, and fixing a typo in a default means
-- rewriting every untouched copy of it.
--
-- The shape now:
--
--   platform.template_types.default*   ← the single system default (21 rows)
--   platform.templates                 ← ONLY where a merchant has customised
--
-- Resolution everywhere is `store override ?? system default`. A merchant with
-- no row is not "unconfigured" and broken — they are on the system default, and
-- their emails send. Editing a default now takes effect for every merchant who
-- has not overridden it, immediately, without touching their data.

BEGIN;

ALTER TABLE platform.template_types
  ADD COLUMN IF NOT EXISTS "defaultName"     TEXT,
  ADD COLUMN IF NOT EXISTS "defaultSubject"  TEXT,
  ADD COLUMN IF NOT EXISTS "defaultBodyHtml" TEXT;

-- ─────────────────────────────────────────────────────────────────────────────
-- 1. Lift the canonical content onto the type
-- ─────────────────────────────────────────────────────────────────────────────
--
-- Source is version 1 of the default store's template — the same "version 1 is
-- the system default" rule TemplatesService.restoreDefault() already used.
-- Falls back to the live row for types seeded before versioning existed.

UPDATE platform.template_types tt
   SET "defaultName"     = COALESCE(src.name,      tt."defaultName"),
       "defaultSubject"  = COALESCE(src.subject,   tt."defaultSubject"),
       "defaultBodyHtml" = COALESCE(src.body_html, tt."defaultBodyHtml")
  FROM (
    SELECT t."templateTypeId",
           COALESCE(v."name",     t."name")     AS name,
           COALESCE(v."subject",  t."subject")  AS subject,
           COALESCE(v."bodyHtml", t."bodyHtml") AS body_html
      FROM platform.templates t
      LEFT JOIN LATERAL (
        SELECT "name", "subject", "bodyHtml"
          FROM platform.template_versions
         WHERE "templateId" = t."id"
         ORDER BY "versionNumber" ASC
         LIMIT 1
      ) v ON TRUE
     WHERE t."storeId" = 'store_demo'
  ) src
 WHERE src."templateTypeId" = tt."id";

-- ─────────────────────────────────────────────────────────────────────────────
-- 2. Drop the copies that carry no merchant intent
-- ─────────────────────────────────────────────────────────────────────────────
--
-- Only rows whose content still EQUALS the system default are removed — a
-- merchant who edited even the subject line keeps their row. `store_demo` keeps
-- its rows too, since it is a real store as well as the content source.

DELETE FROM platform.template_versions v
 USING platform.templates t, platform.template_types tt
 WHERE v."templateId" = t."id"
   AND tt."id" = t."templateTypeId"
   AND t."storeId" <> 'store_demo'
   AND t."name"     IS NOT DISTINCT FROM tt."defaultName"
   AND t."subject"  IS NOT DISTINCT FROM tt."defaultSubject"
   AND t."bodyHtml" IS NOT DISTINCT FROM tt."defaultBodyHtml";

DELETE FROM platform.templates t
 USING platform.template_types tt
 WHERE tt."id" = t."templateTypeId"
   AND t."storeId" <> 'store_demo'
   AND t."name"     IS NOT DISTINCT FROM tt."defaultName"
   AND t."subject"  IS NOT DISTINCT FROM tt."defaultSubject"
   AND t."bodyHtml" IS NOT DISTINCT FROM tt."defaultBodyHtml";

-- ─────────────────────────────────────────────────────────────────────────────
-- 3. Retire the per-store provisioning function
-- ─────────────────────────────────────────────────────────────────────────────
--
-- Nothing should copy defaults onto a store any more; a store with no row
-- inherits the type's default at read and at send time.

DROP FUNCTION IF EXISTS platform.provision_store_templates(TEXT, TEXT);

DO $$
DECLARE with_default INT; overrides INT;
BEGIN
  SELECT count(*) INTO with_default FROM platform.template_types WHERE "defaultBodyHtml" IS NOT NULL;
  SELECT count(*) INTO overrides    FROM platform.templates WHERE "storeId" <> 'store_demo';
  RAISE NOTICE 'system-defaults-052: % type(s) carry a default; % per-store override(s) remain', with_default, overrides;
END $$;

COMMIT;