-- Safe to run after a partial migration 012 in phpMyAdmin; check database selection first.
-- Each ALTER is guarded because MySQL commits schema changes before a later error.

SET @guest_sql = IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='bookings' AND column_name='booking_code_lookup')=0, 'ALTER TABLE bookings ADD COLUMN booking_code_lookup CHAR(64) NULL AFTER booking_code_hash', 'SELECT 1');
PREPARE guest_stmt FROM @guest_sql;
EXECUTE guest_stmt;
DEALLOCATE PREPARE guest_stmt;

SET @guest_sql = IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='bookings' AND index_name='idx_booking_code_lookup')=0, 'ALTER TABLE bookings ADD KEY idx_booking_code_lookup(booking_code_lookup)', 'SELECT 1');
PREPARE guest_stmt FROM @guest_sql;
EXECUTE guest_stmt;
DEALLOCATE PREPARE guest_stmt;

SET @guest_sql = IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='guest_contacts' AND column_name='consent_text')=0, 'ALTER TABLE guest_contacts ADD COLUMN consent_text TEXT NULL AFTER consented_at', 'SELECT 1');
PREPARE guest_stmt FROM @guest_sql;
EXECUTE guest_stmt;
DEALLOCATE PREPARE guest_stmt;

SET @guest_sql = IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='guest_contacts' AND column_name='consent_version')=0, 'ALTER TABLE guest_contacts ADD COLUMN consent_version VARCHAR(40) NULL AFTER consent_text', 'SELECT 1');
PREPARE guest_stmt FROM @guest_sql;
EXECUTE guest_stmt;
DEALLOCATE PREPARE guest_stmt;

SET @guest_sql = IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='guest_contacts' AND column_name='source_booking_id')=0, 'ALTER TABLE guest_contacts ADD COLUMN source_booking_id BIGINT UNSIGNED NULL AFTER consent_version', 'SELECT 1');
PREPARE guest_stmt FROM @guest_sql;
EXECUTE guest_stmt;
DEALLOCATE PREPARE guest_stmt;

SET @guest_sql = IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='guest_contacts' AND column_name='retention_expires_at')=0, 'ALTER TABLE guest_contacts ADD COLUMN retention_expires_at DATETIME NULL AFTER source_booking_id', 'SELECT 1');
PREPARE guest_stmt FROM @guest_sql;
EXECUTE guest_stmt;
DEALLOCATE PREPARE guest_stmt;

SET @guest_sql = IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='guest_contacts' AND index_name='uq_guest_contact_sponsor')>0, 'ALTER TABLE guest_contacts DROP INDEX uq_guest_contact_sponsor', 'SELECT 1');
PREPARE guest_stmt FROM @guest_sql;
EXECUTE guest_stmt;
DEALLOCATE PREPARE guest_stmt;

SET @guest_sql = IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='attendee_contacts' AND index_name='uq_attendee_contact_email')>0, 'ALTER TABLE attendee_contacts DROP INDEX uq_attendee_contact_email', 'SELECT 1');
PREPARE guest_stmt FROM @guest_sql;
EXECUTE guest_stmt;
DEALLOCATE PREPARE guest_stmt;

SET @guest_sql = IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='guest_contacts' AND index_name='uq_guest_contact_unit_sponsor')=0, 'ALTER TABLE guest_contacts ADD UNIQUE KEY uq_guest_contact_unit_sponsor(unit_id,sponsor_email,email)', 'SELECT 1');
PREPARE guest_stmt FROM @guest_sql;
EXECUTE guest_stmt;
DEALLOCATE PREPARE guest_stmt;

SET @guest_sql = IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='attendee_contacts' AND index_name='uq_attendee_contact_unit_email')=0, 'ALTER TABLE attendee_contacts ADD UNIQUE KEY uq_attendee_contact_unit_email(unit_id,email)', 'SELECT 1');
PREPARE guest_stmt FROM @guest_sql;
EXECUTE guest_stmt;
DEALLOCATE PREPARE guest_stmt;

SET @guest_sql = IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='guest_contacts' AND index_name='idx_guest_contact_expiry')=0, 'ALTER TABLE guest_contacts ADD KEY idx_guest_contact_expiry(unit_id,retention_expires_at)', 'SELECT 1');
PREPARE guest_stmt FROM @guest_sql;
EXECUTE guest_stmt;
DEALLOCATE PREPARE guest_stmt;

SET @guest_sql = IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='guest_contacts' AND index_name='idx_guest_contact_source')=0, 'ALTER TABLE guest_contacts ADD KEY idx_guest_contact_source(source_booking_id)', 'SELECT 1');
PREPARE guest_stmt FROM @guest_sql;
EXECUTE guest_stmt;
DEALLOCATE PREPARE guest_stmt;

SET @guest_sql = IF((SELECT COUNT(*) FROM information_schema.table_constraints WHERE constraint_schema=DATABASE() AND table_name='guest_contacts' AND constraint_name='fk_guest_contact_source_booking')=0, 'ALTER TABLE guest_contacts ADD CONSTRAINT fk_guest_contact_source_booking FOREIGN KEY(source_booking_id) REFERENCES bookings(id) ON DELETE SET NULL', 'SELECT 1');
PREPARE guest_stmt FROM @guest_sql;
EXECUTE guest_stmt;
DEALLOCATE PREPARE guest_stmt;

-- 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 IF NOT EXISTS 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;