-- WhatsApp reporting bot support tables. Additive: applied AFTER schema.sql,
-- schema.sql itself is untouched.
--
-- whatsapp_sessions: one row per in-progress conversation. Everything the
-- reporter has said so far lives in data_ciphertext (AES-256-GCM), the
-- number needed to reply lives in number_ciphertext, and BOTH are wiped the
-- moment the report is submitted, cancelled, or the session expires. The
-- row itself is deleted at that point too.
CREATE TABLE IF NOT EXISTS whatsapp_sessions (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sender_hash       CHAR(64) NOT NULL UNIQUE,          -- peppered SHA-256 of the number
  number_ciphertext TEXT NOT NULL,                     -- encrypted; needed only to reply mid-conversation
  state             VARCHAR(40) NOT NULL,
  data_ciphertext   MEDIUMTEXT NULL,                   -- encrypted JSON of answers so far
  created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  expires_at        TIMESTAMP NOT NULL,
  INDEX idx_wasess_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- whatsapp_processed_events: idempotency (UltraMsg may deliver the same
-- webhook more than once) and per-sender rate limiting. Holds no message
-- content and no phone number: only a peppered hash of the provider message id
-- (raw ids embed the sender's number) and a peppered hash of the sender. Purged after 24h by cron/purge-whatsapp.php.
CREATE TABLE IF NOT EXISTS whatsapp_processed_events (
  message_id    CHAR(64) NOT NULL PRIMARY KEY,           -- peppered SHA-256 of the provider id
  sender_hash   CHAR(64) NOT NULL,
  processed_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_waev_sender (sender_hash, processed_at),
  INDEX idx_waev_time (processed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;