-- =====================================================================
-- Caresoft Imaging — migration 003
-- P5: report delivery — patient/referrer links, QR verification,
--     WhatsApp and email dispatch, HIS pull-back.
-- Safe to run on a database created from schema.sql + migration 002.
-- =====================================================================
USE `caresoft_imaging`;

ALTER TABLE img_report
  ADD COLUMN verify_code  VARCHAR(24) NULL COMMENT 'short public code printed with the QR' AFTER signature_hash,
  ADD COLUMN delivered_at DATETIME NULL AFTER verified_at,
  ADD UNIQUE KEY uq_verify (verify_code);

ALTER TABLE img_delivery
  ADD COLUMN tenant_id      INT UNSIGNED NULL AFTER id,
  ADD COLUMN report_version INT NOT NULL DEFAULT 1 AFTER report_id,
  ADD COLUMN recipient_kind ENUM('patient','referrer','hospital','partner','other')
             NOT NULL DEFAULT 'patient' AFTER channel,
  ADD COLUMN recipient_name VARCHAR(140) NULL AFTER recipient_kind,
  ADD COLUMN sent_by        INT UNSIGNED NULL AFTER status,
  ADD COLUMN view_count     INT NOT NULL DEFAULT 0 AFTER viewed_at,
  ADD COLUMN last_view_ip   VARCHAR(45) NULL AFTER view_count,
  ADD COLUMN revoked_at     DATETIME NULL AFTER last_view_ip,
  ADD KEY ix_del_tenant (tenant_id, sent_at);