-- Explicit member relationships and role-owned action centre. No email-based access inference.
CREATE TABLE person_province_relationships (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL, person_id BIGINT UNSIGNED NOT NULL,
 relationship_type VARCHAR(80) NOT NULL, status ENUM('active','inactive','ended') NOT NULL DEFAULT 'active',
 start_date DATE NULL, end_date DATE NULL, verified_by BIGINT UNSIGNED NOT NULL, verified_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_person_province_relationship(province_id,person_id,relationship_type),
 KEY idx_person_province(person_id,status,province_id),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE CASCADE,
 FOREIGN KEY(verified_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_booking_links (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, person_id BIGINT UNSIGNED NOT NULL, booking_id BIGINT UNSIGNED NOT NULL,
 verified_by BIGINT UNSIGNED NOT NULL, verified_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_member_booking(booking_id), KEY idx_member_booking_person(person_id,booking_id),
 FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE CASCADE,
 FOREIGN KEY(booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
 FOREIGN KEY(verified_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_visible_communications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, person_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NULL, province_id BIGINT UNSIGNED NULL, communication_id BIGINT UNSIGNED NULL,
 subject VARCHAR(250) NOT NULL, summary TEXT NULL, sent_at DATETIME NOT NULL,
 visible_from DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, revoked_at DATETIME NULL, linked_by BIGINT UNSIGNED NOT NULL,
 KEY idx_member_communication(person_id,unit_id,province_id,revoked_at,sent_at),
 FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(communication_id) REFERENCES communication_log(id) ON DELETE SET NULL,
 FOREIGN KEY(linked_by) REFERENCES users(id),
 CONSTRAINT chk_member_communication_scope CHECK((unit_id IS NULL)<>(province_id IS NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_correction_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, person_id BIGINT UNSIGNED NOT NULL,
 scope_type ENUM('profile','unit_membership','province_relationship') NOT NULL, scope_id BIGINT UNSIGNED NULL,
 field_key VARCHAR(60) NOT NULL, requested_value VARCHAR(500) NOT NULL, reason VARCHAR(1000) NULL,
 status ENUM('open','accepted','partially_accepted','declined','withdrawn') NOT NULL DEFAULT 'open',
 submitted_by BIGINT UNSIGNED NOT NULL, reviewed_by BIGINT UNSIGNED NULL, submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, reviewed_at DATETIME NULL,
 KEY idx_member_correction(person_id,status,submitted_at),
 FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE CASCADE,
 FOREIGN KEY(submitted_by) REFERENCES users(id), FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_communication_preferences (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, person_id BIGINT UNSIGNED NOT NULL, scope_type ENUM('platform','province','unit') NOT NULL,
 province_id BIGINT UNSIGNED NULL, unit_id BIGINT UNSIGNED NULL,
 event_invitations TINYINT(1) NOT NULL DEFAULT 0, general_messages TINYINT(1) NOT NULL DEFAULT 0,
 service_messages TINYINT(1) NOT NULL DEFAULT 1, preferred_channel ENUM('email','phone','post','none') NOT NULL DEFAULT 'email',
 updated_by BIGINT UNSIGNED NOT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 scope_key VARCHAR(90) GENERATED ALWAYS AS (CONCAT(scope_type,':',IFNULL(province_id,0),':',IFNULL(unit_id,0))) STORED,
 UNIQUE KEY uq_member_preference_scope(person_id,scope_key),
 FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE CASCADE,
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(updated_by) REFERENCES users(id),
 CONSTRAINT chk_member_preference_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 unit_id IS NOT NULL)
 )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE action_centre_authorisations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL,
 task_kind ENUM('unpaid_reminder') NOT NULL, authorised_by BIGINT UNSIGNED NOT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1, expires_at DATETIME NULL, authorised_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_action_authorisation(unit_id,task_kind),
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE, FOREIGN KEY(authorised_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE action_centre_tasks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL,
 task_kind ENUM('role_message','invitation_request','booking_deadline','waiting_offer','draft_publication','unpaid_reminder','access_review') NOT NULL,
 source_type VARCHAR(50) NOT NULL, source_id BIGINT UNSIGNED NOT NULL, owner_role VARCHAR(50) NOT NULL,
 required_capability VARCHAR(80) NOT NULL, title VARCHAR(250) NOT NULL, due_at DATETIME NULL, action_url VARCHAR(500) NOT NULL,
 status ENUM('open','resolved','dismissed') NOT NULL DEFAULT 'open', resolved_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_action_source(unit_id,task_kind,source_type,source_id), KEY idx_action_owner(unit_id,status,owner_role,due_at),
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE officer_handovers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL, role_key VARCHAR(50) NOT NULL,
 outgoing_membership_id BIGINT UNSIGNED NOT NULL, incoming_membership_id BIGINT UNSIGNED NOT NULL,
 handover_at DATETIME NOT NULL, status ENUM('draft','active','completed','cancelled') NOT NULL DEFAULT 'draft',
 created_by BIGINT UNSIGNED NOT NULL, completed_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, completed_at DATETIME NULL,
 KEY idx_handover_unit(unit_id,status,handover_at),
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(outgoing_membership_id) REFERENCES user_memberships(id), FOREIGN KEY(incoming_membership_id) REFERENCES user_memberships(id),
 FOREIGN KEY(created_by) REFERENCES users(id), FOREIGN KEY(completed_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT chk_handover_memberships CHECK(outgoing_membership_id<>incoming_membership_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE officer_handover_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, handover_id BIGINT UNSIGNED NOT NULL,
 item_key VARCHAR(80) NOT NULL, item_label VARCHAR(250) NOT NULL, restricted_note TEXT NULL,
 outgoing_confirmed_at DATETIME NULL, incoming_confirmed_at DATETIME NULL,
 UNIQUE KEY uq_handover_item(handover_id,item_key),
 FOREIGN KEY(handover_id) REFERENCES officer_handovers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO migration_history(version,name,checksum,execution_ms)
VALUES('202609270027','Signed-in memberships hub, role action centre and officer handover',SHA2('202609270027_member_hub_action_centre_v1',256),0)
ON DUPLICATE KEY UPDATE name=VALUES(name),checksum=VALUES(checksum);