-- Additive people directory. Historical bookings/guests are never updated. CREATE TABLE people ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, salutation VARCHAR(30) NULL, first_name VARCHAR(100) NOT NULL, surname VARCHAR(100) NOT NULL, email VARCHAR(254) NULL, phone VARCHAR(30) NULL, identity_verified_at DATETIME NULL, identity_verified_by BIGINT UNSIGNED NULL, legacy_guest_contact_id BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_people_user(user_id), UNIQUE KEY uq_people_legacy_guest(legacy_guest_contact_id), KEY idx_people_name(surname,first_name), KEY idx_people_email(email), CONSTRAINT fk_people_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_people_verifier FOREIGN KEY(identity_verified_by) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_people_guest_contact FOREIGN KEY(legacy_guest_contact_id) REFERENCES guest_contacts(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE unit_members ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL, person_id BIGINT UNSIGNED NOT NULL, order_key VARCHAR(40) NOT NULL DEFAULT 'craft', membership_type VARCHAR(80) NOT NULL, admission_route ENUM('admitted','joining') NOT NULL, state ENUM('active','inactive','resigned','deceased') NOT NULL DEFAULT 'active', start_date DATE NOT NULL, end_date DATE NULL, participation_status VARCHAR(80) NULL, contact_email TINYINT(1) NOT NULL DEFAULT 0, contact_post TINYINT(1) NOT NULL DEFAULT 0, contact_phone TINYINT(1) NOT NULL DEFAULT 0, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_unit_member(unit_id,person_id,order_key), KEY idx_unit_member_state(unit_id,state,start_date), CONSTRAINT fk_unit_member_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE, CONSTRAINT fk_unit_member_person FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE RESTRICT, CONSTRAINT fk_unit_member_actor FOREIGN KEY(created_by) REFERENCES users(id), CONSTRAINT chk_member_dates CHECK(end_date IS NULL OR end_date>=start_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE member_milestones ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, member_id BIGINT UNSIGNED NOT NULL, milestone_key VARCHAR(60) NOT NULL, terminology VARCHAR(80) NOT NULL, ceremony_date DATE NOT NULL, confirmed_by BIGINT UNSIGNED NOT NULL, confirmed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_member_milestone(member_id,milestone_key), CONSTRAINT fk_milestone_member FOREIGN KEY(member_id) REFERENCES unit_members(id) ON DELETE CASCADE, CONSTRAINT fk_milestone_actor FOREIGN KEY(confirmed_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE member_ranks ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, member_id BIGINT UNSIGNED NOT NULL, rank_scope ENUM('masonic','provincial','grand') NOT NULL, rank_name VARCHAR(150) NOT NULL, attained_date DATE NULL, ended_date DATE NULL, UNIQUE KEY uq_member_rank(member_id,rank_scope,rank_name), CONSTRAINT fk_member_rank_member FOREIGN KEY(member_id) REFERENCES unit_members(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE member_offices ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, member_id BIGINT UNSIGNED NOT NULL, office_name VARCHAR(150) NOT NULL, start_date DATE NOT NULL, end_date DATE NULL, KEY idx_member_office(member_id,start_date), CONSTRAINT fk_member_office_member FOREIGN KEY(member_id) REFERENCES unit_members(id) ON DELETE CASCADE, CONSTRAINT chk_office_dates CHECK(end_date IS NULL OR end_date>=start_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE unit_contacts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL, person_id BIGINT UNSIGNED NOT NULL, source_guest_contact_id BIGINT UNSIGNED NULL, source_booking_id BIGINT UNSIGNED NULL, consent_text TEXT NOT NULL, consent_version VARCHAR(40) NOT NULL, consented_at DATETIME NOT NULL, permitted_unit_id BIGINT UNSIGNED NOT NULL, permitted_reuse TINYINT(1) NOT NULL DEFAULT 1, permit_event_invitations TINYINT(1) NOT NULL DEFAULT 0, permit_general_messages TINYINT(1) NOT NULL DEFAULT 0, agreed_contact_method ENUM('email','phone','post','none') NOT NULL DEFAULT 'none', withdrawn_at DATETIME NULL, corrected_at DATETIME NULL, retention_expires_at DATETIME NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_unit_contact(unit_id,person_id), UNIQUE KEY uq_contact_guest_source(source_guest_contact_id), KEY idx_unit_contact_consent(unit_id,withdrawn_at,retention_expires_at), CONSTRAINT fk_unit_contact_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE, CONSTRAINT fk_unit_contact_scope FOREIGN KEY(permitted_unit_id) REFERENCES units(id) ON DELETE CASCADE, CONSTRAINT fk_unit_contact_person FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE RESTRICT, CONSTRAINT fk_unit_contact_guest FOREIGN KEY(source_guest_contact_id) REFERENCES guest_contacts(id) ON DELETE SET NULL, CONSTRAINT fk_unit_contact_booking FOREIGN KEY(source_booking_id) REFERENCES bookings(id) ON DELETE SET NULL, CONSTRAINT chk_contact_unit_scope CHECK(unit_id=permitted_unit_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE unit_candidates ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL, person_id BIGINT UNSIGNED NOT NULL, order_key VARCHAR(40) NOT NULL DEFAULT 'craft', sponsor_person_id BIGINT UNSIGNED NULL, sponsor_name VARCHAR(200) NULL, stage ENUM('enquiry','meeting','application','ballot','approved','on_hold','closed','admitted') NOT NULL DEFAULT 'enquiry', next_action VARCHAR(250) NULL, agreed_contact_method ENUM('email','phone','post','none') NOT NULL DEFAULT 'none', review_date DATE NULL, closed_at DATETIME NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_unit_candidate(unit_id,person_id,order_key), KEY idx_candidate_stage(unit_id,stage,review_date), CONSTRAINT fk_candidate_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE, CONSTRAINT fk_candidate_person FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE RESTRICT, CONSTRAINT fk_candidate_sponsor FOREIGN KEY(sponsor_person_id) REFERENCES people(id) ON DELETE SET NULL, CONSTRAINT fk_candidate_actor FOREIGN KEY(created_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE candidate_history ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, candidate_id BIGINT UNSIGNED NOT NULL, actor_id BIGINT UNSIGNED NOT NULL, action_key VARCHAR(60) NOT NULL, from_stage VARCHAR(30) NULL, to_stage VARCHAR(30) NULL, occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_candidate_history(candidate_id,occurred_at), CONSTRAINT fk_candidate_history_candidate FOREIGN KEY(candidate_id) REFERENCES unit_candidates(id) ON DELETE CASCADE, CONSTRAINT fk_candidate_history_actor FOREIGN KEY(actor_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE candidate_admissions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, candidate_id BIGINT UNSIGNED NOT NULL, member_id BIGINT UNSIGNED NOT NULL, ceremony_date DATE NOT NULL, terminology VARCHAR(80) NOT NULL, confirmed_by BIGINT UNSIGNED NOT NULL, confirmed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_candidate_admission(candidate_id), UNIQUE KEY uq_admission_member(member_id), CONSTRAINT fk_admission_candidate FOREIGN KEY(candidate_id) REFERENCES unit_candidates(id) ON DELETE RESTRICT, CONSTRAINT fk_admission_member FOREIGN KEY(member_id) REFERENCES unit_members(id) ON DELETE RESTRICT, CONSTRAINT fk_admission_actor FOREIGN KEY(confirmed_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE person_duplicate_reviews ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL, person_a_id BIGINT UNSIGNED NOT NULL, person_b_id BIGINT UNSIGNED NOT NULL, status ENUM('pending','same_person','different_people') NOT NULL DEFAULT 'pending', reviewed_by BIGINT UNSIGNED NULL, reviewed_at DATETIME NULL, canonical_person_id BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_duplicate_pair(unit_id,person_a_id,person_b_id), CONSTRAINT fk_duplicate_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE, CONSTRAINT fk_duplicate_a FOREIGN KEY(person_a_id) REFERENCES people(id) ON DELETE CASCADE, CONSTRAINT fk_duplicate_b FOREIGN KEY(person_b_id) REFERENCES people(id) ON DELETE CASCADE, CONSTRAINT fk_duplicate_reviewer FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_duplicate_canonical FOREIGN KEY(canonical_person_id) REFERENCES people(id) ON DELETE SET NULL, CONSTRAINT chk_duplicate_order CHECK(person_a_id