-- Add email delivery status tracking for the first registration QR email.
-- Safe to run multiple times on MySQL/MariaDB.

DELIMITER $$

DROP PROCEDURE IF EXISTS add_attendee_qr_send_status_columns $$

CREATE PROCEDURE add_attendee_qr_send_status_columns()
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'attendee'
      AND COLUMN_NAME = 'qrSendStatus'
  ) THEN
    ALTER TABLE attendee
      ADD COLUMN qrSendStatus varchar(20) NOT NULL DEFAULT 'pending' AFTER qrSentAt;
  END IF;

  IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'attendee'
      AND COLUMN_NAME = 'qrSendError'
  ) THEN
    ALTER TABLE attendee
      ADD COLUMN qrSendError text NULL AFTER qrSendStatus;
  END IF;
END $$

CALL add_attendee_qr_send_status_columns() $$

DROP PROCEDURE IF EXISTS add_attendee_qr_send_status_columns $$

DELIMITER ;

UPDATE attendee
SET qrSendStatus = CASE
  WHEN qrSentAt IS NOT NULL THEN 'sent'
  WHEN qrSendStatus = 'failed' THEN 'failed'
  ELSE 'pending'
END
WHERE qrSendStatus IS NULL
   OR qrSendStatus = ''
   OR qrSendStatus = 'sending';