-- CORRECTED fresh install. The prior first-setup-complete.sql has a
-- fatal foundation precondition (17 expected for 16 listed legacy tables).
-- Import this file into a NEW EMPTY DATABASE only. A partially imported
-- database cannot safely be resumed using this full installer.
-- Back up the original database before making changes.
-- This installer contains no personal passwords or user accounts.

DELIMITER //
DROP PROCEDURE IF EXISTS verify_first_setup_empty_database//
CREATE PROCEDURE verify_first_setup_empty_database()
BEGIN
  IF DATABASE() IS NULL OR (SELECT COUNT(*) FROM information_schema.tables
    WHERE table_schema=DATABASE() AND table_type='BASE TABLE')<>0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Select a NEW EMPTY database for this complete installer.';
  END IF;
END//
CALL verify_first_setup_empty_database()//
DROP PROCEDURE verify_first_setup_empty_database//
DELIMITER ;

-- COMPLETE FIRST SETUP: fresh empty database only.
-- Historical upgrade-002/003/004/005 files are represented in the base schema.
-- Uploaded tenant migrations 003-014 appear once each in dependency order.
-- Guest-directory repair equals migration 012 and is included once only.
-- Additional historical upgrades 006-022 and 026-028 were compared to
-- database.sql. Their schema columns/tables and default email templates are
-- already represented there; do not run the upgrades against this install.
-- Built-in rank and salutation rows remain in platform_order_rank_settings.
-- Read the accompanying first-unit-setup.sql to enter a real Province/unit
-- before visiting admin/setup.php: the current PHP installer needs one unit.
-- No user, Province, Lodge/unit, event or booking records remain after import.

-- Complete fresh-install schema for the September 2026 BookingSystem package.
-- Import ONLY into a newly created, EMPTY MySQL/MariaDB database.
-- Includes all tables, built-in rank/salutation defaults, Order terminology,
-- and email template defaults. Clears legacy example/operational rows at end.
-- No users, provinces, units, meetings, bookings or unit-owned settings remain.
-- Does not contain passwords, credentials, DROP DATABASE or account creation.
-- Requires MySQL 8+ or MariaDB 10.5+ and phpMyAdmin SQL import support for DELIMITER.


-- ===== Legacy schema and reference defaults =====
-- Lea Lodge booking and administration platform
-- Complete clean-install database schema incorporating upgrades 002-007.
-- Import into an EMPTY MySQL/MariaDB database. Existing data is not removed.
SET
  NAMES utf8mb4;

SET
  time_zone = '+00:00';

