-- Staff notification support: seeds notification_templates (schema.sql
-- creates the table but leaves it empty) and adds a delivery log so "did
-- the duty officer actually get notified" is answerable, not assumed.
-- Applied AFTER schema.sql; additive, non-destructive.

-- schema.sql declares template_key VARCHAR(80) NOT NULL UNIQUE — unique across
-- the WHOLE table, not per (key, channel). So one logical event needs one
-- distinct key per channel (matching the schema comment's own example style,
-- 'new_report_alert', 'case_assignment', ...), not one key with two rows.
INSERT INTO notification_templates (template_key, channel, subject, body_template, is_active) VALUES
  ('urgent_report_alert_email', 'email',
   'URGENT: safeguarding report {{case_reference}} needs review',
   'A safeguarding report has been submitted and flagged as urgent (current risk: {{risk}}).\n\nCase reference: {{case_reference}}\nCategory: {{category}}\nChannel: {{channel}}\n\nThis message intentionally does not include the report description. Please log in to the safeguarding admin panel to review it:\n{{admin_url}}\n\nThis is an automated notification. Do not reply to this email.',
   1),
  ('urgent_report_alert_whatsapp', 'whatsapp',
   NULL,
   'URGENT safeguarding report {{case_reference}} ({{category}}, risk: {{risk}}) needs review. Log in to the admin panel to view details. This is an automated message.',
   1);

-- One row per (report, recipient, channel) attempt. Content is NOT stored
-- here — only whether it was sent and, on failure, a short reason — so
-- this table itself never becomes a second place case data could leak from.
--
-- For an anonymous report not yet linked to a case, correlation is by
-- anonymous_report_id (an internal, non-secret primary key — the same
-- thing audit_logs already references for these) rather than the
-- reporter-facing case_reference string, which by design is shown once
-- and never persisted anywhere after that, including here.
CREATE TABLE IF NOT EXISTS notification_deliveries (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  template_key          VARCHAR(80) NOT NULL,
  channel               ENUM('whatsapp','email','sms') NOT NULL,
  case_id               BIGINT UNSIGNED NULL,   -- safeguarding_cases.id, when this alert is about a linked case
  anonymous_report_id   BIGINT UNSIGNED NULL,   -- anonymous_reports.id, when not yet linked to a case
  recipient_user_id     BIGINT UNSIGNED NULL,
  status                ENUM('sent','failed') NOT NULL,
  failure_reason        VARCHAR(255) NULL,
  created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notifdeliv_user FOREIGN KEY (recipient_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_notifdeliv_case FOREIGN KEY (case_id) REFERENCES safeguarding_cases(id) ON DELETE SET NULL,
  CONSTRAINT fk_notifdeliv_anon FOREIGN KEY (anonymous_report_id) REFERENCES anonymous_reports(id) ON DELETE SET NULL,
  INDEX idx_notifdeliv_case (case_id),
  INDEX idx_notifdeliv_anon (anonymous_report_id),
  INDEX idx_notifdeliv_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;