-- Run once after migration 017. An unassigned unit is held outside public
-- Province routing until assigned. Existing units and roles are unchanged.
ALTER TABLE units MODIFY province_id BIGINT UNSIGNED NULL;

CREATE TABLE unit_role_invitations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 email VARCHAR(254) NOT NULL,
 role_key VARCHAR(50) NOT NULL,
 token_hash CHAR(64) NOT NULL,
 invited_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 expires_at DATETIME NOT NULL,
 accepted_at DATETIME NULL,
 revoked_at DATETIME NULL,
 UNIQUE KEY uq_unit_role_token(token_hash),
 KEY idx_unit_role_pending(unit_id,email,expires_at),
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(invited_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;