CREATE TABLE
  meetings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lodge_name VARCHAR(150) NOT NULL DEFAULT 'Your Lodge',
    lodge_number VARCHAR(20) NOT NULL DEFAULT '0',
    masonic_order VARCHAR(40) NOT NULL DEFAULT 'craft',
    linked_event_group VARCHAR(100) NULL,
    title VARCHAR(200) NOT NULL,
    meeting_date DATE NOT NULL,
    meeting_start DATETIME NOT NULL,
    meeting_end DATETIME NULL,
    booking_deadline DATETIME NOT NULL,
    venue_name VARCHAR(200) NOT NULL,
    venue_address TEXT NULL,
    dining_capacity INT UNSIGNED NOT NULL DEFAULT 80,
    member_price DECIMAL(10, 2) NOT NULL DEFAULT 0,
    visitor_price DECIMAL(10, 2) NOT NULL DEFAULT 0,
    special_event_enabled TINYINT (1) NOT NULL DEFAULT 0,
    special_event_official_visits TINYINT (1) NOT NULL DEFAULT 0,
    special_event_type ENUM (
      'none',
      'ladies_evening',
      'white_table',
      'blue_table',
      'other'
    ) NOT NULL DEFAULT 'none',
    special_event_label VARCHAR(120) NULL,
    special_event_description TEXT NULL,
    raffle_enabled TINYINT (1) NOT NULL DEFAULT 0,
    raffle_sales_unit ENUM ('ticket', 'strip') NOT NULL DEFAULT 'ticket',
    raffle_ticket_price DECIMAL(10, 2) NOT NULL DEFAULT 0,
    raffle_max_per_booking INT UNSIGNED NULL,
    raffle_description VARCHAR(500) NULL,
    template_source_id BIGINT UNSIGNED NULL,
    summons_url TEXT NULL,
    toast_list_url TEXT NULL,
    alms_collection_url TEXT NULL,
    menu_note TEXT NULL,
    bookings_open TINYINT (1) NOT NULL DEFAULT 1,
    calendar_invite_enabled TINYINT (1) NOT NULL DEFAULT 0,
    lifecycle_status ENUM (
      'draft',
      'open',
      'closed',
      'completed',
      'archived',
      'cancelled'
    ) NOT NULL DEFAULT 'open',
    test_mode TINYINT (1) NOT NULL DEFAULT 0,
    archived TINYINT (1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_meeting_date (meeting_date),
    INDEX idx_meeting_order (masonic_order),
    INDEX idx_meeting_linked_group (linked_event_group),
    INDEX idx_meeting_status (archived, bookings_open),
    INDEX idx_meeting_lifecycle (lifecycle_status, meeting_date),
    INDEX idx_meeting_public_booking (
      archived,
      bookings_open,
      lifecycle_status,
      booking_deadline,
      meeting_date
    )
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  meeting_menu (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    meeting_id BIGINT UNSIGNED NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    dish TEXT NOT NULL,
    vegetarian_alternative TEXT NULL,
    vegan_alternative TEXT NULL,
    pescetarian_alternative TEXT NULL,
    kosher_alternative TEXT NULL,
    halal_alternative TEXT NULL,
    hindu_alternative TEXT NULL,
    sikh_alternative TEXT NULL,
    display_order INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_menu_meeting (meeting_id, display_order),
    CONSTRAINT fk_menu_meeting FOREIGN KEY (meeting_id) REFERENCES meetings (id) ON DELETE CASCADE
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  order_rank_settings (
    order_key VARCHAR(40) PRIMARY KEY,
    salutations TEXT NULL,
    salutation_rank_links LONGTEXT NULL,
    masonic_ranks TEXT NULL,
    active_provincial_ranks TEXT NULL,
    past_provincial_ranks TEXT NULL,
    active_grand_ranks TEXT NULL,
    past_grand_ranks TEXT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Complete editable default ranks supplied for every supported built-in Order.
-- First-time setup controls which of these Orders are enabled for the Lodge.
INSERT INTO
  `order_rank_settings` (
    `order_key`,
    `salutations`,
    `salutation_rank_links`,
    `masonic_ranks`,
    `active_provincial_ranks`,
    `past_provincial_ranks`,
    `active_grand_ranks`,
    `past_grand_ranks`,
    `updated_at`
  )
VALUES
  (
    'craft',
    'Bro\nWBro\nVWBro\nRWBro\nMWBro',
    '{\"Bro\":[{\"scope\":\"masonic\",\"rank\":\"Entered Apprentice\"},{\"scope\":\"masonic\",\"rank\":\"Fellow Craft\"},{\"scope\":\"masonic\",\"rank\":\"Master Mason\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Steward\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Tyler\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Pursuivant\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Pursuivant\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Tyler\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Steward\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Deputy Grand Organist\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Deputy Grand Organist\"}],\"WBro\":[{\"scope\":\"provincial\",\"rank\":\"Deputy Provincial Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"Assistant Provincial Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Senior Grand Warden\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Junior Grand Warden\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Chaplain\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Treasurer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Registrar\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Secretary\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Sword Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Superintendent of Works\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Deputy Grand Chaplain\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Deputy Grand Registrar\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Deputy Grand Secretary\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Deputy Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Deputy Grand Sword Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Deputy Grand Superintendent of Works\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Inspector\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Almoner\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Charity Steward\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Membership Officer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Communications Officer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Learning and Development Officer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Senior Grand Deacon\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Junior Grand Deacon\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Chaplain\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Registrar\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Secretary\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Sword Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Superintendent of Works\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Organise\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Deputy Grand Organist\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Pursuivant\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Tyler\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Steward\"},{\"scope\":\"grand\",\"rank\":\"Grand Treasurer\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Sword Bearer\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Superintendent of Works\"},{\"scope\":\"grand\",\"rank\":\"Senior Grand Deacon\"},{\"scope\":\"grand\",\"rank\":\"Junior Grand Deacon\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Directors of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Sword Bearers\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Superintendents of Works\"},{\"scope\":\"grand\",\"rank\":\"Grand Organist\"},{\"scope\":\"grand\",\"rank\":\"Grand Standard Bearer\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Organist\"},{\"scope\":\"grand\",\"rank\":\"Grand Pursuivant\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Pursiuvant\"},{\"scope\":\"grand\",\"rank\":\"Grand Steward\"},{\"scope\":\"provincial\",\"rank\":\"Past Deputy Provincial Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"Past Assistant Provincial Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Senior Grand Warden\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Junior Grand Warden\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Chaplain\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Treasurer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Registrar\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Secretary\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Sword Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Superintendent of Works\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Deputy Grand Chaplain\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Deputy Grand Registrar\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Deputy Grand Secretary\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Deputy Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Deputy Grand Sword Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Deputy Grand Superintendent of Works\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Inspector\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Almoner\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Charity Steward\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Treasurer\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Sword Bearer\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Superintendent of Works\"},{\"scope\":\"grand\",\"rank\":\"Past Senior Grand Deacon\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Membership Officer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Communications Officer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Learning and Development Officer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Senior Grand Deacon\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Junior Grand Deacon\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Chaplain\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Registrar\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Secretary\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Sword Bearer\"},{\"scope\":\"grand\",\"rank\":\"Past Junior Grand Deacon\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Directors of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Sword Bearers\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Superintendents of Works\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Organist\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Standard Bearer\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Organist\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Pursuivant\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Pursiuvant\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Steward\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Superintendent of Works\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Organise\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Deputy Grand Organist\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Pursuivant\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Tyler\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Steward\"},{\"scope\":\"masonic\",\"rank\":\"Worshipful Master\"},{\"scope\":\"masonic\",\"rank\":\"Immediate Past Master\"},{\"scope\":\"masonic\",\"rank\":\"Past Master\"}],\"VWBro\":[{\"scope\":\"provincial\",\"rank\":\"Deputy Provincial Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"Past Deputy Provincial Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Grand Secretary\"},{\"scope\":\"grand\",\"rank\":\"Grand Chancellor\"},{\"scope\":\"grand\",\"rank\":\"President of the Masonic Charitable Foundation\"},{\"scope\":\"grand\",\"rank\":\"Deputy President of the Board of General Purposes\"},{\"scope\":\"grand\",\"rank\":\"Deputy President of the Masonic Charitable Foundation\"},{\"scope\":\"grand\",\"rank\":\"Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Grand Sword Bearer\"},{\"scope\":\"grand\",\"rank\":\"Grand Superintendent of Works\"},{\"scope\":\"grand\",\"rank\":\"Grand Inspector\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Secretary\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Chancellor\"},{\"scope\":\"grand\",\"rank\":\"Past President of the Masonic Charitable Foundation\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy President of the Board of General Purposes\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy President of the Masonic Charitable Foundation\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Sword Bearer\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Superintendent of Works\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Inspector\"}],\"RWBro\":[{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Senior Grand Warden\"},{\"scope\":\"grand\",\"rank\":\"Junior Grand Warden\"},{\"scope\":\"grand\",\"rank\":\"President of the Board of General Purposes\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Past Senior Grand Warden\"},{\"scope\":\"grand\",\"rank\":\"Past Junior Grand Warden\"},{\"scope\":\"grand\",\"rank\":\"Past President of the Board of General Purposes\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Master\"}],\"MWBro\":[{\"scope\":\"grand\",\"rank\":\"Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Pro Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Past Pro Grand Master\"}]}',
    'Entered Apprentice\nFellow Craft\nMaster Mason\nWorshipful Master\nImmediate Past Master\nPast Master',
    'Provincial Grand Master\nDeputy Provincial Grand Master\nAssistant Provincial Grand Master\nProvincial Senior Grand Warden\nProvincial Junior Grand Warden\nProvincial Grand Chaplain\nProvincial Grand Treasurer\nProvincial Grand Registrar\nProvincial Grand Secretary\nProvincial Grand Director of Ceremonies\nProvincial Grand Sword Bearer\nProvincial Grand Superintendent of Works\nProvincial Deputy Grand Chaplain\nProvincial Deputy Grand Registrar\nProvincial Deputy Grand Secretary\nProvincial Deputy Grand Director of Ceremonies\nProvincial Deputy Grand Sword Bearer\nProvincial Deputy Grand Superintendent of Works\nProvincial Assistant Grand Inspector\nProvincial Grand Almoner\nProvincial Grand Charity Steward\nProvincial Grand Membership Officer\nProvincial Grand Communications Officer\nProvincial Grand Learning and Development Officer\nProvincial Senior Grand Deacon\nProvincial Junior Grand Deacon\nProvincial Assistant Grand Chaplain\nProvincial Assistant Grand Registrar\nProvincial Assistant Grand Secretary\nProvincial Assistant Grand Director of Ceremonies\nProvincial Assistant Grand Sword Bearer\nProvincial Assistant Grand Superintendent of Works\nProvincial Grand Organise\nProvincial Standard Bearer\nProvincial Assistant Grand Standard Bearer\nProvincial Deputy Grand Organist\nProvincial Grand Pursuivant\nProvincial Grand Tyler\nProvincial Grand Steward',
    'Past Provincial Grand Master\nPast Deputy Provincial Grand Master\nPast Assistant Provincial Grand Master\nPast Provincial Senior Grand Warden\nPast Provincial Junior Grand Warden\nPast Provincial Grand Chaplain\nPast Provincial Grand Treasurer\nPast Provincial Grand Registrar\nPast Provincial Grand Secretary\nPast Provincial Grand Director of Ceremonies\nPast Provincial Grand Sword Bearer\nPast Provincial Grand Superintendent of Works\nPast Provincial Deputy Grand Chaplain\nPast Provincial Deputy Grand Registrar\nPast Provincial Deputy Grand Secretary\nPast Provincial Deputy Grand Director of Ceremonies\nPast Provincial Deputy Grand Sword Bearer\nPast Provincial Deputy Grand Superintendent of Works\nPast Provincial Assistant Grand Inspector\nPast Provincial Grand Almoner\nPast Provincial Grand Charity Steward\nPast Provincial Grand Membership Officer\nPast Provincial Grand Communications Officer\nPast Provincial Grand Learning and Development Officer\nPast Provincial Senior Grand Deacon\nPast Provincial Junior Grand Deacon\nPast Provincial Assistant Grand Chaplain\nPast Provincial Assistant Grand Registrar\nPast Provincial Assistant Grand Secretary\nPast Provincial Assistant Grand Director of Ceremonies\nPast Provincial Assistant Grand Sword Bearer\nPast Provincial Assistant Grand Superintendent of Works\nPast Provincial Grand Organise\nPast Provincial Standard Bearer\nPast Provincial Assistant Grand Standard Bearer\nPast Provincial Deputy Grand Organist\nPast Provincial Grand Pursuivant\nPast Provincial Grand Tyler\nPast Provincial Grand Steward',
    'Grand Master\nPro Grand Master\nDeputy Grand Master\nAssistant Grand Master\nSenior Grand Warden\nJunior Grand Warden\nPresident of the Board of General Purposes\nGrand Chaplain\nGrand Registrar\nGrand Secretary\nGrand Chancellor\nPresident of the Masonic Charitable Foundation\nDeputy President of the Board of General Purposes\nDeputy President of the Masonic Charitable Foundation\nGrand Director of Ceremonies\nGrand Sword Bearer\nGrand Superintendent of Works\nGrand Inspector\nGrand Treasurer\nDeputy Grand Chaplain\nDeputy Grand Registrar\nDeputy Grand Director of Ceremonies\nDeputy Grand Sword Bearer\nDeputy Grand Superintendent of Works\nSenior Grand Deacon\nJunior Grand Deacon\nAssistant Grand Chaplain\nAssistant Grand Registrar\nAssistant Grand Directors of Ceremonies\nAssistant Grand Sword Bearers\nAssistant Grand Superintendents of Works\nGrand Organist\nGrand Standard Bearer\nDeputy Grand Organist\nGrand Pursuivant\nAssistant Grand Pursiuvant\nGrand Steward',
    'Past Grand Master\nPast Pro Grand Master\nPast Deputy Grand Master\nPast Assistant Grand Master\nPast Senior Grand Warden\nPast Junior Grand Warden\nPast President of the Board of General Purposes\nPast Grand Chaplain\nPast Grand Registrar\nPast Grand Secretary\nPast Grand Chancellor\nPast President of the Masonic Charitable Foundation\nPast Deputy President of the Board of General Purposes\nPast Deputy President of the Masonic Charitable Foundation\nPast Grand Director of Ceremonies\nPast Grand Sword Bearer\nPast Grand Superintendent of Works\nPast Grand Inspector\nPast Grand Treasurer\nPast Deputy Grand Chaplain\nPast Deputy Grand Registrar\nPast Deputy Grand Director of Ceremonies\nPast Deputy Grand Sword Bearer\nPast Deputy Grand Superintendent of Works\nPast Senior Grand Deacon\nPast Junior Grand Deacon\nPast Assistant Grand Chaplain\nPast Assistant Grand Registrar\nPast Assistant Grand Directors of Ceremonies\nPast Assistant Grand Sword Bearers\nPast Assistant Grand Superintendents of Works\nPast Grand Organist\nPast Grand Standard Bearer\nPast Deputy Grand Organist\nPast Grand Pursuivant\nPast Assistant Grand Pursiuvant\nPast Grand Steward',
    '2026-09-22 16:23:51'
  ),
  (
    'mark',
    'Bro\nWBro\nVWBro\nRWBro\nMWBro',
    '{\"Bro\":[{\"scope\":\"masonic\",\"rank\":\"Mark Master Mason\"},{\"scope\":\"masonic\",\"rank\":\"Worshipful Master\"},{\"scope\":\"masonic\",\"rank\":\"Immediate Past Master\"},{\"scope\":\"masonic\",\"rank\":\"Past Master\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Tyler\"},{\"scope\":\"grand\",\"rank\":\"Grand Tyler\"},{\"scope\":\"grand\",\"rank\":\"Grand Steward\"},{\"scope\":\"grand\",\"rank\":\"Grand Inner Guard\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Inner Guard\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Inner Guard\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Steward\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Inner Guard\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Tyler\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Tyler\"}],\"WBro\":[{\"scope\":\"grand\",\"rank\":\"Grand Inspector of Works\"},{\"scope\":\"grand\",\"rank\":\"Grand Sword Bearer\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Deputy President of the General Board\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Deputy President of the Mark Benevolent Fund\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Secretary\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Inspectors of Works\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Sword Bearer\"},{\"scope\":\"grand\",\"rank\":\"Grand Senior Deacon\"},{\"scope\":\"grand\",\"rank\":\"Grand Junior Deacon\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Secretary\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Directors of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Inspector of Works\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Sword Bearers\"},{\"scope\":\"grand\",\"rank\":\"Grand Librarian\"},{\"scope\":\"grand\",\"rank\":\"Grand Organist\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Standard Bearer\"},{\"scope\":\"grand\",\"rank\":\"Grand Standard Bearer\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Organist\"},{\"scope\":\"grand\",\"rank\":\"Grand Inner Guard\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Inner Guard\"},{\"scope\":\"grand\",\"rank\":\"Grand Steward\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Tyler\"},{\"scope\":\"grand\",\"rank\":\"Grand Tyler\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Tyler\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Tyler\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Steward\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Inner Guard\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Inner Guard\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Inspector of Works\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Sword Bearer\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy President of the General Board\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy President of the Mark Benevolent Fund\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Secretary\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Inspectors of Works\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Sword Bearer\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Senior Deacon\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Junior Deacon\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Secretary\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Directors of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Inspector of Works\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Sword Bearers\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Librarian\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Organist\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Standard Bearer\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Standard Bearer\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Organist\"},{\"scope\":\"provincial\",\"rank\":\"Assistant Provincial Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Senior Warden\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Junior Warden\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Master Overseer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Senior Overseer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Chaplain\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Junior Overseer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Treasurer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Registrar\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Secretary\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Senior Deacon\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Junior Deacon\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Chaplain\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Charity Steward\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Almoner\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Organist\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Inner Guard\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Steward\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Tyler\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Tyler\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Steward\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Inner Guard\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Organist\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Almoner\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Charity Steward\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Chaplain\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Junior Deacon\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Senior Deacon\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Secretary\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Registrar\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Treasurer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Chaplain\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Junior Overseer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Senior Overseer\"},{\"scope\":\"provincial\",\"rank\":\"Past Assistant Provincial Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Senior Warden\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Junior Warden\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Master Overseer\"},{\"scope\":\"masonic\",\"rank\":\"Worshipful Master\"},{\"scope\":\"masonic\",\"rank\":\"Immediate Past Master\"},{\"scope\":\"masonic\",\"rank\":\"Past Master\"}],\"VWBro\":[{\"scope\":\"grand\",\"rank\":\"Grand Inspector\"},{\"scope\":\"grand\",\"rank\":\"Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Grand Secretary\"},{\"scope\":\"grand\",\"rank\":\"President of the Mark Benevolent Fund\"},{\"scope\":\"grand\",\"rank\":\"Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Grand Treasurer\"},{\"scope\":\"grand\",\"rank\":\"President of the Board of General Purposes\"},{\"scope\":\"grand\",\"rank\":\"Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Grand Junior Overseer\"},{\"scope\":\"grand\",\"rank\":\"Grand Senior Overseer\"},{\"scope\":\"grand\",\"rank\":\"Grand Master Overseer\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Inspector\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Master Overseer\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Senior Overseer\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Junior Overseer\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Past President of the Board of General Purposes\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Treasurer\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Past President of the Mark Benevolent Fund\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Secretary\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Deputy Provincial Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"Past Deputy Provincial Grand Master\"}],\"RWBro\":[{\"scope\":\"grand\",\"rank\":\"Deputy Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Senior Grand Warden\"},{\"scope\":\"grand\",\"rank\":\"Junior Grand Warden\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Past Senior Grand Warden\"},{\"scope\":\"grand\",\"rank\":\"Past Junior Grand Warden\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Master\"}],\"MWBro\":[{\"scope\":\"grand\",\"rank\":\"Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Pro Grand Master\"}]}',
    'Mark Master Mason\nWorshipful Master\nImmediate Past Master\nPast Master',
    'Provincial Grand Master\nDeputy Provincial Grand Master\nAssistant Provincial Grand Master\nProvincial Grand Senior Warden\nProvincial Grand Junior Warden\nProvincial Grand Master Overseer\nProvincial Grand Senior Overseer\nProvincial Grand Junior Overseer\nProvincial Grand Chaplain\nProvincial Grand Treasurer\nProvincial Grand Registrar\nProvincial Grand Secretary\nProvincial Grand Director of Ceremonies\nProvincial Grand Senior Deacon\nProvincial Grand Junior Deacon\nProvincial Assistant Grand Chaplain\nProvincial Assistant Grand Charity Steward\nProvincial Assistant Grand Almoner\nProvincial Grand Organist\nProvincial Grand Standard Bearer\nProvincial Assistant Grand Standard Bearer\nProvincial Grand Inner Guard\nProvincial Grand Steward\nProvincial Grand Tyler',
    'Past Provincial Grand Master\nPast Deputy Provincial Grand Master\nPast Assistant Provincial Grand Master\nPast Provincial Grand Senior Warden\nPast Provincial Grand Junior Warden\nPast Provincial Grand Master Overseer\nPast Provincial Grand Senior Overseer\nPast Provincial Grand Junior Overseer\nPast Provincial Grand Chaplain\nPast Provincial Grand Treasurer\nPast Provincial Grand Registrar\nPast Provincial Grand Secretary\nPast Provincial Grand Director of Ceremonies\nPast Provincial Grand Senior Deacon\nPast Provincial Grand Junior Deacon\nPast Provincial Assistant Grand Chaplain\nPast Provincial Assistant Grand Charity Steward\nPast Provincial Assistant Grand Almoner\nPast Provincial Grand Organist\nPast Provincial Grand Standard Bearer\nPast Provincial Assistant Grand Standard Bearer\nPast Provincial Grand Inner Guard\nPast Provincial Grand Steward\nPast Provincial Grand Tyler',
    'Grand Master\nPro Grand Master\nDeputy Grand Master\nAssistant Grand Master\nSenior Grand Warden\nJunior Grand Warden\nGrand Inspector\nGrand Master Overseer\nGrand Senior Overseer\nGrand Junior Overseer\nGrand Chaplain\nPresident of the Board of General Purposes\nGrand Treasurer\nGrand Registrar\nPresident of the Mark Benevolent Fund\nGrand Secretary\nGrand Director of Ceremonies\nGrand Inspector of Works\nGrand Sword Bearer\nDeputy Grand Chaplain\nDeputy President of the General Board\nDeputy Grand Registrar\nDeputy President of the Mark Benevolent Fund\nDeputy Grand Secretary\nDeputy Grand Director of Ceremonies\nDeputy Grand Inspectors of Works\nDeputy Grand Sword Bearer\nGrand Senior Deacon\nGrand Junior Deacon\nAssistant Grand Chaplain\nAssistant Grand Registrar\nAssistant Grand Secretary\nAssistant Grand Directors of Ceremonies\nAssistant Grand Inspector of Works\nAssistant Grand Sword Bearers\nGrand Librarian\nGrand Organist\nGrand Standard Bearer\nAssistant Grand Standard Bearer\nDeputy Grand Organist\nGrand Inner Guard\nAssistant Grand Inner Guard\nGrand Steward\nGrand Tyler\nDeputy Grand Tyler',
    'Past Grand Master\nPro Grand Master\nPast Deputy Grand Master\nPast Assistant Grand Master\nPast Senior Grand Warden\nPast Junior Grand Warden\nPast Grand Inspector\nPast Grand Master Overseer\nPast Grand Senior Overseer\nPast Grand Junior Overseer\nPast Grand Chaplain\nPast President of the Board of General Purposes\nPast Grand Treasurer\nPast Grand Registrar\nPast President of the Mark Benevolent Fund\nPast Grand Secretary\nPast Grand Director of Ceremonies\nPast Grand Inspector of Works\nPast Grand Sword Bearer\nPast Deputy Grand Chaplain\nPast Deputy President of the General Board\nPast Deputy Grand Registrar\nPast Deputy President of the Mark Benevolent Fund\nPast Deputy Grand Secretary\nPast Deputy Grand Director of Ceremonies\nPast Deputy Grand Inspectors of Works\nPast Deputy Grand Sword Bearer\nPast Grand Senior Deacon\nPast Grand Junior Deacon\nPast Assistant Grand Chaplain\nPast Assistant Grand Registrar\nPast Assistant Grand Secretary\nPast Assistant Grand Directors of Ceremonies\nPast Assistant Grand Inspector of Works\nPast Assistant Grand Sword Bearers\nPast Grand Librarian\nPast Grand Organist\nPast Grand Standard Bearer\nPast Assistant Grand Standard Bearer\nPast Deputy Grand Organist\nPast Grand Inner Guard\nPast Assistant Grand Inner Guard\nPast Grand Steward\nPast Grand Tyler\nPast Deputy Grand Tyler',
    '2026-09-22 15:52:26'
  ),
  (
    'royal_arch',
    'Comp\nEComp\nMEComp',
    '{\"Comp\":[{\"scope\":\"masonic\",\"rank\":\"Companion\"}],\"EComp\":[{\"scope\":\"masonic\",\"rank\":\"MEZ\"},{\"scope\":\"masonic\",\"rank\":\"IPZ\"},{\"scope\":\"masonic\",\"rank\":\"PZ\"},{\"scope\":\"grand\",\"rank\":\"President of the Committee General Purposes\"},{\"scope\":\"grand\",\"rank\":\"Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Grand Scribe Ezra\"},{\"scope\":\"grand\",\"rank\":\"Grand Scribe Nehemiah\"},{\"scope\":\"grand\",\"rank\":\"Grand Director of Cermonies\"},{\"scope\":\"grand\",\"rank\":\"Grand Sword Bearer\"},{\"scope\":\"grand\",\"rank\":\"Grand Inspector\"},{\"scope\":\"grand\",\"rank\":\"Grand Treasurer\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Scribe Ezra\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Sword Bearer\"},{\"scope\":\"grand\",\"rank\":\"Principal Grand Sojourner\"},{\"scope\":\"grand\",\"rank\":\"First Assistant Grand Sojourner\"},{\"scope\":\"grand\",\"rank\":\"Second Assistant Grand Sojourner\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Scribe Ezra\"},{\"scope\":\"grand\",\"rank\":\"Grand Standard Bearer\"},{\"scope\":\"grand\",\"rank\":\"Grand Organist\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Grand Janitor\"},{\"scope\":\"provincial\",\"rank\":\"Deputy Grand Superintendent\"},{\"scope\":\"provincial\",\"rank\":\"Second Provincial Grand Principal\"},{\"scope\":\"provincial\",\"rank\":\"Third Provincial Grand Principal\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Scribe Ezra\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Scribe Nehemiah\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Treasurer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Registrar\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Sword Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Deputy Grand Registrar\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Deputy Grand Scribe Ezra\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Deputy Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Inspectors\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Almoner\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Charity Steward\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Membership Officer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Communications Officer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Sojourner\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Mentor\"},{\"scope\":\"provincial\",\"rank\":\"Provincial First Assistant Grand Sojourner\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Second Assistant Grand Sojourner\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Scribe Ezra\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Organist\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Assistant Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Janitor\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Steward\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Steward\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Janitor\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Organist\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Scribe Ezra\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Second Assistant Grand Sojourner\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial First Assistant Grand Sojourner\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Sojourner\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Mentor\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Communications Officer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Membership Officer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Charity Steward\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Almoner\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Assistant Grand Inspectors\"},{\"scope\":\"provincial\",\"rank\":\"Past Deputy Grand Superintendent\"},{\"scope\":\"provincial\",\"rank\":\"Past Second Provincial Grand Principal\"},{\"scope\":\"provincial\",\"rank\":\"Past Third Provincial Grand Principal\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Scribe Ezra\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Scribe Nehemiah\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Treasurer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Registrar\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Sword Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Deputy Grand Registrar\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Deputy Grand Scribe Ezra\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Deputy Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Janitor\"},{\"scope\":\"grand\",\"rank\":\"Past Past Assistant Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Organist\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Standard Bearer\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Scribe Ezra\"},{\"scope\":\"grand\",\"rank\":\"Past Second Assistant Grand Sojourner\"},{\"scope\":\"grand\",\"rank\":\"Past First Assistant Grand Sojourner\"},{\"scope\":\"grand\",\"rank\":\"Past Principal Grand Sojourner\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Sword Bearer\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Scribe Ezra\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Treasurer\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Inspector\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Sword Bearer\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Director of Cermonies\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Scribe Nehemiah\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Scribe Ezra\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Registrar\"},{\"scope\":\"grand\",\"rank\":\"Past President of the Committee General Purposes\"}],\"MEComp\":[{\"scope\":\"grand\",\"rank\":\"First Grand Principal\"},{\"scope\":\"grand\",\"rank\":\"Pro First Grand Principal\"},{\"scope\":\"grand\",\"rank\":\"Second Grand Principal\"},{\"scope\":\"grand\",\"rank\":\"Third Grand Principal\"},{\"scope\":\"grand\",\"rank\":\"Past First Grand Principal\"},{\"scope\":\"grand\",\"rank\":\"Past Pro First Grand Principal\"},{\"scope\":\"grand\",\"rank\":\"Past Second Grand Principal\"},{\"scope\":\"grand\",\"rank\":\"Past Third Grand Principal\"},{\"scope\":\"provincial\",\"rank\":\"Most Excellent Grand Superintendent\"},{\"scope\":\"provincial\",\"rank\":\"Past Most Excellent Grand Superintendent\"}]}',
    'Companion\nMEZ\nIPZ\nPZ',
    'Most Excellent Grand Superintendent\nDeputy Grand Superintendent\nSecond Provincial Grand Principal\nThird Provincial Grand Principal\nProvincial Grand Scribe Ezra\nProvincial Grand Scribe Nehemiah\nProvincial Grand Treasurer\nProvincial Grand Registrar\nProvincial Grand Director of Ceremonies\nProvincial Grand Sword Bearer\nProvincial Deputy Grand Registrar\nProvincial Deputy Grand Scribe Ezra\nProvincial Deputy Grand Director of Ceremonies\nProvincial Assistant Grand Inspectors\nProvincial Grand Almoner\nProvincial Grand Charity Steward\nProvincial Grand Membership Officer\nProvincial Grand Communications Officer\nProvincial Grand Mentor\nProvincial Grand Sojourner\nProvincial First Assistant Grand Sojourner\nProvincial Second Assistant Grand Sojourner\nProvincial Assistant Grand Scribe Ezra\nProvincial Grand Standard Bearer\nProvincial Grand Organist\nProvincial Assistant Grand Director of Ceremonies\nProvincial Grand Janitor\nProvincial Grand Steward',
    'Past Most Excellent Grand Superintendent\nPast Deputy Grand Superintendent\nPast Second Provincial Grand Principal\nPast Third Provincial Grand Principal\nPast Provincial Grand Scribe Ezra\nPast Provincial Grand Scribe Nehemiah\nPast Provincial Grand Treasurer\nPast Provincial Grand Registrar\nPast Provincial Grand Director of Ceremonies\nPast Provincial Grand Sword Bearer\nPast Provincial Deputy Grand Registrar\nPast Provincial Deputy Grand Scribe Ezra\nPast Provincial Deputy Grand Director of Ceremonies\nPast Provincial Assistant Grand Inspectors\nPast Provincial Grand Almoner\nPast Provincial Grand Charity Steward\nPast Provincial Grand Membership Officer\nPast Provincial Grand Communications Officer\nPast Provincial Grand Mentor\nPast Provincial Grand Sojourner\nPast Provincial First Assistant Grand Sojourner\nPast Provincial Second Assistant Grand Sojourner\nPast Provincial Assistant Grand Scribe Ezra\nPast Provincial Grand Standard Bearer\nPast Provincial Grand Organist\nPast Provincial Assistant Grand Director of Ceremonies\nPast Provincial Grand Janitor\nPast Provincial Grand Steward',
    'First Grand Principal\nPro First Grand Principal\nSecond Grand Principal\nThird Grand Principal\nPresident of the Committee General Purposes\nGrand Registrar\nGrand Scribe Ezra\nGrand Scribe Nehemiah\nGrand Director of Cermonies\nGrand Sword Bearer\nGrand Inspector\nGrand Treasurer\nDeputy Grand Registrar\nDeputy Grand Scribe Ezra\nDeputy Grand Director of Ceremonies\nDeputy Grand Sword Bearer\nPrincipal Grand Sojourner\nFirst Assistant Grand Sojourner\nSecond Assistant Grand Sojourner\nAssistant Grand Scribe Ezra\nGrand Standard Bearer\nGrand Organist\nPast Assistant Grand Director of Ceremonies\nGrand Janitor',
    'Past First Grand Principal\nPast Pro First Grand Principal\nPast Second Grand Principal\nPast Third Grand Principal\nPast President of the Committee General Purposes\nPast Grand Registrar\nPast Grand Scribe Ezra\nPast Grand Scribe Nehemiah\nPast Grand Director of Cermonies\nPast Grand Sword Bearer\nPast Grand Inspector\nPast Grand Treasurer\nPast Deputy Grand Registrar\nPast Deputy Grand Scribe Ezra\nPast Deputy Grand Director of Ceremonies\nPast Deputy Grand Sword Bearer\nPast Principal Grand Sojourner\nPast First Assistant Grand Sojourner\nPast Second Assistant Grand Sojourner\nPast Assistant Grand Scribe Ezra\nPast Grand Standard Bearer\nPast Grand Organist\nPast Past Assistant Grand Director of Ceremonies\nPast Grand Janitor',
    '2026-09-22 15:37:29'
  ),
  (
    'royal_ark_mariners',
    'Bro\nWBro\nRWBro\nMWBro',
    '{\"Bro\":[{\"scope\":\"masonic\",\"rank\":\"Royal Ark Mariner\"},{\"scope\":\"provincial\",\"rank\":\"Royal Ark Mariner Provincial Grand Rank\"},{\"scope\":\"provincial\",\"rank\":\"Past Royal Ark Mariner Provincial Grand Rank\"}],\"WBro\":[{\"scope\":\"grand\",\"rank\":\"Royal Ark Mariner Grand Rank\"},{\"scope\":\"grand\",\"rank\":\"Grand Master\'s Royal Ark Council Honoris Causa\"},{\"scope\":\"grand\",\"rank\":\"Member of the Grand Master\'s Royal Ark Council\"},{\"scope\":\"grand\",\"rank\":\"Past Royal Ark Mariner Grand Rank\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Master\'s Royal Ark Council Honoris Causa\"},{\"scope\":\"provincial\",\"rank\":\"Deputy Provincial Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"Assistant Provincial Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"Royal Ark Mariner Provincial Grand Rank\"},{\"scope\":\"provincial\",\"rank\":\"Past Deputy Provincial Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"Past Assistant Provincial Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"Past Royal Ark Mariner Provincial Grand Rank\"},{\"scope\":\"grand\",\"rank\":\"Past Member of the Grand Master\'s Royal Ark Council\"}],\"RWBro\":[{\"scope\":\"grand\",\"rank\":\"Deputy Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Member of the Grand Master\'s Royal Ark Council\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Past Member of the Grand Master\'s Royal Ark Council\"},{\"scope\":\"provincial\",\"rank\":\"Provincial Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"Past Provincial Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Grand Master\'s Royal Ark Council Honoris Causa\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Master\'s Royal Ark Council Honoris Causa\"}],\"MWBro\":[{\"scope\":\"grand\",\"rank\":\"Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Pro Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Past Pro Grand Master\"}]}',
    'Royal Ark Mariner\nWorshipful Commander\nImmediate Past Commander\nPast Commander',
    'Provincial Grand Master\nDeputy Provincial Grand Master\nAssistant Provincial Grand Master\nRoyal Ark Mariner Provincial Grand Rank',
    'Past Provincial Grand Master\nPast Deputy Provincial Grand Master\nPast Assistant Provincial Grand Master\nPast Royal Ark Mariner Provincial Grand Rank',
    'Grand Master\nPro Grand Master\nDeputy Grand Master\nAssistant Grand Master\nMember of the Grand Master\'s Royal Ark Council\nGrand Master\'s Royal Ark Council Honoris Causa\nRoyal Ark Mariner Grand Rank',
    'Past Grand Master\nPast Pro Grand Master\nPast Deputy Grand Master\nPast Assistant Grand Master\nPast Member of the Grand Master\'s Royal Ark Council\nPast Grand Master\'s Royal Ark Council Honoris Causa\nPast Royal Ark Mariner Grand Rank',
    '2026-09-22 15:59:01'
  ),
  (
    'royal_select',
    'Comp\nIll.\nV.Ill\nR.Ill.\nM.Ill.',
    '{\"Comp\":[{\"scope\":\"masonic\",\"rank\":\"Select Master\"},{\"scope\":\"masonic\",\"rank\":\"Royal Master\"},{\"scope\":\"masonic\",\"rank\":\"Most Excellent Master\"},{\"scope\":\"masonic\",\"rank\":\"Super-Excellent Master\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Sentinel\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Manciple\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Steward\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Steward\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Manciple\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Sentinel\"}],\"Ill.\":[{\"scope\":\"grand\",\"rank\":\"Deputy Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Grand Sentinel\"},{\"scope\":\"grand\",\"rank\":\"Grand Manciple\"},{\"scope\":\"grand\",\"rank\":\"Grand Steward\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Organist\"},{\"scope\":\"grand\",\"rank\":\"Grand Organist\"},{\"scope\":\"grand\",\"rank\":\"Grand Sword Bearer\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Recorder\"},{\"scope\":\"grand\",\"rank\":\"Grand Conductor of the Council\"},{\"scope\":\"grand\",\"rank\":\"Assistant Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Grand Captain of the Guard\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Recorder\"},{\"scope\":\"grand\",\"rank\":\"Deputy Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Recorder\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Captain of the Guard\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Conductor of the Council\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Recorder\"},{\"scope\":\"grand\",\"rank\":\"Past Assistant Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Sword Bearer\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Organist\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Organist\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Steward\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Manciple\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Sentinel\"},{\"scope\":\"provincial\",\"rank\":\"Deputy District Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"District Principal Conductor of the Work\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Chaplain\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Recorder\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Lecturer\"},{\"scope\":\"provincial\",\"rank\":\"District Deputy Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Captain of the Guard\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Almoner\"},{\"scope\":\"provincial\",\"rank\":\"District Assistant Grand Recorder\"},{\"scope\":\"provincial\",\"rank\":\"District Assistant Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Sword Bearer\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Organist\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Steward\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Manciple\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Sentinel\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Sentinel\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Manciple\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Steward\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Organist\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Standard Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Sword Bearer\"},{\"scope\":\"provincial\",\"rank\":\"Past District Assistant Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Past District Assistant Grand Recorder\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Almoner\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Captain of the Guard\"},{\"scope\":\"provincial\",\"rank\":\"Past District Deputy Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Lecturer\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Director of Ceremonies\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Recorder\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Chaplain\"},{\"scope\":\"provincial\",\"rank\":\"Past District Principal Conductor of the Work\"},{\"scope\":\"provincial\",\"rank\":\"Past Deputy District Grand Master\"}],\"V.Ill\":[{\"scope\":\"grand\",\"rank\":\"Grand Inspector\"},{\"scope\":\"grand\",\"rank\":\"Grand Historiographer\"},{\"scope\":\"grand\",\"rank\":\"Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Grand Treasurer\"},{\"scope\":\"grand\",\"rank\":\"President of the Executive Committee\"},{\"scope\":\"grand\",\"rank\":\"Grand Recorder\"},{\"scope\":\"grand\",\"rank\":\"Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Grand Lecturer\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Inspector\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Chaplain\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Treasurer\"},{\"scope\":\"grand\",\"rank\":\"Past President of the Executive Committee\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Recorder\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Director of Ceremonies\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Lecturer\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Historiographer\"}],\"R.Ill.\":[{\"scope\":\"grand\",\"rank\":\"Deputy Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Grand Councillor\"},{\"scope\":\"grand\",\"rank\":\"Grand Principal Conductor of the Work\"},{\"scope\":\"grand\",\"rank\":\"Order of Service to Cryptic Masonry\"},{\"scope\":\"grand\",\"rank\":\"Custodian of the Ninth Arch\"},{\"scope\":\"grand\",\"rank\":\"Grand Chancellor\"},{\"scope\":\"grand\",\"rank\":\"Past Deputy Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Principal Conductor of the Work\"},{\"scope\":\"grand\",\"rank\":\"Past Order of Service to Cryptic Masonry\"},{\"scope\":\"grand\",\"rank\":\"Past Custodian of the Ninth Arch\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Chancellor\"},{\"scope\":\"provincial\",\"rank\":\"District Grand Master\"},{\"scope\":\"provincial\",\"rank\":\"Past District Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Councillor\"}],\"M.Ill.\":[{\"scope\":\"grand\",\"rank\":\"Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Pro Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Past Grand Master\"},{\"scope\":\"grand\",\"rank\":\"Past Pro Grand Master\"}]}',
    'Select Master\nRoyal Master\nMost Excellent Master\nSuper-Excellent Master\nThrice Illustrious Master\nExcellent Master',
    'District Grand Master\nDeputy District Grand Master\nDistrict Principal Conductor of the Work\nDistrict Grand Chaplain\nDistrict Grand Recorder\nDistrict Grand Director of Ceremonies\nDistrict Grand Lecturer\nDistrict Deputy Grand Director of Ceremonies\nDistrict Grand Captain of the Guard\nDistrict Grand Almoner\nDistrict Assistant Grand Recorder\nDistrict Assistant Grand Director of Ceremonies\nDistrict Grand Sword Bearer\nDistrict Grand Standard Bearer\nDistrict Grand Organist\nDistrict Grand Steward\nDistrict Grand Manciple\nDistrict Grand Sentinel',
    'Past District Grand Master\nPast Deputy District Grand Master\nPast District Principal Conductor of the Work\nPast District Grand Chaplain\nPast District Grand Recorder\nPast District Grand Director of Ceremonies\nPast District Grand Lecturer\nPast District Deputy Grand Director of Ceremonies\nPast District Grand Captain of the Guard\nPast District Grand Almoner\nPast District Assistant Grand Recorder\nPast District Assistant Grand Director of Ceremonies\nPast District Grand Sword Bearer\nPast District Grand Standard Bearer\nPast District Grand Organist\nPast District Grand Steward\nPast District Grand Manciple\nPast District Grand Sentinel',
    'Grand Master\nPro Grand Master\nDeputy Grand Master\nGrand Principal Conductor of the Work\nOrder of Service to Cryptic Masonry\nCustodian of the Ninth Arch\nGrand Chancellor\nGrand Councillor\nGrand Inspector\nGrand Chaplain\nGrand Treasurer\nPresident of the Executive Committee\nGrand Recorder\nGrand Director of Ceremonies\nGrand Lecturer\nGrand Historiographer\nDeputy Grand Chaplain\nDeputy Grand Recorder\nDeputy Grand Director of Ceremonies\nGrand Captain of the Guard\nGrand Conductor of the Council\nAssistant Grand Chaplain\nAssistant Grand Recorder\nAssistant Grand Director of Ceremonies\nGrand Sword Bearer\nGrand Organist\nDeputy Grand Organist\nGrand Steward\nGrand Manciple\nGrand Sentinel',
    'Past Grand Master\nPast Pro Grand Master\nPast Deputy Grand Master\nPast Grand Principal Conductor of the Work\nPast Order of Service to Cryptic Masonry\nPast Custodian of the Ninth Arch\nPast Grand Chancellor\nPast Grand Councillor\nPast Grand Inspector\nPast Grand Chaplain\nPast Grand Treasurer\nPast President of the Executive Committee\nPast Grand Recorder\nPast Grand Director of Ceremonies\nPast Grand Lecturer\nPast Grand Historiographer\nPast Deputy Grand Chaplain\nPast Deputy Grand Recorder\nPast Deputy Grand Director of Ceremonies\nPast Grand Captain of the Guard\nPast Grand Conductor of the Council\nPast Assistant Grand Chaplain\nPast Assistant Grand Recorder\nPast Assistant Grand Director of Ceremonies\nPast Grand Sword Bearer\nPast Grand Organist\nPast Deputy Grand Organist\nPast Grand Steward\nPast Grand Manciple\nPast Grand Sentinel',
    '2026-09-22 16:19:57'
  );

CREATE TABLE
  custom_orders (
    order_key VARCHAR(40) PRIMARY KEY,
    order_name VARCHAR(150) NOT NULL,
    member_noun VARCHAR(50) NOT NULL DEFAULT 'Member',
    body_noun VARCHAR(50) NOT NULL DEFAULT 'Lodge',
    salutations TEXT NULL,
    active TINYINT (1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_custom_order_name (order_name)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(254) NOT NULL,
    display_name VARCHAR(150) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM (
      'admin',
      'secretary',
      'dining',
      'treasurer',
      'worshipful_master',
      'readonly',
      'member'
    ) NOT NULL DEFAULT 'member',
    active TINYINT (1) NOT NULL DEFAULT 1,
    failed_logins INT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME NULL,
    last_login_at DATETIME NULL,
    password_changed_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_user_email (email)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  bookings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    meeting_id BIGINT UNSIGNED NOT NULL,
    booking_reference VARCHAR(50) NOT NULL,
    booking_code_hash VARCHAR(255) NOT NULL,
    status ENUM (
      'active',
      'cancelled',
      'waiting',
      'offered',
      'declined'
    ) NOT NULL DEFAULT 'active',
    attendance ENUM (
      'meeting_and_festive_board',
      'meeting_only',
      'apology'
    ) NOT NULL,
    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,
    future_contact_consent TINYINT (1) NOT NULL DEFAULT 0,
    is_lodge_member TINYINT (1) NOT NULL DEFAULT 0,
    attendee_type ENUM (
      'lodge_member',
      'visiting_member',
      'special_event_guest',
      'ladies_evening_guest',
      'white_table_guest',
      'blue_table_guest'
    ) NOT NULL DEFAULT 'visiting_member',
    attendee_type_label VARCHAR(120) NULL,
    lodge_name VARCHAR(150) NULL,
    lodge_number VARCHAR(30) NULL,
    masonic_rank VARCHAR(150) NULL,
    provincial_rank VARCHAR(150) NULL,
    grand_rank VARCHAR(150) NULL,
    dining TINYINT (1) NOT NULL DEFAULT 0,
    dietary_requirements TEXT NULL,
    allergy_details TEXT NULL,
    notes TEXT NULL,
    payment_selection ENUM (
      'pay',
      'provincial_visit_pay',
      'grand_lodge_visit_pay',
      'provincial_visit',
      'grand_lodge_visit',
      'not_required'
    ) NOT NULL DEFAULT 'not_required',
    dining_subtotal DECIMAL(10, 2) NOT NULL DEFAULT 0,
    raffle_ticket_quantity INT UNSIGNED NOT NULL DEFAULT 0,
    raffle_ticket_unit_price DECIMAL(10, 2) NOT NULL DEFAULT 0,
    raffle_subtotal DECIMAL(10, 2) NOT NULL DEFAULT 0,
    total_charge DECIMAL(10, 2) NOT NULL DEFAULT 0,
    amount_received DECIMAL(10, 2) NOT NULL DEFAULT 0,
    refund_amount DECIMAL(10, 2) NOT NULL DEFAULT 0,
    payment_status ENUM (
      'unpaid',
      'paid',
      'exempt',
      'not_required',
      'refunded'
    ) NOT NULL DEFAULT 'not_required',
    payment_method VARCHAR(50) NULL,
    payment_reference VARCHAR(100) NULL,
    paid_at DATETIME NULL,
    refunded_at DATETIME NULL,
    treasurer_notes TEXT NULL,
    payment_updated_by BIGINT UNSIGNED NULL,
    arrived TINYINT (1) NOT NULL DEFAULT 0,
    arrived_at DATETIME NULL,
    checked_in_by BIGINT UNSIGNED NULL,
    confirmation_sent_at DATETIME NULL,
    calendar_sent_at DATETIME NULL,
    test_booking TINYINT (1) NOT NULL DEFAULT 0,
    cancelled_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_booking_reference (booking_reference),
    INDEX idx_booking_meeting (meeting_id),
    INDEX idx_booking_email (email),
    INDEX idx_booking_status (meeting_id, status),
    INDEX idx_booking_payment (meeting_id, payment_status),
    CONSTRAINT fk_booking_meeting FOREIGN KEY (meeting_id) REFERENCES meetings (id) ON DELETE RESTRICT,
    CONSTRAINT fk_payment_updated_by FOREIGN KEY (payment_updated_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_booking_checked_in_by FOREIGN KEY (checked_in_by) REFERENCES users (id) ON DELETE SET NULL
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  guests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    booking_id BIGINT UNSIGNED NOT NULL,
    salutation VARCHAR(30) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    surname VARCHAR(100) NOT NULL,
    email VARCHAR(254) NULL,
    retention_consent TINYINT (1) NOT NULL DEFAULT 0,
    guest_contact_id BIGINT UNSIGNED 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,
    dining TINYINT (1) NOT NULL DEFAULT 0,
    dietary_requirements TEXT NULL,
    allergy_details TEXT NULL,
    dining_charge DECIMAL(10, 2) NOT NULL DEFAULT 0,
    arrived TINYINT (1) NOT NULL DEFAULT 0,
    arrived_at DATETIME NULL,
    checked_in_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_guest_booking (booking_id),
    INDEX idx_guest_email (email),
    INDEX idx_guest_contact (guest_contact_id),
    INDEX idx_guest_arrival (arrived, arrived_at),
    CONSTRAINT fk_guest_booking FOREIGN KEY (booking_id) REFERENCES bookings (id) ON DELETE CASCADE,
    CONSTRAINT fk_guest_checked_in_by FOREIGN KEY (checked_in_by) REFERENCES users (id) ON DELETE SET NULL
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  guest_contacts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sponsor_email VARCHAR(254) NOT NULL,
    salutation VARCHAR(30) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    surname VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT 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,
    dietary_requirements TEXT NULL,
    allergy_details TEXT 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_guest_contact_sponsor (sponsor_email, email),
    INDEX idx_guest_contact_email (email),
    INDEX idx_guest_contact_consent (consent_withdrawn_at, last_booked_at)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

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;

ALTER TABLE guests ADD CONSTRAINT fk_guest_contact FOREIGN KEY (guest_contact_id) REFERENCES guest_contacts (id) ON DELETE SET NULL;

CREATE TABLE
  guest_invitations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    guest_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_guest_meeting_invitation (guest_contact_id, meeting_id),
    CONSTRAINT fk_guest_invitation_contact FOREIGN KEY (guest_contact_id) REFERENCES guest_contacts (id) ON DELETE CASCADE,
    CONSTRAINT fk_guest_invitation_meeting FOREIGN KEY (meeting_id) REFERENCES meetings (id) ON DELETE CASCADE
  ) 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;

CREATE TABLE
  contact_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    email VARCHAR(254) NOT NULL,
    subject VARCHAR(150) NOT NULL,
    other_subject VARCHAR(200) NULL,
    message TEXT NOT NULL,
    recipients TEXT NOT NULL,
    status ENUM ('new', 'sent', 'failed', 'archived') NOT NULL DEFAULT 'new',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_contact_status (status, created_at)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  reminders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    booking_id BIGINT UNSIGNED NOT NULL,
    reminder_type ENUM (
      'payment',
      'booking_deadline',
      'meeting',
      'waiting_list'
    ) NOT NULL,
    status ENUM ('pending', 'sent', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',
    scheduled_for DATETIME NOT NULL,
    sent_at DATETIME NULL,
    failure_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_reminder_schedule (status, scheduled_for),
    CONSTRAINT fk_reminder_booking FOREIGN KEY (booking_id) REFERENCES bookings (id) ON DELETE CASCADE
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  audit_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NULL,
    entity_id BIGINT UNSIGNED NULL,
    booking_reference VARCHAR(50) NULL,
    details TEXT NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_created (created_at),
    INDEX idx_audit_reference (booking_reference),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT NULL,
    updated_by BIGINT UNSIGNED NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_setting_user FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  lodge_settings (
    id TINYINT UNSIGNED NOT NULL PRIMARY KEY DEFAULT 1,
    lodge_name VARCHAR(150) NOT NULL,
    lodge_number VARCHAR(30) NOT NULL,
    lodge_short_name VARCHAR(100) NULL,
    order_name VARCHAR(150) NULL,
    province_name VARCHAR(200) NULL,
    lodge_logo_url TEXT NULL,
    lodge_honours_logo_url TEXT NULL,
    province_logo_url TEXT NULL,
    order_logo_url TEXT NULL,
    companion_order_logo_url TEXT NULL,
    charity_logo_url TEXT NULL,
    primary_colour VARCHAR(7) NOT NULL DEFAULT '#990033',
    secondary_colour VARCHAR(7) NOT NULL DEFAULT '#122849',
    accent_colour VARCHAR(7) NOT NULL DEFAULT '#C7A646',
    background_colour VARCHAR(7) NOT NULL DEFAULT '#F7F1E3',
    panel_colour VARCHAR(7) NOT NULL DEFAULT '#FFFDFA',
    text_colour VARCHAR(7) NOT NULL DEFAULT '#27231E',
    heading_font VARCHAR(120) NOT NULL DEFAULT 'Georgia, serif',
    body_font VARCHAR(120) NOT NULL DEFAULT 'system-ui, sans-serif',
    browser_title VARCHAR(200) NULL,
    favicon_url TEXT NULL,
    secretary_email VARCHAR(254) NULL,
    treasurer_email VARCHAR(254) NULL,
    dining_steward_email VARCHAR(254) NULL,
    director_of_ceremonies_email VARCHAR(254) NULL,
    almoner_email VARCHAR(254) NULL,
    general_email VARCHAR(254) NULL,
    provincial_website_url TEXT NULL,
    provincial_website_label VARCHAR(150) NULL,
    order_website_url TEXT NULL,
    order_website_label VARCHAR(150) NULL,
    book_of_constitutions_url TEXT NULL,
    bylaws_url TEXT NULL,
    timezone VARCHAR(80) NOT NULL DEFAULT 'Europe/London',
    currency_code CHAR(3) NOT NULL DEFAULT 'GBP',
    retention_meetings INT UNSIGNED NOT NULL DEFAULT 4,
    bank_account_name VARCHAR(150) NULL,
    bank_sort_code VARCHAR(20) NULL,
    bank_account_number VARCHAR(40) NULL,
    updated_by BIGINT UNSIGNED NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_lodge_settings_user FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  documents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description VARCHAR(500) NULL,
    document_url TEXT NOT NULL,
    category ENUM (
      'summons',
      'minutes',
      'bylaws',
      'constitutions',
      'forms_guidance',
      'other'
    ) NOT NULL DEFAULT 'other',
    visibility ENUM ('public', 'members', 'officers', 'administrators') NOT NULL DEFAULT 'members',
    active TINYINT (1) NOT NULL DEFAULT 1,
    display_order INT UNSIGNED NOT NULL DEFAULT 0,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_documents_category_visibility (category, visibility, active, display_order),
    CONSTRAINT fk_document_user FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  email_templates (
    template_key VARCHAR(80) PRIMARY KEY,
    display_name VARCHAR(150) NOT NULL,
    subject_template VARCHAR(250) NOT NULL,
    body_template MEDIUMTEXT NOT NULL,
    enabled TINYINT (1) NOT NULL DEFAULT 1,
    updated_by BIGINT UNSIGNED NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_email_template_user FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  communication_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    meeting_id BIGINT UNSIGNED NULL,
    booking_id BIGINT UNSIGNED NULL,
    template_key VARCHAR(80) NULL,
    recipient VARCHAR(254) NOT NULL,
    subject VARCHAR(250) NOT NULL,
    status ENUM ('queued', 'sent', 'failed') NOT NULL DEFAULT 'queued',
    error_message TEXT NULL,
    sent_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at DATETIME NULL,
    INDEX idx_communication_meeting (meeting_id, created_at),
    CONSTRAINT fk_communication_meeting FOREIGN KEY (meeting_id) REFERENCES meetings (id) ON DELETE SET NULL,
    CONSTRAINT fk_communication_booking FOREIGN KEY (booking_id) REFERENCES bookings (id) ON DELETE SET NULL,
    CONSTRAINT fk_communication_user FOREIGN KEY (sent_by) REFERENCES users (id) ON DELETE SET NULL
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  booking_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    booking_id BIGINT UNSIGNED NOT NULL,
    changed_by_user_id BIGINT UNSIGNED NULL,
    changed_by_type ENUM ('attendee', 'officer', 'system') NOT NULL DEFAULT 'system',
    field_name VARCHAR(100) NOT NULL,
    old_value TEXT NULL,
    new_value TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_history_booking (booking_id, created_at),
    CONSTRAINT fk_history_booking FOREIGN KEY (booking_id) REFERENCES bookings (id) ON DELETE CASCADE,
    CONSTRAINT fk_history_user FOREIGN KEY (changed_by_user_id) REFERENCES users (id) ON DELETE SET NULL
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  dining_tables (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    meeting_id BIGINT UNSIGNED NOT NULL,
    table_name VARCHAR(100) NOT NULL,
    capacity INT UNSIGNED NOT NULL DEFAULT 8,
    display_order INT UNSIGNED NOT NULL DEFAULT 0,
    UNIQUE KEY uq_dining_table (meeting_id, table_name),
    CONSTRAINT fk_table_meeting FOREIGN KEY (meeting_id) REFERENCES meetings (id) ON DELETE CASCADE
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  seat_assignments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dining_table_id BIGINT UNSIGNED NOT NULL,
    booking_id BIGINT UNSIGNED NOT NULL,
    guest_id BIGINT UNSIGNED NULL,
    seat_number INT UNSIGNED NULL,
    UNIQUE KEY uq_booking_guest_seat (booking_id, guest_id),
    CONSTRAINT fk_seat_table FOREIGN KEY (dining_table_id) REFERENCES dining_tables (id) ON DELETE CASCADE,
    CONSTRAINT fk_seat_booking FOREIGN KEY (booking_id) REFERENCES bookings (id) ON DELETE CASCADE,
    CONSTRAINT fk_seat_guest FOREIGN KEY (guest_id) REFERENCES guests (id) ON DELETE CASCADE
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  system_jobs (
    job_key VARCHAR(80) PRIMARY KEY,
    last_started_at DATETIME NULL,
    last_completed_at DATETIME NULL,
    last_status ENUM ('never', 'running', 'success', 'failed') NOT NULL DEFAULT 'never',
    last_message TEXT NULL
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  backup_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM ('success', 'failed') NOT NULL,
    message TEXT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_backup_user FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

INSERT INTO
  meetings (
    lodge_name,
    lodge_number,
    title,
    meeting_date,
    meeting_start,
    meeting_end,
    booking_deadline,
    venue_name,
    venue_address,
    dining_capacity,
    member_price,
    visitor_price,
    summons_url,
    menu_note,
    bookings_open,
    lifecycle_status
  )
VALUES
  (
    'Lea Mark Lodge',
    '405',
    'Regular Meeting and Festive Board',
    '2026-11-19',
    '2026-11-19 17:15:00',
    '2026-11-19 22:30:00',
    '2026-11-12 23:59:59',
    'Luton Masonic Hall',
    'The Pavilion, Bowling Green Lane, Luton, LU2 7HR',
    80,
    30.00,
    15.00,
    'CHANGE_ME_SUMMONS_URL',
    'The Festive Board is a set menu. Alterations are limited to necessary dietary requirements.',
    1,
    'open'
  );

INSERT INTO
  meeting_menu (
    meeting_id,
    course_name,
    dish,
    vegetarian_alternative,
    display_order
  )
VALUES
  (
    1,
    'Starter',
    'Creamy Tarragon Mushrooms on Ciabatta Toast',
    NULL,
    1
  ),
  (
    1,
    'Main Course',
    'Roasted Gammon with Honey Mustard Sauce, potatoes and seasonal vegetables',
    'Beetroot Wellington, potatoes and seasonal vegetables',
    2
  ),
  (1, 'Dessert', 'Eton Mess', NULL, 3),
  (
    1,
    'Cheese Board',
    'Traditional cheese and crackers',
    NULL,
    4
  ),
  (1, 'Tea & Coffee', 'Tea and coffee', NULL, 5);

INSERT INTO
  lodge_settings (
    id,
    lodge_name,
    lodge_number,
    lodge_short_name,
    order_name,
    province_name
  )
VALUES
  (
    1,
    'Lea Mark Lodge',
    '405',
    'Lea Mark Lodge 405',
    'Mark Master Masons',
    ''
  );

INSERT INTO
  email_templates (
    template_key,
    display_name,
    subject_template,
    body_template
  )
VALUES
  (
    'booking_confirmation',
    'Booking confirmation',
    '{{lodge_name}} booking confirmation – {{meeting_date}}',
    'Booking confirmation\n\nDear {{name}},\n\nYour attendance has been recorded for {{meeting_date}}.\n\nReference: {{booking_reference}}\nBooking & Cancellation code: {{booking_code}}\n\nTotal due: {{total_charge}}\n\n{{payment_details}}\nPayment reference: {{booking_reference}}\n\nPlease keep your booking & cancellation code safe.'
  ),
  (
    'payment_reminder',
    'Payment reminder',
    'Payment reminder – {{booking_reference}}',
    '<p>Dear {{name}},</p><p>Our records show a balance of <strong>{{balance}}</strong> for {{meeting_date}}.</p>{{payment_details}}'
  ),
  (
    'meeting_reminder',
    'Meeting reminder',
    'Reminder – {{meeting_title}} on {{meeting_date}}',
    '<p>Dear {{name}},</p><p>This is a reminder that {{meeting_title}} is on {{meeting_date}} at {{meeting_time}} at {{venue_name}}.</p>'
  ),
  (
    'booking_code',
    'Booking code',
    'Your booking code – {{booking_reference}}',
    '<p>Dear {{name}},</p><p>Your new booking code is <strong>{{booking_code}}</strong>.</p>'
  ),
  (
    'place_confirmed',
    'Waiting-list place confirmed',
    'Your place is confirmed – {{booking_reference}}',
    '<p>Dear {{name}},</p><p>Your place for {{meeting_title}} on {{meeting_date}} is now confirmed.</p>'
  ),
  (
    'booking_cancelled',
    'Booking cancelled',
    'Booking cancelled – {{booking_reference}}',
    '<p>Your booking for {{meeting_date}} has been cancelled.</p>'
  ),
  (
    'waiting_list',
    'Waiting list confirmation',
    'Waiting list – {{meeting_date}}',
    '<p>You have been added to the waiting list. We will contact you if a place becomes available.</p>'
  ),
  (
    'place_offered',
    'Waiting-list place offered',
    'A dining place is available',
    '<p>A place is available until {{offer_expiry}}. Please use your booking code to accept it.</p>'
  ),
  (
    'meeting_changed',
    'Meeting details changed',
    'Important update – {{meeting_date}}',
    '<p>The meeting details have changed. Please review the updated information.</p>'
  );

INSERT INTO
  email_templates (
    template_key,
    display_name,
    subject_template,
    body_template
  )
VALUES
  (
    'summons_email',
    'Summons or invitation email',
    '{{meeting_title}} – {{document_name}}',
    '{{meeting_title}}\n\n{{meeting_date}} at {{meeting_time}}\n{{venue_name}}\n\nYour {{document_name}} is available here:\n{{document_url}}'
  );

INSERT INTO
  email_templates (
    template_key,
    display_name,
    subject_template,
    body_template
  )
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.'
  ),
  (
    'guest_meeting_invitation',
    'Previous guest meeting invitation',
    '{{lodge_name}} invitation — {{meeting_title}}',
    'Dear {{name}},\n\nYou previously attended as a guest and agreed that the Lodge could retain your details and contact you about future meetings.\n\nThe summons for {{meeting_title}} on {{meeting_date}} at {{meeting_time}} is now available.\n\nView the summons: {{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.'
  );

INSERT INTO
  system_jobs (job_key)
VALUES
  ('reminders'),
  ('retention'),
  ('backup');

-- Operations and resilience features (schema version 22).
ALTER TABLE communication_log
ADD COLUMN body_html MEDIUMTEXT NULL AFTER subject,
ADD COLUMN retry_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER error_message,
ADD COLUMN last_attempt_at DATETIME NULL AFTER retry_count;

ALTER TABLE bookings
ADD COLUMN offer_expires_at DATETIME NULL AFTER cancelled_at,
ADD COLUMN anonymised_at DATETIME NULL AFTER offer_expires_at;

ALTER TABLE lodge_settings
ADD COLUMN retention_months INT UNSIGNED NOT NULL DEFAULT 18 AFTER retention_meetings,
ADD COLUMN automatic_backup TINYINT (1) NOT NULL DEFAULT 1 AFTER retention_months,
ADD COLUMN backup_retention_days INT UNSIGNED NOT NULL DEFAULT 30 AFTER automatic_backup,
ADD COLUMN health_alert_email VARCHAR(254) NULL AFTER backup_retention_days;

ALTER TABLE users
ADD COLUMN two_factor_enabled TINYINT (1) NOT NULL DEFAULT 0 AFTER active;

CREATE TABLE
  login_challenges (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    code_hash VARCHAR(255) NOT NULL,
    expires_at DATETIME NOT NULL,
    attempts INT UNSIGNED NOT NULL DEFAULT 0,
    used_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_login_challenge_user (user_id, expires_at),
    CONSTRAINT fk_login_challenge_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  password_reset_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    selector CHAR(16) NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_password_reset_selector (selector),
    CONSTRAINT fk_password_reset_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  administrator_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    session_hash CHAR(64) NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    revoked_at DATETIME NULL,
    UNIQUE KEY uq_administrator_session_hash (session_hash),
    INDEX idx_administrator_session_user (user_id, last_seen_at),
    CONSTRAINT fk_administrator_session_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE
  schema_versions (
    version_number INT UNSIGNED PRIMARY KEY,
    version_name VARCHAR(150) NOT NULL,
    installed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

INSERT INTO
  schema_versions (version_number, version_name)
VALUES
  (22, 'Operations and resilience centre');

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

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

INSERT INTO
  schema_versions (version_number, version_name)
VALUES
  (28, 'Paying official visit classifications');

INSERT INTO
  system_jobs (job_key)
VALUES
  ('waiting_list') ON DUPLICATE KEY
UPDATE job_key =
VALUES
  (job_key);

-- ===== Tenant foundation =====
-- phpMyAdmin/manual equivalent of canonical migration 202609240001.
-- Existing installations only. Take a verified backup before importing.
-- Import this file first, then 202609240002_migration.sql.
-- MySQL 8+ / MariaDB 10.5+.

DELIMITER //
DROP PROCEDURE IF EXISTS migrate_202609240001//
CREATE PROCEDURE migrate_202609240001()
main: BEGIN
    DECLARE v_count BIGINT DEFAULT 0;
    DECLARE v_unit_id BIGINT UNSIGNED;
    DECLARE v_province_id BIGINT UNSIGNED;

    SELECT COUNT(*) INTO v_count
    FROM information_schema.tables
    WHERE table_schema=DATABASE()
      AND table_name IN (
        'users','meetings','lodge_settings','bookings','documents',
        'email_templates','order_rank_settings','settings','custom_orders',
        'system_jobs','contact_messages','guest_contacts','attendee_contacts',
        'communication_log','backup_log','audit_log'
      );
    IF v_count<>16 THEN
        SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT='202609240001 stopped: required legacy tables are missing or the wrong database is selected.';
    END IF;

    CREATE TABLE IF NOT EXISTS migration_history(
      version VARCHAR(12) PRIMARY KEY,
      name VARCHAR(200) NOT NULL,
      checksum CHAR(64) NOT NULL,
      applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      execution_ms INT UNSIGNED NOT NULL DEFAULT 0
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE IF NOT EXISTS historical_migration_reconciliation(
      id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      filename VARCHAR(255) NOT NULL,
      checksum CHAR(64) NOT NULL,
      status ENUM('present_unverified','reconciled','not_applied') NOT NULL DEFAULT 'present_unverified',
      notes TEXT NULL,
      reconciled_at DATETIME NULL,
      UNIQUE KEY uq_historical_filename(filename)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    IF EXISTS(SELECT 1 FROM migration_history WHERE version='202609240001') THEN
        SELECT '202609240001 is already recorded; no changes made.' AS migration_result;
        LEAVE main;
    END IF;

    SELECT COUNT(*) INTO v_count
    FROM information_schema.tables
    WHERE table_schema=DATABASE()
      AND table_name IN (
        'provinces','units','user_memberships','platform_email_templates',
        'province_email_templates','platform_order_rank_settings',
        'province_order_rank_settings','source_installations','import_runs',
        'import_id_map','import_conflicts'
      );
    IF v_count<>0 THEN
        SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT='202609240001 stopped: a partial tenancy schema exists. Restore the backup or obtain a targeted recovery; do not rerun blindly.';
    END IF;

    CREATE TABLE provinces(
      id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(200) NOT NULL,
      slug VARCHAR(100) NOT NULL,
      active TINYINT(1) NOT NULL DEFAULT 1,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      UNIQUE KEY uq_province_slug(slug)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE units(
      id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      province_id BIGINT UNSIGNED NOT NULL,
      name VARCHAR(200) NOT NULL,
      number VARCHAR(30) NOT NULL DEFAULT '',
      slug VARCHAR(100) NOT NULL,
      unit_type VARCHAR(50) NOT NULL DEFAULT 'lodge',
      order_key VARCHAR(40) NULL,
      active TINYINT(1) NOT NULL DEFAULT 1,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      UNIQUE KEY uq_unit_slug(slug),
      UNIQUE KEY uq_unit_province_name_number(province_id,name,number),
      KEY idx_unit_province_active(province_id,active),
      CONSTRAINT fk_unit_province FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE RESTRICT
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    INSERT INTO provinces(name,slug,active)
    SELECT COALESCE(NULLIF(TRIM(province_name),''),'Default Province'),'default-province',1
    FROM lodge_settings ORDER BY id LIMIT 1;

    INSERT INTO units(province_id,name,number,slug,unit_type,order_key,active)
    SELECT p.id,
           COALESCE(NULLIF(TRIM(ls.lodge_name),''),'Default Unit'),
           COALESCE(ls.lodge_number,''),
           CONCAT('unit-',LEFT(SHA2(CONCAT(COALESCE(ls.lodge_name,'unit'),'|',COALESCE(ls.lodge_number,'')),256),12)),
           'lodge',(SELECT masonic_order FROM meetings ORDER BY id LIMIT 1),1
    FROM lodge_settings ls CROSS JOIN provinces p
    WHERE p.slug='default-province'
    ORDER BY ls.id LIMIT 1;

    SELECT id,province_id INTO v_unit_id,v_province_id FROM units ORDER BY id LIMIT 1;
    IF v_unit_id IS NULL OR v_province_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT='202609240001 stopped: the default Province/unit could not be created. Check lodge_settings.';
    END IF;

    ALTER TABLE meetings
      ADD COLUMN unit_id BIGINT UNSIGNED NULL AFTER id,
      ADD COLUMN event_slug VARCHAR(120) NULL AFTER unit_id;
    UPDATE meetings
    SET unit_id=v_unit_id,
        event_slug=COALESCE(NULLIF(event_slug,''),CONCAT('event-',id));
    ALTER TABLE meetings
      MODIFY unit_id BIGINT UNSIGNED NOT NULL,
      MODIFY event_slug VARCHAR(120) NOT NULL,
      ADD UNIQUE KEY uq_meeting_unit_slug(unit_id,event_slug),
      ADD KEY idx_meeting_unit_public(unit_id,archived,bookings_open,lifecycle_status,booking_deadline),
      ADD CONSTRAINT fk_meeting_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT;

    ALTER TABLE lodge_settings ADD COLUMN unit_id BIGINT UNSIGNED NULL AFTER id;
    ALTER TABLE documents ADD COLUMN unit_id BIGINT UNSIGNED NULL AFTER id;
    ALTER TABLE contact_messages ADD COLUMN unit_id BIGINT UNSIGNED NULL AFTER id;
    ALTER TABLE guest_contacts ADD COLUMN unit_id BIGINT UNSIGNED NULL AFTER id;
    ALTER TABLE attendee_contacts ADD COLUMN unit_id BIGINT UNSIGNED NULL AFTER id;
    ALTER TABLE communication_log ADD COLUMN unit_id BIGINT UNSIGNED NULL AFTER id;
    ALTER TABLE backup_log ADD COLUMN unit_id BIGINT UNSIGNED NULL AFTER id;

    UPDATE lodge_settings SET unit_id=v_unit_id WHERE unit_id IS NULL;
    UPDATE documents SET unit_id=v_unit_id WHERE unit_id IS NULL;
    UPDATE contact_messages SET unit_id=v_unit_id WHERE unit_id IS NULL;
    UPDATE guest_contacts SET unit_id=v_unit_id WHERE unit_id IS NULL;
    UPDATE attendee_contacts SET unit_id=v_unit_id WHERE unit_id IS NULL;
    UPDATE communication_log SET unit_id=v_unit_id WHERE unit_id IS NULL;
    UPDATE backup_log SET unit_id=v_unit_id WHERE unit_id IS NULL;

    ALTER TABLE lodge_settings MODIFY unit_id BIGINT UNSIGNED NOT NULL,
      ADD KEY idx_lodge_settings_unit(unit_id),
      ADD CONSTRAINT fk_lodge_settings_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT;
    ALTER TABLE documents MODIFY unit_id BIGINT UNSIGNED NOT NULL,
      ADD KEY idx_documents_unit(unit_id),
      ADD CONSTRAINT fk_documents_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT;
    ALTER TABLE contact_messages MODIFY unit_id BIGINT UNSIGNED NOT NULL,
      ADD KEY idx_contact_messages_unit(unit_id),
      ADD CONSTRAINT fk_contact_messages_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT;
    ALTER TABLE guest_contacts MODIFY unit_id BIGINT UNSIGNED NOT NULL,
      ADD KEY idx_guest_contacts_unit(unit_id),
      ADD CONSTRAINT fk_guest_contacts_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT;
    ALTER TABLE attendee_contacts MODIFY unit_id BIGINT UNSIGNED NOT NULL,
      ADD KEY idx_attendee_contacts_unit(unit_id),
      ADD CONSTRAINT fk_attendee_contacts_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT;
    ALTER TABLE communication_log MODIFY unit_id BIGINT UNSIGNED NOT NULL,
      ADD KEY idx_communication_log_unit(unit_id),
      ADD CONSTRAINT fk_communication_log_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT;
    ALTER TABLE backup_log MODIFY unit_id BIGINT UNSIGNED NOT NULL,
      ADD KEY idx_backup_log_unit(unit_id),
      ADD CONSTRAINT fk_backup_log_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT;

    ALTER TABLE audit_log ADD COLUMN unit_id BIGINT UNSIGNED NULL AFTER id,
      ADD KEY idx_audit_unit_created(unit_id,created_at),
      ADD CONSTRAINT fk_audit_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE SET NULL;
    UPDATE audit_log SET unit_id=v_unit_id WHERE unit_id IS NULL;

    ALTER TABLE lodge_settings
      ADD UNIQUE KEY uq_lodge_settings_unit(unit_id),
      MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      ADD COLUMN legal_contact_email VARCHAR(254) NULL AFTER general_email,
      ADD COLUMN email_display_name VARCHAR(150) NULL AFTER legal_contact_email,
      ADD COLUMN booking_reference_prefix VARCHAR(30) NULL AFTER currency_code;
    UPDATE lodge_settings
    SET booking_reference_prefix=COALESCE(NULLIF(booking_reference_prefix,''),CONCAT('U',REPLACE(lodge_number,' ',''))),
        legal_contact_email=COALESCE(legal_contact_email,secretary_email),
        email_display_name=COALESCE(email_display_name,lodge_short_name,lodge_name)
    WHERE unit_id=v_unit_id;

    ALTER TABLE guest_contacts DROP INDEX uq_guest_contact_sponsor,
      ADD UNIQUE KEY uq_guest_contact_unit_sponsor(unit_id,sponsor_email,email);
    ALTER TABLE attendee_contacts DROP INDEX uq_attendee_contact_email,
      ADD UNIQUE KEY uq_attendee_contact_unit_email(unit_id,email);

    ALTER TABLE settings ADD COLUMN unit_id BIGINT UNSIGNED NULL FIRST;
    ALTER TABLE email_templates ADD COLUMN unit_id BIGINT UNSIGNED NULL FIRST;
    ALTER TABLE order_rank_settings ADD COLUMN unit_id BIGINT UNSIGNED NULL FIRST;
    ALTER TABLE custom_orders ADD COLUMN unit_id BIGINT UNSIGNED NULL FIRST;
    ALTER TABLE system_jobs ADD COLUMN unit_id BIGINT UNSIGNED NULL FIRST;
    UPDATE settings SET unit_id=v_unit_id WHERE unit_id IS NULL;
    UPDATE email_templates SET unit_id=v_unit_id WHERE unit_id IS NULL;
    UPDATE order_rank_settings SET unit_id=v_unit_id WHERE unit_id IS NULL;
    UPDATE custom_orders SET unit_id=v_unit_id WHERE unit_id IS NULL;
    UPDATE system_jobs SET unit_id=v_unit_id WHERE unit_id IS NULL;

    ALTER TABLE settings MODIFY unit_id BIGINT UNSIGNED NOT NULL,
      DROP PRIMARY KEY, ADD PRIMARY KEY(unit_id,setting_key),
      ADD CONSTRAINT fk_settings_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT;
    ALTER TABLE email_templates MODIFY unit_id BIGINT UNSIGNED NOT NULL,
      DROP PRIMARY KEY, ADD PRIMARY KEY(unit_id,template_key),
      ADD CONSTRAINT fk_email_templates_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT;
    ALTER TABLE order_rank_settings MODIFY unit_id BIGINT UNSIGNED NOT NULL,
      DROP PRIMARY KEY, ADD PRIMARY KEY(unit_id,order_key),
      ADD CONSTRAINT fk_order_rank_settings_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT;
    ALTER TABLE custom_orders MODIFY unit_id BIGINT UNSIGNED NOT NULL,
      DROP PRIMARY KEY, ADD PRIMARY KEY(unit_id,order_key),
      ADD CONSTRAINT fk_custom_orders_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT;
    ALTER TABLE system_jobs MODIFY unit_id BIGINT UNSIGNED NOT NULL,
      DROP PRIMARY KEY, ADD PRIMARY KEY(unit_id,job_key),
      ADD CONSTRAINT fk_system_jobs_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT;

    CREATE TABLE platform_email_templates(
      template_key VARCHAR(80) PRIMARY KEY,
      display_name VARCHAR(150) NOT NULL,
      subject_template VARCHAR(250) NOT NULL,
      body_template MEDIUMTEXT NOT NULL,
      enabled TINYINT(1) NOT NULL DEFAULT 1,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    INSERT IGNORE INTO platform_email_templates(template_key,display_name,subject_template,body_template,enabled)
    SELECT template_key,display_name,subject_template,body_template,enabled
    FROM email_templates WHERE unit_id=v_unit_id;

    CREATE TABLE province_email_templates(
      province_id BIGINT UNSIGNED NOT NULL,
      template_key VARCHAR(80) NOT NULL,
      display_name VARCHAR(150) NOT NULL,
      subject_template VARCHAR(250) NOT NULL,
      body_template MEDIUMTEXT NOT NULL,
      enabled TINYINT(1) NOT NULL DEFAULT 1,
      updated_by BIGINT UNSIGNED NULL,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY(province_id,template_key),
      CONSTRAINT fk_province_email_template_province FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
      CONSTRAINT fk_province_email_template_user FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE platform_order_rank_settings LIKE order_rank_settings;
    ALTER TABLE platform_order_rank_settings
      DROP PRIMARY KEY,
      DROP COLUMN unit_id,
      ADD PRIMARY KEY(order_key);
    INSERT IGNORE INTO platform_order_rank_settings
      (order_key,salutations,salutation_rank_links,masonic_ranks,active_provincial_ranks,past_provincial_ranks,active_grand_ranks,past_grand_ranks,updated_at)
    SELECT order_key,salutations,salutation_rank_links,masonic_ranks,active_provincial_ranks,past_provincial_ranks,active_grand_ranks,past_grand_ranks,updated_at
    FROM order_rank_settings WHERE unit_id=v_unit_id;

    CREATE TABLE province_order_rank_settings(
      province_id BIGINT UNSIGNED NOT NULL,
      order_key VARCHAR(40) NOT NULL,
      salutations TEXT NULL,
      salutation_rank_links LONGTEXT NULL,
      masonic_ranks TEXT NULL,
      active_provincial_ranks TEXT NULL,
      past_provincial_ranks TEXT NULL,
      active_grand_ranks TEXT NULL,
      past_grand_ranks TEXT NULL,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY(province_id,order_key),
      CONSTRAINT fk_province_rank_province FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE user_memberships(
      id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      user_id BIGINT UNSIGNED NOT NULL,
      scope_type ENUM('platform','province','unit') NOT NULL,
      province_id BIGINT UNSIGNED NULL,
      unit_id BIGINT UNSIGNED NULL,
      role VARCHAR(50) NOT NULL,
      active TINYINT(1) NOT NULL DEFAULT 1,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      scope_key VARCHAR(80) GENERATED ALWAYS AS
        (CASE scope_type WHEN 'platform' THEN 'platform' WHEN 'province' THEN CONCAT('province:',province_id) ELSE CONCAT('unit:',unit_id) END) STORED,
      UNIQUE KEY uq_user_membership(user_id,scope_type,scope_key),
      KEY idx_membership_province(province_id,user_id,active),
      KEY idx_membership_unit(unit_id,user_id,active),
      CONSTRAINT fk_membership_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_membership_province FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
      CONSTRAINT fk_membership_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
      CONSTRAINT chk_membership_scope CHECK (
        (scope_type='platform' AND province_id IS NULL AND unit_id IS NULL) OR
        (scope_type='province' AND province_id IS NOT NULL AND unit_id IS NULL) OR
        (scope_type='unit' AND province_id IS NULL AND unit_id IS NOT NULL)
      )
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    INSERT IGNORE INTO user_memberships(user_id,scope_type,unit_id,role,active)
    SELECT id,'unit',v_unit_id,role,active FROM users;

    CREATE TABLE source_installations(
      id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      source_key VARCHAR(100) NOT NULL,
      display_name VARCHAR(200) NOT NULL,
      target_province_id BIGINT UNSIGNED NOT NULL,
      target_unit_id BIGINT UNSIGNED NOT NULL,
      status ENUM('registered','importing','complete','failed') NOT NULL DEFAULT 'registered',
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      UNIQUE KEY uq_source_installation_key(source_key),
      CONSTRAINT fk_source_province FOREIGN KEY(target_province_id) REFERENCES provinces(id) ON DELETE RESTRICT,
      CONSTRAINT fk_source_unit FOREIGN KEY(target_unit_id) REFERENCES units(id) ON DELETE RESTRICT
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    CREATE TABLE import_runs(
      id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      source_installation_id BIGINT UNSIGNED NOT NULL,
      dry_run TINYINT(1) NOT NULL DEFAULT 1,
      status ENUM('running','complete','failed') NOT NULL DEFAULT 'running',
      started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      completed_at DATETIME NULL,
      report_json LONGTEXT NULL,
      error_message TEXT NULL,
      CONSTRAINT fk_import_run_source FOREIGN KEY(source_installation_id) REFERENCES source_installations(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    CREATE TABLE import_id_map(
      source_installation_id BIGINT UNSIGNED NOT NULL,
      source_table VARCHAR(100) NOT NULL,
      source_id VARCHAR(191) NOT NULL,
      destination_table VARCHAR(100) NOT NULL,
      destination_id VARCHAR(191) NOT NULL,
      natural_key_hash CHAR(64) NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY(source_installation_id,source_table,source_id),
      KEY idx_import_destination(destination_table,destination_id),
      CONSTRAINT fk_import_map_source FOREIGN KEY(source_installation_id) REFERENCES source_installations(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    CREATE TABLE import_conflicts(
      id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      import_run_id BIGINT UNSIGNED NOT NULL,
      source_table VARCHAR(100) NOT NULL,
      source_id VARCHAR(191) NOT NULL,
      conflict_type VARCHAR(100) NOT NULL,
      details_json LONGTEXT NULL,
      resolution VARCHAR(100) NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      CONSTRAINT fk_import_conflict_run FOREIGN KEY(import_run_id) REFERENCES import_runs(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    INSERT INTO migration_history(version,name,checksum,execution_ms)
    VALUES(
      '202609240001',
      'Multi-Province and multi-unit security foundation',
      '9c2133ea25711808e2e899bc37222a316bcca7c5e19664f3b1f8202d4c37431a',
      0
    );
    SELECT '202609240001 completed. Import 202609240002_migration.sql next.' AS migration_result;
END//
CALL migrate_202609240001()//
DROP PROCEDURE migrate_202609240001//
DELIMITER ;

-- ===== Tenant catalogue and terminology =====
-- DBA-readable SQL equivalent of canonical migration 202609240002.
-- Preferred execution: php bin/migrate.php
-- Requirements: migration 202609240001 already applied; MySQL 8+/MariaDB 10.5+.
-- This script aborts before DDL on invalid foundation data. If the complete
-- target schema already exists it exits without changing data.

DELIMITER //
DROP PROCEDURE IF EXISTS migrate_202609240002//
CREATE PROCEDURE migrate_202609240002()
main: BEGIN
    DECLARE v_count BIGINT DEFAULT 0;
    DECLARE v_unit_id BIGINT UNSIGNED;

    SELECT COUNT(*) INTO v_count
    FROM information_schema.tables
    WHERE table_schema=DATABASE()
      AND table_name IN ('migration_history','provinces','units','users','user_memberships','lodge_settings','meetings');
    IF v_count<>7 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='202609240002 precondition failed: foundation tables are missing; apply 202609240001 first.';
    END IF;

    SELECT COUNT(*) INTO v_count FROM migration_history WHERE version='202609240001';
    IF v_count<>1 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='202609240002 precondition failed: canonical migration 202609240001 is not recorded as applied.';
    END IF;

    SELECT COUNT(*) INTO v_count FROM units u LEFT JOIN provinces p ON p.id=u.province_id WHERE p.id IS NULL;
    IF v_count>0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='202609240002 precondition failed: one or more units have no valid Province.';
    END IF;

    SELECT COUNT(*) INTO v_count FROM units
    WHERE unit_type NOT REGEXP '^[a-z][a-z0-9_]{0,39}$'
       OR slug NOT REGEXP '^[a-z0-9][a-z0-9-]{0,99}$'
       OR slug IN ('login','logout','province','admin','api','assets','cron','health');
    IF v_count>0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='202609240002 precondition failed: invalid/reserved unit_type or slug.';
    END IF;

    SELECT COUNT(*) INTO v_count FROM user_memberships
    WHERE role=''
       OR (scope_type='platform' AND (province_id IS NOT NULL OR unit_id IS NOT NULL))
       OR (scope_type='province' AND (province_id IS NULL OR unit_id IS NOT NULL))
       OR (scope_type='unit' AND (province_id IS NOT NULL OR unit_id IS NULL));
    IF v_count>0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='202609240002 precondition failed: invalid membership scope or role.';
    END IF;

    SELECT COUNT(*) INTO v_count
    FROM information_schema.tables
    WHERE table_schema=DATABASE()
      AND table_name IN ('unit_types','unit_domains_or_slugs','tenant_roles','tenant_role_scopes');
    IF v_count=4 AND EXISTS(
        SELECT 1 FROM information_schema.columns
        WHERE table_schema=DATABASE() AND table_name='units' AND column_name='unit_type_id'
    ) THEN
        SELECT COUNT(*) INTO v_count
        FROM information_schema.table_constraints
        WHERE constraint_schema=DATABASE()
          AND constraint_name IN ('fk_units_unit_type','fk_membership_role_scope','fk_unit_identifier_unit','fk_tenant_role_scope_role');
        IF v_count=4
           AND (SELECT COUNT(*) FROM units WHERE unit_type_id IS NULL)=0
           AND (SELECT COUNT(*) FROM units u LEFT JOIN unit_domains_or_slugs r ON r.unit_id=u.id AND r.identifier_type='slug' AND r.is_primary=1 AND r.active=1 WHERE r.id IS NULL)=0 THEN
            INSERT IGNORE INTO migration_history(version,name,checksum,execution_ms)
            VALUES(
              '202609240002',
              'Tenant catalogue, unit identifiers and membership role scopes',
              '9106c46bcb26766f662c3c920426306898939c03739cb8eca0ca662353763a43',
              0
            );
            SELECT '202609240002 target schema already exists; no changes made.' AS migration_result;
            LEAVE main;
        END IF;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='202609240002 found a partial target schema; use the canonical PHP migration to inspect/resume safely.';
    ELSEIF v_count<>0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='202609240002 found a partial target schema; use the canonical PHP migration to inspect/resume safely.';
    END IF;

    SELECT COUNT(*) INTO v_count
    FROM users u LEFT JOIN user_memberships um ON um.user_id=u.id
    WHERE um.id IS NULL;
    IF v_count>0 AND (SELECT COUNT(*) FROM units)<>1 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='202609240002 cannot infer memberships because more than one unit exists.';
    END IF;

    IF v_count>0 THEN
        SELECT id INTO v_unit_id FROM units ORDER BY id LIMIT 1;
        INSERT INTO user_memberships(user_id,scope_type,unit_id,role,active)
        SELECT u.id,'unit',v_unit_id,u.role,u.active
        FROM users u
        LEFT JOIN user_memberships um ON um.user_id=u.id
        WHERE um.id IS NULL;
    END IF;

    CREATE TABLE unit_types(
        id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        type_key VARCHAR(40) NOT NULL,
        display_name VARCHAR(100) NOT NULL,
        body_noun VARCHAR(50) NOT NULL,
        member_noun VARCHAR(50) NOT NULL,
        built_in TINYINT(1) NOT NULL DEFAULT 0,
        active TINYINT(1) NOT NULL DEFAULT 1,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_unit_type_key(type_key),
        KEY idx_unit_type_active(active,display_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE tenant_roles(
        role_key VARCHAR(50) PRIMARY KEY,
        display_name VARCHAR(100) NOT NULL,
        built_in TINYINT(1) NOT NULL DEFAULT 0,
        active TINYINT(1) NOT NULL DEFAULT 1,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE tenant_role_scopes(
        role_key VARCHAR(50) NOT NULL,
        scope_type ENUM('platform','province','unit') NOT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY(role_key,scope_type),
        CONSTRAINT fk_tenant_role_scope_role FOREIGN KEY(role_key) REFERENCES tenant_roles(role_key) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE unit_domains_or_slugs(
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        unit_id BIGINT UNSIGNED NOT NULL,
        identifier_type ENUM('slug','domain') NOT NULL,
        identifier_value VARCHAR(253) NOT NULL,
        normalized_value VARCHAR(253) GENERATED ALWAYS AS (LOWER(TRIM(identifier_value))) STORED,
        is_primary TINYINT(1) NOT NULL DEFAULT 0,
        redirect_to_primary TINYINT(1) NOT NULL DEFAULT 1,
        verified_at DATETIME NULL,
        active TINYINT(1) NOT NULL DEFAULT 1,
        primary_slot VARCHAR(80) GENERATED ALWAYS AS (CASE WHEN is_primary=1 AND active=1 THEN CONCAT(identifier_type,':',unit_id) ELSE NULL END) STORED,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_unit_identifier(identifier_type,normalized_value),
        UNIQUE KEY uq_unit_primary_identifier(primary_slot),
        KEY idx_unit_identifier_unit(unit_id,identifier_type,active),
        CONSTRAINT fk_unit_identifier_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    INSERT INTO unit_types(type_key,display_name,body_noun,member_noun,built_in,active) VALUES
      ('lodge','Lodge','Lodge','Brother',1,1),
      ('chapter','Chapter','Chapter','Companion',1,1),
      ('council','Council','Council','Companion',1,1),
      ('other','Other Masonic Body','Unit','Member',1,1);

    INSERT IGNORE INTO unit_types(type_key,display_name,body_noun,member_noun,built_in,active)
    SELECT DISTINCT unit_type,
           CONCAT(UCASE(LEFT(REPLACE(unit_type,'_',' '),1)),SUBSTRING(REPLACE(unit_type,'_',' '),2)),
           CONCAT(UCASE(LEFT(REPLACE(unit_type,'_',' '),1)),SUBSTRING(REPLACE(unit_type,'_',' '),2)),
           'Member',0,1
    FROM units;

    ALTER TABLE units ADD COLUMN unit_type_id SMALLINT UNSIGNED NULL AFTER province_id;
    UPDATE units u INNER JOIN unit_types t ON t.type_key=u.unit_type SET u.unit_type_id=t.id;
    SELECT COUNT(*) INTO v_count FROM units WHERE unit_type_id IS NULL;
    IF v_count>0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='202609240002 failed to map all units to unit_types.';
    END IF;
    ALTER TABLE units
      MODIFY unit_type_id SMALLINT UNSIGNED NOT NULL,
      ADD KEY idx_units_type_active(unit_type_id,active),
      ADD CONSTRAINT fk_units_unit_type FOREIGN KEY(unit_type_id) REFERENCES unit_types(id) ON DELETE RESTRICT;

    INSERT INTO unit_domains_or_slugs(unit_id,identifier_type,identifier_value,is_primary,redirect_to_primary,active)
    SELECT id,'slug',slug,1,0,1 FROM units;

    INSERT INTO tenant_roles(role_key,display_name,built_in,active) VALUES
      ('platform_admin','Platform Administrator',1,1),
      ('province_admin','Province Administrator',1,1),
      ('admin','Unit Administrator',1,1),
      ('secretary','Secretary',1,1),
      ('dining','Dining Steward',1,1),
      ('treasurer','Treasurer',1,1),
      ('worshipful_master','Worshipful Master',1,1),
      ('readonly','Read-only Officer',1,1),
      ('member','Member',1,1);

    INSERT INTO tenant_role_scopes(role_key,scope_type) VALUES
      ('platform_admin','platform'),('province_admin','province'),
      ('admin','unit'),('secretary','unit'),('dining','unit'),
      ('treasurer','unit'),('worshipful_master','unit'),
      ('readonly','unit'),('member','unit');

    INSERT IGNORE INTO tenant_roles(role_key,display_name,built_in,active)
    SELECT DISTINCT role,
           CONCAT(UCASE(LEFT(REPLACE(role,'_',' '),1)),SUBSTRING(REPLACE(role,'_',' '),2)),
           0,1
    FROM user_memberships;
    INSERT IGNORE INTO tenant_role_scopes(role_key,scope_type)
    SELECT DISTINCT role,scope_type FROM user_memberships;

    ALTER TABLE user_memberships
      ADD KEY idx_membership_role_scope(`role`,scope_type),
      ADD CONSTRAINT fk_membership_role_scope FOREIGN KEY(`role`,scope_type) REFERENCES tenant_role_scopes(role_key,scope_type) ON DELETE RESTRICT;

    INSERT INTO migration_history(version,name,checksum,execution_ms)
    VALUES(
      '202609240002',
      'Tenant catalogue, unit identifiers and membership role scopes',
      '9106c46bcb26766f662c3c920426306898939c03739cb8eca0ca662353763a43',
      0
    );

    SELECT '202609240002 migration completed. Run the verification SQL before opening the application.' AS migration_result;
END//
CALL migrate_202609240002()//
DROP PROCEDURE migrate_202609240002//
DELIMITER ;

-- ===== 202609240003_auth_rate_limits.sql =====
-- Apply once, after 202609240001 and 202609240002. No existing data is changed.
CREATE TABLE IF NOT EXISTS auth_rate_limits (
    bucket CHAR(64) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
    attempts INT UNSIGNED NOT NULL DEFAULT 0,
    window_started DATETIME NOT NULL,
    INDEX idx_auth_window_started(window_started)
) ENGINE=InnoDB;

-- ===== 202609240004_scoped_roles.sql =====
-- Apply after migration 003. Adds roles without changing existing assignments.
INSERT INTO tenant_roles(role_key,display_name,built_in,active) VALUES
 ('document_viewer','Document Viewer',1,1),
 ('checkin_operator','Check-in Operator',1,1)
ON DUPLICATE KEY UPDATE display_name=VALUES(display_name),active=1;
INSERT IGNORE INTO tenant_role_scopes(role_key,scope_type) VALUES
 ('document_viewer','unit'),('checkin_operator','unit');

-- ===== 202609240005_provincial_admin.sql =====
-- Apply after migrations 001-004. Additive; no existing rows are changed.
CREATE TABLE province_settings (
 province_id BIGINT UNSIGNED PRIMARY KEY,
 logo_url VARCHAR(500) NULL,
 primary_colour CHAR(7) NOT NULL DEFAULT '#990033',
 secondary_colour CHAR(7) NOT NULL DEFAULT '#122849',
 accent_colour CHAR(7) NOT NULL DEFAULT '#c7a646',
 contact_name VARCHAR(150) NULL,
 contact_email VARCHAR(254) NULL,
 contact_phone VARCHAR(50) NULL,
 website_url VARCHAR(500) NULL,
 postal_address VARCHAR(500) NULL,
 updated_by BIGINT UNSIGNED NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE province_audit_log (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 province_id BIGINT UNSIGNED NOT NULL,
 actor_id BIGINT UNSIGNED NULL,
 action VARCHAR(80) NOT NULL,
 unit_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_province_audit(province_id,created_at,id),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(actor_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE province_unit_invites (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 province_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NOT NULL,
 email VARCHAR(254) NOT NULL,
 token_hash CHAR(64) NOT NULL,
 invited_by BIGINT UNSIGNED NULL,
 expires_at DATETIME NOT NULL,
 accepted_at DATETIME NULL,
 revoked_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_province_invite_token(token_hash),
 KEY idx_province_invites(province_id,unit_id,created_at),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(invited_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== 202609240006_unit_archive.sql =====
-- Additive: preserves bookings and audit history when a Province removes a unit.
-- Apply after 202609240005_provincial_admin.sql.
ALTER TABLE units
 ADD COLUMN deleted_at DATETIME NULL,
 ADD COLUMN deleted_by BIGINT UNSIGNED NULL,
 ADD KEY idx_units_province_visible(province_id,deleted_at,active),
 ADD CONSTRAINT fk_units_deleted_by FOREIGN KEY (deleted_by) REFERENCES users(id) ON DELETE SET NULL;

-- ===== 202609240007_province_admin_invites.sql =====
-- Apply after 202609240005. Additive; no existing accounts or grants change.
CREATE TABLE province_admin_invites (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 province_id BIGINT UNSIGNED NOT NULL,
 email VARCHAR(254) NOT NULL,
 token_hash CHAR(64) NOT NULL,
 invited_by BIGINT UNSIGNED NULL,
 expires_at DATETIME NOT NULL,
 accepted_at DATETIME NULL,
 revoked_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_province_admin_invite_token(token_hash),
 KEY idx_province_admin_invites(province_id,created_at),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(invited_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== 202609240008_directional_unit_links.sql =====
-- Apply after 202609240007. Additive; existing units and events are untouched.
CREATE TABLE unit_display_links (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 source_unit_id BIGINT UNSIGNED NOT NULL,
 target_unit_id BIGINT UNSIGNED NOT NULL,
 priority INT UNSIGNED NOT NULL DEFAULT 100,
 active_from DATE NOT NULL,
 active_until DATE NULL,
 requested_by BIGINT UNSIGNED NULL,
 requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 accepted_by BIGINT UNSIGNED NULL,
 accepted_at DATETIME NULL,
 revoked_by BIGINT UNSIGNED NULL,
 revoked_at DATETIME NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_unit_display_direction (source_unit_id,target_unit_id),
 KEY idx_unit_display_source (source_unit_id,accepted_at,revoked_at,priority),
 KEY idx_unit_display_target (target_unit_id,accepted_at,revoked_at),
 CONSTRAINT fk_unit_display_source FOREIGN KEY (source_unit_id) REFERENCES units(id) ON DELETE CASCADE,
 CONSTRAINT fk_unit_display_target FOREIGN KEY (target_unit_id) REFERENCES units(id) ON DELETE CASCADE,
 CONSTRAINT fk_unit_display_requester FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT fk_unit_display_accepter FOREIGN KEY (accepted_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT fk_unit_display_revoker FOREIGN KEY (revoked_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT chk_unit_display_distinct CHECK (source_unit_id<>target_unit_id),
 CONSTRAINT chk_unit_display_dates CHECK (active_until IS NULL OR active_until>=active_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== 202609240009_tenant_events.sql =====
-- Apply after 202609240008 and the foundation migration. Retains meeting IDs and bookings.
ALTER TABLE meetings
 ADD COLUMN event_type VARCHAR(30) NOT NULL DEFAULT 'regular' AFTER event_slug,
 ADD COLUMN document_kind VARCHAR(12) NOT NULL DEFAULT 'summons' AFTER summons_url;
UPDATE meetings SET document_kind='invitation'
 WHERE document_kind='summons' AND special_event_type IN ('ladies_evening','other');
UPDATE meetings SET event_type='special' WHERE event_type='regular' AND special_event_enabled=1 AND special_event_type<>'none';
-- Reconcile legacy entries that predated the event slug before adding uniqueness.
UPDATE meetings SET event_slug=CONCAT('event-',id) WHERE event_slug IS NULL OR event_slug='';
-- MySQL/MariaDB compatible conditional index creation (the foundation may already have it).
SET @event_slug_index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='meetings' AND index_name IN ('uq_meeting_unit_slug','uq_meetings_unit_event_slug'));
SET @event_slug_sql = IF(@event_slug_index_exists=0,
 'ALTER TABLE meetings ADD UNIQUE KEY uq_meetings_unit_event_slug (unit_id,event_slug)',
 'SELECT 1');
PREPARE event_slug_index_statement FROM @event_slug_sql;
EXECUTE event_slug_index_statement;
DEALLOCATE PREPARE event_slug_index_statement;

-- ===== 202609240010_event_programmes.sql =====
-- Apply after 202609240009. One programme and capacity for every existing event/group.
CREATE TABLE event_programmes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 programme_key VARCHAR(140) NOT NULL,
 title VARCHAR(200) NOT NULL,
 capacity INT 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_programme_unit_key(unit_id,programme_key),
 UNIQUE KEY uq_programme_id_unit(id,unit_id),
 CONSTRAINT fk_programme_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE meetings ADD COLUMN programme_id BIGINT UNSIGNED NULL AFTER unit_id;
INSERT INTO event_programmes(unit_id,programme_key,title,capacity)
SELECT m.unit_id,
 CASE WHEN m.linked_event_group IS NOT NULL AND m.linked_event_group<>''
      THEN CONCAT('group:',m.linked_event_group) ELSE CONCAT('event:',m.id) END,
 MIN(m.title),
 COALESCE(MIN(CASE WHEN m.archived=0 THEN m.dining_capacity END),MIN(m.dining_capacity))
FROM meetings m
GROUP BY m.unit_id, CASE WHEN m.linked_event_group IS NOT NULL AND m.linked_event_group<>''
      THEN CONCAT('group:',m.linked_event_group) ELSE CONCAT('event:',m.id) END;
UPDATE meetings m JOIN event_programmes p ON p.unit_id=m.unit_id AND p.programme_key=
 CASE WHEN m.linked_event_group IS NOT NULL AND m.linked_event_group<>''
      THEN CONCAT('group:',m.linked_event_group) ELSE CONCAT('event:',m.id) END
SET m.programme_id=p.id,m.dining_capacity=p.capacity;
ALTER TABLE meetings
 ADD KEY idx_meeting_programme(programme_id,unit_id),
 ADD CONSTRAINT fk_meeting_programme_unit FOREIGN KEY(programme_id,unit_id) REFERENCES event_programmes(id,unit_id);

-- ===== 202609240011_public_slug_redirects.sql =====
-- Apply after 202609240010. Retain historical slugs when changing names later.
-- Existing unit_domains_or_slugs holds retained unit slugs.
CREATE TABLE public_event_slug_redirects (
 unit_id BIGINT UNSIGNED NOT NULL,
 old_slug VARCHAR(100) NOT NULL,
 event_id BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(unit_id,old_slug),
 KEY idx_event_slug_redirect_event(event_id),
 CONSTRAINT fk_public_event_slug_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 CONSTRAINT fk_public_event_slug_event FOREIGN KEY(event_id) REFERENCES meetings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== 202609240012_guest_directory_consent.sql =====
-- 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;

-- ===== 202609240013_province_secretary.sql =====
-- Apply once after migration 012. Existing memberships and permissions are unchanged.
INSERT INTO tenant_roles(role_key,display_name,built_in,active) VALUES
 ('province_secretary','Provincial Secretary',1,1)
ON DUPLICATE KEY UPDATE display_name=VALUES(display_name),active=1;
INSERT IGNORE INTO tenant_role_scopes(role_key,scope_type) VALUES ('province_secretary','province');
ALTER TABLE province_admin_invites ADD COLUMN role_key VARCHAR(40) NOT NULL DEFAULT 'province_admin' AFTER province_id;

-- ===== 202609240014_arrival_province_events.sql =====
-- Apply once after migration 013. Adds a kiosk credential and Province event ownership.
CREATE TABLE kiosk_sessions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 token_hash CHAR(64) NOT NULL UNIQUE,
 user_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NOT NULL,
 expires_at DATETIME NOT NULL,
 revoked_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_kiosk_expiry(expires_at),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE province_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 province_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NOT NULL,
 meeting_id BIGINT UNSIGNED NOT NULL,
 event_kind ENUM('grand_lodge','convocation') NOT NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_province_event_meeting(meeting_id),
 KEY idx_province_events(province_id,unit_id),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(meeting_id) REFERENCES meetings(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE province_event_hosts (
 province_id BIGINT UNSIGNED PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL UNIQUE,
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Remove ALL sample and operational records, keep reference defaults =====
-- The preceding historical foundation requires its sample unit to exist during
-- migration. Platform-wide defaults have now been copied to their own tables.
-- Empty every operational table, including the sample unit and all users.
-- This step runs only in the new empty database used for this import.
DELIMITER //
DROP PROCEDURE IF EXISTS clean_install_remove_sample//
CREATE PROCEDURE clean_install_remove_sample()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE table_to_clear VARCHAR(64);
  DECLARE tables_to_clear CURSOR FOR
    SELECT table_name FROM information_schema.tables
    WHERE table_schema=DATABASE() AND table_type='BASE TABLE'
      AND table_name NOT IN (
        'platform_order_rank_settings', 'platform_email_templates',
        'unit_types', 'tenant_roles', 'tenant_role_scopes',
        'migration_history', 'historical_migration_reconciliation',
        'schema_versions'
      ) ORDER BY table_name;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
  IF DATABASE() IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Select the new empty database before import.';
  END IF;
  SET FOREIGN_KEY_CHECKS=0;
  OPEN tables_to_clear;
  clear_loop: LOOP
    FETCH tables_to_clear INTO table_to_clear;
    IF done=1 THEN LEAVE clear_loop; END IF;
    SET @clean_table_sql=CONCAT('TRUNCATE TABLE `',REPLACE(table_to_clear,'`','``'),'`');
    PREPARE clean_table_statement FROM @clean_table_sql;
    EXECUTE clean_table_statement;
    DEALLOCATE PREPARE clean_table_statement;
  END LOOP;
  CLOSE tables_to_clear;
  SET FOREIGN_KEY_CHECKS=1;
END//
CALL clean_install_remove_sample()//
DROP PROCEDURE clean_install_remove_sample//
DELIMITER ;

-- Final assertion: import must fail if any operational owner remains.
DELIMITER //
DROP PROCEDURE IF EXISTS verify_clean_install//
CREATE PROCEDURE verify_clean_install()
BEGIN
  IF (SELECT COUNT(*) FROM users) <> 0 OR
     (SELECT COUNT(*) FROM provinces) <> 0 OR
     (SELECT COUNT(*) FROM units) <> 0 OR
     (SELECT COUNT(*) FROM meetings) <> 0 OR
     (SELECT COUNT(*) FROM platform_order_rank_settings) = 0 OR
     (SELECT COUNT(*) FROM unit_types) = 0 OR
     (SELECT COUNT(*) FROM tenant_roles) = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Clean-install verification failed; do not use this database.';
  END IF;
END//
CALL verify_clean_install()//
DROP PROCEDURE verify_clean_install//
DELIMITER ;
SELECT (SELECT COUNT(*) FROM users) AS users,
       (SELECT COUNT(*) FROM provinces) AS provinces,
       (SELECT COUNT(*) FROM units) AS units,
       (SELECT COUNT(*) FROM platform_order_rank_settings) AS built_in_orders,
       (SELECT COUNT(*) FROM unit_types) AS body_terms;