-- =====================================================================
-- CareMeet — notifications add-on schema
-- Apply after caremeet_schema.sql
-- =====================================================================
-- Consultation links reach patients over WhatsApp, SMS or email. Every
-- send is logged: "the patient says they never got the link" is one of
-- the most common support tickets in teleconsultation, and it is only
-- answerable if delivery state is durable.
-- =====================================================================

SET NAMES utf8mb4;

CREATE TABLE notification_templates (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id         BIGINT UNSIGNED NULL,          -- NULL = platform default
  template_code     VARCHAR(60)  NOT NULL,         -- consult_invite | consult_reminder | otp | consult_ready
  channel           ENUM('whatsapp','sms','email') NOT NULL,
  language          VARCHAR(10)  NOT NULL DEFAULT 'en',
  subject           VARCHAR(190) NULL,             -- email only
  body              TEXT NOT NULL,                 -- {{placeholders}}
  /**
   * WhatsApp business-initiated messages MUST use a template pre-approved by
   * Meta. This is the approved template's registered name — the `body` above
   * is only for preview and for the SMS/email fallback.
   */
  wa_template_name  VARCHAR(120) NULL,
  wa_category       ENUM('UTILITY','AUTHENTICATION','MARKETING') NULL DEFAULT 'UTILITY',
  status            ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_template (tenant_id, template_code, channel, language),
  KEY ix_template_lookup (template_code, channel, language, status),
  CONSTRAINT fk_tpl_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE notifications (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id           BIGINT UNSIGNED NOT NULL,
  room_id             BIGINT UNSIGNED NULL,
  participant_id      BIGINT UNSIGNED NULL,
  channel             ENUM('whatsapp','sms','email') NOT NULL,
  template_code       VARCHAR(60) NOT NULL,
  destination         VARCHAR(190) NOT NULL,
  /**
   * The rendered body is stored for support and dispute purposes, but the
   * join URL inside it is a link to a token-minting page, never a token.
   * A message archive must not become a set of working call credentials.
   */
  rendered_body       TEXT NULL,
  status              ENUM('queued','sending','sent','delivered','read','failed','cancelled')
                      NOT NULL DEFAULT 'queued',
  provider            VARCHAR(40) NULL,
  provider_message_id VARCHAR(190) NULL,
  error_code          VARCHAR(60) NULL,
  error_message       VARCHAR(500) NULL,
  attempts            TINYINT UNSIGNED NOT NULL DEFAULT 0,
  scheduled_at        DATETIME NULL,
  sent_at             DATETIME NULL,
  delivered_at        DATETIME NULL,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_notif_queue (status, scheduled_at, attempts),
  KEY ix_notif_room (room_id),
  KEY ix_notif_tenant_date (tenant_id, created_at),
  KEY ix_notif_provider (provider_message_id),
  CONSTRAINT fk_notif_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-tenant provider credentials. Kept out of tenant_settings because these
-- are secrets with a different access profile from branding preferences.
CREATE TABLE tenant_messaging (
  tenant_id            BIGINT UNSIGNED PRIMARY KEY,
  wa_enabled           TINYINT(1) NOT NULL DEFAULT 0,
  wa_phone_number_id   VARCHAR(60)  NULL,
  wa_business_id       VARCHAR(60)  NULL,
  wa_access_token_enc  TEXT NULL,                  -- encrypted at rest
  sms_enabled          TINYINT(1) NOT NULL DEFAULT 0,
  sms_provider         VARCHAR(40)  NULL,
  sms_sender_id        VARCHAR(20)  NULL,          -- 6-char DLT-registered header
  sms_dlt_entity_id    VARCHAR(40)  NULL,          -- TRAI DLT registration
  sms_api_key_enc      TEXT NULL,
  email_enabled        TINYINT(1) NOT NULL DEFAULT 1,
  email_from           VARCHAR(190) NULL,
  email_from_name      VARCHAR(120) NULL,
  updated_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_msg_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- Platform default templates
-- =====================================================================
-- Kept deliberately free of clinical detail. A consultation link arriving on
-- a shared family phone must not disclose why the patient is seeing a doctor.

INSERT INTO notification_templates
  (tenant_id, template_code, channel, language, subject, body, wa_template_name, wa_category)
VALUES
 (NULL, 'consult_invite', 'whatsapp', 'en', NULL,
  'Hello {{patient_name}}, your video consultation with {{doctor_name}} at {{hospital_name}} is scheduled for {{when}}. Join here: {{link}}',
  'consult_invite_en', 'UTILITY'),

 (NULL, 'consult_invite', 'sms', 'en', NULL,
  '{{hospital_name}}: Your video consultation is at {{when}}. Join: {{link}}',
  NULL, NULL),

 (NULL, 'consult_invite', 'email', 'en', 'Your video consultation at {{hospital_name}}',
  'Dear {{patient_name}},\n\nYour video consultation with {{doctor_name}} is scheduled for {{when}}.\n\nJoin using this link: {{link}}\n\nPlease join a few minutes early so you can check your camera and microphone.\n\n{{hospital_name}}',
  NULL, NULL),

 (NULL, 'consult_ready', 'whatsapp', 'en', NULL,
  '{{doctor_name}} is ready for your consultation now. Join here: {{link}}',
  'consult_ready_en', 'UTILITY'),

 (NULL, 'otp', 'sms', 'en', NULL,
  '{{otp}} is your verification code for your consultation at {{hospital_name}}. Valid for 10 minutes. Do not share it.',
  NULL, NULL),

 (NULL, 'otp', 'whatsapp', 'en', NULL,
  '{{otp}} is your verification code. Valid for 10 minutes. Do not share it with anyone.',
  'consult_otp_en', 'AUTHENTICATION');