-- Consented returning-guest directory and one-per-meeting invitation tracking.

ALTER TABLE guests
  ADD COLUMN email VARCHAR(254) NULL AFTER surname,
  ADD COLUMN retention_consent TINYINT(1) NOT NULL DEFAULT 0 AFTER email,
  ADD COLUMN guest_contact_id BIGINT UNSIGNED NULL AFTER retention_consent,
  ADD INDEX idx_guest_email (email),
  ADD INDEX idx_guest_contact (guest_contact_id);

CREATE TABLE guest_contacts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sponsor_email VARCHAR(254) NOT NULL,
  salutation VARCHAR(30) NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  surname VARCHAR(100) NOT NULL,
  email VARCHAR(254) NOT NULL,
  masonic_rank VARCHAR(150) NULL,
  provincial_rank VARCHAR(150) NULL,
  grand_rank VARCHAR(150) NULL,
  lodge_name VARCHAR(150) NULL,
  lodge_number VARCHAR(30) NULL,
  dietary_requirements TEXT NULL,
  allergy_details TEXT NULL,
  consented_at DATETIME NOT NULL,
  last_booked_at DATETIME NOT NULL,
  consent_withdrawn_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_guest_contact_sponsor (sponsor_email,email),
  INDEX idx_guest_contact_email (email),
  INDEX idx_guest_contact_consent (consent_withdrawn_at,last_booked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE guest_invitations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  guest_contact_id BIGINT UNSIGNED NOT NULL,
  meeting_id BIGINT UNSIGNED NOT NULL,
  status ENUM('sent','failed') NOT NULL,
  sent_at DATETIME NULL,
  failure_message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_guest_meeting_invitation (guest_contact_id,meeting_id),
  CONSTRAINT fk_guest_invitation_contact FOREIGN KEY (guest_contact_id) REFERENCES guest_contacts(id) ON DELETE CASCADE,
  CONSTRAINT fk_guest_invitation_meeting FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE guests
  ADD CONSTRAINT fk_guest_contact FOREIGN KEY (guest_contact_id) REFERENCES guest_contacts(id) ON DELETE SET NULL;

INSERT INTO email_templates(template_key,display_name,subject_template,body_template,enabled)
VALUES(
  'guest_meeting_invitation',
  'Previous guest meeting invitation',
  '{{lodge_name}} invitation — {{meeting_title}}',
  'Dear {{name}},\n\nYou previously attended as a guest and agreed that the Lodge could retain your details and contact you about future meetings.\n\nThe summons for {{meeting_title}} on {{meeting_date}} at {{meeting_time}} is now available.\n\nView the summons: {{summons_url}}\nBook for this meeting: {{booking_url}}\n\nIf you no longer wish to receive these invitations, please contact the Lodge using the website contact form.',
  1
)
ON DUPLICATE KEY UPDATE display_name=VALUES(display_name);

INSERT INTO schema_versions(version_number,version_name)
VALUES(26,'Consented guest directory and meeting invitations')
ON DUPLICATE KEY UPDATE version_name=VALUES(version_name);