-- 051_provision_store_email_templates.sql
--
-- Gives every store its own set of email templates.
--
-- `platform.templates` is per store — `@@unique([storeId, templateTypeId])` —
-- but every seed migration so far hardcoded `v_store_id := 'store_demo'`, and
-- `MerchantService.createStore()` never provisioned any. So exactly one store on
-- this database had templates and every other had none.
--
-- What a merchant saw: every template listed as "Not Configured", an empty
-- preview pane, and Edit/Publish doing nothing — because there was no row to
-- edit or publish. Nothing was broken in the UI; there was simply no data.
--
-- The provisioning is a FUNCTION rather than a one-off script, because
-- createStore() calls the same function inside its transaction. One definition
-- of "what a new store starts with", used by both the backfill and every store
-- created from now on.

BEGIN;

/**
 * Copies the canonical template set onto one store.
 *
 * The canonical content is the FIRST version of the corresponding template on
 * the default store — the same "version 1 is the system default" rule that
 * TemplatesService.restoreDefault() already relies on. Reading version 1 rather
 * than the live row matters: the default store's templates can be edited like
 * anyone else's, and a new store must not inherit somebody's experiment.
 *
 * Idempotent — a store that already has a template for a type is left alone, so
 * this never overwrites a merchant's own wording.
 */
CREATE OR REPLACE FUNCTION platform.provision_store_templates(
  p_store_id TEXT,
  p_actor    TEXT DEFAULT 'system'
) RETURNS INTEGER
LANGUAGE plpgsql
AS $fn$
DECLARE
  v_source_store TEXT := 'store_demo';
  v_created      INTEGER := 0;
  v_template_id  TEXT;
  src            RECORD;
BEGIN
  FOR src IN
    SELECT t."templateTypeId",
           -- Version 1 where it exists, else the template row itself; a
           -- template seeded before versioning existed has no version 1.
           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" = v_source_store
       AND NOT EXISTS (
         SELECT 1 FROM platform.templates existing
          WHERE existing."storeId" = p_store_id
            AND existing."templateTypeId" = t."templateTypeId"
       )
  LOOP
    v_template_id := gen_random_uuid()::TEXT;

    INSERT INTO platform.templates
      ("id", "storeId", "templateTypeId", "name", "subject", "bodyHtml",
       "isActive", "isPublished", "createdBy", "createdOn", "updatedBy", "updatedOn")
    VALUES
      (v_template_id, p_store_id, src."templateTypeId", src.name, src.subject, src.body_html,
       TRUE, TRUE, p_actor, now(), p_actor, now());

    -- Version 1, so "Restore default" works on a brand-new store too. Without
    -- it, restoreDefault() throws "No default version found to restore".
    INSERT INTO platform.template_versions
      ("id", "templateId", "versionNumber", "name", "subject", "bodyHtml",
       "isActive", "isPublished", "action", "comment", "createdBy", "createdOn")
    VALUES
      (gen_random_uuid()::TEXT, v_template_id, 1, src.name, src.subject, src.body_html,
       TRUE, TRUE, 'seeded', 'Provisioned with the store', p_actor, now());

    v_created := v_created + 1;
  END LOOP;

  RETURN v_created;
END;
$fn$;

-- ─────────────────────────────────────────────────────────────────────────────
-- Backfill every store that has none
-- ─────────────────────────────────────────────────────────────────────────────

DO $$
DECLARE
  s        RECORD;
  n        INTEGER;
  total    INTEGER := 0;
  stores   INTEGER := 0;
BEGIN
  FOR s IN
    SELECT "id", "name" FROM store.stores
     WHERE "deletedAt" IS NULL AND "id" <> 'store_demo'
  LOOP
    n := platform.provision_store_templates(s."id", 'system:provision-051');
    IF n > 0 THEN
      stores := stores + 1;
      total  := total + n;
    END IF;
  END LOOP;

  RAISE NOTICE 'provision-051: added % template(s) across % store(s)', total, stores;
END $$;

COMMIT;