-- Apply after 202609240005. Additive; no existing accounts or grants change.
CREATE TABLE province_admin_invites (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 province_id BIGINT UNSIGNED NOT NULL,
 email VARCHAR(254) NOT NULL,
 token_hash CHAR(64) NOT NULL,
 invited_by BIGINT UNSIGNED NULL,
 expires_at DATETIME NOT NULL,
 accepted_at DATETIME NULL,
 revoked_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_province_admin_invite_token(token_hash),
 KEY idx_province_admin_invites(province_id,created_at),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(invited_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;