-- Apply once after migration 018. SMTP credentials remain in private config only.
CREATE TABLE outbound_mail_queue (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NULL,
 communication_id BIGINT UNSIGNED NULL,
 booking_id BIGINT UNSIGNED NULL,
 reminder_id BIGINT UNSIGNED NULL,
 contact_id BIGINT UNSIGNED NULL,
 attendee_invitation_id BIGINT UNSIGNED NULL,
 guest_invitation_id BIGINT UNSIGNED NULL,
 recipient VARCHAR(254) NOT NULL,
 recipient_key VARCHAR(254) GENERATED ALWAYS AS (LOWER(TRIM(recipient))) STORED,
 subject VARCHAR(250) NOT NULL,
 body_html MEDIUMTEXT NOT NULL,
 attachments_json MEDIUMTEXT NULL,
 message_kind VARCHAR(80) NOT NULL DEFAULT 'general',
 status ENUM('queued','sending','sent','failed','bounced') NOT NULL DEFAULT 'queued',
 attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
 max_attempts TINYINT UNSIGNED NOT NULL DEFAULT 5,
 next_attempt_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 lease_token CHAR(32) NULL,
 locked_until DATETIME NULL,
 sent_at DATETIME NULL,
 last_error_code VARCHAR(40) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_mail_ready(status,next_attempt_at,id),
 KEY idx_mail_tenant_recipient(unit_id,recipient_key,id),
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE SET NULL,
 FOREIGN KEY(communication_id) REFERENCES communication_log(id) ON DELETE SET NULL,
 FOREIGN KEY(booking_id) REFERENCES bookings(id) ON DELETE SET NULL,
 FOREIGN KEY(reminder_id) REFERENCES reminders(id) ON DELETE SET NULL,
 FOREIGN KEY(contact_id) REFERENCES contact_messages(id) ON DELETE SET NULL,
 FOREIGN KEY(attendee_invitation_id) REFERENCES attendee_invitations(id) ON DELETE SET NULL,
 FOREIGN KEY(guest_invitation_id) REFERENCES guest_invitations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE attendee_invitations MODIFY status ENUM('queued','sent','failed') NOT NULL;
ALTER TABLE guest_invitations MODIFY status ENUM('queued','sent','failed') NOT NULL;

INSERT IGNORE INTO email_templates(unit_id,template_key,display_name,subject_template,body_template,enabled)
SELECT u.id,'emergency_contact','Emergency contact message','Emergency contact for {{lodge_name}}','<p>From {{sender_name}} ({{sender_email}})</p><p>{{message}}</p>',1 FROM units u;