START TRANSACTION;

ALTER TABLE people
  ADD COLUMN civic_title VARCHAR(40) NULL AFTER salutation,
  ADD COLUMN honours VARCHAR(200) NULL AFTER surname,
  ADD COLUMN occupation VARCHAR(150) NULL AFTER birth_date;

CREATE TABLE person_addresses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 person_id BIGINT UNSIGNED NOT NULL,
 address_type ENUM('home','correspondence','business') NOT NULL,
 property_number VARCHAR(30) NULL,
 property_name VARCHAR(150) NULL,
 street_address_1 VARCHAR(150) NULL,
 street_address_2 VARCHAR(150) NULL,
 street_address_3 VARCHAR(150) NULL,
 town_city VARCHAR(100) NULL,
 county VARCHAR(100) NULL,
 postcode VARCHAR(30) NULL,
 country_code CHAR(2) NULL,
 updated_by BIGINT UNSIGNED NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_person_address(person_id,address_type),
 CONSTRAINT fk_person_address_person FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE CASCADE,
 CONSTRAINT fk_person_address_actor FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_profiles (
 member_id BIGINT UNSIGNED PRIMARY KEY,
 grand_lodge_id VARCHAR(100) NULL,
 grand_lodge_certificate_number VARCHAR(100) NULL,
 correspondence_same_as_home TINYINT(1) NOT NULL DEFAULT 1,
 business_same_as_home TINYINT(1) NOT NULL DEFAULT 1,
 rejoined_date DATE NULL,
 petitioned_date DATE NULL,
 interviewed_date DATE NULL,
 proposed_date DATE NULL,
 balloted_date DATE NULL,
 proposer_name VARCHAR(200) NULL,
 seconder_name VARCHAR(200) NULL,
 honorary_member TINYINT(1) NOT NULL DEFAULT 0,
 honorary_set_at DATETIME NULL,
 honorary_set_by BIGINT UNSIGNED NULL,
 cessation_date DATE NULL,
 cessation_reason VARCHAR(500) NULL,
 exclusion_date DATE NULL,
 exclusion_reason VARCHAR(500) NULL,
 death_date DATE NULL,
 death_notes_cipher LONGTEXT NULL,
 reinstatement_petition_date DATE NULL,
 healing_petition_date DATE NULL,
 affiliation_petition_date DATE NULL,
 petition_notes VARCHAR(2000) NULL,
 updated_by BIGINT UNSIGNED NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_member_profile_member FOREIGN KEY(member_id) REFERENCES unit_members(id) ON DELETE CASCADE,
 CONSTRAINT fk_member_profile_honorary_actor FOREIGN KEY(honorary_set_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT fk_member_profile_actor FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT chk_member_end_state CHECK ((cessation_date IS NOT NULL) + (exclusion_date IS NOT NULL) + (death_date IS NOT NULL) <= 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE member_offices
  ADD COLUMN office_kind ENUM('main','subsidiary') NOT NULL DEFAULT 'subsidiary' AFTER office_name,
  ADD COLUMN created_by BIGINT UNSIGNED NULL AFTER end_date,
  ADD COLUMN ended_by BIGINT UNSIGNED NULL AFTER created_by,
  ADD COLUMN ended_at DATETIME NULL AFTER ended_by,
  ADD COLUMN active_main_key TINYINT AS (CASE WHEN office_kind='main' AND end_date IS NULL THEN 1 ELSE NULL END) PERSISTENT,
  ADD UNIQUE KEY uq_member_active_main(member_id,active_main_key),
  ADD KEY idx_member_office_active(member_id,office_kind,end_date),
  ADD CONSTRAINT fk_member_office_creator FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
  ADD CONSTRAINT fk_member_office_ender FOREIGN KEY(ended_by) REFERENCES users(id) ON DELETE SET NULL;

CREATE TABLE member_office_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 member_id BIGINT UNSIGNED NOT NULL,
 office_id BIGINT UNSIGNED NOT NULL,
 action_key ENUM('appointed','ended') NOT NULL,
 office_kind ENUM('main','subsidiary') NOT NULL,
 office_name VARCHAR(150) NOT NULL,
 effective_date DATE NOT NULL,
 actor_user_id BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_member_office_event(member_id,created_at,id),
 CONSTRAINT fk_member_office_event_member FOREIGN KEY(member_id) REFERENCES unit_members(id) ON DELETE CASCADE,
 CONSTRAINT fk_member_office_event_office FOREIGN KEY(office_id) REFERENCES member_offices(id) ON DELETE CASCADE,
 CONSTRAINT fk_member_office_event_actor FOREIGN KEY(actor_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_degrees (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 member_id BIGINT UNSIGNED NOT NULL,
 degree_name VARCHAR(150) NOT NULL,
 conferred_date DATE NOT NULL,
 lodge_name VARCHAR(200) NULL,
 lodge_number VARCHAR(80) 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,
 KEY idx_member_degree(member_id,conferred_date,id),
 CONSTRAINT fk_member_degree_member FOREIGN KEY(member_id) REFERENCES unit_members(id) ON DELETE CASCADE,
 CONSTRAINT fk_member_degree_actor FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_other_lodges (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 member_id BIGINT UNSIGNED NOT NULL,
 lodge_name VARCHAR(200) NULL,
 lodge_number VARCHAR(80) 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,
 KEY idx_member_other_lodge(member_id,id),
 CONSTRAINT fk_member_other_lodge_member FOREIGN KEY(member_id) REFERENCES unit_members(id) ON DELETE CASCADE,
 CONSTRAINT fk_member_other_lodge_actor FOREIGN KEY(created_by) REFERENCES users(id),
 CONSTRAINT chk_other_lodge_identity CHECK (lodge_name IS NOT NULL OR lodge_number IS NOT NULL)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_partners (
 member_id BIGINT UNSIGNED PRIMARY KEY,
 first_name VARCHAR(100) NULL,
 surname VARCHAR(100) NULL,
 birth_date DATE NULL,
 nationality VARCHAR(100) NULL,
 divorced TINYINT(1) NOT NULL DEFAULT 0,
 deceased TINYINT(1) NOT NULL DEFAULT 0,
 updated_by BIGINT UNSIGNED NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_member_partner_member FOREIGN KEY(member_id) REFERENCES unit_members(id) ON DELETE CASCADE,
 CONSTRAINT fk_member_partner_actor FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT chk_member_partner_state CHECK (NOT (divorced=1 AND deceased=1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_children (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 member_id BIGINT UNSIGNED NOT NULL,
 first_name VARCHAR(100) NOT NULL,
 surname VARCHAR(100) NOT NULL,
 birth_date DATE NULL,
 nationality VARCHAR(100) NULL,
 deceased 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,
 KEY idx_member_child(member_id,id),
 CONSTRAINT fk_member_child_member FOREIGN KEY(member_id) REFERENCES unit_members(id) ON DELETE CASCADE,
 CONSTRAINT fk_member_child_actor FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_next_of_kin (
 member_id BIGINT UNSIGNED PRIMARY KEY,
 forename VARCHAR(100) NULL,
 surname VARCHAR(100) NULL,
 address_text VARCHAR(1000) NULL,
 phone VARCHAR(40) NULL,
 updated_by BIGINT UNSIGNED NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_member_nok_member FOREIGN KEY(member_id) REFERENCES unit_members(id) ON DELETE CASCADE,
 CONSTRAINT fk_member_nok_actor FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_promotion_entries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 member_id BIGINT UNSIGNED NOT NULL,
 category ENUM('london_rank','london_grand_rank','senior_london_grand_rank','provincial_grand_rank','grand_rank') NOT NULL,
 sequence_no TINYINT UNSIGNED NOT NULL DEFAULT 0,
 recommended_by VARCHAR(200) NULL,
 rank_name VARCHAR(150) NOT NULL,
 investiture_date DATE NOT 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_member_promotion_sequence(member_id,category,sequence_no),
 KEY idx_member_promotion(member_id,investiture_date,id),
 CONSTRAINT fk_member_promotion_member FOREIGN KEY(member_id) REFERENCES unit_members(id) ON DELETE CASCADE,
 CONSTRAINT fk_member_promotion_actor FOREIGN KEY(created_by) REFERENCES users(id),
 CONSTRAINT chk_member_promotion_sequence CHECK (sequence_no <= 9)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TRIGGER IF EXISTS trg_member_honorary_irreversible;
DELIMITER $$
CREATE TRIGGER trg_member_honorary_irreversible BEFORE UPDATE ON member_profiles
FOR EACH ROW BEGIN
  IF OLD.honorary_member=1 AND NEW.honorary_member=0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Honorary status cannot be reversed';
  END IF;
END$$
DELIMITER ;

INSERT INTO migration_history(version,name,checksum,execution_ms)
VALUES('202609290044','Member workspace',SHA2('202609290044_member_workspace_v1',256),0)
ON DUPLICATE KEY UPDATE name=VALUES(name),checksum=VALUES(checksum);

COMMIT;