-- Apply once after 202609240011. No existing booking or guest is deleted.
ALTER TABLE bookings ADD COLUMN booking_code_lookup CHAR(64) NULL AFTER booking_code_hash,
 ADD KEY idx_booking_code_lookup(booking_code_lookup);
ALTER TABLE guest_contacts
 ADD COLUMN consent_text TEXT NULL AFTER consented_at,
 ADD COLUMN consent_version VARCHAR(40) NULL AFTER consent_text,
 ADD COLUMN source_booking_id BIGINT UNSIGNED NULL AFTER consent_version,
 ADD COLUMN retention_expires_at DATETIME NULL AFTER source_booking_id,
 DROP INDEX uq_guest_contact_sponsor,
 ADD UNIQUE KEY uq_guest_contact_unit_sponsor(unit_id,sponsor_email,email),
 ADD KEY idx_guest_contact_expiry(unit_id,retention_expires_at),
 ADD KEY idx_guest_contact_source(source_booking_id),
 ADD CONSTRAINT fk_guest_contact_source_booking FOREIGN KEY(source_booking_id) REFERENCES bookings(id) ON DELETE SET NULL;
-- A person may consent separately for each unit without altering another unit's profile.
ALTER TABLE attendee_contacts DROP INDEX uq_attendee_contact_email,
 ADD UNIQUE KEY uq_attendee_contact_unit_email(unit_id,email);
-- Legacy checked consent has a source booking and the form's historical wording.
UPDATE guest_contacts c SET
 c.source_booking_id=(SELECT g.booking_id FROM guests g JOIN bookings b ON b.id=g.booking_id JOIN meetings m ON m.id=b.meeting_id WHERE g.guest_contact_id=c.id AND g.retention_consent=1 AND m.unit_id=c.unit_id ORDER BY g.created_at DESC,g.id DESC LIMIT 1),
 c.consent_text='The guest has agreed that the Lodge may retain their contact details and email them when future meeting summonses or invitations are published.',
 c.consent_version='legacy-20260924',
 c.retention_expires_at=DATE_ADD(c.last_booked_at,INTERVAL 18 MONTH)
WHERE c.consent_version IS NULL;
-- A profile without evidence of a checked consent must not be available or invited.
UPDATE guest_contacts SET consent_withdrawn_at=COALESCE(consent_withdrawn_at,NOW())
WHERE source_booking_id IS NULL;
CREATE TABLE guest_directory_rate_limits (
 unit_id BIGINT UNSIGNED NOT NULL,
 bucket CHAR(64) NOT NULL,
 attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 window_started DATETIME NOT NULL,
 PRIMARY KEY(unit_id,bucket),
 CONSTRAINT fk_guest_directory_rate_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;