-- Upgrade 027: optional consent for the main booking attendee.
-- Run once after upgrade 026.

ALTER TABLE bookings
  ADD COLUMN future_contact_consent TINYINT(1) NOT NULL DEFAULT 0 AFTER phone;

CREATE TABLE attendee_contacts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  salutation VARCHAR(30) NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  surname VARCHAR(100) NOT NULL,
  email VARCHAR(254) NOT NULL,
  phone VARCHAR(30) 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,
  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_attendee_contact_email (email),
  INDEX idx_attendee_contact_consent (consent_withdrawn_at,last_booked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE attendee_invitations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  attendee_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_attendee_meeting_invitation (attendee_contact_id,meeting_id),
  CONSTRAINT fk_attendee_invitation_contact FOREIGN KEY (attendee_contact_id) REFERENCES attendee_contacts(id) ON DELETE CASCADE,
  CONSTRAINT fk_attendee_invitation_meeting FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO email_templates(template_key,display_name,subject_template,body_template,enabled)
VALUES(
  'attendee_meeting_invitation',
  'Previous attendee meeting invitation',
  '{{lodge_name}} invitation — {{meeting_title}}',
  'Dear {{name}},\n\nYou previously booked with the Lodge and agreed that it could retain your contact details and tell you when future meeting summonses or invitations are published.\n\nThe summons or invitation for {{meeting_title}} on {{meeting_date}} at {{meeting_time}} is now available.\n\nView it here: {{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),
  subject_template=VALUES(subject_template),
  body_template=VALUES(body_template);

INSERT INTO schema_versions(version_number,version_name)
VALUES(27,'Consented attendee directory and meeting invitations');