CREATE TABLE IF NOT EXISTS province_cross_membership_review_queue (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL,
 person_a_id BIGINT UNSIGNED NOT NULL, person_b_id BIGINT UNSIGNED NOT NULL,
 unit_a_id BIGINT UNSIGNED NOT NULL, unit_b_id BIGINT UNSIGNED NOT NULL,
 review_state ENUM('open','confirmed_same','not_same','dismissed') NOT NULL DEFAULT 'open',
 reason_key ENUM('matching_contact','import_candidate','manual_review') NOT NULL,
 reviewed_by BIGINT UNSIGNED NULL, reviewed_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pcmrq_pair(province_id,person_a_id,person_b_id,unit_a_id,unit_b_id), KEY idx_pcmrq_queue(province_id,review_state,created_at),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(person_a_id) REFERENCES people(id) ON DELETE CASCADE,
 FOREIGN KEY(person_b_id) REFERENCES people(id) ON DELETE CASCADE, FOREIGN KEY(unit_a_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_b_id) REFERENCES units(id) ON DELETE CASCADE, FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT chk_pcmrq_people CHECK(person_a_id<person_b_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;