START TRANSACTION;

ALTER TABLE units
  ADD COLUMN consecration_date DATE NULL AFTER number;

ALTER TABLE people
  ADD COLUMN address_line_1 VARCHAR(150) NULL AFTER phone,
  ADD COLUMN address_line_2 VARCHAR(150) NULL AFTER address_line_1,
  ADD COLUMN address_line_3 VARCHAR(150) NULL AFTER address_line_2,
  ADD COLUMN town_city VARCHAR(100) NULL AFTER address_line_3,
  ADD COLUMN county VARCHAR(100) NULL AFTER town_city,
  ADD COLUMN postcode VARCHAR(30) NULL AFTER county,
  ADD COLUMN country_code CHAR(2) NULL AFTER postcode;

CREATE TABLE unit_lodge_details (
  unit_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  order_key VARCHAR(60) NOT NULL DEFAULT 'craft',
  grand_lodge VARCHAR(200) NOT NULL DEFAULT '',
  provincial_secretary_address TEXT NULL,
  provincial_secretary_email VARCHAR(254) NULL,
  grand_inspector_name VARCHAR(200) NULL,
  grand_inspector_email VARCHAR(254) NULL,
  senior_visiting_officer_name VARCHAR(200) NULL,
  senior_visiting_officer_email VARCHAR(254) NULL,
  visiting_officer_name VARCHAR(200) NULL,
  visiting_officer_email VARCHAR(254) NULL,
  meeting_address_line_1 VARCHAR(150) NULL,
  meeting_address_line_2 VARCHAR(150) NULL,
  meeting_address_line_3 VARCHAR(150) NULL,
  meeting_town_city VARCHAR(100) NULL,
  meeting_county VARCHAR(100) NULL,
  meeting_postcode VARCHAR(30) NULL,
  meeting_country_code CHAR(2) NULL,
  updated_by BIGINT UNSIGNED NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_unit_lodge_details_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
  CONSTRAINT fk_unit_lodge_details_actor FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE unit_lodge_dues (
  unit_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  currency_code CHAR(3) NOT NULL DEFAULT 'GBP',
  standard_joining_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  overseas_joining_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  initiation_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  grand_provincial_fee_applicable TINYINT(1) NOT NULL DEFAULT 0,
  grand_provincial_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  annual_dues_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  annual_due_day TINYINT UNSIGNED NOT NULL DEFAULT 1,
  annual_due_month TINYINT UNSIGNED NOT NULL DEFAULT 1,
  secretary_exempt TINYINT(1) NOT NULL DEFAULT 0,
  updated_by BIGINT UNSIGNED NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_unit_lodge_dues_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
  CONSTRAINT fk_unit_lodge_dues_actor FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT chk_unit_lodge_dues_day CHECK(annual_due_day BETWEEN 1 AND 31),
  CONSTRAINT chk_unit_lodge_dues_month CHECK(annual_due_month BETWEEN 1 AND 12),
  CONSTRAINT chk_unit_lodge_dues_money CHECK(standard_joining_fee>=0 AND overseas_joining_fee>=0 AND initiation_fee>=0 AND grand_provincial_fee>=0 AND annual_dues_amount>=0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE unit_meeting_patterns (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  unit_id BIGINT UNSIGNED NOT NULL,
  occurrence_no TINYINT UNSIGNED NOT NULL,
  weekday_no TINYINT UNSIGNED NOT NULL,
  month_no TINYINT UNSIGNED NULL,
  meeting_type ENUM('installation','regular','emergency') NOT NULL,
  active_installation TINYINT GENERATED ALWAYS AS (CASE WHEN meeting_type='installation' AND deleted_at IS NULL THEN 1 ELSE NULL END) STORED,
  created_by BIGINT UNSIGNED NOT NULL,
  updated_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  UNIQUE KEY uq_unit_one_installation(unit_id,active_installation),
  KEY idx_unit_meeting_patterns(unit_id,deleted_at,meeting_type),
  CONSTRAINT fk_unit_meeting_patterns_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
  CONSTRAINT fk_unit_meeting_patterns_creator FOREIGN KEY(created_by) REFERENCES users(id),
  CONSTRAINT fk_unit_meeting_patterns_updater FOREIGN KEY(updated_by) REFERENCES users(id),
  CONSTRAINT chk_unit_meeting_pattern_occurrence CHECK(occurrence_no BETWEEN 1 AND 5),
  CONSTRAINT chk_unit_meeting_pattern_weekday CHECK(weekday_no BETWEEN 1 AND 7),
  CONSTRAINT chk_unit_meeting_pattern_month CHECK(month_no IS NULL OR month_no BETWEEN 1 AND 12),
  CONSTRAINT chk_installation_has_month CHECK(meeting_type<>'installation' OR month_no IS NOT NULL)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE unit_meeting_pattern_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  pattern_id BIGINT UNSIGNED NULL,
  unit_id BIGINT UNSIGNED NOT NULL,
  actor_id BIGINT UNSIGNED NOT NULL,
  action_key ENUM('created','updated','removed') NOT NULL,
  snapshot_json TEXT NOT NULL,
  occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_unit_meeting_pattern_history(unit_id,occurred_at),
  CONSTRAINT fk_unit_meeting_pattern_history_pattern FOREIGN KEY(pattern_id) REFERENCES unit_meeting_patterns(id) ON DELETE SET NULL,
  CONSTRAINT fk_unit_meeting_pattern_history_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
  CONSTRAINT fk_unit_meeting_pattern_history_actor FOREIGN KEY(actor_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO unit_lodge_details(unit_id,order_key,grand_lodge,updated_by)
SELECT u.id,COALESCE(NULLIF(u.order_key,''),'craft'),COALESCE(ls.order_name,''),ls.updated_by
FROM units u LEFT JOIN lodge_settings ls ON ls.unit_id=u.id
ON DUPLICATE KEY UPDATE unit_id=VALUES(unit_id);

INSERT INTO unit_lodge_dues(unit_id,currency_code,updated_by)
SELECT u.id,COALESCE(NULLIF(ls.currency_code,''),'GBP'),ls.updated_by
FROM units u LEFT JOIN lodge_settings ls ON ls.unit_id=u.id
ON DUPLICATE KEY UPDATE unit_id=VALUES(unit_id);

INSERT INTO migration_history(version,name,checksum,execution_ms)
VALUES('202609290043','Scoped Lodge Details settings, dues and recurring meeting patterns',SHA2('202609290043_lodge_details_v1',256),0);

COMMIT;