-- Optional Director of Ceremonies workspace. Canonical meetings remain in meetings.
ALTER TABLE calendar_planning_entries MODIFY entry_kind ENUM('rehearsal','lodge_of_instruction','installation','provincial_visit','special_event','summons_deadline','dining_deadline','reporting_date','officer_task') NOT NULL;
ALTER TABLE calendar_recurring_templates MODIFY entry_kind ENUM('rehearsal','lodge_of_instruction','installation','provincial_visit','special_event','reporting_date','officer_task') NOT NULL;
ALTER TABLE tenant_retention_schedules MODIFY data_class ENUM('memberships','candidates','guest_directory','bookings','guests','messages','documents','welfare','finance','dc_private_notes') NOT NULL;

INSERT INTO tenant_roles(role_key,display_name,built_in,active) VALUES('director_of_ceremonies','Director of Ceremonies',1,1) ON DUPLICATE KEY UPDATE display_name=VALUES(display_name),active=1;
INSERT IGNORE INTO tenant_role_scopes(role_key,scope_type) VALUES('director_of_ceremonies','unit'),('director_of_ceremonies','province');

CREATE TABLE dc_ceremony_templates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, scope_type ENUM('province','unit') NOT NULL, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NULL,
 order_key VARCHAR(40) NOT NULL, title VARCHAR(200) NOT NULL, ceremony_term VARCHAR(100) NOT NULL, structure_json MEDIUMTEXT NOT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_dc_template_scope(scope_type,province_id,unit_id,order_key,active), FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id),
 CONSTRAINT chk_dc_template_scope CHECK((scope_type='province' AND unit_id IS NULL) OR (scope_type='unit' AND unit_id IS NOT NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dc_template_roles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, template_id BIGINT UNSIGNED NOT NULL, role_key VARCHAR(80) NOT NULL, role_label VARCHAR(150) NOT NULL,
 required TINYINT(1) NOT NULL DEFAULT 1, display_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, instructions_cipher MEDIUMTEXT NULL,
 UNIQUE KEY uq_dc_template_role(template_id,role_key), FOREIGN KEY(template_id) REFERENCES dc_ceremony_templates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dc_event_programmes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, meeting_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NOT NULL, template_id BIGINT UNSIGNED NULL,
 title VARCHAR(200) NOT NULL, status ENUM('draft','approved','published','superseded') NOT NULL DEFAULT 'draft', version_no INT UNSIGNED NOT NULL DEFAULT 1,
 approved_by BIGINT UNSIGNED NULL, approved_at DATETIME NULL, published_at DATETIME NULL, created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_dc_programme_meeting(meeting_id), KEY idx_dc_programme_unit(unit_id,status), FOREIGN KEY(meeting_id) REFERENCES meetings(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE, FOREIGN KEY(template_id) REFERENCES dc_ceremony_templates(id) ON DELETE SET NULL,
 FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dc_programme_assignments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, programme_id BIGINT UNSIGNED NOT NULL, role_key VARCHAR(80) NOT NULL, role_label VARCHAR(150) NOT NULL,
 person_id BIGINT UNSIGNED NULL, understudy_person_id BIGINT UNSIGNED NULL, assignment_source ENUM('suggested','manual') NOT NULL DEFAULT 'manual',
 status ENUM('suggested','invited','accepted','declined','replaced','withdrawn') NOT NULL DEFAULT 'suggested', availability ENUM('unknown','available','unavailable') NOT NULL DEFAULT 'unknown',
 accepted_at DATETIME NULL, responded_at DATETIME NULL, updated_by BIGINT UNSIGNED NOT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_dc_programme_role(programme_id,role_key), KEY idx_dc_assignment_people(person_id,understudy_person_id,status),
 FOREIGN KEY(programme_id) REFERENCES dc_event_programmes(id) ON DELETE CASCADE, FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE SET NULL,
 FOREIGN KEY(understudy_person_id) REFERENCES people(id) ON DELETE SET NULL, FOREIGN KEY(updated_by) REFERENCES users(id),
 CONSTRAINT chk_dc_different_understudy CHECK(person_id IS NULL OR understudy_person_id IS NULL OR person_id<>understudy_person_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dc_assignment_changes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, programme_id BIGINT UNSIGNED NOT NULL, version_no INT UNSIGNED NOT NULL, assignment_id BIGINT UNSIGNED NULL,
 change_kind ENUM('assigned','accepted','declined','replaced','withdrawn','approved','published') NOT NULL, before_json TEXT NULL, after_json TEXT NULL,
 changed_by BIGINT UNSIGNED NULL, changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_dc_change_version(programme_id,version_no,id), FOREIGN KEY(programme_id) REFERENCES dc_event_programmes(id) ON DELETE CASCADE,
 FOREIGN KEY(assignment_id) REFERENCES dc_programme_assignments(id) ON DELETE SET NULL, FOREIGN KEY(changed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dc_rehearsals (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, programme_id BIGINT UNSIGNED NULL, calendar_entry_id BIGINT UNSIGNED NOT NULL, rehearsal_type ENUM('rehearsal','lodge_of_instruction') NOT NULL,
 created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_dc_rehearsal_calendar(calendar_entry_id),
 FOREIGN KEY(programme_id) REFERENCES dc_event_programmes(id) ON DELETE SET NULL, FOREIGN KEY(calendar_entry_id) REFERENCES calendar_planning_entries(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dc_rehearsal_attendance (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, rehearsal_id BIGINT UNSIGNED NOT NULL, person_id BIGINT UNSIGNED NOT NULL,
 attendance ENUM('invited','attended','apology','absent') NOT NULL DEFAULT 'invited', recorded_by BIGINT UNSIGNED NOT NULL, recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_dc_rehearsal_person(rehearsal_id,person_id), FOREIGN KEY(rehearsal_id) REFERENCES dc_rehearsals(id) ON DELETE CASCADE,
 FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE CASCADE, FOREIGN KEY(recorded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dc_volunteer_profiles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL, person_id BIGINT UNSIGNED NOT NULL,
 preferred_roles_json TEXT NULL, availability_notes_cipher MEDIUMTEXT NULL, experience_cipher MEDIUMTEXT NULL, consented_at DATETIME NOT NULL,
 withdrawn_at DATETIME NULL, retention_until DATE NULL, updated_by BIGINT UNSIGNED NOT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_dc_volunteer(unit_id,person_id), KEY idx_dc_volunteer_active(unit_id,withdrawn_at,retention_until),
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE, FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE CASCADE, FOREIGN KEY(updated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dc_private_notes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL, person_id BIGINT UNSIGNED NULL, programme_id BIGINT UNSIGNED NULL,
 note_cipher MEDIUMTEXT NOT NULL, retention_until DATE NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deleted_at DATETIME NULL, KEY idx_dc_note_scope(unit_id,person_id,programme_id,retention_until), FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE SET NULL, FOREIGN KEY(programme_id) REFERENCES dc_event_programmes(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dc_private_exports (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL, token_hash CHAR(64) NOT NULL, payload_cipher MEDIUMTEXT NOT NULL,
 expires_at DATETIME NOT NULL, created_by BIGINT UNSIGNED NOT NULL, downloaded_at DATETIME NULL, revoked_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_dc_export_token(token_hash), KEY idx_dc_export_expiry(unit_id,expires_at,revoked_at), FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO migration_history(version,name,checksum,execution_ms) VALUES('202609280029','Director of Ceremonies workspaces',SHA2('202609280029_director_of_ceremonies_v1',256),0)
ON DUPLICATE KEY UPDATE name=VALUES(name),checksum=VALUES(checksum);