ALTER TABLE vehicle_alert_notification
  ADD COLUMN recipient VARCHAR(160) NOT NULL DEFAULT '',
  ADD COLUMN attempt_count INT NOT NULL DEFAULT 1,
  ADD COLUMN provider_message_id VARCHAR(160) NOT NULL DEFAULT '',
  ADD COLUMN delivered_at DATETIME(3) NULL,
  ADD COLUMN last_error VARCHAR(500) NOT NULL DEFAULT '',
  ADD COLUMN last_attempt_at DATETIME(3) NULL,
  ADD COLUMN retry_requested_by VARCHAR(128) NOT NULL DEFAULT '',
  ADD COLUMN retry_requested_at DATETIME(3) NULL,
  ADD KEY idx_alert_notification_delivery (delivery_status, created_at),
  ADD KEY idx_alert_notification_retry (retry_requested_at, id);

CREATE TABLE IF NOT EXISTS vehicle_alert_notification_retry_audit (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  notification_id BIGINT NOT NULL,
  idempotency_key VARCHAR(96) NOT NULL,
  actor VARCHAR(128) NOT NULL,
  reason VARCHAR(200) NOT NULL,
  previous_status VARCHAR(32) NOT NULL,
  next_status VARCHAR(32) NOT NULL,
  attempt_count INT NOT NULL,
  requested_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  UNIQUE KEY uk_alert_notification_retry_key (idempotency_key),
  KEY idx_alert_notification_retry_notification (notification_id, requested_at),
  CONSTRAINT fk_alert_notification_retry_notification
    FOREIGN KEY (notification_id) REFERENCES vehicle_alert_notification(id)
    ON DELETE CASCADE
);