-- Adds Masonic Order profiles and optional linked-meeting grouping.
-- Run once against an existing installation.

ALTER TABLE meetings
  ADD COLUMN masonic_order VARCHAR(40) NOT NULL DEFAULT 'mark' AFTER lodge_number,
  ADD COLUMN linked_event_group VARCHAR(100) NULL AFTER masonic_order,
  ADD INDEX idx_meeting_order (masonic_order),
  ADD INDEX idx_meeting_linked_group (linked_event_group);

-- Existing Lea meetings are Mark meetings unless changed in Administration.
UPDATE meetings SET masonic_order='mark' WHERE masonic_order IS NULL OR masonic_order='';

CREATE TABLE order_rank_settings (
  order_key VARCHAR(40) PRIMARY KEY,
  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;