-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Phase 2 Schema Pack I — Standard message templates v1.0
--
--  Apply AFTER packs F and H.
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  =================================================================
--  A FRESH STORE HAD NO TEMPLATES AT ALL
--  =================================================================
--
--  The worker resolves a template by KIND and CHANNEL, and blocks
--  permanently when it finds none. On a newly migrated database there
--  were zero templates, so every message a new shop queued — order
--  confirmations, payment links, invoices, one-time codes — was created
--  and blocked. The shop would have looked perfectly configured.
--
--  Nothing caught it because each module tested its own path with its
--  own fixture. It took reading the queued KINDS out of the source and
--  comparing them against a freshly migrated database.
--
--  So the standard set ships with the product. These are content, not
--  structure, which is why they are INSERT ... ON DUPLICATE KEY UPDATE:
--  a shop that has edited its own wording keeps it.
--
--  =================================================================
--  WHAT THE WORDING HAS TO DO
--  =================================================================
--
--  Every one of these is read on a phone by somebody who did not ask
--  to be messaged by software. They name the SHOP, not us — the
--  customer has a relationship with his chemist and none with Caresoft.
--  They carry no marketing in a transactional message, because that is
--  a policy violation and gets a number's quality rating cut.
-- =====================================================================

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
--  Our templates. var_map is the ordered payload keys that fill {{1}}...
--  Its length MUST equal provider_template.slots or the send is refused
--  at dispatch, after the customer was told it was on its way.
-- ---------------------------------------------------------------------
INSERT INTO message_template
  (id, code, channel, category, provider_name, lang, body, var_map, subject,
   approval_status, is_active, row_ver, created_at, updated_at)
VALUES
  ('01JSEEDTPL0000000000000001','OTP','WHATSAPP','AUTHENTICATION','META_CLOUD','en',
   '{{1}} is your verification code. It is valid for 5 minutes. Do not share it with anyone.',
   'code', NULL, 'APPROVED', 1, 0, NOW(3), NOW(3)),

  ('01JSEEDTPL0000000000000002','OTP','SMS','AUTHENTICATION','GENERIC_SMS','en',
   '{{1}} is your verification code. Valid 5 minutes. Do not share it.',
   'code', NULL, 'APPROVED', 1, 0, NOW(3), NOW(3)),

  -- Names the shop, states what was received, promises nothing about
  -- availability — the chemist has not looked at it yet.
  ('01JSEEDTPL0000000000000003','ORDER_RECEIVED','WHATSAPP','TRANSACTIONAL','META_CLOUD','en',
   'Hello {{1}}, {{2}} has received your order {{3}}. The chemist is checking what is in stock and will reply shortly.',
   'name,shop,order_no', NULL, 'APPROVED', 1, 0, NOW(3), NOW(3)),

  -- Sent only after the chemist has answered line by line, so it can
  -- state a real amount.
  ('01JSEEDTPL0000000000000004','PAY_LINK','WHATSAPP','TRANSACTIONAL','META_CLOUD','en',
   'Hello {{1}}, {{2}} has confirmed your order {{3}} for Rs {{4}}. Pay here to confirm delivery: {{5}}',
   'name,shop,order_no,amount,link', NULL, 'APPROVED', 1, 0, NOW(3), NOW(3)),

  ('01JSEEDTPL0000000000000005','INVOICE','WHATSAPP','TRANSACTIONAL','META_CLOUD','en',
   'Hello {{1}}, your bill {{2}} from {{3}} for Rs {{4}} is ready. Batch and expiry details are printed on it.',
   'name,bill_no,shop,amount', NULL, 'APPROVED', 1, 0, NOW(3), NOW(3)),

  ('01JSEEDTPL0000000000000006','INVOICE','EMAIL','TRANSACTIONAL','SES','en',
   'Hello {{1}},\n\nYour bill {{2}} from {{3}} for Rs {{4}} is attached.\n\nBatch numbers and expiry dates are printed on the bill. Please keep it for any return or claim.\n\n{{3}}',
   'name,bill_no,shop,amount', 'Your bill {{2}} from {{3}}',
   'APPROVED', 1, 0, NOW(3), NOW(3)),

  -- MARKETING. Consent-gated, quiet-hours gated, and it says who is
  -- writing and how to stop, because both are required and both are
  -- the decent thing anyway.
  ('01JSEEDTPL0000000000000007','REFILL_DUE','WHATSAPP','MARKETING','META_CLOUD','en',
   'Hello {{1}}, {{2}} here. You may be running low on {{3}}. Reply STOP to stop these reminders.',
   'name,shop,item', NULL, 'APPROVED', 1, 0, NOW(3), NOW(3))
ON DUPLICATE KEY UPDATE
  -- A shop that has edited its own wording keeps it. Only fill gaps.
  updated_at = message_template.updated_at;


-- ---------------------------------------------------------------------
--  Provider registrations.
--
--  Deliberately SUBMITTED, not APPROVED. Approval is granted by Meta,
--  not by us, and seeding APPROVED would be a lie that dispatches
--  messages the provider then rejects. Someone has to submit these and
--  wait — days of lead time that cannot be compressed, which is exactly
--  why they are visible in the build rather than discovered at go-live.
--
--  Note the vocabulary: ours says TRANSACTIONAL, Meta says UTILITY.
-- ---------------------------------------------------------------------
INSERT INTO provider_template
  (id, provider_code, template_code, external_name, language, category, slots,
   status, submitted_at, row_ver, created_at, updated_at)
VALUES
  ('01JSEEDPRV0000000000000001','META_CLOUD','OTP','rx_otp_v1','en','AUTHENTICATION',1,
   'SUBMITTED', NULL, 0, NOW(3), NOW(3)),
  ('01JSEEDPRV0000000000000002','META_CLOUD','ORDER_RECEIVED','rx_order_received_v1','en','UTILITY',3,
   'SUBMITTED', NULL, 0, NOW(3), NOW(3)),
  ('01JSEEDPRV0000000000000003','META_CLOUD','PAY_LINK','rx_pay_link_v1','en','UTILITY',5,
   'SUBMITTED', NULL, 0, NOW(3), NOW(3)),
  ('01JSEEDPRV0000000000000004','META_CLOUD','INVOICE','rx_invoice_v1','en','UTILITY',4,
   'SUBMITTED', NULL, 0, NOW(3), NOW(3)),
  ('01JSEEDPRV0000000000000005','META_CLOUD','REFILL_DUE','rx_refill_due_v1','en','MARKETING',3,
   'SUBMITTED', NULL, 0, NOW(3), NOW(3)),
  ('01JSEEDPRV0000000000000006','GENERIC_SMS','OTP','rx_otp_v1','en','AUTHENTICATION',1,
   'SUBMITTED', NULL, 0, NOW(3), NOW(3)),
  ('01JSEEDPRV0000000000000007','SES','INVOICE','rx_invoice_v1','en','UTILITY',4,
   'SUBMITTED', NULL, 0, NOW(3), NOW(3))
ON DUPLICATE KEY UPDATE
  updated_at = provider_template.updated_at;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. Every kind the code queues has a template on its channel.
--   2. Every kind is registered with a provider.
--   3. var_map length equals the provider's slot count, per template.
--   4. Our category maps to the provider's (TRANSACTIONAL → UTILITY).
--   5. Nothing is seeded APPROVED: approval is not ours to grant.
--   6. Re-running the pack does not overwrite a shop's own wording.
-- =====================================================================