-- =============================================================================
-- MIGRATION: Seed the "merchant_application_received" admin-notification
-- email template
--
-- New template type — added alongside the admin-notification-emails
-- constants file (api/src/app/notifications/admin-notification-emails.
-- constants.ts) so platform reviewers get pinged the moment a merchant
-- registers on a plan that needs review (never sent for an auto-approved
-- plan). Same pure-SQL, pgAdmin-runnable pattern as migration 025.
--
-- Run: idempotent — safe to re-execute (upserts TemplateType by code,
-- Template by storeId+templateTypeId, TemplateVersion #1 by templateId).
-- =============================================================================
BEGIN;
DO $$
DECLARE
v_store_id text := 'store_demo';
v_type_id text;
v_tpl_id text;
v_tpl_name text;
v_tpl_subject text;
v_tpl_body text;
v_version_id text;
BEGIN
INSERT INTO platform.template_types (id, code, name, category, description, "sortOrder")
VALUES (
gen_random_uuid()::text,
'merchant_application_received',
'Merchant Application Received (Admin Notification)',
'merchant',
'Sent to the hardcoded platform-review recipients right after a merchant registers on a plan that needs review — never sent for an auto-approved plan. Variables: businessName, contactName, contactEmail, contactPhone, plan, reviewUrl',
904
)
ON CONFLICT (code) DO UPDATE SET name = EXCLUDED.name, category = EXCLUDED.category, description = EXCLUDED.description
RETURNING id INTO v_type_id;
v_tpl_name := 'Merchant Application Received (Admin Notification) — Default';
INSERT INTO platform.templates (id, "storeId", "templateTypeId", name, subject, "bodyHtml")
VALUES (
gen_random_uuid()::text, v_store_id, v_type_id, v_tpl_name,
'New merchant application: {{businessName}}',
'New Merchant Application
A new merchant has applied and is waiting for review:
{{businessName}}
Contact: {{contactName}} ({{contactEmail}})
Phone: {{contactPhone}}
Plan: {{plan}}
'
)
ON CONFLICT ("storeId", "templateTypeId") DO UPDATE SET subject = EXCLUDED.subject, "bodyHtml" = EXCLUDED."bodyHtml"
RETURNING id, name, subject, "bodyHtml" INTO v_tpl_id, v_tpl_name, v_tpl_subject, v_tpl_body;
SELECT id INTO v_version_id FROM platform.template_versions
WHERE "templateId" = v_tpl_id AND "versionNumber" = 1;
IF v_version_id IS NOT NULL THEN
UPDATE platform.template_versions
SET name = v_tpl_name, subject = v_tpl_subject, "bodyHtml" = v_tpl_body
WHERE id = v_version_id;
ELSE
INSERT INTO platform.template_versions
(id, "templateId", "versionNumber", name, subject, "bodyHtml", "isActive", "isPublished", action, comment)
VALUES
(gen_random_uuid()::text, v_tpl_id, 1, v_tpl_name, v_tpl_subject, v_tpl_body, true, true, 'created', 'Initial system default (backfilled via manual migration 026)');
END IF;
RAISE NOTICE 'OK: merchant_application_received';
END $$;
COMMIT;
-- Verify — should show active + published
SELECT tt.code, t."isActive", t."isPublished"
FROM platform.template_types tt
JOIN platform.templates t ON t."templateTypeId" = tt.id AND t."storeId" = 'store_demo'
WHERE tt.code = 'merchant_application_received';