-- ============================================================================= -- MIGRATION: Seed the 4 merchant/auth email templates missing on production -- -- Confirmed via live pm2 logs: "no active/published template found for this -- type" for email_verification, merchant_registration_pending, -- merchant_approved (merchant_rejected shares the same code path, included -- for completeness). These were added to prisma/seed.ts this session but -- prisma:seed itself was never run against production — this is a pure-SQL -- equivalent of that seed step, scoped to just these 4 template types, for -- running directly in psql/pgAdmin without needing ts-node or a repo clone. -- -- 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; slab RECORD; BEGIN FOR slab IN SELECT * FROM (VALUES ('email_verification', 'Email Verification', 'auth', 'Sent right after registration — required before the account can log in. Variables: customerName, verificationUrl', 900, 'Verify your email address', '

Confirm Your Email

Hi {{customerName}},

Thanks for creating an account. You''ll need to verify this email address before you can sign in.

Verify Email Address

This link will expire in 24 hours. If you didn''t create this account, you can safely ignore this email.

' ), ('merchant_registration_pending', 'Merchant Registration — Pending Review', 'merchant', 'Sent right after merchant email verification when the plan requires admin review. Variables: customerName, businessName', 901, 'Your merchant application is under review', '

Application Received

Hi {{customerName}},

Thanks for registering {{businessName}}. Your application is now under review — approval typically takes up to 24 hours. We''ll email you as soon as a decision is made.

' ), ('merchant_approved', 'Merchant Approved', 'merchant', 'Sent when a merchant is approved (by an admin, or automatically for Basic/Premium plans). Variables: customerName, businessName, plan', 902, 'Your merchant account has been approved', '

You''re Approved!

Hi {{customerName}},

{{businessName}} has been approved on the {{plan}} plan. You can now sign in and create your first store.

' ), ('merchant_rejected', 'Merchant Registration Rejected', 'merchant', 'Sent when an admin rejects a merchant application. Variables: customerName, businessName, rejectionReason', 903, 'Update on your merchant application', '

Application Update

Hi {{customerName}},

We were unable to approve {{businessName}} at this time.

Reason: {{rejectionReason}}

You can update your details and resubmit your application for another review.

' ) ) AS t(code, name, category, description, sort_order, subject, body_html) LOOP -- 1. TemplateType (upsert by code) INSERT INTO platform.template_types (id, code, name, category, description, "sortOrder") VALUES (gen_random_uuid()::text, slab.code, slab.name, slab.category, slab.description, slab.sort_order) ON CONFLICT (code) DO UPDATE SET name = EXCLUDED.name, category = EXCLUDED.category, description = EXCLUDED.description RETURNING id INTO v_type_id; -- 2. Template (upsert by storeId + templateTypeId) v_tpl_name := slab.name || ' — 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, slab.subject, slab.body_html) 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; -- 3. TemplateVersion #1 (no natural unique constraint — manual check) 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 025)'); END IF; RAISE NOTICE 'OK: %', slab.code; END LOOP; END $$; COMMIT; -- Verify — all 4 should show up 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 IN ('email_verification', 'merchant_registration_pending', 'merchant_approved', 'merchant_rejected');