ALTER TABLE scratchcards
ADD COLUMN send_result_email TINYINT(1) NOT NULL DEFAULT 0,
ADD COLUMN email_from_name VARCHAR(150) DEFAULT NULL,
ADD COLUMN email_from_address VARCHAR(190) DEFAULT NULL,
ADD COLUMN email_reply_to VARCHAR(190) DEFAULT NULL,
ADD COLUMN email_subject VARCHAR(190) DEFAULT NULL,
ADD COLUMN email_body_html MEDIUMTEXT DEFAULT NULL;

CREATE TABLE email_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    scratchcard_id INT NOT NULL,
    play_id BIGINT DEFAULT NULL,
    recipient_email VARCHAR(190) NOT NULL,
    subject VARCHAR(190) NOT NULL,
    delivery_status ENUM('sent','failed','test_sent','test_failed') NOT NULL DEFAULT 'sent',
    error_message TEXT DEFAULT NULL,
    sent_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (scratchcard_id) REFERENCES scratchcards(id) ON DELETE CASCADE,
    FOREIGN KEY (play_id) REFERENCES plays(id) ON DELETE SET NULL
);